Reminance's Studio.

Mysql数据库的高级应用(存储过程，触发器、索引，视图，函数）

mysql
字数统计: 4k阅读时长: 16 min
2019/10/24 Share

@TOC

Mysql数据库的高级应用(存储过程，触发器、索引，视图，函数）

SQL分类:

DDL：数据定义语言（对库、表，和表字段的增删改）
DML：数据操作语言（给表中数据的增删改）
DCL：数据控制语言（权限和安全）
DQL：数据查询语言（所有的查询select）

例：假设Student表中字段有(username,age,sex)

  • 查询整张表 select from Student; – (不建议用，效率低，可以将要查询的字段名写出来)
  • 查询整张表 select (username,age,sex) from Student;
  • 查询整张表，可以写表名.字段名 select(Student.username,Student.age,Student.sex) from Student;

查询条件where

  • between…and…; 再某某之间，前后都包含
  • in(值1，值2); 比如set(10,20,30),就是查询某字段值为10或者20或者30的
  • is null; 判断是否为null,null值参与的数学运算结果都是null.
  • is not null;判断不为null,
  • and or not 逻辑运算符

查询时候可以起别名：
示例：select username as 姓名,age as 年龄 from Student; （as可以省略）

字段可以运算
示例：select username as 姓名,age as 年龄 age+1 as 虚岁 from Student;

去掉字段中重复值
示例：select distinct 字段名 from Student;

根据年龄从大到小展示
asc(默认的)，从小到大排列。
desc 从大到小排列
示例：select *from Student order by age desc;

模糊查询like
% 通配任意多个字符
_ 通陪单个任意字符
示例：找出姓名以S开头的学生 select * from Student where username like ‘S%’;
找出姓名中包涵M的学生 select * from Student where username like ‘%M%’;
找出第二个字符为N的学生 select * from Student where username like ‘_N%’;

聚合函数

  • count(字段名); 统计指定列不为null的记录行数 示例select count(*) from Student;
  • max(); select name,max(age) from Student;
  • min();
  • sum();
  • avg(); select avg(age) as 平均年龄 from Student;

分组函数

emp表中（name,deptno,sal）
对某字段分组
select 字段名 from 表名 group by 字段名;
示例：以部门编号deptno，分组，求各部门的总工资

1
select deptno,sum(sal) from emp group by deptno;

having用法：对分组后的结果再次筛选
选出各个部门总工资大于9000的部门: 

1
select deptno sum(sal) from emp group by deptno having sum(sal)>9000;

where和having区别
where:是对分组之前的条件筛选。
having:是对分组之后的结果集的再次筛选。

分页查询

limit 起始索引，每页展示的条数
每页展示四条：select * from emp limit 0,4;
起始索引=(页码-1)*每页条数；

MySQL中常见的约束

  • 主键约束 primary key 非空且唯一，一种表只能由一个主键
  • 非空约束 not null
  • 唯一约束 unique 不能重复，但对null不起作用
  • 自增长约束 auto_increment 配合主键使用
  • 外键约束 foreign key
  • 无符号unsigned 不允许添加负数，但会将原来范围为负的加到原来范围为正的上，-128~127变为0-255

添加约束方式1：

1
2
3
4
create table test(
username varchar(32) primary key,
age int
);

添加约束方式2：

1
2
3
4
5
create table test2(
username varchar(32),
age int,
primary key(username)
);

添加约束方式3：

1
2
3
4
-- 在修改表时候添加约束
alter table 表名 add primary key(字段名);
-- 删除主键约束 
alter table 表名 drop primary key;

联合主键，将几个字段看作整体，非空且唯一

1
2
alter table 表名 add primary key(字段名1,字段名2);

给主键设置自增长

1
2
3
4
5
6
-- 方法和以上类似
create table test3(
id int auto_increment primary key,
username varchar(32),
age int
);

设置自增长之后，插入数据自动增长序号
例如：insert into test3 values(null,‘张三’,23);
注意：若你插入进去了一行，假设此时自增长序号为3，然后删除了此行，再先插入了新的一行，新行的自增长序号为4，不是3。

性别约束

如果你有性别约束的需求，mysql可以如下写（利用枚举）:

1
2
3
4
5
create table test4(
username varchar(32),
age int,
sex enum('男','女','妖')
);

外键约束

1
2
-- orders订单表，users用户表
alter table orders add foreign key (uid) references users (id);

建表语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
create table user(
id int auto_increment primary key,
username varchar(32),
age int,
sex varchar(1));

create table orders(
idd int auto_increment primary key,
name varchar(32),
price double,
uid int,
foreign key(uid) references user(id));

用来保证数据的有效性和完整性，会在从表一方添加外键约束，去关联主表一方的主键

  • 一旦添加了外键约束，那么两张表之间就会相互制约；
  • 主表不能删除从表还在引用的数据；
  • 从表不能添加主表未存在的数据；

举例：用户张三买了上衣和玫瑰，订单表中有两项，外键1指向用户表主键，表明是张三下的订单。
这样有了外键约束，主表不能删除从表还在引用的数据（张三和李四），但是可以删除王五（从表中没有引用王五），如果要删除张三，那就先删除张三的订单，再删除张三。
从表不能添加主表中未存在的数据（不能在从表中添加一个赵六）。

级联删除：

只要删除主表中的张三，从表中的张三的订单一并删除。

1
2
-- 给订单表增加外键uid 关联用户表主键id,级联删除。
alter table 订单表名  add foreign key (uid) references 用户表名(id) on delete cascade;

来说说如何删除主键？

1
2
3
alter table test drop primary key;
-- 这样只是删除了唯一性，非空还是没删除所以还得在来让他可以为空。
alter table test change username username varchar(32) null;

删除主键及自增长？

1
2
3
alter table test change id id int; -- 必须先删除自增长才能再删除主键;
alter table test drop primary key;-- 删除限制重复元素;
alter table test change id id int null; -- 删除非空

多表查询：

查询都属于DQL数据查询语言
表一user(id,username,age,sex)
表二orders(idd,name,price,uid)

笛卡尔积查询无意义

1
select user.id,user.username,orders.* from user,orders;

隐式内连接(where 后面有多个表名)

1
select user.id,user.username ,orders.* from user,orders where user.id=orders.uid;

显式内链接(和隐式内连接查询结果一样，就是语法不同，好像这个相对快速)

1
select user.*,orders.* from user inner join orders on user.id=orders.uid;

左外连接

1
2
//left关键字左边的表他里面的数据全部展示，left右边的表无对应的数据以null展示
select user.*,orders.* from user left outer join orders on user.id=orders.uid

右外连接

1
2
//right关键字右边的表他里面的数据全部展示，左边的表无对应的数据以null展示
select user.*,orders.* from user right outer join orders on user.id=orders.uid;

子查询

一个主查询的条件要依赖与另外一个子查询的结果

1
2
3
4
-- 若没有外键约束想要查张三的订单，先在user表中根据张三姓名找出张三对应的id,用id当作条件去查询orders表中的订单，现在可以整合在一起了。
select id from user where username="张三";
select * from orders where uid=1;
select * from orders where uid=(select id from user where username="张三");

自查询

假设有如下表：employee(员工编号，职位名称，上司编号，薪水)；
现在要查询每个人所对应的上司的名字。

1
select a.username as "员工职位" ,b.username as "上司职位" from employee a,employee b where a.superior=b.empno;

复制表

1
2
3
4
5
6
7
-- 将查询的结果作为新表的内容
create table 新表名 as select * from user;
-- 只复制字段,where后写一个假条件。
create table 新表名 as select * from user where false;
create table 新表名 as select * from user where 1=2;
-- 只复制个别字段
create table 新表名 as select 字段名 from user;

取并集

union

1
select * from user where id=2 union select * from orders where uid=2;

数据库对象(表，视图，存储过程，函数，索引)

存储过程

MySQL数据库在5.0版本后开始支持存储过程，那么什么是存储过程呢？怎么创建、查看和删除存储过程呢？存储过程有什么优点？

什么是存储过程：

类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，
该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。

存储过程优点：

  1. 存储过程增强了SQL语言灵活性。
    存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；

  2. 减少网络流量，降低了网络负载。
    存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行；

  3. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，
    而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

  4. 系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，
    避免了非授权用户对数据的访问，保证了数据的安全。

1
2
3
4
5
6
7
-- 创建名为mypro的存储过程
delimiter $$
create procedure mypro()
    begin
        语句
    end $$
delimiter ;
1
2
3
4
5
6
7
8
-- 调用存储过程
call mypro();
--  删除存储过程
drop procedure mypro;
-- 查看所有存储过程状态
show procedure status \G;
-- 查看存储过程语句、
show create procedure mypro;
1
2
3
4
-- 参数类型
in 输入参数
out 输出参数
inout 输入输出参数

举例：

1
2
3
4
5
6
delimiter $$
create procedure myProcedure1(in id int)
    begin
        select * from user where user.id=id;
    end$$
delimiter ;

调用：

1
2
3
call myProcedure1(2);
-- 注意：在命令行下，写存储过程的时候第一行就定一个结束$$标记，并关闭以分号结束，
所以创建成功，调用的时候需要加上以分号结束（delimiter ;）
1
2
3
4
5
6
delimiter $$
create procedure myProcedure2(out str varchar(20))
		begin
			set str='hello';
		end$$
delimiter ;
1
2
3
4
-- 调用时候接收带回来的值，用一个@代表零时变量。
call myProcedure2(@r);
-- 查看
select @r;

语句 if 

1
2
3
4
5
6
7
8
9
10
delimiter $$
create procedure myProcedure3(in num int,out result char(32))
begin
	if num=1 then 
		set result='星期一';
	elseif num =2 then
		set result ='星期二';
	end if;
end$$
delimiter ;

while(常用)
declare i int default 1;定义局部变量i，默认值为1；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
delimiter $$
create procedure myProcedure4(in num int,out result int)
	begin
		declare i int default  1;
		declare sum  int default 0;
		while(i<=num) do
			set sum=sum+i;
			set i=i+1;
		end while;
	set result=sum;
	end$$
delimiter ;
-- 调用:
-- set @n = 100;
-- call myProcedure4(10, @n);
-- select @n;

loop(一般不用)
repeat(一般不用)

触发器

触发器(trigger)：监视某种情况，并触发某种操作。
触发器创建语法四要素：

  1. 监视地点(table)
  2. 监视事件(insert/update/delete)
  3. 触发时间(after/before)
  4. 触发事件(insert/update/delete)

可以监听增删改三个动作，若有动作，可以向另一张表中记录日志

1
2
3
4
5
6
7
8
9
create trigger 名字 (after/before) (insert/update/delete) on 表名 for each row

-- 创建触发器，监听向user中插入数据，只要插入就在loger(time,log)中记录日志
delimiter $$
	create trigger mytrigger after insert on user for each row
	begin
insert into logger values(now(),"insert succeed！");
end $$
delimiter ;

old 和 new
比如给user中插入数据的时候，给user2表也要添加

1
2
3
4
5
6
delimiter $$
create tigger mytri after insert on user for each row
	begin 
		insert into user2 values(new.id,new.username,new.age,new.age);
	end$$
delimiter ;
1
2
3
4
-- 更新表
begin
 update user2 set id=new.id,username =new.username,age=new.age,sex=new.sex where id=old.id;
end$$

索引

index /key

索引分类:

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引
  • FULLTEXT:全文索引(目前只有MyISAM引擎支持。而且只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引.)

FULLTEXT索引是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。
但很明显，中文不能按照这种方式进行分词。那又怎么办呢？
这个向大家介绍一个Mysql的中文分词插件Mysqlcft，有了它，就可以对中文进行分词，
想了解的同学请移步Mysqlcft，当然还有其他的分词插件可以使用。

创建索引

  • 方式一
    1
    2
    3
    4
    5
    6
    -- 给name字段设置索引
    create table student(
     id int,
     name varchar(32),
     index(name)
    );
  • 方式2
    1
    create index myIndex on student(name);
  • 方式3
    1
    alter table student add index myIndex(name);
  1. 创建 普通索引
    1
    2
    3
    CREATE index aaa on ren(p_name)
    -- 添加普通索引
    -- 注意:index :表示索引 aaa:表示索引的别名, on:表示给哪个表添加索引  ren:表名称,(添加索引的字段,多个字段以","间隔)
  2. 创建 唯一索引
    1
    2
    3
    CREATE UNIQUE index age on ren(p_age)
    -- 添加唯一索引
    -- 注意:unique index :表示唯一索引 aaa:表示索引的别名, on:表示给哪个表添加索引  ren:表名称,(添加索引的字段,多个字段以","间隔)
  3. 创建 主键索引
    1
    2
    3
    alter table 表名 add primary key(id);
    -- 添加主键索引
    -- 注意:主键索引只能有一个
  4. 创建 组合索引 
    1
    2
    3
    4
    5
    6
    create index id_name on ren (id,name)
    -- 添加组合索引
    -- 注意: 如上创建组合索引之后，查询：
    -- id [使用索引]
    -- id and name [使用索引]
    -- name [不使用索引]

视图

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

视图好处:

  1. 简化表之间的联结（把联结写在select中）；
  2. 适当的利用视图可以更清晰地表达查询
  3. 过滤不想要的数据（select部分）视图能够对机密数据提供安全保护
  4. 使用视图计算字段值，如汇总这样的值
1
2
-- 创建单表视图
create view myView as select * from user;

查询视图：

  • 视图是为了简化查询，而且视图就是用来查询的（只能查询，不能增删改） ；
  • 可以对外隐藏一些不想让别人看到的数据；
  • 可以封装查询查出的数据；
1
select * from myView;

删除视图： 

1
drop view myView;

修改视图：
视图本身不能修改，但是可以修改视图的数据来源

1
alter view myView as select * from user where id=2;

注意： 单表视图可以插入数据，但会影响基表，不建议做增删改；多表不能插入数据。

函数

内置函数和自定义函数

  • 函数一定会有返回值，参数只用来收参数的，不用写in，out;
  • 存储过程没有返回值，out类型又可以起到返回值的作用；

运算符(和Java很像)：
除法
x1 div x2;
x1 / x2;

取模：
mod(x1,x2);

内置函数(很多，字符串的方法和数学方法和Java几乎一样)

字符串函数 cancat(str1,str2);
时间函数 now();

自定义函数

1
2
3
4
5
6
7
8
9
delimiter $$
create function myFun( num int)
		returns int
		begin
			declare i int default 100;
			set i = i+num;
			return i;
		end $$
delimiter ;
1
2
-- 调用查看
select myFun(10);
1
2
drop FUNCTION fun_name;
-- 删除函数

DCL数据控制语言

权限和安全

1
2
3
4
-- 给用户名为zhangsan 密码为123456 的用户分配mydb数据库中user表查询和更改的权限。 
grant select ,update on mydb.user to "zhangsan"@"localhost" identified by "123456";
-- 若向要给库中的所有表，并给出全部权限
grant all on mydb.* to "zhangsan"@"localhost" identified by "123456";

原文作者：Reminance

原文链接：https://reminance.github.io/2019/10/24/20191024-01/

发表日期：October 24th 2019, 7:29:35 pm

更新日期：March 2nd 2021, 3:40:33 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. Mysql数据库的高级应用(存储过程，触发器、索引，视图，函数）
    1. 1.1. SQL分类:
    2. 1.2. 查询条件where
    3. 1.3. 聚合函数
    4. 1.4. 分组函数
    5. 1.5. 分页查询
    6. 1.6. MySQL中常见的约束
      1. 1.6.1. 联合主键，将几个字段看作整体，非空且唯一
      2. 1.6.2. 给主键设置自增长
      3. 1.6.3. 性别约束
      4. 1.6.4. 外键约束
      5. 1.6.5. 级联删除：
      6. 1.6.6. 来说说如何删除主键？
      7. 1.6.7. 删除主键及自增长？
      8. 1.6.8. 多表查询：
        1. 1.6.8.1. 笛卡尔积查询无意义
        2. 1.6.8.2. 隐式内连接(where 后面有多个表名)
        3. 1.6.8.3. 显式内链接(和隐式内连接查询结果一样，就是语法不同，好像这个相对快速)
        4. 1.6.8.4. 左外连接
        5. 1.6.8.5. 右外连接
        6. 1.6.8.6. 子查询
        7. 1.6.8.7. 自查询
        8. 1.6.8.8. 复制表
        9. 1.6.8.9. 取并集
    7. 1.7. 数据库对象(表，视图，存储过程，函数，索引)
      1. 1.7.1. 存储过程
        1. 1.7.1.1. 什么是存储过程：
        2. 1.7.1.2. 存储过程优点：
      2. 1.7.2. 触发器
      3. 1.7.3. 索引
        1. 1.7.3.1. 索引分类:
        2. 1.7.3.2. 创建索引
      4. 1.7.4. 视图
      5. 1.7.5. 函数
        1. 1.7.5.1. 内置函数(很多，字符串的方法和数学方法和Java几乎一样)
        2. 1.7.5.2. 自定义函数
      6. 1.7.6. DCL数据控制语言
Total : 15
2021
2020
2019
生活 面试 总结 java 日签 工作 算法 redis SpringBoot 网络工具 内网穿透 JAVA mysql 索引 HTTPS sentinel spring cloud
工作 生活 spring-cloud