python数据表_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;