MySQL建表及约束

MySQL建表及外约束

---

如果指定的数据库存在则删除该数据库

drop database if exists school;

创建数据库并指定默认的字符集

create database school default charset utf8;

切换到school数据库

use school;

关系型数据库通过二维表来组织数据
varchar（10）表示字符串，10表示最大长度为10个字符
char(11) 定长字符串
timestamp 时间戳
datetime 时间
date日期
default 1 默认为1，默认值约束
comment ”也可以做注

假如学生表存在则删除学生表

drop table if exists tb_student;

创建学生表

主键(primary key) - 能够标识唯一一条记录的列

create table tb_student
(
stuid int not null comment '学号',
sname varchar(10) not null comment '姓名',
ssex bit default 1 comment '性别',
stel char(11) comment '联系电话',
sbirth date comment '出生日期',
primary key (stuid)
);

修改学生表

alter table tb_student add column saddr varchar(100);
alter table tb_student drop column stel;

插入学生记录

insert into tb_student values (1001, '王大锤', 1, '1990-2-12', '四川成都');
insert into tb_student (sname, stuid) values ('骆昊', 1002);
insert into tb_student (stuid, sname, ssex) values (1003, '李飘飘', 0);
insert into tb_student values
(1004, '张三丰', 1, '1940-12-3', '湖北武汉'),
(1005, '黄蓉', 0, '1975-3-25', '山东东营'),
(1006, '杨过', 1, '1987-1-19', '湖南长沙');

删除数据

delete from tb_student where stuid=1003;

更新数据

通常情况下更新或删除单条数据都是以ID字段(主键)作为条件

update tb_student set sbirth='1980-11-28', saddr='四川德阳'
where sname='小明';
update tb_student set saddr='四川绵阳'
where stuid=1004 or stuid=1005 or stuid=1006;
update tb_student set saddr='四川绵阳'
where stuid in (1004, 1005, 1006);


创建课程表(课程编号/课程名称/学分)

create table tb_course
(
courseid int not null,
cname varchar(20) not null,
ccredit int not null,
primary key (courseid)
);

添加课程表数据

insert into tb_course (courseid, cname, ccredit) values
(1111, 'Python程序设计', 4),
(2222, 'HTML程序设计', 2),
(3333, 'Linux操作系统', 1),
(4444, '数据库基础', 1);

创建人表格

create table tb_person
(
personid int not null auto_increment,
pname varchar(20) not null,
pbirth date,
primary key (personid)
);

创建人的属性(id)表格

create table tb_idcard
(
cardid char(18) not null,
cpolice varchar(20) not null,
cexpire date not null,
pid int not null,
primary key (cardid)
);

外键约束

alter table tb_idcard add constraint fk_idcard_pid
foreign key (pid) references tb_person (personid);

唯一性约束

alter table tb_idcard add constraint uk_idcard_pid
unique (pid);

插入人表格内容

insert into tb_person (pname, pbirth) values
('小明', '1980-11-28'),
('王大锤', '1990-01-01');

插入人的属性表格内容

insert into tb_idcard values
('510622198011288899', '成都金牛区*局', '2030-01-10', 1),
('110220199001019876', '北京朝阳区*局', '2022-05-12', 2);

创建人和人属性表格的关联性表格

create table tb_account
(
accid char(16) not null,
balance decimal(16,2) default 0.0,
pid int not null,
primary key (accid)
);

添加外键约束

alter table tb_account add constraint fk_account_pid
foreign key (pid) references tb_person (personid);

在第三方表格插入内容

insert into tb_account values
('1111222233334444', 99999.99, 1),
('4444333322221111', 100.0, 1);

insert into tb_account values
('1111222233335555', 99.99, 2),
('4444333322221234', -100.0, 2),
('4444333322224321', -200.0, 2);

复合主键(多个列合在一起作为主键)
实际开发中一般都不用复合主键因为可能导致各种麻烦

create table tb_sc
(
scid int not null auto_increment,
sid int not null,
cid int not null,
score float,
primary key (scid)
);

alter table tb_sc add constraint fk_sc_sid
foreign key (sid) references tb_student (stuid);

alter table tb_sc add constraint fk_sc_cid
foreign key (cid) references tb_course (courseid);

insert into tb_sc (sid, cid, score) values
(1001, 1111, 90),
(1001, 2222, 80),
(1002, 1111, 60),
(1002, 3333, 95),
(1002, 4444, 78),
(1005, 3333, 63);

例子1: 丈夫和妻子 一对一

创建丈夫表格

create table tb_husband
(
husid int not null auto_increment,
hname varchar(20) not null,
wifeid int,
primary key (husid)
);

创建妻子表格

create table tb_wife
(
wifeid int not null auto_increment,
wname varchar(20) not null,
primary key (wifeid)
);

添加丈夫与妻子的外键约束

alter table tb_husband add constraint fk_husband_wifeid
foreign key (wifeid) references tb_wife (wifeid);

添加丈夫与妻子的唯一约束

alter table tb_husband add constraint uk_husband_wifeid
unique (wifeid);

参照完整性

insert into tb_husband (hname) values ('王大锤');
insert into tb_husband (hname) values ('郭靖');
insert into tb_wife (wname) values ('柳岩');
update tb_husband set wifeid=1 where husid=1;

例子2: 用户和订单 一对多

创建用户表格

create table tb_user
(
username varchar(20) not null,
userpass varchar(20) not null,
nickname varchar(50) not null,
email varchar(255),
primary key (username)
);

创建订单表格

create table tb_order
(
orderid char(12) not null,
makedate datetime not null,
userid varchar(20) not null,
primary key (orderid)
);

添加用户和订单的外键约束

alter table tb_order add constraint fk_order_userid
foreign key (userid) references tb_user (username) on delete restrict;

on delete set null
on update cascade

添加用户表格内容

insert into tb_user (username, userpass, nickname) values
('jackfrued', '123123', '骆昊'),
('hellokitty', '123456', '王大锤');
insert into tb_order values
('112233445566', now(), 'jackfrued'),
('112233445567', now(), 'jackfrued'),
('223344556677', now(), 'hellokitty');

例子3: 读者和图书 多对多

创建读者表格

create table tb_reader
(
readerid int not null,
rname varchar(20) not null,
rtel char(11) not null,
remail varchar(255)
);

添加读者的主键

alter table tb_reader add constraint pk_reader_readerid
primary key (readerid);

创建查询图书的表格

create table tb_record
(
recid int not null auto_increment,
rid int not null,
bid int not null,
borrowdate datetime not null,
returndate datetime,
primary key (recid)
);

将两个表格作外键约束

alter table tb_record add constraint fk_record_rid
foreign key (rid) references tb_reader (readerid);

外键约束: 保证参照完整性

alter table tb_record add constraint fk_record_bid
foreign key (bid) references tb_book (bookid);

检查约束: 保证数据的有效性(域完整性)

alter table tb_record add constraint ck_record_returndate
check (returndate > borrowdate);