MySQL学习笔记

基础概念
DB(Database): 数据库，保存一组有组织的数据的容器
DBMS(Database Management System): 数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
SQL(Structure Query Language): 结构化查询语言，用于和DBMS通信的语言

MySQL服务的启动和停止

1. 计算机管理-服务和应用程序-服务-MySQL
2. CMD（以管理员方式打开）: net start/stop mysql

MySQL服务的登录和退出
登录：1.自带的客户端（只限于root用户）
2.CMD（以管理员方式打开）: mysql -h localhost -P 端口号 -u 用户名 -p密码（密码前不能有空格）
简写：mysql -u 用户名 -p密码

退出：1. Exit
2. Ctrl+C

配置环境变量
计算机属性-高级系统设置-环境变量-复制bin文件夹路径

MySQL的常见命令
1.查看当前所有到数据库
show databases;
2.打开指定的库
use 库名
3.查看当前苦的所有表
show tables;
4.查看其他库到所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型，
列名 列类型);
6.查看表结构
desc 表名;
7.查看服务器版本
方式一：登陆到mysql服务端
select version();
方式二：没有登陆到mysql服务端
mysql --version/mysql -V

MySQL的语法规范
1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
单行注释：#注释文字/–注释文字
多行注释：/* 注释文字 */

DQL(Data Query Language) 数据查询语言
1.基础查询
Select 查询列表 from 表名;
查询列表可以是：表中的字段、常量值、表达式、函数
查询所有字段：Select *
将代码格式化：F9
起别名：Select a As “b”
去重：Select Distinct
连接字符串函数：Concat(‘a’,’b’)
修改null值函数：IFNULL(a,0)
判断null值函数：ISNULL(a) 返回值为1（是），0（否）

2.条件查询
Select
查询列表
from
表名
where
筛选条件;

按条件表达式筛选
条件运算符：> < = <> >= <=

按逻辑表达式筛选（用于连接条件表达式）
逻辑运算符：and or not

模糊查询
like 配合通配符：%（任意多字符）、_（单一字符）

between and 包含临界值，不可调换顺序
in 判断某字段中的是否属于in列表中的某一项
is null/is not null
安全等于 <=> 既可查询普通值，也可查询null值
转义字符：
指定转义字符函数：Escape ‘a’

3.排序查询
Select 查询列表
from 表名
[where 筛选条件]
order by 排序列表 (asc/desc)
排序列表可以是：单个字段、多个字段、表达式、函数、别名

4.常见函数
Select 函数名()
单行函数
字符函数
length 获取参数值到字节个数
concat 拼接字符串
upper/lower 变换大小写
substr/substring 截取字符串 （索引才1开始）
instr 返回子串在原字符串的起始索引
trim 去除前后字符 trim(‘’ from 字符串)
lpad 用指定字符实现左填充指定长度
rpad 用指定字符实现右填充指定长度
replace 替换
数学函数
round 四写五入
ceil 向上取整
floor 向下取整
truncate 截断
mod(%) 求余 mod(a,b)=a-a/bb
rand 取随机数
日期函数
now 返回当前系统日期和时间
curdate 返回当前系统日期
curtime 返回当前系统时间
str_to_date 将字符通过指定的形式转换成日期
date_format 将日期转换成字符
datediff 日期之差（大日期在前）
monthname 以英文形式返回月
其他函数
version 当前数据库服务器版本
database 当前打开的数据库
user 当前用户
password 返回字符的密码形式
md5 返回字符的md5加密形式
流程控制函数
if
case函数
case [要判断的字段或表达式]
when 条件1 then 值1或语句1;
when 条件2 then 值2或语句2；
else 值n或语句n；
end
分组函数（统计函数、聚合函数、组函数）
sum、avg、max、min、count
以上分组函数都忽略null值
可搭配distinct
统计行数：count(/任意值)

5.分组查询
Select 查询列表
from 表名
[where 筛选条件]
group by 分组列表
[having 筛选条件]（分组后的筛选）
[order by 排序列表 (asc/desc)]

6.连接查询（多个表的字段）
sql92标准
内连接：等值连接、非等值连接、自连接
sql99标准
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
内连接：inner
外连接（查询一个表中有另一个表中没有的记录）：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：cross

7.子查询（内查询）
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或having后面
标量子查询
列子查询
（行子查询）
exists后面（相关子查询）
表子查询
标量子查询：结果集只有一行一列
搭配单行操作符：< > >= <= = <>
列子查询：结果集只有一列多行
搭配多行操作符：in/not in any|some all
行子查询：结果集有一行多列、多行多列
表子查询：结果集一般为多行多列
8. 分页查询
select 查询列表
from 表名
limit offset,size
offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

9.联合查询
查询语句1
union [all(包含重复项)]
查询语句2

DML(Data Manipulation Language) 数据操纵语言
1.插入：insert
方式一：insert into 表名（列名1,…） values（值1,…）,(…)
支持多行插入、子查询

方式二：insert into 表名
set 列名1=值2，列名2=值2,…

2.修改：update
修改单表：update 表名
set 列1=新值1，列2=新值2，…
where 筛选条件;

修改多表：
sql92语法
update 表1 别名，表2 别名
set 列=值，…
where 连接条件
and 筛选条件;

sql99语法
update 表1 别名
inner|left|right| join 表2 别名
on 连接条件
set 列=值，…
where 筛选条件;

3.删除：delete
方式一：
删除单表
delete from 表名 where 筛选条件;

删除多表
sql92语法
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件;

sql99语法
delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件;

方式二：truncate table 表名;（清空数据、无返回值）

DDL(Data Definition Language) 数据定义语言
1.库的管理
创建
create database [if not exists] 库名;

修改（更改字符集）
alter database 库名 character set 新字符集;

删除
drop database [if exists] 库名;

2 表的管理
创建
create table 表名(
列名 列的类型 [(长度)约束],
列名 列的类型 [(长度)约束],
…
);

修改
修改列名
alter table 表名 change column 旧列名 新列名 类型;
修改类型或约束
alter table 表名 modify column 列名 新类型;
添加新列
alter table 表名 add column 新列名 类型 [first|after 列名];
删除列
alter table 表名 drop column 列名;
修改表名
alter table 表名 rename to 新表名;

删除
drop table 表名;

复制
仅复制表的结构
create table 表名 like 旧表名;
复制表的结构+数据
create table 表名
select 查询列表 from 旧表名
[where 筛选条件];

3.常见的数据类型
数值型
整型：tinyint、smallint、mediumint、int/integer、bigint
无符号：unsigned
零填充：zerofill
小数：
浮点数：float(M,D)、double(M,D)
定点数：dec(M,D)、decimal(M,D)
M:整数部位+小数部位
D:小数部位
字符型
较短的文本：char、varchar(可变长度的字符)
较长的文本：text、blob
枚举型：Enum
集合型：Set(可选多个值)
日期型
date 只保存日期
time 只保存时间
year 只保存年份
datetime 保存日期+时间
timestamp 保存日期+时间（受时区影响）

4. 常见的约束
   not null：非空
   default：默认值
   primary key：主键，保证非空且唯一性
   unique：唯一性
   check：检查约束(mysql中不支持)
   foreign key：外键，保证值来自于主表的关联列（主键或唯一）的值（references）

   列级约束：六大约束语法都支持，但外键约束没有效果
   表级约束：除了非空、默认，其他的都支持
   [constraint 约束名] 约束类型（列名）

查看索引：show index from 表名;

添加约束
列级：alter table 表名 modify column 列名 类型 约束;
表级：alter table 表名 add [constraint约束名] 约束(列名);
删除约束
删除主键/外键
alter table 表名 drop primary key/foreign key 列名;
删除唯一
alter table 表名 drop index 索引名;

标识列(自增长列)
auto_increment
设置步长
set auto_increment_increment=3;

级联删除
添加外键时在最后加 on delete cascade

级联置空
添加外键时在最后加 on delete set null

TCL(Transaction Control Language) 事务控制语言
显式事务
开启事务
set autocommit=0;
[start transaction;]
编写事务中的sql语句（select、insert、update、delete）
结束事务
commit; 提交事务
rollback; 回滚事务
truncate 无法回滚
savepoint 节点名; 设置保存点

事务的隔离级别
read uncommitted：出现脏读、不可重复读、幻读
read committed：出现不可重复读、幻读
repeatable read：出现幻读
serialization：全部避免

mysql中默认第三个隔离级别 repeatable read

查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

视图
创建
create view 视图名
as
查询语句;

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

删除
drop view 视图1，视图2，…;

查看
desc 视图名;
show create view 视图名;

使用
插入 insert
修改 update
删除 delete
查看 select
具备以下特点的视图不允许更新：
包含分组函数、group by、distinct、having、union
join
常量视图
where后的子查询用到了from中的表
用到了不可更新的视图

变量
系统变量

全局变量
作用域：服务器
会话变量
作用域：当前会话
查看系统变量
show global|session variables;
查看满足条件的部分系统变量
show global|session variables like ‘%char%’;
查看指定的某个系统变量的值
select @@global|session.系统变量名
为某个系统变量赋值
方式一：set global|session 系统变量名=值;
方式二：set @@global|session.系统变量名=值;

自定义变量

用户变量
作用域：当前会话
声明并初始化
set @用户变量名=值;或
set @用户变量名:=值;或
select @用户变量名:=值;或
赋值
方式一：set @用户变量名=值;或
set @用户变量名:=值;或
select @用户变量名:=值;或
方式二：select 字段 into @用户变量名
from 表;
使用
select @用户变量名;

局部变量
作用域：仅仅在定义它的begin end中有效
声明
declare 变量名 类型;
declare 变量名 类型 default 值;
赋值
方式一：set 局部变量名=值;或
set 局部变量名:=值;或
select @部变量名:=值;或
方式二：select 字段 into 局部变量名
from 表;
使用
select 局部变量名;

存储过程和函数
存储过程
创建
create procedure 存储过程名(参数列表)
begin
存储过程体
end
参数列表
参数模式
in 该参数可以作为输入
out 该参数可以作为输出
inout 该参数既可以作为输入又可以作为输出
参数名
参数类型
存储过程的结束
先定义结束标记 delimiter 结束标记
放在end 后

调用
call 存储过程名(实参列表) 结束标记

删除
drop procedure 存储过程名;

查看结构
show create procedure 存储过程名;

函数
创建
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
参数列表
参数名
参数类型

调用
select 函数名(参数列表)

查看结构
show create function 函数名;

删除
drop function 函数名;

流程控制结构
分支结构
if函数
case结构
作为独立语句，结尾为end case; (应用在begin end中)
if结构
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
应用在begin end中

循环结构
while
[标签:] while 循环条件 do
循环体;
end while [标签];

loop
[标签:] loop
end loop [标签];

repeat
[标签:] repeat
循环体;
until 结束循环的条件
end repeat [标签];

循环控制
iterate
leave