五、事件

1.如何打开事件(例)

CREATE TABLE test1_3(  id INT,  NAME VARCHAR(20),  balance INT ); #插入数据 INSERT INTO test1_3 VALUES(25,'wwww(28，wwww',1400); INSERT INTO test1_3 VALUES(26,'wwww',1500); INSERT INTO test1_3 VALUES(27,'wwww',1400); #遍历数据 SELECT * FROM test1_3; #打开事件的过程 SET autocommit=0; START TRANSACTION; DELETE FROM test1_3 WHERE id=26; #设置保存点，以后回滚方便 SAVEPOINT a; DELETE FROM test1_3 WHERE id=27; #回滚至设定的存储点 ROLLBACK TO a;

六、视图

例子： #查询以a开头的员工名和部门名

SELECT e.last_name,d.department_name  FROM employees e  INNER JOIN departments d  ON e.`department_id`=d.`department_id` WHERE e.`last_name` LIKE 'a%';

如何创建视图？

/* 语法： create view 视图别名(自己起一个) as 查询语句 (然后就可以像使用这个查询句一样使用这个视图) */

CREATE VIEW v1 AS  SELECT e.last_name,d.department_name  FROM employees e  INNER JOIN departments d  ON e.`department_id`=d.`department_id`; SELECT * FROM v1 WHERE last_name LIKE 'a%';

#查询名称包含‘查询名称’a员工名、部门名、工种信息

#创建视图 #视图相当于查询表

CREATE VIEW v2 AS SELECT e.last_name,d.department_name,j.* FROM  employees e INNER JOIN departments d INNER JOIN jobs j ON e.department_id=d.department_id AND e.job_id=j.job_id

#使用视图

SELECT * FROM v2 WHERE last_name LIKE '%a%';

#查询各部门的平均工资水平 #创建视图

CREATE VIEW v5 AS  SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;

#使用视图

SELECT DISTINCT(v5.ag),g.grade_level FROM v5 JOIN job_grades g ON v5.ag BETWEEN g.lowest_sal AND g.`highest_sal`;

我不知道为什么这不好

SELECT AVG(e.salary), j.`grade_level` FROM employees e  JOIN job_grades j ON AVG(salary) BETWEEN j.`lowest_sal` AND j.`highest_sal` GROUP BY department_id;

#查询平均工资最低的部门信息

SELECT * FROM departments d JOIN v5 ON v5.`department_id`=d.`department_id` ORDER BY v5.`ag` ASC LIMIT 1;

#查询平均工资最低的部门名称和工资

SELECT d.`department_name`,v5.`ag` FROM departments d JOIN v5 ON v5.`department_id`=d.`department_id` ORDER BY v5.`ag` ASC LIMIT 1;

#视图修改 #方式一

CREATE OR REPLACE VIEW v3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

#方式二

ALTER VIEW v3 AS SELECT * FROM employees; SELECT * FROM v3;

#4.删除视图 /* 语法：drop view 视图名，视图名…; 多个可以删除 */

DROP VIEW v3; #5.查看视图 DESC v3; SHOW CREATE VIEW v3.#这个再命令符窗口好看一点

#创建视图emp_v1.要求在011年初查询员工姓名、工资和电子邮件号码

CREATE OR REPLACE VIEW emp_v1 AS SELECT  last_name,  salary,  email FROM employees WHERE phone_number LIKE '011%';

#检查是否正确

SELECT * FROM emp_v1 ;

#创建视图emp_v2.查询部门最高工资高于1.2万的部门信息

CREATE OR REPLACE VIEW emp_v2 AS SELECT   d.*,MAX(salary) m FROM employees e INNER JOIN departments d ON d.department_id=e.department_id GROUP BY d.department_id HAVING m>12000;

2.视图更新(一般视图很少变化)

#1.插入 #insert into 视图名 values(数据)；可以插入，原始表的数据插入后也会插入这个数据 #2.修改 #update 视图名 set 列名=要修改的量 where 筛选条件；修改后也会 #3.删除 #delete from 视图名 where 条件；删除视图中的数据，删除原始表中的数据 #因为这种变化的视图会改变原始表，所以会增加权限

#不允许具有以下特征的变化 /* 1.包括以下关键字:分组函数，distinct、group by、having、union、union all 2.常量视图无法更新 3、select包含子查询 4、join (不能插入，可修改) 5、from无法更新的视图 6、where 引用子查询引用from子句中的表 */

七、变量

1、变量简介

#变量 /* 系统变量：全局变量绘画变量自定义变量：用户变量局部变量 / #一、系统变量 #说明:变量由系统提供，不是用户自定义的，属于服务器层面 #注意： #如果是全局变量，只需要添加global，会话变量加session或者不加，默认是session /

2、系统变量

使用语法： 1.检查所有系统变量

show global/session variables;

其中session可以省略，默认是session 2.检查一些不符合条件的系统变量

show global/session variables like '';

3.检查指定系统变量的值

select @@global|session.

系统变量名假如是全局变量加global，绘画变量加session，session可以省略 4.赋值系统变量方式一、 set global|session 系统变量名=值；如果是全局变量加global，绘画变量加session，session可以省略方式二、 set @@global|session.系统变量名=值；如果是全局变量加global，绘画变量加session，session可以省略 */

SHOW GLOBAL/SESSION VARIABLES;

#查看全局变量(查看全局变量)会话变量因为和查看全局的类似，所以就不写了，全局变量是针对多个连接，但是也只能在本次电脑开启时有效，电脑重启之后就会回复默认，会话变量是只针对当前连接)

SHOW GLOBAL VARIABLES;

#查看部分全局变量

SHOW GLOBAL VARIABLES LIKE '%char%';

#查看指定的某个全局变量的值

SELECT @@transaction_isolation;
SELECT @@global.auto_increment_increment;

#为某个指定的全局变量赋值

SET @@global.auto_increment_increment=3;

#如果使用global那么在其他连接向下也会更改，可以跨连接，但是重启之后就会自动恢复 #如果想要永久的，就需要更改配置文件

3、自定义变量

变量时用户自己定义的，不是由系统定义的使用步骤：声明赋值使用（查看、比较、运算等）分类：用户变量作用域：针对当前会话有效（跟会话变量相仿），应用于任何地方，在begin end的里面或外面 1、用户变量声明时必须初始化(其实使用select就会打印值) 例：set @用户变量名=值；或者 set @用户变量名：=值；或：select @用户变量名：=值；因为=以前用来进行判断，所以说用set的时候可以用=或者：=，如果用select必须使用：= 2、赋值（更新用户变量的值）方式一:通过set或者select（跟上面那个一样） set @用户变量名=值；或者 set @用户变量名：=值；或：select @用户变量名：=值；可以第一次select @name=‘join’ 然后set @name=100前面时字符型，后面又是int型方式二：通过select into select 字段 into @变量名 from 表名（把最后求出来的字段的值赋值给变量名） 3、查看（查看用户变量的值） select @用户变量名局部变量作用域：仅仅在定义它的begin end中有效，且再begin end 的第一句话声明：declare 变量名类型；或者declare 变量名类型 default 值；赋值：set 局部变量名=值；或者 set 局部变量名：=值；或：select @用户变量名：=值；或者 select 字段 into 局部变量名 from 表名（把最后求出来的字段的值赋值给变量名）

#用户变量 #赋值

SELECT @a:=12;

#查看

SELECT @a;

#更改（从新赋值）

SELECT @a:=13;

#局部变量 #查看 #声明两个变量，并求和打印 #1、用户变量

SET @a=1;
SET @b=2;
SET @sum = @a+@b;
SELECT @sum;

#2、局部变量这个必须在 BEGIN END 中不然会报错我现在还没学到 BEGIN end就先不举例子了

八、存储过程和函数

（类似于Java中的方法）创建语法：

create procedure 存储过程名(参数列表)
begin
	方法体
end

1、存储过程：参数列表包括：参数模式、参数名。参数类型

例： in strname varchar(20)；参数模式是： in ：该参数可以作为输入，该参数需要调用方传入值 out ：该参数可以作为输出，相当于返回值 inout ：该参数既可以作为输入又可以作为输出，既需要传入值，又需要返回值

2、如果函数体就一句话，begin end可以省略

存储过程中的每条SQL语句的结尾都需要加分号，到是分号就意味着程序结束，所以我们需要设置存储过程的结尾使用 delimiter 重新设置语法： delimiter 结束标记案例： delimiter $; (这些只能在命令提示符窗口使用，再SQLyog里面不能用)

3、调用

call 存储过程名(实参列表);

#向admin表中插入五条数据

SELECT * FROM admin;
DELIMITER $ //定义结束符号
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','6666'),('john2','0000'),('john3','0000'),('john4','0000'),('john5','0000');
END $

#调用

CALL myp1()$

4、例子

#创建带in模式参数的存储过程 #1、创建存储过程根据女神名查询对应的你男神信息

CREATE PROCEDURE myp4(IN beautyname VARCHAR(20))
BEGIN
	SELECT b.* FROM boys b RIGHT JOIN beauty be ON be.boyfriend_id=b.id WHERE be.name=beautyname;
END $

#调用

CALL myp4('柳岩')$

#2、输入两个信息查看是否和admin中的数据对应，对应则输出登录成成功，否则登录失败

CREATE PROCEDURE myp7(IN username VARCHAR(20),IN userpassword VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	SELECT COUNT(*) INTO result FROM  admin  a WHERE username=a.username AND userpassword=a.password; 
	SELECT IF(result>0,'登录成功','登录失败');#使用
END $

#调用

CALL myp5('john','8888')$

#创建存储过程实现传入用户名和密码，插入到admin中

CREATE PROCEDURE myp9(IN `name` VARCHAR(20),IN userpassword VARCHAR(20))
BEGIN
	INSERT INTO admin(username,`password`) VALUES(`name`,userpassword);
END $
CALL myp8('shimisi','123456')$

#检验

SELECT * FROM admin;

#创建存储过程，实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE myp10(IN id INT,OUT `name` VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
	SELECT b.name,b.phone INTO `name`,phone
	FROM beauty b
	WHERE b.id=id;
END $
CALL myp10(8,@n,@m)$
SELECT @n,@m$

#删除存储过程 #语法：drop procedure 存储过程名 (一次只能删一个)

DROP PROCEDURE myp4;

#查看存储过程的结构

SHOW CREATE PROCEDURE myp4;

#创建一个存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE time_str(IN mydate DATETIME,OUT strtime VARCHAR(30))
BEGIN
	SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO strtime;
END $
CALL time_str(NOW(),@str)$
SELECT @str$

#创建存储过程或函数实现传入女神名称返回女神 and 男神格式化的字符串

CREATE PROCEDURE myp11(IN namee VARCHAR(20),OUT namen VARCHAR(20))
BEGIN
	SELECT  CONCAT(namee,' AND ',IFNULL(bo.boyname,' ')) INTO namen
	FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id=bo.id
	WHERE b.name=namee;  
END $
CALL myp11('小昭',@name)$
SELECT @name;
DROP PROCEDURE myp11;

#创建一个存储过程或函数，根据传入的条目和起始索引，查询beauty表的记录

CREATE PROCEDURE myp12(IN size INT,IN num INT)
BEGIN
	SELECT * FROM beauty LIMIT num,size;
END $
CALL myp12(5,3)$

5、函数

#函数和存储过程的区别是有且仅有一个返回值

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END #调用函数（不用call，用select） SELECT 函数名(参数列表) /* 1、参数列表包括：参数名参数类型 2、函数体：肯定有return语句，如果没有也不会报错，但是不建议 3、也是要使用delimiter语句设置结束标记 */ #返回有工资的员工个数

CREATE FUNCTION hs1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT
		COUNT(*) INTO c
	FROM
		employees
	WHERE
		salary IS NOT NULL;
	RETURN c;
END $
SELECT hs1()$
DROP FUNCTION hs2;

#根据员工名返回工资

CREATE FUNCTION hs2(name1 VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE a DOUBLE DEFAULT 0;#定义用户变量
	SELECT 
		salary INTO a#赋值
	FROM
		employees e
	WHERE 
		name1=e.last_name;
	RETURN a;
END $
SELECT hs2('Kochhar')$

#查看函数

SHOW CREATE FUNCTION hs2;

#删除函数

DROP FUNCTION hs2;

#实现两数之和

CREATE FUNCTION jia(a DOUBLE,b DOUBLE) RETURNS DOUBLE
BEGIN
	DECLARE c DOUBLE DEFAULT 0;
	SELECT a+b INTO c;
	RETURN c;
END $
SELECT jia(1,2)$

#创建存储过程或函数实现成绩评测

CREATE FUNCTION hs3(score INT) RETURNS CHAR
BEGIN
	DECLARE ab CHAR ;
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A' INTO ab;
	WHEN score>=80 THEN SELECT 'B' INTO ab;
	WHEN score>=70 THEN SELECT 'C' INTO ab;
	WHEN score>=60 THEN SELECT 'D' INTO ab;
	ELSE SELECT 'E' INTO ab;
	END CASE;
	RETURN ab;
END $
SELECT hs3(98)$

#或者

CREATE PROCEDURE myp14(IN score INT)
BEGIN
	DECLARE ab CHAR;
	IF score>=90 AND score<=100 THEN SELECT 'A' INTO ab;
	ELSEIF score>=80 THEN SELECT 'B' INTO ab;
	ELSEIF score>=70 THEN SELECT 'C' INTO ab;
	ELSEIF score>=60 THEN SELECT 'D' INTO ab;
	ELSE SELECT 'E' INTO ab;
	END IF;
	SELECT ab;
END $
CALL myp14(99)$

九、循环结构

#循环结构 /* 分类： while、loop、repeat 循环控制 iterate类似于continue，继续，结束本次循环，进行下一次循环 leave类似于break，跳出，结束当前循环 */ /*1、 while 语法：【别名:】while 循环条件 do 循环体 end while 【别名】; */ /*2、 loop 语法：(可以用来模拟简单的死循环) 【别名:】 loop 循环体; end loop 【别名】; */ /*3、repeat 语法：【别名:】repeat 循环体; util 结束循环的条件【别名】; */ #批量插入，根据次数插入到admin表中多条记录

DROP PROCEDURE pro_while1;
CREATE PROCEDURE pro_while1(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<n DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('john',i),CONCAT('666',i));
		SET i=i+1;
	END WHILE a;
END $
CALL pro_while1(100)$

#批量插入，根据次数插入到admin表中多条记录,如果次数大于20，则停止

TRUNCATE TABLE admin;
CREATE PROCEDURE pro_while2(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<n DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('john',i),CONCAT('666',i));
		SET i=i+1;
		IF i>20 THEN LEAVE a;
		END IF;
	END WHILE a;
END $
CALL pro_while2(100)$

#批量插入，根据次数插入到admin表中多条记录,只插入偶数次的记录

DROP PROCEDURE pro_while1;
CREATE PROCEDURE pro_while3(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<n DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('john',i),CONCAT('666',i));
	END WHILE a;
END $
CALL pro_while3(100)$

/*已知表stringcontent 其中字段： id 自增长 content varchar(20) 向该表插入指定个数的，随机的字段 */

DROP TABLE IF EXISTS stringconntent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);
DELIMITER $
CREATE PROCEDURE sdf(IN size INT)
BEGIN
	DECLARE i INT DEFAULT  0;#定义循环变量
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE s1 INT DEFAULT 1;#默认起始索引是1
	DECLARE s2 INT DEFAULT 1;#默认长度是1
	WHILE i<size DO
		SET s2=FLOOR(RAND()*(20-s1+1)+1);#产生一个随机长度
		SET s1=FLOOR(RAND()*26+1);#产生一个随机数，代表起始索引
		INSERT INTO stringcontent (content) VALUES(SUBSTR(str,s1,s2));
		SET i=i+1;
	END WHILE;
END $
CALL sdf(10)$
TRUNCATE TABLE stringcontent;
SELECT * FROM stringcontent;
DROP TABLE stringcontent;

资讯详情

MySQL数据库操作2.0