Mysql(四)---增删查改(进阶)

---

前言

上一篇博客，我们学习了一些主键的概念，并且分别创造了一些示例表，下面我们添加训练案例，来对增删改查进行全面的学习。
数据案例:位于大松鼠的gitee码云中：https://gitee.com/sun-meng-yu/csdn—mysql-database
将data.sql文件一运行，就会出现6个表

---

1.查询操作

语法:

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

1.1.全列查询

语法:

select * from 表名

例如：查询 departments 表中的所有数据

select * from department;

1.2.指定列查询

语法:

select 列名[,列名][,列名] from 表名; 

查询 departments 表中所有部门名称。

1.3.列名为表达式查询

在查询中也可以使用算数表达式，来改变数据的显示格式，例如知道了学生的语数英三科成绩，那么正好可以知道三科成绩之和
算数运算符：* / + - (优先级从高到低)
示例：
查询雇员的年薪，并显示他们的雇员ID，名字。(employees的salary是月薪)

select employee_id,Last_name,12 * salary from employees;

由于人数众多，我就不在截全了

示例2：
计算 employees 表中的员工薪水加 100 以后的全年薪水是多少，
并显示他们的员工ID与名字。

select employee_id,Last_name,12 * (salary+100) from employees;

1.4.查询中使用别名

上面12*salary这个列名，不好看，并且咱们也知道代表年薪的意思，那么我们可以使用别名来进行代替。

select 列名 [as] 别名 [,列名 [as] 别名]... from 表名;

示例:
查询 employees 表为表定义别名为emp，将雇员 last_name ,列定
义别名为 name，将12*salary定义为年薪

1.5.去重查询

关键字：distinct

select distinct 列名 from 表

示例：
查询 employees 表，显示唯一的部门 ID

select distinct department_id from employees;

1.6.排序

语法:

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

排序规则：
升序 asc （默认）降序 desc
示例1：
显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对
该列进行升序排序，

select employee_id,last_name,12*salary as annsal from employees order by annsal asc;

可不可以按照两个进行排序，一个是升序，另一个是降序
示例2：
以升序排序显示 DEPARTMENT_ID 列，同时以降序排序显示
SALARY 列

select department_id,salary from employees order by department_id asc,salary desc;

注意
查看表结构中用到了desc(describe缩写)描述
排序中示desc(descend缩写)
在mysql中一个关键字，表示除了两种意思，要尽量杜绝这种现象，注意以后写代码的命名规范。

1.6.2.NULL

如果一行中的某个列缺少数据值，该值被置为 null， 或者说包含一个空。
空是一个难以获得的、未分配的、未知的，或不适用的值。空和 0或者空格不相同。 0 是一个数字，而空格是一个字符。

示例：
计算年薪包含佣金

select department_id,Last_name,commission_pct,12*salary*commission_pct from employees;

NULL和任何值进行计算都是NULL

NULL的特殊之处

1. NULL数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面
2. 不论和什么值进行运算，返回的值都是NULL
3. NULL始终倍判定为FALSE
4. NULL的值不是我们以前学过的其他编程语言中的0，在Mysql中他就是NULL

1.7.条件查询

根据指定的一些条件，过滤到不符合条件的记录，把符合条件的记录返回给用户可以通过一些运算符，比如比较运算符，逻辑运算符等

比较运算符：

运算符	说明
>,<,>=,<=	大于，小于，大于等于，小于等于
=	等于，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)

示例：
1.检验 = 和 <=>

2.查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。

select department_id,location_id from department where department_id = 90;

3,查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪
水。

select last_name from employees where salary >= 3000;

4.查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪
水。

select department_id,last_name,salary from employees where salary between 3000 and 8000;

5.查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字
与薪水。

select department_id,last_name,salary from employees where salary in (5000,6000,8000);

6.查询 employees 中雇员名字第二个字母是 e 的雇员名字

select last_name from employees where last_name like '_e%' ;

7.找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。
IS NULL 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此，你不能用 = ，因为 null 不能等于
或不等于任何值。

select department_id,last_name,commission_pct from employees where commission_pct is null;

8.查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员
名字与薪水。

select last_name,salary from employees where salary = 8000 and last_name like '%e%';

1.8.分页查询

前面在学习select * from 表名;的时候，说过，不加限制记录的查询是不安全的，因此我们引出分页查询
通过分页查询可以有效的控制一次查询出来的结果，
可以有效地减少数据库服务器的压力，同时对用户也比较友好
语法:

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY
LIMIT 开始位置，查询数量;

和

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY
LIMIT 查询数量 OFFSET 开始位置;

offset 表示偏移量，意思是从哪开始读取数据
示例:
1.查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结
果。

select * from departments order by DEPARTMENT_ID limit 0,2;

2.查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查
询，每次返回两条结果。

select * from departments order by DEPARTMENT_ID limit 2 offset 4;

---

2.修改

语法:

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

因为上面的数据库对接下来的联合查询等仍有用处，
为此我们在使用新的数据来完成下面的操作
先运行下面的代码

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,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);

示例：
1.将孙悟空的数学成绩改为80分

update exam set math = 80 where name = '孙悟空';

2.在插入一条学生姓名为孙悟空的数据，然后在执行同样的更新操作

insert into exam values(11,'孙悟空',40,,46,37);
update exam set math = 80 where name = '孙悟空';

谨记：不能写成这个代码

update exam set math = 80;

update操作的时候，如果不加where条件，修改的将是整张表中的所有记录，是非常危险的！
3.将总成绩倒数前三的同学的数学成绩再上30分

那么我们就将总成绩倒数前三的同学的数学成绩再减去30分

update exam set math = math - 30 where math is not null order by (chinese + math + english) limit 3;

---

3.删除

语法：

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

示例：
1.删除孙悟空同学的考试成绩

delete from exam where name = '孙悟空';

2.删除英语成绩倒数前三的同学的所有考试成绩

delete from exam order by english asc limit 3;

谨记：跟update用法一样，如果不加where限制的话，那么整张表都会清空
小技巧：
我们可以在设置一个字段deleteStatus，专门去显示数据是否删除，只需要更改deleteStatus的值，不不用使用delete，就可以完成删除操作。

---

下一篇博客，我们就要介绍联合查询等相关知识点，我们不见不散!