数据库基础

数据库配置和管理

数据库配置文件，可以修改端口号，存储位置，连接数，数据库引擎等等信息。

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

语法规范

1. 不区分大小写，但建议关键字大写，表名字段名小写。
2. 每条命令用分号结尾 \g也行
3. 每条命令可以换行或缩进
4. 注释
   1. 单行注释： #注释内容或者-- 注释内容 -- 后面有一个空格
   2. 多行注释/注释内容/

SQL的语言分类

1. DQL（Data Query Language）：数据查询语言select
2. DML(Data Manipulate Language):数据操作语言insert 、update、delete
3. DDL（Data Define Languge）：数据定义语言create、drop、alter
4. TCL（Transaction Control Language）：事务控制语言commit、rollback

DQL（Data Query Language）：数据查询语言

1. 查询语句结构

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】

2. 条件查询

   1. 条件表达式
      示例：salary>10000
      条件运算符：

      < >= <= = != <>

   2. 逻辑表达式
      示例：salary>10000 && salary<20000
      逻辑运算符：
      and（&&）:两个条件如果同时成立，结果为true，否则为false
      or(||)：两个条件只要有一个成立，结果为true，否则为false
      not(!)：如果条件成立，则not后为false，否则为true
      3.模糊查询
      示例：last_name like 'a%'

3. 常见函数

   1. 单行函数

      1. 字符函数
         concat拼接
         substr截取子串
         upper转换成大写
         lower转换成小写
         trim去前后指定的空格或字符，trim('字符' from '字符串')
         ltrim去左边空格
         rtrim去右边空格
         replace替换
         lpad左填充
         rpad右填充
         instr返回子串第一次出现的索引
         length 获取字节个数

      2. 数学函数
         round 四舍五入
         rand 随机数
         floor向下取整
         ceil向上取整
         mod取余
         truncate截断

      3. 日期函数
         now当前系统日期+时间
         curdate当前系统日期
         curtime当前系统时间
         str_to_date 将字符转换成日期
         date_format将日期转换成字符
         year将日期中的年
         month获取日期中的月
         monthname获取日期中的月的英文名字

      4. 流程控制函数
         if 处理双分支，类似JAVA的三元运算符
         case 字段或表达式
         when 常量值1 then 要返回的的值或执行的语句1
         when 常量值2 then 要返回的的值或执行的语句2
         else 默认值n或默认执行的语句n
         end;
         常量可以换位条件表达式

      5. 其他函数
         version版本
         database当前库
         user当前连接用户

   2. 分组函数（聚合函数，统计函数，组函数）
      sum 求和，只处理数字类型
      avg 平均值，只处理数字类型
      max 最大值
      min 最小值
      count 计数
      以上分组函数都忽略值为null的记录
      和distinct配合使用，去重
      分组函数一起查询的字段只能是group by后面的字段，不能是其他字段

特点：
1、以上五个分组函数都忽略null值，除了count()
2、sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：字段、、常量值，一般放1，建议使用 count(*)

4. 分组查询
   分组前筛选条件使用where，分组后再次筛选使用having

5. 连接查询
   按年代可以分为：

• 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】

6. 子查询（又称内查询）
   用在其他语句中的select语句

按出现的位置分类：

• 放select后面
• from后面
• where或having后面
• exists后面（相关子查询）
  按结果集的行列数不同：
• 标量子查询（结果集只有一行一列）
• 列子查询（结果集有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集一般为多行多列）

where或having后面
1. 标量子查询（结果集只有一行一列）
2. 列子查询（结果集有一列多行）
3. 行子查询（结果集有一行多列）

特点：子查询放小括号内，放条件右侧，标量子查询一般搭配单行操作符使用> < >= <= = <>，列子查询一般搭配着多行操作符使用in,any/some,all

7. 分也查询

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;

8. 联合查询（将多条查询语句的结果合并成一个结果）
   语法结构：

查询语句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):数据操作语言

1. 插入insert
   语法结构

INSERT INTO 表名 (列名1,...) VALUES (值1,...);
INSERT INTO 表名 (列名1,...) VALUES (值1,...),(值1,...),(值1,...),...;   -- 插入多条语句
INSERT INTO 表名 (列名1,...) SELECT语句; -- 将查询结果插入，可以用来表备份。
INSERT INTO 表名 SET 列名 1=值 1,列名 2=值 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
	筛选条件;

3. 删除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省略后，根据插入数值的精度来决定精度

• 字符型

  • 较短的文本：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');

• 日期型：

  • 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分为库的管理和表的管理

1. 库的管理
   创建：CREATE
   修改：ALTER
   删除：DROP
   创建库的语法：

CREATE DATABASE [IF NOT EXISTS]库名;

库的修改：

RENAME DATABASE 库名 TO 新库名;-- 在MySQL 5.1.23 之后版本，因为安全考虑，删掉了这一条命令。
ALTER DATABASE 库名 CHARACTER SET 编码;-- 修改字符集

库的删除：

DROP DATABASE [IF EXISTS] 库名;

2. 表的管理
   创建：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属性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建

1. 隐式事务：事务并没有明显的开启和结束的标记
   比如insert，update，delete语句

2. 显式事务：事务具有明显的开启和结束的标记
   前提：首先必须禁用自动提交功能。

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 视图名;-- 查看视图

好处：

1. 重用SQL语句
2. 简化复杂的SQL操作，不必知道它的查询细节
3. 保护数据，提高安全性

视图更新（不用）：
与表的增删改查语句相同，修改的是原始表的数据。所以不建议修改视图，应该设置视图权限为只读。
视图能否更新与视图中查询语句的定义有关系，以下类型的视图是不能更新的：

• 含有分组函数，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里面或外面

1. 声明并初始化
   赋值操作符: =或:=

SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;-- SELECT赋值时不能去掉冒号

2. 赋值或更新用户变量的值
   方式一：通过SET或SELECT

SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;-- SELECT赋值时不能去掉冒号

方式一：通过SELECT INTO把查询结果赋值给变量

SELECT 字段 INTO @变量名 FROM 表;-- select count(*) into @count from 表名;

3. 使用（查看用户变量的值）

SELECT @用户变量名;

局部变量

仅仅在定义它的begin end中有效。

1. 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 初始值;

2. 赋值（赋值必须兼容定义时的类型）

SET 局部变量名=值;-- 注意这里不需要@符号
SET 局部变量名:=值;
SELECT @局部变量名:=值;-- 注意这里需要@符号

3. 使用

SELECT 变量名;

存储过程和函数

存储过程

类似于Java中的方法
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
好处：

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

创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

注意：

1. 参数列表包含三部分：参数模式 参数名 参数类型
   举例：IN stuname VARCHAR(20)
   参数模式：
   IN：该参数可以作为输入，也就是说该参数需要调用方传入值
   OUT：该参数可以作为输出，也就是说该参数可以作为返回值
   INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2. 如果存储过程体仅仅只有一句话，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;

流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选则一条取执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

分支结构

1. if函数
   语法结构：

IF(表达式1,表达式2,表达式3)

如果表达式1成立，则返回表达式2的值，否则返回表达式3的值。

2. 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。

3. if结构
   功能：实现多重分支
   语法结构：

IF 条件1 THEN 语句1;
ELSE IF 条件2 THEN 语句2;
...
[ELSE 语句n;]
END IF;

应用在BEGIN END中。

循环结构

应用在BEGIN END中。
iterate类似continue，结束本次循环，继续下一次循环
leave类似break，结束当前所在循环

1. while
   语法结构：

[标签:] WHILE 循环条件 do
	循环体;
END WHILE [标签];

2. loop
   可以用来模拟简单的死循环。
   语法结构：

[标签:] LOOP
	循环体;
END LOOP [标签];

3. repeat
   语法结构：

[标签:] REPEAT
	循环体;
UNTIL 结束循环的条件
END REPEAT [标签];