首页 > 代码库 > MySQL基本命令操作
MySQL基本命令操作
操作系统：CentOS
数据库系统：MySQL
[root@sky9896~]# mysql -h localhost -P 3306 -uroot –p #注意端口号前的P是大小
mysql>select version(),current_date; #检索当前数据库版本和当前时间
+----------------+--------------+
|version() | current_date |
+----------------+--------------+
|5.5.52-cll-lve | 2017-08-09 |
+----------------+--------------+
1row in set (0.00 sec)
mysql>use employees #打开数据库
Databasechanged
mysql>select database(); #查询当前数据库
+------------+
|database() |
+------------+
|employees |
+------------+
1row in set (0.00 sec)
mysql>show tables; #显示该数据库当中的所有表
+---------------------+
|Tables_in_employees |
+---------------------+
|departments |
|dept_emp |
|dept_manager |
|employees |
|salaries |
|titles |
+---------------------+
6rows in set (0.00 sec)
mysql>create table employees_2( #建表
-> emp_no int(11) NOT NULL,
-> birth_date date NOT null,
-> first_name varchar(14) not null,
-> last_name varchar(16) not null,
-> gender enum(‘M‘,‘F‘) not null,
-> hire_date date not null,
-> primary key(emp_no)
-> )engine=innodb default charset=latin1;
QueryOK, 0 rows affected (0.19 sec)
mysql>desc employees_2; #显示表结构
+------------+---------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
|emp_no | int(11) | NO | PRI | NULL | |
|birth_date | date | NO | | NULL | |
|first_name | varchar(14) | NO | | NULL | |
|last_name | varchar(16) | NO | | NULL | |
|gender | enum(‘M‘,‘F‘) | NO | | NULL | |
|hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6rows in set (0.02 sec)
mysql>create table t1(a integer,b char(10));
QueryOK, 0 rows affected (0.11 sec)
mysql>show tables;
+---------------------+
|Tables_in_employees |
+---------------------+
|departments |
|dept_emp |
|dept_manager |
|employees |
|employees_2 |
|salaries |
|t1 |
|titles |
+---------------------+
8rows in set (0.00 sec)
mysql>alter table t1 rename t2; #修改表名
QueryOK, 0 rows affected (0.05 sec)
mysql>show tables;
+---------------------+
|Tables_in_employees |
+---------------------+
|departments |
|dept_emp |
|dept_manager |
|employees |
|employees_2 |
|salaries |
|t2 |
|titles |
+---------------------+
8rows in set (0.00 sec)
mysql>desc t2;
+-------+----------+------+-----+---------+-------+
|Field | Type | Null | Key | Default |Extra |
+-------+----------+------+-----+---------+-------+
|a | int(11) | YES | | NULL | |
|b | char(10) | YES | |NULL | |
+-------+----------+------+-----+---------+-------+
2rows in set (0.00 sec)
mysql> alter table t2 modify a tinyint notnull,change b c char(20); #修改列表
QueryOK, 0 rows affected (0.25 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>desc t2;
+-------+------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default| Extra |
+-------+------------+------+-----+---------+-------+
|a | tinyint(4) | NO | | NULL | |
|c | char(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2rows in set (0.00 sec)
ysql>alter table t2 add d timestamp;
QueryOK, 0 rows affected (0.19 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>desc t2;
+-------+------------+------+-----+-------------------+-----------------------------+
|Field | Type | Null | Key |Default | Extra |
+-------+------------+------+-----+-------------------+-----------------------------+
|a | tinyint(4) | NO | | NULL | |
|c | char(20) | YES | | NULL | |
|d | timestamp | NO | | CURRENT_TIMESTAMP | on updateCURRENT_TIMESTAMP |
+-------+------------+------+-----+-------------------+-----------------------------+
3rows in set (0.00 sec)
mysql>alter table t2 add index(d),add index(a); #添加索引
mysql>show keys from t2;
mysql>insert into employees(emp_no,birth_date,first_name,last_name,gender,hire_date)values(‘1111112‘,‘2017-8-9‘,‘wu‘,‘haiming‘,‘M‘,‘2017-08-09‘);
QueryOK, 1 row affected (0.09 sec)
mysql>select * from employees where emp_no=1111112;
+---------+------------+------------+-----------+--------+------------+
|emp_no | birth_date | first_name |last_name | gender | hire_date |
+---------+------------+------------+-----------+--------+------------+
|1111112 | 2017-08-09 | wu |haiming | M | 2017-08-09 |
+---------+------------+------------+-----------+--------+------------+
1row in set (0.00 sec)
本文出自 “sky9890” 博客，请务必保留此出处http://sky9896.blog.51cto.com/2330653/1954961
MySQL基本命令操作