简单的SQL语句的快速复习
语法的执行顺序
select 4 字段列表
from 1 表名列表
where 2 条件列表
group by 3 分组前过滤
having 分组后过滤
order by 5 排序字段列表
limit 6 分页参数
聚合函数
count 统计数量
max 最大值
min 最小值
avg 平均
sum 总和
分组查询使用例子
1.性别分组，统计数量
select gender ，count（*）from emp group by gender
2.性别分组 统计平均年龄
select age ，avg(age) from emp group by gender
3.查询年龄小于45的员工，并且按照工作地址分组，获取员工数量>=3的工作地址
首先
按工作地址分组然后获取年龄小于45的员工的地址信息的总数
select workaddress，count（*）from emp where age<45 group by workaddress
再分组完后进行过滤
having count（*）>=3
排序查询使用例子
语法：
select 字段列表 from 表名 order by 字段1 排序方式1，字段2，排序方式2；
排序方式：
1.ASC：升序（默认）
2.DESC：降序
例子
1.根据年龄进行排序，2.年龄相同，再入职日期降序排序
select age from emp order by ageasc,entrydate desc
多字段排序：第一个字段相同时再进行第二个字段排序
分页查询
语法
select 字段列表 from 表名 limit 起始索引，查询记录数；
select * from emp limit 0 10
函数
字符串函数
concat（s1,s2,s3）字符串的拼接
lower（str） 小写
upper（str）大写
lpad（str,n,pad）
左填充，用字符串pad对左边进行填充，达到n个字符串长度
rpad（str，n，pad）
右填充
trim（str）
去掉字符串头部和尾部的空格
substring（str，start，len）
返回字符串str从str位置起的len个长度的字符串
如
int类型不能补0，因为是整形但可以补1
数值函数
ceil（x） 向上取整
floor（x） 向下取整
mod（X,Y） 返回x/y的模
rand（）返回0-1内的随机数
round（X,Y）四舍五入，保留y位小数
日期函数
curdate() 日期
curtime() 时间
now() 现在
year（date） 获取指定date的年份
month（date） 获取指定date的月份
day（date） 日期
date-add（date，interval exprtype）
返回这个日期加上一个时间间隔后的时间值
datediff（date1，date2）
返回起始时间date1和结束时间date2之间的天数
流程函数
if（value，t，f）
true返回t
false返回f
ifnull（value1,value2）不空返回value1，空的话返回value2
case when then
case when [val]then [res1] else [defaulse] End
val为true则返回res1
否则返回default默认值
case [expr] when [val] then [res1] else [default] End
end是结束
当expr的值等于val时返回res1否则返回default
使用例子
select name,(case workaddress when'北京' then'一线'，when‘上海’，then‘一线’ end)as‘工作地址’
增删改查
添加数据
1.给指定字段添加数据 insert values
insert into 表名(字段1 ， 字段2) values（值1，值2）
2.给全部字段添加数据（不写出具体字段名）
insert into 表名 values(值1，值2)
3.批量添加数据
给特定字段
insert into 表名（字段1，字段2）values(值1，值2)（值1，值2）;
给全部字段
insert into 表名 values (值1，值2)， (值1，值2)， (值1，值2)；
更新与删除
修改数据：update set
update 表名 set 字段名1=值1，字段名2=值2......[where 条件]
不写条件where的话就是所有都执行
删除数据 delete
delete from 表名 [where 条件]
联合查询(union)
union查询，就是把多次的查询结果合并起来形成一个新的查询结果
select 字段列表 from 表a
union[all]
select 字段列表 from 表b
分别查询薪资>5000，年龄>50的员工
select *from emp where salary>5000
union all
select *from emp where age>50
但是结果会有重复的，为了去重
可以把all去
报错情况
select *from emp where salary>5000
union all
select name from emp where age>50
这个会发现报错
因为对于联合查询来说。字段表的列数和字段类型必须保持一致
子查询
子查询
又称为 嵌套查询
标量子查询
查询销售部的所有员工信息
1. select id from emp where name='销售部';
第一部查询出销售部id等于4
2.select *from emp where dept_id=4;
要两条指令，但我们想用一条搞定
select *from emp where dept_id=（select id from emp where name='销售部'）；
列子查询
常用操作符：
in,not in,any,some,all
1
select id from dept where name='销售部'or name='市场部';
查出的id是1和2
然后
select* from emp where dept_id in (1,2)
或者
select* from emp where dept_id in (select id from dept where name='销售部'or name='市场部');
2
查询比财务部的所有人工资都高的员工的 信息
a 查询所有财务部人员的工资
select id from dept where name='财务部';
select salary from emp where dept_id=3
b查询比财务部所有人工资都高的员工信息
select *from emp where salary> all( select salary from emp where dept_id=3)
3
查询比研发部其中任意一人工资都高的员工信息
因为是任意一人所以 没有all
行子查询
查询与‘张无忌’薪资以及领导都相同的员工的信息
a.查询张无忌的工资及其领导
select salary,managerid from emp where name='张无忌'
b. 查询员工
select *from emp where salary=12500 and mangerid =1;
或者
select *from emp where （salary，managerid)=(12500,1);
再或者
select *from emp where(salary,mangerid)=（select salary,managerid from emp where name='张无忌'）
表子查询
常用操作符 in
1.查询与‘路’和‘白’薪资以及职位相同的员工
select job,salary from emp where name='路'or name=‘白’
select * from emp where (job,salary) in(select job,salary from emp where name='路'or name=‘白’)
2.查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
select * from emp where entrydate>"2006-01-01"
把上面那个作为临时表
select e.*,d.* from(select * from emp where entrydate>"2006-01-01") e left join dept d on e.dept_id=d.id
多表联查
1.查询员工的姓名，年龄，职位，部门信息（隐式内连接）
表：emp dept
连接条件：emp.dept_id=dept.id
记得消除笛卡尔积
select e.name,e.age,e.job,d.name from emp e, dept d where e.dept_id=d.id;
2.查询年龄小于30岁的员工的姓名，年龄，职位，部门信息（显示内连接）
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id=d.id where e.age<30
3.查询拥有员工的部门id和部门名称
求取员工表和部门表之间的交集用内连接
select d.id,d.name from emp e,dept d where e.dept_id=d.id
此时会有多个重复的部门，因为他是按照员工数量来的
去重复用 distinct
select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id
4.查询所有年龄大于40的员工，及其归属部门的名称；如果员工没有分配部门也要显示出来
要用外连接
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where e.age>40
5.查询所有员工的工资等级
表:emp salarygrade
连接条件：emp.salary >=salagrade.losal and emp.salary<=salagrade.hisal
select e.*,s.grade emp e,salagrade s where e.salary>=s.losal and e.salary <=s.hisal
第二种写法:
select e.*,s.grade emp e,salagrade s where e.salary between s.losal and s.hisal
6.查询 研发部 所有员工的信息以及工资等级
涉及到的表：emp dept salgrade
连接条件:
emp.salary between s.losal and s.hisal
emp.dept_id=dept.id
查询条件 dept.name='研发部'
select e.*,s.grade from emp e ,dept d,salgrade s where e.dept_id=d.id and ( emp.salary between s.losal and s.hisal)and d.name='研发部'
7.查询研发部员工的平均工资
表 emp dept
select avg(e.salary) from emp e, dept d where e.dept_id=d.id and e.name='研发部'
8.查询工资比‘灭绝’高的员工信息
select * from emp where salary>(select salary from emp where name='灭绝')
查询灭绝的薪资
select salary from emp where e.name='灭绝'
9.查询比平均薪资高的员工信息
select avg(salary) from emp
select * from emp where salary>(select avg(salary) from emp)
10.查询低于 本部门 平均薪资的员工
a.查询指定部门的平均薪资
select avg(e.salary) from emp e where e.dept_id=1
select avg(e.salary) from emp e where e.dept_id=2
b.
select *from emp e2 where salary<(select avg(e.salary) from emp e where e.dept_id=e2.dept_id)
保证平均下来的薪资是同一个部门的
11.查询所有的部门信息，并统计部门的员工人数
a.查询信息
select id,name from dept
b.查询指定部门的人数
select count(*) from emp where dept_id=1
最终
select d.id ,d.name (select count(*) from emp e where e.dept_id=id)'人数' from dept d;
12.查询所有学生的选课情况，展示出学生的名称，学号，课程名称
表：student ,course,student_course
连接条件：student.id=student_course.studentid,course.id=student_course.courseid
select s.name ,s.no,c.name from student s,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id