一 库相关操作：
0创建数据库：
语法(help create database)
CREATE DATABASE 数据库名 charset utf8;
1 查看数据库
show databases;
show create database db1;
select database();
2 选择数据库
USE 数据库名
3 删除数据库
DROP DATABASE 数据库名;
4 修改数据库
alter database db1 charset utf8;
二 表相关操作
创建表：
#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
例子一：
1 创建表
create table t1(id int,name varchar(50),sex enum('male','female'),age int(3));
表中插入数据
insert into t1 values(1,'egon','male',18),(2,'alex','female',81); 顺序要和表结构一致
查看表结构
1 mysql> describe t1;
2 +-------+-----------------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +-------+-----------------------+------+-----+---------+-------+
5 | id | int(11) | YES | | NULL | |
6 | name | varchar(50) | YES | | NULL | |
7 | sex | enum('male','female') | YES | | NULL | |
8 | age | int(3) | YES | | NULL | |
9 +-------+-----------------------+------+-----+---------+-------+
10 4 rows in set (0.00 sec)
show create table t1\G; #查看表详细结构，可加\G
2 修改表ALTER TABLE
ALTER TABLE 表名 RENAME 新表名;
例子： alter table t1 rename t2;
3 增加表字段：（不能和原来的重复）
1 mysql> alter table student10 add name varchar(20) not null,add age int(3) not null default 22;
2 Query OK, 0 rows affected (1.06 sec)
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> describe student10;
6 +-------+-------------+------+-----+---------+-------+
7 | Field | Type | Null | Key | Default | Extra |
8 +-------+-------------+------+-----+---------+-------+
9 | id | int(11) | YES | | NULL | |
10 | name | varchar(20) | NO | | NULL | |
11 | age | int(3) | NO | | 22 | |
12 +-------+-------------+------+-----+---------+-------+
13 3 rows in set (0.00 sec)
4 在name字段之后添加一个class字段
1 mysql> alter table student10 add class varchar(10) not null after name;
2 Query OK, 0 rows affected (1.00 sec)
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> describe student10;
6 +-------+-------------+------+-----+---------+-------+
7 | Field | Type | Null | Key | Default | Extra |
8 +-------+-------------+------+-----+---------+-------+
9 | id | int(11) | YES | | NULL | |
10 | name | varchar(20) | NO | | NULL | |
11 | class | varchar(10) | NO | | NULL | |
12 | age | int(3) | NO | | 22 | |
13 +-------+-------------+------+-----+---------+-------+
14 4 rows in set (0.00 sec)
5 添加到字段最前面
1 mysql> alter table student10 add sex enum('male','female') default 'male' first;
2 Query OK, 0 rows affected (1.05 sec)
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> describe student10;
6 +-------+-----------------------+------+-----+---------+-------+
7 | Field | Type | Null | Key | Default | Extra |
8 +-------+-----------------------+------+-----+---------+-------+
9 | sex | enum('male','female') | YES | | male | |
10 | id | int(11) | YES | | NULL | |
11 | name | varchar(20) | NO | | NULL | |
12 | class | varchar(10) | NO | | NULL | |
13 | age | int(3) | NO | | 22 | |
14 +-------+-----------------------+------+-----+---------+-------+
15 5 rows in set (0.00 sec)
6 删除字段
mysql> alter table student10 drop sex;
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
7 修改字段 modify
例子一：
1 mysql> alter table student10 modify age int(5);
2 Query OK, 0 rows affected (1.21 sec)
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> describe student10;
6 +-------+-------------+------+-----+---------+-------+
7 | Field | Type | Null | Key | Default | Extra |
8 +-------+-------------+------+-----+---------+-------+
9 | id | int(11) | YES | | NULL | |
10 | name | varchar(20) | NO | | NULL | |
11 | class | varchar(10) | NO | | NULL | |
12 | age | int(5) | YES | | NULL | |
13 +-------+-------------+------+-----+---------+-------+
14 4 rows in set (0.00 sec)
例子二：
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
Query OK, 0 rows affected (1.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe student10;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | NO | | NULL | |
| age | int(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
8. 对已经存在的表增加复合主键
mysql> alter table service2
-> add primary key(host_ip,port);
9. 增加主键
mysql> alter table student1
-> modify name varchar(10) not null primary key;
10. 增加主键和自动增长
mysql> alter table student1
-> modify id int not null primary key auto_increment;
11. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null;
b. 删除主键
mysql> alter table student10
-> drop primary key;
12 复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引
mysql> create table new_hu select * from department;
Query OK, 2 rows affected (0.77 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from new_hu;
+-------+-----------------------------------+
| id | name |
+-------+-----------------------------------+
| 1 | 欧德博爱技术有限事业部 |
| 22222 | 艾利克斯人力资源部 |
+-------+-----------------------------------+
2 rows in set (0.00 sec)
只复制表结构：用like
mysql> create table t10 like department;
Query OK, 0 rows affected (0.66 sec)
mysql> select * from t10;
Empty set (0.01 sec)
13 删除表
DROP TABLE 表名;
