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

2019-11-18杂谈搜奇网31°c

A⁺ A^-

一、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;

（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)
);

#删除表
drop table if exists test_table; 

#展现表构造
desc test_table;

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

insert into test_table(s_age) values(18);

insert into test_table set s_age=19; #插进去部排列值数据

inert ...select...;

#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 tb1 cross join tb2;

#内衔接
#---左外衔接
select * from stu_info inner join stu_score on 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;
 
#比较运算符
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 挑选---过滤分组后的数据
select saddress,ssex ,count(*) from stu_info group by saddress,ssex having count(*)>1;

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';

二、存储历程 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 }