数据库索引、触发器、存储过程、视图

1 索引
2 触发器
3 存储过程和函数
4 视图
5 基本的数据库建表语句练习

1 索引

（1）基本概念

https://blog.csdn.net/buhuikanjian/article/details/52966039

（2）建立索引的原则

https://www.cnblogs.com/aspwebchh/p/6652855.html

（3）具体操作语句

步骤1 创建表test_table1,添加三个索引

CREATE TABLE test_table1(
id INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(100) NOT NULL,
address CHAR(100) NOT NULL,
description CHAR(100) NOT NULL,
UNIQUE INDEX UniqIdx(id),
INDEX MultiColIdx(NAME(20), address(30)),
INDEX ComIdx(description(30))
);

步骤2 创建表test_table1,添加三个索引创建表test_table2，存储引擎为MyISAM

CREATE TABLE test_table2(
id         INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,
firstname   CHAR(100) NOT NULL,
middlename CHAR(100) NOT NULL,
lastname   CHAR(100) NOT NULL,
birth      DATE NOT NULL,
title       CHAR(100) NULL
) ENGINE=MYISAM;

步骤3 创建表test_table1,添加三个索引使用ALTER TABLE语句在表test_table2的birth字段上，建立名称为ComDateIdx的普通索引

ALTER TABLE test_table2 ADD INDEX ComDateIdx(birth);

步骤4 创建表test_table1,添加三个索引使用ALTER TABLE语句在表test_table2的id字段上，添加名称为UniqIdx2的唯一索引，并以降序排列

ALTER TABLE test_table2 ADD UNIQUE INDEX UniqIdx2 (id DESC);

步骤5 创建表test_table1,添加三个索引使用CREATE INDEX在firstname、middlename和lastname3个字段上建立名称为MultiColIdx2的组合索引

CREATE INDEX MultiColIdx2 ON test_table2(firstname, middlename, lastname);

步骤6 创建表test_table1,添加三个索引使用CREATE INDEX在title字段上建立名称为FTIdx的全文索引

CREATE FULLTEXT INDEX FTIdx ON test_table2(title);

步骤7 创建表test_table1,添加三个索引使用ALTER TABLE语句删除表test_table1中名称为UniqIdx的唯一索引

ALTER TABLE test_table1 DROP INDEX UniqIdx;

步骤8 创建表test_table1,添加三个索引使用DROP INDEX语句删除表test_table2中名称为MultiColIdx2的组合索引

DROP INDEX MultiColIdx2 ON test_table2;

2 触发器

image.png

image.png

（2）触发器使用

https://www.cnblogs.com/yank/p/4193820.html

3 存储过程和函数

（1）存储过程优缺点

https://blog.csdn.net/jackmacro/article/details/5688687

（2）存储过程、函数、游标

https://www.cnblogs.com/doudouxiaoye/p/5811836.html
https://www.cnblogs.com/jacketlin/p/7874009.html

（3）代码详解

1 创建查看fruits表的存储过程，创建了一个查看fruits表的存储过程，每次调用这个存储过程的时候都会执行SELECT语句查看表的内容。

CREATE PROCEDURE Proc()
     BEGIN
        SELECT * FROM fruits;
     END ;

2 创建名称为CountProc的存储过程，获取fruits表记录条数。COUNT(*) 计算后把结果放入参数param1中。
当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。

CREATE PROCEDURE CountProc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM fruits;
END;

3 创建存储函数NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型。

CREATE FUNCTION NameByZip ()
  RETURNS CHAR(50)
  RETURN  (SELECT s_name FROM suppliers WHERE s_call= '48075');

4 定义名称为myparam的变量，类型为INT类型，默认值为100。

DECLARE  myparam  INT  DEFAULT 100;

5 声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值。

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

MySQL中还可以通过SELECT ... INTO为一个或多个变量赋值，语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr;

这个SELECT语法把选定的列直接存储到对应位置的变量。col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

6 声明变量fruitname和fruitprice，通过SELECT，INTO语句查询指定记录并为变量赋值。

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='a1';

7 声明名称为cursor_fruit的光标

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;

8 使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量中。

FETCH  cursor_fruit INTO fruit_name, fruit_price ;

9 IF语句的示例

IF val IS NULL
  THEN SELECT 'val is NULL';
  ELSE SELECT 'val is not NULL';
END IF;

10 使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。

CASE val
  WHEN 1 THEN SELECT 'val is 1';
  WHEN 2 THEN SELECT 'val is 2';
  ELSE SELECT 'val is not 1 or 2';
END CASE;

11 使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程。

DECLARE id INT DEFAULT 0;
add_loop: LOOP  
SET id = id + 1;
  IF id >= 10 THEN  LEAVE add_loop;
  END IF;
END LOOP add_ loop;

12 使用LEAVE语句退出循环。循环执行count加1的操作。当count的值等于50时，使用LEAVE语句跳出循环。

add_num: LOOP  
SET @count=@count+1;
IF @count=50 THEN LEAVE add_num ;
END LOOP add_num ;

13 ITERATE语句示例。

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
  SET p1= p1 + 1;
  IF p1 < 10 THEN ITERATE my_loop;
  ELSEIF p1 > 20 THEN LEAVE my_loop;
  END IF;
  SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

14 REPEAT语句示例，id值小于等于10之前，将重复执行循环过程。
该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，使用LEAVE语句退出循环。REPEAT循环都以END REPEAT结束。

DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTIL  id >= 10
END REPEAT;

15 WHILE语句示例，id值小于等于10之前，将重复执行循环过程。

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;

16 定义名为CountProc1的存储过程，然后调用这个存储过程。

CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
     BEGIN
       SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
     END //

4 视图

（1）视图的含义和作用

视图是数据库中的一个虚拟表。同真实的表一样，视图包含一系列的行和列数据。行和列数据来源于自由定义视图查询所引用的表，并且在引用视图是动态生成。

（2）视图和表的联系、区别

image.png

（3）视图基本操作

步骤1：创建学生表stu，插入3条记录。

CREATE TABLE stu 
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
addr VARCHAR(50),
tel VARCHAR(50)
); 
INSERT INTO stu 
VALUES(1,'XiaoWang','Henan','0371-12345678'),
(2,'XiaoLi','Hebei','13889072345'),
(3,'XiaoTian','Henan','0371-12345670');

步骤2：创建报名表sign，插入3条记录。

CREATE TABLE sign 
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
s_sch VARCHAR(50),
s_sign_sch VARCHAR(50)
); 
INSERT INTO sign 
VALUES(1,'XiaoWang','Middle School1','Peking University'),
(2,'XiaoLi','Middle School2','Tsinghua University'),
(3,'XiaoTian','Middle School3','Tsinghua University');

步骤3：创建成绩表stu_mark，插入3条记录。

CREATE TABLE stu_mark (s_id INT PRIMARY KEY ,s_name VARCHAR(20) ,mark int ); 
INSERT INTO stu_mark VALUES(1,'XiaoWang',80),(2,'XiaoLi',71),(3,'XiaoTian',70);

步骤4：创建考上Peking University的学生的视图

CREATE VIEW beida (id,name,mark,sch)
AS SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark, sign.s_sign_sch
FROM stu_mark ,sign
WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=41
AND sign.s_sign_sch='Peking University';

步骤5：创建考上qinghua University的学生的视图

CREATE VIEW qinghua (id,name,mark,sch) 
AS SELECT stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch 
FROM stu_mark ,sign
WHERE stu_mark.s_id=sign.s_id  AND stu_mark.mark>=40 
AND sign.s_sign_sch='Tsinghua University';

步骤6：XiaoTian的成绩在录入的时候录入错误多录了50分，对其录入成绩进行更正。

UPDATE stu_mark SET mark = mark-50 WHERE stu_mark.s_name ='XiaoTian';

步骤7：查看更新过后视图和表的情况。

SELECT * FROM stu_mark;
SELECT * FROM qinghua;
SELECT * FROM beida;

步骤8：查看视图的创建信息。

 SELECT * FROM information_schema.views

步骤9：删除创建的视图。

DROP VIEW beida;
DROP VIEW qinghua;

5 基本的数据库建表语句练习（这个好像我真的是不会......）

建立一个数据库，逻辑名称为Student，包含1个数据文件和1个日志文件。数据文件初始大小为10M

if exists(select * from sys.sysdatabases where name='Student')
begin
use master
drop database Student
end
go
create database Student
on
--路径根据实际情况自行修改
(name=N'Student',filename=N'E:\Student.mdf',size=10mb,maxsize=unlimited,filegrowth=1)
log on
(name=N'Student',filename=N'E:\Student_log.ldf',size=10mb,maxsize=unlimited,filegrowth=1)

https://www.cnblogs.com/accumulater/p/6158294.html

创建表，增加约束。包括：主键约束、非空约束、性别范围约束、出生日期约束、年龄约束、外键约束、唯一性约束、评论约束、默认关键词约束

下面的语句可能不通顺，但是这些约束都有。check也可以使用enum代替。

CREATE TABLE tblstudent(
    stuID BIGINT PRIMARY KEY NOT NULL,
    stuName NVARCHAR(10) NOT NULL,
    stuSex NCHAR(1) NOT NULL DEFAULT '男' CHECK (stuSex IN ('男','女')),
    stuBirth DATETIME CHECK (stuBirth < getdate()) COMMENT '出生日期',
    stuNum NVARCHAR(18) UNIQUE
    Math INT CHECK(Sage > 18 AND Sage < 30) COMMENT '数学'
    stuID BIGINT REFERENCES tblstudent(stuID)
)

https://www.cnblogs.com/ghost-xyx/p/3795679.html

drop，alter，insert，update，delete

https://blog.csdn.net/leftwukaixing/article/details/44415875

这个东西属于基础知识，可能不需要深入了解，但是不知道一定会有问题。
END

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,864评论 6赞 494
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,175评论 3赞 387
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 159,401评论 0赞 349
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,170评论 1赞 286
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,276评论 6赞 385
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,364评论 1赞 292
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,401评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,179评论 0赞 269
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,604评论 1赞 306
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,902评论 2赞 328
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,070评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,751评论 4赞 337
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,380评论 3赞 319
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,077评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,312评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,924评论 2赞 365
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,957评论 2赞 351