Python基础之MySQL数据库

一、SQL语句常用查询方法

前期数据准备

​ 为了更加直观的展示、演示SQL语句查询关键字，需导入下列表格与记录（数据）

​ 模拟公司，导入创建公司员工表，表内包含：ID、姓名、年龄、工作时间、岗位

创建人员表格:

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender 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,gender,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);:``

1、基本查询

• 关键字：select

• 功能：基本、最常用的查询方法，可以通过关键字查询表内所有或指定的数据

select ： 指定需要查询的字段信息
	select *					 查询所有字段
	select 字段名		 		  查询指定字段记录
	select 方法（指定的字段）	  查询处理后的字段记录
    
from 
	指定需要查询的表信息
	from 库名.表名
	from 表名   
   
'''
注意事项：
	1、SQL语句中关键字的执行顺序和编写顺序并不是一致的
		eg:
			select * from t1;
      	我们先写的是select，执行的顺序却是from t1 ——>  select *
	2、对于执行的顺序我们不用过多在意，只需要注意功能，熟练之后会编写的很自然
'''

2、编写SQL语句的小技巧

• 对于查询用法，针对‘select’后面的字段我们可以先使用‘*’占位，然后往后面写，写到需要查询的字段时回来补全

• 在实际应用中‘select’后面很少直接写‘*’ ， 因为星号表示所有，在当前表中数据量非常庞大时会非常浪费数据库资源

• SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要缝缝补补

• 在查询的字段后使用‘as’的方式可以用来修改展示的字段名，不会影响表的结构，只用来当前打印下的展示

3、查询之where筛选

3、1.功能介绍

• 关键字：where
  • 功能：SQL语句中最常用的关键字，用于筛选数据，支持成员运算符，逻辑运算符号、身份运算符、模糊查询
• 模糊查询：
  • 功能：当条件不足时可使用模糊查询，特征搭配模糊查询字符
  • 关键词：like

字符	方法	功能
%	模糊查询	搭配字符前后，匹配任意字符
_	模糊查询	搭配字符前后，匹配单个字符

3、2.实际应用

• 1.查询id大于等于3，小于等于6的数据

1、方式一：
    select * from emp where id>3 and id<=6;
    
2、方式二： 搭配关键词：between
   	select * from emp where id between 3 and 6;

• 2.查询薪资是20000或者18000或者是17000的数据

1、方式一：
	select * from emp where salary=20000 or salary=18000 or salary=17000;
	
2、方式二：
	select * from emp where salary in (20000,18000,17000);

• 3.查询id小于三和大于6的数据

1、方式一：
	select * from emp where id<3 or id>6;

2、方式二：
	select * from emp where id not between 3 and 6;

• 4.查询姓名中包含o的员工姓名和薪资

​ 条件不够时通常使用模糊查询，搭配查询字符

select * from emp where name like %o%;

• 5.查询员工姓名是由四个字符组成的员工姓名与其薪资

1、方式一：
	selsect * from emp where name like '____';
    
2、方式二：
	select * from emp where char_length(name)=4;

• 6.查询岗位描述为空的员工名与岗位名---针对null不能用等号，只能用is

select * from emp where post_comment is null;

4、查询之group by分组

4、1.功能介绍

• 关键词：group by
  • 功能：可以将指定的条件进行分组，例如将多个相同部门的员工按部门分组
  • 可搭配聚合函数：
    • max()：取最大值
    • min()：取最小值
    • sum()：求和
    • avg()：平均值
    • count()：计数

4、2.实际应用

• 1.统计表内公司的总类

​ 在使用，group by 的时候，可能会出现以下这种用法

​ 这种方法在MySQL5.5之前，并不会报错，但是打印的数据并不利于观看，因为它直接将部门的某一行打印出来，我们应该将需要打印的字符名放在selsct后：

​ select post from group by post;

​ 在MySQL5.5之后，使用这种方法将会报错，可以通过在配置内添加严格模式来改正这种方法

# 将下列代码拷贝至my.ini文件的MySQLd下，重启系统环境中的MySQL服务端
	sql_mode='strict_trans_tables,only_full_group_by'

• 2.获取每个部门的最高工资

推导流程：
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资
	select post,max(salary) from emp group by post;

• 3.一次性获取部门薪资的所有数据，最高、最低、平均、薪资总和

推导流程：
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资的综合信息
	select post as '部门' ,
		max(salary) as '最高薪资',
		min(salary) as '最低薪资',
		avg(salary) as '平均薪资',
		sum(salary) as '薪资总和'
       from emp group by post;

• 4.统计每个部门的人数

推导流程：
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资的综合信息
	select post as '部门' ,
		count(id) as '部门人数总合'
       from emp group by post;

• 5.统计每个部门的部门名称及员工姓名
  • 使用group by后，不可直接展示多条字段名，想要展示多条字段名只能使用方法的方式来展示
  • 关键词：group_concat（）
    • 支持在后方阔内填入多条字段名，需要用逗号隔开，同时也支持数据展示自定义

1、打印部门名称及部门下人员姓名
select post,group_concat(name) from emp group by post;

2、打印部门名称，部门下人员姓名、年龄
select post,group_concat(name,age) from emp group by post;

3、自定义输出内容
select post,group_concat('姓名：',name,'年龄：',age) from emp group by post;

5、关查询之having过滤

5、1.功能介绍

• 关键词：having
  • 功能：having的功能和where功能的本质是一样的，都是用来对数据进行筛选
    • 区别是having用来对数据进行二次筛选

5、2.实际应用

• 1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据

推导流程：
	# 1、筛选大于30的员工
	select * from emp where age > 30;
	# 2、对获取的信息进行部门分类
	select post from where age > 30 group by;
	# 3、对获取的信息取工资的平均值
	select post.vag(salary) from emp where age > 30 group bay;
	# 4、对平均工资进行过滤，保留大于10000的数据
	select post,vag(salary) from emp where age > 30 group by having vag(salary)>10000;

6、查询之distinct去重

6、1.功能介绍

• 关键词：distinct

• 功能：可以去除表中重复的数据，但是数据必须要一样才可以，也可以多列使用

  • 多列使用时，满足的条件是多个字段的组合发生重复才会去重

6、2.实际应用

• 去除重复的年龄

1、单列使用
select distinct age from emp;

2、多列使用
select distinct name,age from emp;

7、查询之order by排序

7、1.功能介绍

• 关键词：order by
• 功能：在查看表时，可将指定的字段下的数据排序，支持多列排序（例如先按照年龄排序，再将相同年龄段的用户按照收入排序），默认为升序，可修改为降序
• 升降序
  • 升序：
    • select * from 表名 order by 字段名;
    • select * from 表名 order by 字段名 asc;
  • 降序：
    • select * from 表名 order by 字段名 desc;

7、2.实际应用

• 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

推导流程：
	# 1、先筛选出年龄大于10的人员平均工资
	select avg(salary) from emp where age>10;
	# 2、将各部门人员信息分开
	select post,avg(salary) from emp where age>10 group by post;
	# 3、进行二次筛选，保留平均工资大于1000的部门
	select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000;
	# 4、对工资进行排序
	select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary);

8、查询之Limit分页

8、1.功能介绍

• 关键词：limit
• 功能：当我们再打开一个数据量较为庞大的表时，会占用很多内存，limit可以帮助我们将表分页，再后方阔内输入参数可控制表打开条数，也可支持打开的范围

8、2.实际应用

• 常规用法

1、限制表打开的条数
select * from emp limit 5;

2、控制表打开的范围
select * from emp limit 5,5;

• 查询工资最高的人的信息

推导流程：
	# 1、将表内所有人员的工资进行排序 设置为降序
	select * from empor order by salary desc limit 1;

9、查询之regexp正则

9、1.功能介绍

• 关键词：regexp
• 功能：可以按照指定正则条件，对表内的数据进行搜索，是模糊搜索的一种

9、2.实际应用

• 搜索j开头n或y结尾的用户信息

select * from emp where name regexp '^j.*(y|n)$';