• MySQL数据的完整性和多表查询

    目录

    分组查询

    数据的完整性

    1.实体完整性

    2.域完整性

    3.引用完整性（参照完整性)

     

    表与表之间的关系

    多表查询

    1.合并结果集

    2.连接查询 （非常重要）

            1）标准内连接查询

            2）外连接（左连接、右连接）

            3）自然连接 

    3.子查询

    MySQL数据库的备份与恢复

    1.生成SQL脚本  导出数据

    2　执行SQL脚本 恢复数据

    分组查询


    分组查询：按照某个字段来进行分组，分组后可以进行数据统计，分组查询需要使用group by关键字实现
    凡是和聚合函数同时出现的列名，一定要写在group by后面

    SELECT 字段名 FROM 表名 GROUP BY 条件;

    以emp表为例：

    1. #创建表emp
    2. CREATE TABLE emp(
    3. 	empno		INT,
    4. 	ename		VARCHAR(50),
    5. 	job		VARCHAR(50),
    6. 	mgr		INT,
    7. 	hiredate	DATE,
    8. 	sal		DECIMAL(7,2),
    9. 	comm		DECIMAL(7,2),
    10. 	deptno		INT
    11. ) ;
    12. #向emp表中插入数据
    13. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    14. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    15. INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    16. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    17. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    18. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    19. INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    20. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
    21. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    22. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    23. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
    24. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    25. INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
    26. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

    1. #查询每个部门的部门编号和每个部门的工资和：
    2. SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
    3. #查询每个部门的部门编号以及每个部门的人数
    4. SELECT deptno AS '部门编号',COUNT(*) AS '每个部门的人数' FROM emp GROUP BY deptno;
    5. #查询每个部门的部门编号以及每个部门工资大于1500的人数
    6. SELECT deptno AS '部门编号',COUNT(*) AS '部门工资大于1500的人数' FROM emp WHERE sal>1500 GROUP BY deptno;
    7. #查询stu表中男生和女生各有多少人
    8. SELECT gender,COUNT(*) FROM stu GROUP BY gender;
    9.  
    10. #HAVING子句：对分组以后的数据再次进行过滤
    11. #查询工资总和大于9000的部门编号以及工资和
    12.  
    13. #查询部门编号以及每个部门的工资和
    14. SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
    15. #查询工资总和大于9000的部门编号以及工资和
    16. SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

    HAVING子句：对分组以后的数据再次进行过滤

    having与where的区别:

    1.having是在分组后对数据进行过滤，where是在分组前对数据进行过滤

    2.having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。

    WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

    1. #查询工资总和大于9000的部门编号以及工资和
    2.  
    3. #查询部门编号以及每个部门的工资和
    4. SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
    5. #查询工资总和大于9000的部门编号以及工资和
    6. SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

    LIMIT：对查询出来的结果进行显示的限定，用来限定查询结果的起始行，以及总行数

    LIMIT 起始行数下标，显示多少行行数
    1. #查询emp表中的数据，显示前三条
    2. SELECT * FROM emp LIMIT 0,3;
    3. #查询emp表中的数据，从第三条（记录的下标为2）开始显示，一共显示3条
    4. SELECT * FROM emp LIMIT 2,3;

    数据的完整性

    作用：保证用户输入的数据保存到数据库中是正确的。

    确保数据的完整性 = 在创建表时给表中添加约束

    完整性的分类：

    实体完整性

    域完整性

    引用完整性

    1.实体完整性

    实体：即表中的一行(一条记录)代表一个实体（entity）

    实体完整性的作用：标识每一行数据不重复。

    约束类型： 主键约束（primary key）  唯一约束(unique)  自动增长列(auto_increment)

    1.1主键约束（primary key）

    注：每个表中要有一个主键。

    特点：数据唯一，且不能为null

    1. #第一种方式创建主键
    2. CREATE TABLE `student1`(
    3. `id` INT PRIMARY KEY,
    4. `name` VARCHAR(50)
    5. );
    6.  
    7. CREATE TABLE `student2`(
    8. `id` INT,
    9. `name` VARCHAR(50),
    10. PRIMARY KEY(`id`)
    11. );
    12.  
    13. #第二种方式创建主键可以创建联合主键
    14. CREATE TABLE `student3`(
    15. `classid` INT,
    16. `stuid` INT,
    17. `name` VARCHAR(50),
    18. PRIMARY KEY(`classid`,`stuid`)
    19. );
    20.  
    21. #表创建的时候没有添加主键，可以通过修改操作来添加主键
    22. CREATE TABLE `student4`(
    23. `id` INT ,
    24. `name` VARCHAR(50)
    25. );
    26.  
    27. ALTER TABLE student4 ADD PRIMARY KEY (`id`);

    1.2唯一约束(unique)

    特点：数据不能重复。

    唯一约束UNIQUE,使用UNIQUE修饰的字段，里面的值具有唯一性，只能出现一次

    1. CREATE TABLE `student5`(
    2. `id` INT PRIMARY KEY,
    3. `name` VARCHAR(50) UNIQUE
    4. );

    1.3自动增长列(auto_increment) （在MySQL中，是auto_increment）

     在其他数据库是另一种写法：

    sqlserver数据库 (identity)  oracle数据库( sequence)

    给主键添加自动增长的数值，列只能是整数类型

    1. CREATE TABLE student(
    2. Id int primary key auto_increment,
    3. Name varchar(50)
    4. );

     

    2.域完整性

    域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

    域代表当前单元格

    域完整性约束：数据类型 非空约束（not null） 默认值约束(default) 

    check约束（mysql不支持）check(sex='男' or  sex='女')

    1.2 非空约束：not null

    1. CREATE TABLE student(
    2. Id int pirmary key,
    3. Name varchar(50) not null,
    4. Sex varchar(10)
    5. );

    1.3 默认值约束 default

    1. CREATE TABLE student(
    2. Id int pirmary key,
    3. Name varchar(50) not null,
    4. Sex varchar(10) default ‘男’
    5. );

    3.引用完整性（参照完整性)

    外键约束(FOREIGN KEY)

    外键：一个表中某个字段里的数据来源于另一个表中某个字段的数据

    外键列的数据类型一定要与主键的类型一致

    添加外键的第一种方式：两个表创建在创键的时候关联外键，在需要引入外键的表创建外键

    1. CREATE TABLE `student6`(
    2. `sid``student6` INT PRIMARY KEY,
    3. `name` VARCHAR(50) NOT NULL,
    4. `sex` VARCHAR(10) DEFAULT '男'
    5. );
    6.  
    7. CREATE TABLE `score1`(
    8. `id` INT,
    9. `score` INT,
    10. `sid` INT,  -- 外键列的数据类型一定要与主键的类型一致
    11. CONSTRAINT fk_score1_sid FOREIGN KEY(sid) REFERENCES student6 (sid)
    12. );

    添加外键的第二种方式：两个表创建在创键的时候没有关联外键，可以通过修改表来创建外键

    1. CREATE TABLE `student7`(
    2. `sid`INT PRIMARY KEY,
    3. `name` VARCHAR(50) NOT NULL,
    4. `sex` VARCHAR(10) DEFAULT '男'
    5. );
    6. CREATE TABLE `score2`(
    7. `id` INT,
    8. `score` INT,
    9. `sid` INT -- 外键列的数据类型一定要与主键的类型一致
    10. )
    11.  
    12. #将表score2里的sid字段引用外键，关联student7表中的sid字段
    13. ALTER TABLE score2 ADD CONSTRAINT fk_student7_score2 FOREIGN KEY(sid) REFERENCES student7(sid);
    14.

     

    表与表之间的关系

    1. 一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。
    2. 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
    3. 多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

    多表查询

    多表查询有如下几种：

    1.合并结果集；UNION 、  UNION  ALL

    2.连接查询（非常重要）

            内连接  [INNER] JOIN  ON

            外连接  OUTER JOIN ON

                    左外连接 LEFT [OUTER] JOIN

                    右外连接 RIGHT [OUTER] JOIN

                    全外连接（MySQL不支持）FULL JOIN

            自然连接  NATURAL JOIN

    3.子查询（非常重要）

    1.合并结果集

    1. 作用：合并结果集就是把两个select语句的查询结果合并到一起！
    2. 合并结果集的要求：被合并的两个结果：列数、列类型必须相同。
    3. 合并结果集有两种方式：
    1. UNION：去除重复记录
    2. UNION ALL：不去除重复记录
    1. CREATE TABLE `t1`(
    2. `a` INT,
    3. `b` VARCHAR(5)
    4. );
    5.  
    6. CREATE TABLE `t2`(
    7. `c` INT,
    8. `d` VARCHAR(5)
    9. );
    10.  
    11. #UNION：去除两个表中重复的数据
    12. SELECT * FROM t1
    13. UNION
    14. SELECT * FROM t2;
    15.  
    16. #UNION ALL：合并结果集，不去除重复记录
    17. SELECT * FROM t1
    18. UNION ALL
    19. SELECT *FROM t2;

    2.连接查询 （非常重要）

    连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

    连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

    那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

    1. #员工表emp表中有14条数据，部门表dept中有4条数据，这两个表进行连接查询，会产生14*4=56条数据
    2.  
    3. SELECT * FROM emp,dept;
    4.  
    5. #emp表和dept表中有一个相同的字段deptno，可以通过这个相同的字段来进行数据过滤
    6. SELECT * FROM emp,dept WHERE emp.`deptno`=dept.`deptno`;

    给表取别名： AS可以省略

    1. #查询指定列
    2. SELECT empno,ename,dept.`deptno`,loc FROM emp,dept WHERE emp.`deptno` = dept.`deptno`;
    3.  
    4. #给表取别名：当表的名称比较长的时候，书写起来比较费事，这时候你可以给表取一个简单的别名
    5. SELECT e.empno,e.ename,e.deptno,d.dname,d.loc FROM emp AS e,dept AS d WHERE e.`deptno` =d.`deptno`;

     上述SQL语句就是内连接查询，不是SQL标准的内连接查询，是MySQL中的内连接查询

            1）标准内连接查询

    标准内连接特点：查询结果必须满足条件,不满足条件的数据不会显示出来，比如emp表中有一个员工zhangsan是50号部门，但是部门表中没有50号部门，所以zhangsan这条数据不会显示出来

    1. #标准内连接查询
    2. SELECT * FROM emp e INNER JOIN dept d ON e.`deptno`=d.`deptno`;
    3. SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e INNER JOIN dept d ON e.`deptno` = d.`deptno`;

            2）外连接（左连接、右连接）

    外连接的特点：查询出的结果存在不满足条件的可能,可以解决上述内连接中查询不出不满足条件的数据

     外连接分为左外连接和右外连接

    左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

    SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`deptno`=dept.`deptno`;

    右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。 

     

    SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.`deptno` =dept.`deptno`;

            3）自然连接 

    自然连接：不需要我们主动给出两个表中的过滤条件，而是通过MySQL自动给出,要求进行连接查询的两个表中有相同名称和类型的字段

    自然连接也分自然类连接，自然左外连接，自然右外连接

    1. #自然内连接
    2. SELECT * FROM emp NATURAL JOIN dept;
    3. #自然左外连接
    4. SELECT * FROM emp NATURAL LEFT JOIN dept;
    5. #自然右外连接
    6. SELECT * FROM emp NATURAL RIGHT JOIN dept;

    3.子查询

    子查询：一个select语句中包含另一个select语句（select语句嵌套查询）

     

    1. #查询工资高于2500的员工信息
    2. SELECT * FROM emp WHERE sal>2500;
    3. #1.工资高于JONES的员工
    4. #第一步：我们需要知道JONES员工的工资是多少
    5. SELECT sal FROM emp WHERE ename ='JONES'
    6. #第二步：查询工资高于JONES的员工-->查询工资高于2975的员工信息
    7. SELECT * FROM emp WHERE sal>2975;
    8. #将2975替换成查询语句
    9. SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename ='JONES');
    10.  
    11. #查询与SCOTT同一个部门的员工。
    12. #第一步：我们需要知道SCOTT这个员工是哪一个部门的？
    13. SELECT deptno FROM emp WHERE ename ='SCOTT';#20
    14.  
    15. #第二步：查询与SCOTT同一个部门的员工。--->查询20号部门的员工信息
    16. SELECT * FROM emp WHERE deptno = 20;
    17.  
    18. SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename ='SCOTT');

    MySQL数据库的备份与恢复

    1.生成SQL脚本  导出数据

    在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

    　　mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

    现在可以在C盘下找到mydb1.sql文件了！

    注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

    2　执行SQL脚本 恢复数据

    前提：必须先创建数据库名

    执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

    执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

    SOURCE C:\mydb1.sql

    　　注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

    还可以通过下面的方式来执行脚本文件：

    mysql -uroot -p123 mydb1

    mysql –u用户名 –p密码 数据库<要执行脚本文件路径

    这种方式无需登录mysql！

    注意：在CMD下 命令不能加;

    演示的表案例：

    1. #创建数据库Javastudy
    2. CREATE DATABASE IF NOT EXISTS `Javastudy`;
    3. #使用数据库Javastudy
    4. USE Javastudy;
    5. #创建表stu
    6. CREATE TABLE `stu` (
    7. `sid`	CHAR(6),
    8. `sname`	VARCHAR(50),
    9. `age`	INT,
    10. `gender`VARCHAR(50)
    11. );
    12. #向stu表中插入数据
    13. INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
    14. INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
    15. INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
    16. INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
    17. INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
    18. INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
    19. INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
    20. INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
    21. INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
    22. INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
    23. INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
    24.  
    25. #创建表emp
    26. CREATE TABLE emp(
    27. 	empno		INT,
    28. 	ename		VARCHAR(50),
    29. 	job		VARCHAR(50),
    30. 	mgr		INT,
    31. 	hiredate	DATE,
    32. 	sal		DECIMAL(7,2),
    33. 	comm		DECIMAL(7,2),
    34. 	deptno		INT
    35. ) ;
    36. #向emp表中插入数据
    37. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    38. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    39. INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    40. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    41. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    42. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    43. INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    44. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
    45. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    46. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    47. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
    48. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    49. INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
    50. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
    51.  
    52. #创建表dept
    53. CREATE TABLE dept(
    54. 	deptno		INT,
    55. 	dname		VARCHAR(14),
    56. 	loc		VARCHAR(13)
    57. );
    58. #向dept表中插入数据
    59. INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
    60. INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
    61. INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
    62. INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

     

  • 相关阅读:
    [框架设计之道（二）]设备、任务设置及业务流程
    什么是全流程的UI设计？它与单页面的视觉设计有什么区别？
    openGauss3.1.0 版本的gs_stack功能解密
    Vue3.X笔记总结
    中国电信移动物联网发展成果与创新实践 ，干货满满
    中间件 | RPC - [Dubbo]
    Vue3中的Ref与Reactive：深入理解响应式编程
    rabbit的扇出模式（fanout发布订阅）的生产者与消费者使用案例
    Java字符串拼接选择
    【c++】——类和对象(中)——默认成员函数(上)
  • 原文地址：https://blog.csdn.net/qq_51810428/article/details/126649674