目录
- 一、概述
- 数据模型
- SQL语句的分类
- 数据类型
- 二、数据库设计 DDL
- 数据库层面
- 数据表层面
- 三、数据库操作 DML
- 添加数据insert
- 修改数据update
- 删除数据delete
- 四、数据查询 DQL
- 总结
- 基本查询
- 条件查询where
- 聚合函数
- 分组查询group by(where与having的区别)
- 排序查询order by
- 分页查询limit
- if和case的使用
- 五、多表关系
- 一对多
- 外键约束
- 一对一
- 多对多
- 六、多表查询
- 笛卡尔积
- 内连接
- 外连接
- 子查询
- 标量子查询
- 列子查询
- 行子查询
- 表子查询
- 七、事务
- 问题场景引入
- 概念
- 事务操作
- 事务
- 八、索引
- 介绍
- 索引结构:B+树
- 索引操作语法
一、概述
数据模型
MySQL是如何存储和管理数据的？
DBMS是MySQL服务器里内置的一个软件将SQL语句发送给它 通过它即可操作数据库当中的数据
SQL语句的分类
数据类型
数值类型:
tinyint默认是有符号的
tinynit unsigned才是无符号的
字符串类型:
char(10):不足十个,用空字符补到十个;超过十个,直接报错(性能高 但浪费空间)
varchar(10):最多只能存10个字符,不足10个字符, 按照实际长度存储(性能低 但节省空间 要判断实际长度)
char使用空间换时间 varchar使用时间换空间
日期时间类型:
二、数据库设计 DDL
数据库层面
数据表层面
创建表:
[ ]表示该语法可选
约束是作用在字段上的 用于限制存储在表中的数据
约束:
查询:
修改add,modify,change,drop,rename(表):
前四个是针对字段的修改alter table 表名 + 操作
最后一个是针对表的修改
删除:
删除表的时候表中的数据也会被全都删掉
删除字段是alter table tb_user02 drop column school_name;
三、数据库操作 DML
添加数据insert
插入时 指定的字段顺序要和values的顺序一致
字符串类型比如varchar,日期类型比如date,插入的时候都应该在引号里面
修改数据update
where条件如果没有就会更新当前表的所有记录
删除数据delete
where条件也是可有可无 如果没有就会删除当前表所有数据(危险操作)
delete无法删除某个字段的值(可以使用update 更新某个字段的值为NULL)
下图就是把id=1的那一条记录删除了
第二句SQL是删除了整个表所有的记录
四、数据查询 DQL
总结
where是分组前 having是分组后
编写顺序与实际执行顺序
基本查询
as可以省略
尽量少用select *
条件查询where
条件列表:
聚合函数
NULL值不参与任何一个聚合函数的运算 所以COUNT(字段)一定要是非空字段才算进去
COUNT(*),COUNT(字段),COUNT(常量)都可以统计数量 建议用第一个
分组查询group by(where与having的区别)
1.分组之后 select查询的一般是分组字段或者聚合函数查询其他的字段毫无意义
2.分组之后进行条件的过滤要用having而不是where
3.执行顺序:where > 分组操作/聚合函数 > having
4.where后面不能使用聚合函数因为where是在分组/聚合之前执行的
5.having可以对聚合函数进行判断 本质还是因为聚合函数是在having之前执行的
根据性别分组 统计男性和女性员工的数量
排序查询order by
ASC升序 默认就是它 可以不写
DESC降序
如果是多字段排序当第一个字段有重复值 才会根据第二个字段排序
逻辑上 按照下面这种顺序去写更为清晰
分页查询limit
起始索引从0开始 起始索引 = (查询页码 - 1) * 每页记录数
limit是MySQL的方言 并不是通用的
查询第一页的数据 起始索引可以省略
if和case的使用
if和case:
根据性别分组 , 统计男性和女性员工的数量
如果不把个if重命名为性别 那么他的表头就是这一坨东西(as可以省略)
职位信息统计
五、多表关系
一对多
一个部门 对应多个员工
一个员工 只归属一个部门
外键约束
前面只是在逻辑上知道员工和部门多对一的关系 但是数据库层面并没有进行关联
插入测试数据假如把部门表ID为1的记录直接删除 员工表不会受到任何影响
但此时员工表已经出现数据不一致不完整的问题了还有部分员工仍归属于被删掉的部门下
外键约束用于解决多表之间数据的一致性和完整性问题
设置外键关联之后 1部门就删不了了 5部门还是能删 因为没有员工属于5部门
物理外键与逻辑外键:
一对一
比如用户 和 身份证的关系
多对多
通过中间表 可以查到学生选了什么课 课程被谁选了
六、多表查询
笛卡尔积
笛卡尔积 会有5*17条记录 每五条里面只有一条是有效的数据
内连接
第十七个员工 内连接就查不出来(不属于交集) 要用外连接
内连接求的其实就是笛卡尔积 要用where或者on消除无效的数据
外连接
左外连接包含左边表的所有数据 以及 交集部分
子查询
子查询分类:
标量子查询
子查询返回单个值
列子查询
返回的是一列 多行
行子查询
子查询返回的是一行 多列
表子查询
通常作为一张临时表 所以一般在select语句的from后面
七、事务
问题场景引入
有可能是SQL代码写错了或各种原因 导致该部门的员工没有被删除
但是这两个操作应该作为一个整体 要么都删了 要么都没有
概念
一组操作 要么都成功 要么都失败
事务操作
begin
commit
rollback
正常情况:执行完没错 提交事务
异常情况:执行出错 回滚事务
可以把删除掉的数据恢复 保证了操作前后数据的一致性
事务
wsm
的四大特性ACID
原子性 一致性 隔离性 持久性
八、索引
介绍
简单来说就是提高查询效率的数据结构
索引的优缺点
索引结构:B+树
为什么MySQL不用二叉搜索树或者红黑树?
大数据量情况下 层级深(树的高度过高) 检索速度慢 → B+树解决
索引操作语法
如果是唯一索引 就要加上unique
