欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库三,exec内置函数

程序员文章站 2022-06-05 18:17:42
数据库三,exec内置函数 一、数据库查询与执行顺序 必备知识 二、exec内置函数补充 初始 ......

数据库三,exec内置函数

一、数据库查询与执行顺序

  • 必备知识
查询语句的基本操作
    - select
    - from
    - where
    - group by
    - having
    - distinct
    - order by
    - limit
    聚合函数:count max min avg sum


- 单表查询:
    select * from emp; # 若数据比较多,比较凌乱,可以在表后面 + \g
    select * from emp\g  # 不要加分号



    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:如果在windoes系统中,插入中文字符,select的结果为空白,可以将所有字符编码统一设置成gbk

- 重点(********):
        写sql语句必须遵循两点:
            - 书写顺序:
                # 获取id为4、5的两条记录
                select * from emp where id > 3 and id < 6;

                - select
                - from
                - where

            - 执行顺序:
                比如:图书管理员,的先找到是哪一个是图书馆(那张表),
                在找到这本书在图书馆的哪一个位置(那一条记录),最后查找这本书中某一页(那些字段)

            select * from emp where id > 3 and id < 6;
                - from ---> 找到图书馆
                - where ---> 找到书的位置
                - select ---> 找到书本中的某一页

        注意: 必须记住sql语句的 书写顺序 与 执行顺序(*******);

2、where约束条件
#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字母 的 员工姓名和薪资
# like:模糊匹配
# %:匹配0个或多个任意字符
# _:匹配一个任意字符
select name, salary from emp where name like "%o%";


#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;


3、 - group by: 分组
        - 书写顺序:
            - select
            - from
            - where
            - group by

        - 执行顺序:
            - from
            - where
            - group by
            - select

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

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

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

#非严格模式下不报错(我已经设置了严格模式)
select * from emp group by post;   #报错
select id, name 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";

# 设置完以后得先关闭客户端,在重新打开

# 在严格模式下只能“直接” 获取分组条件post 字段;
#*******可以同聚合函数,间接获取其他字段数据
select post, salary from emp group by post;  # 报错

select post from emp group by post;

聚合函数:
    count:计数
    max:最大值
    min:最小值
    avg:平均值
    sum:求和




#2、获取 每个部门 的最高工资
# as 别名:可以给字段 加一个 别名
    select post, max(salary) from emp group by post;

    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;


总结:聚合函数,必须跟在group by 后面(执行顺序)

# 练习题:
    1、查询“岗位名” 以及 岗位包含的所有员工名字 一行展示
    # group_concat(name) : 可以将分组后的 所有名字获取并进行拼接
    # 默认以,拼接
    select post, group_concat(name) from emp group by post;

    # 指定以:拼接
    select post, group_concat(name, ':') from emp group by post;

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

    2、查询岗位名以及各岗位内包含的员工个数
    select post, count(id) from emp group by post;

    3、查询公司内男员工和女员工的个数
    select sex, count(*) from emp group by sex;

    4、查询岗位名以及岗位的平均薪资
    5、查询岗位名以及岗位的最高薪资
    6、查询岗位名以及各岗位的最低薪资


    7、查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
    select sex, avg(salary) from emp group by sex;

    8、统计各部门年龄在30岁以上的员工的平均薪资
    # 步骤:先找到表,在找到年龄30岁以上,在根据部门分组,最后求平均薪资
    select post, avg(salary) from emp where age > 30 group by post;

5、having
# 目前掌握的查找顺序
- 书写顺序:
    select
    from
    where
    group by

- 执行顺序
    from
    where
    group by
    select


having :过滤

    - having 与 where 的作用是一样的
    - having必须跟在group by 后面
    - having 后面可以跟聚合函数,但where后面不能跟聚合函数

    - 书写顺序:
        - select
        - from
        - where
        - group by
        - having

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


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

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

6、distinct
    - 书写顺序:
        - select
        - distin
        - from
        - where
        - group by
        - having


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



distinct:后面跟着去重的字段名

# 注意:查询的字段值必须是重复的才有效,只要有一个字段值是不重复的就没有效果。

    select distinct post from emp;  #可以去重
    select distinct post, id from emp;


7、order by :排序
    - 书写顺序:
        - select
        - from
        - where
        - group by
        - having
        - order by

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


    # order by 默认升序  asc 升序  desc 降序
    1、根据薪资进行升序
    select salary from emp order by salary asc;

    2、根据年龄进行降序排列
    select age from emp order by age desc;

    3、先按照age升序,在按照salary降序
    select age, salary 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) asc;


8、limit:限制查询记录的数量
    - 书写顺序:
        - select
        - from
        - order by
        - limit

    - 执行顺序
        - from
        - select
        - order by
        - limit


    1、从第一条开始,获取4条记录
    select * from emp limit 4;


    2、limit可以有两个参数,参数1:是限制的开始位置 + 1,参数2:是 从开始的位置展示的条数;
    select * from emp limit 4, 4;   # 从第五条数据开始查找,获取四条数据
    select * from emp limit 0, 4;   # 从第一条数据开始查找,获取四条数据


    3、查询工资最高的人的  详细信息
    select * from emp order by salary desc limit 1;

    # 聚合函数:若没有group by 分组,默认将查出来的数据当做一个分组,也能使用
    select max(salary) from emp;

9、正则
    # 在编程中,凡是看到reg 开头,基本都是跟正则有关
    # *:代表 0 个或 多个
    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、查询员工 以及员工所在部门 的信息
    # 通过where 约束条件
    select * from emp2, dep2 where emp2.dep_id = dep2.id;

    2、查询 部门 为 技术部 的 员工 及部门信息
    select emp2.name, dep2.* from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术';

    - 联表查询
        - 内连接
            # 1、内连接:只取两张表有对应关系的记录
            查询 员工 以及所在 部门 的信息
            # 通过内连接
            select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;


        - 左连接
            # 2、左连接:在内连接的基础上保留左表没有对应关系的记录
            select * from emp2 left join dep2 on emp2.dep_id = dep2.id;


        - 右连接
            # 3、右连接:在内连接的基础上保留右表没有对应关系的记录
            select * from emp2 right join dep2 on emp2.dep_id = dep2.id;


        - 全连接
            # 4、全连接:在内连接的基础上 保留左、右表没有对应关系的记录
            # 通过union去连接
            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;


    - 子查询:子查询就是将一个查询语句的结果用括号括起来,当做另一个查询语句的条件去使用
        # 1、查询 部门 是 技术 或者 人力资源 的 员工信息
        select id from dep2 where name = '技术' or name = '人力资源';

        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, t2.* from

            # 第二步
            #拼接了 t1(emp) 与 t2(各部门最新入职的员工数据虚拟表) 表的数据

            emp as t1 inner join

            # 第一步
            # 子查询获取emp表中的 部门名称与最新入职的时间字段值 生成一张虚拟表
            (select post, max(hire_date) as max_date from emp group by post) as t2
            on

            # 若t1.post 的值与t2.post 条件成立,则拼接对应的记录
            t1.post = t2.post

            # 第三步
            where t1.hire_date = t2.max_date;
            

二、exec内置函数补充

  • 初始
'''
exec:是一个python内置函数,可以将字符串的代码添加到名称空间中;
    - 全局名称空间
    - 局部名称空间

    exec(字符串形式的代码,全局名称空间,局部名称空间)

'''

# x = 10  x全局名称空间
# def func():
#     y = 10    #y局部名称空间
#     pass


# 使用exec,code相当于局部名称空间中的名字
code= '''
global x
x = 100

y = 20
def func():
    pass
    
    
def __init__():
    pass
'''
# 假设:以下是模拟操作
# 自定义的全局名称空间
global_dic = {
    'x': 10000
}

# 自定义的局部名称空间
local_dic = {}

print(global_dic)

print(local_dic)


exec(code, global_dic, local_dic)
print(global_dic)

print(local_dic)