数据库基础
数据库配置和管理
数据库配置文件,可以修改端口号,存储位置,连接数,数据库引擎等等信息。
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
###MySQL服务的启动和停止
方式一:计算机——右击管理——服务
方式二:通过管理员身份运行
net start 服务名(启动服务)
net stop 服务名(停止服务)
连接数据库
mysql -u 用户名-P 端口号 -h 服务器地址 -p 密码
退出数据库全
exit或ctrl+C
常用命令
show database;
use 数据库名;打开指定的数据库
show tables;显示当前数据库的所有表
show tables from 数据库名;显示其他数据库的所有表
select database(); 查看当前打开的库
desc 表名;查看表结构
select version();查看数据库版本
没有登录数据库时查看数据库版本
mysql --version
mysql -V
语法规范
- 不区分大小写,但建议关键字大写,表名字段名小写。
- 每条命令用分号结尾 \g也行
- 每条命令可以换行或缩进
- 注释
- 单行注释: #注释内容或者-- 注释内容 -- 后面有一个空格
- 多行注释/注释内容/
SQL的语言分类
- DQL(Data Query Language):数据查询语言select
- DML(Data Manipulate Language):数据操作语言insert 、update、delete
- DDL(Data Define Languge):数据定义语言create、drop、alter
- TCL(Transaction Control Language):事务控制语言commit、rollback
DQL(Data Query Language):数据查询语言
- 查询语句结构
select 要查询的字段|表达式|常量值|函数
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段|表达式|函数|别名 asc|desc】
-
条件查询
- 条件表达式
示例:salary>10000
条件运算符:< >= <= = != <>
- 逻辑表达式
示例:salary>10000 && salary<20000
逻辑运算符:
and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
3.模糊查询
示例:last_name like 'a%'
- 条件表达式
-
常见函数
-
单行函数
-
字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格或字符,trim('字符' from '字符串')
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数 -
数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断 -
日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
year将日期中的年
month获取日期中的月
monthname获取日期中的月的英文名字 -
流程控制函数
if 处理双分支,类似JAVA的三元运算符
case 字段或表达式
when 常量值1 then 要返回的的值或执行的语句1
when 常量值2 then 要返回的的值或执行的语句2
else 默认值n或默认执行的语句n
end;
常量可以换位条件表达式 -
其他函数
version版本
database当前库
user当前连接用户
-
-
分组函数(聚合函数,统计函数,组函数)
sum 求和,只处理数字类型
avg 平均值,只处理数字类型
max 最大值
min 最小值
count 计数
以上分组函数都忽略值为null的记录
和distinct配合使用,去重
分组函数一起查询的字段只能是group by后面的字段,不能是其他字段
-
特点:
1、以上五个分组函数都忽略null值,除了count()
2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
4、count的参数可以支持:字段、、常量值,一般放1,建议使用 count(*)
-
分组查询
分组前筛选条件使用where,分组后再次筛选使用having -
连接查询
按年代可以分为:
- sql92 仅仅支持内连接
- sql99标准:内连接+外连接*(左外连接和右外连接)+交叉连接
按功能分类分为: - 内连接
- 等值连接(条件是等于)
- 非等值连接(条件不是等于)
- 自连接
- 外连接(用于查询一个 表中有,另一个表中没有,主表全部显示,从表没有就填充null,查询结果包含所有内连接的结果)
- 左外连接(左边的是主表)
- 右外连接(右边的是主表)
- 全外连接(两个表中所有的记录)
- 交叉连接
SQL92语法内连接语句结构
SELECT
查询的字段
FROM
表1,表2
WHERE
连接条件或筛选条件
GROUP BY
分组
HAVING
分组后的筛选条件
ORDER BY
字段名【 DESC | ASC】
SQL99语法连接语句结构(便于阅读,内连接可以省略INNER,交叉连接就是笛卡尔乘积,也就是SQL92语法连接去掉筛选条件)
SELECT
查询的字段
FROM
表1
【INNER|LEFT[OUTER]|RIGHT[OUTER]|CROSS】JOIN 表2 ON 连接条件
【INNER|LEFT[OUTER]|RIGHT[OUTER]|CROSS】JOIN 表3 ON 连接条件
WHERE
筛选条件
GROUP BY
分组
HAVING
分组后的筛选条件
ORDER BY
字段名【 DESC | ASC】
- 子查询(又称内查询)
用在其他语句中的select语句
按出现的位置分类:
- 放select后面
- from后面
- where或having后面
- exists后面(相关子查询)
按结果集的行列数不同: - 标量子查询(结果集只有一行一列)
- 列子查询(结果集有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
where或having后面
1. 标量子查询(结果集只有一行一列)
2. 列子查询(结果集有一列多行)
3. 行子查询(结果集有一行多列)
特点:子查询放小括号内,放条件右侧,标量子查询一般搭配单行操作符使用> < >= <= = <>,列子查询一般搭配着多行操作符使用in,any/some,all
- 分也查询
SELECT
查询的字段
FROM
表1
【INNER|LEFT[OUTER]|RIGHT[OUTER]|CROSS】JOIN 表2 ON 连接条件
【INNER|LEFT[OUTER]|RIGHT[OUTER]|CROSS】JOIN 表3 ON 连接条件
WHERE
筛选条件
GROUP BY
分组
HAVING
分组后的筛选条件
ORDER BY
字段名【 DESC | ASC】
LIMIT 起始索引(0开始),每一页的数量;
公式:
SELECT
查询的字段
FROM
表
LIMIT ( page - 1 )* size,size;
- 联合查询(将多条查询语句的结果合并成一个结果)
语法结构:
查询语句1
UNION
查询语句2
UNION
查询语句3
...
SELECT*FROM tableA WHERE columnA LIKE '%a%' OR columnB> 10;
SELECT*FROM tableA WHERE columnA LIKE '%a%' UNION
SELECT*FROM tableA WHERE columnB> 10;
-- 以上两条SQL查询结果一样
特点:
- 要求多条查询语句查询的列数是一致的
- 要求多条查询语句查询的每一列的类型和顺序最好一致
- 默认是去重的,使用union all可以不去重
注意:
sql中索引是从1开始,分页时是0
如果为表起了别名,查询的时候就不能再用原来表的名字
DML(Data Manipulate Language):数据操作语言
- 插入insert
语法结构
INSERT INTO 表名 (列名1,...) VALUES (值1,...);
INSERT INTO 表名 (列名1,...) VALUES (值1,...),(值1,...),(值1,...),...; -- 插入多条语句
INSERT INTO 表名 (列名1,...) SELECT语句; -- 将查询结果插入,可以用来表备份。
INSERT INTO 表名 SET 列名 1=值 1,列名 2=值 2,...;
如果插入语句不写该字段,则为默认值。如果省略所有列名,则默认为所有列名。
- 修改update
语法结构
UPDATE 表名
SET 字段 1 =值 1,字段 2 =值 2,
...
WHERE
筛选条件
SQL92语法修改多表记录
UPDATE 表名 1 别名 1,表名 2 别名 2
SET 字段 1 =值 1,字段 2 =值 2,
...
WHERE
筛选条件
SQL99语法修改多表记录
UPDATE 表名 1 别名 1
INNER | LEFT | RIGHT JOIN 表名 2 别名 2
ON 连接条件
SET 字段 1 =值 1,字段 2 =值 2,...
WHERE
筛选条件;
- 删除delete
删除语句语法结构
DELETE FROM 表名 WHERE 筛选条件;
TRUNCATE TABLE 表名; -- 删除表中所有数据
SQL92删除多表记录
DELETE 别名1 -- 这里写表1的别名,就在表1中删除,写表2的别名就删表2中的记录,全部写都会删除
FROM
表 1 别名 1,表 2 别名 2
WHERE
连接条件
AND 筛选条件;
SQL99删除多表记录
DELETE 别名1,别名2
FROM 表 1 别名1
INNER | LEFT | RIGHT JOIN 表 2 别名2
ON 连接条件
WHERE 筛选条件;
DDL(Data Define Languge):数据定义语言
数据类型
-
数值型
- 整数:tinyint(1字节)、smallint(2字节)、mediumint(3字节)、int/integer(4字节)、bigint(5字节)
如果创建时没有设置是有符号还是无符号,则默认是有符号,在类型后面添加UNSIGNED设置为无符号;如果插入的数值超出了整型的范围,会插入临界值;如果不设置长度会有默认的长度,对于整形类型,类型决定了他的数值范围,设置的长度决定显示的最大宽度,如果宽度不够,会填充0(建表时要使用关键字ZEROFILL,使用这个关键字默认就是无符号整形)。 - 小数
-
定点数:DEC/DECIMAL(M,D)(M+2字节)最大取值范围和DOUBLE相同,但是更精确,货币运算建议使用定点数。
M代表整数部位和小数部位的总长度,D代表小数部分长度,超过范围插入临界值,M和D都可以省略,如果是DECIMAL,M默认为10,D默认为0 -
浮点数:FLOAT(M,D)(4字节)、DOUBLE(M,D)(8字节)
M和D省略后,根据插入数值的精度来决定精度
-
- 整数:tinyint(1字节)、smallint(2字节)、mediumint(3字节)、int/integer(4字节)、bigint(5字节)
-
字符型
- 较短的文本:char(0~255个字符)、varchar(0~65535个字符)
char(M)和varchar(M)的区别,M代表最大的字符数,char是固定长度的字符,性能更高,耗费空间,varchar代表可变长度的字符,节约空间,性能更低 - 较长的文本:text、blob(较长的二进制数据)
- ENUM
CREATE TABLE t1(c1 ENMU('a','b','c'));-- 插入时该字段的值只能是a或b或c - SET
CREATE TABLE t1(c1 SET('a','b','c'));-- 插入时该字段可以插入多个值
INSERT INTO t1 VALUES('a,b');
INSERT INTO t1 VALUES('a,c');
- 较短的文本:char(0~255个字符)、varchar(0~65535个字符)
-
日期型:
- date(4字节)
- datetime(8字节)
- timestamp(4字节)
区别:timestamp支持的时间范围较小,19700101080001——2038年的某个时间
和实际时区有关,插入值之后修改时区,值会跟着变为对应时区的时间,更能反应实际的日期,而datetime则只能反映出插入时的当地时区,timestamp的属性受mysql版本和sqlmode的影响很大 - time(3字节)
- year(1字节)
常见约束
为了保证表中数据的准确性和可靠性
六大约束:
- NOT NULL:非空约束,保证该字段不能为空
- DEFAULT:默认约束,用于保证该字段有默认值
- PRIMARY KEY:主键约束,用于保证该字段的值具有唯一性,并且非空,一个表只能有一个,允许多个字段组合设置,但不推荐
- UNIQUE:唯一性约束,可以为空,可以有多个字段有唯一性约束,允许多个字段组合设置,但不推荐
- CHECK:保证列中的值符合指定的条件。
- FOREIGN KEY:外键约束,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联字段的值,在从表中添加外键约束,用于引用主表中某列的值,主表对应列必须是主键或唯一键,插入数据时先插入主表,再插入从表。删除数据时,先删除从表,再删除主表。
添加约束的时机:创建表或修改表 时
约束的添加分类:
- 列级约束
语法上六大约束逗支持,但是外键约束没有效果 - 表级约束
除了非空约束和默认约束,其他都支持
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束 -- CONSTRAINT 约束名字1 约束类型1,CONSTRAINT 约束名字2 约束类型2
)
主键外键和唯一性约束都会自动创建索引值,所以可以通过SHOW INDEX FROM 表名
查看外键
添加或修改列级约束(也可以删除约束)
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 [约束];-- 设置修改后的字段类型和约束类型,可以用来添加修改或删除约束
添加表级约束
ALTER TABLE 表名 ADD 表级约束;
例如:
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 PRIMARY KEY;
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);-- 添加主键约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(字段名) REFERENCES 表名2(字段名2);-- 添加外键约束,字段名2一般为主键或唯一键,CONSTRAINT 约束名可以省略
删除约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 [约束];
ALTER TABLE 表名 DROP 约束;
例如:
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 NULL;-- 删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型;-- 删除默认约束/主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;-- 删除主键约束
ALTER TABLE 表名 DROP INDEX 需要被删除的唯一性约束的名字;-- 删除唯一性约束
SHOW INDEX FORM 表名;-- 如果设置约束时没有设置名字,可以用来查看默认的约束名字
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名字;-- 删除外键约束
标识列
又称为自增长列,可以不用手动的插入值,系统提供默认的序列值。一个表只能有一个自增长列,自增长列必须是数值型,而且是一个key。
- 创建表时设置标识列
CREATE TABLE table1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20);
);-- id字段在插入时要么不写该字段,要么值写null,都会自动增长
可以通过手动插入值来设置起始值,通过下面语句设置步长
SET auto_increment_increment=3;-- 设置步长
- 修改表时设置标识列
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 键约束 AUTO_INCREMENT;
- 修改表时删除标识列
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 键约束;-- 修改表时删除标识列
DDL分为库的管理和表的管理
- 库的管理
创建:CREATE
修改:ALTER
删除:DROP
创建库的语法:
CREATE DATABASE [IF NOT EXISTS]库名;
库的修改:
RENAME DATABASE 库名 TO 新库名;-- 在MySQL 5.1.23 之后版本,因为安全考虑,删掉了这一条命令。
ALTER DATABASE 库名 CHARACTER SET 编码;-- 修改字符集
库的删除:
DROP DATABASE [IF EXISTS] 库名;
- 表的管理
创建:CREATE
修改:ALTER
删除:DROP
SHOW CREATE TABLE 表名;-- 查看建表语句
表的创建:
CREATE TABLE 表名(
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
...
列名 列的类型[(长度) 约束]
)
表的修改:
- 修改列名
- 修改列的类型或约束
- 添加新列
- 删除列
- 修改表名
ALTER TABLE 表名 CHANGE [COLUMN]原字段名 新字段名 字段类型 [约束];-- 修改字段名和字段类型
ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型 [约束];-- 修改字段类型和约束
ALTER TABLE 表名 ADD COLUMN 新的列名 字段类型 约束;-- 添加新列
ALTER TABLE 表名 DROP COLUMN 列名;-- 删除列名
ALTER TABLE 表名 RENAME TO 新表名;-- 修改表名
表的删除:
DROP TABLE [IF EXISTS] 表名;
表的复制:
- 仅复制表的结构
CREATE TABLE 新的表名 LIKE 被复制的表名;
CREATE TABLE 新的表名 SELECT (需要复制的字段1,字段2,...) FROM 被复制的表名 WHERE 0;-- 仅复制部分字段,不复制数据,0为false,1为true
- 复制表的结构+数据
CREATE TABLE 新的表名 SELECT * FROM 被复制的表名;
CREATE TABLE 新的表名 SELECT (需要复制的字段1,字段2,...) FROM 被复制的表名 WHERE 筛选条件;-- 复制满足条件的数据以及表的部分字段
TCL(Transaction Control Language):事务控制语言
一个或sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
mysql中用的最多的存储引擎有Innodb,myisam,memory。Innodb支持事务,而myisam、memory不支持事务。
SHOW ENGINES;-- 查看有哪些存储引擎
事务的ACID属性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 - 隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 - 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
事务的创建
-
隐式事务:事务并没有明显的开启和结束的标记
比如insert,update,delete语句 -
显式事务:事务具有明显的开启和结束的标记
前提:首先必须禁用自动提交功能。
SHOW VARIABLES LIKE 'autocommit';-- 查看是否关闭自动提交
SET autocommit=0;-- 关闭自动提交
START TRANSACTION;-- 开启事务,默认开启的,此语句可以省略
编写事务中的sql语句(select insert update delete)
语句1;
[SAVEPOINT 保存点名字1];-- 此语句可能会有,用于回滚时回滚到指定状态
语句2;
[SAVEPOINT 保存点名字2];
...
commit/rollback;-- 提交事务/回滚事务
ROLLBACK TO 保存点名字;-- 回滚到指定的保存点
事务的隔离级别
同时运行多个事务,这些事务如果同时访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致问题。
例如同时运行两个事务T1和T2,有以下情况发生:
- 脏读:T1读取了已经被T2更新但还没有被提交的字段之后,若T2回滚,则T1读取的内容就是临时且无效的。
- 不可重复度:T1读取了一个字段,然后T2更新了该字段,T1再次读取同一个字段,值就不同了。
- 幻读:T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后T1再次读取同一个表,就会多出几行。
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。隔离级别越高,数据一致性就越好,但并发性越弱。
数据库提供4种事务隔离级别:
|隔离级别|描述|
|-------|-------|
|READUNCOMMITTED(读未提交数据)|允许事务读取未被其他事务提交的变更,脏读、不可重复读和幻读的问题都会出现。|
|READCOMMITED(读已提交数据)|只允许事务读取已经被其他事务提交的变更,可以避免脏读,但不可重复读和幻读问题仍然可能出现。|
|REPEATABLEREAD(可重复读)|确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读,但幻读的问题仍然存在。|
|SERIALIZABLE(串行化)|确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作,所有并发问题都可以避免,但性能十分低下。|
MySQL支持4种隔离级别,默认的事务隔离级别为REPEATABLEREAD。Oracle支持两种事务隔离级别:READ COMMITED,SERIALIZABLE,默认的事务隔离级别为READ COMMITED。
SET [SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 设置当前连接或会话的隔离级别,SESSION可以省略
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 设置全局的的隔离级别
show variables like '%_isolation%';-- 查看隔离级别
SELECT @@transaction_isolation;-- 查看隔离级别,mysql8 以前是SELECT @@tx_isolation
delete和truncate使用时的区别
delete支持事务回滚,truncate不支持事务
视图
虚拟表,和普通表一样使用。MySQL5.1版本出现的新特性,是通过表动态生成的数据。
使用场景:
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
CREATE VIEW 视图名 AS 查询语句;-- 创建视图
SELECT * FROM 视图名 WHERE 筛选条件;-- 使用视图
CREATE OR REPLACE VIEW 视图名 AS 查询语句;-- 修改视图1
ALTER VIEW 视图名 AS 查询语句;-- 修改视图2
DROP VIEW 视图名1,视图名2,...;-- 删除视图
DESC 视图名;-- 查看视图
SHOW CREATE VIEW 视图名;-- 查看视图
好处:
- 重用SQL语句
- 简化复杂的SQL操作,不必知道它的查询细节
- 保护数据,提高安全性
视图更新(不用):
与表的增删改查语句相同,修改的是原始表的数据。所以不建议修改视图,应该设置视图权限为只读。
视图能否更新与视图中查询语句的定义有关系,以下类型的视图是不能更新的:
- 含有分组函数,distinct,group by,having,union或union all
- 常亮视图
- Select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
视图和表对比
||创建语法的关键字|数据是否实际占用物理空间|使用|
|-------|-------|-------|-------|
|视图|CREATE VIEW|没有|增删改查,一般只查询,不进行正删改|
|表|CREATE TABLE|占用|增删改查|
变量
- 系统变量(变量由系统提供,不是用户定义,属于服务器层面)
- 全局变量(服务器每次启动将所有的全局变量赋初始值,针对所有的会话或连接有效,但是不能跨重启,重启失效,要跨重启就只能改配置文件)
- 会话变量(针对当前的会话或连接有效,换一个连接还是原来的值)
- 自定义变量
- 用户变量
- 局部变量
系统变量
SHOW SESSION] VARIABLES;-- 会话变量
SHOW GLOBAL VARIABLES;-- 全局变量
SHOW GLOBAL|[SESSION] VARIABLES LIKE '%char%';-- 查看满足条件的系统变量
SELECT @@global.系统变量名;--查看具体的一个全局变量
SELECT @@[session.]系统变量名;--查看具体的一个会话变量
SET global|[session]变量名 = 变量值;-- 为某个系统变量赋值
SET @@global.|[session.]系统变量名=变量值;-- 为某个系统变量赋值
自定义变量
用户变量
作用域同会话变量的作用域,针对当前会话或连接有效。应用在任何地方,也就是begin end里面或外面
- 声明并初始化
赋值操作符: =或:=
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;-- SELECT赋值时不能去掉冒号
- 赋值或更新用户变量的值
方式一:通过SET或SELECT
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;-- SELECT赋值时不能去掉冒号
方式一:通过SELECT INTO把查询结果赋值给变量
SELECT 字段 INTO @变量名 FROM 表;-- select count(*) into @count from 表名;
- 使用(查看用户变量的值)
SELECT @用户变量名;
局部变量
仅仅在定义它的begin end中有效。
- 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 初始值;
- 赋值(赋值必须兼容定义时的类型)
SET 局部变量名=值;-- 注意这里不需要@符号
SET 局部变量名:=值;
SELECT @局部变量名:=值;-- 注意这里需要@符号
- 使用
SELECT 变量名;
作用域 | 定义和使用的位置 | 语法 | |
---|---|---|---|
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号,不用限定类型 |
局部变量 | begin end中 | 只能在BEGIN END中,且为第一句话 | 一般不用加@符号,需要限定类型 |
存储过程和函数
存储过程
类似于Java中的方法
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
好处:
- 提高代码的重用性
- 简化操作
- 减少编译次数,并且减少了连接数据库服务器的次数,提高了效率
创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
- 参数列表包含三部分:参数模式 参数名 参数类型
举例:IN stuname VARCHAR(20)
参数模式:
IN:该参数可以作为输入,也就是说该参数需要调用方传入值
OUT:该参数可以作为输出,也就是说该参数可以作为返回值
INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值 - 如果存储过程体仅仅只有一句话,BEGIN END可以省略。存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾符号可以使用DELIMITER 重新设置。
DELIMITER 结束标记
调用方法
CALL 存储过程名(实参列表);
示例:
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR ( 10 ) NOT NULL,
`password` VARCHAR ( 10 ) NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 15 DEFAULT CHARSET = utf8
DELIMITER $;-- 设置结束符
CREATE PROCEDURE test()
BEGIN
INSERT INTO users(username,`password`)VALUES('suixin','123'),('sx','456');
END $
DELIMITER ;-- 设置结束符为分号
CALL test();
-- 带输入参数的存储过程
DELIMITER -- 设置结束符
CREATE PROCEDURE test2 (IN username VARCHAR(10),IN password VARCHAR (10))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT count(*) INTO result FROM users WHERE users.username = username AND users.password =password;
SELECT IF(result>0,'成功','失败');
END ;
delimiter ;
CALL test2 ('suixin','123');
CALL test2 ('suixin','456');
CREATE PROCEDURE test3 (IN id INT,OUT username VARCHAR (10))
BEGIN
SELECT users.username INTO username FROM users where users.id=id;
END ;
-- 带返回值的存储过程
delimiter ;
CALL test3 (3,@name);
select @name;
删除存储过程
DROP PROCEDURE 存储过程名;
查看存储过程
SHOW PROCEDURE 存储过程名;
函数
创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
参数列表包含参数名和参数类型
函数体肯定会有RETURN语句,没有RETURN语句会报错,但如果没有放在函数体的最后也不会报错,但是不建议。如果函数体只有一句话,则可以省略BEGIN END;
调用语法
SELECT 函数名(参数列表)
查看函数
SHOW CREATE FUNCTION 函数名;
删除函数
DROP FUNCTION 函数名;
示例:
DELIMITER $
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)$
如果不能执行,可能要先执行下面这一条,重启后会失效
set global log_bin_trust_function_creators=TRUE;
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选则一条取执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
分支结构
- if函数
语法结构:
IF(表达式1,表达式2,表达式3)
如果表达式1成立,则返回表达式2的值,否则返回表达式3的值。
- case结构
情况1:类似Java中的switch语句,一般用于实现等值判断
语法结构:
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1
WHEN 要判断的值 THEN 返回的值2或语句2
...
ELSE 返回的值n或语句n
END CASE;
情况2:类似Java中的多重IF语句,一般用于实现区间判断
语法结构:
CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1
WHEN 要判断的条件2 THEN 返回的值2或语句2
...
ELSE 返回的值n或语句n
END CASE;
特点:
- 可以作为表达式,嵌套在其他语句中使用,可以放在任何地方。如果作为独立的语句去使用,只能放在BEGIN END中。
- 如果WHEN中的值满足或条件成立,则执行对应THEN后面的语句,并且结束CASE,如果都不满足,则执行ELSE中的语句或值。
- ELSE可以省略,如果ELSE省略了,并且所有WHEN条件都不满足,则返回NULL。
- if结构
功能:实现多重分支
语法结构:
IF 条件1 THEN 语句1;
ELSE IF 条件2 THEN 语句2;
...
[ELSE 语句n;]
END IF;
应用在BEGIN END中。
循环结构
应用在BEGIN END中。
iterate类似continue,结束本次循环,继续下一次循环
leave类似break,结束当前所在循环
- while
语法结构:
[标签:] WHILE 循环条件 do
循环体;
END WHILE [标签];
- loop
可以用来模拟简单的死循环。
语法结构:
[标签:] LOOP
循环体;
END LOOP [标签];
- repeat
语法结构:
[标签:] REPEAT
循环体;
UNTIL 结束循环的条件
END REPEAT [标签];