python 数据库表查询

# 数据的
# 增
    # insert into 表 values (值)
    # insert into 表(字段,字段2) values (值,值2)
    # insert into 表(字段,字段2) select 字段1,字段2  from 表2
# 删
    # delete from 表 where 条件;
    # truncate table 表名;
# 改
    # update 表 set 字段=值 where 条件;
# 查
    # select 字段 from 表
        # where 条件  根据条件筛选符合条件的行
        # group by 分组
        # having 过滤条件 根据分组之后的内容进行组的过滤
        # order by 排序
        # limit m,n 取从m+1开始的前n条

    # 1.where条件中不能用select字段的重命名
    # 2.order by 或者having可以使用select字段的重命名
        # 主要是因为order by 在select语句之后才执行
        # having经过了mysql的特殊处理,使得它能够感知到select语句中的重命名

    # 拓展
        # 在执行select语句的时候,实际上是通过where,group by,having这几个语句锁定对应的行
        # 然后循环每一行执行select语句

    # 所谓连表
    # 总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
    # 再根据条件进行筛选就可以了

# 表与表之间的连接方式
    # 内连接 inner join ... on ...
        # select * from 表1,表2 where 条件;(了解)
        # select * from 表1 inner join 表2  on 条件
        # select * from department inner join employee on department.id = employee.dep_id;
        # select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id;
    # 外连接
        # 左外连接 left join ... on ...
            # select * from 表1 left join 表2 on 条件
            # select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
        # 右外连接 right join ... on ...
            # select * from 表1 right join 表2 on 条件
            # select * from department as t1 right join employee as t2 on t1.id = t2.dep_id
        # 全外连接 full join
            # select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
            # union
            # select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;

# 1.找到技术部的所有人的姓名
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name from department d inner join employee e on e.dep_id = d.id where d.name='技术';

# 2.找到人力资源部的年龄大于40岁的人的姓名
# select * from department d inner join employee e on e.dep_id = d.id
# select * from department d inner join employee e on e.dep_id = d.id where d.name='人力资源' and age>40;

# 3.找出年龄大于25岁的员工以及员工所在的部门
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name,d.name from department d inner join employee e on e.dep_id = d.id where age>25;

# 4.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
# select * from department d inner join employee e on e.dep_id = d.id order by age;

# 5.求每一个部门有多少人
# select d.name,count(e.id) from department d left join employee e on e.dep_id = d.id group by d.name;
# 且按照人数从高到低排序
# select d.name,count(e.id) c from department d left join employee e on e.dep_id = d.id group by d.name order by c desc;

# 所谓连表就是把两张表连接在一起之后 就变成一张大表  从from开始一直到on条件结束就看做一张表
# 之后 where 条件 group by 分组 order by limit 都正常的使用就可以了
子查询

# 查询平均年龄在25岁以上的部门名
# select name from department where id in (
#     select dep_id from employee group by dep_id having avg(age)>25);

# 查看技术部员工姓名
    # 先查询技术部的部门id
    # select id from department where name = '技术';
    # 再根据这个部门id找到对应的员工名
    # select name from employee where dep_id =(select id from department where name = '技术');
    # select name from employee where dep_id in (select id from department where name = '技术');

# 查看不足1人的部门名
    # 先把所有人的部门id查出来
    # select distinct dep_id from employee;
    # 然后查询部门表,把不在所有人部门id这个范围的dep_id找出来
    # select name from department where id not in (select distinct dep_id from employee);

# 查询大于所有人平均年龄的员工名与年龄
    # 求平均年龄
    # select avg(age) from employee;
    # select * from employee where age >28;
    # select name,age from employee where age >(select avg(age) from employee);

# 查询大于部门内平均年龄的员工名、年龄
    # select dep_id,avg(age) from employee group by dep_id;
    # select name,age from employee as t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) as t2
    # on t1.dep_id = t2.dep_id where age>avg_age;