MySQL自学记录——入门

DQL语言（数据查询语言）

基础查询

1. 去重：select distinct student from
2. +的作用：只表示运算符，若有字符串无法转换成数值型，默认为0；null与任何相加都为null；
3. 字符连接：concat（student, name,…）
4. 判断是否为空：
   ifnull（‘不是null的值’，‘若为null的值’）

条件查询

1. where：筛选查询；
2. 一、按条件表达式筛选：>, <, =, !=, <>, <=, >=
   二、按逻辑表达式筛选：and, or, not
   三、模糊查询：like, between and, in, is null/not null
   like：一般和通配符搭配使用
   通配符 %：任意多个字符，包含0个字符
   _：任意单个字符，要查询%或_时，通过转义字符\来区分通配符；或用任意字符加escape（like ‘_#_% escape '‘#’）；
   between and：提高语句的简洁度；包含临界值且不能颠倒； in：判断某字段的值是否属于in列表中的某一项 ；
   is null：=或<>不能用于判断是否为null；
3. 安全等于：<=>，可用于判断是否为null；判断其他普通类型的数值；但可读性较差

排序查询

1. order by 排序列表 [asc(升序)/desc(降序)]；默认为升序；
2. 可以支持单个字段、多个字段、表达式、函数、别名；
3. 一般放在查询语句的最后面，limit子句除外；

常见函数

1. 功能：类似于Java中的方法,将一组逻辑语句封装在方法中，对外暴露方法名；

2. 调用：select 函数名(实参列表) 【from 表】；

3. 字符函数
   length：获取参数值的字节个数；
   concat：拼接字符；
   upper、lower：大小写转换；
   substr/substring：截取函数，索引从1开始；
   instr：返回子串第一次在字符串中的起始索引，若找不到返回0；
   trim：去首尾的空格或其他字符；trim（‘a’ from ’xxx’）;
   lpad/rpad：用指定的字符左/右填充指定长度；
   replace：替换；

4. 数学函数
   round：四舍五入，可选择小数点后保留位数；
   ceil：向上取整，返回大于等于该参数的最小整数；
   floor：向下取整，返回小于等于该参数的最小整数；
   truncate：截断，选择小数点后保留位数；
   mod：取余；

5. 日期函数
   now：返回当前系统日期+时间；
   curdate：返回当前系统日期，不包含时间；
   curtime：返回当前系统时间，不包含日期；
   可以获取指定部分，年，月，日，小时，分钟，秒（select year(now())）；
   str_to_date：将日期格式的字符转换成指定格式的日期（str_to_date(‘9-13-1999’,’%m-%d-%Y’)）；
   date_format：将日期转换成字符( date_format(‘2018/6/6’,’%Y年%m月%d日‘))；

6. 其他函数
   version：查看版本号；
   database：查看当前的库；
   user：查看当前用户；

流程控制函数

1. if函数：实现if else效果，if(表达式，true返回的值，false返回的值)；
2. case函数的使用一：switch case的效果
   case+要判断的字段或表达式
   when 常量1 then 要显示的值1或语句1
   when 常量2 then 要显示的值2或语句2
   …
   else 要显示的值n或语句n
   end
   case函数的使用二：类似于多重if
   case
   when 条件1 then 要显示的值1或语句1
   when 条件2 then 要显示的值2或语句2
   …
   else 要显示的值n或语句n
   end

分组函数

1. 功能：又称为聚合函数或统计函数或组函数，用作统计使用

2. 分类：sum求和、avg平均值、max最大值、min最小值、count计算个数；

3. 特点：
   sum、avg一般用于处理数值型；
   max、min、count可以处理任何类型；
   忽略null值；
   可以和distinct搭配实现去重的运算；

4. count函数的详细介绍

5. 和分组函数一同查询的字段有要求是group by后的字段

count(*)、count（1）、count（字段）效果相同
在MYISAM存储引擎下，count(*)效率高
在INNODB存储引擎下，count(*)和count（1）效率差不多，但大于count(字段)

分组查询（group by）

1. group by子句将表中的数据分成若干组

2. 语法：
   select 分组函数，列（要求出现在group by的后面）
   from 表
   [where筛选条件]
   group by 分组的列表
   [order by子句]
   注意：查询列表必须特殊，要求是分组函数和group by之后出现的字段；

3. 分组查询中的筛选条件分为两类
   分组前筛选：数据源为原始表，在group by子句之前，使用where关键字；
   分组后筛选：数据源为分组后的结果集，在group by子句之后，使用having关键字；
   分组函数做条件肯定是放在having子句中；

4. 按多个字段分组
   语法：group by 字段1，字段2；

5. group by 子句支持单个字段分组，多个字段分组（用逗号隔开且并没有顺序要求），表达式或函数用得较少；

6. 可以添加排序（排序放在整个语句的最后）

连接查询

1. 又称为多表查询，当查询字段涉及多个表时；
2. 连接分类
   按年代分类：sql92标准（仅仅支持内连接）、sql99标准（支持内连接+外连接（左外连接、右外连接）+交叉连接）
   按功能分类：内连接（等值连接、非等值连接、自连接）、外连接（左外连接、右外连接、全外连接）、交叉连接；
3. 为表起别名 之后，查询字段不能使用原来表的名字；

sql92语法

1. 等值连接：
   多表等值连接的结果为多个表的交集部分；
   n表连接，至少需要n-1个链接条件；
   多表的顺序没有要求；
   可以搭配前面介绍的所有查询子句使用，如排序、分组、筛选；
   一般需要为表取别名；

2. 非等值连接 条件between and；

3. 自连接 重复使用同一张表，取别名；

sql99语法

1. 语法：
   select 查询列表
   from 表1 别名【连接类型】
   join 表2 别名
   on 连接条件
   【where 筛选条件】
   【group by 分组】
   【having 筛选条件】
   【order by】
2. 内连接：inner join
3. 外连接：用于查询一个表中有，另一个表中没有的记录；
   外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值；否则显示null；
4. 左外连接中，left join左边的是主表；右外连接right join右边的是主表；
5. 左（右）外连接：left（right）【outer】join
6. 全外链接：full【outer】join 查询两张表的所有内容（mysql中不支持全外连接）
7. 交叉连接：cross join

子查询

1. 出现在其他语句中的select语句，称为子查询或内查询
   外部的查询语句称为主查询或外查询；

2. 分类：
   按子查询出现的位置：select后、from后、where或having后、exists后（相关子查询）
   按结果集的行列数不同：标量子查询（结果只有一行一列）、列子查询（一列多行）、行子查询（一行多列）、表子查询（多行多列）；

3. where或having后的子查询
   1.标量子查询
   2.列子查询
   3.行子查询
   4.特点：
   ①语句写在（）内
   ②一般放在条件右侧
   ③标量子查询一般搭配单行操作符使用（<、>、<=、>=、=、<>）
   列子查询一般搭配多行操作符使用（in/not in（等于列表中的任意一个）、 any/some（和子查询返回的某一个值比较）、 all（和子查询返回的所有值比较））
   ④子查询的执行优先于主查询执行

4. select后的子查询

5. from后的子查询

6. exits后的子查询
   语法：exits（完整的查询语句）
   结果：0或1

分页查询

1. 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求，随用随查
2. 语法：
   select 查询列表
   from 表
   【join type join 表2
   on连接条件
   where 筛选条件
   group by 分组
   having 筛选条件
   order by】
   limit offset，size；
   offset要显示条目的起始索引（从0开始）
   size要显示的条目个数
   特点：
   ①limit语句放在查询语句的最后
   ②公式
   要显示的页数page，每页条目数size
   select 查询列表
   from 表
   limit （page -1）*size， size

联合查询

1. 联合查询：将多条查询语句的结果合并成一个结果
2. 语法：查询语句1 union 查询语句2 union ···
3. 应用场景：要查询的结果来自于多个表且表之间没有直接的关系，但查询信息一致时；
4. 特点：要求多条查询语句的列数一致；每一列的类型和数据是一致的；union关键字默认去重，使用union all包含重复项；

DML语言（数据操作语言）

插入语句

1. 语法一：insert into 表名（列名，…）values （值1，…）
2. 插入值的类型要与列的类型一致或兼容
3. 可以为null的列的插入：插入列名对应值为null或两者皆省略；
4. 插入时列的顺序可以颠倒；列数和值的个数必须一致；可以省略列名，默认所有列；
5. 语法二：insert into 表名 set 列名1 = 值1，列名2 = 值2，…
6. 两种方式对比：方式一支持插入多行、支持子查询

修改语句

1. 修改单表的记录：update 表名 set 列1 = 新值1，列2 = 新值2，…where 筛选条件；
2. 修改多表的记录：update 表1 别名 连接类型 表2 别名 on 连接条件 set 列 = 值,…where 筛选条件；

删除语句

1. 方式一delet
   单表删除：delete from 表名 where 筛选条件
   多表删除：delete 表1的别名，表2的别名 from 表1 别名 连接类型 表2 别名 on 连接条件 where 筛选条件；
   假如要删除的表中有自增长列，用delete后再插入数据，从断点开始计数；

2. 方式二：truncate
   truncate table 表名；（删除整张表）
   truncate删除不能回滚，delete可以；

DDL语言（数据定义语言）

库和表的管理

1. 库的创建：create database if not exits 库名；
   库的修改（一般不修改）：可以更改库的字符集 alter database 库名 character set 字符集名
   库的删除：drop database if exits 库名

2. 表的创建：create table if not exits 表名（列名 列的类型【（长度） 列的约束】，列名 列的类型【（长度） 列的约束】，…）
   表的修改：修改列名（alter table 表名 change column 列名 新列名 列的类型）、列的类型或约束（alter table 表名 modify column 列名 新的列类型）、添加新列（alter table 表名 add column 列名 列的类型）、删除列（alter table 表名 drop column if exits 列名）、修改表名（alter table 表名 rename to 新表名）；
   表的删除：drop table if exits 表名；
   表的复制：仅复制表的结构（create table 表名2 like 表名1）；复制某些字段（create table 表2 select 列1，列2 from 表1 where 0）；全复制或部分复制（create table 表2 select * from 表1）

常见的数据类型

1. 数值型：整型、小数（定点数、浮点数）
   分类：tinyint（1）、smallint（2）、mediumint（3）、int（4）、bigint（8）；float（m,d）（4）、double（m,d）（8）、dec/decimal（m,d）(m为整数部位+小数部位，d为小数部位，可省略)
2. 字符型：较短的文本（char（m）、varchar（m））、较长的文本（text、blob（较长的二进制数据））m为字符数
   char固定长度字符、耗费空间、效果高，m可省略，默认为1；varchar可变长度字符，节省空间、效率低
   enum型（枚举型，只能选一个）、set型（集合型，同时可选多个）
3. 日期型：date（4，日期）、time（3，时间）、datetime（8，日期+时间）、timestamp（4）、year（1，年）
4. 设置无符号和有符号数：默认有符号数，int unsigned（无符号）

常见约束

1. 含义：一种限制，用于限制表中的数据，保证表中数据的准确和可靠性（一致性）

2. not null：非空约束，用于保证该字段的值不能为空；
   default：默认，用于保证该字段有默认值；
   primary key：主键，用于保证该字段的值具有唯一性，并且非空；
   unique：唯一，用于保证该字段的值具有唯一性，可以为空；
   check：检查约束（mysql中不支持但可以写）
   foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值；

3. 添加列级非空约束：alter table 表名 modify column 列名 列的类型 列的约束；
   添加表级非空约束：alter table 表名 add 约束（列名）；

4. 标识列：自增长列，系统提供默认的序列值，使用关键字auto_increment
   特点：标识列必须和key搭配使用；一个表最多有一个标识列；只能是数值型；通过set auto_increment_increment = 3设置步长；通过手动插入值设置起始值

TCL语言（事务控制语言）

1. transaction control language

2. 事务：一个或一组SQL语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行；

3. show engines；查看支持的存储引擎；常用的innodb、myisam、memory等；

4. 支持事务的存储引擎：innodb；

5. 事务的（ACID）属性：
   ①原子性（atomicity）：事务是一个不可分割的工作单位；
   ②一致性（consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态；
   ③隔离性（isolation）：一个事务的执行不能被其他事务干扰；
   ④持久性（durability）：一个事务一旦被提交，对数据库中数据的改变就是永久性的；

6. 事务的创建
   ① 分类：隐式事务，没有明显的开启和结束的标记，如insert；显式事务，有明显的开启和结束的标记（前提：必须先设置自动提交功能为禁用，set autocommit = 0）；
   ②步骤1：set autocommit = 0；start transaction；
   步骤2：编写事务中的SQL语句（select、delete、update、insert）；
   步骤3：commit（提交事务）；rollback（回滚事务）；
   ③四种隔离级别
   read uncommitted（读未提交数据）、read committed（读已提交数据，Oracle默认）、repeatable read（可重复读，mysql默认）、serializable（串行化）
   ④查看当前隔离级别：select @@tx_islolation；
   ⑤设置当前MySQL连接的隔离级别：set transaction isolation level 隔离级别；
   ⑥设数据库系统的全局隔离级别：set global transaction isolation level 隔离级别；

7. 回滚点：savepoint 节点名；

8. delete：支持回滚；
   truncate：不支持回滚；

视图

1. 含义：MySQL5.0.1版本开始提供视图功能，一种虚拟存在的表；只保存了sql逻辑，不保存查询结果，动态生成；

2. 应用场景：多个地方用到同样的查询结果；该查询结果使用的SQL语句较为复杂；

3. 创建语法：create view 视图名 as 查询语句；

4. 修改语法：
   方式一：create or replace视图名 as 查询语句；
   方式二：alter view 视图名 as 查询语句

5. 删除语法：drop view 视图名1，视图名2，…

6. 查看（结构）语法：desc 视图名；show create view 视图名

7. 更新（数据）语法：一般视图不允许更新
   ①插入：insert into 视图名 values（内容）；
   ②修改：update 视图名 set 列名 = 新值 where 筛选条件；
   ③删除：delete from 视图名 where 筛选条件；

8. 特点：
   ①重用SQL语句；
   ②简化复杂的SQL操作，不必知道它的查询细节；
   ③保护数据，提高安全性

存储过程和函数

变量

1. 变量：系统变量（全局变量 、会话变量）、自定义变量（用户变量、局部变量）

2. 系统变量：由系统提供，属于服务器层面
   ①查看所有的系统变量：show global/session variables；
   ②查看满足条件的部分系统变量：show global/session variables like 模糊条件；
   ③查看某个指定的系统变量：select @@global/session.系统变量名；
   ④为某个具体的系统变量赋值：
   方式一：set global/session 系统变量名 = 值；
   方式二：set @@global/session.系统变量名 = 值；
   全局变量：服务器每次启动将为所有的全局变量赋初始值，针对所有回话/连接有效；
   会话变量：仅仅针对当前会话/连接有效

3. 自定义变量：用户自定义
   ①使用步骤：声明、赋值、使用（查看、比较、运算等）
   ②用户变量：针对当前会话/连接有效
   步骤一： 声明并初始化 set @用户变量名 = 值；或 set @用户变量名 ：= 值；或 select @用户变量名 ：= 值
   步骤二：赋值 set或select；或 select 字段 into变量名 from 表名；
   步骤三：使用 select @用户变量名；
   ③ 局部变量：作用域在定义它的begin end，应用在begin end 中的第一句话
   步骤一： 声明 declare 变量名 类型 default 值；
   步骤二：赋值 set或select（加@）；或 select 字段 into 变量名 from 表名；
   步骤三：使用 select @用户变量名；select 字段 into 局部变量名 from 表

存储过程

1. 含义：一组预先编译好的SQL语句的集合，理解成批处理语句

2. 创建：create procedure 存储过程名（参数列表）begin 存储过程体 end 结束标记
   ①参数列表：参数模式 参数名 参数类型
   ② 参数模式：in（该参数可以作为输入）、out（该参数可以作为输出） 、inout（该参数可以作为输入和输出）
   ③如果存储过程体仅仅只有一句话，可以省略begin end
   ④存储过程体中的每条SQL语句的结尾必须加分号；
   ⑤ 存储过程的结尾可以使用delimiter重新设置
   语法：delimiter 结束标记；

3. 调用：call 存储过程名（实参列表）
   ①空参列表
   ②带in模式参数的存储过程
   ③带out模式参数的存储过程
   ④带inout模式参数的存储过程

4. 删除：drop procedure 存储过程名

5. 查看：show create procedure 存储过程名

6. 存储过程一般不支持修改

函数

1. 与存储过程的区别
   存储过程：可以有0个、1个或多个返回值，适合用于批量插入、更新等；
   函数：有且仅有一个返回，适合用于处理数据后返回一个结果

2. 创建：create function 函数名（参数列表） returns 返回类型 begin 函数体 end
   参数列表：参数名 参数类型

3. 调用 ：select 函数名（参数列表）

4. 查看：show create function 函数名

5. 删除：drop procedure 函数名

6. 函数一般不支持修改

流程控制结构

顺序结构、分支结构、循环结构

分支结构

1. if函数 语法：if（表达式1，返回式1，返回式2）
2. case结构语法：作为独立语句使用时只能在begin end中
   语法一：
   case 变量|表达式|字段
   when 要判断得知 then 返回值1
   when 要判断得知 then 返回值2
   …
   else 要返回的值n
   end case
   语法二：
   case
   when 要判断得知 then 返回值1
   when 要判断得知 then 返回值2
   …
   else 要返回的值n
   end case
   若省略else且所有when条件都不满足时，返回null
3. if 结构：实现多重分支，智能应用在begin end中
   if 条件1 then 语句1
   elseif 条件2 then 语句2
   …
   [else 语句n]
   end if

循环结构

1. 分类：while、loop、repeat

2. 循环控制：iterate（类似continue）、leave（类似break）

3. while：
   【标签：】 while 循环条件 do
   循环体；
   end while【标签】；

4. loop：
   【标签：】 loop 循环条件
   循环体；
   end loop【标签】；

5. repeat
   【标签：】 repeat 循环条件
   循环体；
   until 结束循环的条件
   end repeat【标签】；