mysql之表数据的操作

添加数据（INSERT）

-- 语法
-- 1. 指定字段添加数据；insert into 表名(字段1，字段2) values (值1，值2);
-- 2. 全部字段添加数据：insert into 表名 values (值1, 值2, ...);
-- 3. 批量添加数据（指定字段）：insert into 表名(字段1, 字段2) values (值1, 值2), (值1, 值2);
-- 4. 批量添加数据（全部字段）：insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);


-- 例子
-- 1.为tb_emp表的username, name, gender字段插入值；
insert into tb_emp(username, name, gender, create_time, update_time) values ('zhangwuji', '张无忌', 1, now(), now());

-- 2.为tb_emp表的所有字段插入值
insert into tb_emp values(null, 'zhouzhiruo', '123456789', '周芷若', 2, '1.png', 1, '2010-01-01', now(), now());

-- 3.批量为tb_emp表的username, name, gender 字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time) values ('weifuwang', '韦一笑', 1, now(), now()),('jinmaoshiwang', '谢逊', 1, now(), now());

-- 3.批量为tb_emp表的所有字段插入值
insert into tb_emp values
(null, 'zhouzhiruo1', '123456789', '周芷若', 2, '1.png', 1, '2010-01-01', now(), now()),
(null, 'zhouzhiruo2', '123456789', '周芷若', 2, '1.png', 1, '2010-01-01', now(), now());

修改数据（UPDATE）

-- 语法
-- 修改数据：update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件;


-- 例子
-- 1.将tb_emp表的id为1员工 姓名name字段更新为’张三‘;
update tb_emp set name = '张三', update_time = now() where id = 1;

-- 2.将tb_emp表的所有员工的入职日期更新为'2010-01-01'
update tb_emp set entrydate = '2010-01-01', update_time = now();

注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的数据。

删除数据（DELETE）

-- 语法
-- 删除数据：delete from 表名 where 条件;


-- 例子
-- 1.删除tb_emp表中id为1的员工
delete from tb_emp where id=1;

-- 2.删除tb_emp表中的所有员工
delete from tb_emp;

注意事项： 1.delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。2.delete语句不能删除某一个字段的值(如果要操作，可以使用update,将该字段的值置为null)

查询数据（SELECT）

基本查询

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


--例子
-- 1.查询tb_emp表中指定字段name, entrydate并返回
select name, entrydate from tb_emp;

-- 2.查询tb_emp表返回所有字段；
-- 推荐
select id, username, password, name, gender, image_url, job, entrydate, create_time, update_time from tb_emp;
-- 不推荐(不直观，性能低)
select * from tb_emp;

-- 3.查询tb_emp表中所有员工的name,entrydate,并起别名（姓名，入职日期）
select name as 姓名, entrydate as 入职日期 from tb_emp;
-- as  可以省略
select name  姓名, entrydate  入职日期 from tb_emp;

-- 4.查询tb_emp表中已有的员工关联了哪几种职位(不要重复)
select distinct job from tb_emp;

条件查询

-- 语法
-- 条件查询：select 字段列表 from 表名 where 条件列表;

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

逻辑运算符	功能
and 或 &&	并且（多个条件同时成立）
or 或 //（竖的，表格大不了竖的）	或者（多个条件任意一个成立）
not 或 !	非，不是

-- 练习
-- 1. 在tb_emp表中查询姓名为周芷若的员工
select * from tb_emp where name = '周芷若';

-- 2. 在tb_emp表中查询id小于等于8的员工信息
select * from tb_emp where id <= 8;

-- 3. 在tb_emp表中查询没有分配职业的员工信息
select * from tb_emp where job is null;

-- 4. 在tb_emp表中查询有职位的员工信息
select * from tb_emp where job is not null;

-- 5. 在tb_emp表中查询密码不等于‘123456789’的员工信息
select * from tb_emp where password != '123456789';
select * from tb_emp where password <> '123456789';

-- 6. 在tb_emp表中查询入职日期在‘2000-01-01’(包含)到‘2010-01-01(包含)’之间的员工信息
select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

-- 7.  在tb_emp表中查询入职日期在‘2000-01-01’(包含)到‘2010-01-01(包含)’且性别为女的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;

-- 8. 在tb_emp表中查询职位是2（讲师），3（学生主管），4（教研主管）的员工信息
select * from tb_emp where job in (2,3,4);
select * from tb_emp where job = 2 or job = 3 or job = 4;

-- 9. 在tb_emp表中查询姓名为两个字的员工信息
select * from tb_emp where name like '__';

-- 10. 在tb_emp表中查询姓‘张’的员工信息
select * from tb_emp where name like '张%';

-- 11. 在tb_emp表中查询姓‘张’的员工信息
select * from tb_emp where name like '张%';

聚合查询

-- 介绍：将一列数据作为一个整体，进行纵向计算
-- 语法：select 聚合函数(字段列表) 表名;

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

-- 练习
-- 1.统计tb_emp表中员工数量
select count(*) from tb_emp;

-- 2.统计tb_emp表中最早入职的员工
select min(entrydate) as 最早入职 from tb_emp;

-- 3.统计tb_emp表中最迟入职的员工
select max(entrydate) as 最迟入职 from tb_emp;

-- 4.统计tb_emp表中员工id的平均值
select avg(id) as id平均值 from tb_emp;

-- 5.统计tb_emp表中员工的id之和
select sum(id) as id之和 from tb_emp;

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

分组查询

-- 语法
-- 分组查询：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];


-- 练习
-- 1.在tb_emp表中根据性别分组，统计男性和女性员工的数量
select gender,count(*) from tb_emp group by gender;

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

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果精修过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：1.分组之后查询一般为聚合函数和分组字段，查询其他字段无任何意义。2.执行顺序：where > 聚合函数 > having。

排序查询

-- 语法
-- 条件查询；select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1, 字段2 排序方式2...;

-- 排序方式
-- 1. ASC：升序（默认值）
-- 2. DESC：降序

-- 练习
-- 1.在tb_emp表中根据入职时间，对员工进行升序排序
select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate;

-- 2.在tb_emp表中根据入职时间，对员工进行降序排序
select * from tb_emp order by entrydate desc;

-- 3.在tb_emp表中根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
select * from tb_emp order by entrydate, update_time desc;

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

-- 语法
-- 分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数;

-- 练习
-- 1.在tb_emp表中从起始索引0开始查询员工数据，每页展示5条记录
select * from tb_emp limit 0, 3;

-- 2.查询在tb_emp表中第1页员工数据，每页展示5条记录
select * from tb_emp limit 0, 3;

-- 3.查询在tb_emp表中第2页员工数据，每页展示5条记录
select * from tb_emp limit 3, 3;

-- 4.查询在tb_emp表中第3页员工数据，每页展示5条记录
select * from tb_emp limit 6, 3;

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

案例

案例1

-- 按需求完成tb_emp表中的条件分页查询 - 根据输入条件，查询第一页数据，每页展示5条记录
-- 输入条件
    -- 姓名：包含张
    -- 性别：男
    -- 入职时间：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, 5;

案例2

-- 1.在tb_emp表中完成员工性别信息的统计
select if(gender = 1, '男性员工', '女性员工') as '性别', count(*) as '数量' from tb_emp group by gender;

-- 2.在tb_emp表中完成员工职位信息的统计
select case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end as '职位', 
count(*) as '数量' from tb_emp group by job;

• if(表达式,val1,val2) 当表达式为true时取值val1，当表达式为false时取值val2
• case expr when val1 then 'xxx' when val2 then 'xxx' else 'xxx' end