• MySQL数据库表的增删查改

    文章目录

    CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

    一、表的创建

    -- 创建一张学生表
CREATE TABLE students (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 sn INT NOT NULL UNIQUE COMMENT '学号',
 name VARCHAR(20) NOT NULL,
 qq VARCHAR(20)
);

    插入数据的语法：

    INSERT [INTO] table_name[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...

value_list: value, [, value] ...

    1.1 全列插入

    全列插入是指，一次插入一行，每列都赋值。

    mysql> insert into students values(10,1234,'zhangsan',22222);
Query OK, 1 row affected (0.00 sec)

    1.2 指定列插入

    给出指定插入的列名和插入的数据。

    insert into students(sn, name, qq)  values(12345,'lisi',33333);
Query OK, 1 row affected (0.01 sec)

mysql> select * from students ;
+----+-------+----------+-------+
| id | sn    | name     | qq    |
+----+-------+----------+-------+
| 10 |  1234 | zhangsan | 22222 |
| 11 | 12345 | lisi     | 33333 |
+----+-------+----------+-------+
2 rows in set (0.00 sec)

    id是自增的。

    1.3 多行插入

    多行插入在values后面每行用逗号分隔。

    mysql> insert into students values(12, 123456, 'aaa', 1213), (13, 1234567, 'bbb', 123321);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+---------+----------+--------+
| id | sn      | name     | qq     |
+----+---------+----------+--------+
| 10 |    1234 | zhangsan | 22222  |
| 11 |   12345 | lisi     | 33333  |
| 12 |  123456 | aaa      | 1213   |
| 13 | 1234567 | bbb      | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)

    1.4 更新含key行的其他数据

    由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。
    可以选择性的进行同步更新操作。
    语法：

    INSERT ... ON DUPLICATE KEY UPDATE
	column = value [, column = value] ...

    如果冲突，就把要替换的列的值写在后面。

    mysql> select * from students;
+----+---------+----------+--------+
| id | sn      | name     | qq     |
+----+---------+----------+--------+
| 10 |    1234 | zhangsan | 22222  |
| 11 |   12345 | lisi     | 33333  |
| 12 |  123456 | aaa      | 1213   |
| 13 | 1234567 | bbb      | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)

mysql> insert into students(sn, name, qq) values(123456, 'ccc', 44444) on duplicate key update sn=123456, name = 'ccc';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from students;
+----+---------+----------+--------+
| id | sn      | name     | qq     |
+----+---------+----------+--------+
| 10 |    1234 | zhangsan | 22222  |
| 11 |   12345 | lisi     | 33333  |
| 12 |  123456 | ccc      | 1213   |
| 13 | 1234567 | bbb      | 123321 |
+----+---------+----------+--------+
4 rows in set (0.00 sec)

    上面只替换了名字，没有替换qq，因为后面没有写。

    对表执行操作后，都会提示多少行被影响。例如上面这个例子：

    -- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

    1.5 替换

    替换使用关键字replace，其他和插入的用法相同。

    -- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (123456, 'ddd');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

    二、表的读取

    查询是使用最为频繁的，规则也是最多的。

    语法：

    --- 估计的学完后面的具体使用才能看懂这个语法
SELECT  [DISTINCT] {* | {column [, column] ...} [FROM table_name]  [WHERE ...]
[ORDER BY column [ASC | DESC], ...]   LIMIT ...

    2.1 全列查询

    select *from 表名;‘ * '表示当前表的所有列。一般情况不这样查询，用后面的各种查询方法。
    对于字段比较少，并且插入数据比较少的表，可以使用全列查询。如果一个表中字段比较多，或者数据量较大，则全列查询会很慢。

    2.2 指定列查询

    指定列的顺序不需要按定义表的顺序来。

    mysql> select id, name, chinese from exam_result;
+----+-----------+---------+
| id | name      | chinese |
+----+-----------+---------+
|  1 | 唐三藏    |      67 |
|  2 | 孙悟空    |      87 |
|  3 | 猪悟能    |      88 |
|  4 | 曹孟德    |      82 |
|  5 | 刘玄德    |      55 |
|  6 | 孙权      |      70 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)

    2.3 查询字段为表达式

    表达式是任意可以计算，有含义的式子。

    例如：查询每个人的总分。

    mysql> select name, chinese + math + english from exam_result;
+-----------+--------------------------+
| name      | chinese + math + english |
+-----------+--------------------------+
| 唐三藏    |                      221 |
| 孙悟空    |                      242 |
| 猪悟能    |                      276 |
| 曹孟德    |                      233 |
| 刘玄德    |                      185 |
| 孙权      |                      221 |
| 宋公明    |                      170 |
+-----------+--------------------------+
7 rows in set (0.00 sec)

    当表达式太常，后续查询还有使用的时候，可以为某个表达式去别名。在查询的时候，可以为任意字段或者表名取别名，别名只在本查询语句有效。

    语法：表达式 [as] 别名 as 可以省略

    mysql> select name, chinese + math + english as 总分 from exam_result;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
| 曹孟德    |    233 |
| 刘玄德    |    185 |
| 孙权      |    221 |
| 宋公明    |    170 |
+-----------+--------+
7 rows in set (0.00 sec)

    2.4 结果去重

    使用关键字distinct对结果去重。

    mysql> select name, chinese + math + english as 总分 from exam_result;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
| 曹孟德    |    233 |
| 刘玄德    |    185 |
| 孙权      |    221 |
| 宋公明    |    170 |
+-----------+--------+
7 rows in set (0.00 sec)

mysql> select distinct  chinese + math + english as 总分 from exam_result;
+--------+
| 总分   |
+--------+
|    221 |
|    242 |
|    276 |
|    233 |
|    185 |
|    170 |
+--------+
6 rows in set (0.00 sec)

    2.5 where条件查询

    可以使用下列运算符筛选。

    比较运算符：

    运算符说明
    >, >=, <, <=大于，大于等于，小于，小于等于
    =等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
    <=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
    !=, <>不等于
    BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
    IN (option, …)如果是 option 中的任意一个，返回 TRUE(1)
    IS NULL是 NULL
    IS NOT NULL不是 NULL
    LIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

    逻辑运算符：

    运算符说明
    AND多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
    OR任意一个条件为 TRUE(1), 结果为 TRUE(1)
    NOT条件为 TRUE(1)，结果为 FALSE(0)

    例如：

    mysql> select name, chinese from exam_result where chinese < 60;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 刘玄德    |      55 |
+-----------+---------+
1 row in set (0.00 sec)

    mysql> select name, math, english from exam_result where math > 80 and english > 70;
+-----------+------+---------+
| name      | math | english |
+-----------+------+---------+
| 猪悟能    |   98 |      90 |
+-----------+------+---------+
1 row in set (0.00 sec)
    • 从下面这个例子可以看到，在显示字段的地方定义的别名，在where阶段是不知道。说明where条件语句是在先执行。这也符合正常现象，需要显示的内容是筛选之后的，所以先从表里面拿出数据筛选，然后再显示。
    mysql> select name, math + english + chinese as 总分 from exam_result where 总分 > 200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
mysql> select name, math + english + chinese as 总分 from exam_result where math + english + chinese > 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
| 曹孟德    |    233 |
| 孙权      |    221 |
+-----------+--------+
5 rows in set (0.00 sec)

    判断空尽量使用is null 和is not null。

    --- 显示姓孙的人
mysql> select name, math from exam_result where name like '孙%';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
2 rows in set (0.00 sec)

    2.6 结果排序

    将查询到的结果排序。
    注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
    语法：

    -- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];

    例如：

    mysql> select name, math from exam_result order by math; --- 默认按照升序
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

    NULL 视为比任何值都小，升序出现在最上面，降序在最下面。

    • 可以看到，在排序的时候可以使用别名，说明数据已经出来了，然后再排序。
    mysql> select name, math + english + chinese as 总分 from exam_result order by 总分; 
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 宋公明    |    170 |
| 刘玄德    |    185 |
| 唐三藏    |    221 |
| 孙权      |    221 |
| 曹孟德    |    233 |
| 孙悟空    |    242 |
| 猪悟能    |    276 |
+-----------+--------+
7 rows in set (0.00 sec)

    2.7 分页查询

    语法：

    -- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

    注意第三种使用方法的n和s的位置。

    mysql> select name, math + english + chinese as 总分 from exam_result order by 总分 limit 4 offset 2 ;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |    221 |
| 孙权      |    221 |
| 曹孟德    |    233 |
| 孙悟空    |    242 |
+-----------+--------+
4 rows in set (0.00 sec)

    建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

    三、表的更新

    对查询到的结果进行列值更新。

    语法：

    UPDATE table_name SET column = expr [, column = expr ...]
	[WHERE ...] [ORDER BY ...] [LIMIT ...]

    例如：将孙悟空的数学成绩改为87

    mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+
1 row in set (0.00 sec)

mysql> update exam_result set math = 87 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   87 |
+-----------+------+
1 row in set (0.00 sec)

    同样可以更新查询结果的多列的值。

    四、表的删除

    4.1 删除数据

    语法：

    DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

    -- 删除查询结果的数据，按行删除
DELETE FROM exam_result WHERE name = '孙悟空';

    -- 删除整表数据
DELETE FROM table_name;
    • 这种删除，不会将自增值归零。

    4.2 截断表

    注意：这个操作慎用

    语法：

    TRUNCATE [TABLE] table_name
    1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
    2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚。
    3. 会重置 AUTO_INCREMENT 项。
    -- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)

    五、插入查询结果

    其实就是把查询结果作为插入的值。不需要写values。

    语法：

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

    案例：删除表中的的重复复记录，重复的数据只能有一份

    -- 创建原数据表
mysql> create table duplicate_table (id int, name varchar(10));
Query OK, 0 rows affected (0.03 sec)
-- 插入测试数据
mysql> insert into duplicate_table values
    -> (100, 'aaa'),
    -> (100, 'aaa'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
-- mysql> create table no_duplicate_table (id int, name varchar(10));
mysql> create table no_duplicate_table like duplicate_table;  -- 效果和上面的一样
Query OK, 0 rows affected (0.03 sec)

-- 将查询到的不重复数据，插入新表
mysql> insert into no_duplicate_table select distinct id, name from duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 对两张表重命名即可得到不重复数据的表
mysql> rename table duplicate_table to old_duplicate_table, 
    -> no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

    六、聚合函数

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

    聚合函数括号里面的内容应该是查询结果里面有的。

    count 可以统计null行。
    例如：

    统计表的总行数

    mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select max(math) from exam_result;
+-----------+
| max(math) |
+-----------+
|        98 |
+-----------+
1 row in set (0.00 sec)

    七、group by子句

    在select中使用group by 子句可以对指定列进行分组查询

    语法：

    select column1, column2, .. from table group by column;
    • having和group by配合使用，对group by结果进行过滤
      having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

    现在有这样一张表：

    mysql> select * from emp limit 1;
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)
    • 显示每个部门的每种岗位的平均工资和最低工资

    先根据部门号分组，再根据岗位分组，然后计算出每个部门每个岗位聚合值。

    mysql> select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     20 | CLERK     |  950.000000 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)
    • 显示平均工资低于2000的部门和它的平均工资

    统计各个部门的平均工资
    select avg(sal) from EMP group by deptno
    使用having对分组结果过滤（条件筛选）

    mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

    总结下来，语句的执行顺序
    SQL查询中各个关键字的执行先后顺序 from > on > join > where > group by > with > having > select > distinct > order by > limit

    不用刻意去背，可以联想实际筛查的顺序。

  • 相关阅读:
    mysql 8.0 date、datetime time, timestamp的区别
    【MySQL索引与优化篇】InnoDB数据存储结构
    你有没有注意到Java越来越卷了？
    Spring cloud学习笔记（服务注册与发现框架Eureka）
    如何在Ubuntu下安装RabbitMQ服务并异地远程访问？
    深入浅出mybatis分页
    【工具】Java请求带http重定向的地址 自动进行重定向
    学生个人网页设计作品 学生个人网页模板 简单个人主页成品 个人网页制作 HTML学生个人网站作业设计
    【转】OAK-D双目相机进行标定及标定结果说明
    [html] 动态炫彩渐变背景
  • 原文地址：https://blog.csdn.net/qq_52145272/article/details/127034492