mysql多表练习笔记

一.创建多表及多表的关系

--创建一个新的数据库 test01
create database test01;
--创建分类表 cid分类的ID cname分类名称 cdesc分类描述
create table category(
    cid int primary key auto_increment,
    cname varchar(20),
    cdesc varchar(30)
);
insert into category values(null,'手机数码','小手机');
insert into category values(null,'鞋靴箱包','包包');
insert into category values(null,'香烟酒水','黄鹤楼,茅台');
insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');
--创建一个商品 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,'华为手机',500,'支持国产',1);
-- 插入的数据无效
insert into product values(null,'华为荣耀',600,'支持国产',11);

-- 五大约束:
    主键约束--primary key   不能为空,必须唯一
    唯一约束--unique   不能重复,可以为空
    非空约束--not null   不能为空
    检查约束:-- check(条件) mysql里面语法可以写,但是它是直接忽略掉检查约束,不支持
    外键约束--foreign key ... references  约束多表之间数据的


insert into product values(null,'热干面',5,'大武汉热干面',77);

--外键约束(多表约束): 实际上是用来约束两张表,约束从表中的记录必须存在于主表中
alter table product add foreign key(cno) references category(cid);

insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(null,'小米',1599,'为发烧而生',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,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(null,'老干娘辣椒酱',null,'永远不变的味道',4);


alter table product add foreign key(cno) references category(cid);

二.使用多表查询

1.多表查询讲解

-- 交叉连接查询  笛卡尔积 : 两张表的乘积, 实际没有意义
-- 内连接查询:

 隐式内连接(等值内连接): select * from product p,category c where p.cno = c.cid;

 显式内连接: select *  from product p inner join category c on  p.cno = c.cid;

     表A   inner  join  表B  on  条件

     inner关键字可以省略

-- 外连接查询:


-- 左外连接: 以左表为基础,将左表中所有的记录都查询出来,如果没有对应的记录,用null值填充

               select * from product p left outer join category c on p.cno = c.cid;

-- 右外连接:以右表为基础,将右表中的所有记录都查询出来,如果没有对应的记录,用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条数数据, 请查询出第2页的所有数据

select * from product limit 0,3;  --第1页

select * from product limit 3,3; -- 第2 页

--起始索引:  (页数-1)*每页显示数量 ,页数是从1开始

(3-1)*3

(4-1)*3

select * from product limit 6,3;

3.多表查询练习

查询出(商品名称product,商品分类名称category)信息

 select p.pname 商品名称, c.cname 分类名称 from product p , category c where p.cno = c.cid;

查询分类名称为手机数码(category)的所有商品(product)

 select * from product p , category c where p.cno = c.cid and c.cname = '手机数码';

 select p.* from product p , category c where p.cno = c.cid and c.cname = '手机数码';

4.练习题

按照商品分类的名称统计商品的个数: count group by

 select * from product p , category c where p.cno = c.cid; 内联接查询出的结果

select c.cname, count(1) from product p, category c where p.cno = c.cid group by c.cname;

查询1号订单的订单项信息(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.多表查询

分组查询

--每个部门的平均工资
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;

多表查询

-- 查询员工编号,员工姓名,经理编号,经理姓名
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);

-- 多列子查询(实际使用较少)   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表中经理信息
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同一个部门的员工
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');

-- 有两个以上直接下属的员工信息
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;

-- 统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息
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);

-- 列出达拉斯加工作的人中,比纽约平均工资高的人
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员工编号,姓名,经理编号和经理姓名
SELECT e1.empno,e1.ename,e1.mgr,m1.ename FROM emp e1,emp m1 WHERE e1.empno=7369 AND e1.mgr=m1.empno;


-- 查询出各个部门薪水最高的员工所有信息
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),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',82);

--请用一条Sql语句查处分数大于80的学生

SELECT * FROM test WHERE fenshu>80;

最后编辑于：2017.12.11 04:25:52

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 220,492评论 6赞 513
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 94,048评论 3赞 396
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 166,927评论 0赞 358
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 59,293评论 1赞 295
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 68,309评论 6赞 397
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 52,024评论 1赞 308
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,638评论 3赞 420
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,546评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 46,073评论 1赞 319
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 38,188评论 3赞 340
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 40,321评论 1赞 352
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,998评论 5赞 347
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,678评论 3赞 331
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 32,186评论 0赞 23
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,303评论 1赞 272
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,663评论 3赞 375
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 45,330评论 2赞 358

mysql多表练习笔记

一.创建多表及多表的关系

二.使用多表查询

1.多表查询讲解

2.分页查询 : limit

3.多表查询练习

4.练习题

5.多表查询

6.面试题

推荐阅读更多精彩内容