MySQL相关知识点总结

相关概念

语言分类

• DQL（Data Query Language）数据查询语言
  • select
• DML（Data Manipulate Language）数据操作语言
  • insert into
  • update
  • delete
• DDL（Data Define Language）数据定义语言
  • create
  • drop
  • alter
• TCL（Transaction Control Language）事务控制语言
  • commit
  • rollback

常用命令

# 查看所有数据库
show databases;
# 打开指定的数据库
use database_name;
# 查看数据库中所有数据表
show tables;
# 查看指定数据库中所有的数据表
show tables from database_name;
# 创建数据表
create table table_name{
	column_name1 data_type,
	column_name2 data_type,
	...
};
# 查看指定表的结构
desc table_name;
# 查看表中的所有数据
select * from table_name;
# 查看MySQL版本
select version();  # （已登录）
mysql --version;   # （未登录）
# 或者
mysql --V;   # (未登录)

DQL语言

基础查询

SELECT 要查询的东西
FROM  表名;

特点：

• 通过select查询的结果是一个虚拟的表格，不是真实存在的；
• 要查询的东西：可以是常量值、表达式、字段或者函数；

条件查询

根据条件过滤原始表的数据以查询想要的数据；

SELECT 要查询的字段|表达式|常量值|函数
FROM 表名
WHERE 条件;

• 条件表达式

  > < = >= <= != <>
  

• 逻辑表达式

  逻辑运算符：
  and(&&): 两个条件同时成立，结果为true，否则为false
  or(||): 两个条件只要有一个成立，结果为true，否则为false
  not(!): 如果条件成立，则not后为false，否则为true
  

• 模糊查询

  like关键字：
  示例：last_name like 'a%'
  '%a'     //以a结尾的数据
  'a%'     //以a开头的数据
  '%a%'    //含有a的数据
  '_a_'    //三位且中间字母是a的
  '_a'     //两位且结尾字母是a的
  'a_'     //两位且开头字母是a的
  

  匹配中文

  由于数据存储编码问题，在某些情况下，MySQL进行LIKE搜索返回的数据除了符合要求的数据外，往往还会返回许多不相干的数据，这时候也需要在LIKE后面加上BINARY操作符进行二进制比较

  SELECT * FROM username WHERE LIKE BINARY '%小%'
  

  注意：

  当LIKE匹配时加上BINARY操作符之后，则会严格区分英文大小写，因此检索的内容中如果出现中英文混合且需要忽略英文大小写的时候，就会遇见问题，这个时候可以引入MySQL中的UPPER()和COUNT()函数：

  UPPER() ：将英文字符转成大写，同UCASE()
  CONCAT()：将多个字符串连接成一个字符串
  

  所以，当我们要进行中英文混合匹配检索且要忽略英文大小写时候，可以用下面的语句：

  SELECT * FROM username WHERE UPPER(username) LIKE BINARY CONCAT('%',UPPER('a中文b')，'%');
  

  like 的效率

  LIKE运算符要对字段数据进行逐一扫描匹配，实际执行的效率比较差

排序查询

SELECT 要查的东西
FROM 表名
WHERE 条件
ORDER BY 要排序的字段|表达式|函数|别名 [ASC|DESC]

常见的函数

单行函数

1. 字符函数

   • concat(str1, str2, str3,...) -- 拼接字符，参数可以是多个

   • substr(str, start, len) -- 截取字符串，从str的第start个索引位置开始，截取len个长度的子串（len参数可以省略，省略len参数时，表示从start处截取到字符串结尾）

   • upper(str) -- 转换成大写

   • lower(str) -- 转换成小写

   • trim(str) -- 去掉str前后的空格

     还可以去掉字符串前后的指定字符，如：
     SELECT trim('a' FROM username) name FROM users WHERE username='admin';
     查询结果为：'dmin'
     

   • ltrim() -- 去掉左边空格

   • rtrim() -- 去掉右边空格

   • replace(str, from_str, to_str) -- 替换字符，用to_str替换掉str中所有的from_str字符

   • lpad(str, len, pad_str) -- 左填充，用pad_str将原字符串str左填充至len长度（而不是在原字符串的左边填充len个pad_str）

     # 注意：如果str长度大于len，那么返回值将被缩短至len个字符
     SELECT lpad(username, 3, 'abc') name FROM users WHERE username='admin';
     查询结果为：'adm'
     
     SELECT lpad(username, 10, 'abc') name FROM users WHERE username='admin';
     查询结果为：'dddddadmin'
     

   • rpad(str, len, pad_str) -- 右填充，与lpad原理相同

   • instr(str, substr) -- 返回子串substr在字符串str中第一次出现的索引

   • length(str) -- 获取字符串长度

2. 数学函数

   • round(X, D) -- 四舍五入 此函数用于对小数X进行四舍五入

     1）当输入整数时原样返回
     
     2）当参数D被省略时，默认四舍五入到整数位
     
     3）当参数D未被省略时，四舍五入后保留D位小数
     

   • rand() -- 随机数 此函数用于产生0和1之间的一个随机数，函数可以传入一个随机种子参数seed以生成固定的随机数

   • floor(X) -- 向下取整

   • ceil(X) -- 向上取整

   • mod(N, M) -- 取余

   • truncate(X, D) -- 截断 此函数用于返回X的截断到小数位D号的值。 如果D为0，则小数点被除去。如果D是负的，那么D的值的整数部分值的数量被截断

3. 日期函数

   • now() -- 当前系统日期+时间
   • curdate() -- 当前系统日期
   • curtime() -- 当前系统时间
   • str_to_date() -- 将字符串转换成日期
   • date_format() -- 将日期转换为字符串

4. 流程控制函数

   • if -- 处理双分支

   • case -- 处理多分支

     语法：
     CASE  condition
        WHEN expression_1 THEN commands1
        WHEN expression_2 THEN commands2
        ...
        ELSE commands3
     END CASE;
     
     说明：
     当condition满足expression_1，则执行commands1指令；
     否则判断condition是否满足expression_2，如果满足，则执行commands2指令；
     如果上述表达式都不成立，则执行commands3指令；
     

5. 其他函数

   • version -- 版本
   • database -- 当前数据库
   • user -- 当前连接用户

分组函数

• sum() -- 求和
• max() -- 求最大值
• min() -- 求最小值
• avg() -- 求平均值
• count() -- 统计数量

​ 特点：
​ 1.以上5个分组函数都忽略null值，除了count()
​ 2.sum和avg一般用于处理数值型，而max、min、count可以处理任何数据类型
​ 3.都可以搭配distinct使用，用于统计去重后的结果
​ 4.count的参数可以支持：字段||常量值，建议使用count(*)

分组查询

SELECT 查询的字段|分组函数
FROM 表名
GROUP BY 分组字段

特点：

1. 可以按单个字段分组
2. 和分组函数一同查询的字段最好是分组后的字段
3. 分组筛选

DDL语言

数据库和表的管理

数据库的管理

# 创建数据库
create database 数据库名;
# 删除数据库
drop database 数据库名;

数据表的管理

# 创建表
create table [if exists] test(
id int,
name varchar(20),
gender char,
...
);

# 修改表
# 修改字段名
alter table test change column gender sex char;
# 修改表名
alter table test rename [to] new_test;
# 修改字段类型和约束
alter table test modify column sex tinyint;
# 添加字段
alter table test add column email varchar(50) first;
# 删除字段
alter table test drop column email;

# 删除表
drop table [if exists] test;

常见约束

not null
default
unique
check
primary key
foreign key

数据库事务

含义

通过一组逻辑操作单元（一组DML，即SQL语句）将数据从一种状态切换到另一种状态

特点（ACID）

• 原子性 ：要么都执行，要么都不执行

• 一致性 ：保证数据的状态操作前后保持一致

• 隔离性 ：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰

• 持久性 ：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

事务的隔离级别

• 事务并发问题何时发生

当多个事务同时操作同一数据库的相同数据时

• 事务的并发问题有哪些

脏读：一个事务读取到了另一个事务未提交的数据
幻读：一个事务读取数据时，另一个事务进行更新操作，导致第一个事务读取到了没有更新的数据
不可重复读：同一个事务中，多次读取到的数据不一致

• 如何避免事务的并发问题

通过设置事务的隔离级别
read uncommitted 读未提交
read committed 读已提交（可以避免脏读）
repeatable read 可重复读 （可以避免脏读，不可重复读和一部分幻读）
serializable 可序列化 （可以避免脏读，不可重复读和幻读，但是效率比较低）

• 设置隔离级别

set session|global transaction isolation level 隔离级别名;

• 查看隔离级别

select @@tx_isolation;