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;