落叶枫林

记录每一天

MySQL常用语法

发表于 更新于 分类于
本文字数： 2.8k 阅读时长 ≈ 3 分钟

MySQL增删改查，多表查询，视图，存储过程，存储函数，游标。

DDL

表操作-修改

添加字段

1
alter table 表名 add 字段名 类型(长度) [约束];

修改数据类型

1
alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

1
alter table 表名 change 旧字段名 新字段名 类型(长度) [约束];

删除字段

1
alter table 表名 drop 字段名;

修改表名

1
alter table 表名 rename to 新表名;

约束

外键

1
constraint 外键名称 foreign key 外键字段名 references 主表(列名)
1
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);

索引

  1. 创建索引

    1
    create index index_name on table_name (index_col_name, ...);

  2. 查看索引

    1
    show index from table_name;

  3. 删除索引

    1
    drop index index_name on table_name;

视图

  1. 创建视图

    1
    create view view_name as select语句

  2. 查询

    1
    2
    3
    4
    # 查看视图语句
    show create view view_name;
    # 查看视图数据
    select * from view_name ...;

  3. 修改

    1
    2
    create [or replace] view view_name as select语句;
    alter view view_name as select语句;

  4. 删除

    1
    drop view [if exists] view_name ...;

存储过程

  1. 创建

    1
    2
    3
    4
    create procedure 存储过程名称([[in or out]参数列表])
    begin
     -- SQL语句
    end;

  2. 调用

    1
    call 名称 ([参数]);

  3. 删除

    1
    drop procedure [if exists] procedure_name ...;

参数

类型 含义 备注
IN 作为输入 默认
OUT 作为输出，返回值
INOUT 即可作为输入参数也可作为输出参数

变量

  1. 查看系统变量

    1
    2
    3
    SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
    SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
    SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

  2. 设置系统变量

    1
    2
    SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
    SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;

  3. 用户自定义变量赋值

    1
    2
    3
    4
    5
    SET @var_name = expr [, @var_name = expr] ... ;
    SET @var_name := expr [, @var_name := expr] ... ;

    SELECT @var_name := expr [, @var_name := expr] ... ;
    SELECT 字段名 INTO @var_name FROM 表名;

  4. 自定义变量使用

    1
    SELECT @var_name

局部变量

1
2
3
4
5
6
7
# 声明
declare 变量名 变量类型 [default ...];

# 赋值
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

条件/循环语句

1
2
3
4
5
6
7
if 条件 then
	...
elseif 条件2 then
	...
else
	...
end if;
1
2
3
while 条件 do
	-- SQL逻辑
end while;
1
2
3
4
5
-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
    SQL逻辑...;
    UNTIL 条件
END REPEAT;
1
2
3
4
5
6
[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

游标

  1. 声明游标

    1
    declare 游标名称 cursor for 查询语句;

  2. 打开游标

    1
    open 游标名称;

  3. 获取游标

    1
    fetch 游标 into 变量 [, 变量 ...];

  4. 关闭游标

    1
    close 游标名称;

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create procedure p8()
begin
    declare t_index char(6);
    declare price float;
    declare flag bool default true;
    declare cur_update cursor for
        (select goods.goodsno, goods.saleprice from goods join category c on c.categoryno = goods.categoryno where c.categoryno = 'cn001');
    declare continue handler for not found set flag := false;

    open cur_update;
    while flag do
        fetch cur_update into t_index, price;
        update goods set saleprice = price * 0.9 where goodsno = t_index;
    end while;
    close cur_update;
end;

MySQL游标的关闭是通过处理异常来关闭的。

1
declare continue handler for not found set flag := false;

触发器

触发器是与表相关的数据库对象，指在insert/update/delete之前before或之后after，触发并执行触发器中定义的SQL语句。

触发器类型 NEW 和 OLD
INSERT NEW 表示将要或者已经新增的数据
UPDATE OLD 表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE OLD 表示将要或者已经删除的数据

  1. 创建

    1
    2
    3
    4
    5
    6
    create trigger trigger_name
    before/after insert/update/delete
    on tb1_name for each row -- 行级触发器
    begin
    	-- SQL执行
    end;

  2. 查看

    1
    show triggers;

  3. 删除

    1
    drop trigger trigger_name;

示例

1
2
3
4
5
6
7
create trigger supplier_delete_trigger
    before delete on supplier for each row
begin
    delete from salebill where salebill.goodsno in
           (select g.goodsno from goods g join supplier s on g.supplierno = s.supplierno where s.supplierno = OLD.supplierno);
    delete from goods where goods.supplierno = OLD.supplierno;
end;