复习
day01
数据库相关:
create database db4 character set utf8;
show databases;
show create database db4;
drop database db4;
use db4;
表相关:
create table t1 (字段);
show tables;
desc t1;
show create table t1;
create table t2 (字段)engine=innodb/myisam charset=utf8;
修改:
rename table t2 to t3;
alter table t2 add age int;
change age age2 int;
modify age double after id;
drop age;
drop table t2;
数据相关:
insert into t1 values (1,2,3),(1,2,3)
insert into t1 (name1,name2) values (1,2);
update t1 set age=18 where id=1;
delete from t1 where id=1;
select * from t1;
select name1,name2 from t1 where id<18;
day02:
主键 primary key 唯一 非空
auto_increment
not null
comment
‘’ ``
事务 set autocommit=0;
show variables like '%autocommit%'
begin commit rollback
数据库分类
DDL:数据定义语言 craete alter drop
DML: 数据操作语言 insert update delete select
DQL select
TCL:事务控制语言 begin commit rollback
DCL:数据控制语言 控制表的权限
数据类型
整数 int bitint
浮点数 double decimal(m,d)
日期 date time datatime timestamp
字符串 char varchar text
其他
day03
别名
去重 distinct
where
and or
in(23,22,34)
between x and y
like _ 单个未知 %多个未知
order by 字段名1,字段名2
limit begin,count 16,8
concat(s1,s2,s3)
数值计算
+ - * / % mod(7,2)
日期:
now() curdate() curtime()
date(now()) time(now())
extract(year from now())
month day hour minute second
date_format(now(),'') %Y y m c d H h i s
str_to_date('','') 把字符串时间转成date
age = ifnull(x,y);
聚合函数
sum() avg() count(*) max() min()
字符串函数
char_length()
instr(x,y)
locate(x,y)
insert(str,begin,length,newStr)
lower() upper()
left() right()
substring(s,5,6)
trim()
repeat ('dd',3)
replace(str,'a','b')
reverse('abc')
数学
floor()
round(num,2)
truncate
0,2 +3
rand()*3 + 3
GROUP BY 语句
-查询hero表中男女的平均年龄
select sex,avg(age) from hero
group by sex;
-查询hero表 每类型的平均年龄
select type,avg(age) from hero
group by type;
-查询 每种类型中 价格最贵的
select type,max(money) from hero
group by type;
-查询每种类型的人数
select type,count(*) from hero
group by type;
-查询 每种性别 平均价格,最大年龄和最便宜的价格 是多少
select sex,avg(money) 平均价格,max(age) 最大年龄,min(money) 最便宜价格 from hero
group by sex;
-查询 每种类型 年龄在30岁以下的人数是多少
select type,count(*) from hero
where age<30
group by type;
-查询 每种类型下男女各多少人
select type,sex,count(*) from hero
group by type,sex;
-- 查询出每个分类下商品的库存总量
select category_id,sum(num)
from t_item
group by category_id;
-- 查询出每个分类商品所对应的平均单价
select category_id,avg(price)
from t_item
group by category_id;
-- 查询每个部门各多少人
select deptno,count(*)
from emp
group by deptno;
1.案例:查询emp表中每个部门的编号,人数,工资总和,最后根据人数进行升序排列,如果人数一致,根据工资总和降序排列。
select deptno,count(*) c,sum(sal) s
from emp
group by deptno
order by c,s desc;
2.案例:统计每个部门下工资在1000~3000之间的部门的编号,平均工资,最低工资,最高工资,根据平均工资进行升序排列。
select deptno,avg(sal) a,min(sal),max(sal) from emp
where sal between 1000 and 3000
group by deptno
order by a;
3.案例:查询含有上级领导的员工,每个职业的人数,工资的总和,平均工资,最低工资,最后根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列
select job,count(*) c,sum(sal),avg(sal) a,min(sal)
from emp
where mgr is not null
group by job
order by c desc,a;
练习
-- 1. 每个部门中,每个主管的手下人数
select deptno,mgr,count(*)
from emp
group by deptno,mgr;
-- 2. 每种工作的平均工资
select job,avg(sal)
from emp
group by job;
-- 提高题 3. 每年的入职人数
select extract(year from hiredate) y,count(*)
from emp
group by y;
有条件分组统计
HAVING 子句
-聚合函数不能写在where 后面 因为执行where的时候聚合函数还没有执行
-having要和聚合函数结合使用,虽然可以写普通字段的条件 但是普通字段的条件推荐写在 where后面
-查询hero表中平均年龄在30岁以下的类型
select type,avg(age)
from hero
where age<60
group by type
having avg(age)<30
order by 字段名
SQL关键字执行顺序:
1.from 从那张表
2.where 普通字段过滤
3.group by 分组
4.having 聚合字段过滤
5.order by 排序
6.select 筛除
-查询所有分类商品所对应的库存总量中,高于1000的总量
select category_id,sum(num) s
from t_item
group by category_id
having s>100000;
-查询所有分类商品所对应的平均单价中,均价低于100分类
select category_id,avg(price) a
from t_item
group by category_id
having a<100;
-查询编号238和编号917分类商品的平均单价
select category_id,avg(price)
from t_item
where category_id in (238,917)
group by category_id;
回顾:group by 和 having
课堂练习
1.案例:查询emp表中,每个部门的平均工资高于2000的部门的编号,部门的人数,平均工资, 最后根据平均工资进行升序排列。
select deptno,avg(sal) a,count(*)
from emp
group by deptno
having a>2000
order by a;
2.案例:查询emp表中名字中不是以'K'开头的信息,每个部门的最低工资高于1000的部门的编号,工资总和,平均工资以及最低工资,最后根据平均工资进行升序排列。
select deptno,min(sal) m,sum(sal),avg(sal) a
from emp
where ename not like 'k%'
group by deptno
having m>1000
order by a;
3.案例:查询emp表中部门编号是10,30号部门的员工,每个职业的最高工资低于5000的职业的名称,人数,平均工资,最高工资,最后根据人数进行升序排列,如果人数一致,根据最高工资进行降序排列。
select job,max(sal) m,count(*) c,avg(sal) a
from emp
where deptno in (10,30)
group by job
having m<5000
order by c,m desc;
4.案例:查询emp表中,每个部门的编号,人数,工资总和,最高工资以及最低工资,过滤掉最高工资是5000的部门,根据部门的人数进行升序排列,如果人数一致,则根据最高工资进行降序排列。
select deptno,count(*) c,sum(sal) s, max(sal) max, min(sal) min
from emp
group by deptno
having max=5000
order by c,max desc;
5.案例:查询emp表中工资在1000~3000之间的员工信息,每个部门的编号,工资总和,平均工资,过滤掉平均工资低于2000的部门,按照平均工资进行升序排列
select deptno,sum(sal) s,avg(sal) a
from emp
where sal between 1000 and 3000
group by deptno
having a>=2000
order by a;
6.案例:查询emp表中名字不是以‘S’开头,每个职位的名字,人数,工资总和,最高工资,过滤掉工资是3000的职位,根据人数进行升序排列,如果人数一致,根据工资总和进行降序排列。
select job,count(*) c,sum(sal) s,max(sal) m
from emp
where ename not like 's%'
and sal!=3000
group by job
order by c,s desc;
7.案例:查询emp表的信息,每个职位的名称,人数,平均工资,最低工资,过滤掉平均工资是3000的职位信息,根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列
select job,count(*) c,avg(sal) a,min(sal)
from emp
group by job
having a!=3000
order by c desc,a;
子查询
MySQL子查询
思考:查询攻击力最高的英雄的所有信息
-如果不用子查询 需要写两行sql
select max(att) from hero; 500
select * from hero where att=500;
-通过子查询 把两条sql语句整合到一起
select * from hero where att=(select max(att) from hero);
-练习:查询年龄大于平均年龄的英雄信息
select * from hero where age>(select avg(age) from hero);
**能用where用where 不能才用having**
**having要和group by结合使用**
1.案例:拿最低工资的员工信息
select * from emp
where sal=(select min(sal) from emp);
2.案例:工资多于平均工资的员工信息
select * from emp
where sal>(select avg(sal) from emp);
3.案例:最后入职的员工信息
select * from emp
where hiredate=(select max(hiredate) from emp);
4.案例:查询出哪些分类在商品表中出现过,并查询此分类的详情
-得到商品表里出现的分类id
select distinct category_id from t_item
-从分类表里查询商品表出现的分类信息
select * from t_item_category
where id in (select distinct category_id from t_item);
5.案例:查询工资高于20号部门最高工资的员工的所有信息
select * from emp where sal>(select max(sal) from emp
where deptno=20);
6.案例:查询emp表中姓名是‘KING’所属的部门的编号,名称
select * from dept
where deptno=(select deptno from emp where ename='king');
7.案例:查询部门名称是SALES的部门下所有员工的编号,姓名,职位,以及所属部门的编号
select empno,ename,job,deptno
from emp
where deptno=(select deptno from dept where dname='sales');
8.案例:查询部门地址是DALLAS的部门下所有员工的所有信息
select * from emp
where deptno=(select deptno from dept where loc='dallas');
9.案例:查询跟JONES同样工作的员工的所有信息(包含JONES)
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
关联查询数据
-
查看每个员工的名字以及所在部门的名字
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
-
查看每个商品及所对应的分类名称
select i.title,c.name from t_item i,t_item_category c where i.category_id=c.id;
如果不写关联关系
笛卡尔积
通常情况下 笛卡尔积是无用的结果
是两张表相乘得到的结果
切记工作时不要出现这种情况
1.查看在new york工作的员工
select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno
and d.loc='new york';
2.查看工资高于3000的员工,名字,工资,部门名,所在地
select e.ename,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno
and e.sal>3000;
等值连接/内连接
-等值连接
select * from A,B
where A.x=B.x and age>18
-内连接
select * from
A [inner] join B
on A.x=B.x
where age>18
1.查看在new york工作的员工
select e.ename,d.loc
from emp e join dept d
on e.deptno=d.deptno
where d.loc='new york';
2.查看工资高于3000的员工,名字,工资,部门名,所在地
select e.ename,e.sal,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.sal>3000;
3.查询价格高于1000的dell商品,显示商品名字,库存,商品分类名,结果按库存降序排序
内连接只能查询出有关联关系的数据
select
from type t right join hero h
on t.id=h.typeid
左外连接
以join左边的表为基准 查询结果显示左边表的所有数据,没有关联关系的显示null
select e.ename,d.dname from
emp e left join dept d
on e.deptno=d.deptno
右外连接
以join右边的表为基准 查询结果显示右边表的所有数据,没有关联关系的显示null
select e.ename,d.dname from
emp e right join dept d
on e.deptno=d.deptno;
关联查询数据案例
代码实践
-- 查询出所有可以匹配的商品分类及商品数据
select *
from t_item i join t_item_category c
on i.category_id=c.id;
-- 查询出所有的分类,以及与之匹配的商品
select *
from t_item i right join t_item_category c
on i.category_id=c.id;
-- 查询出所有的商品,以及与之匹配的分类
select *
from t_item i left join t_item_category c
on i.category_id=c.id;
强化练习
代码实践
-- 查询出所有有地址的用户
select *
from emp join dept
on emp.deptno=dept.deptno
where dept.loc is not null;
-- 查询出所有用户,同时查出这些用户的地址
select *
from emp left join dept
on emp.deptno=dept.deptno;
-- 查询出所有的地址,同时查出地址所对应的用户信息
select *
from emp right join dept
on emp.deptno=dept.deptno;
课程回顾:
1. group by 分组 having 聚合函数的条件
2. 子查询 在sql语句中嵌套dql 可以多层
3. 关联查询 等值连接 和 内连接
4. 左外和右外
练习
每个部门的人数,根据人数排序
每个部门中,每个主管的手下人数
每种工作的平均工资
每年的入职人数
少于等于3个人的部门
拿最低工资的员工信息
只有一个下属的主管信息
平均工资最高的部门编号
下属人数最多的人,查询其个人信息
拿最低工资的人的信息
最后入职的员工信息
工资多于平均工资的员工信息
查询员工信息,部门名称
员工信息,部门名称,所在城市
DALLAS 市所有的员工信息
按城市分组,计算每个城市的员工数量
查询员工信息和他的主管姓名
员工信息,员工主管名字,部门名
员工信息,部门名,和部门经理
员工和他所在部门名
案例:查询emp表中所有员工的编号,姓名,职位,工资以及工资的等级,根据工资的等级进行升序排列
案例:查询emp表中所有员工的编号,姓名,职位,工资以及该员工上级领导的编号,姓名,职位,工资
案例:查询emp表中名字中没有字母'K'的所有员工的编号,姓名,职位以及所在部门的编号,名称,地址
案例:查询dept表中所有的部门的所有的信息,以及与之关联的emp表中员工的编号,姓名,职位,工资
案例:查询emp表中所有员工的编号,姓名,职位,工资以及工资的等级,该等级的最低工资,按照员工的编号进行升序排列。