MySQL聚合函数、分组查询、数据完整性、域完整性、引用完整性（2022-02-22）

-- 聚合函数
-- sum()统计和
-- couint() 统计个数
-- avg() 平均值
-- max() 最大值
-- min() 最小值

-- 查询表
select * from exam;

-- 统计学生英语成绩总和
select sum(english) '英语总和' from exam;

-- 统计学生英语成绩总合 和 数学成绩总和
select sum(english) '英语成绩',sum(math) '数学成绩' from exam;

-- 查询姓王的学生的英语总成绩
select sum(english) '姓王的同学的英语总成绩' from exam where uname like '王%';

-- 查询所有学生的各科总成绩
select sum(chinese) as '语文总成绩',sum(math) as '数学总成绩',sum(english) as '英语总成绩' from exam;
select sum(chinese)+sum(math)+sum(english) as '各科总成绩' from exam;
select sum(chinese+math+english) as '各科总成绩' from exam;

-- 查询王涛的成绩总和
select sum(chinese+math+english) as '王涛总成绩' from exam where uname='王涛';

select uname,chinese,math,english,sum(chinese+math+english) as '王涛总成绩' from exam where uname='王涛';

-- NULL
select uname as '姓名',sum(english+math+chinese) as '三科总成绩' from exam where uname='杜申';

-- IFNULL(NULL,0)
select uname ,english+math+IFNULL(chinese,0) from exam where uname='王涛';

-- 统计学生的总数
select COUNT(*) as '学生个数' from exam;

select COUNT(id) from exam;

-- 统计英语成绩的学生个数
select COUNT(english) from exam;

-- MAX(expr) MIN(expr) AVG(expr)

-- 求数学成绩的最高值
-- 求语文成绩的最低值
-- 求英语成绩的平均值
-- 求语文成绩的平均值

select MAX(math) from exam;

SELECT MIN(chinese) from exam;

select SUM(english)/COUNT(english) from exam;

select AVG(chinese) from exam;

-- 分组查询
-- 前期准备

drop table emp;
create table emp(
        empno int,
        ename varchar(5),
        job varchar(100),
        mgr int,
        hiredte date,
        sal double(7,2),
        comm double(7,2),
        deptno int 
)

insert into emp VALUES(7365,'jack','doc',7902,'1998-06-05',9898,3000,20);
insert into emp VALUES(7366,'rose','doc',7902,'1995-10-05',6858,3000,20);
insert into emp VALUES(7367,'tom','doc',9800,'1996-12-06',7586,7895,20);
insert into emp VALUES(7368,'karry','doc',8007,'1999-06-01',9456,5853,20);
insert into emp VALUES(7369,'bob','doc',8324,'1995-06-05',8524,3686,20);
insert into emp VALUES(7255,'viya','doc',8907,'1980-10-31',5748,3000,20);
insert into emp VALUES(7263,'hou','doc',7546,'1998-02-25',8765,3000,20);
insert into emp VALUES(7156,'jay','doc',7879,'1997-08-15',5786,3000,20);
insert into emp VALUES(7258,'jj','doc',7998,'1996-03-05',8678,3000,20);
insert into emp VALUES(7239,'kk','doc',9782,'1997-02-05',9564,3000,20);
insert into emp VALUES(7168,'mm','doc',9856,'1988-06-05',5788,3000,20);
insert into emp VALUES(7400,'hh','doc',6532,'1994-06-22',10155,3000,20);
insert into emp VALUES(7287,'aa','doc',8989,'1992-10-22',5868,3000,20);
insert into emp VALUES(7324,'ss','doc',7865,'1998-06-15',8775,3000,20);
insert into emp VALUES(7321,'xcc','doc',7945,'1998-3-31',8956,3000,20);

-- 分组查询 group by
create table orderitem(
        id int PRIMARY KEY auto_increment,
        product VARCHAR(50),
        price double
)

insert into orderitem values(null,'洗衣机',2638);
insert into orderitem values(null,'电视机',3632);
insert into orderitem values(null,'洗碗机',2438);
insert into orderitem values(null,'电冰箱',1965);
insert into orderitem values(null,'微波炉',999);
insert into orderitem values(null,'蒸柜机',2350);
insert into orderitem values(null,'空调',2523);
insert into orderitem values(null,'空调',4258);
insert into orderitem values(null,'电视机',1958);

-- 按照商品的名称统计每类商品所购买的个数
select product,COUNT(*) from orderitem GROUP BY product;

-- 按照 商品名称进行统计，每类商品所花费的总金额
select product, SUM(price) from orderitem GROUP BY product;

-- 需求 按照商品名称统计每类商品金额大于2000的商品
select product,price from orderitem where price > 2000 GROUP BY product;

-- 按照商品名称进行统计，统计出商品的花费总金额大于5000的商品
select product,sum(price) from orderitem where price > 5000 GROUP BY product;

-- 按照商品名称进行统计，统计出商品花费总金额大于5000的商品
select product,SUM(price) from orderitem group by product HAVING sum(price) > 5000 ;

-- 分组过滤使用having关键字 where
-- having是在分组后对数据进行过滤
-- where先对条件进行判断，然后在进行分组过滤
-- having后面可以使用聚合函数，可以跟非聚合函数吗？
-- where后面能跟聚合函数，只能跟条件判断

-- 分页 LIMIT
-- 查询emp员工表
select * from emp;

-- 查询5条记录，起始从0开始，代表的是从第一行开始查
-- limit 第一个参数代表的是从多少条件开始查，第二个参数代表的是每次查询多少条记录
select * from emp limit 0,5;

-- 查询10行记录， 起始从第三行开始查
select * from emp limit 3,10;

-- 一页记录是5条，希望展示2页
select * from emp limit 0,5;

select * from emp limit 6,5;

select * from emp ;
set @currentPage=2;-- 当前页一共需要多少页
set @pageSize=5;-- 每一页显示的条数

set @currentPage=(currentPage-1)*pageSize;

select * from emp limit @@currentPage,@pageSize;
select * from emp orderr by limit (curPage-1)*pageSize,pageSize;

  
-- 主键数据唯一切不能为空
-- 第一种添加主键的方式
create table student(
        id int PRIMARY KEY,
        uname VARCHAR(50)
)

-- 第二种添加主键的方式
create table students(
        id int,
        uname varchar(40)
);
-- 添加主键
alter table students add PRIMARY KEY(id);

CREATE TABLE studentss(
        id int PRIMARY KEY,
        uname VARCHAR(50) UNIQUE-- 唯一键约束 unique
)

CREATE TABLE studentsss(
        id int PRIMARY KEY auto_increment,-- 自动增长
        uname VARCHAR(29) UNIQUE-- 唯一键约束 unique
)

-- 非空约束
create table tea(
    
        id int PRIMARY KEY auto_increment,
        uname varchar(39) not NULL,
        sex varchar(2)
)

insert into tea values(null,"",'男');
-- insert into tea values(null,NULL,'男');

create table tea1(
    
        id int PRIMARY KEY auto_increment,
        uname varchar(39) not NULL,
        sex varchar(2) default '男'
)

-- 默认约束 default
insert into tea1 values(null,"",'男');
insert into tea1 values(null,"aaa",DEFAULT);

-- 第一种创建外键的方式
-- 创建一个部门表
create table dept(
        did int PRIMARY KEY auto_increment,
        dname varchar(50)
)

insert into dept values(null,'市场部');
insert into dept values(null,'教学部');
insert into dept values(null,'教务部');
insert into dept values(null,'渠道部');
insert into dept values(null,'咨询部');

-- 创建一个员工表
create table employee(
        eid int PRIMARY KEY auto_increment,
        ename varchar(20),
        salary double,
        birthday date,
        sex varchar(2),
        dno int
)

insert into employee values(null,'张三',8000,'1999-06-02','男',2);
insert into employee values(null,'李四',5000,'1988-12-23','男',1);
insert into employee values(null,'王五',6000,'1996-04-04','女',5);

-- 在员工表上添加外键
alter table employee add foreign key(dno) REFERENCES dept(did);

-- 第二种创建外键的方式
create table dept1(
        did int PRIMARY KEY auto_increment,
        dname varchar(50)
)

insert into dept1 values(null,'市场部');
insert into dept1 values(null,'教学部');
insert into dept1 values(null,'教务部');
insert into dept1 values(null,'渠道部');
insert into dept1 values(null,'咨询部');

-- 创建一个员工表
create table employee1(
        eid int PRIMARY KEY auto_increment,
        ename varchar(20),
        salary double,
        birthday date,
        sex varchar(2),
        dno int,
        CONSTRAINT fk_dno_did FOREIGN KEY(dno) REFERENCES dept1(did)-- 第二种创建外键的方式
)

insert into employee1 values(null,'张三',8000,'1999-06-02','男',2);
insert into employee1 values(null,'李四',5000,'1988-12-23','男',1);
insert into employee1 values(null,'王五',6000,'1996-04-04','女',5);