2019-04-25-day040-数据库的查询

2021-12-23 07:06:55

内容回顾

  1. 数据的增删改查
  2. 插入数据
    • insert into 表 values (值1,值2...)
    • insert into 表(指定字段名1,字段名2) values (值1,值2...)
  3. 删除数据
    • delete from 表名 where 条件
  4. 更新数据
    • update 表名 set 字段=新的值 where 条件
  5. 查询数据
    • select * from 表
      • where 条件
      • group by 分组 having 过滤条件(可以使用聚合函数)
      • order by 排序(默认是升序asc,降序desc)
      • limit m,n (默认m=0,从m+1开始,取n个)
        • limit n offset m(与上面的写法完全一致)

多表查询

  • 连表查询
    • 表和表是怎么连在一起的
      • 通过笛卡尔积得到一个全量拼接的大表
    • 内连接(inner join) 双方能够互相匹配的项才会被显示出来
      • select * from 表1 inner join 表2 on 条件
      • select * from employee inner join department
      • on employee.dep_id = department.id
      • 给表重命名
      • select t1.name,t2.name from employee as t1 inner join department as t2
      • on t1.dep_id = t2.id
    • 外连接
      • 左外连接(left join) 只完整的显示左表中的所有内容,以及右表中与左表匹配的项
        • select * from 表1 left join 表2 on 条件
        • select * from employee left join department
        • on employee.dep_id = department.id
      • 右外连接(right join) 只完整的显示右表中的所有内容,以及左表中与右表匹配的项
        • select * from 表1 right join 表2 on 条件
        • select * from employee right join department
        • on employee.dep_id = department.id
      • 全外连接 永远显示左表和右表中所有的项
        • select * from employee left join department
        • on employee.dep_id = department.id
        • union
        • select * from employee right join department
        • on employee.dep_id = department.id
  • 子查询
    • 总是在一个select中 套着另一个select语句
      • 嵌套着的这个select语句就是一个子查询语句
    • 查"技术"部的所有员工的名字
      • 查询技术部的id
      • select id from department where name = '技术'
      • select name from employee where dep_id = (select id from department where name = '技术');
    • 如果一个需求 子查询和连表查询都能实现,选择连表or子查询?
      • 选连表查询
    • 查"技术"和"销售"部的所有员工的名字
      • select id from department where name in ('技术','销售');
      • select name from employee where dep_id in (select id from department where name in ('技术','销售'));

练习

  • 连表查询
  • 1.找出年龄大于25岁的员工以及员工所在的部门
  • select e.name,d.name from employee e inner join department d
  • on e.dep_id = d.id where e.age>25
  • 2.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
  • select * from employee e inner join department d
  • on e.dep_id = d.id order by e.age
  • 子查询
  • 查询平均年龄在25岁以上的部门名
    • select dep_id from employee group by dep_id having avg(age)>25
    • select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
  • 查看技术部员工姓名
    • select name from employee where dep_id = (select id from department where name = '技术');
  • 查看不足1人的部门名(子查询得到的是有人的部门id)
    • 在员工表中不存在的一个部门id,在department表里
    • 在department表里的id字段中找到一个在员工表的dep_id中不存在的项
    • select name from department where id not in (select dep_id from employee group by dep_id);
    • 把员工表里所有的人所在的dep_id都查出来
  • 带比较运算的子查询
  • 1.查询大于所有人平均年龄的员工名与年龄
    • select avg(age) from employee;
    • select name,age from employee where age > (select avg(age) from employee);
  • 2.查询大于部门内平均年龄的员工名、年龄
    • select dep_id,avg(age) from employee group by dep_id;
    • select * from employee inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
    • on employee.dep_id = t2.dep_id where employee.age > t2.avg_age;

练习

查询每个部门最新入职的那位员工

  • 入职时间最大的员工
  • 每个部门最新入职员工的时间
  • select post,max(hire_date) from emp group by post;
  • select emp.name,emp.hire_date from emp inner join (select post,max(hire_date) latest_date from emp group by post) as t
  • on emp.post = t.post where emp.hire_date = t.latest_date;
select
(select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1)
from emp as t1 group by post;

索引原理

读还是写操作多

  • 博客园
  • 百度
  • 豆瓣
  • 知乎
    ###读操作更多
  • 读写 :10:1
    ###IO一次 写更浪费时间
  • 数据库的瓶颈 : 读取数据的时间
  • 磁盘的读效率
  • 上百万条数据
    • 如何存储
    • 如何读取
alex 50 alex@oldboy.com
wusir 30 wusir@oldboy.com
jin 70 jin@oldboy.com
baoyuan 45 baoyuan@oldboy.com
ning 91 ning@oldboy.com
xiaohei 53 xiaohei@oldboy.com
lihua 21 lihua@oldboy.com

1
2
3

存储

  1. 树型结构里
  2. balance tree 平衡树
  3. 平衡树能够提高读的速度,但是降低了写的速度
  4. 存储行数据和树中数字的关系
    • 树中的数字指向数据的具体地址
    • 树中的数字节点内直接存储行内容
  5. 如果在非叶子节点 存储数值和表中的具体行,那么会导致每个节点能够存储的数据量降低
    • 无形中增加了树的高度
  6. 查找数据的效率不稳定
  7. 一种新的存储数据的办法
alex 50 alex@oldboy.com
wusir 30 wusir@oldboy.com
jin 70 jin@oldboy.com
baoyuan 45 baoyuan@oldboy.com
ning 91 ning@oldboy.com
xiaohei 53 xiaohei@oldboy.com
lihua 21 lihua@oldboy.com
  1. 所有的数据都存储在叶子节点上而不是存储在根节点或者分支中
  2. select * from 表 where age between 20 and 70
  3. 在叶子节点之间添加了双向链表,让范围查询变得更加容易

树型结构里

  1. balance tree 平衡树
  2. 平衡树能够提高读的速度,但是降低了写的速度
  3. 存储行数据和树中数字的关系
    • 叶子节点中的数字指向数据的具体地址
    • 叶子节点内直接存储行内容
  4. 所有的数据都存储在叶子节点上而不是存储在根节点或者分支中
    • 为了降低树的高度
    • 让每一个数据被找到的时间变得稳定
  5. 在叶子节点之间添加了双向链表,为了能够更加快速的进行范围查询

b+树

  • 影响查找速度的最根本的原因是什么
    • IO次数 - 树的高度
    • 为了降低树的高度
      • 1.增加数据与数据之间的区分度
      • 2.降低目录列的数据的长度
  • 索引 : 实际上就是一个搜索表时使用的目录
    • 聚集索引 : 叶子节点内直接存储行内容
      • 只有innodb存储引擎才有聚集索引
      • 主键
    • 辅助索引 : 叶子节点中的数字指向数据的具体地址
      • innodb中和myisam中都可以存在
        # innodb 对应2个文件 表结构 数据 + 索引
        # myisam 对应3个文件 表结构 纯数据 辅助索引
上一篇：Python for z/OS


下一篇：QQ列表展示