MySql单表操作

目录

1：单表操作

DML语句：

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

DQL语句：

1：基本查询：

2：条件查询（where）

3：分组查询：（聚合函数）

4：排序查询：

5：分页查询：

6：MySql语句查询案例：

---

1：单表操作

DML语句：

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）

# 1. 为 tb_emp 表的 username, name, gender 字段插入值
# 指定字段添加数据：insert into 表名 (字段名1, 字段名2)  values (值1, 值2);
insert into tb_emp (username, name, gender) values ('Tom','汤姆',1);
# 2. 为 tb_emp 表的 所有字段插入值
# 全部字段添加数据：insert into 表名 values (值1, 值2, ...);
insert into tb_emp values (null,'Ros','654321','肉丝',2,'https://p0.ssl.qhimgs1.com/sdr/400__/t0106b985681269099a.png',1,'2000-01-01',now(),now());
# 3. 批量为 为 tb_emp 表的 username , name , gender 字段插入数据
# 批量添加数据（指定字段）：insert into 表名 (字段名1, 字段名2)  values (值1, 值2), (值1, 值2);
insert into tb_emp(username, name, gender)
            values ('Jerry','杰瑞',1),('Shonw','仁慈',1);

注意点：
1：插入操作的时候，字段需要一一对应
2：插入日期时间数据类型的数据，需要加单引号
3：注意数据范围

修改数据（UPDATE）

# 修改数据表中的内容
-- 1. 将 tb_emp 表的ID为1员工 姓名name字段更新为 '张三'
# 修改数据：update  表名  set  字段名1 = 值1 , 字段名2 = 值2 , .... [ where  条件 ]
update tb_emp set name = '张三' where id = 1;
-- 2. 将 tb_emp 表的所有员工的入职日期更新为 '2010-01-01'
update tb_emp set entrydate = '2021-01-01',create_time = now();

如果想将所有员工的入职日期都修改，则where后面不要有条件。

删除数据（DELETE）

# 删除数据表中的内容
# delete  from  表名  [ where  条件 ]
-- 1. 删除 tb_emp 表中 ID为1的员工
delete from tb_emp where id = 1;
-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;

注意点：
1：和上面一样，where里面没有条件，就是默认删除整张表
2：delete不能删除单独的字段内容，如果需要删除，就用update将字段的值设置成null。

DQL语句：

DQL：DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录
select

1：基本查询：

查询多个字段：select  字段1, 字段2, 字段3  from   表名;
查询所有字段（通配符）：select  *  from   表名;
设置别名：select  字段1  [ as  别名1 ] , 字段2  [ as  别名2 ]   from   表名;
去除重复记录：select  distinct  字段列表  from   表名;

-- 1. 查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp;
-- 2. 查询返回所有字段
# 方式一性能更高更直观
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
select * from tb_emp;
-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略
select name as '姓名',entrydate as '入职日期' from tb_emp;
-- 4. 查询员工有哪几种职位(不要重复) -- distinct
select distinct tb_emp.job from tb_emp;

2：条件查询（where）

条件查询：select  字段列表  from   表名   where   条件列表

比较运算符    功能
>    大于
>=    大于等于
<    小于
<=    小于等于
=    等于
<>  或 !=    不等于
between ... and ...    在某个范围之内(含最小、最大值)
in(...)    在in之后的列表中的值，多选一
like  占位符    模糊匹配(_匹配单个字符, %匹配任意个字符)
is null    是null

逻辑运算符    功能
and  或  &&    并且 (多个条件同时成立)
or  或  ||    或者 (多个条件任意一个成立)
not  或  !    非 , 不是

例题
-- 1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';
-- 2. 查询在 id小于等于5 的员工信息
select * from tb_emp where id<5;
-- 3. 查询 没有分配职位 的员工信息  -- 判断 null , 用 is null
select * from tb_emp where job is null;
-- 4. 查询 有职位 的员工信息  -- 判断 不是null , 用 is not null
select * from tb_emp where job is not null;
-- 5. 查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password != '123456';
-- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' && gender = 1;
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job = 2||job = 3||job = 4;
-- 9. 查询姓名为两个字的员工信息
select * from tb_emp where name like '___';
-- 10. 查询姓 '张' 的员工信息  ---------> 张%
select * from tb_emp where name like '张%';
-- 11. 查询姓名中包含 '三' 的员工信息
select * from tb_emp where name like '%三%';

3：分组查询：（聚合函数）

聚合函数：
介绍：将一列数据作为一个整体，进行纵向计算。
语法：select  聚合函数(字段列表)  from   表名
count：统计数量
max：最大值
min：最小值
avg：平均值
sum：求和

注意点：
null值不参与所有聚合函数运算。
统计数量可以使用：count(*)   count(字段)   count(常量)，推荐使用count(*)。

例题：
# 聚合函数 : 不对null值进行计算
/*举个例子：就是如果你用count（id），就能统计出全部的员工总数，因为每个人都有id
如果你用count（job），显示的结果就不一定了，因为如果有人没工作（job is null），记录就会少*/
# 1. 统计该企业员工数量 -- count
-- A. count(字段)
select count(id) from tb_emp;
-- B. count(*)
select count(*) from tb_emp;
-- C. count(值)
-- 2. 统计该企业员工 ID 的平均值
select avg(id) from tb_emp;
-- 3. 统计该企业最早入职的员工的入职日期
select min(tb_emp.entrydate) from tb_emp;
-- 4. 统计该企业最近入职的员工的入职日期
select max(tb_emp.entrydate) from tb_emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id) from tb_emp;

分组查询语法：select  字段列表（这个字段列表只能有两个：一个是字段名，一个是聚合函数）  from   表名  [ where   条件 ]  group   by  分组字段名  [ having  分组后过滤条件 ]

例题：
# 分组查询
-- 1. 根据性别分组 , 统计男性和女性员工的数量  -- count
select gender,count(*) from tb_emp group by gender;

看第一个案例：select之后的字段列表里面只包含了字段名，和聚合函数，其实应该是一定要包含字段名，要不然这个分组就没有意义。

-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 -- count
select job,count(*) from tb_emp where entrydate<='2015-01-01' group by job having count(job)>=2;

这句话涉及到了分组之后的过滤条件：having

这里首先提到where和having的区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

从两者的区别看来也能知道：where是在分组之前对数据进行筛选，筛选完的数据才能进行分组，而having是在数据已经筛选完了然后再进行筛选。
执行顺序: where  >  聚合函数 > having 
 

4：排序查询：

条件查询：select  字段列表  from   表名   [ where   条件列表 ] [ group by  分组字段 ] order  by  字段1  排序方式1 , 字段2

ASC：升序（默认值）
DESC：降序

-- 1. 根据入职时间, 对员工进行升序排序  -- 排序条件
select * from tb_emp order by entrydate ASC ;
-- 2. 根据入职时间, 对员工进行降序排序
select * from tb_emp order by entrydate DESC ;
-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select * from tb_emp order by entrydate desc ,update_time DESC ;

第三个例题：当第一个判断条件相同的时候，再进行第二个条件进行比较。

5：分页查询：

select  字段列表  from   表名  limit  起始索引, 查询记录数

例题：
#分页查询
-- 1. 查询第1页员工数据, 每页展示10条记录
select * from tb_emp limit 0,10;
-- 2. 查询第2页员工数据, 每页展示10条记录
select * from tb_emp limit 5,10;

注意点：
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

重点说一下第一个注意点：limit后面的参数是索引
看例题，第一页员工数据，下标索引从0开始。这没什么问题
例题第二题，如果要查询第2页员工数据，并且要从索引开始，
（这个点很难说，因为我一开始把limit后面那个参数理解成了页码，也就是第几页，而不是索引）
所以第二个例题那一行sql语句的意思就是：从第5个索引开始（第五个索引由于mysql索引是从0开始，所以开始展示的是第6条记录，在这一页展示10条记录，也就是到15）。

6：MySql语句查询案例：

#案例测试1
-- 按需求完成员工管理的条件分页查询 查询第一页数据，每页展示10条记录
-- 姓名：张
-- 性别：男
-- 入职时间：2000-01-01  2015-12-31
-- 根据最后修改时间进行倒序排列
select *
from tb_emp
where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;

分析：这个案例其实没有什么好说的，就是按照要求根据学习的查询语法一步一步来就行。

#案例测试2
-- 员工性别信息的统计
select if(gender=1,'男性员工','女性员工') as '性别',count(*) from tb_emp group by gender;

这里用了一个if语句：函数: if(条件表达式 , t , f)，if函数有三个参数：当前面的条件表达式是true时就执行t，false执行f。

#案例测试3
-- 根据员工职位进行信息的统计
select
(case when job = 1 then '班主任' when job = 2 then '讲师' when job = 3 then '教研主管' when job = 4 then '学工主管' else '无职位' end)
as '职位分类',
count(*) from tb_emp group by job;

这里同样有一个case函数：
-- 函数: case when ... then ... when ... then ... else ... end
-- 函数: case ... when ... then ... when ... then ... else ... end
就是应对情况多的时候。