復(fù)習(xí)
day01
數(shù)據(jù)庫相關(guān)：
create database db4 character set utf8;
show databases;
show create database db4;
drop database db4;
use db4;
表相關(guān)：
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;
數(shù)據(jù)相關(guān)：
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
‘’ ``
事務(wù) set autocommit=0;
show variables like '%autocommit%'
begin commit rollback
數(shù)據(jù)庫分類
DDL：數(shù)據(jù)定義語言 craete alter drop
DML: 數(shù)據(jù)操作語言 insert update delete select
DQL select
TCL：事務(wù)控制語言 begin commit rollback
DCL：數(shù)據(jù)控制語言 控制表的權(quán)限
數(shù)據(jù)類型
整數(shù) int bitint
浮點數(shù) 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)
數(shù)值計算
+ - * / % 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('','') 把字符串時間轉(zhuǎn)成date
age = ifnull(x,y);
聚合函數(shù)
sum() avg() count(*) max() min()
字符串函數(shù)
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')
數(shù)學(xué)
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;
-查詢每種類型的人數(shù)
select type,count(*) from hero
group by type;
-查詢 每種性別 平均價格渣玲，最大年齡和最便宜的價格 是多少
select sex,avg(money) 平均價格,max(age) 最大年齡,min(money) 最便宜價格 from hero
group by sex;
-查詢 每種類型 年齡在30歲以下的人數(shù)是多少
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;
-- 查詢出每個分類商品所對應(yīng)的平均單價
select category_id,avg(price)
from t_item
group by category_id;
-- 查詢每個部門各多少人
select deptno,count(*)
from emp
group by deptno;
1.案例：查詢emp表中每個部門的編號，人數(shù)颅夺，工資總和朋截，最后根據(jù)人數(shù)進(jìn)行升序排列，如果人數(shù)一致吧黄，根據(jù)工資總和降序排列部服。
select deptno,count(*) c,sum(sal) s
from emp
group by deptno
order by c,s desc;
2.案例：統(tǒng)計每個部門下工資在1000~3000之間的部門的編號，平均工資拗慨，最低工資廓八，最高工資，根據(jù)平均工資進(jìn)行升序排列赵抢。
select deptno,avg(sal) a,min(sal),max(sal) from emp
where sal between 1000 and 3000
group by deptno
order by a;
3.案例：查詢含有上級領(lǐng)導(dǎo)的員工剧蹂，每個職業(yè)的人數(shù)，工資的總和昌讲，平均工資国夜，最低工資，最后根據(jù)人數(shù)進(jìn)行降序排列短绸，如果人數(shù)一致车吹，根據(jù)平均工資進(jìn)行升序排列
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;
練習(xí)
-- 1. 每個部門中，每個主管的手下人數(shù)
select deptno,mgr,count(*)
from emp
group by deptno,mgr;
-- 2. 每種工作的平均工資
select job,avg(sal)
from emp
group by job;
-- 提高題 3. 每年的入職人數(shù)
select extract(year from hiredate) y,count(*)
from emp
group by y;
有條件分組統(tǒng)計
HAVING 子句
-聚合函數(shù)不能寫在where 后面 因為執(zhí)行where的時候聚合函數(shù)還沒有執(zhí)行
-having要和聚合函數(shù)結(jié)合使用醋闭，雖然可以寫普通字段的條件 但是普通字段的條件推薦寫在 where后面
-查詢hero表中平均年齡在30歲以下的類型
select type,avg(age)
from hero
where age<60
group by type
having avg(age)<30
order by 字段名
SQL關(guān)鍵字執(zhí)行順序:
1.from 從那張表
2.where 普通字段過濾
3.group by 分組
4.having 聚合字段過濾
5.order by 排序
6.select 篩除
-查詢所有分類商品所對應(yīng)的庫存總量中,高于1000的總量
select category_id,sum(num) s
from t_item
group by category_id
having s>100000;
-查詢所有分類商品所對應(yīng)的平均單價中,均價低于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
課堂練習(xí)
1.案例：查詢emp表中窄驹，每個部門的平均工資高于2000的部門的編號,部門的人數(shù),平均工資, 最后根據(jù)平均工資進(jìn)行升序排列。
select deptno,avg(sal) a,count(*)
from emp
group by deptno
having a>2000
order by a;
2.案例：查詢emp表中名字中不是以'K'開頭的信息,每個部門的最低工資高于1000的部門的編號证逻，工資總和,平均工資以及最低工資乐埠，最后根據(jù)平均工資進(jìn)行升序排列。
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號部門的員工,每個職業(yè)的最高工資低于5000的職業(yè)的名稱,人數(shù)囚企，平均工資丈咐，最高工資，最后根據(jù)人數(shù)進(jìn)行升序排列龙宏，如果人數(shù)一致棵逊，根據(jù)最高工資進(jìn)行降序排列。
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表中银酗，每個部門的編號辆影，人數(shù)，工資總和黍特，最高工資以及最低工資蛙讥，過濾掉最高工資是5000的部門，根據(jù)部門的人數(shù)進(jìn)行升序排列灭衷，如果人數(shù)一致次慢，則根據(jù)最高工資進(jìn)行降序排列。
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的部門侵蒙，按照平均工資進(jìn)行升序排列
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’開頭，每個職位的名字傅蹂，人數(shù)纷闺，工資總和，最高工資份蝴，過濾掉工資是3000的職位犁功，根據(jù)人數(shù)進(jìn)行升序排列，如果人數(shù)一致婚夫，根據(jù)工資總和進(jìn)行降序排列浸卦。
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表的信息，每個職位的名稱案糙，人數(shù)限嫌，平均工資，最低工資时捌，過濾掉平均工資是3000的職位信息怒医，根據(jù)人數(shù)進(jìn)行降序排列，如果人數(shù)一致奢讨，根據(jù)平均工資進(jìn)行升序排列
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);
-練習(xí)：查詢年齡大于平均年齡的英雄信息
select * from hero where age>(select avg(age) from hero);
**能用where用where 不能才用having**
**having要和group by結(jié)合使用**
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.案例：查詢出哪些分類在商品表中出現(xiàn)過稚叹，并查詢此分類的詳情
-得到商品表里出現(xiàn)的分類id
select distinct category_id from t_item
-從分類表里查詢商品表出現(xiàn)的分類信息
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';
關(guān)聯(lián)查詢數(shù)據(jù)
-
查看每個員工的名字以及所在部門的名字
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; -
查看每個商品及所對應(yīng)的分類名稱
select i.title,c.name from t_item i,t_item_category c where i.category_id=c.id; 如果不寫關(guān)聯(lián)關(guān)系
笛卡爾積
通常情況下 笛卡爾積是無用的結(jié)果
是兩張表相乘得到的結(jié)果
切記工作時不要出現(xiàn)這種情況
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;
等值連接/內(nèi)連接
-等值連接
select * from A,B
where A.x=B.x and age>18
-內(nèi)連接
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商品，顯示商品名字迟蜜，庫存刹孔，商品分類名，結(jié)果按庫存降序排序
內(nèi)連接只能查詢出有關(guān)聯(lián)關(guān)系的數(shù)據(jù)
select
from type t right join hero h
on t.id=h.typeid
左外連接
以join左邊的表為基準(zhǔn) 查詢結(jié)果顯示左邊表的所有數(shù)據(jù)，沒有關(guān)聯(lián)關(guān)系的顯示null
select e.ename,d.dname from
emp e left join dept d
on e.deptno=d.deptno
右外連接
以join右邊的表為基準(zhǔn) 查詢結(jié)果顯示右邊表的所有數(shù)據(jù)髓霞，沒有關(guān)聯(lián)關(guān)系的顯示null
select e.ename,d.dname from
emp e right join dept d
on e.deptno=d.deptno;
關(guān)聯(lián)查詢數(shù)據(jù)案例
代碼實踐
-- 查詢出所有可以匹配的商品分類及商品數(shù)據(jù)
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;
強(qiáng)化練習(xí)
代碼實踐
-- 查詢出所有有地址的用戶
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;
-- 查詢出所有的地址,同時查出地址所對應(yīng)的用戶信息
select *
from emp right join dept
on emp.deptno=dept.deptno;
課程回顧：
1. group by 分組 having 聚合函數(shù)的條件
2. 子查詢 在sql語句中嵌套dql 可以多層
3. 關(guān)聯(lián)查詢 等值連接 和 內(nèi)連接
4. 左外和右外
練習(xí)
每個部門的人數(shù),根據(jù)人數(shù)排序
每個部門中卦睹，每個主管的手下人數(shù)
每種工作的平均工資
每年的入職人數(shù)
少于等于3個人的部門
拿最低工資的員工信息
只有一個下屬的主管信息
平均工資最高的部門編號
下屬人數(shù)最多的人，查詢其個人信息
拿最低工資的人的信息
最后入職的員工信息
工資多于平均工資的員工信息
查詢員工信息方库，部門名稱
員工信息结序，部門名稱，所在城市
DALLAS 市所有的員工信息
按城市分組纵潦，計算每個城市的員工數(shù)量
查詢員工信息和他的主管姓名
員工信息徐鹤，員工主管名字，部門名
員工信息邀层，部門名返敬，和部門經(jīng)理
員工和他所在部門名
案例：查詢emp表中所有員工的編號，姓名寥院，職位劲赠，工資以及工資的等級，根據(jù)工資的等級進(jìn)行升序排列
案例：查詢emp表中所有員工的編號秸谢，姓名凛澎，職位，工資以及該員工上級領(lǐng)導(dǎo)的編號钮追，姓名预厌，職位，工資
案例：查詢emp表中名字中沒有字母'K'的所有員工的編號元媚，姓名轧叽，職位以及所在部門的編號，名稱刊棕，地址
案例：查詢dept表中所有的部門的所有的信息炭晒，以及與之關(guān)聯(lián)的emp表中員工的編號，姓名甥角，職位网严，工資
案例：查詢emp表中所有員工的編號，姓名嗤无，職位震束，工資以及工資的等級，該等級的最低工資当犯，按照員工的編號進(jìn)行升序排列垢村。
