内容输出来源:拉钩教育Java就业训练营
1 MySql索引
在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。
1.1 常见索引分类
索引名称 | 说明 |
---|---|
主键索引 (primary key) | 主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一条记录 |
唯一索引 (unique) | 唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一. |
普通索引 (index) | 最常见的索引,作用就是 加快对数据的访问速度 |
1.1.1 主键索引
特点: 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录。
语法格式:
-- 创建表的时候直接添加主键索引 (最常用)
CREATE TABLE 表名(
-- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
字段名 类型 PRIMARY KEY,
);
-- 修改表结构 添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
1.1.2 唯一索引
特点: 索引列的所有值都只能出现一次, 必须唯一.
唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。
语法格式:
-- 创建表的时候直接添加主键索引
CREATE TABLE 表名(
列名 类型(长度),
-- 添加唯一索引
UNIQUE [索引名称] (列名)
);
-- 使用create语句创建: 在已有的表上创建索引
create unique index 索引名 on 表名(列名(长度))
-- 修改表结构添加索引
ALTER TABLE 表名 ADD UNIQUE ( 列名 )
1.1.3 普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column=)或排序条件(ORDERBY column)中的数据列创建索引。
语法格式:
-- 使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名[长度])
-- 修改表结构添加索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名)
1.1.4 删除索引
由于索引会占用一定的磁盘空间,因此,为了避免影响数据库的性能,应该及时删除不再使用的索引
语法格式:
ALTER TABLE table_name DROP INDEX index_name;
1.2 索引的优缺点
添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
索引的优点
- 大大的提高查询速度
- 可以显著的减少查询中分组和排序的时间。
索引的缺点
- 创建索引和维护索引需要时间,而且数据量越大时间越长
- 当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度
2 MySql视图
- 视图是由查询结果形成的一种虚拟表。
- 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
- 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
- 视图向用户提供基表数据的另一种表现形式
2.1 视图的作用
权限控制时可以使用
比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
简化复杂的多表查询
视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图
就可以获取想要得到的信息(不需要再编写复杂的SQL)
视图主要就是为了简化多表的查询
2.2 视图的使用
2.2.1 创建视图
语法格式:
create view 视图名 [column_list] as select语句;
view: 表示视图
column_list: 可选参数,表示属性清单,指定视图中各个属性的名称,默认情况下,与SELECT语句中查询
的属性相同
as : 表示视图要执行的操作
select语句: 向视图提供数据内容
实例:
#1. 先编写查询语句
#查询所有商品 和 商品的对应分类信息
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
#2.基于上面的查询语句,创建一张视图
CREATE VIEW products_category_view
AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
#3.查询视图 ,当做一张只读的表操作就可以
SELECT * FROM products_category_view;
2.2.2 通过视图进行查询
#通过 多表查询
SELECT
cname AS '分类名称',
AVG(p.`price`) AS '平均价格'
FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`
GROUP BY c.`cname`;
# 通过视图查询 可以省略连表的操作
SELECT
cname AS '分类名称',
AVG(price) AS '平均价格'
FROM products_category_view GROUP BY cname;
2.3 视图与表的区别
视图是建立在表的基础上,表存储数据库中的数据,而视图只是做一个数据的展示
通过视图不能改变表中数据(一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新)
删除视图,表不受影响,而删除表,视图不再起作用
3 MySql存储过程(了解)
简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
3.1 存储过程的优缺点
优点:
1.存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)
2.存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与数据库服务器不在同一个地区)
缺点:
1.在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使用,并且互联网行业需求变化较快也是原因之一
2.尽量在简单的逻辑中使用,存储过程移植十分困难,数据库集群环境,保证各个库之间存储过程变更一致也十分困难。
3.阿里的代码规范里也提出了禁止使用存储过程,存储过程维护起来的确麻烦;
3.2 存储过程创建方式
3.2.1 方式一
语法格式:
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$
CREATE PROCEDURE 过程名称() -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
-- 调用存储过程
call 存储过程名
3.2.2 方式二
语法格式:
-- IN 输入参数:表示调用者向存储过程传入值
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
-- 创建接收参数的存储过程
DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$
-- 调用存储过程 传递参数
CALL goods_proc02(2);
3.2.3 方式三
-- 1) 变量赋值
SET @变量名=值
-- 2) OUT 输出参数:表示存储过程向调用者传出值
OUT 变量名 数据类型
-- 3) 创建存储过程
# 创建存储过程 接收参数插入数据, 并返回受影响的行数
DELIMITER $$
CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT
out_num INT)
BEGIN
-- 执行插入操作
INSERT INTO orders VALUES(o_oid,o_gid,o_price);
-- 设置 num的值为 1
SET @out_num = 1;
-- 返回 out_num的值
SELECT @out_num;
END $$
-- 4) 调用存储过程
# 调用存储过程插入数据,获取返回值
CALL orders_proc(1,2,30,@out_num);
4 MySql触发器(了解)
当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。
4.1 触发器创建的四个要素
- 监视地点(table)
- 监视事件(insert/update/delete)
- 触发时间(before/after)
- 触发事件(insert/update/delete)
4.2 创建触发器
语法格式:
delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
CREATE TRIGGER Trigger_Name -- 触发器名,在一个数据库中触发器名是唯一的
before/after(insert/update/delete) -- 触发的时机 和 监视的事件
on table_Name -- 触发器所在的表
for each row -- 固定写法 叫做行触发器, 每一行受影响,触发事件都执行
begin
-- begin和end之间写触发事件
end
$ -- 结束标记
5 DCL(数据控制语言)
MySql默认使用的都是 root 用户,超级管理员,拥有全部的权限。除了root用户以外,我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。
5.1 创建用户
语法格式:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
参数 | 说明 |
---|---|
用户名 | 创建的新用户,登录名称 |
主机名 | 指定该用户在哪个主机上可以登陆,本地用户可用 localhost 如果想让该用户可以 从任意远程主机登陆,可以使用通配符 % |
密码 | 登录密码 |
5.2 用户权限
创建好的用户,需要进行授权
语法格式:
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
-- 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询
GRANT SELECT ON db4.products TO 'admin1'@'localhost';
-- 给 admin2 用户分配所有权限,对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
参数 | 说明 |
---|---|
权限 | 授予用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。 如果要授 予所有的权限则使用 ALL |
ON | 用来指定权限针对哪些库和表。 |
TO | 表示将权限赋予某个用户。 |
5.3 查看权限
语法格式:
SHOW GRANTS FOR '用户名'@'主机名';
-- 查看root用户权限
SHOW GRANTS FOR 'root'@'localhost';
5.4 删除用户
语法格式:
DROP USER '用户名'@'主机名';
-- 删除 admin1 用户
DROP USER 'admin1'@'localhost';
5.5 查询用户
-- 查询用户
SELECT * FROM USER;