一.創(chuàng)建多表及多表的關(guān)系
--創(chuàng)建一個(gè)新的數(shù)據(jù)庫 test01
create database test01;
--創(chuàng)建分類表 cid分類的ID cname分類名稱 cdesc分類描述
create table category(
cid int primary key auto_increment,
cname varchar(20),
cdesc varchar(30)
);
insert into category values(null,'手機(jī)數(shù)碼','小手機(jī)');
insert into category values(null,'鞋靴箱包','包包');
insert into category values(null,'香煙酒水','黃鶴樓,茅臺');
insert into category values(null,'饞嘴零食','衛(wèi)龍辣條,周黑鴨');
--創(chuàng)建一個(gè)商品 product pid pname price pdesc cno
create table product(
pid int primary key auto_increment,
pname varchar(10) unique,
price double,
pdesc varchar(30),
cno int
);
insert into product values(null,'華為手機(jī)',500,'支持國產(chǎn)',1);
-- 插入的數(shù)據(jù)無效
insert into product values(null,'華為榮耀',600,'支持國產(chǎn)',11);
-- 五大約束:
主鍵約束--primary key 不能為空,必須唯一
唯一約束--unique 不能重復(fù),可以為空
非空約束--not null 不能為空
檢查約束:-- check(條件) mysql里面語法可以寫,但是它是直接忽略掉檢查約束,不支持
外鍵約束--foreign key ... references 約束多表之間數(shù)據(jù)的
insert into product values(null,'熱干面',5,'大武漢熱干面',77);
--外鍵約束(多表約束): 實(shí)際上是用來約束兩張表,約束從表中的記錄必須存在于主表中
alter table product add foreign key(cno) references category(cid);
insert into product values(null,'錘子',2999,'可以砸榴蓮的手機(jī)',1);
insert into product values(null,'小米',1599,'為發(fā)燒而生',1);
insert into product values(null,'李寧',99,'不走尋常路',2);
insert into product values(null,'耐克',399,'just do it',2);
insert into product values(null,'黃鶴樓',20,'飯后一根煙',3);
insert into product values(null,'衛(wèi)龍辣條',5,'衛(wèi)龍辣條加料不加價(jià)',4);
insert into product values(null,'老干媽辣椒醬',9,'永遠(yuǎn)不變的味道',4);
insert into product values(null,'老干娘辣椒醬',null,'永遠(yuǎn)不變的味道',4);
alter table product add foreign key(cno) references category(cid);
二.使用多表查詢
1.多表查詢講解
-- 交叉連接查詢 笛卡爾積 : 兩張表的乘積, 實(shí)際沒有意義
-- 內(nèi)連接查詢:
? 隱式內(nèi)連接(等值內(nèi)連接): select * from product p,category c where p.cno = c.cid;
? 顯式內(nèi)連接: select * from product p inner join category c on p.cno = c.cid;
? 表A inner join 表B on 條件
? inner關(guān)鍵字可以省略
-- 外連接查詢:
-- 左外連接: 以左表為基礎(chǔ),將左表中所有的記錄都查詢出來,如果沒有對應(yīng)的記錄,用null值填充
select * from product p left outer join category c on p.cno = c.cid;
-- 右外連接:以右表為基礎(chǔ),將右表中的所有記錄都查詢出來,如果沒有對應(yīng)的記錄,用null值填充
select * from product p right outer join category c on p.cno= c.cid;
2.分頁查詢 : limit
--limit : 起始索引, 查詢幾條; 索引是0開始
--limit[offset],[limit]
-- offset 為偏移量
-- limit 為長度限制
limit 6,offset 4 = limit 4,6;
-- 加條件:
select * from product where cid =? limit ?,? ;
--請聽題: 每頁顯示3條數(shù)數(shù)據(jù), 請查詢出第2頁的所有數(shù)據(jù)
select * from product limit 0,3; --第1頁
select * from product limit 3,3; -- 第2 頁
--起始索引: (頁數(shù)-1)*每頁顯示數(shù)量 ,頁數(shù)是從1開始
(3-1)*3
(4-1)*3
select * from product limit 6,3;
3.多表查詢練習(xí)
查詢出(商品名稱product,商品分類名稱category)信息
select p.pname 商品名稱, c.cname 分類名稱 from product p , category c where p.cno = c.cid;
查詢分類名稱為手機(jī)數(shù)碼(category)的所有商品(product)
select * from product p , category c where p.cno = c.cid and c.cname = '手機(jī)數(shù)碼';
select p.* from product p , category c where p.cno = c.cid and c.cname = '手機(jī)數(shù)碼';
4.練習(xí)題
- 按照商品分類的名稱統(tǒng)計(jì)商品的個(gè)數(shù): count group by
select * from product p , category c where p.cno = c.cid; 內(nèi)聯(lián)接查詢出的結(jié)果
select c.cname, count(1) from product p, category c where p.cno = c.cid group by c.cname;
- 查詢1號訂單的訂單項(xiàng)信息(orderitem)和商品信息(product)
select * from orderitem oi,product p where oi.pno = p.pid;
select * from orderitem oi,product p where oi.pno = p.pid and oi.ono=1;
5.多表查詢
- 分組查詢
--每個(gè)部門的平均工資
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;
- 多表查詢
-- 查詢員工編號,員工姓名,經(jīng)理編號,經(jīng)理姓名
SELECT e1.empno,e1.ename,m1.empno mgrno,m1.ename mname FROM emp e1,emp m1 WHERE e1.mgr=m1.empno;
- 子查詢
-- 單行子查詢(> < >= <= = <>)
-- 查詢出高于10號部門的平均工資的員工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);
-- 多行子查詢(in not in any all) >any >all
-- 查詢出比10號部門任何員工薪資高的員工信息
SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);
-- 多列子查詢(實(shí)際使用較少) in
-- 和10號部門同名同工作的員工信息
SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno = 10) ;
-- Select接子查詢
-- 獲取員工的名字和部門的名字
SELECT e1.ename,d1.dname FROM emp e1,dept d1 WHERE e1.deptno=d1.deptno;
-- from后面接子查詢
-- 查詢emp表中經(jīng)理信息
select * from (select distinct mgr from emp where mgr is not null) m1, emp e1 where m1.mgr = e1.empno;
-- where 接子查詢
-- 薪資高于10號部門平均工資的所有員工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);
-- having后面接子查詢
-- 有哪些部門的平均工資高于30號部門的平均工資
SELECT AVG(sal) FROM emp WHERE deptno=30;
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp WHERE deptno=30);
-- 工資>JONES工資
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='jones');
-- 查詢與SCOTT同一個(gè)部門的員工
SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='scott');
-- 工資高于30號部門所有人的員工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 查詢工作和工資與MARTIN完全相同的員工信息
SELECT * FROM emp WHERE (job,sal)IN(SELECT job,sal FROM emp WHERE ename='Martin');
-- 有兩個(gè)以上直接下屬的員工信息
SELECT mgr,COUNT(1) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING COUNT(1)>=2;
SELECT * FROM emp e1,
(SELECT mgr,COUNT(1) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING COUNT(1)>=2) e2
WHERE e1.empno=e2.mgr;
-- 查詢員工編號為7788的員工名稱,員工工資,部門名稱,部門地址
SELECT e1.ename,e1.sal,d1.dname,d1.loc FROM emp e1,dept d1 WHERE e1.deptno=d1.deptno AND e1.empno=7788;
-- 統(tǒng)計(jì)薪資大于薪資最高的員工所在部門的平均工資和薪資最低的員工所在部門的平均工資的平均工資的員工信息
select * from emp
where sal > ( select ((select avg(sal) from emp where deptno = (select deptno from emp where sal = (select MAX(sal) from emp)))
+
(select avg(sal) from emp where deptno = (select deptno from emp where sal = (select MIN(sal) from emp)))
)/2);
-- 查詢出高于本部門平均工資的員工信息
SELECT * FROM emp e1 WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno = e1.deptno);
-- 列出達(dá)拉斯加工作的人中,比紐約平均工資高的人
SELECT AVG(sal) FROM emp e1,dept d1 WHERE d1.deptno=e1.deptno AND d1.loc='NEW YORK';
SELECT * FROM emp e1,dept d1 WHERE d1.deptno=e1.deptno AND d1.loc='dallas' AND sal>(SELECT AVG(sal) FROM emp e1,dept d1 WHERE d1.deptno=e1.deptno AND d1.loc='NEW YORK');
-- 查詢7369員工編號,姓名,經(jīng)理編號和經(jīng)理姓名
SELECT e1.empno,e1.ename,e1.mgr,m1.ename FROM emp e1,emp m1 WHERE e1.empno=7369 AND e1.mgr=m1.empno;
-- 查詢出各個(gè)部門薪水最高的員工所有信息
SELECT * FROM emp e1 WHERE sal =(SELECT MAX(sal) FROM emp WHERE deptno = e1.deptno);
6.面試題
CREATE TABLE test(
name CHAR(20),
kecheng CHAR(20),
fenshu CHAR(20)
);
INSERT INTO test VALUES('張三','語文',81),
('張三','數(shù)學(xué)',75),
('李四','語文',76),
('李四','數(shù)學(xué)',90),
('王五','語文',81),
('王五','數(shù)學(xué)',82);
--請用一條Sql語句查處分?jǐn)?shù)大于80的學(xué)生
SELECT * FROM test WHERE fenshu>80;
