MySQl

最新推荐文章于 2023-02-02 13:52:42 发布

傻蛋日常呆

最新推荐文章于 2023-02-02 13:52:42 发布

阅读量564

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_64486093/article/details/124279640

版权

本文详细介绍了MySQL数据库的各类操作，包括数据库的创建、显示和删除，查询数据的多种方式如单表查询、子查询、连接查询，以及使用聚合函数、排序、分组等。还涉及索引、视图、触发器、常用函数、存储过程和函数的创建与使用，内容全面深入。

摘要由CSDN通过智能技术生成

第三章：数据库操作类型

1. 数据库简介

数据库类型是按照数据结构来组织、存储和管理数据的仓库

2. 显示所有数据库

Show databases

3. 创建数据库

Create database 数据库名

4. 删除数据库

Drop database 数据库名

SHOW DATABASES
CREATE DATABASE db_book;
DROP DATABASE db_book;

第五章：查询数据

1. 单表查询

1. 查询所有字段

SELECT * FROM 表名;

SELECT 字段1，字段2，字段3…FROM 表名;

2 查询指定字段

SELECT 字段1，字段2，字段3…FROM 表名；

3 Where条件查询

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 条件表达式;

4 带IN关键字查询(NOT)

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 字段 [NOT]IN(元素1、元素2、元素3…);

5 带BETWEEN AND 的范围查询

SELECT 字段1、字段2、字段3…FROM 表名 [NOT]BETWEEN　取值１　AND　取值２；

６带LIKE的模糊查询（%、_）

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 字段 [NOT]LIKE ‘字符串’;

7 空值查询

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 字段 IS [NOT]NULL;

8 带AND的多条件查询

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 条件表达式1 AND 条件表达式2 […AND条件表达式n]

9 带OR的多条件查询

SELECT 字段1，字段2，字段3…FROM 表名 WHERE 条件表达式1 OR 条件表达式2 […OR条件表达式n]

10. DISTINCT去重复查询

SELECT DISTINCT 字段名 FROM 表名;

11. 对查询结果排序（默认升序）

SELECT 字段1，字段2，字段3…FROM 表名 ORDER BY 属性名[ASC|DSEC]

12. GROUP BY 分组查询

GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

12.1：单独使用（毫无意义）

12.2：与GROPUP_CONACT()函数一起使用

12.3：与聚合函数一起使用

12.4：与HAVING一起使用(限制输出的结果)

12.5：与WITH ROLLUP一起使用(最后加入一个总和行)

13. LIMIT 分页查询

SELECT 字段1，字段2，字段3…FROM 表名 LIMIT 初始位置，记录数；

CREATE DATABASE db_book;
DROP DATABASE db_book;

SELECT * FROM t_student;
SELECT stuName,sex,age FROM t_student;
SELECT * FROM t_student WHERE age>20;
SELECT * FROM t_student WHERE age IN(23,27);
SELECT * FROM t_student WHERE age NOT IN(23,25);
SELECT * FROM t_student WHERE age BETWEEN 21 AND 23;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 23;
SELECT * FROM t_student WHERE stuName LIKE '%张三%';
SELECT * FROM t_student WHERE stuName NOT LIKE '%张三%';
SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;

SELECT * FROM t_student WHERE age>20 AND stuName='张三';
SELECT * FROM t_student WHERE age>20 OR stuName='张三';

SELECT DISTINCT gradeName FROM t_student;
SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)<4;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT * FROM t_student LIMIT 0,5;

2. 使用聚合函数查询

1. COUNT()函数

1.1 COUNT()函数用来统计记录的条数

1.2 与GROUP BY关键字一起用

2. SUM()函数

2.1 SUN()函数用来统计记录的条数

2.2 与GROUP BY关键字一起用

3. AVG()函数

3.1 AVG()函数用来统计记录的条数

3.2 与GROUP BY关键字一起用

4. MIN()函数

4.1 MIN()函数用来统计记录的条数

4.2 与GROUP BY关键字一起用

5. MAX()函数

5.1 MAX()函数用来统计记录的条数

5.2 与GROUP BY关键字一起用

SELECT * FROM t_grade;
SELECT stuName,COUNT(*) FROM t_grade;
SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;
SELECT stuName,SUM(score) FROM t_grade WHERE stuName='张三';
SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;
SELECT stuName,AVG(score) FROM t_grade WHERE stuName='张三';
SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;
SELECT stuName,MAX(score) FROM t_grade WHERE stuName='张三';
SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;
SELECT stuName,MIN(score) FROM t_grade WHERE stuName='张三';
SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

3. 连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取重要的数据

1. 内连接查询

内连接查询是一种常用的连接查询，内连接可以查询两个或两个以上的表

2. 外连接查询

外连接可以查出某一张的所有信息

SELECT 属性名列表 FROM 表名1 LEFT｜RIGHT　JION　表名２　ON　表名１.属性名１＝表名２.属性名２

2.1 左连接查询

可以查询出“表名１”的所有记录，而“表名２”中，只能查出匹配的记录

2.2 右连接查询

可以查询出“表名２”的所有记录，而“表名１”中，只能查出匹配的记录

3.3 多条件连接查询

AND　OR

SELECT * FROM t_book,t_booktype;
SELECT * FROM t_book,t_booktype WHERE t_book.`bookTypeId`=t_booktype.`id`;
SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.`bookTypeId`=t_bookType.`id`;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.`bookTypeId`=tby.`id`;
SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.`bookTypeId`=t_booktype.`id`;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.`bookTypeId`=tby.`id`;
SELECT * FROM t_book,t_booktype WHERE t_book.`bookTypeId`=t_booktype.`id` AND price>80;
SELECT * FROM t_book,t_booktype WHERE t_book.`bookTypeId`=t_booktype.`id` OR price>80;

4. 子查询

1. 带IN关键字的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中

2. 带比较运算符的子查询

子查询可以比较运算符

3. 带EXISTS关键字的子查询

假如子查询查询到记录，则进行外层查询，否则，不执行外层查询

4. 带ANY关键字的子查询

ANY关键字表示满足其中任一条件

5. 带ALL关键字的子查询

ALL关键字表示满足所有条件

SELECT * FROM t_book,t_booktype;
SELECT * FROM t_book WHERE id IN(SELECT id FROM t_bookType);
SELECT * FROM t_book WHERE id NOT IN(SELECT id FROM t_bookType);

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE id=1);
SELECT * FROM t_book WHERE EXISTS(SELECT * FROM t_bookType);
SELECT * FROM t_book WHERE NOT EXISTS(SELECT * FROM t_bookType);
SELECT * FROM t_book WHERE price>=ALL(SELECT price FROM t_pricelevel);
SELECT * FROM t_book WHERE price>=ANY(SELECT price FROM t_pricelevel);

5. 合并查询结果

1. UNION

使用UNION关键字时，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录

2. UNION All

使用UNION ALL，不会除掉系统的记录

6. 为表和字段取别名

1. 为表取别名

格式：表名表的别名

2. 为字段取别名

格式：属性名 [AS] 别名

SELECT id FROM t_book UNION SELECT id FROM t_bookType;
SELECT id FROM t_book UNION ALL SELECT id FROM t_bookType;

tb.bookName，tby.bookName
t_book tb   t_bookType tby

第六章：插入、更新和删除数据

1. 插入数据

1.1 给表的所有字段插入数据

格式:INSERT INTO 表名 VALUES(值1，值2.值3…值n):

1.2 给表的指定字段插入数据

格式:INSERT INTO 表名(属性1、属性2… 属性n) VALUES(值 1. 值 2.值 3… 值m);

1.3 同时插入多条记录

INSERT NTO 表名((属性列表) VALUES(取值列表1)(取值列表 2)(取值列表m):

2.更新数据

UPDATE 表名

SET 属性名1=取值 1、属性名 2=取值2.
属性名 n=取值n

WHERE 条件表达式:

3. 删除数据

DELETE FROM 表名 [WHERE 条件表达式]

INSERT INTO t_book VALUES(NULL,'倔强','111','小明','1');
INSERT INTO t_book(bookName,price) VALUES('崛起','20');

INSERT INTO t_book VALUES(NULL,'白起',123,'兄案件','2'),(NULL,'李白',111,'对的',3);

UPDATE t_book SET bookName='觉醒',price=119 WHERE id=6;
DELETE FROM t_book WHERE id=5;
DELETE FROM t_book WHERE bookName LIKE '白起';
DELETE FROM t_book WHERE id=8;
DELETE FROM t_book WHERE id=6;
INSERT INTO t_bookType VALUES(NULL,'生物类');

第七章：索引

1. 索引的引入

索引定义:索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度;类似于图书的目录，方便快速定位，寻找指定的内容:

2. 索引的优缺点优点:提高查询数据的速度;

缺点:创建和维护索引的时间增加了:

3. 索引实例

4. 索引分类

4.1 普通索引

这类索引可以创建在任何数据类型中:

4.2 唯一性索引

使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的

4.3 全文索引

使用FULLTEXT 参数可以设置，全文索引只能创建在CHAR，VARCHAR，TEXT类型的字段上。主要作用就是提高查询较大字符串类型的速度;只有MyISAM 引擎支持该索引，Mysql默认引擎不支持

4.4 单列索引

在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引

4.5 多列索引

多列索引是在表的多个字段上创建一个索引

4.6 空间索引

使用SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率:只有 MyISAM 引擎支持该索引，Mysal默认引擎不支持:

5. 创建索引

5.1 创建表的时候创建索引

CREATE TABLE 表名(属性名数据类型整性约条件]
属性名数据类型[完整性约束条件]。
属性名数据类型
UNIOUE|FULLTEXT|SPATIAL ]INDEX|KEY
[别名] (属性名1[(长度)][ASC|DESC)

);

1，创建普通索引2，创建唯一性索引3，创建全文索引4，创建单列索引5，创建多列索引6，创建空间索引

5.2 在已经存在的表上创建索引

CREATE[UNIQUE|FULLTEXT|SPATIAL JINDEX 索引名 ON 表名(属性名[(长度)1ASCDESCD:

5.3 用ALTER TABLE语句来创建索引

ALTER TABLE 表名ADD [UNIQUE| FULLTEXT SPATIAL ]INDEX
索引名(属性名【(长度)] [ASC|DESC]);

6. 删除索引

DROP INDEX 索引名 ON 表名:

CREATE TABLE t_user(id INT,
		    userName VARCHAR(20),
		    PASSWORD VARCHAR(20),
		    INDEX index_userName(userName)
		     );
CREATE TABLE t_user1(id INT,
		    userName VARCHAR(20),
		    PASSWORD VARCHAR(20),
		    UNIQUE  index_userName(userName)
	             );
CREATE TABLE t_user2(id INT,
		    userName VARCHAR(20),
		    PASSWORD VARCHAR(20),
		    INDEX index_userName_password(userName,PASSWORD)
		     );
CREATE TABLE t_user3(id INT,
		    userName VARCHAR(20),
		    PASSWORD VARCHAR(20),
		    UNIQUE  index_userName_password(userName,PASSWORD)
	             );
CREATE INDEX index_userName
     ON t_v(userName)
CREATE  INDEX index_userName_password
     ON t_v(userName,PASSWORD)
CREATE UNIQUE INDEX index_userName
     ON t_v2(userName)
CREATE UNIQUE INDEX index_userName_password
     ON t_v2(userName,PASSWORD)
CREATE FULLTEXT INDEX index_userName
     ON t_v3(userName)
CREATE  FULLTEXT INDEX index_userName_password
     ON t_v3(userName,PASSWORD)
 
ALTER TABLE t_v ADD INDEX
  index_user(userName)
ALTER TABLE t_v ADD INDEX
  index_user_pass(userName,PASSWORD)
ALTER TABLE t_v2 ADD UNIQUE INDEX
  index_user(userName)
ALTER TABLE t_v2 ADD UNIQUE INDEX
  index_user_pass(userName,PASSWORD)
ALTER TABLE t_v3 ADD FULLTEXT INDEX
  index_user(userName)
ALTER TABLE t_v3 ADD FULLTEXT INDEX
  index_user_pass(userName,PASSWORD)

DROP INDEX index_user_password ON t_v3;
DROP INDEX index_user ON t_v3;

第八章：视图

1. 视图的引入

1.视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。

2.数据库中只存放了视应的数据。

2. 视图的作用

1. 使操作简便化;

2. 增加数据的安全性:

3.提高表的逻辑独立性:

3. 创建视图

CREATE[ALGORITHM=(UNDEFIEND|MERGE| TEMPTABLE}]
VIEW 视图名[(属性清单)] AS SELECT 语句
[WITH[ CASCADED| LOCAL ] CHECK OPTION ]:

ALGORITHM 是可选参数，表示视图选择的算法:

“视图名”参数表示要创建的视图的名称;
“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与SELECT语句中查询的属性相同: SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中: WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内;

ALGORITHM 包括3个选项UNDEFINED、MERGE 和 TEMPTABLE。其中，UNDEFINED 选项表示MySOL将自动选择所要使用的算法:MERGE选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分:TEMPTABLE选项表示将视图的结果存入临时表，然后使用临时表执行语句;CASCADED是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值;LOCAL 表示更新视图时，要满足该视图本身的定义条件即可;

3.1 在单表上创建视图

3.2 在多表上创建视图

4. 查看视图

4.1 DESCRIBE 语句查看视图基本信息

4.2 SHOWTABLESTATUS语句查看视图基本信息

4.3 SHOWCREATE VIEW 语句查看视图详细信息

4.3 在views表中查看视图详细信息

5. 修改视图

5.1CREATEOR REPLACE VIEW 语句修改视图

CREATE OR REPLACE[ ALGORITHM =( UNDEFINED | MERGE |TEMPTABLE )]
VIEW 视图名[(属性清单 )] AS SELECT 语句
[WITH [ CASCADED LOCAL I CHECK OPTION ]:

5.2 ALTER 语修改视图

ALTER [ALGORITHM=UNDEFINEDMERGE|TEMPTABLE !
VIEW 视图名(属性清单)] AS SELECT 语句
[ WITH [ CASCADED| LOCAL ]CHECK OPTION ]:

6. 更新视图

更新裸图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

6.1插入(INSERT)

6.2更新(UPDATE)

6.3删除(DELETE)

7. 删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据;

DROP VIEW [IF EXISTS]视图名列表[RESTRICT|CASCADE]

CREATE VIEW v1 AS SELECT * FROM t_book;
CREATE VIEW v2(b,p) AS SELECT bookName,author FROM t_book;
SELECT * FROM v2;
CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_bookType;
SELECT * FROM v4;
CREATE VIEW v5 AS SELECT bookName,bookTypeName FROM t_book,t_bookType WHERE t_book.`bookTypeId`=t_bookType.`id`;
SELECT * FROM v5;
DESC  v5;
SHOW TABLE STATUS LIKE 'v5';
SHOW CREATE VIEW v5;
ALTER VIEW v4(b,p) AS SELECT bookName,price FROM t_book;
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t_bookType;
INSERT INTO v1 VALUES(NULL,)
UPDATE v1 SET bookName='',price=''  WHERE id=1;

第九章：触发器

1. 触发器的引入

触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE 语句和 DELETE 语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

2. 创建与使用触发器

2.1 创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名BEFORE|AFTER 触发事件
ON 表名FOR EACH ROW 执行语句

2.2 创建有多执器

CREATE TRIGGER 触发器名BEFORE|AFTER 触发事件
ON 表名 FOREACH ROW BEGIN
执行语句列表 END

3. 查看触发器

3.1 SHOW TRIGGERS 语句查看触发器信息

3.2 在 triggers 表中查看触发器信息

4. 删除触发器

DROP TRIGGER 触发器名

CREATE TRIGGER trig_book AFTER INSERT
    ON t_book FOR EACH ROW 
    UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_bookType.`id`
    
INSERT INTO t_book VALUES(NULL,'aaa',122,'ad',1);

DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE 
     ON t_book FOR EACH ROW
     BEGIN
     UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_bookType.id;
     INSERT INTO t_log VALUES(NULL,NOW(),'删除了一条数据');
     DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
     END
|
DELIMITER ;

DELETE FROM t_book WHERE id=5;

SHOW TRIGGERS;

DROP TRIGGER trig_book;
DROP TRIGGER trig_book2;

第十章：MySQL常用函数

1. 日期和时间函数

1.1 CURDATE()返回当前日期:

1.2 CURTIME()返回当前时间:

1.3 MONTH(d)返回日期d中的月份值，范围是1~12

1. 字符串函数

2.1 CHAR LENGTH(s) 计算字符s的字符数:

2.2 UPPER(S) 把所有字母变成大写字母:

2.3 LOWER(s) 把所有字母变成小写字母:

3. 数学函数

3.1 ABS(x) 求绝对值

3.2 SQRT(x) 求平方根

3.3 MOD(x.y) 求余

4. 加密函数

4.1 PASSWORD(str) 一般对用户的密码加密不可逆

4.2 MD5(str) 普通加密不可逆

4.3 ENCODE(str，pswd str) 加密函数，结果是一个二进制数，必须使用BLOB类型的字段来保存它:4，DECODE(crypt_str,pswd_str) 解密函数

CREATE DATABASE db_test;
SELECT CURDATE(),CURTIME(),MONTH(brithday) AS m FROM t_t;

SELECT userName,CHAR_LENGTH(userName),UPPER(userName),LOWER(userName) FROM t_t;

SELECT num,ABS(num) FROM t_t;
SELECT SQRT(4),MOD(9,2) FROM t_t;

INSERT INTO t_t VALUES(3,'2013-1-1','q11',1,PASSWORD('123456'));
INSERT INTO t_t VALUES(NULL,'2013-1-1','q',1,MD5('123456'));
INSERT INTO t_t VALUES(4,'2013-1-1','q',1,MD5('123456'),ENCODE('abc','aa'));
INSERT INTO t_t VALUES(5,'2010-2-22','m',-2,MD5('123456'),ENCODE('abb','a'));
SELECT DECODE(pp,'aa') FROM t_t WHERE id=4;

SELECT DECODE(pp,'a') FROM t_t WHERE id=5;
INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,PASSWORD(123456));
INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,MD5('123456'),ENCODE('abcd','aa'));
SELECT pp FROM t_t WHERE id=5;
SELECT DECODE(pp,'aa') FROM t_t WHERE id=5;

第十一章：存储过程和函数

1. 存储过程和函数的引入

存储过程和雨数是在数据库中定文一些SOL语句的集合，然后直接调用这些存储过程和的数来执行已经定义好的SQL语句-存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQI服务器中存储和执行的，可以减少客户端和服务器端的数据传输;

2. 创建存储过程和函数

2.1 创建存储过程

2.2 创建存储函数

2.3 变量的使用

1. 定义变量

2. 为变量赋值

2.4 游标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

1. 声明游标

DECLARE cursor name CURSOR FOR select statement;

2. 打开游标 OPEN cursor_name

3. 使用游标

FETCH cursor name INTO var name [,var name … ]:

4. 关闭游标

CLOSE cursor name;

2.5 流程控制的使用

存储讨程和承敬中可以使用流程控制来控制语句的执行。MvSOL中可以使用F语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。

1，IF 语句

IF search condition THEN statement list
[ ELSEIF search condition THEN statement list ]…[ ELSE statement list ] END IF

2，CASE 语句 CASE case value

WHEN when value THEN statement list[WHEN when value THEN statement list]…[ELSE statement list ] END CASE

3.LOOP，LEAVE语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP 语句本身没有停止循环的语句，必须是遇到LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下:[begin label: JLOOP
Statement list END LOOP [ end label ]
LEAVE 语句主要用于跳出循环控制。语法形式如下: LEAVE label

4，ITERATE语句

ITERATE 语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。基本语法: ITERATE label;

5，REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下:
[ begin_label: ] REPEAT
Statement list
UNTIL search_condition END REPEAT [ end label ]

6，WHILE 语句

[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [end label ]

3. 调用存储过程和函数

3.1 调用存储过程

CALL sp_name( [parameter[…])

3.2调用存储函数

fun_name( [parameter[…l)

4. 查看存储过程和函数

4.1 SHOWSTATUS 语句查看存储过程和数的状态

SHOW{PROCEDURE|FUNCTION)STATUS [ LIKE ‘pattern’]:

4.2 SHOWCREATE语句查看存储过程的函数的定义

SHOW CREATE{ PROCEDURE| FUNCTION } sp name

4.3 从information schemaRoutines表中查看存储过程和函数的信息

5. 修改存储过程和函数

ALTER( PROCEDURE|FUNCTION ) sp_name [ characteristic … ] characteristic:
( CONTAINS SOL I NO SQL I READS SOL DATA| MODIFIES SQL DATA) SOL SECURITY { DEFINER IINVOKER! COMMENT 'string"
其中，sp name 参数表示存储过程或函数的名称:characteristic 参数指定函数的特性。CONTAINS SQL 表示子程序包含SOL 语句，但不包含读或写数据的语句;NOSQL 表示子程序中不包含SQL 语句;READS SQL DATA表示子程序中包含数据的语句:MODIFIES SQLDATA表示子程序中包含写数据的语句。SQL SECURITYI DEFINERINVODER)指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行; INVODER 表示调用者可以执行。COMMENT*string’是注释信息。

6. 删除存储过程和函数

DROP{PROCEDURE|FUNCTION} sp_name;

DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
   READS SQL DATA
   BEGIN
     SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
   END
   &&
DELIMITER ;


CALL pro_book(1,@total);
CALL pro_book(2,@total);


DELIMITER &&
CREATE FUNCTION fun_book (bookId INT)
  RETURNS VARCHAR(20)
  BEGIN
   RETURN(SELECT bookName FROM t_book WHERE id=bookId);
  END
  &&
DELIMITER ;

SELECT fun_book(1);
SELECT fun_book(2);

DELIMITER &&
CREATE PROCEDURE pro_user()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     INSERT INTO t_user VALUES(NULL,a,b);
    END
    &&
DELIMITER ;    

DELIMITER &&
CREATE PROCEDURE pro_user2()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     SET a='java1234',b='3456';
     INSERT INTO t_user VALUES(NULL,a,b);
    END
    &&
DELIMITER ;

DELIMITER &&
CREATE PROCEDURE pro_user3()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;	
     INSERT INTO t_user VALUES(NULL,a,b);
    END
    &&
DELIMITER ;

CALL pro_user();
CALL pro_user2();
CALL pro_user3();

DELIMITER &&
CREATE PROCEDURE pro_user4()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;
     OPEN cur_t_user2;
     FETCH cur_t_user2 INTO a,b;
     INSERT INTO t_user VALUES(NULL,a,b);
     CLOSE cur_t_user2;
    END
    &&
DELIMITER ;


DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
    BEGIN
     SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
     IF @num>0 THEN UPDATE t_user SET userName='java1234' WHERE id=bookid;
     ELSE 
       INSERT INTO t_user VALUES(NULL,'2232','223q1');
       END IF;
    END
    &&
DELIMITER ;

DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
    BEGIN
     SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
     CASE @num
     WHEN 1 THEN UPDATE t_user SET userName='我是你爸' WHERE id=bookid;
     WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'wwww','bbbb');
     ELSE INSERT INTO t_user VALUES(NULL,'edd','end');
     END CASE;
    END
    &&
DELIMITER ;

CALL pro_user4();
CALL pro_user5(4);
CALL pro_user5(5);
CALL pro_user6(5);
CALL pro_user6(0);

DELETE FROM t_user ;

DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
    BEGIN
     aaa:LOOP
       SET totalNum=totalNum-1;
       IF totalNum=0 THEN LEAVE aaa ;
       ELSE INSERT INTO t_user VALUES(totalNum,'1232','2233');
       END IF ;
     END LOOP aaa;
    END
    &&
DELIMITER ;


DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
    BEGIN
     aaa:LOOP
       SET totalNum=totalNum-1;
       IF totalNum=0 THEN LEAVE aaa ;
       ELSEIF totalNum=3 THEN ITERATE aaa ;
       END IF ;
       INSERT INTO t_user VALUES(totalNum,'1232','22233');
     END LOOP aaa;
    END
    &&
DELIMITER ;

CALL pro_user7(11);
DELETE FROM t_user;
CALL pro_user8(11);

DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
	BEGIN
	  REPEAT
	     SET totalNum=totalNum-1;
	     INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	     UNTIL totalNum=1 
	  END REPEAT;
	END 
&&
DELIMITER ;


DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
	BEGIN
	 WHILE totalNum>0 DO
	  INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	  SET totalNum=totalNum-1;
	 END WHILE ;
	END 
&&
DELIMITER ;

CALL pro_user9(10);

SHOW PROCEDURE STATUS LIKE 'pro_book';

SHOW CREATE PROCEDURE pro_book;

ALTER PROCEDURE pro_book COMMENT '我来测试一个comment';

DROP PROCEDURE pro_user10;
DROP PROCEDURE pro_user9;