#!/usr/bin/env python
# -*- coding:utf-8 -*-
# book ：id name price author_id
# author:aid name birthday gender
# 作者与书 一对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# author_id int,
# foreign key(author_id) references author(aid)
# )
# 作者与书一对一
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# author_id int unique,
# foreign key(author_id) references author(aid)
# )
# 作者与书多对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# )
# create table book_author(
# id int primary key auto_increment,
# book_id int not null,
# author_id int not null,
# foreign key(book_id) references book(id),
# foreign key(author_id) references author(aid),
# );
where 语句
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 比较运算 > < = >= <= != <>
# 范围筛选
# 多选一 字段名 in (值1,值2，值3)
# 20000,30000,3000,19000,18000,17000
# select * from employee where salary in (20000,30000,3000,19000,18000,17000)
# 在一个模糊的范围里 between 10000 and 20000
# 在一个数值区间 1w-2w之间的所有人的名字
# select emp_name from employee where salary between 10000 and 20000;
# 字符串的模糊查询 like
# 通配符 % 匹配任意长度的任意内容
# 通配符 _ 匹配一个字符长度的任意内容
# 正则匹配 regexp 更加细粒度的匹配的时候
# select * from 表 where 字段 regexp 正则表达式
# select * from employee where emp_name regexp '^j[a-z]{5}'
# 逻辑运算 - 条件的拼接
# 与 and
# 或 or
# 非 not
# select * from employee where salary not in (20000,30000,3000,19000,18000,17000)
# 身份运算 - 关于null is null /is not null
# 查看岗位描述不为NULL的员工信息
# select * from employee where post_comment is not null;
# 查看岗位是teacher且名字是jin开头的员工姓名、年薪
#select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%'
#select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'
group by 语句
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 分组 group by
# select * from employee group by post
# 会把在group by后面的这个字段，也就是post字段中的每一个不同的项都保留下来
# 并且把值是这一项的的所有行归为一组
# 聚合 把很多行的同一个字段进行一些统计，最终的到一个结果
# count(字段) 统计这个字段有多少项
# sum(字段) 统计这个字段对应的数值的和
# avg(字段) 统计这个字段对应的数值的平均值
# min(字段)
# max(字段)
# 分组聚合
# 求各个部门的人数
# select count(*) from employee group by post
# 求公司里 男生 和女生的人数
# select count(id) from employee group by sex
# 求各部门的平均薪资
# 求各部门的平均年龄
# 求各部门年龄最小的
# select post,min(age) from employee group by post
# 求各部门年龄最大的
# 求各部门薪资最高的
# 求各部门薪资最低的
# 求最晚入职的
# 求最早入职的
# 求各部门最晚入职的
# 求各部门最早入职的
# 求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到
# 但是要得到对应的人，就必须通过多表查询
# 总是根据会重复的项来进行分组
# 分组总是和聚合函数一起用 最大 最小 平均 求和 有多少项
having/order by/limit 语句
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# having 条件 # 过滤 组
# 部门人数大于3的部门
# select post from employee group by post having count(*) > 3
# 1.执行顺序 总是先执行where 再执行group by分组
# 所以相关先分组 之后再根据分组做某些条件筛选的时候 where都用不上
# 2.只能用having来完成
# 平均薪资大于10000的部门
# select post from employee group by post having avg(salary) > 10000
# select * from employee having age>18
# order by
# order by 某一个字段 asc; 默认是升序asc 从小到大
# order by 某一个字段 desc; 指定降序排列desc 从大到小
# order by 第一个字段 asc,第二个字段 desc;
# 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列
# limit
# 取前n个 limit n == limit 0,n
# 考试成绩的前三名
# 入职时间最晚的前三个
# 分页 limit m,n 从m+1开始取n个
# 员工展示的网页
# 18个员工
# 每一页展示5个员工
# limit n offset m == limit m,n 从m+1开始取n个
pymysql模块
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
#
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
# database='day40')
# cur = conn.cursor() # 数据库操作符 游标
# # cur.execute('insert into employee(emp_name,sex,age,hire_date) '
# # 'values ("郭凯丰","male",40,20190808)')
# # cur.execute('delete from employee where id = 18')
# conn.commit()
# conn.close()
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
# database='day40')
# cur = conn.cursor(pymysql.cursors.DictCursor) # 数据库操作符 游标
# cur.execute('select * from employee '
# 'where id > 10')
# ret = cur.fetchone()
# print(ret['emp_name'])
# # ret = cur.fetchmany(5)
# ret = cur.fetchall()
# print(ret)
# conn.close()
多表查询
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 多表查询
# 两张表是怎么连在一起的
# select * from emp,department;
# 连表查询
# 把两张表连在一起查
# 内链接 inner join 两张表条件不匹配的项不会出现再结果中
# select * from emp inner join department on emp.dep_id = department.id;
# 外连接
# 左外连接 left join 永远显示全量的左表中的数据
# select * from emp left join department on emp.dep_id = department.id;
# 右外连接 right join 永远显示全量的右表中的数据
# select * from emp right join department on emp.dep_id = department.id;
# 全外连接
# select * from emp left join department on emp.dep_id = department.id
# union
# select * from department right join emp on emp.dep_id = department.id;
# 子查询
# 找技术部门的所有人的姓名
# 先找到部门表技术部门的部门id
# select id from department where name = '技术';
# 再找emp表中部门id = 200
# select name from emp where dep_id = (select id from department where name = '技术');
# 找到技术部门和销售部门所有人的姓名
# 先找到技术部门和销售部门的的部门id
# select id from department where name = '技术' or name='销售'
# 找到emp表中部门id = 200或者202的人名
# select name from emp where dep_id in (select id from department where name = '技术' or name='销售');
# select emp.name from emp inner join department on emp.dep_id = department.id where department.name in ('技术','销售');
# 连接的语法
# select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
# 常用
# 内链接
# 左外链接
# 找技术部门的所有人的姓名
# select * from emp inner join department on emp.dep_id = department.id;
# +----+-----------+--------+------+--------+------+--------------+
# | id | name | sex | age | dep_id | id | name |
# +----+-----------+--------+------+--------+------+--------------+
# | 1 | egon | male | 18 | 200 | 200 | 技术 |
# | 2 | alex | female | 48 | 201 | 201 | 人力资源 |
# | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
# | 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
# | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
# +----+-----------+--------+------+--------+------+--------------+
# select * from emp inner join department on emp.dep_id = department.id where department.name = '技术'
# select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技术'
# 找出年龄大于25岁的员工以及员工所在的部门名称
# select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;
# 根据age的升序顺序来连表查询emp和department
# select * from emp inner join department as d on emp.dep_id = d.id order by age;
# 优先使用连表查询，因为连表查询的效率高
# 练习
# 查询平均年龄在25岁以上的部门名
# 部门名 department表
# select name from department where id in (
# select dep_id from emp group by dep_id having avg(age)>25
# );
# 员工表
# select dep_id,avg(age) from emp group by dep_id;
# select dep_id from emp group by dep_id having avg(age)>25;
# 查看不足1人的部门名(子查询得到的是有人的部门id)
# 查emp表中有哪些部门id
# select dep_id from emp group by dep_id;
# 再看department表中
# select * from department where id not in (???)
# select * from department where id not in (select dep_id from emp group by dep_id);
# 查询大于所有人平均年龄的员工名与年龄
# select * from emp where age>(select avg(age) from emp);
# 查询大于部门内平均年龄的员工名、年龄
# select dep_id,avg(age) from emp group by dep_id;
# select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
# on emp.dep_id = d.dep_id where emp.age > d.avg_age;
这篇博客详细介绍了MySQL中的表间关系，包括使用WHERE子句进行条件筛选，GROUP BY和HAVING子句用于数据分组和过滤，以及ORDER BY和LIMIT子句实现排序和限制返回结果。此外，还探讨了Python的pymysql模块在数据库操作中的应用，并深入讲解了如何执行多表查询，提升数据库查询效率。
扫一扫
4810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
