mysql：单表查询，多表查询.

mysql

mysql基本查询语句及方法

	- select
    - distinct
  	- from
    - where
    - group by
    - having
    - limit

连表

		- inner join
		- left join
		- right join
		- union

子连接

单表查询

多表查询

一 单表查询

1、前期表与数据准备

create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex 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,sex,age,hire_date,post,salary,office,depart_id) values
('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('jason','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('大饼','male',18,'19000301','teacher',30000,401,1),
('sean','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);

PS：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

插入表数据

2、语法书写与执行顺序

在写SQL命令是注意两点:

- 书写顺序
  			# 查询id是4-5记录的id与名字
  			- select id, name from emp where id > 3 and id < 6;

 PS: 根据现实生活中图书馆管理员找书的过程: 先知道书在哪个位置，然后再判断要找的是什么书，最后再找书本中的第几页;

执行顺序

	- from  # 找图书馆
    - where  # 书在图书馆中的位置
    - select  # 查找书中的某一页内容

注意: 执行顺序必须要清楚!!!

查询数据量大时，可以在表后面 + \G，修改显示格式;
select * from emp\G  # 不要加分号

3、where约束条件

mysql  : PS: 根据执行顺序来书写 SQL语句，一步一步来写;

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

select * from emp where id >= 3 and id <= 6;

between: 两者之间

and: 与

select * from emp where id between 3 and 6;

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

or: 或者

select * from emp where salary=20000 or salary=18000 or salary=17000;

in: 在什么里

select * from emp where salary in (20000, 18000, 17000);

3.查询员工姓名中包含o字母 的 员工姓名和薪资

select name, salary from emp where name like "%o%";

like: 模糊匹配

%: 匹配0个或多个任意字符

_: 匹配一个任意字符

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

select name, salary from emp where name like "_____";

char_length(字段): 获取字段长度

select name, salary from emp where char_length(name) = 4;

5.查询id小于3或者大于6的数据

select * from emp where id < 3 or id > 6;
select * from emp where id not between 3 and 6;

6.查询薪资不在20000，18000，17000范围的数据

select * from emp where salary not in (20000, 18000, 17000);

7.查询岗位描述为空的员工名与岗位名

注意: 针对null不能用等号，只能用is

select name, post from emp where post_comment = null;
select name, post from emp where post_comment is null;
select name,post from emp where post_comment is not null;

4、group by

group by: 分组

比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等...

1.按部门分组

严格模式下只能获取分组字段post数据, 无法获取其他字段信息，就好比是进程之间数据隔离，但是可以使用聚合函数来获取

聚合函数: max, min, sum, avg, count

非严格模式下不报错

select * from emp group by post;  # 报错
select id, name from emp group by post;  # 报错
select post from emp group by post;

严格模式设置

设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

show variables like "%mode%";
set global sql_mode="strict_trans_tables,only_full_group_by";

2.获取每个部门的最高工资 ----> 画图，讲述数据分组后，发生了什么事情

select post, max(salary) from emp group by post;

as: 起别名; 给获取出来的数据字段名，设置别名

select post as '部门', max(salary) as '薪资' from emp group by post;

# 可简写, 但不推荐
select post '部门', max(salary) '薪资' from emp group by post;

每个部门的最低工资

select post, min(salary) from emp group by post;

每个部门的平均工资

select post, avg(salary) from emp group by post;

每个部门的工资总和

select post, sum(salary) from emp group by post;

每个部门的人数: count() 中传任意参数都没问题

select post, count(id) from emp group by post;
select post, count(post) from emp group by post;

3.查询分组之后的部门名称和每个部门下所有员工的姓名

group_concat(name): 不仅可以获取分组后的某一个字段，并且可以对字符串进行拼接

select post, group_concat(name) from emp group by post;

给每个部门的员工名字前 + NB_

select post, group_concat('NB_', name) from emp group by post;

拼接部门员工名字+薪资

select post, group_concat(name, ":", salary) from emp group by post;

4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用

select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp;

5.补充as语法 即可以给字段起别名也可以给表起

select emp.name as '名字', emp.salary as '薪资' from emp;

6.查询四则运算

# 求各部门所有员工的年薪
select name, salary * 12 as annual_salary from emp;

5、练习题

写查询语句的步骤:  先看需要查哪张表，然后看有没有什么限制条件, 再看需要根据什么分组，最后再看需要查看什么字段!

执行顺序

from --> where --> group by --> select 

注意: 聚合函数:

1、只能在group by后(执行顺序)使用  
2、若查询语句没有group by，则默认整张表就是一个分组。

1.查询岗位名以及岗位包含的所有员工名字

select post, group_concat(name) from emp group by post;

2.查询岗位名以及各岗位内包含的员工个数

select post, count(id) from emp group by post;

3.查询公司内男员工和女员工的个数

select sex, count(id) from emp group by sex;

4.查询岗位名以及各岗位的平均薪资

select post, avg(salary) from emp group by post;

5.查询岗位名以及各岗位的最高薪资

select post, max(salary) from emp group by post;

6.查询岗位名以及各岗位的最低薪资

select post, min(salary) from emp group by post;

7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

select sex, avg(salary) from emp group by sex;

8.统计各部门年龄在30岁以上的员工平均工资

select post, avg(salary) from emp where age > 30 group by post;

分结构查询

分结构查询: 先查找所有年龄30岁以上的员工，再根据部门分组查询它们的平均工资;

select * from emp where age > 30;
select post, avg(salary) from emp where age > 30 group by post;

6、having

目前掌握的查询基础

书写顺序:

select 字段1, 字段2...
from 表名
where 查询条件
group by 分组条件

执行顺序:

from
where 
group by
select

having: 过滤

1.having与where语法一样，只不过having需要在group by后使用;
2.where 不能使用聚合函数，但having可以;

1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门;

select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

执行顺序

		- from
		- where 
		- group by 
		- having
		- select

7、distinct : 去重

distinct: 去重

注意:

查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。

select distinct id, post from emp;
select distinct post from emp;
select distinct age from emp;

8、order by: 排序

 order by: 排序， 获取select获取的数据进行排序

1、根据薪资进行升序

select * from emp order by salary;  # 默认升序
select * from emp order by salary asc;  # 指定升序
select * from emp order by salary desc;  # 指定降序

2、根据年龄进行降序排列

select * from emp order by age desc;

3、先按照age升序，再按照salary降序

select * from emp order by age asc, salary desc;

4、统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序

select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

执行顺序:

		- from
		- where 
		- group by 
		- having 
		- select 
    - order by  # 通过select 查出来的数据再进行排序

9、limit : 限制结果返回数量

应用场景:

类似于博客园首页的数据展示，每一页有固定的数量;

1、从第一条开始，获取4条记录;

select * from emp limit 4;

2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数;

select * from emp limit 0, 4;
select * from emp limit 4, 4;

3、查询工资最高的人的详细信息

select * from emp order by salary limit 1;

执行顺序：

执行顺序:
		- from
		- where
		- group by
		- having
		- select 
		- distinct
		- order by 
		- limit

10、正则

在编程中，凡是看到reg开头的，基本上都是跟正则有关

select * from emp where name regexp '^程.*(金|银|铜|铁)$';

二 多表查询

- 多表查询
    - 关联查询
    - 子查询

创建表与插入数据准备

建表

create table dep2(
id int,
name varchar(20) 
);


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

插入数据

insert into dep2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');


insert into emp2(name,sex,age,dep_id) values
('tank','male',17,200),
('egon','female',48,201),
('kevin','male',38,201),
('jason','female',28,202),
('owen','male',18,200),
('sean','female',18,204);

PS:字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;

注意

注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;

1、关联查询

 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积"   PS: 百度科普
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

一. 比较麻烦的表关联

1、查询员工以及所在部门的信息;

将两张表合并，并且根据id字段去判断

select * from emp2, dep2 where emp2.dep_id = dep2.id;

2、查询部门为技术部的员工及部门信息

select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术';

二 将两张表关联到一起的操作，有专门对应的方法

1、inner join

1、内连接：只取两张表有对应关系的记录

1、内连接：只取两张表有对应关系的记录
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id and dep2.name = '技术';

2、left join

2、左连接: 在内连接的基础上保留左表没有对应关系的记录

select * from emp2 left join dep2 on emp2.dep_id = dep2.id;

3、right join

3、右连接: 在内连接的基础上保留右表没有对应关系的记录

select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

4、union

4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录

select * from emp2 left join dep2 on emp2.dep_id = dep2.id
union
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

2、子查询

子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用

1.查询部门是技术或者人力资源的员工信息

'''
先获取技术部和人力资源的id号，再去员工表里根据前面的id筛选出符合要求的员工信息;
'''
select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源');

2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询

查第一张emp表

select t1.id, t1.name, t1.hire_date, t1.post, t2.* from emp as t1 
inner join 
(select post, max(hire_date) as max_date from emp group by post) as t2 
on t1.post = t2.post
where t1.hire_date = t2.max_date;

as:

- 可以给表起别名
- 可以给查出来的虚拟表起别名
- 可以给字段起别名

元类

引入列子：

list1 = []  # list1 = list([])
# print(type(list1))  # <class 'list'>


# 2.自定一个类
class Chinese(object):
    country = 'china'
    def __init__(self, name, age, sex):
        self.name = name
        self.age = age
        self.sex = sex


p_obj = Chinese('tank', 17, 'male')
# print(type(p_obj))  # <class '__main__.Chinese'>
print(Chinese)  # 类本质上也是一个对象，因为在python中 一切皆对象 ;
# print(type(Chinese))  # <class 'type'>


# 3.如何产生类的:
# 1) 通过class关键字产生类
# 2) 通过调用type类: type()  ---> obj ---> Chinese

# what: 指的是类名
# bases: 继承的父类 (object, )
# dict: 类的名称空间


code = '''
country = 'china'
def __init__(self, name, age, sex):
    self.name = name
    self.age = age
    self.sex = sex
'''
# class_attr
class_attr = {}
exec(code, {}, class_attr)


# type(类的名字, 类的基类, 类的名称空间) ----> 类 ---> 对象
obj = type('Chinese', (object, ), class_attr)  # def __init__(what, bases=None, dict=None)
# print(obj)
Chinese = obj
print(Chinese)


#结果：
<class '__main__.Chinese'>
<class '__main__.Chinese'>

1.什么是元类？

 类的类就是type, 其实type就是元类;

2.为什么要使用元类???

元类可以控制类的创建过程!

type是python内置的元类

# 自定义一个元类
class MyMetaClass(type):

    # 控制类的创建
    # 优酷需要用到的部分;
    def __init__(self, class_name, class_bases, class_dict):  # self， obj
        print(type(class_name))
        # print(class_name.istitle())
        if not class_name.istitle():
            raise NameError('类的首字母必须大写!')

        if not class_dict.get('__doc__'):
            raise TypeError('必须给我写注释!!!')

        # 必须将类中的类名、类的基类、类的名称空间，一并返回给 type中的__init__
        super().__init__(class_name, class_bases, class_dict)


    # 了解: 元类更深层次的作用
    # 控制调用类的行为
    # 为什么调用类就一定会产生一个空对象，为什么一定会执行__new__
    # 其实就是type内部一定会调用一次__call__，由__call__来帮你调用__new__。

    # 元类中的__call__就是创建类的过程!!!
    def __call__(self, *args, **kwargs):
        print(args)  # Foo类括号中的值
        # 1.造一个空对象obj
        obj = object.__new__(self)  # 创造一个空的对象  # self ---> User
        print(obj.__dict__, 1111111)

        # 2.调用类时，__call__会立马调用User.__init__, 并且将obj连同Foo括号内的参数一同传给__init__
        self.__init__(obj, *args, **kwargs)

        # return一个真正创建的对象
        return obj



# obj = MyMetaClass()
# obj()  # obj() ----> User(10, 20)  ----> user_obj


# 被控制类在定义阶段  类名(metaclass=自定义的元类) ---> 会将当前类的
# 1)类名、2)基类、3)类的名称空间   一并 传给 自定义的元类
# metaclass ---> 自定义的元类 ---> 调用自定义的元类(类名, 基类, 类的名称空间)
# type(类名, 基类, 类的名称空间)

class User(object, metaclass=MyMetaClass):  # MyMetaClass(User, (object, ), {'x':10})

    '''tank是真的很帅啊，真的舍不得你们啊!!!'''

    def __init__(self):
        pass
    x = 10

    pass


obj = User()

print(obj)


# 结果：
<class 'str'>
()
{} 1111111
<__main__.User object at 0x00000139F21F9D48>

python连接数据库

下载第三方模块

下载第三方模块: pip3 install pymysql

# 1.连接数据库
client = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='db4',
    charset='utf8',  # 此处不能写utf-8
    autocommit=True
)

# print(client)


# 2.获取游标对象 ----> 游标 可以用来提交sql命令
cursor_obj = client.cursor(pymysql.cursors.DictCursor)

# 3.通过execute 可以提交sql语句

# 1）查数据
sql = 'select * from emp'
# 提交sql语句
cursor_obj.execute(sql)

# 4.提交后，通过cursor_obj对象.fetchall() 获取所以查询到的结果
res = cursor_obj.fetchall()
print(res)

for dic in res:
    print(dic)

# 2）插入数据
# 创建表
sql = 'create table user(id int, name varchar(16))'

sql = 'insert into user(id, name) values(1, "tank")'


# 3) 更新数据
try:
    sql = 'update user set name="tank_is_handsome" where id=1'
    cursor_obj.execute(sql)

except Exception as e:
    print(e)

# 4) 删除数据
sql = 'delete from user'
cursor_obj.execute(sql)

# 关闭游标
cursor_obj.close()

# 关闭客户端连接
client.close()