mssql 使用视图 MySQL的基础学习（四）

发布时间：2022-11-06 14:01:21 所属栏目：MsSql教程来源：未知

导读： 文章目录
一、视图
视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。
视图优势:
语法:
CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED

文章目录

一、视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

视图优势:

语法:

CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name [(字段列表)]
AS 
select 查询语句
[WITH [CASCADED |LOCAL] CHECK OPTION]

说明:

view_name :表示要创建的视图名称。column_list: 可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。select查询语句: 表示一个完整的查询语句，将查询记录映射到视图中。[with [cascaded | local] check option]:可选项，表示更新视图时要保证在该视图的权限范围之内 1. 单表视图

create or replace algorithm=temptable view v_emp(id,username)
as
select id,name
from bemp;
desc bemp;
select * from bemp;
desc v_emp;
select * from v_emp;

create view v_emp1
as
select id,name,salary,salary*30
from bemp;

create view v_emp_new
as
select name,salary*30 as yearSal
from v_emp1;

2. 多表视图

create table v_emp_dept
as
select concat(e.name,'(',d.name,')') as username,e.salary,e.dept_id,d.name dept_name
from bemp e,bdept d
where e.dept_id=d.id;

3. 视图操作语法

alter view view_name
as
select 查询语句

4. 视图更新

视图是可更新的。也就是说，可以在UPDATE、 DELETE或INSERT等语句中使用它们，以更新基表的内容。

对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。

如果视图包含下述结构中的任何一种mssql 使用视图，那么它就是不可更新的：

对基表(数据表)进行更新改会影响视图

案例：

create view v_bemp
as
select id,name
from bemp;
insert into v_bemp values(6,'briup');
update v_bemp set name='zhaosi' where id=4;

二、索引

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引,MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。如:索引类似一本书的目录，比如要查找’briup’这个单词，可以先找到b开头的页然后向后查找，这个就类似索引。

索引的优点:

索引的缺点:

创建原则:

1. 原理 1.1 Hash算法

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。需然常见存储引擎并不支持Hash索引，但InnoDB有另一种实现方法:自适应哈希索引。InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。

通过字段的值计算的hash值，定位数据非常快。但是不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。

1.2 二叉树

二叉查找树实际上是数据域有序的二叉树，即对树上的每个结点，都满足其左子树上所有结点的数据域均小于或等于根结点的数据域，右子树上所有结点的数据域均大于根结点的数据域。使用二叉树可以提高查询数据的速度，但是有可能产生不平衡类似于链表的结构。

1.3 平衡二叉树

平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度，其特点:

平衡二叉树插入操作需要旋转，支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10。如果存放百万条数据的情况下，树高度越高，查询效率会越慢。

1.4 BTree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，Btree结构可以有效的解决之前的相关算法遇到的问题。

（1）B-Tree

在这里插入图片描述

（2）B+Tree

在这里插入图片描述

2. 操作 2.1 按实现的方式进行分类 2.2 按照功能划分 2.2.1 单列索引

（1）普通索取

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_name [(
字段1 数据类型 [约束条件] [默认值] [COMMENT '注解'],
字段2 数据类型 [约束条件] [默认值] [COMMENT '注解'],
字段3 数据类型 [约束条件] [默认值] [COMMENT '注解'],
 ......
[表约束条件]
index index_name(col_name...)
......
)][engine=innodb] [default charset=utf8];

或

create index index_name on tb_name(col_name);

或

alter table tb_name add index index_name(col_name);

案例：

create table tea(
id int primary key auto_increment,
name varchar(20),
age int,
index index_name(name)
);

create table tea1(
id int primary key auto_increment,
name varchar(20),
age int
);
create index index_name on tea1(name);

create table tea2(
id int primary key auto_increment,
name varchar(20),
age int
);
alter table tea2 add index index_name(name);

select *
from mysql.innodb_index_stats
where database_name='briup';

说明:

stat_value: 保存stat_ name的字段对应统计信息值sample_size: stat_value字段提供的统计信息值的采样页数stat_description: 统计信息名称stat_name字段中指定的统计信息的说明

select *
from mysql.innodb_index_stats
where database_name='briup' and table_name='tea';

show index from tea;

说明:

drop index index_name on tea

或

alter table tea drop index index_name;

（2）主键索引

创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯- -性并且不允许为.NULL

案例：

create table tea4(
id int primary key auto_increment,
name varchar(20),
age int
);

或

create table tea4(
id int,
name varchar(20),
age int
);
alter table tea4 add primary key(id);

（3）唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

案例：

create table tea3(
id int primary key auto_increment,
name varchar(20),
age int,
unique index_name(name)
);

或

create table tea3(
id int primary key auto_increment,
name varchar(20),
age int
);
create unique index index_name on tea3(name);

或

create table tea3(
id int primary key auto_increment,
name varchar(20),
age int
);
alter table tea3 add unique index_name(name);

2.2.2 组合索引

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和用户编号建立索引，同样的可以建立为普通索引或者是唯一索引或者主键索引。复合索引的使用复合最左原则。

案例：

create table tea5(
id int,
name varchar(20),
age int,
index index_name(id,name)
);

或

create table tea5(
id int,
name varchar(20),
age int
);
create index index_name on tea5(id,name);

或

create table tea5(
id int,
name varchar(20),
age int
);
alter table tea5 add index index_name(id,name);

2.2.3 全文索引

全文索引的关键字是fulltext,全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎,基于相似度的查询，而不是简单的where语句的参数匹配

要求如下;

索引变量：

MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

参数名称默认值最小值最大值作用

ft_min_word_len

3600

MyISAM引擎表全文索引包含的最小词长度

ft_query_expansion_limit

1000

MyISAM引擎表使用with query expansion进行全文搜索的最大匹配数

innodb_ft_min_token_size

InnoDB引擎表全文索引包含的最小词长度

innodb_ft_max_token_size

InnoDB引擎表全文索引包含的最大词长度

全文检索语法:

match (col_name1,col_name2,...) against(expr [search_modifier])

案例：

show variables like '%ft%';

create table Poetry(
id int primary key auto_increment,
name varchar(20),
content text,
fulltext(content)
);

或

create table Poetry(
id int primary key auto_increment,
name varchar(20),
content text
);
create fulltext index index_content on Poetry(content);

或

create table Poetry(
id int primary key auto_increment,
name varchar(20),
content text
);
alter table Poetry add fulltext index_content(content);

insert into Poetry values(1,'rain','Rain is falling all around');
insert into Poetry values(2,'rain','It falls on field and tree');
insert into Poetry values(3,'rain','It rains on the umbrella here');
insert into Poetry values(4,'rain','And on the ships at sea');

select *
from Poetry
where match(content) against('And');

2.2.4 空间索引

数据类型:

类型含义说明

Geometry

空间数据

任何一种空间类型

Point

点

坐标值

LineString

线

有一系列点连接而成

Polygon

多边形

由多条线组成

三、存储过程

存储过程的英文是Stored Procedure。它的思想很简单，就是一组经过预先编译的SQL语句的封装。执行过程:存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

语法:

CREATE PROCEDURE 参数名(IN |OUT |INOUT ?参数类型, . . . )
[LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string ' ]
BEGIN
存储过程体
END

说明:

存储过程优势

存储过程缺点

结束标记

MySQL默认的语句结束符号为分号;。为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符。

语法:

DELIMITER 新的结束标记

案例：

delimiter $
create procedure select_data()
begin
select id,last_name,salary from s_emp where id<10;
end $
-- 存储过程的调用
call select_data()$

delimiter $
create procedure avg_emp_salary()
begin
select avg(salary) from s_emp;
end $
-- 存储过程的调用
call avg_emp_salary()$

delimiter $
create procedure show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '?最高薪资值'
begin
select max(salary) from s_emp;
end $
-- 存储过程的调用
call show_max_salary()$

delimiter $
create procedure show_min_salary(out minsal float)
begin
select min(salary) into minsal from s_emp;
end $
-- 存储过程的调用 @msal声明变量
call show_min_salary(@msal)$
-- 取出存储过程的返回值
select @msal$

delimiter $
create procedure show_someone_salary(in name varchar(20),out sal float)
begin
select salary into sal from s_emp where last_name=name;
end $
-- 存储过程的调用 @sal声明变量
call show_someone_salary('Smith',@sal)$
select @sal$

delimiter $
create procedure show_mgr_name(inout name varchar(20))
begin
select last_name into name
from s_emp s
where s.id=(select manager_id from s_emp where last_name=name);
end $
set @name='Smith'$
-- 存储过程的调用
call show_mgr_name(@name)$
select @name$

delimiter $
create procedure add_num(inout n int)
begin
-- 变量的声明
declare i int;
declare sum int;
-- 变量的赋值
set i = 1 ;
set sum = 0 ;
while i <= n do
set sum = sum + i ;
set i = i +1 ;
end while ;
set n=sum;
end $
set @n=100$
call add_num(@n)$
select @n$

存储过程查看删除

案例：

show create procedure add_num\G;

show procedure status like 'add%'\G;

drop procedure add_num;

四、触发器

MySQL从5.0.2版本开始支持触发器, MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一-些数据库逻辑时，可以使用触发器来实现。

语法:

create trigger 触发器名称
{before |after} {insert |update |delete} ON tb_name
for each row
begin
sql语句
.....
end

说明:

触发器优点

触发器缺点

案例：

库存表：

create table goods(
id int primary key,
good_name varchar(50),
kc int
);
insert into goods values(1,'电脑',50),(2,'手机',50),(3,'自行车',50);

订单表：

create table order1(
id int primary key,
good_id int ,
num int,
foreign key(good_id) references goods(id)
);

触发器：

delimiter $
create trigger tg1
after insert on order1
for each row
begin
update goods set kc = kc-new.num
where id = new.good_id;
end$

订单插入数据：

insert into order1 values(1,1,2);

（1）查看触发器

案例：

show triggers\G;

show create trigger tg1;

select * from information_schema .triggers ;

（2）触发器删除

案例：

drop trigger if exists tg1;

（编辑：财气旺网 - 财气网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

sql实现跨数据库数据同	sqlserver 动态创建临
SQL数据操作语句你了解