MySQL学习总结2

MySQL学习总结2

表的五大约束:

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

多表之间的关系如何来维护

• 外键约束:foreign key

  • 插入数据: 数据必须先存在于主表,然后再插入从表中的数据
  • 删除数据(删主表时): 必须先删除从表中数据,再删除主表中的数据
  • alter table product add foreign key(cno) references category(cid);

• 多表之间的建表原则

  • 一对多: 学生和班级, 商品分类和商品
    
    • 建表原则: 在多的一方添加一个键,然后让它作为外键指向一的一方
  • 多对多: 学生和选课
    
    • 建表原则:创建一张中间表,将多对多的关系,拆分成一对多的关系, 中间表至少要包含两个外键,这两个外键分别指向各自原来的表
  • 一对一: 公民和身份证号
    
    • 建表原则: 1. 将两张表的主键建立连接, 2. 将两张合并成一张表 3. 将一对一的关系,拆分成一对多的关系
    • 合并表,拆表
  • 例子：商城订单，商品，用户实例

多表查询讲解

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

• 内连接查询:

  • 隐式内连接(等值连接，常用): select * from product p,category c where p.cno = c.cid;
  • 显式内连接: select * from product p inner join category c on p.cno = c.cid;
  • inner关键字可以省略

• 外连接查询:

  • 左外连接: 以左表为基础,将左表中所有的记录都查询出来，如果（null,null,1，2）那么左边两个也不会显示
    
    • select * from product p left outer join category c on p.cno = c.cid;
  • 右外连接:以右表为基础,将右表中的所有记录都查询出来，如果（1，2，null,null）那么右边两个也不会显示
    
    • select * from product p right outer join category c on p.cno= c.cid;
  • 左右两边有数据的话没什么区别

分页查询 : limit

• limit : 起始索引, 查询几条; 索引是0开始
  
  • select * from product limit 0,3;
  • select * from product limit 3,3;
• 起始索引的计算:(页数-1)*每页显示数量
  
  • (3-1)*3

多表查询例子

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

  • select p.pname,c.cname from product p ,category c where p.cno = c.cid;

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

  • select p.pname,c.cname from product p ,category c where p.cno = c.cid and c.cname=’手机数码’;

• 按照商品分类的名称统计商品的个数:

  • select * from product p ,category c where p.cno = c.cid;
• 分组查询 select 分组条件,分组之后的统计操作
  
  • select c.cname,count(1) from product p ,category c where p.cno = c.cid group by c.cname;

员工表格查询练习

利用表格给信息联系中找

• 所有员工的信息
  
  • SELECT * FROM emp e ,dept p WHERE e.deptno = p.deptno;
• 薪资大于等于1000并且小于等于2000的员工信息
  
  • SELECT * FROM emp e WHERE e.sal BETWEEN 1000 AND 2000;
• 从员工表中查询出所有的部门编号
  
  • SELECT DISTINCT emp.deptno FROM emp ;
• 查询出名字以A开头的员工的信息
  
  • SELECT * FROM emp WHERE emp.ename LIKE ‘A%’;
• 查询出名字第二个字母是L的员工信息
  
  • SELECT * FROM emp WHERE emp.ename LIKE ‘_L%’;
• 查询出没有奖金的员工信息
  
  • SELECT * FROM emp WHERE emp.comm IS NULL;
• 所有员工的平均工资
  
  • SELECT AVG(sal) FROM emp;
• 所有员工的工资总和
  
  • SELECT SUM(sal) FROM emp;
• 所有员工的数量
  
  • SELECT COUNT(1) FROM emp;
• 最高工资
  
  • SELECT MAX(sal) FROM emp;
• 最少工资
  
  • SELECT MIN(sal) FROM emp;
• 最高工资的员工信息
  
  • SELECT *,MAX(sal) FROM emp;
• 最低工资的员工信息
  
  • SELECT *,MIN(sal) FROM emp;
• 分组查询
  
  • 每个部门的平均工资
  • SELECT deptno ,AVG(sal) FROM emp GROUP BY deptno;
• 多表查询
  
  • 查询员工编号,员工姓名,经理编号,经理姓名
  • SELECT e1.empno, e1.ename,e2.empno, e2.ename FROM emp e1,emp e2 WHERE e1.empno = e2.mgr;
  • 查询员工编号,员工姓名,员工部门名称,经理编号,经理姓名
  • SELECT e1.empno ,e1.ename ,d.dname, e2.empno, e2.ename FROM emp e1 , emp e2 , dept d WHERE e1.empno = e2.mgr AND e1.deptno = d.deptno;

• 子查询

  • 单行子查询(> < >= <= = <>)

    • 查询出高于10号部门的平均工资的员工信息
    • SELECT AVG(sal) FROM emp WHERE deptno =10;（10号员工部门的平均工资）
    • SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno =10);

  • 多行子查询(in not in any all) >any >all

    • 查询出比10号部门任何员工薪资高的员工信息
    • select sal from emp where deptno = 10;（查询出10号所有人的工资）
    • select * from emp where sal >any(select sal from emp where deptno = 10);

  • 多列子查询(实际使用较少) in

    • 和10号部门同名同工作的员工信息
    • SELECT * FROM emp e WHERE e.ename IN (SELECT ename FROM emp WHERE deptno = 10 ) AND e.job IN (SELECT job FROM emp WHERE deptno = 10) AND
      deptno != 10;

  • Select接子查询

    • 获取员工的名字和部门的名字
    • select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;（内连接查询）
    • select e.ename,e.deptno from emp e;
    • select e.ename,(select d.dname from dept d where d.deptno = e.deptno) bumen from emp e;

  • from后面接子查询

    • 查询emp表中经理信息
    • select distinct mgr from emp where mgr is not null;
    • select * from (select distinct mgr from emp) t,emp e where t.mgr = e.empno;

  • where 接子查询

    • 薪资高于10号部门平均工资的所有员工信息
    • select avg(sal) from emp where deptno=10;（10号部门的平均工资）
    • select * from emp where sal > (select avg(sal) from emp where deptno=10);

  • having后面接子查询

    • 有哪些部门的平均工资高于30号部门的平均工资
    • select deptno, avg(sal) from emp group by deptno;(所有部门平均工资)
    • select avg(sal) from emp where deptno = 30;(30号部门的平均工资)
    • select deptno, avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp where deptno = 30);
  • 综合题
  • 统计薪资 大于 薪资最高的员工 所在部门的平均工资 和 薪资最低的员工 所在部门的平均工资 的平均工资 的员工信息
    
    • 1.薪资最高的员工
    • select MAX(sal) from emp; – 5000
    • 1.1部门编号
    • select deptno from emp where sal = (select MAX(sal) from emp);
    • 2.10号部门的平均工资 2916
    • select avg(sal) from emp where deptno=(select deptno from emp where sal = (select MAX(sal) from emp));
    • 3.薪资最低的员工
    • 3.1 800
    • select MIN(sal) from emp;
    • 3.2 部门的编号 20
    • select deptno from emp where sal = (select MIN(sal) from emp);
    • 4.20号部门的平均工资 2245
    • select avg(sal) from emp where deptno=(select deptno from emp where sal = (select MIN(sal) from emp));
    • 1. 10号部门和20号部门的平均工资的平均工资
    • select (2916+2245)/2; – 2580.5
    • 算出2581
    • 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;
  • 1. 薪资大于 2581
  • 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);

in 和 exisits的区别

• exisits 的用法: 存在的意思, 判断一张表里面的记录,是否存在与另外一张表中
• exisits(查询语句)如果查询出结果,就返回true，否则,就返回false
• 例子：select * from emp where exists(select * from emp where empno=12345);
• 这个就会查询全部的表，因为where后面跟的是true，
• 正确：select * from emp e1 where exists(select * from emp e2 where e2.deptno=10 and e1.ename = e2.ename)