0xCAFEBABE

talk is cheap show me the code

0%

mysql速查

数据库是web应用的基石

相关概念

  • DB 数据库 数据有组织地存储
  • DBMS 数据库管理系统 例如 MySql DB2 Oracle SqlServer…
  • SQL 结构化查询语言 专门用于与数据库通信地语言

安装

详见官网

配置文件一览

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\Program Files\MySQL\mysql-5.7.24-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files\MySQL\mysql-5.7.24-winx64\data
# 允许最大连接数
# max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 客户端字符集
character-set-client=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_connections=1000
max_user_connections=500
# 等待超时时间
wait_timeout=31536000
# 非活跃超时
interactive_timeout=31536000

启动

通过命令行使用服务端

1
mysql -h localhost -P 3306 -u root -p

MySql服务端命令

1
2
3
4
5
6
7
show databases; -- 显示所有数据库
use xxx; -- 打开某个数据库
show tables; -- 显示某个数据库的所有表
show tables from xxx; -- 显示某个数据库的所有表
select database(); -- 显示当前所在的数据库
desc xxx; -- 查看表结构
select version(); -- 查看当前数据库版本

查询(DQL)

查询语句 在业务中占据了很大的比重

基础查询

以select 为基础

特点:

  • 查询列表可以是表中的字段 常量值 表达式 函数
  • 查询结果是一个虚拟的表格
  • 查询表

    1
    2
    select `字段1`, 字段2 from 表名 -- 查询某个表的指定字段 `` 可对关键字进行转义
    select * from table -- 查询 所有字段 * 表示所有
  • 查询常量

    1
    2
    select 99; -- 查询常量值
    select 'Hello Mysql';
  • 查询表达式

    1
    select 99 / 10; -- 查询表达式
  • 查询函数

    1
    2
    select version(); -- mysql 版本
    select database(); # 当前正在使用的数据库
  • 字段别名

    1
    2
    select version() as 版本号; -- as 可省略
    select version() as "out" -- 当别名有歧义时需要加引号
  • 字段去重

    1
    select distinct f1 from table
  • + 操作符

    当两个操作数都为数字型时 进行加法运算

    其中一方为字符型时 试图将该字符型转换为数字 成功则执行加法运算 如果转换失败 将字符型转换为0

    如果其中一方为null 则结果为null

条件查询

查询加条件

模糊查询: like betwee and in is null

  • 条件运算符

    > < = <> <= >= !=

    1
    select * from t_employee where dept_id<>1
  • 逻辑运算符

    and or not

    1
    2
    select * from t_emplyee where dept_id = 1 and salary >1000 or id = 1 not did = 2
    select * from t_emplyee where not(dept_id = 1 and salary >1000 or id = 1 not did = 2)
  • 模糊查询

    like 模糊匹配 可判断字符型和数值型

    between and 区间

    in 在指定集合内

    is null 为空

    is not null 不为空

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select * from t_employee where name like '%jack%' -- 模糊匹配 % 匹配0个或多个任意字符 
    select * from t_employee where name like '__jack' -- _ 匹配一个任意字符
    select * from t_employee where name like '_$_jack' escape '$' # 将指定字符作为转义字符

    select * from t_employee where salary between 10000 and 2000

    select * from t_employee where name in('jack', 'ma');

    select * from t_employee where name is not null;
  • 安全等于

    <=> 相比于 is null <=> 不仅可以判断 null 还可以判断其他类型

    null只能用is null / is not null 来判断

    1
    seelct * from t_employee where name <=> null;

排序查询

对查询结果最后进行排序

  • order by 列名

    asc 升序(默认)

    desc 降序

    1
    2
    3
    select * from t_employee order by salary desc # 单个字段排序
    select *,salary * rate as total from t_employee order by total asc # 虚拟字段排序
    select * from t_employee order by salary asc,id desc # 多个字段排序 主, 从

分组查询

通过指定列对数据进行分组

  • 基础分组

    1
    select count(*), dept_id from t_employee group by dept_id;
  • 分组前筛选

    数据源为表

    1
    select count(*), dept_id from t_employee where salary>1000 group by dept_id; # 在分组之前进行筛选
  • 分组后筛选

    数据源为结果集

    1
    select dept_id from t_employee group by dept_id having count(*)>10;
  • 分组混合筛选

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT
    avg( salary ),
    dept_id
    FROM
    t_employee
    WHERE
    salary > 2000
    GROUP BY
    dept_id
    HAVING
    count(*) > 1 # 分组函数一定放在分组后筛选
  • 函数分组

    按函数进行分组

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    length( NAME ) AS len
    FROM
    t_employee
    GROUP BY
    len
    HAVING
    len > 4
  • 多字段分组

    1
    select dept_id, job_id, count(*) from t_employee order by dept_id, job_id
  • 分组排序

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT
    dept_id,
    avg( salary ) avg
    FROM
    t_employee
    GROUP BY
    dept_id
    HAVING
    avg > 1000
    ORDER BY
    avg DESC;

连接查询

多表连接查询

sql92 支持 内连接(等值 非等值 自连接)

sql99 支持 内连接 外连接(左外 右外 全外(mysql不支持)) 交叉连接

  • 笛卡尔积

    1
    2
    # 设A 表有m行 B表有n行 则查询结果总行数 m*n 解决方法: 等值连接
    select a.name, b.name from t_employee a, t_department b;
  • 等值连接

    多表等值连接的结果集 为多表的交集

    可搭配 分组 排序 筛选

    1
    2
    #如果起了别名 就不能用原来的名字了
    select a.name, b.name from t_employee a, t_department b where a.dept_id=b.id;
  • 非等值连接

    1
    select a.name, b.name from t_employee a, t_employee b where a.dept_id > b.id;
  • 自连接

    自己与自己连接

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    a.NAME,
    b.NAME
    FROM
    t_employee a,
    t_employee b
    WHERE
    a.salary > b.salary
  • sql99 内连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    SELECT
    count(*),
    job_id
    FROM
    t_employee a
    INNER JOIN t_department b ON a.dept_id = b.id

    GROUP BY
    job_id

    # 多表连接
    SELECT
    count(*),
    job_id
    FROM
    t_employee a
    INNER JOIN t_department b ON a.dept_id = b.id
    INNER JOIN t_jobs c on a.job_id = c.id
    GROUP BY
    job_id
  • 外连接

    外连接查询结果为主表中的所有记录

    如果从表中有和它匹配的则显示匹配值

    如果没有显示null

    外连接查询结果=内连接结果+主表中有儿从表没有的记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    # 左外连接 左表为主表
    SELECT
    a.NAME,
    b.NAME
    FROM
    t_employee a
    LEFT OUTER JOIN t_jobs b ON a.job_id = b.id
    # 右连接 右表为主表
    SELECT
    a.NAME,
    b.NAME
    FROM
    t_employee a
    RIGHT OUTER JOIN t_jobs b ON a.job_id = b.id

    # 全外连接 结果集=内连接+左外连接+右外连接(mysql不支持)
    SELECT
    a.NAME,
    b.NAME
    FROM
    t_employee a
    FULL OUTER JOIN t_jobs b ON a.job_id = b.id
  • 交叉连接(笛卡尔积)

    1
    2
    3
    4
    5
    6
    SELECT
    a.*,
    b.*
    FROM
    t_employee a
    CROSS JOIN t_jobs b

子查询

嵌套在其他查询内的查询 总是优先于主查询执行

  • 标量子查询

    1
    2
    3
    4
    5
    SELECT NAME 
    FROM
    t_employee
    WHERE
    dept_id = ( SELECT id FROM t_department WHERE id = 1 ); # 子查询结果为标量(一个值)
  • 多行单列子查询

    1
    2
    3
    4
    5
    6
    7
    8
    # IN 在结果集中
    # ANY/SOME 匹配结集中中的的任意一个
    # ALL 匹配结果集中的所有
    SELECT NAME
    FROM
    t_employee
    WHERE
    salary > ANY ( SELECT salary FROM t_employee WHERE job_id = 1 )
  • 多列查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT NAME 
    FROM
    t_employee
    WHERE
    ( id, job_id ) = (
    SELECT
    id,
    job_id
    FROM
    t_employee
    WHERE
    id = 1
    );
  • select子查询

    1
    2
    3
    4
    5
    6
    # 仅支持标量查询
    SELECT
    a.NAME,
    ( SELECT b.NAME FROM t_department b WHERE a.dept_id = b.id )
    FROM
    t_employee a
  • from子查询

    1
    2
    3
    select * from (
    select avg(salary) "avg", dept_id from t_employee GROUP BY dept_id
    ) tmp # 必须起别名
  • exists相关子查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT
    name
    FROM
    t_department a
    WHERE
    # 存在结果集返回true 否则返回false
    EXISTS (
    # 子查询在主查询后执行
    SELECT
    name
    FROM
    t_employee b
    WHERE
    b.dept_id = a.id
    );

分页查询

  • limit

    1
    select * from t_employee limit 0,5 # 起始索引(默认省略为0),记录数

联合查询

将多个查询语句的结果合并成一个结果集

  • union

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 各个查询语句的结果集必须有相同的列
    # 默认去除重复行
    # UNION ALL 不去重
    SELECT NAME
    FROM
    t_employee
    WHERE
    dept_id = 1 UNION
    SELECT NAME
    FROM
    t_department
    WHERE
    id = 2

修改(DML)

插入

  • insert into

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 插入所有列 与表中的列一一对应
    INSERT INTO t_employee values(31, "aa", 2000, null, null, null);
    # 插入指定列
    INSERT INTO t_employee(name, salary)VALUES("yong", 28500)
    # 插入指定列 方法2
    INSERT INTO t_employee set name="hehe", salary = 1213453
    # 多行插入
    INSERT INTO t_employee
    VALUES
    ( 34, "aa", 2000, NULL, NULL, NULL ),
    ( 35, "aa", 2000, NULL, NULL, NULL ),
    ( 36, "aa", 2000, NULL, NULL, NULL )
    # 子查询插入
    INSERT INTO t_employee(name, salary)
    SELECT name, salary FROM t_employee WHERE id = 1

更新

  • 更新指定列

    1
    UPDATE t_employee set name = '勇' where name = 'yong'
  • 表连接更新

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 内连接
    UPDATE t_employee a JOIN t_jobs b on a.job_id = b.id
    SET b.description = '钓鱼的'
    WHERE a.name = '勇'

    # 外连接
    UPDATE t_employee a LEFT JOIN t_jobs b on a.job_id = b.id
    set a.job_id = 1
    WHERE b.id is NULL

删除

  • 单表删除

    1
    DELETE FROM t_employee where id = 33;
  • 多表连接删除

    1
    2
    3
    4
    DELETE a # 要删除的表
    FROM t_employee a LEFT JOIN t_department b
    ON a.dept_id = b.id
    WHERE b.id is NULL
  • 清空表

    自增长列将从1开始 (delete 从断点开始)

    效率比delete高

    无返回值 (delete返回受影响的行数)

    不能回滚事务 (delete 可回滚)

    1
    TRUNCATE TABLE t_jobs

DDL

数据定义语言

数据库

1
2
3
4
5
6
# 创建数据库 如果该数据库不存在
CREATE DATABASE IF NOT EXISTS books;
# 修改字符集
ALTER DATABASE books CHARACTER SET gbk;
# 删除数据库 如果存在
DROP DATABASE IF EXISTS books;

  • 创建表

    1
    CREATE TABLE IF NOT EXISTS t_book ( id INT, NAME VARCHAR ( 20 ), author VARCHAR ( 20 ), price DOUBLE, pubdate DATE );
  • 修改表

    • 改变列 (名称,类型)

      1
      ALTER TABLE t_book CHANGE COLUMN pubdate pdate TIMESTAMP
    • 增加列

      1
      ALTER TABLE t_book ADD COLUMN cid int # [first|after 列名] 指定列的位置
    • 删除列

      1
      ALTER TABLE t_book DROP COLUMN cid
    • 修改列类型

      1
      ALTER TABLE t_book MODIFY COLUMN cid VARCHAR(10)
    • 重命名表

      1
      ALTER TABLE t_book RENAME TO book
    • 删除表

      1
      DROP TABLE IF EXISTS book
  • 复制表

    • 复制表结构

      1
      CREATE TABLE book_bak like t_book
    • 复制表结构+数据

      1
      CREATE TABLE book_bak2 SELECT * FROM t_book
    • 复制部分字段

      1
      CREATE TABLE book_bak3 SELECT id,name FROM t_book WHERE 0

数据类型

整型

在整型中 默认为有符号 可通过UNSIGNED关键字设置为无符号

1
CREATE TABLE t_int(i int UNSIGNED )

在插入的时候如果超出范围 则插入的是边界值

在不指定长度的情况下 默认长度为边界长度

可通过 ZEROFILL关键字 指定长度不足时零填充 (变为无符号)

1
CREATE TABLE t_int(i int(7) ZEROFILL)
  • tinyint 1字节
  • smallint 2字节
  • mediumint 3字节
  • int/integer 4字节
  • bigint 8字节

小数型

定点型要比浮点型精度高

M: 整数部分+小数部分

D: 小数部分 (超出位数会进行四舍五入)

  • 浮点型

    默认精度由插入的数值决定

    • float(M, D) (4字节)
    • double(M, D) (8字节)
  • 定点型

    精度确定 默认为 M=10 D =0

    • decimal(M, D)

字符型

  • char(N) 固定长度数组 0~255之间 当n省略时 长度为1

  • varchar(N) 可变长字符串 N在0~65535之间 N 不可省略 效率比char略低

  • enum(…) 枚举型 只能插入固定的几个值 不区分大小写

    1
    2
    CREATE TABLE t_enum (e ENUM('g'))
    INSERT INTO t_enum VALUES('G')
  • set(…) 集合类型 只能可插入多个指定元素

    1
    2
    CREATE TABLE t_set (e SET('a','b','c'))
    INSERT INTO t_set VALUES('a,b,c,d')
  • binary/varbinary 二进制类型

  • bit(N) 位类型 可将其他类型转换为二进位 N代表位数 0<N<=64

日期型

  • date (4字节) 日期
  • datetime (8字节) 日期时间
  • timestamp (4字节) 时间戳 可表示插入时间 最大值到2038年 会受到时区 语法格式 版本影响
  • time (3字节) 时间
  • year (1字节) 年

约束

一种限制 用于限制表中的数据 为保证数据的一致性

添加约束的时机

  • 创建表时
  • 修改表时

按分类

  • 列级约束(6大约束都支持) 但外键约束无效 mysql 不支持check约束

    1
    2
    3
    4
    5
    CREATE TABLE t_slavery (
    id INT UNSIGNED PRIMARY KEY,
    NAME VARCHAR ( 10 ) NOT NULL,
    gender CHAR CHECK ( gender = '男' OR gender = '女' ),
    jod_id INT REFERENCES t_jobs ( id ));
  • 表级约束(除非空 默认 唯一外 都支持)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE t_slavery (
    id INT ,
    name VARCHAR ( 10 ) NOT NULL UNIQUE,
    gender CHAR ,
    age int DEFAULT 20,
    job_id INT,
    # CONSTRAINT 约束名省略时默认为列名
    CONSTRAINT pk PRIMARY KEY(id),
    # 主表的关联列必须是一个key(主键或唯一列)
    CONSTRAINT fk_job_id FOREIGN KEY(job_id) REFERENCES t_jobs(id) ON DELETE CASCADE ON UPDATE SET NULL # 外键级联行为 删除 or 置空
    )

主键和唯一键的区别

  1. 主键不允许为null 唯一键允许为null
  2. 主键只能为1个 唯一键可以有多个

六大约束

  • NOT NULL 非空约束
  • DEFAULT 默认值
  • PRIMARY KEY 主键约束
  • UNIQUE 唯一约束
  • CHECK 检查约束 (mysql 不支持)
  • FOREIGN KEY 外键约束

修改约束

  • 添加约束

    1
    2
    3
    4
    # 列级约束
    ALTER TABLE t_slavery MODIFY age int UNSIGNED DEFAULT 18
    # 表级约束
    ALTER TABLE t_slavery ADD UNIQUE(age)
  • 删除约束

    1
    2
    3
    4
    5
    6
    # 列级约束
    ALTER TABLE t_slavery MODIFY age int;
    # 表级约束 主键 唯一 约必须通过删除索引的方式来删除约束
    ALTER TABLE t_slavery DROP INDEX age
    # 删除外键约束
    ALTER TABLE t_slavery DROP FOREIGN KEY fk_job_id

标识列

自增长列

当一个列标记为自增长列时 无需插入该列由系统自动标识

自增长列只允许出现在key(主键, 唯一, 外键)上

一个表只允许一个标识列

标识列只能出现在数值型上

可通过修改auto_increment_increment 和 auto_increment_offset 变量设置标识列的自增步长和起始偏移量(默认都为1)

  • 建表时添加

    1
    2
    3
    4
    CREATE TABLE t_identity(
    id int PRIMARY KEY auto_increment,
    name VARCHAR(10)
    )
  • 修改表时添加

    1
    ALTER TABLE t_employee MODIFY COLUMN id int UNIQUE auto_increment

事务

事务由一个或多个sql组成 组成一个整体

要么同时成功 要么同时失败

事务具有ACID特性

  • 原子性

    事务是一个不可再分割的工作单位

  • 一致性

    事务必须从数据库得一个一致性状态转换到另一个一致性状态

  • 隔离性

    事务不能被其他事务干扰

  • 持久性

    事务一旦提交 对数据库的改变是永久的

创建事务

  • 隐式事务

    默认一条sql就会开启一个事务 且自动提交事务(变量 autocommit 默认为ON)

    事务没有明显的开始或结束标记

    在autocommit为ON的情况下 每条insert update delete语句 都会开启事务 且自动提交

  • 显式事务

    需要手动指定开始和结束标记

    1
    2
    3
    4
    5
    6
    7
    # 开始事务
    START TRANSACTION;
    UPDATE t_account set balance = balance+500 WHERE id = 1;
    UPDATE t_account set balance = balance-500 WHERE id = 2;
    # ROLLBACK; # 回滚事务
    #提交事务
    COMMIT;

隔离级别

在并发环境下 容易出现一致性问题

  • 脏读 两个事务T1 T2 , T1 读取到了 T2 修改的但未提交的数据 此时T2发生回滚 T1 读到的数据就是无效的
  • 不可重复读 两个事务T1 T2 , T1读取到了一个字段 之后T2修改了该字段 然后T1再次读取 两次读取的数据不一致
  • 幻读 两个事务T1 T2, T1读取一个表 T2随后在该表插入了几行 之后T2再次读取 就会发现多出来几行

select @@tx_isolation 查看当前事务隔离级别

set session transaction isolation level 设置当前mysql连接的隔离级别

set global transaction isolation level 设置全局事务隔离级别

mysql支持4种隔离级别

  • READ UNCOMMITED 未提交读

    可以读取其他事务未提交的数据 会出现所有上述问题

  • READ COMMITED 已提交读

    可以读取其他事务未提交的数据 解决脏读问题

  • REPEATABLE_READ 可重复读

    在一个事务内 一条查询语句多次执行的结果相同 解决不可重复读

  • SERIALIZABLE 串行化

    每次只允许一个事务 解决所有问题 但效率低下

回滚点

听过设置回滚点 可将事务回滚至指定位置

1
2
3
4
5
6
START TRANSACTION;
UPDATE t_account set balance = balance+500 WHERE id = 1;
SAVEPOINT s1;
UPDATE t_account set balance = balance-500 WHERE id = 2;
ROLLBACK TO s1;
COMMIT;

视图

将一条sql查询的结果集封装为一个虚拟表 只保留查询逻辑 不保留查询结果

实现sql的重用

  • 创建视图

    1
    2
    3
    4
    5
    6
    CREATE VIEW myview AS SELECT
    a.NAME NAME,
    b.NAME job_name
    FROM
    t_employee a
    JOIN t_jobs b ON a.job_id = b.id
  • 使用视图

    1
    SELECT * FROM myview
  • 修改视图

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    # 新建或替换
    CREATE
    OR REPLACE VIEW myview AS SELECT
    a.NAME,
    b.id
    FROM
    t_employee a
    LEFT JOIN t_jobs b ON a.job_id = b.id
    # 修改视图
    ALTER VIEW myview
    AS
    SELECT
    a.NAME
    FROM
    t_employee a
    LEFT JOIN t_jobs b ON a.job_id = b.id
    WHERE b.id is NULL
  • 删除视图

    1
    DROP VIEW myview; # 可删除多个用,隔开
  • 查看视图

    1
    DESC myview;
  • 视图更新

    只能更新包含简单单表查询的视图

变量

系统变量

由系统提供的变量

  • 全局变量

    作用域: 对所有会话有效

    1
    2
    3
    4
    5
    6
    7
    8
    # 查看所有全局系统变量
    SHOW GLOBAL VARIABLES;
    # 筛选系统全局变量
    SHOW GLOBAL VARIABLES LIKE '%char%';
    # 查看指定变量值
    SELECT @@global.tx_isolation
    # 修改指定变量值
    SET @@global.tx_isolation = "REPEATABLE-READ"
  • 会话变量

    作用域: 对当前对话有效

    1
    2
    3
    4
    5
    6
    7
    8
    # 查看所有会话变量(SESSION可省略)
    SHOW SESSION GLOBAL VARIABLES;
    # 筛选会话变量
    SHOW SESSION GLOBAL VARIABLES LIKE '%char%';
    # 查看指定变量值 session 可省略
    SELECT @@session.autocommit;
    # 修改指定变量值
    SET @@autocommit=0;

自定义变量

  • 用户变量

    1
    2
    3
    4
    # 定义变量 or 为变量赋值
    SET @count=0;
    # 变量赋值
    SELECT MAX(salary) INTO @count FROM t_employee;
  • 局部变量

    1
    2
    3
    4
    5
    6
    7
    8
    # 只能定义在begin end 块中 且只能放在开头
    BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 2;
    DECLARE sum INT DEFAULT 0;
    SET sum=i+j;
    SELECT @SUM;
    END

存储过程

一组预编译好的sql语句 相当于批处理语句

可进行调用

提高代码的重用性

简化操作

减少编译次数和数据库连接次数 提高效率

创建存储过程

  • 无参数

    1
    2
    3
    4
    5
    6
    CREATE PROCEDURE inserts()
    BEGIN
    INSERT INTO t_jobs VALUES(null, "AS", "AAA");
    END

    CALL inserts();
  • 输入参数

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE PROCEDURE FIND(IN NAME VARCHAR(10))
    BEGIN
    DECLARE result VARCHAR(10) DEFAULT '';
    select name INTO @result from t_employee t WHERE t.NAME = NAME;
    SELECT @result;
    END
    # 调用存储过程
    CALL find('yong');
  • 输出参数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    DROP PROCEDURE IF EXISTS getSalary; # 删除存储过程 如果存在
    CREATE PROCEDURE getSalary(IN NAME VARCHAR(10), OUT salary INT)
    BEGIN
    SELECT t.salary INTO salary from t_employee t WHERE t.name = NAME;
    END


    CALL getSalary('yong', @salary);

    SELECT @salary;
  • inout 参数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 一个参数 同时带有输入 输出功能 
    CREATE PROCEDURE power(INOUT i INT)
    BEGIN
    SET i = i * i;
    END
    set @i = 10;
    # 必须传入已近存在的变量
    CALL power(@i);
    SELECT @i;

流程控制

判断

  • if

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 只能出现在BEGIN END块内
    CREATE FUNCTION getLevel(score int) RETURNS CHAR
    BEGIN
    IF score>=90 THEN RETURN 'A';
    ELSEIF score>=80 AND score<90 THEN RETURN 'B';
    ELSE RETURN 'C';
    END IF;
    END
    SELECT getLevel(99);

分支

  • case

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    # case后加语句时 必须放在begin end 块中
    DROP FUNCTION IF EXISTS getWeekName; # 删除函数 如果存在
    CREATE FUNCTION getWeekName(weekday int) RETURNS VARCHAR(10)
    BEGIN
    DECLARE result VARCHAR(10) DEFAULT '';
    CASE weekday
    WHEN 1 THEN set result = '星期一';
    WHEN 2 THEN set result = '星期二';
    WHEN 3 THEN set result = '星期三';
    WHEN 4 THEN set result = '星期四';
    WHEN 5 THEN set result = '星期五';
    WHEN 6 THEN set result = '星期六';
    WHEN 7 THEN set result = '星期日';
    ELSE SET result = '未知数';
    END CASE;
    RETURN result;
    END

    SELECT getWeekName(1);

循环

  • while

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 先判断后执行
    CREATE FUNCTION doWhile() RETURNS int
    BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    WHILE i<=100 DO
    SET sum = sum +i;
    SET i = i +1;
    END WHILE;
    return sum;
    END
  • repeat

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    # 先执行后判断
    CREATE FUNCTION doRepeat() RETURNS int
    BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    a:REPEAT
    SET i = i +1;
    IF i % 2 = 0 THEN ITERATE a; # 跳过当次循环 继续下一次循环 必须加标签
    END IF;
    IF i <= 100 THEN SET sum = sum +i;
    END IF;
    UNTIL i>100
    END REPEAT a;
    return sum;
    END

    SELECT doRepeat() a
  • loop

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    # loop 死循环
    CREATE FUNCTION doLoop() RETURNS INT
    BEGIN
    DECLARE i INT DEFAULT 1;
    a: LOOP
    SET i = i +1;

    IF i>1000 THEN
    # ITERATE a; 跳过当次循环 继续下一次循环
    LEAVE a; # 跳出标签循环 必须加标签
    END IF;
    END LOOP a;

    RETURN i;

    END

函数

函数必须有且仅有一个返回值

字符串函数

  • concat

    用于进行字符拼接

    1
    select concat('Hello', 'MySql');
  • ifnull

    将null值转换为指定值

    1
    select ifnull(null, 1);
  • isnull

    判断是否为null 如果为null 返回 1 否则返回0

    1
    select isnull(null);
  • length

    返回字符型所占的字节数

    1
    select length('I am a piece of shit');
  • substr

    截取字符 索引(从1开始) , 截取的长度

    1
    select substr('I am a piece of shit', 5, 2)
  • instr

    返回子串出现的索引 不存在返回0

    1
    select instr("safe and sound", 'sound');
  • upper

    转大写

    1
    select upper('a');
  • lower

    转小写

    1
    select lower("A");
  • trim

    去前后指定字符(默认为去空格)

    1
    2
    select trim('   my heart will go on    '); #去空格
    select trim('*' from '***********blabla***'); # 去指定字符
  • lpad

    左填充

    1
    select lpad('keep moving', 20, '*');
  • rpad

    右填充

    1
    select rpad('keep moving', 20, '*')

数学函数

  • round

    四舍五入

    1
    2
    select round(1.66); # 取整
    select round(6.66666, 3) # 保留小数位数
  • ceil

    向上取整

    1
    select ceil(6.6666666666); # 7
  • floor

    向下取整

    1
    select floor(6.66666666666); # 6
  • truncate

    截断小数位

    1
    select truncate(6.6666666666, 2); # 截断 保留2位小数
  • mod

    取余

    1
    select mod(10, 3) # a-a/b*a
  • rand 产生 0-1之间的随机数

日期函数

  • now

    获取当前的日期时间

    1
    select now();
  • curdate

    获取当前日期

    1
    select curdate();
  • curtime

    获取当前时间

    1
    select curtime();
  • year

    从时间中获取年 月 日 时 分 秒

    1
    select year(now());
  • str_to_date

    字符串转日期时间

    格式符 含义
    %Y 四位的年份
    %y 两位的年份
    %m 月份(01, 02, …)
    %c 月份(1, 2, …)
    %d 日(01,02, …)
    %H 小时(24)
    %h 小时(12)
    %i 分钟(00, 01, …, 59)
    %s 秒(00, 01, …, 59)
    1
    select str_to_date('1999-11-10', '%Y-%c-%d');
  • date_format

    日期格式化为字符串

    1
    select date_format(now(), '%Y年%c月%d日 %H时%i分%s秒')
  • datediff

    计算日期差值

    1
    select DATEDIFF(now(),"1999-11-10") as 存活天数

流程控制函数

  • if

    三元表达式

    1
    select if(10>5,'yes','no');
  • case表达式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
     SELECT
    `NAME`,
    salary,
    CASE ifnull( bonus_rate, 1 )
    WHEN 1.5 THEN salary * 1.5
    ELSE salary
    END AS 新工资
    FROM
    t_employee
    #-----------------------------
    SELECT
    `name`,
    CASE
    WHEN salary >= 10000 T 'A'
    ELSE 'B'
    END AS `LEVEL`
    FROM
    t_employee;

分组函数

  • sum max min count avg

    用于统计计算

    sun avg 一般只用于数值型

    max min count 可用于任意类型

    以上函数都忽略null值

    与分组函数一同查出的字段需要是group by的字段

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select sum(salary) 和, max(salary) 最大值, min(salary) 最小值, count(salary) 数量, 

    avg(salary) 平均 from t_employee

    select sum(distinct salary) from t_employee # 可以与distinct搭配去重

    select count(*) from t_employee; # 统计行数(匹配非null的列) 在MYISAM中 效率较高

    select count(1) from t_employee; # 等价于count(*) 生成一个虚拟列1

其他函数

  • user

    1
    select user(); # 查看当前登录用户名
  • password

    1
    select password("pass"); # 字符串加密
  • md5

    1
    select md5("pass"); # md5加密

自定义函数

1
2
3
4
5
6
7
8
9
# 定义函数
CREATE FUNCTION getEmps(id int) RETURNS VARCHAR(10) # 定义返回类型
BEGIN
DECLARE name VARCHAR(10) DEFAULT '';
SELECT t.name INTO name FROM t_employee t where t.id = id;
return name;
END
# 调用函数
select getEmps(1)