【MySQL】-- 增删改查操作（1）

执行本机的sql脚本：

source sql脚本的绝对路径

或

\. sql脚本的绝对路径

1. CRUD简介

CURD是对数据库中的记录进⾏基本的增删改查操作：
• Create (创建)
• Retrieve (读取)
• Update (更新)
• Delete (删除)

2. Create新增

drop table if exists exam;
create TABLE exam(
id BIGINT,
name VARCHAR(20),
chinese DECIMAL(3, 1),
math DECIMAL(3, 1),
english DECIMAL(3, 1)
);INSERT into exam(id, name, chinese, math, english) VALUES
(1, '唐三藏', 67, 98, 56),
(2, '孙悟空', 87.5, 78, 77),
(3, '猪悟能', 88, 98, 90),
(4, '曹孟德', 67, 98, 56),
(5, '刘玄德', 87.5, 78, 77),
(6, '孙权', 70, 73, 78.5),
(7, '宋公明', 70, 73, 78.5);

2.1 语法

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

2.2 示例

数据库中的users表

2.2.1 单行数据全列插入

value_list中值的数量必须和定义表的列的数量及顺序一致

insert into users VALUES(1, 'zhangtian', '123456');

字符串类型的值用英文的单引号''包裹。

推荐在进行插入时指定字段列表的方式，只要values中的值与前面指定的字段一致即可，和表定义时的字段的顺序无关。

2.2.2 单行数据指定列插入

value_list中值的数量必须和指定列数量及顺序一致

insert into users VALUES(1, 'zhangtian', '123456');

若是有字段没有进行插入，会使用默认值去插入。

2.2.3 多行数据指定列插入

insert into users (name, password) VALUES("buxinyu", '00000')
,('zhangsan', '10213')
,('wanggwu', '123456');

问：一次插入一条数据的效率高还是一次插入多条数据的效率高？

1. 执行所有的SQL都要有网络开销。
2. MySQL数据库在保存数据的时候都有磁盘开销。
3. 每执行一条SQL语句都需要开启一个事务，事务的开启到关闭也需要消耗系统资源。

一次提交多条数据（在一个可控的范围之内），比一次提交一条效率能高那么一点。

3. Retrieeve 检索

3.1 语法

SELECT[DISTINCT]select_expr [, select_expr] ...[FROM table_references][WHERE where_condition][GROUP BY {col_name | expr}, ...][HAVING where_condition][ORDER BY {col_name | expr } [ASC | DESC], ... ][LIMIT {[offset,] row_count | row_count OFFSET offset}]

select * from 表名是非常危险的操作。
因为在生产环境中，一个表中的数据量非常大，有可能是TB级。每一个查询执行的时候会有磁盘开销和网络开销，如果不加限制会把服务器的资源耗尽。

3.2 Select

3.2.1 全列查询

查询所有记录：

SELECT * from exam;

3.2.2 指定列查询

SELECT id, name, chinese from exam;

在select后面的查询列表中指定希望查询的列，可以是一个也可以是多个，中间用逗号隔开，指定列的顺序与表结构中的列的顺序无关。

3.2.3 查询字段为表达式

• 常量表达式

SELECT id, name, chinese, 10 from exam;

• 把所有学生的语文成绩加10分

SELECT id, name, chinese + 10 from exam;

• 计算所有学生语文、数学和英语成绩的总分

SELECT id, name, math + chinese + english from exam;

问：当使用表达式计算新成绩时，原表中的数据有没有被修改？
答：返回的结果集是一个根据查询表列中的字段和表达式生成的一张临时表，专门用来保存查询结果。当结果集返回给客户端时，临时表就被销毁了。

3.2.4 为查询结果指定别名

3.2.4.1 语法

SELECT column [AS] alias_name [, ...] FROM table_name;

• AS可以省略
• 别名如果包含空格必须用单引号包裹
• 建议养成好习惯，每次起别名时都用单引号''包裹

• 为总分这一列指定别名

select id, name, math + chinese + english as 'total' from exam;

3.2.5 结果去重查询

• 查询当前所有的数学成绩

select math FROM exam;

• 在结果集中去除重复记录，可以使用DISTINCT

SELECT DISTINCT math from exam;

• 添加一个id列

select DISTINCT id, math from exam;

使用DISTINCT去重时，只有查询列表中所有列的值都相同才会被判定为重复。

【注意】：

• 查询时不加限制条件会返回表中所有的结果，如果表中的数据量过大，会把服务器的资源消耗殆尽
• 在生产环境中不要使用不加限制条件的查询。

去重操作并不会删除表中原来表中的数据，所有的select操作都不会影响原表中的数据了。

3.3 Where条件查询

3.3.1 语法

 SELECTselect_expr [, select_expr] ... [FROM table_references]WHERE where_condition

3.3.2 比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，对于NULL的比较不安全，比如NULL=NULL结果还是NULL
<=>	等于，对于NULL的比较j是安全的，比如NULL<=>NULL结果是TRUE(1)
!= , <>	不等于
value BETWEEN a0 AND a1	范围匹配，[aO,a1]，如果aO<=value<=a1，返回TRUE或1，NOTBETWEEN则取反
value IN (option.,…)	如果value在optoin列表中，则返回TRUE(1)，NOTIN则取反
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配，%表示任意多个(包括O个)字符；_表示任意一个字符，NOTLIKE则取反

其他的语言中的等于是==，赋值是=，MySQL中判断等于和赋值都是=，在SQL编程中赋值也可以写成:=。

• =

SELECT NULL = NULL;

SELECT NULL <=> NULL;

• LIKE

SELECT * from exam WHERE `name` LIKE '孙%';

SELECT * from exam WHERE `name` like '孙_';

SELECT * from exam WHERE `name` LIKE '孙__';

通配符可以放在查询条件的任何位置，但是会对效率造成影响。

3.3.3 逻辑运算符

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

3.3.4 示例

3.3.4.1 基本查询

• 查询英语不及格的同学及英语成绩（<60）

SELECT id, `name`, english from exam WHERE english < 60;

插入一条英语成绩为null的记录：

insert into exam (id, `name`, chinese, math) VALUES(8, '张飞', 66, 88);

SELECT id, `name`, english from exam WHERE english < 60;

由于NULL比较特殊，查询出来的结果集中没有NULL的记录，会自动过滤掉。

• 查询语文成绩高于英语成绩的同学

SELECT id, `name`, chinese, english from exam WHERE chinese > english;

不能进行跨行比较，只能在一行数据中对两个列进行比较。

先读取表中的每行记录，然后把where条件进行比较，把符合条件的记录放在临时表中，最终返回给客户端。

• ** 总分在200分以下的同学**

SELECT id, `name`, math + chinese + english from exam WHERE math + chinese + english > 200;

起别名

SELECT id, `name`, math + chinese + english as '总分' from exam WHERE math + chinese + english > 200;

where子句不能以别名当作过滤条件

SELECT id, `name`, math + chinese + english as '总分' from exam WHERE '总分' > 200;

sql语句的执行顺序

1. 找到所要查询的表----from
2. 定位符合条件的数据行----where
3. 在过滤后的结果集中抽取要显示的列、表达式。
   所以在执行where子句时，select后边指定的别名还没有定义。

3.3.4.2 AND和OR

• 查询语文成绩大于80分且英语成绩大于80分的同学

select id, `name`, chinese, english from exam where chinese > 80 AND  english > 80;

• ** 查询语文成绩大于80分或英语成绩大于80分的同学**

select id, `name`, chinese, english from exam where chinese > 80 OR  english > 80;

• 观察and和or的优先级

select id, `name`, chinese, english, math from exam where chinese > 80 OR math > 70 AND english > 80;

select id, `name`, chinese, english, math from exam 
where (chinese > 80 OR math > 70) AND english > 80;

NOT > AND > OR
推荐使用小括号把条件包裹起来。

3.3.4.3 范围查询

• ** 语文成绩在[80，90]分的同学**

select id, `name`, chinese from exam where chinese BETWEEN 80 AND 90;

SELECT id, `name`, chinese from exam WHERE chinese >= 80 AND chinese <= 90;

• 数学成绩是78或者79 或者98 或者99分的同学及数学成绩

select id, `name`, math from exam WHERE math = 78 OR math = 79 OR math = 98 OR math = 99;

SELECT id, `name`, math from exam 
WHERE math IN(78, 79, 98, 99);

推荐这种写法

3.3.4.4 模糊查询

%和_都称为通配符。

1. %：可以匹配任意个字符，包括0个。

• %表示所有，等于没有指定条件
• %xxx，表示以xxx结束，前面可以包含任意多个字符。
• xxx%，表示以xxx开头，后面可以包含任意多个字符。
• %xXx%，前面和后面可以包含任意多个字符，中间必须有xxx。

2. _：是一个占位符。

• _表示只有一个字符。
• _xxx，表示以xxx结束，前面可以包含一个字符。
• xxx_，表示以xxx开头，后面可以包含一个字符。
• xxx，前面和后面可以包含一个字符，中间必须是xxx。

• ** 查询所有姓孙的同学**

select * from exam WHERE `name` like '孙%';

模糊查询的条件是一个字符串，要用单引号包裹起来。

• 查询姓孙且姓名是两个字的同学

select * from exam WHERE `name` LIKE '孙_';

3.3.4.5 NULL的查询

• 查询英语成绩为NULL的记录

SELECT * from exam WHERE english <=> NULL;

SELECT * FROM exam WHERE english IS NULL;

• 查询英语成绩不为NULL的记录

SELECT * from exam WHERE english is not NULL;

• NULL与其他值进行运算结果为NULL

SELECT id, `name`, math + chinese + english '总分' FROM exam WHERE `name` = '张飞';

• 过滤NULL时不要使用等于号（=）与不等于号（！=，<>）
• NULL与任何值运算结果都为NULL

3.4 Order by 排序

3.4.1 语法

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

3.4.2 示例

• 按数学成绩从低到高排序（升序）

select id, `name`, math from exam order by math asc;

• ** 按语文成绩从高到低排序（降序）**

select id, `name`, chinese from exam ORDER BY chinese DESC;

• 按英语成绩从高到低排序

SELECT id, `name`, english from exam ORDER BY english DESC;

在排序时，NULL值被认为比任何值都小。

SELECT id, `name`, english from exam ORDER BY english ASC;

• 查询同学各门成绩，依次按数学降序，语文升序，语文升序的方式显示

SELECT * from exam ORDER BY math DESC, english ASC, chinese ASC;

不同的列之间使用逗号隔开。

• ** 查询同学及总分，由高到低排序**

SELECT id, `name`, math + chinese + english total from exam 
ORDER BY math + chinese + english DESC;

• 所有英语成绩不为NULL的同学， 按语文成绩从高到低排序

SELECT id, `name`, english FROM exam 
WHERE english is NOT NULL 
ORDER BY english DESC;

3.5 分页查询

3.5.1 语法

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

3.5.2 示例

SELECT * from exam LIMIT 2;

第0条开始，查询两条。

SELECT * from exam LIMIT 3, 2;

从第3条开始，查询两条

SELECT * from exam LIMIT 100, 2;

如果起始位超过表中记录的范围，执行没有问题，但是会返回一个空结果集。

SELECT * from exam LIMIT 2 OFFSET 5;

要查询的个数是2条，偏移量是5条

页数：数据的总页数 / 每页的条数，如果有余数则+1.
start = （当前页号 - 1）* 每页显示的记录数。