数据库查询

1. asc 默认排序方式(从小到大)，desc 从大到小'；

2. 多表查询：

   • 连表查询

     • 连表：总是在连接的时候创建一张大表，里面存放的是两张表的笛卡尔积。
     • 再根据条件筛选就可以了

   • 子查询

     • 查询平均年龄在25岁以上的部门名：

       • Select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

     • 查询部门年龄大于部门平均年龄的员工姓名和年龄；

       • Select name,age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where age > avg_age;
       • 结果：pass

     • 如果一个问题既可以使用连表查询，又可以子查询，推荐使用连表查询。因为效率高。

   • 表与表之间的连接方式：

     • 内连接

       • select * from 表1，表2 where 条件；

       • select * from 表1 inner join 表2 on 条件；

       • select * from 表1 inner join 表2 on 条件 where 条件；

     • 外连接：

       • 左外连接
         • left join。。。on。。。
         • 例子：select * from table1 left join table2 on 条件
       • 右外连接
         • Right join …on....
         • 例子：select * from table1 right join table2 on 条件
       • 全外连接：full join
         • select * from table1 left join table2 on 条件 union select * from table1 right join table2 on 条件

   • 练习：

     • select t2.name from department as t1 inner join employee as t2 on t1.id = t2.dep_id where t1.name = '技术';
     • select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id where t1.name = '人力资源' and t2.age>40;
     • select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id where t2.age>25;
     • mysql> select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id order by t2.age;

3. pymysql的学习