约束
1、主键约束
亦成为主码,关键字:primary key,设置了主键约束的列的数据要求:不能为空,不允许重复。
2、自增长策略
针对主键,不需要手动给值,自动的值是当前表中该列最大值+1
关键字:auto_increment
自增长的特点:
1)只有整型数据列才能设置自增长
2)只有主键才会设置自增长
3)自增长列在插入数据时不用给值
4)初始值为1,增量为1
5)默认的存储引擎下,自增列的数据一旦被使用就不会再出现
3、唯一约束
限定某列值不能重复,唯一约束允许值为空。通常应用于电话号码、邮箱、账号...
关键字:unique
4、非空约束
要求某列值必填,不能为空
关键字:not null
5、默认约束
设置默认约束的列,如果不给值就使用默认值来填充
关键字:default
6、外键约束
外键约束用来在两个表的数据之间建立关系。
关键字;foreign key references
表关系
1、一对一:one-to-one 例如:丈夫-妻子
外键可以建立在关系中任意一张表上面
#将外键加在妻子对应的表中,有丈夫的情况下才能有妻子,并且在妻子存在的情况下不能删除丈夫
CREATE TABLE husband(
hid INT(6) PRIMARY KEY AUTO_INCREMENT,
hname VATCHAR(20) NOT NULL
);
CREATE TABLE wife(
wid INT(6) PRIMARY KEY AUTO_INCREMENT,
wname VATCHAR(20) NOT NULL,
FOREIGN KEY(wid) REFERENCES husband(hid)
);
2、一对多/多对一:one-to-many 例如:班级-学生 用户-订单
外键建立在多的那张表上面
#创建班级表
CREATE TABLE class(
cid INT(6) PRIMARY KEY AUTO_INCREMENT,
cname VATCHAR(20) NOT NULL
);
#创建学生表
CREATE TABLE student(
sid INT(6) PRIMATY KEY AUTO_INCREMENT,
sname VATCHAR(20) NOT NULL,
cid INT(6),
FOREIGN KEY(cid) REFERENCE class(cid)
);
#先插入班级才能添加学生
INSERT INTO class(cname) VALUES('软件班');
INSERT INTO student(sname,cid) VALUES('大毛',1);
#删除某班级时先要保证这个班级里没有学生了
3、多对多:mang-to-mang 例如:学生-课程 商品-订单
外键建立在中间表上
#创建课程表
CREATE TABLE course(
courseid INT(6) PRIMARY KEY AUTO_INCREMENT,
coursename VARCHAR(20) NOT NULL
);
#创建学生表
CREATE TABLE student(
sid INT(6) PRIMATY KEY AUTO_INCREMENT,
sname VATCHAR(20) NOT NULL,
cid INT(6),
FOREIGN KEY(cid) REFERENCE class(cid)
);
#创建中间表
VREATE TABLE sc(
scid INT(6) PRIMARY KEY AUTO_INCREMENT,
sid INT(6),
courseid INT(6),
FOREIGN KEY(sid) REFERENCE student(sid),
FOREIGN KEY(courseid) REFERENCE course(courseid)
);
#必须先创建有某号课程和某号学生,才能在中间表中插入该学生和该课程
连接查询
定义:通过指定的连接条件同时获取多张表的数据。
1、内连接
1)隐式内连接:将连接条件直接拼接到where关键字后面
语法:select 查询的内容 from 表1,表2,表3 where 连接条件 [and 筛选条件];
2)显示内连接:通过inner join on 去查询,将连接条件写在on后面
语法:select 查询内容 from 表1[inner] join 表2 on 连接条件 [where 筛选条件];
2、外连接
以一张表为基表,查询出基表中的所有数据及连接表中能够与基表匹配的数据,如果匹配不上就用null来填充。
1)左外连接(以left左边的表为基表,右边的表为连接表)
语法:select 查询内容 from 表1 left 表2 on 连接条件 [where 筛选条件];
2)右外连接(以right右边的表为基表,左边的表为连接表)
语法:select 查询内容 from 表1 right join 表2 on 连接条件 [where 筛选条件];
聚合函数
主要对数据进行统计,并返回单个值。
聚合函数有:
max():最大值
min():最小值
sum():求和
count():统计数据条数
avg():平均值
分组查询
先对数据进行分组,再对这些分组后的信息进行汇总。分组查询时经常会用到聚合函数,达到对分组后数据的统计效果。
关键字:group by [having]
语法:select 查询内容 from 表名 group by 分组列 [having 筛选条件];
where和having在分组SQL中的区别:
1、having对分组后的数据进行筛选,有having那么一定会有group by,有group by不一定有having。where是在分组前进行筛选。
2、having后面可以写聚合函数,where后面不行
3、如果在一个SQL语句中两者同时出现,where先执行,having后执行
子查询(嵌套查询)
当一个查询得到的结果是另一个查询的条件时,就会用到子查询。
案例:
1、查询出比金坤同学大的学生的信息
步骤:先查询金坤同学的年龄,再根据查到的年龄进行条件查询
select sage from student where sname=’金坤’;
select * from student where sage>上一句查到的结果;
即select * from student where sage>(select sage from student where sname=’金坤’);
2、查询年龄最大的学生的信息
步骤:先查询出最大的年龄,再根据查出的最大年龄查询学生信息
select max(sage) from student;
select * from student where sage=上一句查出的结果;
即select * from student where sage=(select max(sage) from student);
子查询的另外一种写法,将前一个查询的结果当成一张表来处理:
select s.* from student s, (select max(sage) from student) max where s.sage=max.age;