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

雨点打透心脏的1/2处 2024-02-19 11:35 24阅读 0赞

作者主页：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约束

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

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

unique: 唯一约束

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

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

default: 默认值约束

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

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

primary key: 主键约束

指定列为主键:

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

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

  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:

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

表的设计

三大范式

一对一

bcc10f7dbb7b47ce9acc9bd77e02cae8.png

一对多

8eaf18a0706b47bb8ebe29d2f10c0041.png

多对多

4055cf7bce3c4525a3f806869d026f08.png

新增

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

语法:

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

栗子:

  1. 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. +------------+------------+

联合查询

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

d67a9f03503a41a69ab2c98ce6f39738.png

初始化数据

  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. +----+-------+------------+------------------+----------+
  15. mysql> select * from classes;
  16. +---------+-------------------+-----------------------------------------------+
  17. | classID | name              | desc                                          |
  18. +---------+-------------------+-----------------------------------------------+
  19. |       1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法     |
  20. |       2 | 中文系2019级3班   | 学习了中国传统文学                              |
  21. |       3 | 自动化2019级5班   | 学习了机械自动化                                |
  22. +---------+-------------------+-----------------------------------------------+
  24. mysql> select * from score;
  25. +-------+------------+-----------+
  26. | score | student_id | course_id |
  27. +-------+------------+-----------+
  28. |    70 |          1 |         1 |
  29. |    98 |          1 |         3 |
  30. |    33 |          1 |         5 |
  31. |    98 |          1 |         6 |
  32. |    60 |          2 |         1 |
  33. |    59 |          2 |         5 |
  34. |    33 |          3 |         1 |
  35. |    68 |          3 |         3 |
  36. |    99 |          3 |         5 |
  37. |    67 |          4 |         1 |
  38. |    23 |          4 |         3 |
  39. |    56 |          4 |         5 |
  40. |    72 |          4 |         6 |
  41. |    81 |          5 |         1 |
  42. |    37 |          5 |         5 |
  43. |    56 |          6 |         2 |
  44. |    43 |          6 |         4 |
  45. |    79 |          6 |         6 |
  46. |    80 |          7 |         2 |
  47. |    92 |          7 |         6 |
  48. +-------+------------+-----------+
  50. mysql> select * from course;
  51. +----+--------------+
  52. | id | name         |
  53. +----+--------------+
  54. |  1 | Java         |
  55. |  2 | 中国传统文化 |
  56. |  3 | 计算机原理   |
  57. |  4 | 语文         |
  58. |  5 | 高阶数学     |
  59. |  6 | 英文         |
  60. +----+--------------+

内连接

使用方式:

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. +----+-------+----------+---------+----------+-------+------------+-----------+
  10. mysql> select * from student join score on student.id = score.student_id and student.name = '菩提老祖';
  11. +----+-------+----------+---------+----------+-------+------------+-----------+
  12. | id | sn    | name     | qq_mail | class_id | score | student_id | course_id |
  13. +----+-------+----------+---------+----------+-------+------------+-----------+
  14. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    33 |          3 |         1 |
  15. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    68 |          3 |         3 |
  16. |  3 | 00835 | 菩提老祖 | NULL    |        1 |    99 |          3 |         5 |
  17. +----+-------+----------+---------+----------+-------+------------+-----------+

外连接

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

使用方法:

— 左外连接，表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. +----+-------+------------+------------------+----------+-------+------------+-----------+
  25. mysql> select * from student right join score on student.id = score.student_id;
  26. +------+-------+------------+-----------------+----------+-------+------------+-----------+
  27. | id   | sn    | name       | qq_mail         | class_id | score | student_id | course_id |
  28. +------+-------+------------+-----------------+----------+-------+------------+-----------+
  29. | NULL | NULL  | NULL       | NULL            |     NULL |    70 |          1 |         1 |
  30. | NULL | NULL  | NULL       | NULL            |     NULL |    98 |          1 |         3 |
  31. | NULL | NULL  | NULL       | NULL            |     NULL |    33 |          1 |         5 |
  32. | NULL | NULL  | NULL       | NULL            |     NULL |    98 |          1 |         6 |
  33. |    2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |        1 |    60 |          2 |         1 |
  34. |    2 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |        1 |    59 |          2 |         5 |
  35. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    33 |          3 |         1 |
  36. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    68 |          3 |         3 |
  37. |    3 | 00835 | 菩提老祖   | NULL            |        1 |    99 |          3 |         5 |
  38. |    4 | 00391 | 白素贞     | NULL            |        1 |    67 |          4 |         1 |
  39. |    4 | 00391 | 白素贞     | NULL            |        1 |    23 |          4 |         3 |
  40. |    4 | 00391 | 白素贞     | NULL            |        1 |    56 |          4 |         5 |
  41. |    4 | 00391 | 白素贞     | NULL            |        1 |    72 |          4 |         6 |
  42. |    5 | 00031 | 许仙       | xuxian@qq.com   |        1 |    81 |          5 |         1 |
  43. |    5 | 00031 | 许仙       | xuxian@qq.com   |        1 |    37 |          5 |         5 |
  44. |    6 | 00054 | 不想毕业   | NULL            |        1 |    56 |          6 |         2 |
  45. |    6 | 00054 | 不想毕业   | NULL            |        1 |    43 |          6 |         4 |
  46. |    6 | 00054 | 不想毕业   | NULL            |        1 |    79 |          6 |         6 |
  47. |    7 | 51234 | 好好说话   | say@qq.com      |        2 |    80 |          7 |         2 |
  48. |    7 | 51234 | 好好说话   | say@qq.com      |        2 |    92 |          7 |         6 |
  49. +------+-------+------------+-----------------+----------+-------+------------+-----------+

自连接

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

  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. +----+------+

发表评论

表情：
评论列表 （有 0 条评论，24人围观）

还没有评论，来说两句吧...

相关阅读