day 47 记录相关操作
1 概要
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
- 使用INSERT实现数据的插入
- UPDATE实现数据的更新
- 使用DELETE实现数据的删除
- 使用SELECT查询数据以及。
本节内容包括：
插入数据
更新数据
删除数据
查询数据
2 插入数据 INSERT
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
3 更新数据 UPDATE
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
4 删除数据 DELETE
语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;
练习：
更新MySQL root用户密码为mysql123
删除除从本地登录的root用户以外的所有用户
5 查询数据
5.1 前期准备
5.1.1 表准备
点击查看代码
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), # 以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2), # 以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), # 以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;
# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象，你可以将字符编码统一设置成GBK
5.1.2 几个重要关键字
几个重要关键字的执行顺序
# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select
"""
虽然执行顺序和书写顺序不一致 ，你在写sql语句的时候可能不知道怎么写，你就按照书写顺序的方式写sql
select * 先用*号占位，之后去补全后面的sql语句，最后将*号替换后你想要的具体字段
"""
5.1.3 简单查询
点击查看代码
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee;
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;
结合CASE语句：
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name
FROM
emp;
5.2 单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
5.2.1 where筛选条件
作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6; 两者等价
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is
select name,post from emp where post_comment = NULL;
select name,post from emp where post_comment is NULL;
5.2.2 group by分组
5.2.2.1 语法
# 分组实际应用场景，分组应用场景非常的多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
# 按照部门分组
select * from emp group by post;
分组之后，最小可操作单位应该是组，还不再是组内的单个数据
上述这条命令在你没有设置严格模式的时候是可正常执行的，返回的是分组之后每个组的第一条数据，但是这不符合分组的规范：分组之后不应该考虑单个数据，而应该以组为操作单位，即分组之后，没办法直接获取组内单个数据
# 如果设置了严格模式，那么上述命令会直接报错。设置严格模式之后分组，默认只能拿到分组的依据，按照什么分组就只能拿到分组 ，其他字段不能直接获取 ，如果想拿到需要借助于一些方法(聚合函数)
set global sql_mode = 'strict_trans_tables,only_full_group_by';
select post from emp group by post;
聚合函数
点击查看代码
"""
什么时候需要分组啊：
出现以下关键字时大概率是要分组：每个、平均、最高、最低
聚合函数
max
min
sum
count
avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名，也可以直接省略不写，但是不推荐，因为省略的话语意不明确，容易错乱
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post; # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; null不行
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat不单单可以支持你获取分组之后的其他字段值，还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
# 7.补充as语法不单单可以给字段起别名，还可以给表临时起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; # 报错
select t1.id,t1.name from emp as t1;
# 8.查询每个人的年薪，12薪
select name,salary*12 from emp;
5.2.2.2 分组注意事项
# 关键字where和group by同时出现的时候，group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; # 报错
select max(salary) from emp; # 不分组 默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
1 先求所有年龄大于30岁的员工
select * from emp where age>30;
2 再对结果进行分组
select * from emp where age>30 group by post;
select post,avg(salary) from emp where age>30 group by post; # 以上两步合起来
5.2.3 having分组之后的筛选条件
"""
having的语法跟where是一致的，只不过having是在分组之后进行的过滤操作，即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工的平均工资，并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000
;
5.2..4 distinct去重
一定要注意，必须是完全一样的数据才可以去重！！！
一定不要将主键忽视了，有主键存在的情况下，是不可能去重的
# 数据
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
# 操作
select distinct id,age from emp;
select distinct age from emp;
5.2..5 order by排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序asc，该asc可以省略不写
也可以修改为降序desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排，如果碰到age相同，则再按照salary升序排
# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary) > 1000
order by avg(salary) desc
;
5.2..6 limit限制展示条数
select * from emp;
"""针对数据过多的情况，我们通常都是做分页处理"""
select * from emp limit 3; # 只展示三条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数
5.2..7 正则
正则是一门独立的语言
在python中如果你想使用正则需要借助于re模块
面试题
1.re模块中常用的方法
findall:分组优先展示
^j.*(n|y)$
不会展示所有正则表达式匹配到的内容
而仅仅展示括号内正则表达式匹配到的内容
match:从头匹配
search:从整体匹配
2.贪婪匹配与非贪婪匹配
正则表达式默认都是贪婪匹配的
将贪婪变成非贪婪只需要在正则表达式后面加?
.* 贪婪
.*? 非贪婪
select * from emp where name regexp '^j.*(n|y)$';
5.3 多表操作
#重点：外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
5.3.1 前期表准备
点击查看代码
#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
5.3.2 表查询
5.3.2.1 交叉连接
不适用任何匹配条件。生成笛卡尔积
点击查看代码
select * from employee,department; # 结果：笛卡尔积。会用一个表中的一条数据对应另一个表中的所有数据
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
select * from emp,dep where emp.dep_id = dep.id;
5.3.2.2 inner join
内连接：只连接匹配的行
MySQL也知道，你在后面查询数据过程中，肯定会经常用到拼表操作
所以特地给你开设了对应的方法
inner join 内连接
left join 左连接
right join 右连接
union 全连接
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分。用一个表中的数据对应另一个表中符合条件的数据，不符合条件的数据扔掉
-------------------------------------------------------------
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
5.3.2.3 left join
外链接之左连接：优先显示左表全部记录
# left join 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来，没有对应的项就用NULL。用一个表中的数据对应另一个表中符合条件的数据，左边表不符合条件的数据加NULL保留
-------------------------------------------------------------
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+----+------------+--------------+
5.3.2.4 right join
外链接之右连接：优先显示右表全部记录
# right join 右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL。用一个表中的数据对应另一个表中符合条件的数据，右边表不符合条件的数据加NULL保留
-------------------------------------------------------------
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
5.3.2.5 union
# union 全连接，左右两表所有的数据都展示出来
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
-------------------------------------------------------------
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
#注意 union与union all的区别：union会去掉相同的纪录
5.3.3 子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当做另外一个查询语句的条件去用
# 查询部门是技术或者人力资源的员工信息
1 先获取部门的id号
2 再去员工表里面筛选出对应的员工
select id from dep where name='技术' or name = '人力资源';
select name from emp where dep_id in (200,201);
select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
总结
表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
"""
多表查询就两种方式
先拼接表再查询
子查询，一步一步来
"""
浙公网安备 33010602011771号