MySql 核心技术

基本概念

启动和停止

方式一：计算机——右击管理——服务

方式二：通过管理员身份运行

1
2
net start 服务名（启动服务）
net stop 服务名（停止服务）

登录和退出

登录：mysql 【-h主机名 -P端口号 】-u用户名 -p密码

退出：exit或 ctrl+C

常见命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.查看表结构
desc 表名;
6.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

语法规范

  • 不区分大小写,但建议关键字大写，表名、列名小写

  • 每条命令最好用分号结尾

  • 每条命令根据需要，可以进行缩进 或换行

  • 注释：

    单行注释：#注释文字
    单行注释：– 注释文字
    多行注释：/* 注释文字 */

语言分类

DQL（Data Query Language）：数据查询语言（select）

DML (Data Manipulate Language):数据操作语言（insert 、update、delete）

DDL（Data Define Languge）：数据定义语言（create、drop、alter）

TCL（Transaction Control Language）：事务控制语言（commit、rollback）

DQL语言

基础查询

语法：

1
select 查询列表 from 表名;

特点：

1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

加号+作用：做加法运算

1
2
3
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;结果都为null

条件查询

语法：

1
2
3
select 查询列表
from 表名
where 筛选条件

分类：

简单条件运算符：> < = != <> >= <=

逻辑运算符：&& / and、|| / or、! / not

模糊查询：like、between and、in、is null /is not null
通配符：%任意多个字符，_任意单个字符

is null 和 <=> 比较：

普通类型的数值 null值 可读性
is null × √ √
<=> √ √ ×

排序查询

语法：

1
2
3
4
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc/desc】

特点：
1、asc ：升序，如果不写默认升序，desc：降序

2、排序列表 支持 单个字段、多个字段、函数、表达式、别名

3、order by的位置一般放在查询语句的最后（除limit语句之外）

常见函数

单行函数

字符函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#1.length 获取参数值的字节个数
SELECT LENGTH('john');

#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper 变大写 lower 变小写
SELECT UPPER('john');
SELECT LOWER('joHn');

#4.substr、substring 截取字符串  注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

#6.trim  去空格/指定字符
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;

#去除指定字符
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaa')  AS out_put;

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;

#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;

#9.replace 替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
数学函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);

#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); 

#truncate 截断
SELECT TRUNCATE(1.69999,1);  #1.6

#mod取余
/*
mod(a,b) ：  a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;
日期函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
其他函数
1
2
3
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
流程控制函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#1.if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
SELECT IF(10<5,'大','小');

#2.case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
#案例：
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

#case情况2
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
#案例：
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

分组函数

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

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

特点：
1、sum、avg一般用于处理数值型、max、min、count可以处理任何类型

2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、一般使用count(*)用作统计行数

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

效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)

1
2
3
4
5
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

分组查询

语法：

1
2
3
4
5
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段

2、筛选分为两类：分组前筛选和分组后筛选

3、分组可以按单个字段也可以按多个字段

4、可以搭配着排序使用

问题：分组函数做筛选能不能放在where后面
答：不能

问题：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by前
分组后筛选 having 分组后的结果 group by后 
1
2
3
4
5
6
7
#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：
    内连接：
        等值连接
        非等值连接
        自连接
    外连接：
        左外连接
        右外连接
        全外连接
    交叉连接

SQL92语法

等值连接

语法：

1
2
3
4
5
6
7
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分

非等值连接

语法：

1
2
3
4
5
6
7
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
自连接

语法：

1
2
3
4
5
6
7
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

SQL99语法

内连接

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

#案例
SELECT last_name,department_name
FROM departments d
JOIN  employees e
ON e.`department_id` = d.`department_id`;

特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

分类：等值连接、非等值连接、自连接

外连接

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

#案例
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;

特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表， full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

交叉连接

语法：

1
2
3
select 查询列表
from 表1 别名
cross join 表2 别名;

子查询

含义：
嵌套在其他语句内部的select语句称为子查询或内查询，
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询

分类：
1、按出现位置
select后面：仅仅支持标量子查询

from后面：表子查询

where或having后面：标量子查询、列子查询、行子查询

exists后面：标量子查询、列子查询、行子查询、表子查询

2、按结果集的行列
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列

分页查询

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select 查询列表
from 表
【join type】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

#案例1：查询前五条员工信息
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;

特点：
limit语句放在查询语句的最后，假如要显示的页数为page，每一页条目数为size

1
2
3
select 查询列表
from 表
limit (page-1)*size,size;

联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

语法：

1
2
3
4
5
6
7
8
9
10
查询语句1
union 【all】
查询语句2
union 【all】
...

#案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

查询总结

执行顺序：

1
2
3
4
5
6
7
8
9
select 查询列表    			7
from 表1 别名      		1
连接类型 join 表2   			2
on 连接条件         		3
where 筛选          		4
group by 分组列表   		5
having 筛选				6
order by排序列表    		8
limit 起始条目索引，条目数;  	9

DML语言

插入语句

语法：

1
2
3
insert into 表名(字段名,...) values(值,...);
或
insert into 表名 set 字段=值,字段=值,...;

特点：
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上，值使用null
4、字段和值的个数必须一致
5、字段名可以省略，默认所有列

两种方式 的区别：
1.方式一支持一次插入多行，语法如下：

1
insert into 表名【(字段名,..)】 values(值，..),(值，...),...;

2.方式一支持子查询，语法如下：

1
insert into 表名 查询语句;

修改语句

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
update 表名
set 列=新值,列=新值,...
where 筛选条件;

#修改多表的记录【补充】
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

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

#案例 修改多表的记录
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

删除语句

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
方式一：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
truncate table 表名;

delete和truncate的区别：

1.delete 可以加where 条件，truncate不能加

2.truncate删除，效率高一丢丢

3.假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。

4.truncate删除没有返回值，delete删除有返回值

5.truncate删除不能回滚，delete删除可以回滚.

DDL语言

库的管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#库的创建
create database 【if not exists】库名 库名【 character set 字符集名】;

#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books ;

#库的修改
RENAME DATABASE books TO 新库名;

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#库的删除
DROP DATABASE IF EXISTS books;

表的管理

表的创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table 表名(
  列名 列的类型【(长度) 约束】,
  列名 列的类型【(长度) 约束】,
  列名 列的类型【(长度) 约束】,
  ...
  列名 列的类型【(长度) 约束】
)

#案例：创建表Book

CREATE TABLE book(
  id INT,#编号
  bName VARCHAR(20),#图书名
  price DOUBLE,#价格
  authorId  INT,#作者编号
  publishDate DATETIME#出版日期
);

表的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

#添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;

#修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;

#修改列名
alter table 表名 change column 旧列名 新列名 类型;

#删除列
alter table 表名 drop column 列名;

#修改表名
alter table 表名 rename 【to】 新表名;

表的删除

1
drop table【if exists】 表名;

表的复制

1
2
3
4
5
#复制表的结构
create table 表名 like 旧表;
#复制表的结构+数据
create table 表名 
select 查询列表 from 旧表【where 筛选】;

数据类型

数值型

整型
tinyint smallint mediumint int bigint
字节 1 2 3 4 8

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号

②如果超出了范围，会报out or range异常，插入临界值

③长度可以不指定，默认会有一个长度，长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

1
2
3
4
#如何设置无符号
CREATE TABLE tab_int(
  t1 INT(7) ZEROFILL
);
浮点型

定点数：decimal(M,D)
浮点数：

float(M,D) double(M,D)
字节 4 8

特点：
①M代表整数部位+小数部位的个数，D代表小数部位

②如果超出范围，则报out or range异常，并且插入临界值

③M和D都可以省略，但对于定点数，M默认为10，D默认为0

④如果精度要求较高，则优先考虑使用定点数

字符型

较短的文本：char、varchar

其他：binary和varbinary用于保存较短的二进制，enum用于保存枚举，set用于保存集合

较长的文本：text、blob(较大的二进制)

特点：

char varchar
写法 char(M) varchar(M)
M的意思 最大的字符数，可以省略，默认为1 最大的字符数，不可以省略
特点 固定长度的字符 可变长度的字符
空间的耗费 比较耗费 比较节省
效率 高 低
日期型
分类 特点
date 只保存日期
time 只保存时间
year 只保存年
datetime 日期+时间
timestamp 日期+时间
字节 范围 时区影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受 
1
2
3
4
5
6
7
8
9
10
11
12
13
#案例
CREATE TABLE tab_date(
  t1 DATETIME,
  t2 TIMESTAMP

);
INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：

特点
NOT NULL 非空，用于保证该字段的值不能为空
DEFAULT 默认，用于保证该字段有默认值
PRIMARY KEY 主键，用于保证该字段的值具有唯一性，并且非空
UNIQUE 唯一，用于保证该字段的值具有唯一性，可以为空
CHECK 检查约束【mysql中不支持】
FOREIGN KEY 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值

添加约束的时机：创建表时、修改表时

约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持

主键和唯一的区别：

保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ 至多一个 √，但不推荐
唯一 √ √ 可以多个 √，但不推荐

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表

1
2
3
4
5
6
可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

创建表时添加约束

添加列级约束

1
2
3
4
5
6
7
#只支持：默认、非空、主键、唯一
create table 表名(
  字段名 字段类型 not null,#非空
  字段名 字段类型 primary key,#主键
  字段名 字段类型 unique,#唯一
  字段名 字段类型 default 值,#默认
)

添加表级约束

1
2
3
4
create table 表名(
  字段名 字段类型
  constraint 约束名 foreign key(字段名) references 主表（被引用列）
)
支持类型 起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以，但是对主键无效 
1
2
3
4
5
6
7
8
9
10
#案例
CREATE TABLE IF NOT EXISTS stuinfo(
  id INT PRIMARY KEY,
  stuname VARCHAR(20),
  sex CHAR(1),
  age INT DEFAULT 18,
  seat INT UNIQUE,
  majorid INT,
  CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

修改表约束

1
2
3
4
5
6
7
8
#添加/删除列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

#添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

#删除列级约束
ALTER TABLE 表名 DROP 约束类型 字段名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;

2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

自增长列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#创建表时设置自增长列
create table 表(
  字段名 字段类型 约束 auto_increment
)

#修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment

#删除自增长列
alter table 表 modify column 字段名 字段类型 约束 

#相关知识
SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;

特点：
1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment，如果要更改起始值：手动插入值，如果要更改步长：更改系统变量，set auto_increment_increment=值;

2、一个表至多有一个自增长列

3、自增长列只能支持数值型

4、自增长列必须为一个key

索引

1
2
3
4
5
6
7
8
9
ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)
1
2
3
4
5
DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

TCL语言

事务

含义：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
特点（ACID）

  • A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
  • C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
  • I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
  • D 持久性：一个事务一旦提交了，则永久的持久化到本地

隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#显式事务：具有明显的开启和结束
#步骤1：开启事务
set autocommit=0;
start transaction; #可省略
#步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
#可以设置保存点
savepoint 回滚点名;

#步骤3：结束/回滚事务
commit;提交事务
rollback;回滚事务
rollback to 回滚点名;

并发事务

多个事务同时操作同一个数据库的相同数据时，并发问题都有哪些？

  • 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
  • 不可重复读：一个事务多次读取，结果不一样
  • 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据

可以通过设置事务的隔离级别来解决以上的问题。

脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √ 
1
2
3
4
5
6
#mysql中默认 第三个隔离级别 repeatable read
#oracle中默认第二个隔离级别 read committed
#查看隔离级别
select @@tx_isolation;
#设置隔离级别
set session|global transaction isolation level 隔离级别;

其他

视图

mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#创建
create view 视图名
as
查询语句;

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

#方式二：
alter view 视图名
as
查询语句

#删除
drop view 视图1，视图2,...;

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

注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图

关键字 是否占用物理空间 使用
视图 view 占用较小，只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查

变量

系统变量

变量由系统提供的，不用自定义
语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
#查看系统变量
show 【global|session 】variables like ''; 如果没有显式声明global还是session，则默认是session

#查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session

#为系统变量赋值
#方式一：
set 【global|session 】 变量名=值; 如果没有显式声明global还是session，则默认是session

#方式二：
set @@global.变量名=值;
set @@变量名=值；

全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效

1
2
3
4
5
6
7
8
9
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的系统变量的值
SELECT @@global.autocommit;
#为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

1
2
3
4
5
6
7
8
9
10
#查看所有会话变量
SHOW SESSION VARIABLES;
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

用户变量
作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#赋值操作符：=或:=
#声明并赋值：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;

#更新值
#方式一：
set @变量名=值; 或
set @变量名:=值; 或
select @变量名:=值;
#方式二：
select xx into @变量名 from 表;

#使用
select @变量名;

局部变量
作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句

1
2
3
4
5
6
7
8
9
10
11
#声明
declare 变量名 类型 【default 值】;
#赋值或更新
#方式一：
set 变量名=值;或
set 变量名:=值;或
select @变量名:=值;
#方式二：
select xx into 变量名 from 表;
#使用
select 变量名;
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

存储过程

创建

1
2
3
4
5
6
7
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
    存储过程体
end
注意：
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾

注意：
1、参数列表包含三部分：参数模式 参数名 参数类型
举例：in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置

1
2
3
4
#语法
delimiter 结束标记
#案例：
delimiter $

调用

1
2
3
4
5
6
7
8
9
10
11
call 存储过程名(实参列表)

#举例：
#调用in模式的参数：
call sp1（‘值’）;

#调用out模式的参数：
set @name; call sp1(@name);select @name;

#调用inout模式的参数：
set @name=值; call sp1(@name); select @name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#案例
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
  SELECT boys.boyname ,boys.usercp INTO boyname,usercp
  FROM boys 
  RIGHT JOIN
  beauty b ON b.boyfriend_id = boys.id
  WHERE b.name=beautyName ;
  
END $

#多个out参数用into，逗号隔开
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$

查看

1
show create procedure 存储过程名;

删除

1
drop procedure 存储过程名;

函数

最后更新： 2021年01月20日 09:00

原始链接： https://midkuro.gitee.io/2020/09/08/mysql-technology/

赏
× 请我吃糖~
打赏二维码