• MySQL表的增删改查（进阶）

    1. 数据库约束

    1.1 约束类型  

    • NOT NULL - 指示某列不能存储 NULL 值。
    • UNIQUE - 保证某列的每行必须有唯一的值。
    • DEFAULT - 规定没有给列赋值时的默认值。
    • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
    • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
    • CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句

    1.2 NULL约束 

    创建表时，可以指定某列不为空：

    -- 重新设置学生表结构
    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
        id INT NOT NULL ,
        sn INT ,
        name VARCHAR ( 20 ),
        qq_mail VARCHAR ( 20 )
    );

    1.3 UNIQUE：唯一约束 

    指定sn列为唯一的、不重复的：

    -- 重新设置学生表结构
    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
        id INT NOT NULL ,
        sn INT UNIQUE,
        name VARCHAR ( 20 ),
        qq_mail VARCHAR ( 20 )
    );

    1.4 DEFAULT：默认值约束  

    该约束是：规定没有给列赋值时的默认值。如：指定插入数据时，name列为空，默认值unkown：

    -- 重新设置学生表结构
    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
        id INT NOT NULL ,
        sn INT UNIQUE,
        name VARCHAR ( 20 ) DEFAULT 'unkown' ,
        qq_mail VARCHAR ( 20 )
    );

    1.5 PRIMARY KEY：主键约束  

    指定id列为主键：

    -- 重新设置学生表结构
    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
        id INT NOT NULL PRIMARY KEY,
        sn INT UNIQUE,
        name VARCHAR ( 20 ) DEFAULT 'unkown' ,
        qq_mail VARCHAR ( 20 )
    );
    对于整数类型的主键，常配搭自增长 auto_increment 来使用。插入数据对应字段不给值时，使用最大值+1 。

    -- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL

    id INT PRIMARY KEY auto_increment,

    1.6 FOREIGN KEY：外键约束 

    外键用于关联其他表的主键或唯一键，语法：

    foreign key (字段名) references 主表(列)  

    案例：

    • 创建班级表classes，id为主键：
    -- 创建班级表，有使用 MySQL 关键字作为字段时，需要使用 `` 来标识
    DROP TABLE IF EXISTS classes;
    CREATE TABLE classes (
        id INT PRIMARY KEY auto_increment,
        name VARCHAR ( 20 ),
       `desc` VARCHAR ( 100 )
    );  
    • 创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id：
    -- 重新设置学生表结构
    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
        id INT PRIMARY KEY auto_increment,
        sn INT UNIQUE,
        name VARCHAR ( 20 ) DEFAULT 'unkown' ,
        qq_mail VARCHAR ( 20 ),
        classes_id int ,
    FOREIGN KEY (classes_id) REFERENCES classes(id)
    );

    1.7 CHECK约束（了解） 

    MySQL使用时不报错，但忽略该约束：  

    drop table if exists test_user;

    create table test_user (
      id int ,
      name varchar ( 20 ),
      sex varchar ( 1 ),
      check (sex = ' 男 ' or sex= ' 女 ' )
    );

    2. 表的设计 

    表的设计三大范式：一对一、一对多、多对多。

    2.1 一对一  

     

    2.2 一对多   

    2.3 多对多     

     

     3. 新增

    插入查询结果，语法：

    INSERT INTO table_name [(column [, column ...])] SELECT ...  

    案例：创建一张用户表，设计有 name 姓名、 email 邮箱、 sex 性别、 mobile 手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name 、 qq_mail。
    -- 创建用户表
    DROP TABLE IF EXISTS test_user;
    CREATE TABLE test_user (
         id INT primary key auto_increment,
         name VARCHAR ( 20 ) comment ' 姓名 ' ,
         age INT comment ' 年龄 ' ,
         email VARCHAR ( 20 ) comment ' 邮箱 ' ,
        sex varchar ( 1 ) comment ' 性别 ' ,
        mobile varchar ( 20 ) comment ' 手机号 '
    );
    -- 将学生表中的所有数据复制到用户表
    insert into test_user(name, email) select name, qq_mail from student;

    4. 查询 

    4.1 聚合查询  

    4.1.1 聚合函数  

    常见的统计总数、计算平均值等操作，可以使用聚合函数来实现，常见的聚合函数有：

    函数说明
    COUNT([DISTINCT] expr)
    返回查询到的数据的数量
    SUM([DISTINCT] expr)返回查询到的数据的总和，不是数字没有意义
    AVG([DISTINCT] expr)返回查询到的数据的平均值，不是数字没有意义
    MAX([DISTINCT] expr)返回查询到的数据的最大值，不是数字没有意义
    MIN([DISTINCT] expr)
    返回查询到的数据的最小值，不是数字没有意义

    举例：

    • COUNT 
    -- 统计班级共有多少同学
    SELECT COUNT (*) FROM student;
    SELECT COUNT ( 0 ) FROM student;
    -- 统计班级收集的 qq_mail 有多少个， qq_mail 为 NULL 的数据不会计入结果
    SELECT COUNT (qq_mail) FROM student;
    • SUM 
    -- 统计数学成绩总分
    SELECT SUM (math) FROM exam_result;
    -- 不及格 < 60 的总分，没有结果，返回 NULL
    SELECT SUM (math) FROM exam_result WHERE math < 60 ;  
    • AVG  
    -- 统计平均总分
    SELECT AVG (chinese + math + english) 平均总分 FROM exam_result;
    • MAX 
    -- 返回英语最高分
    SELECT MAX (english) FROM exam_result;
    • MIN 
    -- 返回 > 70 分以上的数学最低分
    SELECT MIN (math) FROM exam_result WHERE math > 70 ;

    4.1.2 GROUP BY子句  

    SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是 “ 分组依据字段 ” ，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。
    select column1, sum(column2), .. from table group by column1,column3;
    案例：
    • 准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）
    •  查询每个角色的最高工资、最低工资和平均工资（对角色进行分组）
    select role,max(salary),min(salary),avg(salary) from emp group by role;

    4.1.3 HAVING 

    GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING。
    • 显示平均工资低于1500的角色和它的平均工资
    select role,max(salary),min(salary),avg(salary) from emp group by role
    having avg(salary)< 1500 ;

    4.2 联合查询  

    实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积： 

     

    注意：关联查询可以对关联表使用别名。 

     初始化测试数据：

    insert into classes(name, `desc`) values
    ( ' 计算机系 2019 级 1 班 ' , ' 学习了计算机原理、 C 和 Java 语言、数据结构和算法 ' ),
    ( ' 中文系 2019 级 3 班 ' , ' 学习了中国传统文学 ' ),
    ( ' 自动化 2019 级 5 班 ' , ' 学习了机械自动化 ' );
    insert into student(sn, name, qq_mail, classes_id) values
    ( '09982' , ' 黑旋风李逵 ' , 'xuanfeng@qq.com' , 1 ),
    ( '00835' , ' 菩提老祖 ' , null , 1 ),
    ( '00391' , ' 白素贞 ' , null , 1 ),
    ( '00031' , ' 许仙 ' , 'xuxian@qq.com' , 1 ),
    ( '00054' , ' 不想毕业 ' , null , 1 ),
    ( '51234' , ' 好好说话 ' , 'say@qq.com' , 2 ),
    ( '83223' , 'tellme' , null , 2 ),
    ( '09527' , ' 老外学中文 ' , 'foreigner@qq.com' , 2 );
    insert into course(name) values
    ( 'Java' ),( ' 中国传统文化 ' ),( ' 计算机原理 ' ),( ' 语文 ' ),( ' 高阶数学 ' ),( ' 英文 ' );
    insert into score(score, student_id, course_id) values
    -- 黑旋风李逵
    ( 70.5 , 1 , 1 ),( 98.5 , 1 , 3 ),( 33 , 1 , 5 ),( 98 , 1 , 6 ),
    -- 菩提老祖
    ( 60 , 2 , 1 ),( 59.5 , 2 , 5 ),
    -- 白素贞
    ( 33 , 3 , 1 ),( 68 , 3 , 3 ),( 99 , 3 , 5 ),
    -- 许仙
    ( 67 , 4 , 1 ),( 23 , 4 , 3 ),( 56 , 4 , 5 ),( 72 , 4 , 6 ),
    -- 不想毕业
    ( 81 , 5 , 1 ),( 37 , 5 , 5 ),
    -- 好好说话
    ( 56 , 6 , 2 ),( 43 , 6 , 4 ),( 79 , 6 , 6 ),
    -- tellme
    ( 80 , 7 , 2 ),( 92 , 7 , 6 );

    4.2.1 内连接

    语法：
    select 字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 and 其他条件 ;
    select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件;
    案例：
    （ 1 ）查询 “ 许仙 ” 同学的 成绩
    select sco .score from student stu inner join score sco on stu .id =sco .student_id and stu .name = ' 许仙 ' ;
    -- 或者
    select sco .score from student stu, score sco where stu .id =sco .student_id and stu .name = ' 许仙 ' ;
    （ 2 ）查询所有同学的总成绩，及同学的个人信息：
    -- 成绩表对学生表是多对 1 关系，查询总成绩是根据成绩表的同学 id 来进行分组的
    SELECT
      stu .sn ,
      stu .NAME ,
      stu .qq_mail ,
      sum( sco .score )
    FROM
    student stu
    JOIN score sco ON stu .id = sco .student_id
    GROUP BY
    sco .student_id ;
    （ 3 ）查询所有同学的成绩，及同学的个人信息：
    -- 查询出来的都是有成绩的同学， “ 老外学中文 ” 同学 没有显示
    select * from student stu join score sco on stu .id =sco .student_id ;
    -- 学生表、成绩表、课程表 3 张表关联查询
    SELECT
        stu .id ,
        stu .sn ,
        stu .NAME ,
        stu .qq_mail ,
        sco .score ,
        sco .course_id ,
        cou .NAME
    FROM
    student stu
    JOIN score sco ON stu .id = sco .student_id
    JOIN course cou ON sco .course_id = cou .id
    ORDER BY

     stu.id;

    4.2.2 外连接  

    外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
    语法：
    -- 左外连接，表 1 完全显示
    select 字段名   from 表名 1 left join 表名 2 on 连接条件 ;
    -- 右外连接，表 2 完全显示
    select 字段名   from 表名 1 right join 表名 2 on 连接条件 ;

     案例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

    -- “ 老外学中文 ” 同学 没有考试成绩，也显示出来了
    select * from student stu left join score sco on stu .id =sco .student_id ;
    -- 对应的右外连接为：
    select * from score sco right join student stu on stu .id =sco .student_id ;
    -- 学生表、成绩表、课程表 3 张表关联查询
    SELECT
       stu .id ,
       stu .sn ,
       stu .NAME ,
       stu .qq_mail ,
       sco .score ,
       sco .course_id ,
       cou .NAME
    FROM
    student stu
    LEFT JOIN score sco ON stu .id = sco .student_id
    LEFT JOIN course cou ON sco .course_id = cou .id
    ORDER BY
    stu .id

    4.2.3 自连接 

    自连接是指在同一张表连接自身进行查询。 

    案例：
    显示所有 “ 计算机原理 ” 成绩比 “Java” 成绩高的成绩信息
    -- 先查询 “ 计算机原理 ” 和 “Java” 课程的 id
    select id,name from course where name= 'Java' or name= ' 计算机原理 ' ;
    -- 再查询成绩表中， “ 计算机原理 ” 成绩比 “Java” 成绩 好的信息
    SELECT
       s1.*
    FROM
       score s1,
       score s2
    WHERE
    s1 .student_id = s2 .student_id
    AND s1 .score < s2 .score
    AND s1 .course_id = 1
    AND s2 .course_id = 3 ;
    -- 也可以使用 join on 语句来进行自连接查询
    SELECT
       s1.*
    FROM
    score s1
    JOIN score s2 ON s1 .student_id = s2 .student_id
    AND s1 .score < s2 .score
    AND s1 .course_id = 1
    AND s2 .course_id = 3 ;

    4.2.4 子查询 

    子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

    • 单行子查询：返回一行记录的子查询 

    查询与“不想毕业” 同学的同班同学：

    select * from student where classes_id=( select classes_id from student where
    name= ' 不想毕业 ' );
    • 多行子查询：返回多行记录的子查询 

    查询“语文”或“英文”课程的成绩信息 

    1. [NOT] IN关键字 ：

    -- 使用 IN
    select * from score where course_id in ( select id from course where name= ' 语文 ' or name= ' 英文 ' );
    -- 使用 NOT IN
    select * from score where course_id not in ( select id from course where name!= ' 语文 ' and name!= ' 英文 ' );

    2. [NOT] EXISTS关键字： 

    -- 使用 EXISTS
    select * from score sco where exists ( select sco .id from course cou where (name= ' 语文 ' or name= ' 英文 ' ) and cou .id = sco .course_id );
    -- 使用 NOT EXISTS
    select * from score sco where not exists ( select sco .id from course cou where (name!= ' 语文 ' and name!= ' 英文 ' ) and cou .id = sco .course_id );

    4.2.5 合并查询  

    在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union ， union all 。使用 UNION 和UNION ALL 时，前后查询的结果集中，字段需要一致。
    • union
    该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

     案例：查询id小于3，或者名字为“英文”的课程：

     select * from course where id<3

    union
    select * from course where name= ' 英文 ' ;
    -- 或者使用 or 来实现
    select * from course where id< 3 or name= ' 英文';
    • union all
    该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
    案例：查询 id 小于 3 ，或者名字为 “Java” 的课程：

     -- 可以看到结果集中出现重复数据Java

    select * from course where id< 3
    union all
    select * from course where name= ' 英文';

  • 相关阅读:
    python画图技巧——画完一个Excel如何很方便的换另一个Excel来画或者画多个Excel综合处理的数据，以及换线条样式和颜色
    构建AR视频空间大数据平台（物联网及工业互联网、视频、AI场景识别）
    springboot simple （6） springboot thrift
    推荐一款语音识别软件
    热乎的过万字GameFramework讲解笔记文档
    解决使用`npm install`或`npm i`命令之后报`Unexpected token in JSON at position`错误的问题
    Android 日期筛选器
    SpringBoot内置工具类，告别瞎写工具类了
    el-input自动获取焦点
    闭包和类的分析
  • 原文地址：https://blog.csdn.net/m0_61876562/article/details/134320784