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)$";

二、多表查询思路

数据准备

创建部门表：

create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

创建员工信息表：

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum("male","female") not null default "male",
  age int,
  dep_id int
);

插入数据：

insert into dep values
(200,"技术"),
(201,"人力资源"),
(202,"销售"),
(203,"运营"),
(205,"财务");

insert into emp(name,sex,age,dep_id) values
("jason","male",18,200),
("dragon","female",48,201),
("kevin","male",18,201),
("nick","male",28,202),
("owen","male",18,203),
("jerry","female",18,204);

1、实际应用

​ 以上两个表，类似于公司的员工信息，和公司部门表，两张表为关系型表，通过之前的学习，我们了解到想要查询数据可以通过‘select * from 表名’的方式进行查询，其实这个方法也可以一次性查询多张表，方法如下

select * from emp,dep;

​ 由上图可以看出，select 方法一次性读出了两张表，但是表的数据发生了错乱，字段发生了冲突由此我们可以通过指定表名的方式进行查询，避免发生错乱

select * from emp,dep where emp.dep_id=dep.id;