MySQL之建表语句

mysql安装教程见博客：MySQL 7.7.25 图文安装教程（Win10）
本篇博客以学生表、课程表以及学生-课程表为例，讲解mysql常用的建表语句。

1. 学生表

Sno(学号)	Sname(姓名)	Sex(性别)	Sage(年龄)	Sdept(系)
201215121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215123	王敏	女	18	MA
201215124	张立	男	19	IS

其中，Sno(学号)为主码。

分析：Sno可用varchar()类型，也可以使用int类型，但表中的学号是依次递增的，故用int类型较好，可以实现按规律的增加；Sname是字符串类型，可用varchar()来存储，一般给10个字符就可以；Sex是字符类型，可用char()来存储，由于性别只有男和女，故给1个字符即可；Sage是数字类型，且最大不超过100，故可以用tinyint来存储；Sdept也是字符串型，可用char()来存储，根据表格来看，给4个字符即可。

建表语句：
首先为该表创建一个数据库：学生-课程数据库，之后的课程表和学生-课程表也可以放入该数据库内。

create database student_course;

查看该数据库是否已经在库中。

show databases;

如下图所示，创建好之后会展示在数据库中。

使用创建好的student_course数据库，在里面创建Student表，并备注为学生表。

use student_course;
create table Student(
    -> Sno int not null auto_increment primary key,
    -> Sname varchar(10) not null,
    -> Sex char(1) not null,
    -> Sage tinyint(100) not null,
    -> Sdept char(4) not null)comment = '学生表';

向表中插入数据（由于设置了Sno为自增长，在插入数据时可以不用写Sno，直接为其赋自增长初值即可）。

alter table Student auto_increment = 201215121;
insert into Student (Sname, Sex, Sage, Sdept) values ('李勇', '男', 20, 'CS');
insert into Student (Sname, Sex, Sage, Sdept) values ('刘晨', '女', 19, 'CS');
insert into Student (Sname, Sex, Sage, Sdept) values ('王敏', '女', 18, 'MA');
insert into Student (Sname, Sex, Sage, Sdept) values ('张立', '男', 19, 'IS');

至此，学生表就建好了，可以用select语句查看全表内容。

select * from Student;

如下图就可以看到刚刚在表中插入的数据。

2. 课程表

Cno(课程号)	Cname(课程名)	Cpno(先修课程号)	Ccredit(学分)
1	数据库	5	4
2	数学	NULL	2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	NULL	2
6	PASCAL语言	2	4

其中，Cno(课程号)为主码，Cpno为外码。

分析：Cno为数字类型，和Sno一样，是依次递增，故可以用tinyint来存储，并设置为自增长；Cname为字符串，可以设置为字符串，给20个字符即可；Cpno为数字类型，用tinynit存储即可；Ccredit为数字类型，由于数值比较小，用tinynit存储即可。

建表语句，并规定Cpno为外码。

 create table Course(
    ->Cno tinyint not null auto_increment primary key,
    ->Cname varchar(20) not null,
    ->Cpno tinyint,
    ->Ccredit tinyint not null)comment = '课程表';
 alter table Course add foreign key(Cpno) references Course (Cno);

向表中插入数据（由于Cpno是外键，故先添加参考列，再添加外键列）。

 insert into Course(Cname, Ccredit) values ('数据库', 4);
 insert into Course(Cname, Ccredit) values ('数学', 2);
 insert into Course(Cname, Ccredit) values ('信息系统', 4);
 insert into Course(Cname, Ccredit) values ('操作系统', 3); 
 insert into Course(Cname, Ccredit) values ('数据结构', 2);
 insert into Course(Cname, Ccredit) values ('PASCAL语言', 4);
 update Course set Cpno = 5 where Cno = 1;
 update Course set Cpno = 1 where Cno = 3;
 update Course set Cpno = 6 where Cno = 4;
 update Course set Cpno = 2 where Cno = 6;

至此，课程表就建立完成了，可以用查询语句查看全表内容。

select * from Course;

由于设置了外键，根据参照完整性规则，外码要么为空，要么为有效值。当插入的数据违反了这个规则，就会产生ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (student_course.course, CONSTRAINT course_ibfk_1 FOREIGN KEY (Cpno) REFERENCES course (Cno))错误。
如下图：

当要删除或者更新一条数据时，由于有外码约束，不可以直接使用delete或者update语句，需先取消外码约束，再进行操作，最后再恢复外码约束。否则，会产生ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (student_course.course, CONSTRAINT course_ibfk_1 FOREIGN KEY (Cpno) REFERENCES course (Cno))错误。

/*取消外码约束*/
set foreign_key_checks = 0;
/*删除或更新数据*/
delete/update from 表名 where 条件;
/*恢复外码约束*/
set foreign_key_checks = 1;

3. 学生-课程表

Sno(学号)	Cno(课程号)	Grade(成绩)
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

其中，Sno(学号)和Cno(课程号)为主码，同时也是外码。

分析：Sno是数字类型，由于范围较大，可用int类型存储；Cno和Grade都是数字类型，范围都较小，可用tinyint来存储。

建表语句（注意，Sno(学号)和Cno(课程号)为主码，同时也是外码）。

 create table SC(
    -> Sno int not null,
    -> Cno tinyint not null,
    -> Grade tinyint not null,
    -> primary key(Sno, Cno))comment = '学生课程表';
alter table SC add foreign key(Sno) references Student(Sno);
alter table SC add foreign key(Cno) references Course(Cno);

向表中插入数据。

insert into SC values(201215121, 1, 92);
insert into SC values(201215121, 2, 85);
insert into SC values(201215121, 3, 88);
insert into SC values(201215122, 2, 90);
insert into SC values(201215122, 3, 80);

同样，因为学生-课程表也有外码约束，在添加数据时也要遵循参照完整性规则。

最后，补充一个小知识：在很多大型大型公司中，包括阿里在内，都是不建议使用外码约束的，原因是在外码约束的的前提下，删除和更新数据操作会很痛苦。就以课程表为例，要删除数据要经过三个步骤，取消外键约束，删除数据，恢复外键约束，但如果没有外键约束，就可以直接删除数据。因此，在以后设计数据库的时候尽量避免外码约束的使用。