MySQL基础篇

• DDL：数据定义语言

  • CREATE、ALTER、DROE、RENAME、TRUNCATE

• DML:数据操作语言

  • INSERT、DELETE、UPDATE、SELECT

• DCL:数据控制语言

  • COMMIT、ROLLBACK、SAVEPOINT、GRANT、REVOKE

'_'表示任意的一个字符

转义字符用''转义

AND的优先级高于OR

排序

• order by 字段名

• asc升序，desc降序

分页

• limit 偏移量,显示的条目数

• pageNumber：当前页数；pageSize：每页条数

• 公式：limit (pageNumber - 1) * pageSize,pageSize;

• limit后第一个参数为索引（从0开始），第二个参数为显示的条数；若只写一个参数则为显示的条数

从SQL优化的角度，多表查询时，每个字段都应指明所在的表

如果给表起了别名，则必须使用该别名，不能使用表的原名

多表查询分类

• 等值连接 和 非等值连接

• 自连接 和 非自连接

• 内连接 和 外连接

内连接

• 只匹配两个表满足条件的公共部分

• SQL92语法：from 表1，表2 where 条件

• SQL99语法：from 表1 (inner) join 表2 on 条件

外连接

• 不仅匹配两个表的公共部分，还匹配左表或者右表的不匹配的部分

• 外连接分类：左外连接，右外连接，满外连接

• Oracle支持SQL92和SQL99语法

• MySQL只支持SQL99语法

  • SQL99语法：from 表1 left/right (outer) join 表2 on 条件

union和union all

• union会执行去重操作

• union all不会执行去重的操作

• 如果知道合并数据后没有重复数据，或者不需要去重，则尽量使用union all提高效率

SQL99语法新特性

• 自然连接：自动匹配两个表所有相同的字段，再进行等值连接 from 表1 nature join 表2

• using连接：from 表1 join 表2 using (需要匹配的字段)，相当于表1.字段=表2.字段

字符串的索引从1开始

truncate(123.456,0)：截掉小数点后0位得到的数字

datediff(日期1,日期2)：得到日期1减去日期2得到的天数

date_format(日期,'%Y-%m-%d')：格式化，日期转为字符串 str_to_date('1997-07-07','%Y-%m-%d')：解析，字符串转为日期

case when... then... 相当于 if...else case 字段 when... then...相switch...case

统计表记录数COUNT()

• count(*)

• count(常数)

• count(常数)

• count(没有null值的字段)

• 三种方式的效率：

  • 如果使用的是MyISAM存储引擎，三者效率相同，都是O(1)

  • 如果使用的是InnoDB存储引擎，三者效率是count(*)=count(常数)＞count(字段)

avg = sum / count，都不会计算值null的情况

select中出现的非组函数必须声明在group by中，group by中声明的字段可以不出现在select中

如果过滤条件有聚合函数，必须使用having来替换where，且在group by之后

如果过滤条件中没有聚合函数，此过滤条件使用在where和having中都可以，但用在where中效率更高

• SQL92语法：

  select ...,...,...(聚合函数)

  from ...,...,...

  where ... and ...

  group by ...,... having ...

  order by ...,...(asc/desc)

  limit ..,...

• SQL99语法：

  select ...,...,...(聚合函数)

  from ... (left/right) join ... on ...

  where ... and ...

  group by ...,... having ...

  order by ...,...(asc/desc)

  limit ..,...

SQL执行过程

• from → on → (left/right join) → where → group by → having → select → distinct →order by → limit

• SQL语句执行时，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤作为输入

子查询分类

• 内查询的结果是一条还是多条记录，分为单行子查询 和 多行子查询

• 内查询是否被执行多次，分为相关子查询 和 不相关子查询

MySQL中不支持聚合函数嵌套 Oracle中支持聚合函数嵌套

commit

• 提交数据，一旦执行，数据就会永久保存在数据库中，不能回滚

rollback

• 回滚数据，一旦执行，可以实现数据的回滚，回滚到最近的一次commit之后

truncate table和delete from都可以删除表中所有数据，truncate不可回滚，有可能造成事故，不建议使用；delete可回滚，建议使用

DDL的操作不可回滚（因为在执行DDL后会默认执行一次commit） DML的操作默认情况下执行也不可回滚，但是在执行DML之前执行set autocommit = false就可以实现回滚

视图

• 一种虚拟表，没有数据，用于大型项目，它建立在已有表的基础上，视图中数据的增删改操作，数据表中的数据也会相应发生变化，反之亦然，视图的本质可看做是存储起来的查询语句

• 语法：create view 视图名 as 查询语句

MySQL8.0新特性

• limit 显示的条目数 offset 偏移量

• 计算列

  • 创建表时指明字段：

    • 字段c 类型 generated always as (字段a+字段b) virtual：字段c始终为a和b之和