MySQL数据库知识总结

共18853字,阅读约63分钟
图片[1]-MySQL数据库知识总结-墨初博客

一、基本操作

1. 连接数据库操作

/*连接mysql*/
mysql -h 地址 -P 端口 -u 用户名 -p 密码
例如: mysql -h 127.0.0.1 -P 3306 -u root -p ****

/*退出mysql*/
exit;

2. 数据库操作

#数据库操作
/*关键字:create 创建数据库(增)*/
create database 数据库名 [数据库选项];
例如: create database test default charset utf8 collate utf8_bin;
/*数据库选项:字符集和校对规则*/
字符集:一般默认utf8;
校对规则常见: ⑴ci结尾的:不分区大小写 ⑵cs结尾的:区分大小写 ⑶bin结尾的:二进制编码进行比较

/*关键字:show 查看当前有哪些数据库(查)*/
show databases;

/*查看数据库的创建语句*/
show create database 数据库名;

/*关键字:alter 修改数据库的选项信息(改)*/
alter database 数据库名 [新的数据库选项];
例如: alter database test default charset gbk;

/*关键字:drop  删除数据库(删)*/
drop database 数据库名;

/*关键字:use 进入指定的数据库*/
use 数据库名;

3. 表的操作

#表的操作
/*关键字:create 创建数据表(增)*/
create table 表名(
字段1  字段1类型 [字段选项],
字段2  字段2类型 [字段选项],
字段n  字段n类型 [字段选项]
)表选项信息;
例如: create table test(
  id int(10) unsigned not null auto_increment comment 'id',
  content varchar(100) not null default '' comment '内容',
  time int(10) not null default 0 comment '时间',
  primary key (id)
)engine=InnoDB default charset=utf8 comment='测试表';
--ENGINE 设置存储引擎,CHARSET 设置编码, comment 备注信息
/*关键字:show 查询当前数据库下有哪些数据表(查)*/
show tables;

/*关键字:like 模糊查询*/
通配符:_可以代表任意的单个字符,%可以代表任意的字符
show tables like '模糊查询表名%';

/*查看表的创建语句*/
show create table 表名;

/*查看表的结构*/
desc 表名;

/*关键字:drop  删除数据表(删)*/
drop table [if exists] 表名
例如: drop table if exists test;

/*关键字:alter 修改表名(改)*/
alter table 旧表名 rename to 新表名;

/*修改列定义*/
/*关键字:add 增加一列*/
alter table 表名 add 新列名 字段类型 [字段选项];
例如: alter table test add name char(10) not null default '' comment '名字';

/*关键字:drop 删除一列*/
alter table 表名 drop 字段名;
例如: alter table test drop content;

/*关键字:modify 修改字段类型*/
alter table 表名 modify 字段名 新的字段类型 [新的字段选项];
例如: alter table test modify name varchar(100) not null default 'admin' comment '修改后名字';

/*关键字:first 修改字段排序,把某个字段放在最前面*/
alter table 表名 modify 字段名 字段类型 [字段选项] first;
例如: alter table test modify name varchar(100) not null default 'admin' comment '最前面' first;

/*关键字:after 修改字段排序,字段名1放在字段名2的后面*/
alter table 表名 modify 字段名1 字段类型 [字段选项] after 字段名2;
例如: alter table test modify name varchar(100) not null default 'admin' comment 'time字段后面' after time;

/*关键字:change 重命名字段*/
alter table 表名 change 原字段名 新字段名 新的字段类型 [新的字段选项];
例如: alter table test change name username varchar(50) not null default '' comment '用户名字';

/*修改表选项*/ 
alter table 表名 表选项信息;
例如: alter table test engine Myisam default charset gbk; --修改存储引擎和修改表的字符集

4. 数据操作

增加 insert
删除 delete
修改 update
查询 select

#数据操作
/*关键字:insert 插入数据(增)*/
insert into 表名(字段列表) values(值列表);
--例子
insert into user(id,name,age) values(1,'admin_a',50);
insert into user(name) values('admin_b');

/*关键字:select 查询数据(查)*/
select *[字段列表] from 表名[查询条件];

/*关键字:delete 删除数据(删)*/
delete from 表名[删除条件];
例如: delete from user where age<1;--删除age小于1数据

/*关键字:update 修改数据(改)*/
update 表名 set 字段1=新值1,字段n=新值n [修改条件];
例如: update user set age=100 where name='admin_a';

二、数据类型

大致分为三大类:数值,日期/时间,字符串

-- 常用数据类型
int, float, double
date, time, year, datetime
char, varchar, text

1. 数值类型

图片[2]-MySQL数据库知识总结-墨初博客

2. 日期和时间类型

图片[3]-MySQL数据库知识总结-墨初博客

3. 字符串类型

图片[4]-MySQL数据库知识总结-墨初博客

char是定长的,索引效率很高,char(10),不论你存储的数据是否达到了10个字节,都要占去10个字节的空间,当你输入的字符小于10时会在后面补空值,而大于10时会截取超出的字符
varchar的存储效率没有CHAR高,但在存储的字符串是变长时,varchar更加节约空间

如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar。
因为一般情况下,英文字符占一个字节,汉字占两个字节,同时存在容易造成混乱。nchar和nvarchar表示存储Unicode数据类型的字符,其所有的字符都用两个字节表示,但在存储英文时数量上有些损失。

三、建表约束

  • 建表的时候就添加约束
  • alter … add …
  • alter … modify …
  • alter … drop …

1. 主键约束

每个表只能定义一个主键
唯一性原则:非空且不重复

/*单一主键*/
create table user(
	id int primary key,
	name varchar(20)
);

/*联合主键 - 只要联合的主键值加起来不重复就行,但都得非空*/
create table user2(
    id int,
    name varchar(20),
    sex char,
    primary key(id, name) 
);

-- 建表后对主键约束的添加, 删除, 与修改
create table user3(
    id int,
    name varchar(20)
);
//alter 修改	modify 修改字段
alter table user4 add primary key(id);
alter table user4 drop primary key;
alter table user4 modify id int primary key;
alter  table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释';

2. 自增约束

一个表只能有一个自增约束,因为一个表只有一个维护自增值的变量
自增约束的列只能是整数列,且必须是键列(主键,唯一键,外键),实际中一般是主键自增最多

create table user4(
	id int primary key auto_increment,
	name varchar(20)
);
insert into user4 (name) values('张三');
insert into user4 (name) values('张三');

3. 唯一约束

可以有多个,约束修饰的字段的值不可以重复
唯一约束的列允许有空值,但是只能有一个空值

create table user5(
	id int unique,
	name varchar(20)
);

-- 组合在一起不重复,单个得分开加
create table user6(
	id int,
	name varchar(20),
	unique(id, name)	
);

4. 非空约束

修饰的字段不能为空

create table user7(
	id int,
	name varchar(20) not NULL	
);

5. 默认约束

插入字段值的时候,未传值则使用默认值

create table user8(
	id int primary key auto_increment,
	name varchar(20),
	age int default 10
);
insert into user8(name) values('111');
insert into user8(name) values('111');

6. 外键约束

用来建立主表与从表的关联关系,约束两个表中数据的一致性和完整性

定义外键时,需要遵守以下规则
  • 主表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性。
  • 必须为主表定义主键。
  • 主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
  • 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
  • 外键中列的数目必须和主表的主键中列的数目相同。
  • 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
-- 班级表(主表)
create table classes(
	id int primary key,
	name varchar(20)
);

-- 学生表(从表/副表)
create table students(
	id int primary key,
	name varchar(20),
	class_id int,
	foreign key(class_id) references classes(id)
);

-- 主表 classes 中没有的数据值,在副表中是不可以使用的
-- 主表中的记录被副表引用时,是不可以被删除的

四、设计范式

范式 ( NF ):Normal Format,是一种离散数学中的知识,是为了解决数据的存储与优化的问题,保存数据的存储之后,凡是能够通过关系寻找出来的数据,坚决不再重复存储,终极目标是为了减少数据的冗余

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。一般说来,数据库只需满足第三范式(3NF)就行了

1. 第一范式 – 1NF

所有属性都是不可分割的原子值

(确保每一列不可再分)

  • 在设计表存储数据时候,如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理(拆分),那么表的设计不满足第一范式
  • 第一范式要求字段具有原子性、不可再分割
-- 字段值还可以继续拆分的,不满足第一范式
create table student(
	id int primary key,
	name varchar(20),
	address varchar(30)
);
insert into student values(1, '张三', '中国四川省成都市金牛区');
insert into student values(2, '李四', '中国四川省成都市武侯区');
insert into student values(3, '王五', '中国四川省成都市高新区');


-- 应该满足以下形式
create table student2(
	id int primary key,
	name varchar(20),
	country varchar(30),
	province varchar(30),
	city varchar(30),
	details varchar(30)
);
insert into student2 values(1, '张三', '中国', '四川省', '成都市', '金牛区');
insert into student2 values(2, '李四', '中国', '四川省', '成都市', '武侯区');
insert into student2 values(3, '王五', '中国', '四川省', '成都市', '高新区');

2. 第二范式 – 2NF

在第一范式的基础上,要求非主属性都要和码有完全依赖关系

(确保表中每一列都和主键有关)

  • 在数据表设计过程中,如果有复合主键(多字段主键),且表中有字段并不是由整个主键来确定,而是依赖于主键中的某个字段(主键的部分)
  • 存在字段依赖主键的部分的问题,称之为部分依赖
  • 第二范式就是解决表设计不允许出现部分依赖
-- 除主键以外的其他列,只依赖于主键的部分字段,部分依赖
create table myorder(
	product_id int,
	customer_id int,
	product_name varchar(20),
	customer_name varchar(20),
	primary key(product_id, customer_id)
);


-- 拆表,分成多个表之后就满足第二范式
create table myorder(
	order_id int primary key,
	product_id int,
	customer_id int
);
create table product(
	id int primary key,
	name varchar(20)
);
create table customer(
	id int primary key,
	name varchar(20)
);

3. 第三范式 – 3NF

任何非主属性不依赖于其它非主属性

(确保每一列都和主键列直接相关,而不是间接相关)

  • 要满足第三范式、必须满足第二范式
  • 理论上讲,应该一张表中的所有字段都应该直接依赖于主键,如果表设计中存在一个字段,并不直接依赖主键,而是通过某个非关键字段依赖,最终实现依赖主键,把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖
  • 第三范式就是解决传递依赖的问题
-- 除开主键列的其他列之间有传递依赖关系,不满足
create table myorder(
	order_id int primary key,
	product_id int,
	customer_id int,
	customer_phone varchar(15)
);


-- 应该把phone提出来
create table myorder(
	order_id int primary key,
	product_id int,
	customer_id int
);
create table customer(
	id int primary key,
	name varchar(20),
	phone varchar(15)
);

4. BC范式 – BCNF

在 3NF 的基础上消除任何属性(主属性和非主属性)对关系键的部分函数依赖和传递函数依赖

不管这些A、B、C属性是主属性还是非主属性,不存在部分函数依赖 (AB)–>C , B–>C,也不存在传递函数依赖A–>B , B–>C,就是BC范式

图片[5]-MySQL数据库知识总结-墨初博客

五、查询

1. 基本语句

SELECT column_name,column_name
FROM table_name [WHERE Clause][LIMIT N][ OFFSET M]

在查询过程中的执行顺序

from > where > group(含聚合)> having > order > select

完整书写顺序

SELECT column_name,column_name
FROM table_name [WHERE Clause][GROUP BY ][HAVING Clause][ORDER BY ][LIMIT N][ OFFSET M]

2. 别名 – AS或空格加别名

SELECT id AS 学号, name 姓名 FROM exam;

3. 去重 – DISTINCT

SELECT DISTINCT math FROM exam;

4. 排序 – ORDER BY

ASC升序,DESC降序,默认ASC
NULL 数据排序,视为比任何值都小
可以对多个字段进行排序,排序优先级随书写顺序

-- 查询所有人的成绩,按照数学成绩排序,从高到低,如果数学成绩相等,按照语文成绩排序,从低到高,如果语文成绩相等,按照id排序,从高到底
select * from exam 
order by math desc, chinese asc, id desc;

5. 分组 – GROUP BY

根据一个或多个字段对查询结果进行分组

-- 统计学生出现的次数,并按次数的升序排列
select name, count(*) from exam group by name order by count(name);

6. 条件查询 – WHERE

WHERE条件可以使用表达式,但不能使用别名,条件为true才返回
AND的优先级高于OR,在同时使用时,需要使用小括号()包裹优先执行的部分

图片[6]-MySQL数据库知识总结-墨初博客

① BETWEEN … AND …

between语句范围匹配,[a0, a1],如果 a0 <= value <= a1,返回 TRUE(1)

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

② IN……OR……

范围查询 IN (option, …) 如果是 option 中的任意一个,返回 TRUE(1)

select * from exam_result where name in ('孙悟空', '唐三藏');
select * from exam_result where name = '孙悟空' or name = '唐三藏';

③ NULL

不能使用 = 或者 != 判断,null值在where中被视为false,一般就以下三种表达形式

-- 查询已经登记过 qq 邮箱的同学
select * from student where qq_mail is not null;
select * from student where qq_mail is null;
select * from student where qq_mail <=> null;

④ 模糊查询:LIKE

% 表示任意多个(包括 0 个)任意字符
_ 表示任意一个字符

-- % 匹配任意多个(包括 0 个)字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权
-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权

7. 分页查询 – LIMIT

偏移量是从0号开始的

-- 起始下标为 0
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

8. 聚合查询 – COUNT…

图片[7]-MySQL数据库知识总结-墨初博客

9. 条件过滤 – HAVING子句

having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
聚合语句在having之前,在where子句之后执行,所以having后面可以使用聚合函数,where后面不可以使用聚合函数
在查询过程中执行顺序:from > where > group(含聚合)> having > order > select

-- 通过学号筛选出总分大于400的学生
SELECT 
    id,
    name,
    SUM(score) AS total
FROM
    exam
GROUP BY id
HAVING total > 400;

10. 连接查询(联合查询)

没有条件的联表,结果是一个笛卡尔积, 有效数据,需要通过一些条件过滤,关联查询可以对关联表使用别名

-- 内连接
join (inner join)

-- 外连接
left join (left outer join) 左连接
right join (right outer join) 右连接
full join (full outer join) 完全外连接
图片[8]-MySQL数据库知识总结-墨初博客

内连接

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

② 外连接

外连接分为左外连接和右外连接
联合查询,如果左侧的表完全显示我们就说是左外连接,右侧的表完全显示我们就说是右外连接

-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

③ 自连接

当您想将表中行与同一表中的其他行组合时,可以使用自连接
要执行自连接操作必须使用表别名来帮助MySQL在单个查询中区分左表与同一张表的右表

SELECT c1.cust_id ,c1.cust_name ,c1.cust_adress
FROM customers as c1 ,customers as c2
where c2.cust_company = '爱艺奇'
and c1.cust_name = c2.cust_name

④ 子查询

子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询,常常跟在from、where后面

单行子查询:返回一行记录的子查询,子查询的结果只有一行一列,可以把这个结果当成具体数值来使用
多行子查询:返回多行记录的子查询,只有一列,但有很多行,可以当成一组数据,配合in、max、min等方法使用

子查询必须在小括号里,写在from里面子查询必须加上别名

-- 查询部门人数 < 3 人的部门
select depart_id, count(*) from employee group by depart_id having count(*) < 3;
--子查询:
select
    depart_id, cnt
from (select depart_id, count(*) cnt from employee group by depart_id) t
where t.cnt < 3;

子查询中的IN和EXISTS

EXISTS用于检查子查询是否至少会返回一行数据,返回值True就保留这行的结果,false不保留

查找表中所有重复的电子邮箱
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

-- 方法 1 利用聚合查询完成我们的操作
select Email from Person group by Email having count(*) > 1;
-- 方法 2 利用子查询的 in 完成
select distinct Email from Person p1 where Email in (select Email from Person p2 where p1.Id != p2.Id); 

-- 方法 3 利用子查询的 exists 完成
select distinct Email from Person p1 where exists (
  select Email from Person p2 where p1.Id != p2.Id and p1.Email = p2.Email
);
  • IN:在范围内的值,只要有就true
  • ALL: 与子查询返回的所有值比较为true 则返回true
  • ANY:与子查询返回的任何值比较为true 则返回true
  • SOME:是ANY的别称,很少用
/*
举个例子: 表1(table1)中 列r1有 1 2 8  表2(table2)中  列r2有 2 3
*/
-- 用IN举例子 返回的结果是 2 
SELECT r2 FROM table2 WHERE r2 IN (SELECT r1 FROM table1);
        
-- ALL例子 没有返回结果  因为2 3没有都大于或等于1 2 8 所有的数值,也就是子查询中的所有值都成立则成立 
SELECT r2 FROM table2 WHERE r2 >= ALL(SELECT r1 FROM table1);
-- ANY例子 返回结果是2 3 只要子查询中返回的值 有一个成立则成立
SELECT r2 FROM table2 WHERE r2 >= ANY(SELECT r1 FROM table1);            

11. 控制语句 – CASE WHEN

简单函数:枚举这个字段所有可能的值

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

-- 例子
SELECT
    course as '课程',
    CASE course
        WHEN '高数' THEN
            '难'
        WHEN '英语' THEN
            '一般'
        WHEN '物理' THEN
            '简单'
        ELSE
            '一般'
    END '难度'
FROM
    class;

搜索函数:搜索函数可以写判断,并且搜索函数同时满足时只会返回第一个符合条件的值,其他case被忽略

CASE WHEN [expr] THEN [result1]…ELSE [default] END

-- 例子1
SELECT
    name '姓名',
    age '年龄',
    CASE
        WHEN age < 18 THEN '少年'
        WHEN age < 30 THEN '青年'
        WHEN age >= 30 AND age < 50 THEN '中年'
        ELSE '老年'
    END '状态'
FROM
    resident;

-- 例子2
SELECT
    sno AS '学号',
    sum(credit) AS Total,
    CASE
        WHEN sum(credit) > 30 THEN '优秀'    -- 注意这里不能用Total,select过程在最后,但是having筛选可以用
        WHEN sum(credit) BETWEEN 25 AND 30 THEN '良好'    -- [25, 30]
        WHEN sum(credit) > 20 AND sum(credit) < 25 THEN '正常'
        ELSE '应努力'
    END '等级'
FROM course, sc
WHERE sc.cno = course.cno
GROUP BY sno

12. 合并查询 – UNION

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符UNION和UNION ALL,使用时前后查询的结果集中,字段需要一致

union 该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行
union all该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行

六、存储例程

图片[9]-MySQL数据库知识总结-墨初博客

1. 变量的定义

在调用存储过程时,以declare声明的变量都会被初始化为null,变量名不区分大小写
而会话变量(即@开头的变量)则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量

定义后的变量采用set语句进行赋值

-- 用户变量,可以在一个会话的任何地方声明,作用域是整个会话
set @var=1; 

-- 存储过程变量,只能在存储过程中使用,或者是给存储传参数
declare var int default 0;
以下为补充内容
  • 局部变量 
  • 用户变量 
  • 会话变量 
  • 全局变量 

会话变量和全局变量叫系统变量

一、局部变量,只在当前begin/end代码块中有效

局部变量一般用在sql语句块中,比如存储过程的begin/end。其作用域仅限于该语句块,在该语句块执行完毕后,局部变量就消失了。declare语句专门用于定义局部变量,可以使用default来说明默认值。set语句是设置不同类型的变量,包括会话变量和全局变量

declare var_name [, var_name]... data_type [ DEFAULT value ];

二、用户变量,在客户端链接到数据库实例整个过程中用户变量都是有效的

-- 这里要使用set语句创建并初始化变量,直接使用@num变量
第一种用法:set @num=1; 或set @num:=1;  
第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where ……, 
-- select语句一般用来输出用户变量,比如select @变量名,用于输出数据源不是表格的数据

三、会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接数据库实例时,使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效

set session var_name = value;
set @@session.var_name = value;
set var_name = value;  #缺省session关键字默认认为是session

-- 查看所有的会话变量
show session variables;

-- 查看一个会话变量有如下三种方式
select @@var_name;
select @@session.var_name;
show session variables like "%var%";

四、全局变量

全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量,必须具有super权限。全局变量作用于server的整个生命周期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置文件

-- 要设置一个全局变量,有如下两种方式:
set global var_name = value; //注意:此处的global不能省略。根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION
set @@global.var_name = value; //同上

-- 查看所有的全局变量 
show global variables; 

-- 要想查看一个全局变量,有如下两种方式:
select @@global.var_name;
show global variables like “%var%”;

2. 流程控制

-- 条件语句
          if 布尔表达式 then
               处理语句
          elseif 布尔表达式 then
               处理语句
          else 
               处理语句
          end if;

-- 循环语句
          -- 方式1
          while 布尔表达式 do
               循环语句
          end while;

          -- 方式2
          repeat
               循环语句
          until 布尔表达式 end repeat;

          -- 方式3
          循环标记:loop
               循环语句
               leave 循环标记;   -- leave语句一般和判断语句一起用,用来退出循环
          end loop 循环标记

3. 存储过程 – PROCEDURE

由过程化SQL语句书写的过程,经编译和优化后存储在数据库服务器中,使用时只要调用即可

CREATE PROCEDURE 存储过程名称([参数类型 参数名 数据类型]组成的参数列表)
-- 存储过程的参数类型有三种:in out inout
-- in:实参可为常量/变量;实参为变量时,只能向外赋值,不能被赋值(即in型参数的值不会被存储过程更改)
-- out:实参只能是变量;实参不能向外赋值,只能被赋值
-- inout:实参只能是变量;实参既可向外赋值,又可被赋值
BEGIN
    需要执行的语句,可包含内容和存储函数是一样的。
END    


-- 1.创建存储过程
	delimiter ||
		create procedure test(in in_count int)
		begin 
			declare count int default 0;
			declare sum int default 0;
			while count < in_count do
				set sum = sum + count;
				set count = count + 1;
			end while;
			select sum;
		end ||
	delimiter ;
	
-- 2.调用存储过程
	call test(10);
	
-- 3.查看储存过程
	-- 查看定义的存储过程有哪些
	show procedure status [like 需要匹配的函数名]
	-- 查看某个存储过程的详细定义
	show create procedure test
	
-- 4.删除存储过程
	drop procedure test;
	或者drop procedure if exists test;
	
-- 5.输入输出参数
	delimiter ||
	create procedure query_2(in in_ename varchar(100), out out_eamount int)
		begin
			select eamount into out_eamount from let where ename = in_ename;
		end ||
	delimiter ;
	
	call query_2("广州美能达电器有限公司", @a);
	select @a as eamount;
	
	drop procedure query_2;

4. 存储函数 – FUNCTION

函数和存储过程都是持久性存储模块,但是函数必须指定返回的类型

CREATE FUNCTION 存储函数名称([参数名 数据类型]组成的参数列表) comment ‘函数的注释说明’ 
RETURNS 返回值类型
BEGIN
    函数体内容
    -- 函数体中,每条语句都要以分号;结尾
    -- 函数体内容可以包含:
    -- 1. SQL语句
    -- 2. 变量定义(此时变量的定义用declare,不加@)和赋值
    -- 3. 流程控制语句
END


-- 1.创建函数
	delimiter $$
	create function test(quantity int(10))returns int(10)
	begin
		while quantity mod 12 > 0 do
			set quantity = quantity + 1;
		end while;
		return quantity;
	end $$
	delimiter ;
	
	-- 注意:创建函数之前必须更改系统设置,否则无法创建函数
	set global log_bin_trust_function_creators = TRUE;
	
-- 2.调用函数
	select test(10);
	select test(24);
	select test(222);

-- 3.查看函数
	-- 查看定义的函数有哪些
	show function status [like 需要匹配的函数名]
	-- 查看某个函数的具体定义
	show create function 函数名

-- 4.删除函数
	drop function test;

5. 游标 – CURSOR

类似于迭代器,用来标记结果集中正在访问的某一行记录,初始状态下它标记结果集中的第一条记录,每次使用后自动移动到下一条记录的位置

游标可以用在存储函数中和存储过程中时,游标的创建、打开、调用和关闭,均在存储函数和存储过程中完成

-- 创建
DECLARE 游标名称 CURSOR FOR 查询语句; 
-- 注意,创建游标的语句必须放在变量声明的后面

-- 打开
-- 打开游标意味着执行查询语句,让创建好的游标与该查询语句得到的结果集关联起来
OPEN 游标名称;

-- 调用
-- 把指定游标对应记录的各列的值依次赋值给INTO后边的各个变量。赋值后,游标自动移动到下一条记录的位置。
-- fetch语句一般用在循环语句中
FETCH 游标名 INTO 变量1, 变量2, ... 变量n

-- 关闭
-- 关闭游标意味着会释放该游标占用的内存。
CLOSE 游标名称;

-- 例子
delimiter //
	create procedure query_2()
	begin
		declare done int default 0;
		declare l_eno char(7);
		declare the_eamount int;
		declare cur cursor for select eno from let where eno in (select eno from loant);
		declare continue handler for not found set done = 1;
		create temporary table tmp_table(
			eno char(7) not null,
			eamount int
		);
		open cur;
		repeat
			fetch cur into l_eno;
			call query_1(l_eno, the_eamount);
			insert into tmp_table select l_eno, the_eamount;
			update let set let.eamount = the_eamount where let.eno = l_eno;
		until done=1
		end repeat;
		close cur; 
		select * from tmp_table;
		drop table tmp_table;
	end //
delimiter ;

call query_2();

6. 触发器 – TRIGGER

触发器的作用就是,在对某个表格进行某项操作(增删改)之前或之后,自动地(即被服务器隐性调用)对操作所涉及的记录执行触发器操作

CREATE TRIGGER 触发器名
{BEFORE|AFTER}          
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW 
BEGIN
    触发器操作
END

-- 例子
delimiter //
create trigger maxInEamount before insert on 
loant for each row
begin
	declare msg varchar(200);
	declare the_eamount int;
	declare the_ecapital int;
	select ecapital into the_ecapital from let where eno = new.eno;
	call query_1(new.eno, the_eamount);
	if the_eamount + new.lamount > 10 * the_ecapital then
		set msg = "拒绝添加数据的请求!";
		SIGNAL sqlstate 'HY000' set MESSAGE_TEXT = msg;
	end if;
	update let set let.eamount = the_eamount + new.lamount where let.eno = new.eno;
end //

7. 事件 – EVENT

事件可以实现,自动地(即被服务器隐性调用)在某个时间点或者某隔一段时间执行一次某些命令

CREATE EVENT 事件名
ON SCHEDULE
{AT 某个确定的时间点 | EVERY 期望的时间间隔 [STARTS datetime][END datetime]}
DO
BEGIN
    具体的语句
END


-- 例子

-- 指定某个时间点执行
CREATE EVENT insert_t1
ON SCHEDULE
AT '2018-03-10 15:48:54'
-- 也可以写成:AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
    INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

-- 指定某个时间间隔执行
CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2018-03-10 15:48:54' ENDS '2018-03-12 15:48:54'
-- 也可以写成:EVERY 1 HOUR 
DO
BEGIN
    INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

七、事务

1. 什么是事务

事物是独立的工作单元,在这个独立工作单元中所有操作要么全部成功,要么全部失败

也就是说如果有任何一条语句因为崩溃或者其它原因导致执行失败,那么未执行的语句都不会再执行,已经执行的语句会进行回滚操作,这个过程被称之为事务

2. 事务四大特征 – ACID

① 原子性 (atomicity)

事务是一个不可拆分的工作单位,事务中的操作,要么全部完成,要么全部失败
基于回滚日志(undo log)实现,当事物需要回滚时就会调用回滚日志进行 SQL 语句回滚操作,实现数据还原

② 一致性 (Consistency)

事务的前后数据的完整性必须保持一致
比如A、B余额共计1000,那么不管两人怎么转账自始至终就只有1000
一致性是事物追求的最终目标,原子性、隔离性、持久性都是为了保证数据库一致性而存在

③ 隔离性 (Isolation)

一个事务的执行不能被其他的事务干扰
原子性和持久性是针对单个事务,而隔离性是针对事物与事物之间的关系
基于mvcc实现(幻读问题除外)

④ 持久性 (Durability)

一旦事务提交之后,他对数据库的操作数据改变是永久的,接下来的任何突发情况都不会放生影响
基于事务日志的重做日志(redo log)实现

3. 事务的创建

隐式事务与显式事务

-- 隐式的事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id=1;

-- 显式事务:事物具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;

创建事务

-- 1、关闭自动提交
SET autocommit=0;
-- 2、开启事务
START TRANSACTION;
-- 3、事务语句
ALTER TABLE girls MODIFY gname VARCHAR(20) NOT NULL;
INSERT INTO girls VALUES(0,'张三123');
-- 4、明显的结束标记
COMMIT;  #提交
ROLLBACK;  #回滚

SELECT * FROM girls; -- 查看

4. 数据库的隔离级别

对于多个事务运行时,如果事务访问的是相同的数据,如果没有设置隔离级别,就会出现并发问题

read uncommitted;	-- 读未提交的
read committed;		-- 读已提交的
repeatable read; 	-- 可以重复读 (默认)
serializable; 	 	-- 串行化

-- 查看数据库的隔离级别
mysql 8.0
-- 系统级别的
select @@global.transaction_isolation;
-- 会话级别的
select @@transaction_isolation;
mysql 5.x
-- 系统级别的
select @@global.tx_isolation;
-- 会话级别的
select @@tx_isolation;

-- 修改隔离级别
set global transaction isolation level read uncommitted;
图片[10]-MySQL数据库知识总结-墨初博客

5. 事务的并发问题

图片[11]-MySQL数据库知识总结-墨初博客

① 脏读
读取了另一个事务没有提交的数据

② 不可重复读
事务读取同一个数据,返回结果先后不一致问题
脏读与不可重复读的区别:脏读读取的是另一个事物没有提交的数据,而不可重复读读取的是另一个事务已经提交的数据

③ 幻读
事务按照范围查询,俩次返回结果不同

④ 区别
脏读读取的是另一个事务没有提交的数据,而不可重复读读取的是另一个事物已经提交的数据。
幻读和不可重复读都是读取了另一条已经提交的事务(这点与脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)

6. 锁机制

锁在 MySQL 中是是非常重要的一部分,锁对 MySQL 数据访问并发有着举足轻重的作用
MySQL中有三类锁,分别为行锁、表锁、页锁。首先需要明确的是这三类锁是是归属于那种存储引擎的:

  • 行锁:Innodb 存储引擎
  • 表锁:Myisam、MEMORY 存储引擎
  • 页锁:BDB 存储引擎

① 行锁

行锁又分为共享锁、排它锁,也被称之为读锁、写锁,Innodb 存储引擎的默认锁

共享锁(S):假设一个事物对数据 A 加了共享锁(S),则这个事物只能读 A 的数据
其他事物只能再对数据 A 添加共享锁(S),而不能添加排它锁(X),直到这个事物释放了数据 A 的共享锁(S)
这就保证了其他事物也可以读取 A 的数据,但是在这个事物没有释放在 A 数据上的共享锁(S)之前不能对 A 做任何修改

排它锁(X):假设一个事物对数据 A 添加了排它锁(X),则只允许这个事物读取和修改数据 A
其他任何事物都不能在对数据A添加任何类型的锁,直至这个事物释放了数据 A 上的锁
排它锁阻止其它事物获取相同数据的共享锁(S)、排它锁(X),直至释放排它锁(X)
特点如下:

  • 只针对单一数据进行加锁
  • 开销大
  • 加锁慢
  • 会出现死锁
  • 锁粒度最小,发生锁冲突的概率越低,并发越高

② 表锁

表锁又分为表共享读锁、表独占写锁,也被称之为读锁、写锁,Myisam 存储引擎的默认锁

表共享读锁:针对同一个份数据,可以同时读取互不影响,但不允许写操作
表独占写锁:当写操作没有结束时,会阻塞所有读和写
特点如下:

  • 对整张表加锁
  • 开销小
  • 加锁快
  • 无死锁
  • 锁粒度最大,发生锁冲突的概率越大,并发越小

③ 如何加锁

表锁:

  • 隐式加锁:默认自动加锁释放锁,select 加读锁、update、insert、delete 加写锁
  • 手动加锁:lock table tableName read;(添加读锁)、lock table tableName write(添加写锁)
  • 手动解锁:unlock table tableName(释放单表)、unlock table(释放所有表)

行锁:

  • 隐式加锁:默认自动加锁释放锁,只有 select 不会加锁,update、insert、delete 加排它锁
  • 手动加共享锁:select id name from user lock in share mode
  • 手动加排它锁:select id name form user for update
  • 解锁:正常提交事物(commit)、事物回滚(rollback)、kill 进程
图片[1]-MySQL数据库知识总结-墨初博客

数据库系统概论pdf下载

https://pan.baidu.com/s/1QGeDEBWaP7-DdODoq7q_RQ?pwd=b40a

参考资料

https://www.cnblogs.com/cxx8181602/p/9525950.html

https://blog.csdn.net/qq_41207757/article/details/108132442

https://www.php.cn/mysql-tutorials-445537.html

https://blog.csdn.net/u013617791/article/details/104854495

https://blog.csdn.net/dianxinlaozong/article/details/108650368

https://www.cnblogs.com/syy1757528181/p/14577800.html

© 版权声明
THE END
喜欢就支持一下吧
点赞1
分享
评论 抢沙发
墨初的头像-墨初博客

昵称

取消
昵称表情代码图片