数据库系统（六）---MySQL语句及存储过程

一、DDL、DML、DCL常用语句 

1、DDL（Data Definition Language）数据库定义语言

（1）数据库模式定义

#创建数据库　　
create database if exsites db_name;

#选定数据库
use db_name;

#删除数据库
drop database if exists db_name;

#修改数据库
alter database db_name set ...;

#展示所创建的数据库
show databases;

#统计mysql中某个数据库中的表的数量
SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = 'mysql中数据库名称' GROUP BY table_schema; 

（2）表定义　　　　

 

#创建表
create table test_table
(
s_id int not null auto_increment,
s_name char(50) not null default "hanmei",
s_age int not null,
primary key(s_id),
index index_name(s_name)
);

#添加外键
alter table B add constraint `bfk` foreign key ('fk_column_name') references A('column_name') on delete no action on update no action;

#删除外键
alter table B drop foreign key `bfk`;

#删除表
drop table if exists test_table; 

#展示表结构
desc test_table;

#修改表结构的操作

　1、添加表字段

　alter table 表名 add 字段名 类型(值)

　eg：alter table user add name varchar(40);

　2、删除表字段

　alter table 表名 drop  字段名

　eg： alter table user drop name;

　3、字段名更名

　alter table 表名 rename 老字段名 to 新字段名

　eg：alter table user rename oldname to newname;

　4、更改字段类型

　alter table 表名 alter 字段 类型;

　eg：alter table user alter name varchar(50);

　5、修改表名

　alter table 表名 rename to 新表名;

　eg：alter table test001 rename to test002;

2、DML（data manipulation language）数据库操作语言

insert into test_table(s_age) values(18);

insert into test_table set s_age=19; #插入部分列值数据

#case...when 匹配条件
select s_name as name,s_sex
case
　　when  'f' then ‘女’
　　else '男'
end as sex
from test_table;

#使用内置函数
select count(*) from customers;
select max(cust_id) from customers;
select min(cust_id) from customers;
select sum(cust_id) from customers;
select avg(cust_id) from customers;

#比较运算符 
select * from customers where cust_id!=2; select * from customers where cust_id<>2; 

#逻辑运算符 
#---and 与 
select * from customers where cust_id>2 and cust_sex=1; 
#---or 或 
select * from customers where cust_id>2 or cust_sex=1; 

#两者之间 范围 

select * from customers where cust_id between 2 and 4; 

select * from customers where cust_id>=2 and cust_id<=4; 

#in 
select * from customers where cust_id in(2,4); 

select * from customers where cust_id=2 or cust_id=4; 

#子查询 
select * from stu_info where sno in(select sno from stu_score); 

#分组查询 

select ssex,count(*)from stu_info group by ssex; 

select saddress,ssex,count(*) from stu_info group by saddress,ssex; 

select saddress,ssex,count(*) from stu_info group by saddress,ssex with rollup; 

#having 筛选---过滤分组后的数据 

• HAVING 用于对汇总的 GROUP BY 结果进行过滤。

• HAVING 要求存在一个 GROUP BY 子句。

• WHERE 和 HAVING 可以在相同的查询中。

• HAVING vs WHERE

• WHERE 和 HAVING 都是用于过滤。

• HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。

select saddress,ssex ,count(*) from stu_info group by saddress,ssex having count(*)>1;

补充：

limit start,pageSize;
eg: SELECT * FROM table  as t order by t.id LIMIT 0,10;

//删除数据
DELETE FROM tb_courses_new;
DELETE FROM tb_courses WHERE course_id=4；

inert ...select...;  #INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。
eg:
INSERT INTO table2 SELECT * FROM table1; #从一个表中复制所有的列插入到另一个已存在的表中

INSERT INTO table2(column_name(s)) SELECT column_name(s) FROM table1; #我们可以只复制希望的列插入到另一个已存在的表中

create table ... select...;//从一个表中复制数据，然后新建一个表，并复制到该表中
eg:
create table newTable select * from oldTable;

 

 2.1扩展函数

#---文本处理函数
LEFT()、RIGHT() 左边或者右边的字符
LOWER()、UPPER() 转换为小写或者大写
LTRIM()、RTIM() 去除左边或者右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值

#---时间日期函数
Now()    返回当前日期和时间

#---数值处理函数
AVG()    返回某列的平均值
COUNT()    返回某列的行数
MAX()    返回某列的最大值
MIN()    返回某列的最小值
SUM()    返回某列值之和
注：AVG() 会忽略 NULL 行

 2.2 DML之多表关联查询

#交叉连接（笛卡尔积）
select * from tb1 cross join tb2;

#---内连接
select * from stu_info inner join stu_score on stu_info.sno=stu_score.sno;
<=等价写法=>
#---注，此处如果没有where条件，查询结果相当于笛卡尔积
select * from stu_info,stu_score where stu_info.sno=stu_score.sno; 
#---左外连接 

select stu_info.sno,stu_info.sname,stu_score.sscore from stu_info left join stu_score on stu_info.sno=stu_score.sno; 

#---右外连接 

select stu_info.sno,stu_info.sname,stu_score.sscore from stu_score right join stu_info on stu_score.sno=stu_info.sno; 

3、DCL（Data Control Language）数据库控制语言

安全与访问控制

-- 查看 mysql 数据库的使用者账号
select user from mysql.user;

-- 密码加密
select password(456);

-- 创建用户
create user 'zhangsan'@'localhost' identified by '123',
'lisi'@'localhost' identified by password '*531E182E2F72080AB0740FE2F2D68
9DBE0146E04';

-- 删除用户账号
drop user lisi@localhost;

-- 重命名
rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';

-- 修改密码
set password for 'wangwu'@'localhost'='*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119';

-- 设置权限
grant select n test1.customers o 'wangwu'@'localhost';

-- 创建两个用户
grant select,update on test1.customers to 'liming'@'localhost' identified by '123', 'huang'@'localhost' identified by '789';

--执行所有数据库操作的权限
grant all on test1.* to 'wangwu'@'localhost';

-- 添加用户的权限
grant create user on *.*to 'wangwu'@'localhost';

-- 权限转移
grant select,update on test1.customers to 'zhou'@'localhost' identified by '123' with grant option;

-- 权限撤回
revoke select on test1.customers from 'zhou'@'localhost';

 4、sql高级及调优

（1）索引

#---创建索引
create index index_name on table(fild1,filed2)
#---创建唯一索引
create unique index index_name on 学生基本情况(姓名) 

 （2）引擎（mySql）

SHOW ENGINES  查看当前数据库支持的引擎
alter table test engine=innodb;  更改数据库表的引擎
show create table test; 查看创建表格的引擎类别

 （3）并发场景下应用

　 1）排它锁（for update）

　　   当一个事务加入排他锁后，不允许其他事务加共享锁或者排它锁读取，更加不允许其他事务修改加锁的行。

select * from t for update 会等待行锁释放之后，返回查询结果。
select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

　   特点：

• 事务之间不允许其它排他锁或共享锁读取，修改更不允许
• 所有事务中有一个排他锁执行 commit 之后，其它事务才可执行

　 2）共享锁

• 允许其它事务也增加共享锁读取
• 不允许其它事务增加排他锁 (for update)
• 当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁

start TRANSACTION
SELECT * from t LOCK IN SHARE MODE

 

二、存储过程

  1、存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的 SQL 语句（如 CREATE、UPDATE 和SELECT 等语句）和过程式 SQL 语句（如 IF…THEN…ELSE 控制结构语句）组成。创建的存储过程保存在数据库的数据字典中。

　　使用存储过程通常具有以下优势：

　　1） 可增强 SQL 语言的功能和灵活性；

　　2） 良好的封装性；

　　3） 高性能；

　　4） 可减少网络流量；

　　5） 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性。

 

  2、创建存储过程

　  2.1）DELIMITER 命令

　　  DELIMITER 命令将 MySQL 语句的结束标志临时修改为其他符号，从而使得 MySQL 服务器可以完整地处理存储过程体中所有的 SQL 语句，而后可通过 DELIMITER 命令再将 MySQL 语句的结束标志改回为 MySQL 的默认结束标志，即分号（；）。

　　  DELIMITER 命令的语法格式： DELIMITER $$

　

     2.2）创建存储过程

　　  在 MySQL 中，是使用 CREATE PROCEDURE 语句来创建存储过程，其常用的语法格式是： CREATE PROCEDURE sp_name（[proc_parameter[，…]]） Routine_body  

　　 　  "sp_name" 用于指定存储过程的名称，且默认在当前数据库中创建；

　　 　  "proc_parameter" 用于指定存储过程的参数列表；

　　 　  "routine_body" 表示存储过程的主体部分，也称为存储过程体。

 　         其中，语法项“proc_parameter”的语法格式是： [IN|OUT|INOUT] param_name type

　　　　　　IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；
　　　　　　OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；
　　　　　　INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）；

　  2.3）存储过程体

　  　　局部变量：在存储过程体中可以声明局部变量，用来存储存储过程体中的临时结果。在 MySQL 中，可以使用 DECLARE 语句来声明局部变量，并且同时还可以对该局部变量赋予一个初始值，其使用的语法格式是： DECLARE var_name[，…] type [DEFAULT value]

其中，

　　  1）"var_name" 用于指定局部变量的名称；

　　  2）"type" 用于声明局部变量的数据类型；

　　  3） DEFAULT 子句用于为局部变量指定一个默认值，若没有指定，则默认为 NULL。 需要注意的事项如下：

　　　　  1） 局部变量只能在存储过程体的 BEGIN…END 语句块中声明。

　　　　  2） 局部变量必须在存储过程体的开头处声明。

　　　　  3） 局部变量的作用范围仅限于声明它的 BEGIN…END 语句块，其他语句块中的语句不可以使用它。

　　　　  4） 局部变量不同于用户变量，两者的区别是：

　　        　　  局部变量声明时，在其前面没有使用@符号，只能被声明它的 BEGIN…END 语句块中的语句所使用；

　　　　　　   用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。

 

　　  4)  SET 语句   在 MySQL 中，可以使用 SET 语句为局部变量赋值，语法格式是： SET var_name=expr[，var_name=expr] …

　　  5)  SELECT…INTO 语句   在 MySQL 中，可以使用 SELECT…INTO 语句把选定列的值直接存储到局部变量中，语法格式是： SELECT col_name[,…] INTO var_name[,…] table_expr   其中：

　　　　“col_name”用于指定列名；

　　　　“var_name”用于指定要赋值的变量名；

　　　　“table_expr”表示 SELECT 语句中的 FROM 子句及后面的语法部分。

　　          注意：存储过程体中的 SELECT…INTO 语句返回的结果集只能有一行数据。

　　  6）流程控制语句

　　　　在 MySQL 中，可以在存储过程体中，使用条件判断语句和循环语句这样两类用于控制语句流程的过程式 SQL 语句。

　　  　　（1） 条件判断语句 常用的条件判断语句有 IF…THEN…ELSE 语句和 CASE 语句；

　　　　  （2） 循环语句 常用的循环语句有 WHILE 语句、REPEAR 语句和 LOOP 语句。此外，循环语句中还可以使用 ITERATE 语句，但它只能出现在循环语句的 LOOP、REPEAT 和 WHILE 子句 中，用于表示退出当前循环，且重新开始一个循环。

eg：　　

 

select...into...

mysql> delimiter $$
mysql> CREATE PROCEDURE proc_add_stu(
　　 -> IN sNo INTEGER,
　　 -> OUT sid int
　　 -> )

mysql> BEGIN #存储过程开始
　　 -> insert into student(s_no) values(sNo);
　　 -> SELECT LAST_INSERT_ID() into sid;  #将选定列的值直接存储到局部变量中
　　 -> END $$ #存储过程结束

mysql> delimiter;　　#将语句的结束符号恢复为分号

mysql> call pro_add_stu('0001'); 

 

in输入参数（默认，可省略不写）

mysql> delimiter $$
mysql> create procedure in_proce(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=0; #局部变量赋值（begin...和end之间）
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1; #全局变量@p_in赋值
 
mysql> call in_param(@p_in); #将全局变量@p_in的值作为参数传递给局部变量p_in
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    0 |
+------+
 
mysql> select @p_in; #输出全局变量@p_in的结果
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_id 的值，因为前者为局部变量、后者为全局变量。

 

out输出参数

mysql> delimiter //
mysql> create procedure out_proce(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_proce(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null

+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out; #输出全局变量（用户变量）结果
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#调用了out_proce存储过程，输出参数，改变了p_out变量的值

 

inout输入参数(尽量少用)

mysql> delimiter $$
mysql> create procedure inout_proce(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_proce(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

 

变量作用域

　　内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //
mysql > DELIMITER ;

 

条件语句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

 

循环语句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

   

　2.5） 删除存储过程

　　　　DROP PROCEDURE[IF EXISTS] sp_name 其中，“sp_name”用于指定要删除的存储过程的名称。

 

   2.4）游标

　　 游标是一个被 SELECT 语句检索出来的结果集。即，游标可以遍历返回的多行结果。

　　在 MySQL 中，使用游标的具体步骤如下：

　　（1） 声明游标 DECLARE cursor_name CURSOR FOR select_statement 其中，

　　　　“cursor_name”用于指定要创建的游标的名称，其命名规则与表名相同；

　　　　“select_statement”用于指定一个 SELECT 语句，其会返回一行或 多行的数据，且需注意此处的 SELECT 语句不能有 INTO 子句。

　　（2） 打开游标 OPEN cursor_name   其中，“cursor_name”用于指定要打开的游标。

　　（3） 读取数据 FETCH cursor_name INTO var_name[,var_name]…   其中，“cursor_name”用于指定已打开的游标；语法项“var_name”用于指定存放数据的变量名。

　　（4） 关闭游标 CLOSE cursor_name 其中，语法项“cursor_name”用于要关闭的游标。

       （5）在使用游标过程中，需要注意以下几点：

　　　　1） 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。

　　　　2） 在存储过程或存储函数中可以定义多个游标，但是在一个 BEGIN…END 语 句块中每一个游标的名字必须是唯一的。

　　　　3） 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。 模块四 调用存储过程 CALL sp_name[parameter[,…]] CALL sp_name[()] 在此语法格式中：

　　　　　　 语法项“sp_name”用于指定被调用的存储过程的名称。如果要调用某个特 定数据库的存储过程，则需要在前面加上该数据库的名称。

　　　　　　 语法项“parameter”用于指定调用存储过程所要使用的参数。调用语句中参数的个数必须等于存储过程的参数个数。

　　　　当调用没有参数的存储过程时，使用 CALL sp_name()语句与使用 CALL sp_name 语句是相同的。

 eg:

create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
    
end;

 

 

三、存储函数

  1、存储函数和存储过程的区别：

　　（1） 存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。

　　（2） 可以直接对存储函数进行调用，且不需要使用 CALL 语句；而对存储过程 的调用，需要使用 CALL 语句。

　　（3） 存储函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。

 

  2、创建存储函数 CREATE FUNCTION sp_name([func_parameter[，…]]) RETURNS type routine_body 其中，语法项“func_parameter”的语法格式是： param_name type

　　　　 在此语法格式中：

　　　　（1） 语法项“sp_name”用于指定存储函数的名称，需注意，存储函数不能与存 储过程具有相同的名字。

　　　　（2） 语法项“func_parameter”用于指定存储函数的参数，这里的参数只有名称 和类型，不能指定关键字“IN”“OUT”和“INOUT”。

　　　　（3） RETURNS 子句用于声明存储函数返回值的数据类型，其中 type 用于指定 返回值的数据类型。

　　　　（4） 语法项“routine_body”用于指定存储函数的主体部分，也称为存储函数体。 所有在存储过程中使用的 SQL 语句在存储函数中同样也适用，包括前面所介绍的局部变量、SET 语句，流程控制语句、游标等。但是，存储函数体中还必须包含一个 RETURN value 语句，其中 value 用于指定存储函数的返回值。

 eg:

#删除已经存在的存储函数
DROP FUNCTION IF EXISTS func_stu;

#创建存储函数(声明返回类型为varChar(50))
CREATE FUNCTION func_stu(in_id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE o_name VARCHAR(50); #声明局部变量
    SELECT name INTO o_name FROM tb_stu WHERE id = in_id; #tb_stu指事先创建好的数据库
    RETURN o_name;
END;

 

 

   3、调用存储函数

　　成功创建存储函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 对其进行调用，语法格式是： SELECT sp_name([func_parameter[，…]])

 eg:

SELECT func_stu(1);

 

    4、删除存储函数

　　在 MySQL 中，可以使用 DROP FUNCTION 语句来实现，语法格式： DROP FUNCTION[IF EXISTS] sp_name     其中，语法项“sp_name”指定要删除的存储函数的名称。

eg:

DROP FUNCTION IF EXISTS func_stu;

 

    5、修改存储函数

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

 

感谢阅读，如需转载，请注明出处，谢谢！https://www.cnblogs.com/huyangshu-fs/p/11669708.html