MySql Review

最新推荐文章于 2023-02-20 23:57:34 发布
最新推荐文章于 2023-02-20 23:57:34 发布
阅读量332 收藏
点赞数
分类专栏： SQL 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44955356/article/details/105456892
版权

小标题很可爱的小目录

DML语言

数据操作语言
插入: insert
修改: update
删除: delete

插入语句

方式一: 经典的插入

语法：

insert into 表名(列名,...) values(值1,...);
  1. 插入的值的类型要与列的类型一致或兼容
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
  1. 不可以为null的列必须插入值。可以为null的列如何插入值？

方式一

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

方式二

INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');

  1. 列的顺序是否可以调换
    INSERT INTO beauty(NAME,sex,id,phone)
    VALUES(‘蒋欣’,‘女’,16,‘110’);

  2. 列数和值的个数必须一致

INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
  1. 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

方式二

语法：insert into 表名 set 列名=值,列名=值,...

INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

两种方式大pk 🎉

方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
方式一支持子查询, 方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

修改语句

修改单表的记录🎉

语法：
update 表名 set 列=新值,列=新值,... where 筛选条件;

修改多表的记录[补充]

语法：
sql92语法：
update 表1 别名,表2 别名 set 列=值,... where 连接条件 and 筛选条件;

sql99语法：
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;

案例👉修改单表的记录

案例1: 修改beauty表中姓唐的女神的电话为13899888899

UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';

案例2: 修改boys表中id好为2的名称为张飞, 魅力值 10

UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

案例👉修改多表的记录

案例1:修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

案例2: 修改没有男朋友的女神的男朋友编号都为2号

UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

SELECT * FROM boys;

删除语句, 删除整行

方式一：delete

语法：

  1. 单表的删除🎉
    delete from 表名 where 筛选条件

  2. 多表的删除[补充]

sql92语法

delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

方式二: truncate

语法：truncate table 表名;

案例👉方式一: delete

单表的删除

案例: 删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;
多表的删除

案例: 删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';

案例: 删除黄晓明的信息以及他女朋友的信息

DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

案例👉方式二：truncate语句

案例: 将魅力值>100的男神信息删除

TRUNCATE TABLE boys ;

delete pk truncate面试题 🎂

情况deletetruncate
where筛选可以加不能加
效率低高
删除的表有自增长列再插入时值从断点开始值从1开始
返回值有无
回滚可以不能

注解: truncate比 delete速度快, 且使用的系统和事务日志资源少. delete语句每次删除一行, 并在事务日志中为所删除的每行记录一项. 所以可以对delete操作进行roll back, 而truncate是ddl语言, 隐式提交, 因此不可以使用rollback.

SELECT * FROM boys;

DELETE FROM boys;

TRUNCATE TABLE boys;

INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);

DDL

数据定义语言, 库和表的管理
库的管理: 创建、修改、删除
表的管理: 创建、修改、删除

创建: create
修改: alter
删除: drop

库的管理

库的创建

语法：
create database [if not exists]库名;

案例: 创建库Books

CREATE DATABASE IF NOT EXISTS books ;

库的修改

改名:
RENAME DATABASE books TO 新库名;

更改库的字符集:

ALTER DATABASE books CHARACTER SET gbk;

库的删除

DROP DATABASE IF EXISTS books;

表的管理

表的创建 🐈

语法:

create table 表名(
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	...
	列名 列的类型[(长度) 约束]
)

案例: 创建表Book

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期
);
DESC book;

案例: 创建表author

CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)

)
DESC author;

表的修改

语法
alter table 表名 add|drop|modify|change column 列名 [列类型 约束];

修改列名

**案例: book表中的publishdate字段改名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
删除列
ALTER TABLE book_author DROP COLUMN  annual;
修改表名
ALTER TABLE author RENAME TO book_author;

DESC book;

表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;

通用的写法

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

表的复制

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

SELECT * FROM Author;
SELECT * FROM copy2;
仅仅复制表的结构
CREATE TABLE copy LIKE author;
复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;
只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';
仅仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

常见约束

含义

一种限制, 用于限制表中的数据, 为了保证表中的数据的准确和可靠性

分类: 六大约束

NOT NULL: 非空, 用于保证该字段的值不能为空
比如姓名, 学号等
DEFAULT: 默认, 用于保证该字段有默认值
比如性别
PRIMARY KEY:主键, 用于保证该字段的值具有唯一性, 并且非空
比如学号、员工编号等
UNIQUE:唯一, 用于保证该字段的值具有唯一性, 可以为空
比如座位号
CHECK:检查约束mysql中不支持
比如年龄、性别
FOREIGN KEY:外键, 用于限制两个表的关系, 用于保证该字段的值必须来自于主表的关联列的值, 在从表添加外键约束, 用于引用主表中某列的值. 比如学生表的专业编号, 员工表的部门编号, 员工表的工种编号.

添加约束的时机
1.创建表时
2.修改表时

约束的添加分类
列级约束:六大约束语法上都支持: 但外键约束没有效果;
表级约束: 除了非空、默认，其他的都支持

主键和唯一的大对比

类型保证唯一性是否允许为空一个表中可以有多少个是否允许组合
主键√×至多有1个√, 但不推荐
唯一√√可以有多个√,但不推荐

外键

  • 要求在从表设置外键关系
  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  • 主表的关联列必须是一个key（一般是主键或唯一）
  • 插入数据时，先插入主表，再插入从表
  • 删除数据时，先删除从表，再删除主表
CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束);

创建表时添加约束

添加列级约束

语法:

直接在字段名和类型后面追加约束类型即可.

只支持: 默认, 非空, 主键,唯一

CREATE DATABASE students;
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;

添加表级约束

语法: 在各个字段的最下面 [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)

);

修改表时添加约束

理解约束是modify, 表级约束是add
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

添加表级约束
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);

修改表时删除约束

删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
alter table modify column stu_name varchar(20);

删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

标识列

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

特点：

  • 标识列必须和主键搭配吗? 不一定, 但要求是一个key
  • 一个表可以有几个标识列?至多一个!
  • 标识列的类型只能是数值型
  • 标识列可以通过 SET auto_increment_increment=3;设置步长,也可以通过手动插入值设置起始值

创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 
);
TRUNCATE TABLE tab_identity;

INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;


SHOW VARIABLES LIKE '%auto_increment%';


SET auto_increment_increment=3;

常见的数据类型

数值型
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）

日期型

整型

tinyintsmallintmediumintint/integerbigint
12348

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 

);

DESC tab_int;


INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);

INSERT INTO tab_int VALUES(123,123);


SELECT * FROM tab_int;

小数

分类

  1. 浮点型
    float(M, D)
    double(M, D)
  2. 定点型
    dec(M, D)
    decimal(M, D)

特点

①
M：整数部位+小数部位
D：小数部位
如果超过范围，则插入临界值

②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

#测试M和D

DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT,
	f2 DOUBLE,
	f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);

原则: 所选择的类型越简单越好, 能保存数值的类型越小越好

字符型

  1. 较短的文本：char, varchar
  2. 其他：
    binary和varbinary用于保存较短的二进制
    enum用于保存枚举
    set用于保存集合
  3. 较长的文本：
    text
    blob(较大的二进制)

特点：

写法M的意思特点空间的耗费效率
char char(M)最大的字符数, 可以省略, 默认为1固定长度的字符比较耗费高
varchar varchar(M)最大的字符数, 不可以省略可变长度的字符比较节省低


CREATE TABLE tab_char(
	c1 ENUM('a','b','c')


);


INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');

SELECT * FROM tab_set;



CREATE TABLE tab_set(

	s1 SET('a','b','c','d')



);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');

日期型

分类

  1. date只保存日期
  2. time 只保存时间
  3. year只保存年
  4. datetime保存日期+时间
  5. timestamp保存日期+时间

特点：

写法字节范围时区等的影响
datetime81000-9999不受
timestamp41970-2038受
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP

);



INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;


SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

变量

系统变量
全局变量
会话变量

自定义变量
用户变量
局部变量

系统变量

说明: 变量由系统定义, 不是用户定义, 属于服务器层面
注意: 全局变量需要添加global关键字, 会话变量需要添加session关键字, 如果不写, 默认会话级别
使用步骤：

1、查看所有系统变量
show global|[session]variables;
2、查看满足条件的部分系统变量
show global|[session] variables like '%char%';
3、查看指定的系统变量的值
select @@global|[session]系统变量名;
4、为某个系统变量赋值
方式一：
set global|[session]系统变量名=值;
方式二：
set @@global|[session]系统变量名=值;

全局变量

作用域
针对于所有会话（连接）有效, 但不能跨重启
语法如下:

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

会话变量

作用域
针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

说明: 变量由用户自定义, 而不是系统提供的
使用步骤:

  • 声明
  • 赋值
  • 使用（查看, 比较, 运算等）

用户变量

作用域
针对于当前会话（连接）有效, 作用域同于会话变量
赋值操作符为=或:=

#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
#③使用（查看变量的值）
SELECT @变量名;

局部变量

作用域
仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话

#1. 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 [DEFAULT 值];


#2. 赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
#3. 使用（查看变量的值）
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的第一句话一般不用加@,需要指定类型

存储过程

存储过程和函数: 类似于java中的方法
好处: 提高代码的重用性; 简化操作.

含义: 一组预先编译好的SQL语句的集合, 理解成批处理语句

  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN

	存储过程体（一组合法的SQL语句）
END

注意:

参数列表包含三部分: 参数模式, 参数名, 参数类型
举例: in stuname varchar(20)

参数模式：

in: 该参数可以作为输入, 也就是该参数需要调用方传入值
out: 该参数可以作为输出, 也就是该参数可以作为返回值
inout: 该参数既可以作为输入又可以作为输出, 也就是该参数既需要传入值, 又可以返回值

begin end省略

如果存储过程体仅仅只有一句话, begin end可以省略

分号

存储过程体中的每条sql语句的结尾要求必须加分号,存储过程的结尾可以使用 delimiter 重新设置.
语法:delimiter 结束标记
案例: delimiter $

调用语法

CALL 存储过程名(实参列表);

案例👇

空参列表

案例1: 插入到admin表中五条记录

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $


#调用
CALL myp1()$

创建带in模式参数的存储过程

案例1:创建存储过程实现 根据女神名, 查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;

END $

#调用
CALL myp2('柳岩')$

案例2: 创建存储过程实现, 用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp3('张飞','8888')$

创建out模式参数的存储过程

案例1: 根据输入的女神名, 返回对应的男神名

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
	
END $

案例2: 根据输入的女神名, 返回对应的男神名和魅力值

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $


#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp $

创建带inout模式参数的存储过程

案例1: 传入a和b两个值, 最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n $

删除存储过程

语法: drop procedure 存储过程名

DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#不能一次删多个

查看存储过程的信息

DESC myp2;#不对
SHOW CREATE PROCEDURE  myp2;

函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别

存储过程: 可以有0个返回, 也可以有多个返回, 适合做批量插入或批量更新
函数: 有且仅有1 个返回, 适合做处理数据后返回一个结果

创建语法

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

注:
1.参数列表包含两部分: 参数名 参数类型
2.函数体: 肯定会有return语句, 如果没有会报错;如果return语句没有放在函数体的最后也不报错,但不建议return 值;
3.函数体中仅有一句话, 则可以省略begin end
4.使用delimiter语句设置结束标记

调用语法

SELECT 函数名(参数列表)

案例👇

无参有返回

案例: 返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
	
END $

SELECT myf1()$

有参有返回

案例1:根据员工名, 返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('k_ing') $

案例2: 根据部门名,返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

查看函数

SHOW CREATE FUNCTION myf3;

删除函数

DROP FUNCTION myf3;

案例👇

案例: 创建函数, 实现传入两个float, 返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

流程控制结构

顺序、分支、循环

分支结构

if函数

语法: if(条件, 值1, 值2)
功能: 实现双分支
应用在begin end中或外面

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 中或外面

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, 跳出, 结束当前所在的循环

while

语法：

[标签:] while 循环条件 do
	循环体;
end while [标签];

联想：

while(循环条件){

	循环体;
}

loop

语法：

[标签:]loop
	循环体;
end loop [标签];

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

repeat

语法:

[标签:] repeat
	循环体;
until 结束循环的条件
end repeat [标签];

没有添加循环控制语句

案例: 批量插入, 根据次数插入到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++;

}

添加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)$

添加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;
	}
	插入
	
}
朝阳王铁蛋
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
DBA神技之SQL Review
07-31
DBA要做哪些SQL Review工作 • Schema Review的注意事项 • SQL Review的注意事项 • 线上Schema分析、优化技巧 • 线上SQL分析、优化技巧
mysql清除重复数据，只保留一条最新记录
xyl的博客
11-25 1852
1，查出重复数据，现以card_number 重复就认定为重复 SELECT card_number FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1) 2，查出最新的数据 SELECT max(create_date) FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1 3,组合查出..
MySQL Review
qq_45762465的博客
02-20 142
MySQL复习
Mysql Review
qq_43378019的博客
03-12 105
数据结构 B树： 有序。 一个节点里由多个元素。 B+树： 非叶子节点的元素冗余了一份在叶子节点。 叶子结点之间有一个指针。（在mysql中是双向链表） 有序的。 innodb页 innodb负责从磁盘拿数据。一页16kb（最小基本单位） 为什么要这个页：涉及到操作系统的页（一页4kb）。 如果不考虑页，执行这个语句： 会扫整个表。 例如下图一行数据大概20b，一页16kb，如果想取到a=7，需要1次磁盘io即可。 这16kb里又包含了什么？innodb做了一些事来提升查询速度。这16kb并不完全存用
MySQL---review
weixin_61050264的博客
02-27 382
创建与删除数据库 1.使用DDL语句创建数据库： create database 数据库名 default character set 字符编码； 2.查看数据库： show databases; 3.删除数据库： drop database 数据库名称； 4.选择数据库： use 数据库名； Mysql数据库类型的分类： 整数类型： MySQL数据类型 含义（有符号） tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) me.
2020-review-8-mysql.pptx
07-30
MYSQL架构优化
MySQL+SQL+Review标准.doc 设计标准
02-07
表一旦设计好，字段只允许增加，不允许减少（drop column)
Linux上安装 Review Board
12-03
Linux上安装 Review Board 图文教程，针对在 CentOS 系统下的安装...develyum install mod_wsgiyum install memcachedpip install python-memcachedyum install patchpip install ReviewBoardpip install mysql-python
Java+mysql+Linux面试题
最新发布
05-31
"Java+mysql+Linux面试题" Java 是一种广泛使用的编程语言，它可以用于开发各种类型的应用程序，从桌面应用程序到 web 应用程序再到移动应用程序。...同时，需要通过代码 review 和测试来确保代码的正确性和可靠性。
Beginning MySQL Database Design and Optimization
01-01
* Shows how to take advantage of MySQL’s built-in functions, ...* Includes a review of MySQL fundamentals and essential theory, such as naming conventions and connections, for quick reference purposes.
MySql数据字典
07-29 487
information_schema数据库表说明: SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。 TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。 COLUMNS表：提供了表中...
mysql基本查询语句review
fly9006的博客
02-18 908
工作中很多时候都会用封装的库去操作mysql数据库，所以sql原生语句一旦长时间没有使用就会有所生疏，参考其他人的练习题，重新复习一遍基本的查询语句并记录。1.创建student和score表CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex
Mysql readview理解
祁东握力的博客
08-30 1182
https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc
MYSQL优化浅谈，工具及优化点介绍，mysqldumpslow，pt-query-digest，explain等
06-11 8086
MYSQL优化浅谈msyql是开发常用的关系型数据库，快速、稳定、开源等优点就不说了。 个人认为，项目上线，标志着一个项目真正的开始。从运维，到反馈，到再分析，再版本迭代，再优化… 这是一个漫长且考验耐心的过程。在这个过程中，作为数据存储的关键–>数据库的优化起到尤为重要的作用。 语文学的不好，废话也不多说，下面结合实例咱们说说MYSQL需要从哪些方面进行优化。
MySQL(二): 事务的隔离性原理
qq_37805943的博客
06-02 370
mvcc是什么，事务如何保证可见行，undolog，事务的隔离性
数据库并发操作可能出现的问题及解决方式
echo666的博客
09-23 5887
并发操作可能出现的问题 两类丢失更新 脏读 没提交就读到 幻读 如何处理 锁机制： 事务隔离级别 自动加锁 读未提交 – 会发生脏读 读提交 – 会发生不可重复读，不会读到脏数据 可重复读 – 重复读到之前的数据。在一个事务中。 测试不同隔离级别下并发操作出现的问题 开启事务环境 mysql> begin; start transaction ; commit ; rollback; 查看 更改事务 mysql> select @@tx_isolation; mysql> set se
SqlReview
有话好好说
04-23 540
*** 以mysql 库moon中的user_t为例。 数据库操作： show databases; use database; create database moon; drop database moon; 表操作： show tables; create table 'moon' ( 'id' int(11) not null auto_increm...
MySQL事务 - ReadView
weixin_39997438的博客
08-05 1589
在隔离级别中提到： 对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了。 对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。 对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到版本链。 核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。所以设计InnoDB的设计者提出了一个ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到
MySQL案例集锦
热门推荐
谁要大葱批发价一斤八毛
04-12 1万+
简单查询 下面的语句是否可以执行成功 可以 SELECT last_name , job_id , salary AS sal FROM employees; 下面的语句是否可以执行成功 可以 SELECT * FROM employees; 表达式可以直接用到select之后 SELECT employee_id , last_name, salary * 12 AS "ANNUAL...
mysql TransactionRollbackException
05-18
MySQL TransactionRollbackException is an exception that is thrown when a transaction is rolled back due to an error or an explicit rollback command. This exception is usually caused by a problem with the SQL query, such as a syntax error or a constraint violation. To resolve this issue, you can try the following steps: 1. Check the MySQL error log for any error messages related to the transaction rollback. 2. Review the SQL statements that were executed in the transaction to identify any issues with the queries. 3. Ensure that the transaction is properly structured and that all statements are executed within the same transaction context. 4. Check the database constraints to ensure that they are not being violated during the transaction. 5. If the issue persists, consider rolling back the transaction and starting over with a new transaction. It is important to note that TransactionRollbackException is a runtime exception, so it may not be caught by the application code. It is recommended to handle this exception in a try-catch block to gracefully handle the error and prevent any unwanted behavior.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值