五、事件
1、怎样打开事件(例子)
CREATE TABLE test1_3(
id INT,
NAME VARCHAR(20),
balance INT
);
#插入数据
INSERT INTO test1_3 VALUES(25,'wwww',1000),(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%';
1、怎样创建视图
/*
语法:
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,要求查询部门的最高工资高于12000的部门信息
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;