视图 (了解即可,不用)
虚拟表,从一个表或多个表中导出来的表,作用和真实表一样,包含一系列带有行和列的数据 视图中,
用户可以使用SELECT语句查询数据,也可以使用INSERT,UPDATE,DELETE修改记录,视图可以使用户操作方便,并保障数据库系统安全
优点
简单化,数据所见即所得
安全性,用户只能查询或修改他们所能见到得到的数据
逻辑独立性,可以屏蔽真实表结构变化带来的影响
缺点
性能相对较差,简单的查询也会变得稍显复杂
修改不方便,特变是复杂的聚合视图基本无法修改
create view 视图名称 as CREATE VIEW v1 AS SELECT * from test; # 创建
alter view 视图名称 as SQL # 修改
drop view 视图名称; # 删除
SELECT * from A # 直接从视图里查数据 A 为视图名称
触发器
当对某张表做:增删改操作时,可以使用触发器自定义关联行为(代码可也控制,触发器也可以)
扩展知识:
delimiter //
CREATE TRIGGER t1 BEFORE INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO teacher(tname) VALUES(NEW.sname);
END //
delimiter ;
-- delimiter // 修改数据库语句的终止符,这里使用了 // 创建完触发器后又修改为;
NEW : 代指新数据插入
OLD :老数据,删除和更新都有OLD
after是先完成数据的增删改,再触发,触发的语句晚于监视的增删改操作
before是先完成触发,再增删改,触发的语句先于监视的增删改,
-- 删除触发器 t1代表触发器名称
DROP TRIGGER t1;
-- 实例:创建插入前的触发器
CREATE TRIGGER t2 BEFORE INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO teacher(tname) VALUES(NEW.sname);
END;
-- 测试
INSERT into student(sname,gender,age,class_id) VALUES("测试","av",18,1),("测试","av",18,1)
-- 插入后会自动往teacher表里添加两条数据,student表每变动一行都会插入
-- -------------------------------------------------------------------------------------------------------
-- 创建删除的触发器
delimiter //
CREATE TRIGGER t1 BEFORE DELETE ON student FOR EACH ROW
BEGIN
INSERT INTO teacher(tname) VALUES(OLD.sname);
END //
delimiter ;
-- 测试
DELETE from student WHERE sname="测试"
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...;
END;
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...;
END;
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...;
END;
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...;
END;
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...;
END;
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...;
END;
函数
为了简化操作,mysql提供了大量的函数给程序员使用(比如你想输入当前时间,可以调用now()函数)
函数可以出现的位置:插入语句的values()中,更新语句中,删除语句中,查询语句及其子句中
SELECT CURRENT_DATE; -- 显示年月日
SELECT CURRENT_TIME; -- 显示时分秒
SELECT CURRENT_TIMESTAMP; -- 显示全部时间
时间格式化
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
-- 如果数据里存的是2019-11-11 12:20可以通过时间格式化让其显示2019-11-11
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
);
INSERT into blog(create_time) VALUES
('2019-07-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-06-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-07-28 17:54:29'),('2019-07-28 17:54:29'),
('2019-06-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-07-28 17:54:29'),('2019-04-28 17:54:29');
-- 时间格式化
mysql> SELECT DATE_FORMAT(create_time,"%Y-%m"),count(1) from blog GROUP BY DATE_FORMAT(create_time,"%Y-%m");
+----------------------------------+----------+
| DATE_FORMAT(create_time,"%Y-%m") | count(1) |
+----------------------------------+----------+
| 2019-04 | 1 |
| 2019-06 | 13 |
| 2019-07 | 12 |
| 2019-08 | 4 |
+----------------------------------+----------+
4 rows in set (0.00 sec)
字符串函数https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
SELECT CHAR_LENGTH("linyaohong"); -- 统计字符串长度
SELECT CONCAT('lin','yao','hong'); -- 字符串拼接
自定义函数
-- 自定义函数
CREATE FUNCTION f2(
i1 int,
i2 int)
returns int
BEGIN
declare num int DEFAULT 0; -- 可以设置默认值为0,设置变量
set num = i1 +i2;
return(num);
END;
-- 调用自定义函数(可以传参,有返回值)
mysql> select f2(10,2);
+----------+
| f2(10,2) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
存储过程(Stored Procedure):是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数: 表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值。
OUT 输出参数: 该值可在存储过程内部被改变,并可返回。
INOUT 输入输出参数: 调用时指定,并且可被改变和返回
-- ----------------------简单存储过程(in,OUT,INOUT)
CREATE PROCEDURE p1()
BEGIN
SELECT * FROM student;
INSERT INTO class(caption) VALUES("测试班级");
END;
-- -- 调用存储过程
-- CALL p1()
-- ----------------------创建传参存储过程 (in)
CREATE PROCEDURE p2(
IN n1 int,
IN n2 int
)
BEGIN
SELECT * FROM student WHERE sid >n1;
END;
-- 调用存储过程
CALL p2(8,2)
-- ----------------------创建传参存储过程 (in,OUT) OUT必须是变量
CREATE PROCEDURE p3(
IN n1 int,
OUT n2 int -- 可以用于标识存储过程的执行结果 1 2 3
)
BEGIN
set n2 = 123456;
SELECT * FROM student WHERE sid >n1;
END;
-- 调用存储过程
set @v1 = 0;
CALL p3(8,@v1);
SELECT @v1;
-- -------------------------- 创建传参存储过程 (INOUT)
CREATE PROCEDURE p4(
INOUT n1 int,
in n2 int
)
BEGIN
INSERT INTO class(caption) VALUES(n1);
INSERT INTO class(caption) VALUES(n2);
SELECT * FROM student WHERE sid >n2;
END;
set @v1 = 123456789000;
call p4(@v1,7);
SELECT @v1;
-- 利用存储过程批量插入简单的数据
DROP TABLE IF EXISTS `test`;
CREATE TABLE test(
id int PRIMARY key auto_increment,
num int not null
)ENGINE=INNODB CHARSET=UTF8;
create PROCEDURE pro_test(in para int(11))
BEGIN
declare i int DEFAULT 0;
declare rand_num int;
while i < para do
SELECT cast(rand()*10000 as UNSIGNED) into rand_num;
INSERT into test(num) VALUES(rand_num);
set i = i+1;
end while;
END;
call pro_test(500000)
事务
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
事务用来管理insert,update,delete语句
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性:一组事务,要么成功;要么撤回。
2、稳定性 : 有非法数据(外键约束之类),事务撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
2、稳定性 : 有非法数据(外键约束之类),事务撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
结合上文所述的存储过程的事务
mysql> show global variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
-- 如果错误返回 1 如果warning返回 2 如果全部执行完毕并且提交则返回 0
delimiter \\
create PROCEDURE p1(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\\
delimiter ;
-- 在Mysql控制台使用事务来操作
mysql> begin; #开始一个事务
mysql> insert into student (name) values(lin);
mysql> rollback; #回滚这样数据是不会写入的
当然如果上面的数据没问题,就输入commit提交命令就行
mysql> commit; # commit 提交
游标 (只有当对每行进行计算的时候才会使用,一般不使用,性能低)
-- 创建相关表
CREATE TABLE `A` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`num` int(0) NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8;
INSERT INTO `a`(`num`) VALUES (99);
INSERT INTO `a`(`num`) VALUES (98);
INSERT INTO `a`(`num`) VALUES (97);
-- 创建存储过程
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimiter ;
-- 调用存储过程
call p6();
动态执行SQL(防SQL注入)
delimiter \\
CREATE PROCEDURE p7(
in nid int
)
BEGIN
set @nid = nid
PREPARE prod FROM 'select * from student where sid > ?'; -- 准备
EXECUTE prod USING @nid; -- @nid 代替? 而且只支持@
DEALLOCATE prepare prod;
END\\
delimiter ;