Python基础之MySQL数据库
目录
- Python基础之MySQL数据库
- 一、SQL语句常用查询方法
- 前期数据准备
- 1、基本查询
- 2、编写SQL语句的小技巧
- 3、查询之where筛选
- 3、1.功能介绍
- 3、2.实际应用
- 4、查询之group by分组
- 4、1.功能介绍
- 4、2.实际应用
- 5、关查询之having过滤
- 5、1.功能介绍
- 5、2.实际应用
- 6、查询之distinct去重
- 6、1.功能介绍
- 6、2.实际应用
- 7、查询之order by排序
- 7、1.功能介绍
- 7、2.实际应用
- 8、查询之Limit分页
- 8、1.功能介绍
- 8、2.实际应用
- 9、查询之regexp正则
- 9、1.功能介绍
- 9、2.实际应用
- 二、多表查询思路
- 数据准备
- 1、实际应用
一、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 id6;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用来对数据进行二次筛选
- 功能：having的功能和where功能的本质是一样的，都是用来对数据进行筛选
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;