学习JavaWeb第八天

最新推荐文章于 2022-07-20 20:20:27 发布

无学无忧

最新推荐文章于 2022-07-20 20:20:27 发布

阅读量301

点赞数 1

分类专栏： JavaWeb 文章标签： JavaWeb

本文链接：https://blog.csdn.net/weixin_44377696/article/details/102944072

版权

JavaWeb 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

本文介绍了MySQL中的外键约束，包括其作用和设置方法，详细讲解了1对1、1对多和多对多的表关系建表原则。此外，还探讨了连接查询的类型，如左连接、右连接和内连接，并展示了各种关系的查询实例。同时，文章涵盖了SQL的基本查询操作，如分组、聚合函数、排序和关键字的使用。

摘要由CSDN通过智能技术生成

day08

mysql进阶

外键约束

概述
- 描述表于表之间的关系
作用
- 保证数据完整性
设置外键的语法: ALTER TABLE 从表 ADD CONSTRAINT 外键的名称 FOREIGN KEY(关键的字段) REFERENCES 主表 (主键);
删除：先删除从表,再删除主表语法： ALTER TABLE 表的名字 DROP FOREIGN KEY 外键的名字

注意点

3.外键约束：建立在两个表之间

注意点：一方(主表)(单) --> 主键 

        多(从表) --> 外键 

        1.一般是主表的主键对应从表的外键

        2.主键的主键的长度以及类型必须跟外键的类型以及长度一样(名字不是必须一样)

        3.设置外键的时候，要么全部是null,要么是数据完整的

设置外键(从表)

    设置外键的语法:      

      ALTER  TABLE 从表 ADD  CONSTRAINT 外键的名称  FOREIGN KEY(关键的字段)  REFERENCES  主表 (主键);

    设置外键对查询没有影响，只对增加删除有影响

    增加(修改类似)：必须是主表已经存在的，先增加主表，再添加从表

    删除：先删除从表,再删除主表

注意：删除外键的时候，一定不要忘记删除外键的索引(index)

              ALTER TABLE  表的名字 DROP   FOREIGN KEY   外键的名字

KEY 外键的名字

表关系

1对1
- 建表原则
  - 1、确定主从表
  - 2、在从表中添加一个外键字段
  - 3、让这个外键字段指向主表中的主键
1对多
- 建表原则
  - 1、确定主从表 1的方是主表多的一方是从表
  - 2、在从表中添加一个外键字段
  - 3、让这个外键字段指向1的一方的主键
多对多
- 建表原则
  - 1、创建中间表
    - 至少2个字段
  - 2、中间表(从表)
    - 2个字段分别指向2个主表中的主键

连接查询

左连接
- Select * from 表1 left join 表2 on条件 = 条件;
  - 就是看你以哪个表为基准，以左表为基准，会查询出所有左表的数据，还要右表中有关联的数据
右连接
- Select * from 表1 right join 表2 on条件 = 条件;
  - 就是看你以哪个表为基准，以右表为基准，会查询出所有右表的所有数据，还要左表中有关联的数据
内连接
- 显示内连接是通过 inner join on去查询数据,用on 拼接条件
- 1对1关系查询查询 pgone 的身份证号显示姓名身份号
  - select p.name,i.no from person p,idcard i where i.id=p.id and p.name =‘pgone’
- 1对多关系查询查询pgone下的订单
  - select u.uname,o.oname from user u inner join order o on u.uid =o.uid and u.uname=‘pgone’
  - select u.uname, o.oname from user u,order o where u.uid =o.uid and u.uname=‘pgone’
- 多对多关系查询 – 查询马蓉学习的课程显示马蓉的姓名和课程姓名多对多关系查询
  - select s.sname,c.cname from stu s,course c,c_s cs where cs.sid =s.sid and cs.cid =c.cid and s.sname =‘马蓉’

分组，聚合函数、排序、SQL关键字

基本的查询操作（emp）
- 查询员工的姓名
  - select e.ename from emp e
- 查询员工的薪水以及姓名
  - select e.ename,e.sal from emp e
- 查询员工表所有的数据
  - select * from emp
- 查询每一个员工的年薪
  - select e.ename,e.sal*12 ‘年薪’ from emp e
条件查询关键字：where
- 1、等号查询（=）
  - 查询薪水为5000的员工
    - select * from emp e where e.sal =5000
  - 查询职位为 MANAGER 的员工的信息
    - select * from emp e where e.job =‘MANAGER’
- 2、不等号的查询(<>或者!=)
  - 查询薪水不等于5000的员工的信息
    - select * from emp e where e.sal <> 5000;
  - 查询职位不是MANAGER 的员工的信息
    - select * from emp e where e.job!=‘MANAGER’;
- 3、区间条件的查询（<,<=,>,>= ,between…and）
  - 薪水大于1600的员工信息
    - select * from emp e where e.sal >1600
  - #薪水在1600和3000之间的员工信息
  - #BETWEEN and 是包含头尾信息
- 4、或者（or）
  - 查询部门编号为20或者30的部门信息
    - select * from emp e where e.deptno =20 or e.deptno =30;
- 5、包含，不包含的查询（in，not in ）
  - 查询部门编号为20或者30的信息
    - select * from emp e where e.deptno in(20,30);
  - 查询部门编号不为20也不为30的信息
    - select * from emp e where e.deptno not in(20,30);
- 6、关于空的查询操作（is null ，is not null）
  - 查询没有奖金的员工的信息
    - select * from emp e where e.comm is null or e.comm =0
  - 查有奖金的员工的信息
    - select * from emp e where e.comm is not null and e.comm >0
- 7、模糊查询（like ,%_）
  - 要求第一个字母带有s的员工的信息
    - ‘s%’; #第一个是s，后面的不关心
      - select * from emp e where e.ename like ‘S%’
    - ‘%s’; #最后一个是s，前面的不关心
      - select * from emp e where e.ename like ‘%N’
    - 左右匹配%s%
    - 使用_占位符操作_m%
      - select * from emp e where e.ename like ‘_A%’
- （六）、排序查询
  - 例题
    - 根据员工的薪水排序（降序）
      - select * from emp e order by e.sal desc
    - 员工入职日期降序查询
      - select * from emp e order by e.hiredate desc
    - 查询职位为MANAGER 的员工信息,并且按照薪资从高到低排序
      - select * from emp e where e.job =‘MANAGER’ order by e.sal asc
  - 注意：
    - 如果没有其他条件，只有一个排序的情况下，直接去掉where，并且排序一般都是放在最后执行；
- （七）、聚合函数
  - 概述
    - 中存在一些聚合函数，这些特殊函数是不能直接在where后面当做条件使用的，一般使用方式为放在返回值项（select *）或者放在having关键字后
  - 聚合函数包括：
    - 求最大值max
      - 求员工的最高薪资
        
        select MAX(e.sal) from emp e
    - 求最小值 min
      - 求员工的最低工资
        
        select MIN(e.sal) from emp e
    - 求和 sum
      - 求员工ALLEN工资加奖金总共多少钱
        
        select e.sal+e.comm from emp e where e.ename =‘ALLEN’
      - 求员工的工资和
        
        select SUM(e.sal) from emp e
    - 求平均 avg
      - 求员工平均薪资
        
        select AVG(e.sal) from emp e
    - 求总数 count
      - 求一共有多少员工
        
        select COUNT(*) from emp e
- （八）、去重复distinct
  - 查询员工的工作job
    - select distinct e.job from emp e
- （九）、分组查询 group by & having
  - 将查询出的数据进行分组处理
  - 关键字是 group by
  - 例题
    - 找出不同工作类别中的最高薪资
      - select MAX(e.sal),e.job from emp e group by e.job
    - 找出不同工作类别中的最高薪资，显示的时候要求按照薪资从高到低显示
      - select MAX(e.sal),e.job from emp e group by e.job order by MAX(e.sal) desc
    - 求每个部门的平均薪资
      - select AVG(e.sal),e.deptno from emp e group by e.deptno
    - 求每个岗位的最高薪资，除MANAGER之外
      - select MAX(e.sal),e.job from emp e where e.job!=‘MANAGER’ group by e.job
      - select MAX(e.sal),e.job from emp e group by e.job having e.job!=‘MANAGER’
    - 找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的
      - select * from emp e group by e.job having AVG(e.sal)>2000
  - where和having的区别
    - 1、where 用于分组前，不能再分组后使用
    - 2、where后无法使用聚合函数
    - 3、having 可以在分组后去使用
    - 4、having 可以支持聚合函数
- （十）、Limit关键字
  - 在MySQL中，我们会使用limit关键字做分页或者求前N的数
  - Limit 如果加两个参数，适用于分页
  - Limit 参数1,参数2
  - 参数1 ，代表从哪个索引开始取值
  - 参数2，代表一共取几条；
- （十一）、子查询
  - 概述
    - 将一个完整的SQL当做成另一个SQL的条件；<嵌套SQL>
    - 在多表操作中，子查询是非常常见的一种查询方式；
    - 例题
      - 找出薪水比公司平均薪水高的员工,要求显示员工的名字和薪水
        
        select e.ename,e.sal from emp e where e.sal>(select AVG(e.sal) from emp e)
      - 求工资最高的员工，显示员工姓名和薪水
        
        select e.ename,e.sal from emp e where e.sal =(select MAX(e.sal) from emp e)
      - 求工资最低的员工，显示员工的姓名和薪水
        
        select e.ename,e.sal from emp e where e.sal =(select MIN(e.sal) from emp e)
多表
- 案例练习

#表结构
1	表结构
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
           
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
	DEPTNO int  PRIMARY KEY,##部门编号
	DNAME VARCHAR(14) ,	##部门名称
	LOC VARCHAR(13)  	##部门地址
	) ;

INSERT INTO DEPT VALUES	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES	(40,'OPERATIONS','BOSTON');
	
##员工表	
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
	EMPNO int  PRIMARY KEY,	#员工编号
	ENAME VARCHAR(10),	#员工姓名
	JOB VARCHAR(9),		#员工工作
	MGR int, 		#员工直属领导编号
	HIREDATE DATE, 		#入职时间
	SAL double,		#工资
	COMM double,		#奖金
	DEPTNO int		#对应dept表的外键
);  
## 添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);

INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);

#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE( 
	GRADE int,	#等级
	LOSAL double,	#最低工资
	HISAL double );	#最高工资

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

use test1;
-- 1.	返回拥有员工的信息以及员工的部门名、部门号。
select e.ename '名字',d.dname '部门名',e.deptno '部门号'  from emp as e,dept as d where e.deptno=d.deptno;
-- 2.	工资水平多于smith的员工信息。

select *  from emp as e where e.sal> (select e.sal from emp e where e.ename='smith') and e.job='clerk';
-- 3.	返回员工和所属经理的姓名。
select e.ename '下级',m.ename '上级' from emp as e left join emp as m on e.mgr=m.empno;
select e.ename '下级',m.ename '上级' from emp as m right join  emp as e on e.mgr=m.empno;
-- 4.	返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select e.ename '下级',e.hiredate '日期',m.ename '上级',m.hiredate '日期' from emp as e left join emp as m on e.mgr=m.empno and e.hiredate<m.hiredate ;
-- 5.	返回员工姓名及其所在的部门名称。
select e.ename '员工名',d.dname '部门名'  from emp as e left join dept as d on e.deptno=d.deptno;
-- 6.	返回从事clerk工作的员工姓名和所在部门名称。
select e.ename,e.job,d.dname  from emp as e , dept as d where e.deptno=d.deptno and e.job='clerk';
-- 7.	返回部门号及其本部门的最低工资。
select d.dname '部门名', e.deptno,MIN(e.sal) '部门最低工资'  from emp as e ,dept as d where d.deptno=e.deptno group by e.deptno ;
-- 8.	返回销售部(sales)所有员工的姓名。
select e.ename ,d.dname from emp as e,dept as d where e.deptno=d.deptno and d.dname='sales';
-- 9.	返回工资水平多于平均工资的员工。
select * from emp e where e.sal>(select AVG(e.sal) from emp as e);
--         返回与30部门员工工资水平相同的员工姓名与工资。
select e.ename,e.sal from emp e where e.sal in (select e.sal from emp e where e.deptno=30);
-- 10.	返回工资高于30部门所有员工工资水平的员工信息。
select * from emp e where e.sal > (select MAX(e.sal)  from emp e where e.deptno=30);

-- 11.	返回部门号、部门名、部门所在位置及其每个部门的员工总数。
select d.deptno,d.dname,d.loc,COUNT(e.deptno)  from emp e,dept d where e.deptno=d.deptno group by e.deptno;
-- 12.	返回员工的姓名、所在部门名及其工资。
select e.ename,d.dname,e.sal  from emp e ,dept d where e.deptno=d.deptno;
-- 13.	返回员工的详细信息。(包括部门名)
select e.*,d.dname  from emp e ,dept d where e.deptno=d.deptno;
-- 14.	返回员工工作及其从事此工作的最低工资。
select MIN(e.sal) ,e.job from emp e group by e.job;
-- 15.	计算出员工的年薪，并且以年薪排序。
select e.ename,e.sal*12 from emp e order by e.sal*12 desc;