MySQL 基础篇 (下)

原创 于 2022-01-02 10:19:45 发布 · 638 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #sql

本文详细介绍了如何在MySQL中创建、管理数据库，如创建数据库、查看表、修改表结构、删除表以及数据操作，包括添加、更新和删除数据。还涵盖了存储过程、函数、视图和约束的使用，以及SQL流程控制。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建和管理表

1.数据库的创建

# 1.创建数据库

CREATE DATABASE 数据库名 ;

# 2.创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集 ;

# 3.判断数据库是否已经存在，不存在则创建数据库 （推荐）

CREATE DATABASE IF NOT EXISTS 数据库名 (CHARACTER SET 字符集) ;

CREATE DATABASE IF NOT EXISTS nuo CHARACTER SET 'utf8' ;

2.数据库的管理

# 查看当前连接中的数据库有哪些
SHOW DATABASE;

# 使用数据库
USE ...(数据库名);

# 查看当前数据库中保存的数据表
SHOW TABLES;

# 查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

# 查询指定数据库下保存的表
SHOW TABLES FROM ...(数据库名);

3.修改数据库

# 更改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集 ;

4.删除数据库

# 方式 1 ：
DROP DATABASE 数据库名;

# 方式 2 ：(推荐)
DROP DATABASE IF EXISTS 数据库名;

5.创建数据表

# 方式 1 ：白手起家
CREATE TABLE IF NOT EXISTS ...(表名) （
id INT,
name VARCHAR(15),
hire_date DATE
）;
# 方式 2 ：基于现有的表，同时导入数据
CREATE TABLE IF NOT EXISTS ...(表名) 
AS 
SELECT ... , ... 
FROM ...;

# 练习 1 ：创建一个表 t2 ，实现对 表 t1 的复制，包括表数据
CREATE TABLE IF NOT EXISTS t2
AS 
SELECT *
FROM t1;

# 练习 2 ：创建一个表 t2 ，实现对 表 t1 的复制，不包括表数据
CREATE TABLE IF NOT EXISTS t2
AS 
SELECT *
FROM t1
WHERE 1 = 2;
# 查询表结构
DESC ...;

6.修改表

# 添加一个字段
ALTER TABLE 表名
ADD 字段名 数据类型; # 默认添加到最后一个字段

ALTER TABLE 表名
ADD 字段名 数据类型 FIRST; # 添加至第一个字段

ALTER TABLE 表名
ADD 字段名 数据类型 AFTER 字段名; # 指定位置

# 修改一个字段 ： 数据类型 ，长度 ，默认值
ALTER TABLE 表名
MODIFY name VARCHAR(25);

ALTER TABLE 表名
MODIFY name VARCHAR(25) DEFAULT 'nuo';

# 重命名一个字段
ALTER TABLE 表名
CHANGE name new_name VARCHAR(25);

ALTER TABLE 表名
CHANGE new_name name VARCHAR(15);
# 删除一个字段
ALTER TABLE 表名
DROP COLUMN id ;

7.重命名表

# 方式 1 ：
RENAME TABLE t1
TO t2;

# 方式 2 ：
ALTER TABLE t1
RENAME TO t2; # => TO 可省略

8.删除表

不光将表结构删除掉，同时表中的数据也删除掉，释放空间

DROP TABLE IF EXISTS t;

9.清空表

清空表，清空表中所以数据，但保留表结构

TRUNCATE TABLE t;

10.DCL 中的 COMMIT 和 ROLLBACK

  • COMMIT => 提交数据，一旦执行 COMMIT ，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
  • ROLLBACK => 回滚数据，一旦执行 ROLLBACK ，则可以实现数据的回滚，回滚到最近的一次 COMMIT 之后。
# 对比 TRUNCATE TABLE 和 DELETE FROM
# 相同点 ：都可以实现对表中所有数据的删除 ，同时保留表结构
# 不同点 ：
#	  TRUNCATE TABLE => 一旦执行此操作 ，表数据全部清除 ，同时 ，数据是不可以回滚的
#	  DELETE FROM => 一旦执行此操作 ，表数据可以全部清除（不带 WHERE 的情况） ，同时 ，数据是可以实现回滚

/*
  DDL 和 DML 的说明 ：
  	1.DDL 的操作一旦执行 ，就不可以回滚 ，指令 SET autocommit = FALSE 对 DDL 操作失效(因为在执行完 DDL之后一定会执行一次 COMMIT ，且此 COMMIT 操作不受 SET autocommit = FALSE 的影响)
  	2.DML 的操作默认情况 ，一旦执行 ，也是不可回滚的 ，但是 ，如果在执行 DML 之前 ，执行了 SET autocommit = FALSE ，则执行的 DML 操作就可以实现回滚 .
*/

# 演示 DELETE FROM 
COMMIT;

SELECT * 
FROM t;

SET autocommit = FALSE;

DELETE FROM t;

SELECT * 
FROM t;

ROLLBACK;

SELECT * 
FROM t;


# 演示 TRUNCATE TABLE 
COMMIT;

SELECT * 
FROM t;

SET autocommit = FALSE;

TRUNCATE TABLE t;

SELECT * 
FROM t;

ROLLBACK;

SELECT * 
FROM t;

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

数据处理之增删改

1.添加数据

# 创建数据库
CREATE DATABASE IF NOT EXISTS nuo CHARACTER SET 'utf8';
# 显示已有数据库
SHOW DATABASES;
# 使用数据库
USE nuo;
# 创建表
CREATE TABLE IF NOT EXISTS v_nuo (
id INT,
`name` VARCHAR(15),
`date` DATE
);

# 方式 1 ：一条一条地添加数据
# 1.1没有指明添加的字段
INSERT INTO v_nuo
VALUES (123,'诺','2021-12-29');

# 1.2指明要添加的字段（推荐）
INSERT INTO v_nuo(`name`,`date`)
VALUES ('小诺','2021-12-29');

# 1.3同时插入多条数据
INSERT INTO v_nuo(`id`,`name`)
VALUES 
(100,'Tom'),
(101,'Amy');

# 方式 2 ：将查询结果插入到表中

INSERT INTO v_nuo(`id`,`name`,`date`)
# 查询语句
SELECT ... ,... ,...
FROM ...
WHERE ....;
# 说明 ：添加时字段长度不能超范围

2.更新（修改）数据

/*可实现批量修改数据
  UPDATE ... SET ... (WHERE ... );
*/
UPDATE v_nuo
SET id = 102,`name` = '小诺'
WHERE id = 123;

3.删除数据

# DELETE FROM ...(WHERE ...);

DELETE FROM `v_nuo`
WHERE `id` IS NULL;

4.MySQL 8 的新特性 ：计算列

某一列的值是通过别的列计算得来的，例如 ：a 列的值为 1 ，b 列的值为 2 ，c 列的值不需要手动插入 ，通过 a 和 b 的值计算得到 c 列 ，那么 c 列就是计算列

USE nuo;

CREATE TABLE IF NOT EXISTS test(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

SHOW TABLES FROM nuo;

DESC test;

INSERT INTO test(a,b)
VALUES (1,2);

SELECT *
FROM test; # c = 3

UPDATE test
SET a = 0
WHERE b = 2;

SELECT *
FROM test; # c = 2

MySQL 数据类型概述

1.MySQL 中的数据类型

2.常见数据类型的属性

3.整数类型

ZEROFILL => '0' 填充 & 默认UNSIGNED

4.适用场景

5.日期和时间类型

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

6.文本字符串类型

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

7.小结

在这里插入图片描述

约束 => 保证数据的完整性

  • 实体完整性 => eg. 同一张表中不能存在两条完全无法区分的记录
  • 域的完整性 => eg. 年龄范围 0 - 120 ，性别范围 男 / 女
  • 引用完整性 => eg. 员工所在的部门 ，在部门表中要能找到这个部门
  • 用户自定义完整性 => eg. 用户名唯一 ，密码不为空 …

1.约束的分类

角度 1 ：约束字段的个数

​ 单列约束 & 多列约束

角度 2 ：约束的作用范围

​ 列级约束 ：将此约束声明在对应字段的后面

​ 表级约束 ：在表中所有字段都声明完 ，在所有字段的后面声明的约束

角度 3 ：约束的作用 （功能 ）

1. NOT NULL 	(非空约束)  => 无表级约束
2. UNIQUE     	(唯一性约束)
3. PRIMARY KEY	(主键约束)
4. FOREIGN KEY	(外键约束)
5. CHECK    	(检查约束)
6. DEFAULT		(默认值约束)

# MySQL5.7 不支持 check 约束 ，但可以使用 ，不过无效果

2.添加 / 删除约束

1. CREATE TABLE
2. ALTER TABLE

3.查看约束

# information_schema  => 数据库名 （系统库）
# table_constraints  => 表名称 （专门存储各个表的约束）

SELECT * 
FROM information_schema.table_constraints
WHERE table_name = '表名称';

4.非空约束

CREATE TABLE IF NOT EXISTS xiao_nuo (
uid INT NOT NULL,
v_name VARCHAR(15)
);

#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）

ALTER TABLE emp2 
MODIFY COLUMN id INT PRIMARY KEY;

#2.向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
ALTER TABLE emp2 
ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

#3.向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 
ADD COLUMN dept_id INT;

ALTER TABLE emp2 
ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

/*
		位置		支持的约束类型			是否可以起约束名
列级约束：	列的后面	语法都支持，但外键没有效果	不可以
表级约束：	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）
*/


/*
约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空
	比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值
	比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
	比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
	比如座位号
	CHECK:检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号，员工表的工种编号
	
添加约束的时机：
	1.创建表时
	2.修改表时
	
约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
		
	表级约束：
		
		除了非空、默认，其他的都支持
			
主键和唯一的大对比：

		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
	主键	√		×		至多有1个           √，但不推荐
	唯一	√		√		可以有多个          √，但不推荐
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key（一般是主键或唯一）
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表
*/

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束

)
CREATE DATABASE students;
#一、创建表时添加约束

#1.添加列级约束
/*
语法：
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
*/

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键

);


CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

#2.添加表级约束

/*
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);
SHOW INDEX FROM stuinfo;

#通用的写法：★

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

#二、修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo 
MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo 
MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo 
MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo 
ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo 
MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo 
ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo 
MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo 
MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo 
DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo 
DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

#2.添加表级约束

/*
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);
SHOW INDEX FROM stuinfo;

#通用的写法：★

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

5.自增列

# 主键设置自增列 （唯一且非空）
CREATE TABLE IF NOT EXISTS xiao_nuo (
uid INT PRIMARY KEY AUTO_INCREMENT,
v_name VARCHAR(15)
);

在这里插入图片描述

CREATE TABLE IF NOT EXISTS nuo_1(
id INT PRIMARY KEY,
`name` VARCHAR(15)
);

CREATE TABLE IF NOT EXISTS nuo_2(
uid INT,
`u_name` VARCHAR(15),
CONSTRAINT fk_nuo_2_uid FOREIGN KEY(uid) REFERENCES nuo_1(id) ON UPDATE CASCADE ON DELETE SET NULL
);

在这里插入图片描述
在这里插入图片描述
经典白学
在这里插入图片描述

视图 => 存储起来的 SELECT 语句

含义：理解成一张虚拟的表

1.视图和表的区别：

/*
        使用方式			    占用物理空间

视图    完全相同	      不占用，仅仅保存的是sql逻辑

表	   完全相同			      占用

*/

2.视图的创建

类似表的创建

# 语法：
	CREATE VIEW  视图名
	AS
	查询语句;

3.视图的增删改查

类似表的增删改查

1、查看视图的数据

SELECT * 
FROM my_v4;

SELECT * 
FROM my_v1 
WHERE last_name='Partners';

2.插入视图的数据

INSERT INTO my_v4(last_name,department_id) 
VALUES('虚竹',90);

3、修改视图的数据
UPDATE my_v4 
SET last_name ='梦姑' 
WHERE last_name='虚竹';

4、删除视图的数据
DELETE FROM my_v4;

某些视图不能更新

1.包含以下关键字的sql语句：
	分组函数、distinct、group  by、having、union或者union all
2.常量视图
3.Select中包含子查询
4.join
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表

4.视图逻辑的更新

# 方式一：

CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id > 100;

# 方式二:

ALTER VIEW test_v7
AS
SELECT employee_id 
FROM employees;

SELECT * FROM test_v7;

5.视图的删除

DROP VIEW test_v1;

6.视图结构的查看

DESC test_v7;
SHOW CREATE VIEW test_v7;

7.视图的优点：

1、操作简单 => 将经常使用的查询操作定义为视图 ，可以是开发人员不需要关心视图对应的数据表的结构 、表与表直接的关联关系 ，也不需要关心数据表之间的业务逻辑和查询条件 ，而只需要简单地操作视图即可 ，极大简化了开发人员对数据库的操作

2、减少了数据的冗余 => 视图和实际数据表不一样 ，它存储的是查询语句 。所以在使用的时候 ，我们要通过定义视图的查询语句来获取结果集 。而视图本身不存储数据 ，不占用数据存储的资源 ，减少了数据冗余 。

3.数据安全 => MySQL 将用户对数据的访问限制在某些数据的结果集上 ，而这些数据的结果集可以使用视图来实现 。用户不必直接查询或操作数据表 。这也可以理解为视图具有隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表 。同时 ，MySQL 可以根据权限将用户对数据的访问限制在某些视图上 ，用户不需要查询数据表 ，可以直接通过视图获取数据表中的信息 。这在一定程度上保障了数据表中数据的安全性 。

4.适应灵活多变的需求 => 当业务系统的需求发生变化后 ，如果需要改动数据表的结构 ，则工作量相对较大 ，可以使用视图来减少改动的工作量 。这种方式在实际工作中使用的比较多 。

5.能够分解复杂的查询逻辑 => 数据库中如果存在复杂的逻辑 ，则可以将问题进行分解 ，创建多个视图获取数据 ，再将创建的多个视图结合起来 ，完成复杂的逻辑查询 。

8.视图的不足：

如果我们在实际数据表的基础上创建了视图 ，那么 ，如果实际数据表的结构变更了 ，我们就需要及时对相关的视图进行维护 。特别是嵌套的视图 （在视图的基础上创建视图），维护会变得比较复杂 ，可读性不好 ，容易变成系统的潜在隐患 。因为创建视图的 SQL 可能会对字段重命名 ，也可能包含复杂的逻辑 ，这些都会增加维护的成本 。

所以 ，在创建视图的时候 ，结合实际的项目需求 ，综合考虑视图的优点和不足 使系统整体达到最优。

存储过程 & 存储函数

存储过程

含义：一组经过预先编译的sql语句的集合

好处：
1、提高了sql语句的重用性，减少了开发程序员的压力
2、提高了效率
3、减少了传输次数
分类：
1、无返回无参
2、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程

语法：
create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
	存储过程体
end

类似于方法：

修饰符 返回类型 方法名(参数类型 参数名,...){
	方法体;
}
注意
1、需要设置新的结束标记
delimiter 新的结束标记
示例：
delimiter $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;

END $
delimiter ;
2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

3、参数前面的符号的意思

in:该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出

调用存储过程

# 自定义实参使用 '@'
call 存储过程名(实参列表)

delimiter $

CREATE PROCEDURE nuo (inout a INT)
BEGIN 
	set a = 2 * a;
END $

delimiter ;


set @a = 2;
call nuo (@a);
SELECT @a;

创建函数

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END

调用函数

SELECT 函数名（实参列表）

存储函数和存储过程的区别

		   关键字		   调用语法	      返回值		    	   				应用场景
函数		FUNCTION	SELECT 函数()	只能是一个		一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程   PROCEDURE     CALL 存储过程()	可以有0个或多个		    			一般用于更新

在这里插入图片描述
在这里插入图片描述

变量和流程控制

1.变量

在 MySQL 数据库的存储过程和函数中 ，可以使用变量来存储查询或计算的中间结果数据 ，或者输出最终的结果数据 。

  • 系统变量
  • 用户自定义变量
    在这里插入图片描述
    在这里插入图片描述
1.查看系统变量
SHOW GLOBAL VARIABLES;  # MySQL8 => 620  全局系统变量

SHOW SESSION VARIABLES;  # MySQL8 => 643   会话系统变量

SHOW VARIABLES;  # MySQL8 => 643  默认为会话系统变量
2.查看部分系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';

SHOW VARIABLES LIKE 'character_%';
3.查看指定系统变量
SELECT @@global.max_connections;

4.修改系统变量的值

有时候 ，数据库管理员需要修改系统变量的默认值 ，以便修改当前会话或者MySQL 服务实例的属性 、特征

/*
方法 1 ：
	修改 MySQL的配置文件 ，继而修改 MySQL 的系统变量的值 （该方法需要重启 MySQL 服务）
	
方法 2 ：
	在 MySQL 运行期间 ，使用 'set' 命令重新设置系统变量的值
*/
SET @@global.变量名 = 变量值;
SET GLOBAL 变量名 = 变量值;

session 类似
4.用户变量

在这里插入图片描述

5.分类

用户变量 => 会话用户变量 VS 局部变量

会话用户变量 => 使用 “@” 开头 ，作用域为当前会话

局部变量 => 只能使用在存储过程和存储函数中

6.会话用户变量
# 变量的定义 

# 方式 1 ： "="  or  ":="
SET @用户变量 = 值；
SET @用户变量 := 值；

# 方式 2 ： ":="  or  INTO 关键字
SELECT @用户变量 := 表达式 [FROM 等句子];
SELECT 表达式 INTO @用户变量 [FROM 等句子];

# 查看用户变量的值 （查看、比较、运算等）

SELECT @用户变量;
7.局部变量
# 作用域：仅仅在定义它的begin end块中有效
# 应用在 begin end中的第一句话

#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

#②赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
	
#③使用（查看变量的值）
SELECT 局部变量名;

#案例：声明两个变量，求和并打印

#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;


#用户变量和局部变量的对比

		 	作用域			 			定义位置						语法
用户变量		当前会话					会话的任何地方			加@符号，不用指定类型
局部变量		定义它的 BEGIN END中 	BEGIN END的第一句话	  一般不用加@,需要指定类型

2.定义条件

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3.流程控制

#流程控制结构
/*
顺序、分支、循环
*/
#一、分支结构

#1.if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面
*/
#2.case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 

情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 

应用在begin end 中或外面
*/

#3.if结构
/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if

只能应用在begin end 中
*/

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $
SELECT test_if(87)$

#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
	
END $

CALL test_if_pro(2100)$

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

#二、循环结构
/*
分类：
while、loop、repeat
循环控制：
iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环
*/

#1.while
/*
语法：
【标签:】while 循环条件 do
	循环体;
end while【 标签】;

联想：
while(循环条件){
	循环体;
}
*/

#2.loop
/*
语法：
【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环
*/

#3.repeat
/*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;
*/

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO ADMIN(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$
/*
int i=1;
while(i<=insertcount){
	//插入
	i++;
}
*/
#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE ADMIN$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO ADMIN(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $
CALL test_while1(100)$

#3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE ADMIN$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO ADMIN(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $

CALL test_while1(100)$

/*
int i=0;
while(i<=insertCount){
	i++;
	if(i%2==0){
		continue;
	}
	插入
}
*/

4.游标

在这里插入图片描述

使用游标步骤
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

matrix_studio
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值