数据库 (一) – 数据库基础

一、数据库基础

1.1 数据库的发展史

  • 萌芽阶段:文件系统
    使用磁盘文件来存储数据
  • 初级阶段:第一代数据库
    出现了网状模型、层次模型的数据库
  • 中级阶段:第二代数据库
    关系型数据库和结构化查询语言
  • 高级阶段:新一代数据库
    “关系-对象”型数据库

1.2 数据库管理系统

  • 数据库是数据的汇集,它以一定的组织形式存于存储介质上
  • DBMS是管理数据库的系统软件,它实现数据库系统的各种功能。是数据库系统的核心
  • DBA:负责数据库的规划、设计、协调、维护和管理等工作
  • 数据库管理系统的优点
    • 相互关联的数据的集合
    • 较少的数据冗余
    • 程序与数据相互独立
    • 保证数据的安全、可靠
    • 最中大限度地保证数据的正确性
    • 数据可以并发使用并能同时保证一致性
  • 文件管理系统的缺点
    • 编写应用程序不方便
    • 数据冗余不可避免
    • 应用程序依赖性
    • 不支持对文件的并发访问
    • 数据间联系弱
    • 难以按用户视图表示数据
    • 无安全控制功能
  • 数据库管理系统的基本功能
    • 数据定义
    • 数据处理
    • 数据安全
    • 数据备份

1.3 关系型数据库

  • RDBMS:(Relational Database Management System)
    数据库排名:https://db-engines.com/en/ranking
  • 属性说明
    • 关系 :关系就是二维表,其中:表中的行、列次序并不重要
    • 行row:表中的每一行,又称为一条记录
    • 列column:表中的每一列,称为属性,字段
    • 主键Primary key:用于惟一确定一个记录的字段
    • 域domain:属性的取值范围,如,性别只能是‘男’和‘女’两个值

1.4 数据库的正规化分析

  • 数据库规范化,又称数据库或资料库的正规化、标准化,是数据库设计中的一系列原理和技术,以减少数据库中数据冗余,增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念,并于1970年代初定义了第一范式、第二范式和第三范式的概念
  • RDMBS设计范式基础概念
    设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,不同的规范要求被称为不同范式,各种范式呈递次规范,越高的范式数据库冗余越小
  • 目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般数据库只需满足第三范式(3NF)即可
    • 1NF:无重复的列,每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性,确保每一列的原子性。除去同类型的字段,就是无重复的列
      说明:第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库
    • 2NF:属性完全依赖于主键,第二范式必须先满足第一范式,要求表中的每个行必须可以被唯一地区分,通常为表加上每行的唯一标识PK,非PK的字段需要与整个PK有直接相关性
    • 3NF:属性不依赖于其它非主属性,满足第三范式必须先满足第二范式。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息,非PK的字段间不能有从属关系

1.5 MYSQL的特性

  • 插件式存储引擎:也称为“表类型”,存储管理器有多种实现版本,功能和特性可能均略有差别;用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
    MyISAM ==> Aria InnoDB ==> XtraDB
  • 单进程,多线程
  • 诸多扩展和新特性
  • 提供了较多测试组件
  • 开源

1.6 关系型数据库的常见组件

  • 数据库:database
  • 表:table
    行:row
    列:column
  • 索引:index
  • 视图:view
  • 用户:user
  • 权限:privilege
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event scheduler,任务计划

二、SQL 概要

2.1 标准 SQL

  • 国际标准化组织(ISO)为 SQL 制定了相应的标准,以此为基准的 SQL 称为标准 SQL。
  • 标准 SQL 和特定的 SQL(内容来自 SQL 基础教程一书)
    • 每隔几年,ANSI(美国国家标准协会)或 ISO(国际标准化组织)等便会修订 SQL 的标准,进行语法的修订并追加新功能。
    • 1986 年,ANSI 首次制定了 SQL 的标准,之后又进行了数次修订。修订后的标准以修订年份来命名,例如 SQL:1999、SQL:2003、SQL:2008 等。以这些标准为基准的 SQL 就是标准 SQL。
    • 但是,SQL 的标准并不强制“每种 RDBMS 都必须使用”。虽然支持标准 SQL的 RDBMS 越来越多,但还是存在标准 SQL 无法执行的情况。这时就需要使用只能在特定 RDBMS 中使用的特殊 SQL 语句。
    • 其实,这也是没有办法的事情,起初(大约在 20 世纪 80 年代到 90 年代),标准 SQL 能够实现的功能非常有限,无法完全满足实际需要。RDBMS 的供应商为了弥补这些不足,不得不再单独追加所需要的功能。
    • 尽管如此,这些特定的 SQL 所带来的并不都是负面的影响。标准 SQL 将一些独特的功能收录其中,对其自身的发展起到了积极的推进作用。过去,各个供应商为了展现本公司的优势和独特性,也曾不遗余力地开发各自特定的 SQL。目前的标准 SQL 经过多次修订,功能已经十分完善

2.2 SQL书写规则

  • SQL语言规范
  • 在数据库系统中,SQL语句不区分大小写(建议用大写)
  • SQL语句可单行或多行书写,以“;”结尾
  • 关键词不能跨多行或简写,用空格和缩进来提高语句的可读性
  • 子句通常位于独立行,便于编辑,提高可读性
  • 注释:
  • SQL标准:
    /注释内容/ 多行注释
    — 注释内容 单行注释,注意有空格
  • MySQL注释:
    #

2.3 SQL语句及种类

  • SQL语句分类:
    • **DDL: **Data Defination Language 数据定义语言
      • CREATE: 创建数据库和表等对象
      • DROP: 删除数据库和表等对象
      • ALTER: 修改数据库和表等对象的结构
    • DML: Data Manipulation Language 数据操纵语言
      • SELECT:查询表中的数据
      • INSERT:向表中插入新数据
      • UPDATE:更新表中的数据
      • DELETE:删除表中的数据
    • DCL:Data Control Language 数据控制语言
      • COMMIT: 确认对数据库中的数据进行的变更
      • ROLLBACK: 取消对数据库中的数据进行的变更
      • GRANT: 赋予用户操作权限
      • REVOKE: 取消用户的操作权限

2.4 数据库操作

  • 数据库、表和列的名称需遵循一定规则
  • 命名规则:
    • 必须以半角英文字母作为开头
    • 可包括数字和三个特殊字符(# _ $)
    • 不要使用 MySQL 的保留字
    • 同一 database(Schema) 下的对象不能同名
# 查看数据库列表:
SHOW DATABASES;

# 创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name'COLLATE 'collate name'
# 示例
CREATE DATABASE shop;

# 修改数据库
ALTER DATABASE DB_NAME character set utf8;

# 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] 'DB_NAME';

# 选择数据库
USE 'DB_NAME';

# 查看支持所有字符集
SHOW CHARACTER SET;

# 查看支持所有排序规则
SHOW COLLATION;

# 获取命令使用帮助:
HELP KEYWORD;

2.5 表

2.5.1 表的创建(DDL)

# 获取帮助
HELP CREATE TABLE;

# 创建表:CREATE TABLE
#(1) 直接创建
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
< 列名 2> < 数据类型 > < 该列所需约束 > ,
< 列名 3> < 数据类型 > < 该列所需约束 > ,
< 列名 4> < 数据类型 > < 该列所需约束 > ,
.
.
< 该表的约束 1> , < 该表的约束 2> ,……);

# 示例
CREATE TABLE Product
    (product_id CHAR(4)  NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_type VARCHAR(32)  NOT NULL,
    sale_price INTEGER  ,
    purchase_price INTEGER  ,
    regist_date DATE  ,
    PRIMARY KEY (product_id));


#(2) 通过查询现存表创建;新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 'tbl_name' [(create_definition,...)] [table_options] [partition_options] 'select_statement'

# (3) 通过复制现存的表的表结构创建,但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

# 注意:Storage Engine是指表类型,也即在表创建时指明其使用的存储引擎,同一库中不同表可以使用不同的存储引擎,同一个库中表建议要使用同一种存储引擎类型

# 查看所有的引擎
SHOW ENGINES

# 查看表
SHOW tables [FROM db_name];

# 查看表结构
DESC [db_name.]tb_name;
SHOW COLUMNS FROM [db_name.]tb_name;

# 删除表
DROP TABLE [IF EXISTS] tb_name;

# 查看表创建命令
SHOW CREATE TABLE tbl_name;

# 查看表状态
SHOW TABLE STATUS LIKE tbl_name;

#查看库中所有表状态
SHOW TABLE STATUS FROM db_name;

2.5.2 数据类型

  • 列名右边的 INTEGER 或者 CHAR 等关键字,是用来声明该列的数据类型的,所有的列都必须指定数据类型
  • 数据类型表示数据的种类,包括数字型、字符型和日期型等
# 数字型
tinyint(m)      1个字节    -128~127
smallint(m)      2个字节    -32768~32767
mediumint(m)  3个字节    -8388608~8388607
int(m)          4个字节    -2147483648~2147483647
bigint(m)      8个字节    ±9.22x10^18
float(m,d)      单精度浮点型    8位精度(4字节)    m总个数,d小数位
double(m,d)      双精度浮点型    16位精度(8字节)    m总个数,d小数位

# 字符型
# char 和 varchar :
1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉,所以char类型存储的字符串末尾不能有空格,varchar不限于此
2.char(n) 固定长度,char(4) 不管是存入几个字符,都将占用4个字节,varchar 是存入的实际字符数+1个字节(n< n>255),所以 varchar(4),存入3个字符将占用4个字节
3.char 类型的字符串检索速度要比 varchar 类型的快

# varchar 和 text:
1.varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数+1个字节(n< n>255),text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text

# 二进制数据:BLOB
BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob是以二进制方式存储,不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集,BLOB不用指定字符集

# 日期时间类型
date  日期 '2008-12-2'
time  时间 '12:25:36'
datetime  日期时间 '2008-12-2 22:06:44'
timestamp  自动存储记录修改时间 # timestamp 字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间
YEAR(2), YEAR(4):年份

约束

  • 所有类型:
    • NULL 数据列可包含NULL值
    • NOT NULL 数据列不允许包含NULL值
    • DEFAULT 默认值
    • PRIMARY KEY 主键
    • UNIQUE KEY 唯一键
    • CHARACTER SET name 指定一个字符集
  • 数值型
    • AUTO_INCREMENT 自动递增,适用于整数类型
    • UNSIGNED 无符号

2.5.3 表的操作(DDL)

  • 约束:constraint,表中的数据要遵守的限制
    • 主键:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行;必须提供数据,即 NOT NULL,一个表只能有一个
    • 惟一键:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行;允许为NULL,一个表可以存在多个
    • 外键:一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
# 查看帮助
Help ALTER TABLE

# 查看表上的索引:
SHOW INDEXES FROM [db_name.]tbl_name;

# 表定义的更新
ALTER TABLE 'tbl_name'
字段:
    添加字段:add
    删除字段:drop
修改字段:
    alter(默认值), change(字段名), modify(字段属性)
索引:
    添加索引:add index
    删除索引:drop index
表选项

# 示例
# 添加列的 ALTER TABLE 语句
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
# 删除列的 ALTER TABLE 语句
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
# 其他
ALTER TABLE students RENAME s1;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;

# 表的删除
DROP TABLE [IF EXISTS] 'tbl_name'; # 将表完全删除

表数据的更新(DML语句)

# 数据的插入(INSERT)
INSERT INTO < 表名 > ( 列 1 ,  列 2 ,  列 3 ,  …… )  VALUES ( 值 1 ,  值 2 ,  值 3 ,  …… );

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
    SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

# 示例
INSERT INTO shop.Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T 恤衫 ', ' 衣服 ', 1000, 500, '2009-09-20');

INSERT INTO Product VALUES ('0002', ' 打孔器 ', ' 办公用品 ', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', ' 运动 T 恤 ', ' 衣服 ', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', ' 菜刀 ', ' 厨房用具 ', 3000, 2800, '2009-09-20');

INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', ' 叉子 ', ' 厨房用具 ', 500, NULL, '2009-09-20');


# 数据的删除(DELETE)

DROP TABLE [IF EXISTS] 'tbl_name'; # 将表完全删除
DELETE FROM 'tbl_name'; # 保留数据表,仅删除全部数据行

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] # 可先排序再指定删除的行数,注意:一定要有限制条件,否则将清空表中的所有数据 WHERE、LIMIT

# 删除 product_id 为 0002 的商品
DELETE FROM Product WHERE product_id = 0002;


# 数据的更新(UPDATE)
UPDATE < 表名 >
    SET < 列名 > = < 表达式 >;
  WHERE < 条件 >;
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
# 一定要有限制条件,否则将修改所有行的指定字段

# 将登记日期全部更新为“2009-10-10”
UPDATE Product
    SET regist_date = '2009-10-10';

# 将商品编号为 0003 的数据运动 T 恤的登记日期更新为 NULL
UPDATE Product
    SET regist_date = NULL
  WHERE product_id = '0003';

# 执行的繁琐的 UPDATE 语句
UPDATE Product
    SET sale_price = sale_price * 10
  WHERE product_type = ' 厨房用具 ';

2.6 查询语句

  • 从表中选取数据时需要使用 SELECT 语句,也就是只从表中选出(SELECT)必要数据的意思。通过 SELECT 语句查询并选取出必要数据的过程称为匹配查询或查询(query)
  • 参考:
    https://dev.mysql.com/doc/refman/5.7/en/select.html
    https://blog.csdn.net/zhyoulun/article/details/78513221
# 完整语法
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    

[into_option]

[FROM table_references [PARTITION partition_list]] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], … [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], …] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)]

[into_option]

[FOR UPDATE | LOCK IN SHARE MODE] into_option: { INTO OUTFILE ‘file_name’ [CHARACTER SET charset_name] export_options | INTO DUMPFILE ‘file_name’ | INTO var_name [, var_name] … }
  • 语法流程图

2.6.1 字段显示可以使用别名:

select name as NNN,age as GGG from students;

2.6.2 WHERE子句

指明过滤条件以实现“选择”的功能 算术操作符:+, -, *, /, %
比较操作符:=,<=>(相等或都为空),<>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num #在min和max之间
IN (element1, element2, …)
IS NULL
IS NOT NULL

2.6.3 DISTINCT 去除重复列

SELECT DISTINCT gender FROM students;

2.6.4 LIKE (模糊匹配)

% 任意长度的任意字符
_ 任意单个字符
RLIKE:正则表达式,索引失效,不建议使用
REGEXP:匹配字符串可用正则表达式书写模式,同上
逻辑操作符:NOT、AND、OR、XOR

2.6.5 其它条件

GROUP BY

分组统计:一旦引用了group by以后,在select后跟的内容:分组的字段名+聚合函数 根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()

HAVING

对分组聚合运算后的结果指定过滤条件
对比where:where是分组前过滤,having是分组后过滤。

ORDER BY

根据指定的字段对查询结果进行排序
升序:ASC (默认)
降序:DESC (也可以在字段前-,如-name)

LIMIT [[offset,]row_count]

对查询的结果进行输出行数数量限制

对查询结果中的数据请求施加“锁”

FOR UPDATE: 写锁,独占或排它锁,只有一个读和写
LOCK IN SHARE MODE: 读锁,共享锁,同时多个读

2.6.6 多表查询

交叉连接:笛卡尔乘积

内连接:

INNER JOIN
等值连接:让表之间的字段以“等值”建立连接关系;
不等值连接
自然连接:去掉重复列的等值连接
自连接

外连接:

左外连接:
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询:在查询语句嵌套着查询语句,性能较差

基于某语句的查询结果再次进行的查询
用在WHERE子句中的子查询
用于比较表达式中的子查询;子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age)
FROM students);
用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age
FROM teachers);
用于EXISTS
用于FROM子句中的子查询
使用格式:SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias
WHERE Clause;
示例:
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID
FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s
WHERE s.aage>30;

联合查询:UNION

SELECT Name,Age FROM students UNION SELECT Name,Age FROM
teachers;

三、其他组件

3.1 视图

  • 我们在创建表时,会通过 INSERT 语句将数据保存到数据库之中,而数据库中的数据实际上会被保存到计算机的存储设备(通常是硬盘)中。因此,我们通过 SELECT 语句查询数据时,实际上就是从存储设备(硬盘)中读取数据,进行各种计算之后,再将结果返回给用户这样一个过程
  • 从 SQL 的角度来看视图就是一张表,但是使用视图时并不会将数据保存到存储设备之中,而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句。我们从视图中读取数据时,视图会在内部执行该 SELECT 语句并创建出一张临时表。
# 创建方法
CREATE VIEW view_name [(column_list)]
AS
select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW  视图名称 (< 视图列名 1>, < 视图列名 2>,  …… )
AS
<SELECT 语句 >

# 查看视图定义
SHOW CREATE VIEW view_name

# 删除视图:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

3.2 函数

  • 函数:系统函数和自定义函数(user-defined function UDF)
  • 系统函数:https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
  • 自定义函数:创建的函数保存在mysql.proc表中,参数可以有多个,也可以没有参数,必须有且只有一个返回值
# 创建函数
CREATE [AGGREGATE] FUNCTION function_name (parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body

# 示例:无参 UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!";

# 调用自定义函数语法:
SELECT function_name(parameter_value,...);

# 查看函数列表:
SHOW FUNCTION STATUS;

# 查看函数定义
SHOW CREATE FUNCTION function_name;

# 删除UDF:
DROP FUNCTION function_nameg;

# 示例:有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = uid;
RETURN (SELECT COUNT(stuid) FROM students);
END//
DELIMITER ;


# 自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
# 说明:局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义
# 示例
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a,b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;


# 为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT * from tbl_name INTO parameter_name

# 示例
CREATE FUNCTION rowNumber()
RETURNS SMALLINT
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;
END//

3.3 存储过程

  • 存储过程优势
    • 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
    • 提高了运行速度
    • 同时降低网络数据传输量
  • 存储过程与自定义函数的区别
    • 存储过程实现的过程要复杂一些,而函数的针对性较强
    • 存储过程可以有多个返回值,而自定义函数只有一个返回值
    • 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
  • 存储过程保存在mysql.proc表中
# 创建存储过程
# 其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type

# 查看存储过程列表
SHOW PROCEDURE STATUS;

# 查看存储过程定义
SHOW CREATE PROCEDURE sp_name;

# 调用存储过程
# 说明:当无参时,可以省略"()",当有参数时,不可省略"()”
CALL sp_name ([ proc_parameter [,proc_parameter ...]])
CALL sp_name

# 存储过程修改
# ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

# 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name

# 存储过程示例
# 创建无参存储过程

delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;

# 调用存储过程
CALL showTime;
MariaDB [shop]> CALL showTime;
+---------------------+
| now()               |
+---------------------+
| 2021-04-01 00:30:31 |
+---------------------+
1 row in set (0.000 sec)

# 创建含参存储过程:只有一个IN参数

delimiter //
CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE stuid = uid;
END//
delimiter ;

call selectById(2);


# 存储过程示例:循环

delimiter //
CREATE PROCEDURE dorepeat(n INT)
BEGIN
SET @i = 0;   #'@'表示全局变量
SET @sum = 0;
REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
UNTIL @i > n END REPEAT;
END//
delimiter ;

# 调用存储过程
MariaDB [shop]> CALL dorepeat(100);
Query OK, 0 rows affected (0.001 sec)

MariaDB [shop]> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.000 sec)

# 创建含参存储过程:包含IN参数和OUT参数

delimiter //
CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= uid;
SELECT row_count() into num;
END//
delimiter ;

call deleteById(2,@Line);
SELECT @Line;

# 说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

3.4 流程控制

  • 存储过程和函数中可以使用流程控制来控制语句的执行
  • 流程控制
    • IF:用来进行条件判断。根据是否满足条件,执行不同语句
    • CASE:用来进行条件判断,可实现比IF语句更复杂的条件判断
    • LOOP:重复执行特定的语句,实现一个简单的循环
    • LEAVE:用于跳出循环控制
    • ITERATE:跳出本次循环,然后直接进入下一次循环
    • REPEAT:有条件控制的循环语句。当满足特定条件时,就会跳出循环语句
    • WHILE:有条件控制的循环语句

3.5 触发器

触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行
# 创建触发器
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

# 说明:
# trigger_name:触发器的名称
# trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发
# trigger_event:{ INSERT |UPDATE | DELETE },触发的具体事件
# tbl_name:该触发器作用在表名

# 触发器示例
CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);


# 创建触发器,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少
#增加时的触发器
CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

#删除时的触发嚣
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;


# 查看触发器
SHOW TRIGGERS;

# 查询系统表 information_schema.triggers 的方式指定查询条件,查看指定的触发器信息。
SELECT * FROM information_schema.triggers WHERE trigger_name='trigger_student_count_insert';

# 删除触发器
DROP TRIGGER trigger_name;
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇