简单的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