Mysql

最新推荐文章于 2023-01-11 14:25:14 发布

lcwzzz

最新推荐文章于 2023-01-11 14:25:14 发布

阅读量233

点赞数 1

本文链接：https://blog.csdn.net/lan_faster/article/details/103165850

版权

文章目录

1. 常用数据库dos窗口命令
2. 数据库存储引擎
- （1）MYISAM
- （2）INNODB
- （3）MEMORY
3. 常用数据类型
4. sql功能分类
5. DDL
- （1）创建表
- （2）修改表结构
- （3）删除表
6. DML
- （1）插入（增加）
- （2）更新（修改）
- （3）删除记录
7. DQL
- （1）条件查询
- （2）模糊查询
- （3）字段控制查询
- （4）聚合函数
- （5）分组查询
- （6）分页查询
- （7）书写顺序
8. 数据完整性
- （1）实体完整性
- （2）域完整性
- - 非空约束(not null)
  - 默认值(default)
- （3）参照完整性
9. 表之间的关系
- （1）一对多关系
- （2）多对多关系
10. 多表查询
- （1）合并结果集
- （2）连接查询
- （3）子查询
- （4）自连接
11. 常用函数
- （1）字符串函数
- （2）数值函数
- （3）时间日期函数
- （4）流程函数
- （5）其他函数
12. 事务
- 事务的ACID
- 事务的使用
- 事务隔离级别
13. 权限操作
- 添加/删除用户
- 分配权限
- 查看权限
- 删除权限
14. 视图
- 视图基本操作
- 视图的机制
- 视图不可更新部分
15. 存储过程
- delimiter
- 存储过程的创建
- 查看存储过程
- 存储过程变量
- 存储过程参数

1. 常用数据库dos窗口命令

进入数据库
mysql -u root -p
创建数据库
create database 数据库名称;
删除数据库
drop database 数据库名称;
查看数据库
show databases;
使用数据库
use 数据库名称
查看数据库中的表
show tables;
注：先使用数据库，才能查看数据库中的表
修改密码

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘新密码’;
使用mysqladmin修改：mysqladmin -u root -p password 新密码

mysql默认端口号3306

2. 数据库存储引擎

（1）MYISAM

它不支持事务，不支持外键。但是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用可以用这个引擎来创建表。
每个MyISAM在磁盘上存储成三个文件，其中文件名和表名都相同，但是扩展名不同。frm(存储表定义)，
MYD(MYData，存储数据)，MYI(MYIndex，存储索引)。

（2）INNODB

最常用引擎。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。相比较MyISAM，它的写效率差一些，会占用更多的磁盘空间。

（3）MEMORY

Memory是将数据放在内存中（不是磁盘中），默认使用HASH索引。所以访问速度很快，但是一旦服务器关闭，表中的数据就会丢失，表会继续存在。

3. 常用数据类型

double：浮点数，例如double(5,2)表示最大长度为5位，其中必须有两位小数，即最大值为999.99
char：固定长度字符串，例如char(10)
varchar：可变长度字符串，例如varchar(10)表示最大长度10位
int：整数
data：日期类型，格式为：yyyy-MM-dd
time：时间类型，格式为：hh:mm:ss
datatime：日期时间类型，格式为：yyyy-MM-dd hh:mm:ss

注：字符串和日期、时间类型都要用单引号括起来，例如’abc’，'2020-10-01’

4. sql功能分类

DDL：数据定义语言——用来定义数据库对象：创建库、表、列等
DML：数据操作语言——用来操作数据库表中的记录，增、删、改
DQL：数据查询语言——用来查询数据
DCL：数据控制语言——用来定义访问权限和安全级别

5. DDL

（1）创建表

CREATE TABLE 表名(
	列名1 列的类型 [约束],
	列名2 列的类型 [约束],
	...
	列名n 列的类型 [约束]
);
#举例
CREATE TABLE student(
	id varchar(10),
	stu_name varchar(20),
	stu_age int
);

（2）修改表结构

添加列

ALTER TABLE 表名 ADD 列名 数据类型;
#举例
ALTER TABLE student ADD gender char(2);

删除列

ALTER TABLE 表名 DROP 列名;
#举例
ALTER TABLE student DROP gender;

修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 数据类型;
#举例
ALTER TABLE student MODIFY gender varchar(5);

修改列名

ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;
#举例
ALTER TABLE student CHANGE id new_id varchar(5);

修改表名

RENAME TABLE 原始表名 TO 新表名;
#举例
RENAME TABLE student TO NewStu;

查看表的列(字段)信息

DESC 表名;

（3）删除表

DROP TABLE 表名;

6. DML

（1）插入（增加）

INSERT INTO 表名 (列名1, 列名2, ...) VALUE (列值1, 列值2, ...);
#举例
INSERT INTO student (id, stu_name, stu_age) VALUE ('01', 'jack', 18);

#批量插入
INSERT INTO 表名 (列名1, 列名2, ...) VALUES (列值1, 列值2, ...), (列值1, 列值2, ...),...;
#举例
INSERT INTO student (id, stu_name, stu_age) VALUES ('01', 'jack', 18), ('02', 'mik', 19);

注：列名与列值的类型，顺序要一样，如果没有指定列名则默认是定义时的全部列。

（2）更新（修改）

UPDATE 表名 SET 列名1=列值1, 列名2=列值2,... where 列名=值;
#举例
#1. 将所有学生成绩改为90
UPDATE student SET score=90;
#2. 将mik的成绩改为70
UPDATE student SET score=70 where name='mik';
#3. 将jack的成绩改为70,年龄改为20
UPDATE student SET score=70, stu_age=20 where name='jack';
#4. 将mik的成绩加5
UPDATE student SET score=score+5 where name='mik';

（3）删除记录

DELETE FROM 表名 WHERE 列名=值;
#举例
#删除id为1的同学的记录
DELETE FROM student WHERE id=1;

删除整张表的记录

#方法1
DELETE FROM student;
#方法2
TRUNCATE TABLE student;

两种方法的区别：
方法1是删除表的数据，表结构还在。
方法2是将表直接DROP掉，再创建一个和之前一样的新表，执行速度比方法1快。
当表的主键设置了自动递增时，方法1删除后序号会接着删除前的序号，方法2序号会从1开始。

7. DQL

查询指定的列

SELECT 列名1,列名2,... FROM 表名;
#举例
#查询id这一列
SELECT id FROM student;

（1）条件查询

#1. 查询性别为男且年龄为20的学生记录
SELECT * FROM student WHERE gender='男' AND stu_age=20;
#2. 查询id为1或者姓名为mike的学生记录
SELECT * FROM student WHERE id='1' OR stu_name='mike';
#3. 查询id为1,2,3的学生记录
SELECT * FROM student WHERE id IN(1, 2, 3);
#4. 查询name为NULL的学生记录
SELECT * FROM student WHERE stu_name IS NULL;
#5. 查询name不为NULL的学生记录
SELECT * FROM student WHERE stu_name IS NOT NULL;
#6. 查询性别不为男的学生记录
SELECT * FROM student WHERE gender!='男';
#7. 查询年龄在18-20之间的学生记录
SELECT * FROM student WHERE stu_age>=18 AND stu_age<=20;
SELECT * FROM student WHERE stu_age BETWEEN 18 AND 20;

（2）模糊查询

通配符：_表示任意一个字符，%表示任意多个字符。(汉字算一个字符)

#1. 查询姓名由五个字符构成的学生记录
SELECT * FROM student WHERE stu_name LIKE '_____'; #引号里面是5个下划线
#2. 查询姓名由五个字符构成且结尾为s的学生记录
SELECT * FROM student WHERE stu_name LIKE '____s';
#3. 查询姓名由s开头的学生记录
SELECT * FROM student WHERE stu_name LIKE 's%';
#4. 查询姓名第二个字符为s的学生记录
SELECT * FROM student WHERE stu_name LIKE '_s%';
#5. 查询姓名包含s的学生记录
SELECT * FROM student WHERE stu_name LIKE '%s%';

（3）字段控制查询

去重查询

# 查询成绩为90的学生的年龄
SELECT DISTINCT stu_age FROM student WHERE score=90;

查询的结果进行运算，结果必须是数据型

# 查询所有学生的年龄和成绩之和，如果成绩为NULL运算时当0处理
SELECT stu_age+IFNULL(score,0) FROM student;

对查询结果起别名

# 查询所有学生的年龄和成绩之和，如果成绩为NULL运算时当0处理，结果列名为total
SELECT stu_age+IFNULL(score,0) AS total FROM student;

对查询结果进行排序

#1. 查询所有学生的记录，按照年龄升序进行排序
SELECT * FROM student ORDER BY stu_age ASC; #ASC可以省略，即默认是升序
#2. 查询所有学生的记录，按照年龄降序进行排序
SELECT * FROM student ORDER BY stu_age DESC;
#3. 查询所有学生的记录，按照年龄降序进行排序，如果年龄一样按照id降序进行排序
SELECT * FROM student ORDER BY stu_age DESC, id DESC;

（4）聚合函数

聚合函数就是对查询结果进行统计计算
常用聚合函数：

1.COUNT()：统计指定列不为NULL的记录个数

#1. 查询student表中有多少条记录
SELECT COUNT(*) FROM student;
#2. 查询成绩大于90的学生人数
SELECT COUNT(*) FROM student WHERE score>90;
#3. 查询有成绩的学生人数
SELECT COUNT(score) FROM student;
#4. 查询有成绩的学生人数和有id的学生的人数
SELECT COUNT(score), COUNT(id) FROM student;

2.SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

#1. 查询所有学生的成绩之和
SELECT SUM(score) FROM student;
#2. 查询所有学生的成绩的平均值
SELECT AVG(score) FROM student;

3.MAX()：计算指定列的最大值，如果指定列类型为字符串类型，那么使用字符串排序运算
MIN()：计算指定列的最小值，如果指定列类型为字符串类型，那么使用字符串排序运算

#1. 查询最高分和最低分
SELECT MAX(score), MIN(score) FROM student;
#2. 查询成绩最高的学生的id
SELECT id FROM student WHERE 
score = (SELECT MAX(score) FROM student);

（5）分组查询

将查询结果按照一个或者多个字段进行分组，字段值相同的为一组

SELECT 列名 FROM 表名 GROUP BY 列名;

注：一般SELECT后面的字段都会出现在GROUP BY后。
GROUP BY单独使用只会显示出每组第一条记录，所以一般它不单独使用

1.GROUP BY + GROUP_CONCAT()

# 查询学生的id，以年龄分组
SELECT stu_age, GROUP_CONCAT(id) FROM student GROUP BY stu_age;

2.GROUP BY + 聚合函数
对于分组之后的结果进行统计

#1. 查询每个部门的工资总和
SELECT department, GROUP_CONCAT(salary), SUM(salary) FROM employee GROUP BY department;
#2. 查询每个部门的人数
SELECT department, GROUP_CONCAT(name), COUNT(*) FROM employee GROUP BY department;

3.GROUP BY + HAVING
having和where都是条件限制，但是having只能用于group by。where是对分组前的数据进行过滤，having是对分组后的数据进行过滤。

# 查询工资大于2000的，工资总和大于9000的部门名称及工资总和
# 第一步： 查询工资大于2000的部门名称
SELECT department FROM employee where salary>2000 GROUP BY department;
# 第二步： 查询工资大于2000的部门名称和部门薪资总和
SELECT department, SUM(salary) FROM employee where salary>2000 GROUP BY department;
# 第三步： 查询工资大于2000的，工资总和大于9000的部门名称及工资总和
SELECT department, SUM(salary) FROM employee
where salary>2000
GROUP BY department
HAVING SUM(salary)>9000;

（6）分页查询

格式：LIMIT 参数1, 参数2

参数1：从哪一行开始查
参数2：总共查几行

注：第一个参数角标是从0开始

# 分页思路
int curPage = 1; #当前页
int pageSize = 3; #每页多少条数据

SELECT * FROM employee LIMIT (curPage-1)*pageSize, pageSize;

（7）书写顺序

select ——> from ——> where ——> group by ——> having ——> order by ——> limit

8. 数据完整性

数据完整性就是为了保证输入数据的正确性。
通过在表中添加约束来保证数据完整性。
数据完整性分为 实体完整性，域完整性，参照完整性。

（1）实体完整性

实体完整性对表中的一行数据进行约束，约束类型分为 主键约束、唯一约束和自动增长列。

主键约束

一个表中只能由一个主键，被设置为主键的字段数据唯一且不能为空。
添加主键

# 方法1 在字段定义后面加 primary key
CREATE TABLE student(
	id varchar(10) primary key,
	stu_name varchar(20),
	stu_age int
);
# 方法2 在创建的表的末尾加 primary key(主键)
CREATE TABLE student(
	id varchar(10),
	stu_name varchar(20),
	stu_age int,
	primary key(id)
);
# 联合主键
CREATE TABLE student(
	id varchar(10),
	stu_name varchar(20),
	stu_age int,
	primary key(id, stu_name)
);

注：联合主键表示两个字段共同组合成一个主键。简单来看就是如上述id和stu_name为联合主键，这样如果两条记录的id和stu_name都一样，这种输入不被接受，但是如果两者中有一者不一样则输入为合格。

给已经创建好的表添加主键

alter table student add constraint primary key(id);

唯一约束

特点：指定列数据可以为空，但必须唯一

CREATE TABLE student(
	id varchar(10) primary key,
	stu_name varchar(20) unique,
	stu_age int
);

自动增长列

设置某列为自动增长列，则此列的数据每次都会自动加1。
注：如果删除数据后，还是从删除的序号继续往下。

CREATE TABLE student(
	id int primary key auto_increment,
	stu_name varchar(20) unique,
	stu_age int
);

（2）域完整性

域完整性指列的值域的完整性。如数据类型、值域范围、非空、默认值等。

非空约束(not null)

CREATE TABLE student(
	id int primary key,
	stu_name varchar(20) unique not null,
	stu_age int
);

默认值(default)

CREATE TABLE student(
	id int primary key,
	stu_name varchar(20) unique not null,
	stu_age int default 20
);

（3）参照完整性

参照完整性是指表与表之间的一种对应关系。
通常情况下可以通过设置两表之间的主键、外键关系，或者编写两表的触发器来实现。
有对应参照完整性的两张表格，在对他们进行数据插入、更新和删除的过程中，系统会将被修改表格与另一张对应表格进行对照，从而阻止一些不正确的数据操作。

要求：

存储引擎为INNODB类型。
主键和外键的类型必须一致。
外键当中的值，必须是主键中的内容

# 创建主表，id为主键
create table stu(
	id int primary key,
	name varchar(20) unique
);
# 创建子表，sid为外键
create table sc(
	sid int,
	score int,
	constraint sc2stu foreign key(sid) references stu(id) # sc2stu 为约束的名字
);

给已经创建好的表添加外键

alter table sc add constraint sc2stu foreign key(sid) references stu(id);

9. 表之间的关系

一对一关系，一对多关系，多对多关系。
主要介绍一对多关系和多对多关系。

（1）一对多关系

举例：一个人可以拥有多个车，但一个车只能属于一个人
使用外键可建立一对多的关系

create table person(
	id int primary key,
	name varchar(20),
	age int
);
create table car(
	cid int,
	cname varchar(20),
	color varchar(10),
	constraint foreign key(cid) references person(id) #约束名字省略后系统会自动生成
);

（2）多对多关系

举例：一个学生可以选多个课程，一个课程可以被多个学生选择
多对多关系的建立需要一个中间表来完成

create table stu(
	sid int primary key,
	sname varchar(20)
);
create table course(
	cid int primary key,
	cname varchar(20)
);
create table sc(
	sid int,
	cid int,
	constraint foreign key(sid) references stu(sid),
	constraint foreign key(cid) references course(cid)
);

拆分成多表的目的是避免冗余数据的出现。

10. 多表查询

（1）合并结果集

# 创建a, b表
create table a(name varchar(10), score int);
create table b(name varchar(10), score int);
insert into a values('a', 10), ('b', 20), ('c', 30);
insert into b values('a', 10), ('b', 20), ('d', 30);

# union：去除重复项
select * from a
union
select * from b

# union all：保留重复项
select * from a
union all
select * from b

（2）连接查询

笛卡尔积：
如果集合A={a, b}，集合B={1, 2, 3}, 则两个集合的笛卡尔积为{(a, 1), (a, 2), (a, 3), (b, 1), (b, 2), (b, 3)}
如果直接查询两个表（例如：select * from a,b;）则查询结果为这两个表的笛卡尔积。

对表起别名

select * from stu st; #给stu表起别名为st

等值连接和非等值连接

等值连接，连接符为"="；连接符不为"="就是非等值连接。

# 查询学生成绩
# stu表中有sid, name
# score表中有sid, score
select * from stu, score where stu.sid=score.sid;
# 查询mike的成绩
select * from stu, score where stu.sid=score.sid and stu.name='mike';

多表连接查询

# 查询每个学生考试科目的成绩
# student学生表有id, name, gender
# course课程表有id, name
# score分数表有sid, cid, score
select st.name, c.name, sc.score
from student st, course c, score sc
where st.id=sc.sid and c.id=sc.cid;

外连接

外连接有左外连接和右外连接

# 左外连接就是将左边表全部列出来，右边表满足条件的列出来
select * from stu left outer join score on(stu.sid=score.sid);

# 右外连接就是将右边表全部列出来，左边表满足条件的列出来
select * from stu right outer join score on(stu.sid=score.sid);

自然连接

会自动把两张表中所有列名称和类型完全一致的列作为条件

# 查询学生成绩
# stu表中有sid, sname, gender
# score表中有sid, score
select * from stu natural join score
# 等价于
select * from stu, score where stu.sid=score.sid;

# 如果score表中有sid, score, sname, 那么
select * from stu natural join score
# 等价于
select * from stu, score where stu.sid=score.sid and stu.sname=score.sname;

（3）子查询

子查询就是select语句中包含另一个完整的select语句，也就是嵌套查询。

子查询出现在where后，表示把查询结果作为另一个select的条件；
子查询出现在from后，表示把查询结果当作一个新表进行查询。

# employ表有eno(编号), ename, job, salary, mg(上级领导编号), deno(部门编号)
# department表有deno, dname, local(所在地)

# 1. 查询与mike同部门的员工名称
# 第一步：查询mike的部门编号
select deno from employ where ename='mike';
# 第二步：查询mike所在部门的员工
select ename, deno from employ
where deno = (select deno from employ where ename='mike');

# 2. 查询工资高于30号部门所有人的员工信息
# 第一步：查询30号部门的最高工资
select max(salary) from employ where deno=30;
# 第二步：查询工资高于30号部门的最高工资的员工信息
select ename, salary from employ
where salary > (select max(salary) from employ where deno=30);

# 3. 查询工作和薪资跟mike完全相同的员工信息
# 方法1
select * from employ
where (job, salary)
in (select job, salary from employ where ename='mike');
# 方法2
select * from employ e, (select job, salary from employ where ename='mike') s
where e.job=s.job and e.salary=s.salary;

# 4. 查询有2个及以上下属的员工信息
# 第一步：查询出现2次及以上的mg
select mg from employ group by mg having COUNT(mg)>=2;
# 第二步：查询员工编号属于第一步查询结果的员工信息
select * from employ where
eno in (select mg from employ group by mg having COUNT(mg)>=2);

# 5. 查询编号为101的员工名字、部门名称、部门地址
select e.ename, d.dname, d.local from employ e, department d
where e.deno=d.deno and e.eno=101;

（4）自连接

一个表自己和自己连接。

# 查询编号为101的员工编号和姓名以及他的上级领导编号和姓名
select e1.eno, e1.ename, e2.eno, e2.ename
from employ e1, employ e2 
where e1.mg=e2.eno and e1.eno=101;

11. 常用函数

（1）字符串函数

concat

concat(s1, s2,…,sn)
将传入字符连接成一个字符串
任何字符串与null连接都为null

insert

insert(str, x, y, instr)
将str从x(序号从1开始)位置开始，y个字符长的子串替换为instr

lower、upper

lower(str) 将str变为全小写
upper(str) 将str变为全大写

left、right

left(str, x) 返回最左边x个字符
right(str, x) 返回最右边x个字符

lpad、rpad

lpad(str, n, pad) 用字符串pad对str最左边进行填充，直到总长度为n
rpad(str, n, pad) 用字符串pad对str最右边进行填充，直到总长度为n

ltrim、rtrim、trim

ltrim(str) 去掉str最左侧空格
ltrim(str) 去掉str最右侧空格
ltrim(str) 去掉str两侧的空格

repeat

repeat(str, n) 把str重复n次

replace

replace(str, a, b) 将str中所有a字符变为b字符

substr

substr(str, x, y) 返回str从x位置起y个字符

（2）数值函数

abs(x) 返回x的绝对值
ceil(x) x向上取整
floor(x) x向下取整
mod(x, y) 返回x/y的模(余数)
rand() 返回0-1之间的随机数

（3）时间日期函数

curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间

（4）流程函数

if(value, t, f)
如果value为真返回t，否则返回f

ifnull(value1, value2)
如果value1不为空，返回value1，否则返回value2

（5）其他函数

database() 返回当前数据库名
version() 返回当前数据库版本
user() 返回当前登陆用户名

12. 事务

事务就是不可分割的操作。假设该操作由ABCD四个步骤组成，当这四个步骤都成功完成时，则认为事务成功；若其中任意一个步骤失败，则认为事务失败。
每条sql语句就是一个事务，事务只对DML语句有效，对DQL语句无效。

事务的ACID

原子性（Atomicity）

事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性（Consistency）

事务执行前后必须处于一致性状态。
如商品出库时，仓库数量减1，对应用户的商品加1。

隔离性（Isolation）

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。
在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

持久性（Durability）

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务的使用

开始事务

start transaction;

系统默认是一条语句一个事务，要多条语句为一个事务时，需要手动开启事务。

提交事务

commit;

所有语句全部执行完毕，没有发生异常，提交事务，更新数据库。

回滚事务

rollback;

撤销当前事务的所有操作。

事务隔离级别

三种现象

脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。
非重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了，也可能被删除了。
幻读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作的事务提交，导致每次返回不同的结果集。

隔离级别与现象之间的关系：

	脏读	非重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

mysql默认隔离级别为repeatable read。

查看隔离级别

select @@global.transaction_isolation, @@transaction_isolation;

设置隔离级别

# 设置当前会话的隔离级别
set session transacton isolation level read uncommitted;
# 设置系统的隔离级别
set global transacton isolation level read uncommitted;

13. 权限操作

添加/删除用户

# 添加用户
create user '用户名'@'ip地址' identified by '密码';
# 举例
create user 'user_test'@'localhost' identified by '123456';

# 删除用户
drop user '用户名'@'ip地址;
# 举例
drop user 'user_test'@'localhost';

分配权限

grant 权限 on 数据库对象 to 用户 identified by "密码" with grant option;

# 1. 创建一个超级管理员mylk，密码为123，拥有所有权限且可以继续给其他用户授予权限
# *.*代表所有数据库的所有表，with grant option代表可以给其他用户授予权限;
grant all privileges on *.* to mylk@localhost
identified by "123"
with grant option;
flush privileges; #刷新

# 2. 创建一个test用户，该用户只能对test数据库中的stu表进行增删改查的操作
grant insert,delete,update,select on test.stu to test@localhost identified by "123";
flush privileges; #刷新

查看权限

show grants;
# 查看指定用户权限
show grants for 用户;
show grants for root@localhost;

删除权限

revoke 权限 on 数据库对象 from 用户;

14. 视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图基本操作

# 1. 创建视图
create view 视图名称
as (查询结果);
# 举例：
create view stu_view
as (select * from stu where gender='男');

# 2. 覆盖已创建的视图
create or replace view 视图名称
as (查询结果);
# 举例：
create or replace view stu_view
as (select * from stu where gender='男');

# 3. 删除视图
drop view 视图名称;
# 举例
drop view stu_view;

视图的机制

创建视图的完整形式

create algorithm
view 视图名称
as (select语句)
[with check option];
# 举例
create algorithm=merge 
view stu_view
as (select * from stu where gender='男')
with check option;

algorithm参数：

merge：替换式，此时查询视图的语句会被变成直接对基表的操作也就是，select * from stu_view会被转化为select * from (select * from stu where gender=‘男’) t，这时可以通过视图更新真实表中的数据。mysql的默认方式为替换式。
temptable：具化式，mysql把视图作为临时表存入内存中，所以不可以通过视图更新真实表中的数据。

with check option选项：
更新视图时不能更新不符合视图限制条件的记录，例如上述stu_view视图不可以将学生的gender更改为‘女’。

视图不可更新部分

视图中的数据不来源于基表，即使algorithm=merge也不能修改。
例如：
create algorithm=merge
view stu_view
as (select sum(score) from student);
视图中的数据不是直接来源于student表，所以不可更新。

15. 存储过程

存储过程就是可编程的函数，是为了完成特定功能的sql语句集。
例如，想查询stu表中’mike’的信息，就写一个函数，给函数一个参数’mike’，返回’mike’的信息。
存储过程创建后会保存在数据库的数据字典中，可重复使用。

delimiter

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。如输入语句select * from stu;，然后回车，那么MySQL将立即执行该语句。
当碰到输入多条语句且语句中包含分号的时候，我们就不希望这样，所以用delimiter来指定遇到哪个符号才执行语句。如delimiter $，则遇到 $符号mysql才会执行这段语句。

存储过程的创建

delimiter $$
# 创建存储过程
create procedure show_stu()
begin
select * from stu;
end$$
delimiter ;

# 调用
call show_stu();

# 删除
drop procedure show_stu;

查看存储过程

# 查看所有存储过程
show procedure status;
# 查看指定数据库的存储过程
show procedure status where db='数据库名';
# 查看存储过程源码
show create procedure show_stu;

存储过程变量

delimiter $$
create procedure show_stu()
begin
# 声明变量
declare tmp int default 0;

# 赋值变量
# 方法1
set tmp = 1;
# 方法2
select sum(age) into tmp from stu;

end$$
delimiter ;

存储过程参数

delimiter $$
# 传入名字返回学生信息
create procedure show_stu(in name varchar(20))
begin
select * from stu where stu_name = name;
end$$
delimiter ;

# 传入名字返回学生年龄
delimiter $$
create procedure show_stu(in name varchar(20), out age int)
begin
select age into age from stu where stu_name = name;
end$$
delimiter ;
# 调用
call show_stu('mike', @age);
# 显示
select @age;

# 实现 a = a + b
delimiter $$
create procedure my_add(inout a int, in b int)
begin
set a = a + b;
end$$
delimiter ;
# 调用
set @a = 1;
call my_add(@a, 1);
# 显示
select @a;