• 【MySQL】表的增删改查(强化)

    作者主页：paper jie_博客

    本文作者：大家好，我是paper jie，感谢你阅读本文，欢迎一建三连哦。

    本文录入于《MySQL》专栏，本专栏是针对于大学生，编程小白精心打造的。笔者用重金(时间和精力)打造，将MySQL基础知识一网打尽，希望可以帮到读者们哦。

    其他专栏：《算法详解》《C语言》《javaSE》《数据结构》等

    内容分享：本期将会分享MySQL表的增删改查的一些奇淫巧技

    目录

    数据库的约束

    约束类型

    null约束

    unique: 唯一约束 

    default: 默认值约束

    primary key: 主键约束

    foreign key: 外键约束

    check约束(MySQL中不使用它)

    表的设计

    三大范式

    一对一

    一对多

    多对多

    新增

    查询

    聚合查询

    聚合函数

    group by 子句

    having

    联合查询

    初始化数据

    内连接

    外连接

    自连接

    子查询

    合并查询

    数据库的约束

    约束类型

    not null: 表示某列不可以存放null

    unique: 保证某列的每行必须是唯一值

    default: 规定没有给列赋值时给的默认值

    primary key: 主键, 保证某列有唯一的标记,且不能为null

    foreign key: 外键, 保证一个表的数据匹配另一个表的值的参照完整性,与匹配表对应

    check: 保证列中的值符合指定的条件,但MySQL数据库堆check子句进行分析的时候会忽略它

    null约束

     创建表的时候,指定列不能为空:

     create table student(id int is not null, name varchar(20));

    unique: 唯一约束 

    创建表的时候,指定列是唯一的,不重复的:

     create table student(id int unique, name varchar(20));

    default: 默认值约束

    创建表的时候,指定列为空,则插入指定默认值:

    create table student(id int, name varchar(20) default '无名氏');

    primary key: 主键约束

    指定列为主键:

    create table student(id int primary key, mame varchar(20));

    自增主键: auto_increment, 插入对应数据不给值时,使用最大值+1

     create table student(id int primary key auto_increment, mame varchar(20));

    foreign key: 外键约束

    外键用于关键其他的表的主键:

    注意: 外键约束关键的表的对应列得有主键约束才能使用foreign key

    1. create table student(id int primary key auto_increment, mame varchar(20));
    2. create table score(id int, name varchar(20), student_id int,  foreign key (student_id) references student(id));

    check约束(MySQL中不使用它)

    MySQL使用会忽略check:

    create table student(id int, name varchar(30), check(id = 1 or id = 3));

    表的设计

    三大范式

    一对一

     

    一对多

    多对多

    新增

    将一张表已有的数据复制到另一张表中,复制的字段类型需要在被复制表中有的类型

    语法:

    insert into 表名1(类型1,类型2....) select 类型1, 类型2.... from 表名2;

    栗子:

    insert into student1(id,name) select id, name from student2;

    查询

    聚合查询

    聚合函数

    一般常用的函数有sum, avg, max, min, count:

    函数作用
    sum返回查询的行的数据之和,需要是数字
    avg返回查询的行的数据的平均值,需要是数字
    max返回查询的行的数据的最大值,需要是数字
    min返回查询的行的数据的最小值,需要是数字
    count返回查询的行数

    栗子:

    1. mysql> select * from student;
    2. +----+-------+------------+------------------+----------+
    3. | id | sn    | name       | qq_mail          | class_id |
    4. +----+-------+------------+------------------+----------+
    5. |  2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |        1 |
    6. |  3 | 00835 | 菩提老祖   | NULL             |        1 |
    7. |  4 | 00391 | 白素贞     | NULL             |        1 |
    8. |  5 | 00031 | 许仙       | xuxian@qq.com    |        1 |
    9. |  6 | 00054 | 不想毕业   | NULL             |        1 |
    10. |  7 | 51234 | 好好说话   | say@qq.com       |        2 |
    11. |  8 | 83223 | tellme     | NULL             |        2 |
    12. |  9 | 09527 | 老外学中文 | foreigner@qq.com |        2 |
    13. +----+-------+------------+------------------+----------+

    sum:

    1. mysql> select sum(id) from student;
    2. +---------+
    3. | sum(id) |
    4. +---------+
    5. |      44 |
    6. +---------+

    avg:

    1. mysql> select avg(id) from student;
    2. +---------+
    3. | avg(id) |
    4. +---------+
    5. |  5.5000 |
    6. +---------+

    max:

    1. mysql> select max(id) from student;
    2. +---------+
    3. | max(id) |
    4. +---------+
    5. |       9 |
    6. +---------+

    min:

    1. mysql> select min(id) from student;
    2. +---------+
    3. | min(id) |
    4. +---------+
    5. |       2 |
    6. +---------+

    count: 

    1. mysql> select count(*) from student;
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. |        8 |
    6. +----------+

    注意: 这里*是查询全部的行数, 括号内可以指定查询字段,字符为null的不会记录

    1. mysql> select count(qq_mail) from student;
    2. +----------------+
    3. | count(qq_mail) |
    4. +----------------+
    5. |              4 |
    6. +----------------+

    group by 子句

    select使用group by 可以指定列分组查询. 需要满足的条件有: 使用group by 进行分组查询的时候,select 指定的字段得是分组依据的字段, 其他字段要是想出现在select中得包含在聚合函数中

    下面栗子中需要查询的数据:

    1. mysql> select * from score;
    2. +-------+------------+-----------+
    3. | score | student_id | course_id |
    4. +-------+------------+-----------+
    5. |    70 |          1 |         1 |
    6. |    98 |          1 |         3 |
    7. |    33 |          1 |         5 |
    8. |    98 |          1 |         6 |
    9. |    60 |          2 |         1 |
    10. |    59 |          2 |         5 |
    11. |    33 |          3 |         1 |
    12. |    68 |          3 |         3 |
    13. |    99 |          3 |         5 |
    14. |    67 |          4 |         1 |
    15. |    23 |          4 |         3 |
    16. |    56 |          4 |         5 |
    17. |    72 |          4 |         6 |
    18. |    81 |          5 |         1 |
    19. |    37 |          5 |         5 |
    20. |    56 |          6 |         2 |
    21. |    43 |          6 |         4 |
    22. |    79 |          6 |         6 |
    23. |    80 |          7 |         2 |
    24. |    92 |          7 |         6 |
    25. +-------+------------+-----------+

    栗子:

    1. mysql> select student_id, sum(score) from score group by student_id;
    2. +------------+------------+
    3. | student_id | sum(score) |
    4. +------------+------------+
    5. |          1 |        299 |
    6. |          2 |        119 |
    7. |          3 |        200 |
    8. |          4 |        218 |
    9. |          5 |        118 |
    10. |          6 |        178 |
    11. |          7 |        172 |
    12. +------------+------------+

    having

    分组后需要对结果进行筛选,不能使用where语句,需要使用having

    栗子:

    1. mysql> select student_id, sum(score) from score group by student_id having sum(score) < 200;
    2. +------------+------------+
    3. | student_id | sum(score) |
    4. +------------+------------+
    5. |          2 |        119 |
    6. |          5 |        118 |
    7. |          6 |        178 |
    8. |          7 |        172 |
    9. +------------+------------+

    联合查询

    在开发中会有很多张表存放数据,这里我们就需要联合查询.联合查询就是对多张表进行笛卡尔积:

    初始化数据

    1. mysql> select * from student;
    2. +----+-------+------------+------------------+----------+
    3. | id | sn    | name       | qq_mail          | class_id |
    4. +----+-------+------------+------------------+----------+
    5. |  2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |        1 |
    6. |  3 | 00835 | 菩提老祖   | NULL             |        1 |
    7. |  4 | 00391 | 白素贞     | NULL             |        1 |
    8. |  5 | 00031 | 许仙       | xuxian@qq.com    |        1 |
    9. |  6 | 00054 | 不想毕业   | NULL             |        1 |
    10. |  7 | 51234 | 好好说话   | say@qq.com       |        2 |
    11. |  8 | 83223 | tellme     | NULL             |        2 |
    12. |  9 | 09527 | 老外学中文 | foreigner@qq.com |        2 |
    13. +----+-------+------------+------------------+----------+
    1. mysql> select * from classes;
    2. +---------+-------------------+-----------------------------------------------+
    3. | classID | name              | desc                                          |
    4. +---------+-------------------+-----------------------------------------------+
    5. |       1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法     |
    6. |       2 | 中文系2019级3班   | 学习了中国传统文学                              |
    7. |       3 | 自动化2019级5班   | 学习了机械自动化                                |
    8. +---------+-------------------+-----------------------------------------------+
    1. mysql> select * from score;
    2. +-------+------------+-----------+
    3. | score | student_id | course_id |
    4. +-------+------------+-----------+
    5. |    70 |          1 |         1 |
    6. |    98 |          1 |         3 |
    7. |    33 |          1 |         5 |
    8. |    98 |          1 |         6 |
    9. |    60 |          2 |         1 |
    10. |    59 |          2 |         5 |
    11. |    33 |          3 |         1 |
    12. |    68 |          3 |         3 |
    13. |    99 |          3 |         5 |
    14. |    67 |          4 |         1 |
    15. |    23 |          4 |         3 |
    16. |    56 |          4 |         5 |
    17. |    72 |          4 |         6 |
    18. |    81 |          5 |         1 |
    19. |    37 |          5 |         5 |
    20. |    56 |          6 |         2 |
    21. |    43 |          6 |         4 |
    22. |    79 |          6 |         6 |
    23. |    80 |          7 |         2 |
    24. |    92 |          7 |         6 |
    25. +-------+------------+-----------+

     

    1. mysql> select * from course;
    2. +----+--------------+
    3. | id | name         |
    4. +----+--------------+
    5. |  1 | Java         |
    6. |  2 | 中国传统文化 |
    7. |  3 | 计算机原理   |
    8. |  4 | 语文         |
    9. |  5 | 高阶数学     |
    10. |  6 | 英文         |
    11. +----+--------------+

    内连接

    使用方式: 

    select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
    select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

    1. mysql> select * from student,score where student.id = score.student_id and student.name = '菩提老祖';
    2. +----+-------+----------+---------+----------+-------+------------+-----------+
    3. | id | sn    | name     | qq_mail | class_id | score | student_id | course_id |
    4. +----+-------+----------+---------+----------+-------+------------+-----------+
    5. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    33 |          3 |         1 |
    6. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    68 |          3 |         3 |
    7. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    99 |          3 |         5 |
    8. +----+-------+----------+---------+----------+-------+------------+-----------+

     

    1. mysql> select * from student join score on student.id = score.student_id and student.name = '菩提老祖';
    2. +----+-------+----------+---------+----------+-------+------------+-----------+
    3. | id | sn    | name     | qq_mail | class_id | score | student_id | course_id |
    4. +----+-------+----------+---------+----------+-------+------------+-----------+
    5. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    33 |          3 |         1 |
    6. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    68 |          3 |         3 |
    7. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    99 |          3 |         5 |
    8. +----+-------+----------+---------+----------+-------+------------+-----------+

    外连接

    外连接分为左外连接和右外连接.联合查询中左侧的表完全显示为左外连接,右侧完全显示为有外连接

    使用方法:

    -- 左外连接，表1完全显示
    select 字段名 from 表名1 left join 表名2 on 连接条件;
    -- 右外连接，表2完全显示
    select 字段 from 表名1 right join 表名2 on 连接条件;

    1. mysql> select * from student left join score on student.id = score.student_id;
    2. +----+-------+------------+------------------+----------+-------+------------+-----------+
    3. | id | sn    | name       | qq_mail          | class_id | score | student_id | course_id |
    4. +----+-------+------------+------------------+----------+-------+------------+-----------+
    5. |  2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |        1 |    60 |          2 |         1 |
    6. |  2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |        1 |    59 |          2 |         5 |
    7. |  3 | 00835 | 菩提老祖   | NULL             |        1 |    33 |          3 |         1 |
    8. |  3 | 00835 | 菩提老祖   | NULL             |        1 |    68 |          3 |         3 |
    9. |  3 | 00835 | 菩提老祖   | NULL             |        1 |    99 |          3 |         5 |
    10. |  4 | 00391 | 白素贞     | NULL             |        1 |    67 |          4 |         1 |
    11. |  4 | 00391 | 白素贞     | NULL             |        1 |    23 |          4 |         3 |
    12. |  4 | 00391 | 白素贞     | NULL             |        1 |    56 |          4 |         5 |
    13. |  4 | 00391 | 白素贞     | NULL             |        1 |    72 |          4 |         6 |
    14. |  5 | 00031 | 许仙       | xuxian@qq.com    |        1 |    81 |          5 |         1 |
    15. |  5 | 00031 | 许仙       | xuxian@qq.com    |        1 |    37 |          5 |         5 |
    16. |  6 | 00054 | 不想毕业   | NULL             |        1 |    56 |          6 |         2 |
    17. |  6 | 00054 | 不想毕业   | NULL             |        1 |    43 |          6 |         4 |
    18. |  6 | 00054 | 不想毕业   | NULL             |        1 |    79 |          6 |         6 |
    19. |  7 | 51234 | 好好说话   | say@qq.com       |        2 |    80 |          7 |         2 |
    20. |  7 | 51234 | 好好说话   | say@qq.com       |        2 |    92 |          7 |         6 |
    21. |  8 | 83223 | tellme     | NULL             |        2 |  NULL |       NULL |      NULL |
    22. |  9 | 09527 | 老外学中文 | foreigner@qq.com |        2 |  NULL |       NULL |      NULL |
    23. +----+-------+------------+------------------+----------+-------+------------+-----------+
    1. mysql> select * from student right join score on student.id = score.student_id;
    2. +------+-------+------------+-----------------+----------+-------+------------+-----------+
    3. | id   | sn    | name       | qq_mail         | class_id | score | student_id | course_id |
    4. +------+-------+------------+-----------------+----------+-------+------------+-----------+
    5. | NULL | NULL  | NULL       | NULL            |     NULL |    70 |          1 |         1 |
    6. | NULL | NULL  | NULL       | NULL            |     NULL |    98 |          1 |         3 |
    7. | NULL | NULL  | NULL       | NULL            |     NULL |    33 |          1 |         5 |
    8. | NULL | NULL  | NULL       | NULL            |     NULL |    98 |          1 |         6 |
    9. |    2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |        1 |    60 |          2 |         1 |
    10. |    2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |        1 |    59 |          2 |         5 |
    11. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    33 |          3 |         1 |
    12. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    68 |          3 |         3 |
    13. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    99 |          3 |         5 |
    14. |    4 | 00391 | 白素贞     | NULL            |        1 |    67 |          4 |         1 |
    15. |    4 | 00391 | 白素贞     | NULL            |        1 |    23 |          4 |         3 |
    16. |    4 | 00391 | 白素贞     | NULL            |        1 |    56 |          4 |         5 |
    17. |    4 | 00391 | 白素贞     | NULL            |        1 |    72 |          4 |         6 |
    18. |    5 | 00031 | 许仙       | xuxian@qq.com   |        1 |    81 |          5 |         1 |
    19. |    5 | 00031 | 许仙       | xuxian@qq.com   |        1 |    37 |          5 |         5 |
    20. |    6 | 00054 | 不想毕业   | NULL            |        1 |    56 |          6 |         2 |
    21. |    6 | 00054 | 不想毕业   | NULL            |        1 |    43 |          6 |         4 |
    22. |    6 | 00054 | 不想毕业   | NULL            |        1 |    79 |          6 |         6 |
    23. |    7 | 51234 | 好好说话   | say@qq.com      |        2 |    80 |          7 |         2 |
    24. |    7 | 51234 | 好好说话   | say@qq.com      |        2 |    92 |          7 |         6 |
    25. +------+-------+------------+-----------------+----------+-------+------------+-----------+

    自连接

    自连接就是自己与自己连接查询.

    1. mysql> select * from course c1, course c2 where c1.name = c2.name;
    2. +----+--------------+----+--------------+
    3. | id | name         | id | name         |
    4. +----+--------------+----+--------------+
    5. |  1 | Java         |  1 | Java         |
    6. |  2 | 中国传统文化 |  2 | 中国传统文化 |
    7. |  3 | 计算机原理   |  3 | 计算机原理   |
    8. |  4 | 语文         |  4 | 语文         |
    9. |  5 | 高阶数学     |  5 | 高阶数学     |
    10. |  6 | 英文         |  6 | 英文         |
    11. +----+--------------+----+--------------+

    子查询

    子查询是指嵌入在其他sql语句中的select语句 

    1. mysql> select * from student where class_id = (select class_id from student where name = 'tellme');
    2. +----+-------+------------+------------------+----------+
    3. | id | sn    | name       | qq_mail          | class_id |
    4. +----+-------+------------+------------------+----------+
    5. |  7 | 51234 | 好好说话   | say@qq.com       |        2 |
    6. |  8 | 83223 | tellme     | NULL             |        2 |
    7. |  9 | 09527 | 老外学中文 | foreigner@qq.com |        2 |
    8. +----+-------+------------+------------------+----------+

    合并查询

    将多个类型一样的表合并

    1. mysql> select * from course where id < 2 union select * from course where id = 4;
    2. +----+------+
    3. | id | name |
    4. +----+------+
    5. |  1 | Java |
    6. |  4 | 语文 |
    7. +----+------+

  • 相关阅读:
    Feign远程调用
    Postgres-on conflict do 引起的core宕问题
    【微信开发第一章】SpringBoot实现微信公众号创建菜单，同步菜单功能
    Python基础之生成器
    【手把手带你学会KMP算法】
    golang 自动生成文件头
    0.5 如何运行Python代码
    两个坑：Integer对象比较，MySql中in()、not in()为空
    Vue——使用内置组件Keep-alive
    IS-IS实验总结 （下）
  • 原文地址：https://blog.csdn.net/paperjie/article/details/134335377