MySQL基础笔记(5) - 约束&视图&存储过程

约束&视图&存储过程

目录

• 约束&视图&存储过程
  • • 1. 约束
      • *标识列
    • 2. 视图
    • 3. 存储过程
      • 3.1 变量
      • 3.2 存储过程使用

1. 约束

定义：约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

按功能分类：

• NOT NULL：非空，用于保证该字段的值不能为空
• DEFAULT：默认，用于保证该字段有默认值
• PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
• UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
• CHECK：检查约束
• FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值

按约束的级别分类：

• 列级约束：除了外键约束外都支持
• 表级约束：除了非空，默认，其它的都支持

#1.创建表时添加约束
CREATE TABLE stuinfo(
    #列级约束
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL UNIQUE, #可以同时添加多种约束
    gender CHAR(1) CHECK(gender='男' OR gender='女'),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    
    #表级约束
    CONSTRAINT fk_stuinfo_major FOREIGN_KEY(mojorid) REFERENCES major(id)
);
#也可以在表级约束中实现其它类型约束的效果，但不常用也不好用，此处略去


#2.修改表时添加约束
/*
1.添加列级约束
alter table [表名] modify column [字段名] [字段类型] [新约束];
2.添加表级约束
alter table [表名] add [约束类型](字段名) [外键的引用];
*/

#2.1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#2.3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#2.4.添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#2.5.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(mahorid) REFERENCES major(id);

/*
插入外键时需要注意：
	1.要求在从表设置外键关系
	2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3.主表的关联列必须是一个key(一般为主键或唯一键)
	4.插入数据时，应先插入主表，再插入从表；删除数据时，应先删除从表，再删除主表
	5.外键不能跨引擎，使用一个引擎的表不能引用具有使用不同引擎的表的外键
*/

*标识列

定义：又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

#1.创建表时设置标识列
CREATE TABLE tab_identity(
    	id INT PRIMARY KEY AUTO_INCREMENT,	#设置为标识列
    	name VARCHAR(20)
);

#2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

使用标识列的注意事项：

1. 标识列必须和key(主键/唯一键等)搭配使用
2. 一个表至多有一个标识列
3. 标识列的类型必须为数值型(int,float等)
4. 标识列可以通过 SET auto_increment_increment=[数字] 改变递增量；可以通过手动插入值，设置起始值
5. 标识列有一个缺点，就是无法明确的得知自动增量目前的值。可以使用last_insert_id()函数获取这个值。此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的Mysql语句

2. 视图

定义：生成一个临时的虚拟表，和普通表一样使用。当遇到多个地方用到同样的查询结果，或查询结果使用的sql语句较复杂时使用。

使用视图的优点：

• 可以重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

#1.创建视图
#查询所有订购了TNT2产品的客户
#①创建
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.`cust_id` = orders.`cust_id`
AND orderitems.`order_num` = orders.`order_num`;
#②使用
SELECT cust_name,cust_contact
FROM `productcustomers`
WHERE prod_id = 'TNT2';

#2.修改视图
#alter view 视图名 as 查询语句;

#3.删除视图
#drop view 视图名,视图名...

#4.查看视图
#desc 视图名;

#一般，应该将视图用于检索，而不用于更新。而且更新视图的条件非常苛刻，在此不多做介绍。

3. 存储过程

3.1 变量

• 系统变量：变量由系统提供，不是用户定义，属于服务器层面
  • 全局变量(GLOBAL)：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启
  • 会话变量(SESSION)：仅仅针对于当前会话（连接）有效
• 自定义变量：变量由用户自定义
  • 用户变量：针对于当前会话（连接）有效，同于会话变量的作用域
  • 局部变量：仅仅在定义它的begin end中有效

#1.系统变量
#1.1.查看所有的系统变量
SHOW GLOBAL|SESSION VARIABLES;
#1.2.查看满足条件的部分系统变量
SHOW GLOBAL|SESSION VARIABLES LIKE '%char%';
#1.3.查看指定的某个系统变量的值
#select @@GLOBAL|SESSION.[系统变量名];
#1.4.为某个系统变量赋值
#set @@GLOBAL|SESSION.[系统变量名] = 值;
#如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session

#2.自定义变量
#2.1.用户变量
#①声明并初始化(三种方式)
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
#②赋值
SELECT 字段 INTO 变量名 FROM 表;
#③使用
SELECT @用户变量名
#2.2.局部变量
#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
#②赋值(四种方式)
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
SELECT 字段 INTO 局部变量名 FROM 表;
#③使用
SELECT 局部变量名;

3.2 存储过程使用

定义：一组预先编译好的SQL语句的集合，可以理解成批处理语句

语法：参数模式 参数名 参数类型

• 参数模式有三种：
  • IN：该参数可以作为输入，需要调用时就作为值传入
  • OUT：该参数可以作为输出，可以作为返回值
  • INOUT：该参数既可以作为输入，又可以作为输出，兼具上面两种类型特点

注意：创建语句均只能在命令行窗口执行，请先在命令行登录并转到当前库，然后再粘贴进行操作

#1.创建存储过程
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	#存储过程体(一组合法的SQL的语句)
    SELECT AVG(prod_price) AS priceaverage
    FROM products;
END //
DELIMITER ;
#如果存储过程体仅仅只有一句话，BIGIN END可以省略
#存储过程的结尾必须要使用DELIMITER重新设置

#2.调用存储过程
#CALL 存储过程名(实参列表);
CALL productpricing();
#可以直接在编辑器内运行

#3.删除存储过程
DROP PROCEDURE productpricing;
#注意这里末尾没有();可以直接在编辑器内运行


#案例：接受订单号并返回订单中所有产品总价
DELIMITER //
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT SUM(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END//

CALL ordertotal(20005,@total);
SELECT @total;