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;