（1）表基本操作
1)创建表
create table 表名(字段 数据类型 约束条件)
mysql> create table student1(id int,name varchar(50),sex enum('w','f'),age int);
2)查看表
mysql> desc student1; #查看表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('w','f') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
mysql> show create table student1; #查看创表语句
----------------+| student1 | CREATE TABLE `student1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` enum('w','f') DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3)删除表
drop table 表名
4)插入数据没有约束条件：数据可以重复
mysql> insert into student1 values(3,'wack','f',21),(4,'jaxc','w',18);
mysql> insert into student1 values(3,'wack','f',21),(4,'jaxc','w',18);
mysql> insert into student1(name,sex) values('wf','w');
mysql> insert into student1(name,sex) values('wf','w');
mysql> select * from student1;
| 4 | jaxc | w | 18 |
| 3 | wack | f | 21 |
| 4 | jaxc | w | 18 |
| NULL | wf | w | NULL |
| NULL | wf | w | NULL |
+------+------+------+------+
（2）表约束条件：保证数据的完整性和一致性
1)约束条件
primary key ：标识字段为主键，可以唯一的标识记录，特性：唯一，不可以为空，一个表中只有1个主键
foreign key ：标识字段为外键，实现表与表之间的关联
not null：标识该字段不能为空
unique key ：标识该字段唯一，可以为空，一个表中可以有多个unique key
auto_increment：标识该字段的值自动增长（整数类型，而且是主键）
default ：为该字段设置默认值
unsigned ：无符号，正数
zerofill : 使用0填充
2)default默认值和not null非空
mysql> create table student1(id int not null,name varchar(50) not null,sex enum('m','f') default 'm' not null,age int unsigned default 18 not null);
mysql> desc student1;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| sex | enum('m','f') | NO | | m | |
| age | int(10) unsigned | NO | | 18 | |
+-------+------------------+------+-----+---------+-------+
mysql> insert into student1 values(1,'jack','m',21);
mysql> insert into student1(id,name) values(2,'robin');
mysql> insert into student1 values(3,null,'f',28);
ERROR 1048 (23000): Column 'name' cannot be null
3)primary key单列做主键测试
创建主键方式1：
mysql> create table student(id int auto_increment primary key,
-> name varchar(50) not null,
-> sex enum('m','f') not null default 'm',
-> age int not null default 18);
创建主键方式二：
mysql> create table student(id int auto_increment,
-> name varchar(50) not null,
-> sex enum('m','f') not null default 'm',
-> age int not null default 18, primary key(id));
mysql> desc student;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| sex | enum('m','f') | NO | | m | |
| age | int(11) | NO | | 18 | |
+-------+---------------+------+-----+---------+----------------+
mysql> insert into student values(1,'jack','m',18);
mysql> insert into student(name) values('robby');
mysql> insert into student values(1,'wf','m',20);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from student;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | jack | m | 18 |
| 2 | robby | m | 18 |
+----+-------+-----+-----+
4)复合多列主键：多个列组合作为主键，单列值可以重复
mysql> create table service(ip varchar(15) not null,
-> service varchar(10) not null,
-> port int not null,
-> protocol enum('tcp','udp') not null default 'tcp',
-> allow enum('Y','N') default 'N',
-> primary key(ip,service));
mysql> desc service;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| service | varchar(10) | NO | PRI | NULL | |
| port | int(11) | NO | | NULL | |
| protocol | enum('tcp','udp') | NO | | tcp | |
| allow | enum('Y','N') | YES | | N | |
+----------+-------------------+------+-----+---------+-------+
mysql> insert into service values('192.168.1.32','http',80,'tcp','Y');
mysql> insert into service(ip,service,port) values('192.168.1.32','ftp',21);
mysql> insert into service(ip,service,port) values('192.168.1.32','http',80);
ERROR 1062 (23000): Duplicate entry '192.168.1.32-http' for key 'PRIMAR
mysql> select * from service;
+--------------+---------+------+----------+-------+
| ip | service | port | protocol | allow |
+--------------+---------+------+----------+-------+
| 192.168.1.32 | dns | 53 | tcp | N |
| 192.168.1.32 | ftp | 21 | tcp | N |
| 192.168.1.32 | http | 80 | tcp | Y |
+--------------+---------+------+----------+-------+
5)unique：标识该字段唯一，可以为空，一个表中可以有多个unique key
mysql> create table department(dept_id int,dept_name varchar(50) unique);
mysql> desc department;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(50) | YES | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> insert into department values(1,"hr");
mysql> insert into department values(1,"it");
mysql> insert into department values(1,"hr");
ERROR 1062 (23000): Duplicate entry 'hr' for key 'dept_name'
mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | hr |
| 1 | it |
+---------+-----------+
（3）修改表
1)语法
修改表：
alter table 表名 rename 新表名
增加字段：
alter table 表名 add 字段名 数据类型[约束条件]
alter table 表名 add 字段名 数据类型[约束条件] first;
alter table 表名 add 字段名 数据类型[约束条件] after 字段名;
删除字段：
alter table 表名 drop 字段名
修改字段类型：
alter table 表名 modify 字段名 数据类型[约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型[约束条件];
2)修改表的存储引擎：
mysql> alter table t1 engine=innodb;
3)添加字段
mysql> create table t1(id int);
mysql> alter table t1 add name varchar(50) not null,add age int not null default 20;
mysql> alter table t1 add num int not null after name, add sex enum('m','f') not null default 'm' first
4)删除字段
mysql> alter table t1 drop sex;
5)修改字段类型：
mysql> alter table t1 modify age tinyint; \注意保留原有的约束条件
mysql> alter table t1 modify age tinyint not null default 20;
mysql> alter table t1 modify id int not null primary key auto_increment; \在字段有主键的情况下添加auto_increment这种方法不行
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table t1 modify id int not null auto_increment; \在字段有主键的情况下添加auto_increment方式
6)添加复合主键：
mysql >alter table t2 add primary key(id,name);
7)添加主键：
mysql> alter table t1 modify id int not null primary key; \添加主键，不建议
mysql> alter table t1 add primary key(id);
8)删除主键：如果字段有auto_increment约束，需要先删除这个约束才能在删除主键，因为自增依赖主键
mysql> alter table t1 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table t1 modify id int not null; \先删除主键上的自增
mysql> alter table t1 drop primary key; \删除主键
（4）复制表
1)复制表结构+记录，key不会复制，也就是主键丶索引不会复制
mysql> create table new_t1 select * from t1; \key不会复制，也就是主键丶索引不会复制
2)只复制表结构
mysql> create table new_t2 select * from t1 where 1=3; //条件为假，查不到任何记录
3)复制表结构，包括key
mysql> create table new_t3 like t1;