MySQL表的增删改查基础版

这一部分内容比较多，请大家结合目录查看👀

---

一、新增

1.插入

语法：

insert into 表名 values(值,值);

说明：括号中的值要和列的个数和类型进行匹配

• 例：创建一个学生表，包含学号姓名

mysql> create table student(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

2.指定列插入

语法：

insert into 表名(列名，列名……) values(值，值……);

说明：可以指定在某一列中从插入，没被插入的列自动记为NULL，要注意后面的值要和前面的列名对应

• 例

mysql> insert into student(name) values('李四');
Query OK, 1 row affected (0.01 sec)

3.一次插入多行记录

语法：

insert into 表名 values(值,值),(值,值);

说明：一次插入多行记录相比于一次插入一行分多次插入要快不少，类比扔垃圾

• 例

mysql> insert into student values(2,'王五'),(3,'赵六');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

• 也可以结合指定列插入，未被插入的值也是指定为NULL

mysql> insert into student(name) values('小红'),('李华'),('王明');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

二、查询

1.全列查询

把表中所有行和列都查询出来
语法：

select * from 表名;

说明：通常情况下不建议使用 * 进行全列查询

1. 查询出来之后服务器通过网络把这些数据返回给客户端的，并且在客户端以表格形式打印，如果数据库当前这个表中数据特别多，就可能会有问题
2. 会造成堵车的情况：查询时会读取硬盘，把硬盘的IO跑满了，此时程序其他部分想访问硬盘会非常慢；查询时也会操作网络，也可能把网卡的带宽跑满了，此时其他客户端想通过网络访问服务器，也会非常慢，这样的拥堵可能会导致客户端无法顺利访问数据库，进一步的也就对整个系统造成影响
   -例

mysql> select * from student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | 李四   |
|    2 | 王五   |
|    3 | 赵六   |
| NULL | 小红   |
| NULL | 李华   |
| NULL | 王明   |
+------+--------+
7 rows in set (0.00 sec)

2.指定列查询

语法：

select 列名，列名…… from 表名;

• 例

首先我们插入一组测试数据

mysql> INSERT INTO exam_result (id,name, chinese, math, english) VALUES->  (1,'唐三藏', 67, 98, 56),->  (2,'孙悟空', 87.5, 78, 77),->  (3,'猪悟能', 88, 98.5, 90),->  (4,'曹孟德', 82, 84, 67),->  (5,'刘玄德', 55.5, 85, 45),->  (6,'孙权', 70, 73, 78.5),->  (7,'宋公明',75,65,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

进行指定列查询

mysql> select name from exam_result;
+-----------+
| name      |
+-----------+
| 唐三藏    |
| 孙悟空    |
| 猪悟能    |
| 曹孟德    |
| 刘玄德    |
| 孙权      |
| 宋公明    |
+-----------+
7 rows in set (0.00 sec)

3.查询字段为表达式

说明：一边查询，一遍进行计算，在查询的时候，写做用列名构成的表达式，把这一列中的所有行都带入表达式中，参与运算，不会修改数据库服务器上的原始数据，只是在最终响应里的“临时结果”中做了计算

表达式不包含字段

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

表达式包含一个字段

mysql> select id,name,english+10 from exam_result;
+------+-----------+------------+
| id   | name      | english+10 |
+------+-----------+------------+
|    1 | 唐三藏    |       66.0 |
|    2 | 孙悟空    |       87.0 |
|    3 | 猪悟能    |      100.0 |
|    4 | 曹孟德    |       77.0 |
|    5 | 刘玄德    |       55.0 |
|    6 | 孙权      |       88.5 |
|    7 | 宋公明    |       40.0 |
+------+-----------+------------+
7 rows in set (0.01 sec)

表达式包含多个字段

mysql> select id,name,chinese+math+english from exam_result;
+------+-----------+----------------------+
| id   | name      | chinese+math+english |
+------+-----------+----------------------+
|    1 | 唐三藏    |                221.0 |
|    2 | 孙悟空    |                242.5 |
|    3 | 猪悟能    |                276.5 |
|    4 | 曹孟德    |                233.0 |
|    5 | 刘玄德    |                185.5 |
|    6 | 孙权      |                221.5 |
|    7 | 宋公明    |                170.0 |
+------+-----------+----------------------+
7 rows in set (0.00 sec)

4.别名

语法

select 表达式 as 别名 from 表名;

说明：给列/表达式指定别名，表示返回的结果集中，以别名作为该列的名称

• 例

mysql> select id,name,chinese+math+english as 总分  from exam_result;
+------+-----------+--------+
| id   | name      | 总分   |
+------+-----------+--------+
|    1 | 唐三藏    |  221.0 |
|    2 | 孙悟空    |  242.5 |
|    3 | 猪悟能    |  276.5 |
|    4 | 曹孟德    |  233.0 |
|    5 | 刘玄德    |  185.5 |
|    6 | 孙权      |  221.5 |
|    7 | 宋公明    |  170.0 |
+------+-----------+--------+
7 rows in set (0.00 sec)

5.去重

语法：

select distinct 列名 from 表名;

说明：这个操作很低效，值相同的行只保留一个

mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98.0 |
| 78.0 |
| 98.5 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+
7 rows in set (0.00 sec)

6.多列去重

语法：

select distinct 列名，列名 from 表名;

说明：每一列的行都是一致的

mysql> select distinct math,english from exam_result;
+------+---------+
| math | english |
+------+---------+
| 98.0 |    56.0 |
| 78.0 |    77.0 |
| 98.5 |    90.0 |
| 84.0 |    67.0 |
| 85.0 |    45.0 |
| 73.0 |    78.5 |
| 65.0 |    30.0 |
+------+---------+
7 rows in set (0.00 sec)

7.排序

语法：

 select 列名 from 表名 order by 列名 [asc/desc];

说明：asc为升序（从小到大），desc为降序（从大到小），若不写则默认为升序，没有order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

• 例

-- 升序
mysql> select name,math from exam_result order by math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    | 65.0 |
| 孙权      | 73.0 |
| 孙悟空    | 78.0 |
| 曹孟德    | 84.0 |
| 刘玄德    | 85.0 |
| 唐三藏    | 98.0 |
| 猪悟能    | 98.5 |
+-----------+------+
7 rows in set (0.00 sec)
-- 降序
mysql> select name,math from exam_result order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 猪悟能    | 98.5 |
| 唐三藏    | 98.0 |
| 刘玄德    | 85.0 |
| 曹孟德    | 84.0 |
| 孙悟空    | 78.0 |
| 孙权      | 73.0 |
| 宋公明    | 65.0 |
+-----------+------+
7 rows in set (0.00 sec)

注意：NULL数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

注意理解select条件查询的执行顺序

1. 遍历表中的每个记录
2. 把当前记录的值代入条件，根据条件进行筛选
3. 如果这个记录条件成立，就保留，进行列上表达式的计算
4. 如果有order by，会在所有行都被获取到之后再针对所有的结果进行排序

8.使用表达式以及别名排序

• 例：查询同学及总分

mysql> select name,chinese+english+math from exam_result order by chinese+math+english;
+-----------+----------------------+
| name      | chinese+english+math |
+-----------+----------------------+
| 宋公明    |                170.0 |
| 刘玄德    |                185.5 |
| 唐三藏    |                221.0 |
| 孙权      |                221.5 |
| 曹孟德    |                233.0 |
| 孙悟空    |                242.5 |
| 猪悟能    |                276.5 |
+-----------+----------------------+
7 rows in set (0.00 sec)

• 例：使用别名

mysql> select name,chinese+english+math as 总分 from exam_result order by 总分;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 宋公明    |  170.0 |
| 刘玄德    |  185.5 |
| 唐三藏    |  221.0 |
| 孙权      |  221.5 |
| 曹孟德    |  233.0 |
| 孙悟空    |  242.5 |
| 猪悟能    |  276.5 |
+-----------+--------+
7 rows in set (0.00 sec)

9.条件查询

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果是NULL
<=>	等于，NULL安全，例如NULL<=>NULL的结果是TURE(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，结果才是TRUE
OR	任意一个条件为TRUE，结果为TRUE
NOT	条件为TRUE，结果为FALSE

• 注：

1. where条件可以使用表达式，但是不能使用别名
2. and的优先级高于or，在同时使用时，需要使用小括号包裹优先执行的部分

• 一些例子
  基本查询

-- 查询英语不及格的同学及英语成绩
mysql> select name ,english from exam_result where english<60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |    56.0 |
| 刘玄德    |    45.0 |
| 宋公明    |    30.0 |
+-----------+---------+
3 rows in set (0.01 sec)-- 查询语文成绩好于数学成绩的同学
mysql> select name,chinese,math from exam_result where chinese>math;
+-----------+---------+------+
| name      | chinese | math |
+-----------+---------+------+
| 孙悟空    |    87.5 | 78.0 |
| 宋公明    |    75.0 | 65.0 |
+-----------+---------+------+
2 rows in set (0.00 sec)-- 查询总分在200分以下的同学
mysql> select name,english+math+chinese as 总分 from exam_result where chinese+math+english<200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |  185.5 |
| 宋公明    |  170.0 |
+-----------+--------+
2 rows in set (0.00 sec)

and和or

说明：SQL中and的运算符优先级更高

-- 查询语文成绩和英语成绩都大于80的同学
mysql> select name,english,chinese from exam_result where chinese>80 and english>80;
+-----------+---------+---------+
| name      | english | chinese |
+-----------+---------+---------+
| 猪悟能    |    90.0 |    88.0 |
+-----------+---------+---------+
1 row in set (0.00 sec)-- 查询语文成绩大于80或英语成绩大于80的同学
mysql> select name,english,chinese from exam_result where chinese>80 or english>80;
+-----------+---------+---------+
| name      | english | chinese |
+-----------+---------+---------+
| 孙悟空    |    77.0 |    87.5 |
| 猪悟能    |    90.0 |    88.0 |
| 曹孟德    |    67.0 |    82.0 |
+-----------+---------+---------+
3 rows in set (0.00 sec)

范围查询
1.between ……and……

-- 查询语文成绩在[80,90]分的同学成绩
mysql> select name,chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |    87.5 |
| 猪悟能    |    88.0 |
| 曹孟德    |    82.0 |
+-----------+---------+
3 rows in set (0.00 sec)-- 使用and实现上述条件
mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |    87.5 |
| 猪悟能    |    88.0 |
| 曹孟德    |    82.0 |
+-----------+---------+
3 rows in set (0.01 sec)

2.in

-- 查询数学成绩是58或者59或者98或者99分的同学及数学成绩
mysql> select name,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    | 98.0 |
+-----------+------+
1 row in set (0.01 sec)-- 使用or实现
mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    | 98.0 |
+-----------+------+
1 row in set (0.00 sec)

3.LIKE

说明：通配符就是一些特殊的字符，能够表示特定的含义
%：达标任意个任意字符
_：代表一个任意字符
例如：%孙：以孙字结尾，_孙：两个字且以孙字结尾，%孙%：包含孙

-- %匹配任意多个（包括0个）字符
mysql> select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)-- _匹配严格的一个任意字符
mysql> select name from exam_result where name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.01 sec)

4.NULL的查询

-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail is NULL;

10.分页查询

语法：

-- 从0开始，筛选n条结果select * from 表名 limit n;
-- 从s开始，筛选n条结果select * from 表名 limit s,n;
-- 从s开始，筛选n条结果,比第二种方法更明确，建议使用，offset表示偏增量select * from 表名 limit s offset n;

说明：可以限制这次插叙最多能查几条结果，起始下标为0

• 例

mysql> select * from exam_result order by id limit 4;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)mysql> select * from exam_result order by id limit 0 , 3 ;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select * from exam_result order by id limit 1 offset 3 ;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)

三、修改

语法：

update 表名 set 列名=值 where 条件;

说明：可以一次修改多个列，where后的条件限制这次操作具体要修改哪些行数据，若update后不写任何条件，就是针对所有行都进行修改

• 例

mysql> --  将孙悟空的数学成绩变更为80分
mysql> update exam_result set math=80 where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 查看更改后的结果
mysql> select name,math from exam_result where name='孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    | 80.0 |
+-----------+------+
1 row in set (0.00 sec)mysql> -- 将曹孟德的数学成绩变更为60分，语文成绩变更为70分
mysql> update exam_result set math=60,chinese=70 where name = '曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 查看更改后的结果
mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    | 60.0 |    70.0 |
+-----------+------+---------+
1 row in set (0.00 sec)mysql> -- 将总成绩倒数前三的三位同学的数学成绩加上30分
mysql> update exam_result set math=math+10 order by chinese+math+english limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
-- 查看更改后的结果
mysql> select name,math,chinese,english from exam_result order by  math+english+chinese limit 3;
+-----------+------+---------+---------+
| name      | math | chinese | english |
+-----------+------+---------+---------+
| 宋公明    | 75.0 |    75.0 |    30.0 |
| 刘玄德    | 95.0 |    55.5 |    45.0 |
| 曹孟德    | 70.0 |    70.0 |    67.0 |
+-----------+------+---------+---------+
3 rows in set (0.00 sec)mysql> -- 将所有同学的语文成绩加0.5分
mysql> update exam_result set chinese=chinese+0.5;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0
-- 查看修改后的结果
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    99.5 | 98.0 |    56.0 |
|    2 | 孙悟空    |    88.0 | 80.0 |    77.0 |
|    3 | 猪悟能    |    88.5 | 98.5 |    90.0 |
|    4 | 曹孟德    |    70.5 | 70.0 |    67.0 |
|    5 | 刘玄德    |    56.0 | 95.0 |    45.0 |
|    6 | 孙权      |    70.5 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.5 | 75.0 |    30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)

四、删除

语法：

delate from 表名 where 条件/order by/limit;

说明：会把符合条件的行从表中删除掉，这是个危险操作，一旦删除条件没设置好，就可能把不应该删除的给删了

注意！和drop table不一样，drop table是删除了表，也删除了里面的数据，但是delete只删除了表里面的记录，表还在（空表）

• 例

mysql> -- 删除孙悟空同学的考试成绩
mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
-- 查看删除后的结果
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    99.5 | 98.0 |    56.0 |
|    3 | 猪悟能    |    88.5 | 98.5 |    90.0 |
|    4 | 曹孟德    |    70.5 | 70.0 |    67.0 |
|    5 | 刘玄德    |    56.0 | 95.0 |    45.0 |
|    6 | 孙权      |    70.5 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.5 | 75.0 |    30.0 |
+------+-----------+---------+------+---------+
6 rows in set (0.00 sec)-- 删除前两行
mysql> delete from exam_result limit 2;
Query OK, 2 rows affected (0.00 sec)
-- 查看删除后的结果
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    4 | 曹孟德    |    70.5 | 70.0 |    67.0 |
|    5 | 刘玄德    |    56.0 | 95.0 |    45.0 |
|    6 | 孙权      |    70.5 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.5 | 75.0 |    30.0 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)-- 删除整张表
mysql> delete from exam_result;
Query OK, 4 rows affected (0.00 sec)
-- 查看删除后的结果（我们发现表还在，成为了空表）
mysql> select * from exam_result;
Empty set (0.00 sec)