mysql 单表应用与多表联查

一.聚合函数

max(),min(),sum(),avg()

#聚合函数max min sum avg count
     #最大值     最小值   求和    求平均数  
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp 
#求总记录数
SELECT COUNT(*) FROM emp#低效
SELECT COUNT(1) FROM emp#高效
SELECT COUNT(sal) FROM emp#了解,不推荐
SELECT COUNT(comm) FROM emp#按照字段统计时不统计null
#练习1:统计名字里包含a的总记录数
SELECT COUNT(1) FROM emp WHERE ename LIKE '%a%'
#练习2:统计19年入职的员工里的最高薪
SELECT MAX(sal) FROM emp WHERE YEAR(hiredate)=2019
#练习3:统计2号部门的平均薪资
SELECT AVG(sal) FROM emp WHERE deptno=2
#练习4:统计2号部门一年的工资支出总数
SELECT SUM(sal)*12 FROM emp WHERE deptno=2

二.分组

1.group by

#分组:使用group by 
#练习1:统计每个部门的平均薪资
SELECT AVG(sal),deptno FROM emp 
GROUP BY deptno #按照部门分组
#练习2:统计每个部门的最高薪资
SELECT MAX(sal),deptno FROM emp 
#口诀1:查询结果中出现了混合列,包括着聚合列和非聚合列,必须分组
GROUP BY deptno
#口诀2:通常按照非聚合列分组
#练习3:统计每年入职的总人数
SELECT YEAR(hiredate),COUNT(1) FROM emp 
GROUP BY YEAR(hiredate)
#练习4:统计每个岗位的平均薪资
SELECT AVG(sal),job FROM emp
GROUP BY job

2.having

把分组后的数据还想继续过滤,使用group by的固定搭配having

#having
#练习5:统计每个岗位的平均薪资,而且只要>=10000的
SELECT AVG(sal) a,job FROM emp
#where AVG(sal)>=10000 
#注意:::where里不能用别名,也不能出现聚合函数
GROUP BY job  #按照非聚合列分组
HAVING a>=10000 #分组后的过滤,a是别名
#练习6:统计每年入职的人数,而且只要19年的
SELECT YEAR(hiredate) ,COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019 
GROUP BY YEAR(hiredate) #分组
#having a=2019 #分组后的过滤
# where比having 高效,因为执行时机要早一些

三.事务

窗口1:

mysql> use  xiaojiejie; #使用数据库,准备往表里插入数据
mysql> start transaction; #开启事务start transaction
mysql> insert into book values(2,'西游记',50);#多条执行增删改的SQL
mysql> commit;#提交事务commit,不提交的话别人查不到的,没有持久影响

窗口2:

use xiaojiejie;
select * from book;#窗口1不执行commit,这里永远查不到的

四.多表联查

1.准备表和数据

#多表联查:
#1.课程表
CREATE TABLE courses( 
cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL, 
PRIMARY KEY (cno) #单独设置主键
);
#2.得分表
CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno) #联合主键,了解
);
#3.学生表
CREATE TABLE students( 
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)#主键
);
#4.老师表
CREATE TABLE teachers( 
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
);
#5.插入数据
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,