1 表介绍

　　表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。

　　存储这员工信息的文件是这样的：

id,name,age,sex,phone,job1,Alex,83,female,13651054608,IT2,Egon,26,male,13304320533,Tearcher3,nezha,25,male,13332353222,IT4,boss_jin,40,male,13332353333,IT

　　把上面这个文件改成一张表：

# id,name,age,sex,phone,job称为字段，# 其余的，一行内容称为一条记录

id	name	age	sex	phone	job
1	Alex	83	female	13651054608	IT
2	Egon	26	male	13304320533	Teacher
3	nezha	25	male	13332353222	IT
4	boss_jin	40	male	13332353333	IT

2 创建表

#语法：create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]);#注意：1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

mysql> create database staff;Query OK, 1 row affected (0.00 sec)mysql> use staff;Database changedmysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));Query OK, 0 rows affected (0.02 sec)mysql> show tables;+-----------------+| Tables_in_staff |+-----------------+| staff_info      |+-----------------+1 row in set (0.00 sec)mysql> desc staff_info;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql> select id,name,sex from staff_info;Empty set (0.00 sec)mysql> select * from staff_info;Empty set (0.00 sec)

mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher');Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from staff_info;+------+----------+------+--------+-------------+---------+| id   | name     | age  | sex    | phone       | job     |+------+----------+------+--------+-------------+---------+|    1 | Alex     |   83 | female | 13651054608 | IT      ||    2 | Egon     |   26 | male   | 13304320533 | Teacher ||    3 | nezha    |   25 | male   | 13332353222 | IT      ||    4 | boss_jin |   40 | male   | 13332353333 | IT      |+------+----------+------+--------+-------------+---------+4 rows in set (0.00 sec)

3 查看表结构

# ①describe [tablename]：可以查看当前的表结构# ②desc [tablename]：可以查看当前的表结构# ③show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。   （\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）

mysql> describe staff_info;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql> desc staff_info;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql> show create table staff_info\G;*************************** 1. row ***************************       Table: staff_infoCreate Table: CREATE TABLE `staff_info` (  `id` int(11) DEFAULT NULL,  `name` varchar(50) DEFAULT NULL,  `age` int(3) DEFAULT NULL,  `sex` enum('male','female') DEFAULT NULL,  `phone` bigint(11) DEFAULT NULL,  `job` varchar(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)ERROR: No query specified

4 mysql中的数据类型

　　（https://www.cnblogs.com/Eva-J/articles/9683316.html）

5 表的完整性约束

　　（https://www.cnblogs.com/Eva-J/articles/9687915.html）

6 修改表结构

语法：#1. 修改表名      ALTER TABLE 表名                       RENAME 新表名;#2. 增加字段      ALTER TABLE 表名                      ADD 字段名  数据类型 [完整性约束条件…],                      ADD 字段名  数据类型 [完整性约束条件…];                            #3. 删除字段      ALTER TABLE 表名                       DROP 字段名;#4. 修改字段      ALTER TABLE 表名                       MODIFY  字段名 数据类型 [完整性约束条件…];      ALTER TABLE 表名                       CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];      ALTER TABLE 表名                       CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];#5.修改字段排列顺序/在增加的时候指定字段位置    ALTER TABLE 表名                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;    ALTER TABLE 表名                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;    ALTER TABLE 表名                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;    ALTER TABLE 表名                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

mysql> desc staff_info;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)# 表重命名mysql> alter table staff_info rename staff;Query OK, 0 rows affected (0.00 sec)mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)# 删除sex列mysql> alter table staff drop sex;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || age   | int(3)      | YES  |     | NULL    |       || phone | bigint(11)  | YES  |     | NULL    |       || job   | varchar(11) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)# 添加列mysql> alter table staff add sex enum('male','female');Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0# 修改id的宽度mysql> alter table staff modify id int(4);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(4)                | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.01 sec)# 修改name列的字段名mysql> alter table staff change name sname varchar(20);Query OK, 4 rows affected (0.03 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(4)                | YES  |     | NULL    |       || sname | varchar(20)           | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)# 修改sex列的位置mysql> alter table staff modify sex enum('male','female') after sname;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(4)                | YES  |     | NULL    |       || sname | varchar(20)           | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)# 创建自增id主键mysql> alter table staff modify id int(4) primary key auto_increment;Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+----------------+| Field | Type                  | Null | Key | Default | Extra          |+-------+-----------------------+------+-----+---------+----------------+| id    | int(4)                | NO   | PRI | NULL    | auto_increment || sname | varchar(20)           | YES  |     | NULL    |                || sex   | enum('male','female') | YES  |     | NULL    |                || age   | int(3)                | YES  |     | NULL    |                || phone | bigint(11)            | YES  |     | NULL    |                || job   | varchar(11)           | YES  |     | NULL    |                |+-------+-----------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)# 删除主键，可以看到删除一个自增主键会报错mysql> alter table staff 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 staff modify id int(11);Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | NO   | PRI | 0       |       || sname | varchar(20)           | YES  |     | NULL    |       || sex   | enum('male','female') | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.01 sec)mysql> alter table staff drop primary key;Query OK, 4 rows affected (0.06 sec)Records: 4  Duplicates: 0  Warnings: 0# 添加联合主键mysql> alter table staff add primary key (sname,age);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0# 删除主键mysql> alter table staff drop primary key;Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0# 创建主键idmysql> alter table staff add primary key (id);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | NO   | PRI | 0       |       || sname | varchar(20)           | NO   |     |         |       || sex   | enum('male','female') | YES  |     | NULL    |       || age   | int(3)                | NO   |     | 0       |       || phone | bigint(11)            | YES  |     | NULL    |       || job   | varchar(11)           | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+6 rows in set (0.00 sec)# 为主键添加自增属性mysql> alter table staff modify id int(4) auto_increment;Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> desc staff;+-------+-----------------------+------+-----+---------+----------------+| Field | Type                  | Null | Key | Default | Extra          |+-------+-----------------------+------+-----+---------+----------------+| id    | int(4)                | NO   | PRI | NULL    | auto_increment || sname | varchar(20)           | NO   |     |         |                || sex   | enum('male','female') | YES  |     | NULL    |                || age   | int(3)                | NO   |     | 0       |                || phone | bigint(11)            | YES  |     | NULL    |                || job   | varchar(11)           | YES  |     | NULL    |                |+-------+-----------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)

7 删除表

#清空表：    delete from t1;     （id从上次计数开始）    truncate table t1;  （id从1开始）#删除表：    drop table t1;

8 多表关系的创建与分析

8.1 一对一

#一对一两张表：学生表和客户表一对一：一个学生是一个客户关联方式：foreign key+unique

create table customer(    -> id int primary key auto_increment,    -> name varchar(20) not null,    -> qq varchar(10) not null,    -> phone char(16) not null    -> );create table student(    -> id int primary key auto_increment,    -> class_name varchar(20) not null,    -> customer_id int unique, #该字段一定要是唯一的    -> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique    -> on delete cascade    -> on update cascade    -> );#增加客户mysql> insert into customer(name,qq,phone) values    -> ('韩蕾','31811231',13811341220),    -> ('杨澜','123123123',15213146809),    -> ('翁惠天','283818181',1867141331),    -> ('杨宗河','283818181',1851143312),    -> ('袁承明','888818181',1861243314),    -> ('袁清','112312312',18811431230)mysql> #增加学生mysql> insert into student(class_name,customer_id) values    -> ('脱产1班',3),    -> ('周末1期',4),    -> ('周末1期',5)    -> ;

8.2 多对一

#一对多或称为多对一三张表：出版社，作者信息，书一对多（或多对一）：一个出版社可以出版多本书关联方式：foreign key

create table press(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20),press_id int not null,foreign key(press_id) references press(id)on delete cascadeon update cascade);insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社');insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3);

8.3 多对多

#多对多三张表：出版社，作者信息，书多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多　　关联方式：foreign key+一张新的表 如：学生表（sid）、课程表（cid）、选课表（包含sid，cid）

=====================多对多=====================create table author(id int primary key auto_increment,name varchar(20));#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,constraint fk_author foreign key(author_id) references author(id)on delete cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));#插入四个作者，id依次排开insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下egon: 九阳神功九阴真经九阴白骨爪独孤九剑降龙十巴掌葵花宝典alex: 九阳神功葵花宝典yuanhao:独孤九剑降龙十巴掌葵花宝典wpq:九阳神功insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);