进行sql语句进行查询,使用聚合函数,sql进行分组查询,添加主键,外键,唯一,非空约束
表之间的关系以及外键约束
一.DQL高级查询
数据准备:
======================================================================
--创建数据库
CREATE DATABASE day19;
--使用数据库
USE day19;
--创建表
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT
)
--插入记录
INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES
(1,'马云',58,'男','杭州',56,90),
(2,'胖东来',56,'男','漯河',50,90),
(3,'平煤',32,'男','郑州',56,90),
(4,'董明珠',30,'男','株洲',66,79),
(5,'马景涛',48,'男','香港',76,67),
(6,'杨惠妍',34,'女','惠州',86,56),
(7,'马化腾',73,'女','深证',96,83),
(8,'刘得花',29,'男','香港',46,91),
(9,'马明哲',65,'男','深证',57,70),
(10,'猪八戒',26,'男','高老庄',58,80),
(11,'白骨精',38,'女','白虎岭',58,92),
(12,'唐僧',56,'男','长安',59,90),
(13,'沙僧',35,'男','流沙河',36,90),
(14,'孙悟空',78,'男','花果洞',96,90);
1.1.排序:
1. 语法:select ... from 表名 order by 排序列 [asc|desc], 排序列 [asc|dex]asc :升序 (默认值)desc :降序2. 注意:多字段排序,后面的排序结果是在前面排序的基础之上
# 排序-- 查询所有数据 , 使用年龄降序排序SELECT * FROM student ORDER BY age DESC ;-- 查询所有数据 , 在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序SELECT * FROM student ORDER BY age DESC ,math DESC ;
1.2聚合函数
* 语法:count( 列名 ) :统计一列个数max( 列名 ) :求出一列的最大值min( 列名 ) :求出一列的最小值sum( 列名 ) :对一列求和avg( 列名 ) :求出一列的平均值
# 聚合函数
-- 查询学生总数(null值处理)
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
SELECT COUNT(*) FROM student;
-- 查询年龄大于40的总数
-- 1.1 查询年龄大于40
SELECT * FROM student WHERE age >40;
-- 1.2 总数
SELECT COUNT(*) FROM student WHERE age >40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;
1.3分组
作用:对一列数据进行分组,相同的内容分为一组,通常与聚合函数一起使用,完成统计工作
1. 语法:
select 分组列 from 表名 group by 分组列 having 分组后的过滤条件;
2. where和having区别
where在分组前进行条件过滤,不支持聚合函数
having在分组后今天条件过滤,支持聚合函数
-- 统计男生和女生各有多少人
-- select count(*) from student where sex ='女';
# 分组
-- 按性别分组
SELECT sex FROM student GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(*) FROM student GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
-- 1.1 查询年龄大于25岁的人
SELECT * FROM student WHERE age >25;
-- 1.2 按性别分组
SELECT sex FROM student WHERE age >25 GROUP BY sex;
-- 1.3 统计每组的人数
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age >25 AND COUNT(*) >2 GROUP BY sex; -- 错误
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex HAVING COUNT(*)>2; -- 正确
1.4分页
1. 语法:
select ... from 表名 limit 开始索引,每页显示个数;
2. 索引特点:
所以是从0开始,0也是默认值,可以省略
3. 分页索引公式:
索引 = (当前页-1) × 每页个数
# 分页
-- 查询学生表中数据,显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 查询学生表中数据,从第三条开始显示,显示6条
SELECT * FROM student LIMIT 2,6;
-- 模拟百度分页,一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;
总结:
sql语句执行的顺序问题:
select * from 表名 where 条件 group by 分组 having 分组后条件 order by 排序 limit 分页;
二.数据库约束
2.1 概念
对表中的数据进行限定,保证数据的正确性,有效性,完整性
分类:
1. primary key:主键约束【掌握】 要求表中有一个字段 唯一 且 非空,通常我们使用id作为主键
2. unique:唯一约束
3. not null:非空约束
4. default:默认值
5. foreign key:外键约束
2.2实现
主键约束
作用:限定某一列的值非空且唯一, 主键就是表中记录的唯一标识。
1. 设置主键约束
1)创建表
create table 表名(
id int primary key,
...
...
);
2)已有表
alter tabe 表名 add primary key(id);
2. 特点:
一张表只能有一个主键约束,但是我们可以设置联合主键(多个字段)
3. 自增器
1)创建表【掌握】
create table 表名(
id int priamry key auto_increment,
...
...
);
2)特点:自增器起始值为1,可以手动指定
alter table 表名 auto_increment=起始值;
4. 删除主键约束
语法:
alter table 表名 drop primary key;
1)先移出自增器
alter table stu3 modify id int;
2)才能删除主键约束
alter table stu3 drop primary key;
解释:因为只有主键约束才有意义设置自增器...(保证唯一性....)
-- 主键约束
-- 给student表添加主键约束
ALTER TABLE student ADD PRIMARY KEY(id);
-- 创建表时指定主键约束
CREATE TABLE stu1(
id INT PRIMARY KEY,
`name` VARCHAR(32)
);
-- 插入数据测试
INSERT INTO stu1 VALUES(1,'jack');
-- Duplicate entry '1' for key 'PRIMARY' 错误:主键不能重复
INSERT INTO stu1 VALUES(1,'lucy');
-- Column 'id' cannot be null 错误:主键不能为空
INSERT INTO stu1 VALUES(NULL,'lucy');
-- 我想让name字段,也作为主键使用...
-- Multiple primary key defined -- 错误:主键被重复定义了
ALTER TABLE stu1 ADD PRIMARY KEY(`name`);
-- 联合主键(主键字段完全相同,在进行约束的限定)
CREATE TABLE stu2(
id INT ,
`name` VARCHAR(32),
PRIMARY KEY(id,`name`)
);
-- 插入数据测试
INSERT INTO stu2 VALUES(1,'jack');
INSERT INTO stu2 VALUES(1,'lucy');
-- Duplicate entry '1-lucy' for key 'PRIMARY' 错误
INSERT INTO stu2 VALUES(1,'lucy');
-- 自增器
CREATE TABLE stu3(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
-- 插入数据测试
INSERT INTO stu3 VALUES(1,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(3,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(10,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
-- 设置自增器起始值
ALTER TABLE stu3 AUTO_INCREMENT=1000;
INSERT INTO stu3 VALUES(NULL,'jack');
-- delete(橡皮擦) 和 truncat(撕纸) 区别
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
-- 1)先移出自增器
ALTER TABLE stu3 MODIFY id INT;
-- 2)才能删除主键约束
ALTER TABLE stu3 DROP PRIMARY KEY;
唯一约束
作用:限定某一列的值不能重复,可以出现多个null
1. 创建表时设置唯一约束
create table 表名(
列名 数据类型 unique,
...
...
);
-- 唯一约束
CREATE TABLE stu4(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) UNIQUE
);
INSERT INTO stu4 VALUES(1,'jack');
-- Duplicate entry 'jack' for key 'name' 错误:名称重复了
INSERT INTO stu4 VALUES(2,'jack');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);
非空约束
作用:限定某一列的值不能为null
1. 创建表时设置非空约束
create table 表名(
列名 数据类型 not null,-- 非空约束
列名 数据类型 unique not null,-- (唯一+非空)
);
疑问:唯一 + 非空 = 主键??
回答:不等于,主键约束一张表只能有一个,唯一+非空 设置多个
-- 唯一+非空
CREATE TABLE stu5(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) UNIQUE NOT NULL
);
INSERT INTO stu5 VALUES(1,'jack');
-- Column 'name' cannot be null 错误:名称不能为空
INSERT INTO stu5 VALUES(2,NULL);
默认值
作用:限定某一列的默认值,再没有指定的情况下所有列的默认值为null
1. 创建表设置默认值
create table 表名(
列名 数据类型 default 默认值,
...
...
);
-- 默认值
CREATE TABLE stu6(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32),
sex VARCHAR(5) DEFAULT '男'
);
INSERT INTO stu6(id,`name`) VALUES(1,'小张');
INSERT INTO stu6(id,`name`,sex) VALUES(2,'小刘','女');
-- 因为我们指定了默认值为男,你再插入null,会把默认值覆盖...
INSERT INTO stu6 VALUES(3,'小王',NULL);
三.表与表之间的关系
1概述:
现实生活中,实体与实体之间肯定是有关系的,我们在设计表的时候,那这种关系就应该体现在表与表之间的这种关系。
简称:关系型数据库
1. 一对多
应用场景:
班级和学生、部门和员工
解释:
一个班级下面有多名同学,多名同学属于某一个班级2. 多对多
应用场景:
老师和学生、学生和课程
解释:
一名老师可以教导多名学生,一名学生可以被多个老师教导3. 一对一
应用场景:
公民和身份证号、公司和注册地
解释:
一个公民只能有一个身份证号,一个身份证号对应一个公民
2.实现
一对多:
-- 创建新库
CREATE DATABASE daypro;
USE daypro;
-- 一对多
-- 班级表(主表)
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO class VALUES(1,'java一班');
INSERT INTO class VALUES(2,'java二班');
-- 学生表(从表)
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32),
class_id INT -- 外键字段
);
INSERT INTO student VALUES(1,'流川枫',1);
INSERT INTO student VALUES(2,'樱木花道',1);
INSERT INTO student VALUES(3,'大猩猩',2);
INSERT INTO student VALUES(4,'赤木晴子',2);
-- 通过班级找学生
SELECT * FROM student WHERE class_id =1;
-- 通过学生找班级
SELECT * FROM class WHERE id = 2;
-- 给学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT class_id_fk FOREIGN KEY(class_id) REFERENCES class(id);
-- 删除学生表的外键约束
ALTER TABLE student DROP FOREIGN KEY class_id_fk;
多对多:
-- 多对多
-- 课程表(主表)
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'ui');
INSERT INTO course VALUES(3,'美容美发');
INSERT INTO course VALUES(4,'挖掘机');
-- 中间表(从表)
CREATE TABLE sc(
s_id INT,
c_id INT,
PRIMARY KEY(s_id,c_id)
);
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,2);
INSERT INTO sc VALUES(2,1);
INSERT INTO sc VALUES(2,3);
-- 联合主键,可以帮我们校验重复选修问题
INSERT INTO sc VALUES(1,1);
-- 给中间表增加外键约束
ALTER TABLE sc ADD CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT c_id_fk FOREIGN KEY(c_id) REFERENCES course(id);
-- 流川枫不能选修,不存在的课程
INSERT INTO sc VALUES(1,6);
一对一:
-- 一对一
-- 公司表
CREATE TABLE company(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO company VALUES(1,'拼多多');
INSERT INTO company VALUES(2,'京东');
-- 地址表
CREATE TABLE address(
id INT PRIMARY KEY AUTO_INCREMENT, -- 同时也作为外键
`name` VARCHAR(32),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES company(id)
);
INSERT INTO address VALUES(1,'上海');
INSERT INTO address VALUES(2,'北京');
四.外键约束:
作用:限定二张表有关系的数据,保证数据的正确性、有效性和完整性
1. 在从表中添加外键约束
1)创建表
create table 表名(
列名 数据类型,
[constraint] [约束名] foreign key(外键列) references 主表(主键)
);
2)已有表
alter table 表名 add [constraint] [约束名] foreign key(外键列) references 主表(主键);
2. 外键约束特点
1)主表不能删除从表已引用的数据
2)从表不能添加主表未拥有的数据
3)先添加主表数据再添加从表数据
4)先删除从表数据再删除主表数据
5)外键约束允许为空但不能是错的
3. 删除外键约束
alter table 表名 drop foreign key 约束名;
其实在企业开发中,我们很少用外键约束,一般也就是传统的 项目中我们需要用外键约束,其他项目基本不用。
案例:
表结构:
--创建数据库travel
CREATE DATABASE travel;
--创建类别表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
CODE VARCHAR(16)
)
--创建线路表
CREATE TABLE lineroute(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(64),
CODE VARCHAR(16),
category_code INT
)
--给类别,线路表增加数据
INSERT INTO category VALUES(1,'中国',1);
INSERT INTO category VALUES(2,'美国',2);
INSERT INTO category VALUES(3,'英国',3);
INSERT INTO lineroute VALUES(1,'中国',1,1);
INSERT INTO lineroute VALUES(2,'美国1',2,2);
INSERT INTO lineroute VALUES(3,'美国2',3,2);
--查询线路表
SELECT * FROM lineroute;
--给线路表添加外键
ALTER TABLE lineroute ADD CONSTRAINT category_code_fk FOREIGN KEY(category_code) REFERENCES category(id);
--创建用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
age VARCHAR(16) DEFAULT '0',
sex VARCHAR(16) DEFAULT '男',
phone VARCHAR(32) UNIQUE NOT NULL
)
--插入用户数据
INSERT INTO USER VALUES(1,'张三','25','女','18900126456');
SELECT * FROM USER;
INSERT INTO USER (NAME,age,sex ,phone) VALUES('李三','23','男','18935126456'),('三丰','55','女','18398764567'),('三胖','21','男','12345678909');
SELECT * FROM USER;
SELECT * FROM lineroute;
SELECT * FROM category;
//关于用户表与线路表多对多的关系,=创建中间表,收藏表
CREATE TABLE favorite(
lineroute_id INT,
user_id INT,
PRIMARY KEY(lineroute_id,user_id)
)
--添加数据
INSERT INTO favorite VALUES(1,5);
INSERT INTO favorite VALUES(1,6);
INSERT INTO favorite VALUES(1,7);
INSERT INTO favorite VALUES(2,6);
INSERT INTO favorite VALUES(3,7);
SELECT * FROM favorite;
--添加外键约束
ALTER TABLE favorite ADD CONSTRAINT f_id_fk FOREIGN KEY(lineroute_id) REFERENCES lineroute(id);
ALTER TABLE favorite ADD CONSTRAINT u_id_fk FOREIGN KEY(user_id) REFERENCES USER(id);