'''
1.所有数据按顺序插入
insert [into] 表名 values (值1, ..., 值n)[, ..., (值1, ..., 值n)];
2.指定字段匹配插入,可以任意顺序
insert [into] 表名(字段2, 字段1, ..., 字段n) values (值2, 值1, ..., 值n)[, ..., (值2, 值1, ..., 值n)];
3.插入查询结果
insert [into] 表1(字段1, ..., 字段n) select 字段1, ..., 字段n from 表2 [条件];
'''# eg: 1
create table t1(idint auto_increment,
x int,
y int,
primary key(id));
insert t1 values (1,2,3),(2,20,30);# 按顺序插入
insert into t1(y, x) values (300,200);# 按规定字段顺序指定插入
create table nt1 like t1;# 复制表即完整结构
insert into nt1 select *from t1;# 复制所有数据
create table tt1(
x int,
z int);
insert into tt1 values (999,888);
insert into nt1(x) select x from tt1;# 将tt1中指定字段插入到nt1中指定的字段
insert into nt1(x, y) select x,z from tt1;# tt1x及z字段的结果赋值给nt1中x,y字段
删语法
'''
1.会记录自增信息,操作会被日志记录,效率低
delete from 表名 [条件];
delete from t1; # 没有条件的情况下是清空所有数据, 但会记录自增信息
insert into t1(x, y) values(6, 66);
2.清空表,会重置自增信息
truncate table 表名;
truncate table nt1;
insert into nt1(x, y) values(6, 66);
'''
改语法
update 表名 set 字段1=值1[,..., 字段n=值n][条件]
update tt1 set x=666;# 无条件, 全改
update tt1 set x=777, z=555 where z<888;# 只修改满足条件的行
查语法
'''
select [distinct] 字段1 [as 别名], ..., 字段n [as 别名] from [库名.]表名
[
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
];
注:
1.查表中所有字段用*表示
2.条件的书写规则严格按照语法顺序书写,可以缺省,但不可以错序
3.约束条件的流程:from -> where -> group by -> having -> distinct -> order by -> limit
4.字段可以起别名
5.字段可以直接做运算 select age + 1 'new_age' from emp;
6.分组后的条件均可以使用聚合函数
''''''
3.
def from():
return "查询的文件"
def where(file):
return "条件筛选后的结果"
def group_by(res):
return "分组后的结果"
def having(res):
return "再次过滤后的结果"
def distinct(res):
return "去重后的结果"
def order_by(res):
return "排序后的结果"
def limit(res):
return "限制条数后的结果"
def select(from=from, where=null, ..., limit=null):
file = from()
res = where(file) if where else file
res = group_by(res) if group_by else res
...
res = limit(res) if limit else res
return res
select(where=where, group_by=group_by)
'''
'''
1.比较运算符
= | < | > | <= | >= | !=
select * from emp where area!="上海";
2.区间运算符
between 10 and 20:10~20
in(10, 20, 30):10或20或30
select * from emp where id between 3 and 5; # [3, 5], 闭合区间,包含3和5, 三行数据
select * from emp where id in(2, 4, 6, 8, 10, 12, 14, 16, 18); # 分离的区间,2,4,6,8,10,12,14都会被显示
3.逻辑运算符
and | or | not
select * from emp where area="山东" and port="济南";
4.相似运算符
like '_owen%':模糊匹配字符串owen,_表示一个字符,%表示任意字符
# 匹配的字段为en,想得到的结果为owen
select * from emp where name like '__en%'; # 在en前可以出现2个任意字符, 之后可以出现0或多个任意字符
'''# 需求:# 查找姓名有数字的员工信息
正则匹配
# why: like完成模糊匹配, 但功能局限, 可以模糊个数, 但不能模糊类型, 正则可以完成类型及个数的模糊匹配'''
语法:字段 regexp '正则表达式'
注:只支持部分正则语法
'''# 完成需求:
select *from emp where name regexp '.*[0-9]+.*';
group by 分组
'''
分组:根据字段相同值形成不同的类别,不明确分组其实整个表就为一个默认大组
原因:把以值共性得到的类别作为考虑单位,不再关系单条记录,而且一组记录
结果:只能考虑组内多条数据的聚会结果(聚合函数结果),分组的字段同样是聚合结果,如:组内的最大最小值
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
聚合函数:
max():最大值
min():最小值
avg():平均值
sum():和
count():记数
group_concat():组内字段拼接,用来查看组内其他字段
eg:1
每个部门的平均薪资
select dep, avg(salary) '平均薪资' from emp group by dep;
eg:2
每个部门都有哪些人
select dep, group_concat(name) from emp group by dep;
需求:
各性别中附属于教学部的最高薪资
select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;
思考:
想知道需求中员工的姓名 => 子查询
上方结果: 男的最高薪资对应的人名, 女的最高薪资对应的人名
# select group_concat(name), max(salary) '最高薪资', gender from emp where dep='教学部' group by gender; 错误
''''''
1. 14条数据部门有3个, 并且每个部分有多条记录, 可以作为分组依据, 同理, 性别也可以
# select * from emp group by dep; # 非分组安全模式下, 可以查询非聚合结果, 显示的是第一条记录, 没有意义, 分组安全模式下不能查询非聚合结果的字段
select dep from emp group by dep;
2. 如果就像以姓名进行分组, 可以, 但没多大意义, 原因name值基本上都不相同, 以组考虑会导致组内大多只要一条记录(自成一组), 组的利用就不是很强烈, 此类分组是无意义的
select name from emp group by name; # 可以分组, 意义不大
考虑的三个问题: 以什么分组(相同数据较多的字段) 分组后的考虑单位(组并非组内的每一条记录) 可以查询的结果(当前分组的字段及聚合函数形成的聚合结果)
'''
解决分组中思考题的过程
# res = select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;# select name from emp where (salary 跟 res作比较)# 一个查询依赖于另一个查询的结果 => 一个查询的结果作为另外一个查询的条件 => 子查询
子查询
'''
子查询:将一条查询结果作为另外一条查询的条件
语法:一条select语句用()包裹得到的结果作为另一条select语句的条件
# 伪sql: select * from emp where salary =|in (select salary from emp where 条件)
单行子查询:
子查询语句的结果为一行数据,可以结合 = | < | > | <= | >= | != 运算符来完成父查询
select salary from emp where salary > 10; # => 作为子查询
# 查询姓名,性别.地区,基于薪资大于10的结果的查询结果
eg: 1
select name, gender, area from emp where salary = (select salary from emp where salary > 10);
多行子查询:
子查询语句的结果为多行数据,可以结合 in | all | any 运算符来完成父查询
in:任意单一值,一次只能考虑子查询中的一个结果
all:全部值,将子查询结果作为一个整体考虑
any:任意多个值:子查询的每一个结果都可以作为参考依据
eg: 2
# 子查询的结果 (9.4, 3)
select name from emp where salary in (select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, 14条数据的salary在(9.4, 3)区域中,就可以完成匹配, 结果为两条(9.4和3那两条)
select * from emp where salary < all(select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, salary要小于(9.4, 3)中的每一个, 反映就是小于3, 结果为薪资1.2,2.4的那四条数据
select * from emp where salary > any(select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, salary大于9.4或大于3的数据均满足条件, 结果就是刨除小于等于3的那几条数据
'''
having 筛选
'''
why:完成在分组之后的筛选
注意:having条件是实现聚合结果层面上的筛选 => 拿聚会结果完成判断
需求:
1.各部门的平均薪资
select dep, avg(salary) '平均薪资' from emp group by dep;
2.平均薪资大于6w的部门(部门与部门的平均薪资)
解决: 以dep进行分组, 以avg(salary)作为判断条件(筛选)
select dep, avg(salary) '平均薪资' from emp group by dep having avg(salary) > 6;
# 总结: having通过聚合函数结果完成筛选
select max(salary) from emp having max(salary) > 9.4;
# 虽然没有明确书写group by, 但在having中使用了聚合函数,所以该查询就将整个表当做一个默认大表来考虑,所以查询的字段只能为聚合函数的结果
'''
order by 排序
'''
why:完成排序
注意:可以使用聚合函数,哪怕没有明确group by
升序 | 降序:asc | desc
eg:order by age desc => 按照年龄降序
select * from emp order by age desc;
需求:
将部门按照部门平均工资降序方式排序
select dep, avg(salary) from emp group by dep order by avg(salary) desc;
'''
limit 限制
'''
why:限制最终结果的数据行数
注意:limit只与数字结合使用
应用:
limit 1:只能显示一行数据
limit 6,5:从第6+1行开始显示5条数据(索引从0开始)
select * from emp limit 1;
select * from emp limit 6,5;
需求:
获得薪资最高的人的一条信息
select * from emp order by salary desc limit 1;
'''