sql最后的,感觉在数据库这块学的不是很好,很多东西都没有记住,边学边忘…
1. 约束
作用:为了保证数据的有效性和完整性
mysql中常用的约束
主键约束:(primary key)
自增长约束:(auto_incrment)加在整数型的字段和主键约束来使用
唯一约束:(nuique)
非空约束(not null)
外键约束(foreign key)
sex ENUM(‘男’,‘女’)把一个字段的数据类型设置成枚举类型,也可以起到一定的约束效果
unsigned(非负约束)
例如:TINYINT -128—127 带符号的范围是-128—127.无符号的范围是0—255
2.创建一张表,表的字段和数据来一条查询语句
- 创建一张表,会把另一张表的字段和数据全部都复制过去
语法:create table 表名 as selete * from 另一张表 where 1=1; - 如果我们只是想要字段不要数据的话
create table 表名 as selete * from 另一张表 where 1=2; - 如果我只是想要个别的字段的话
create table 表名 as selete 表名.字段名 表名.字段名 from 另一张表 where 1=2; - 我创建的表也可以来自一个子查询
create table 表名 as 子查询
3.存储过程procedure
- 概念:
类似于java中的方法被封装可以重复调用.意思就是存储过程中数据库的一个对象,存储在服务端,用来封装多条SQL语句且带有逻辑性,由于在创建的时候随sql进行了编译所以执行速度比较快,而且可以重复调用 - 语法
DELIMITER $$
CEATE
PROCEAURE ' prefromance-schema'.'myTestpro'()
BEGIN
END$$
DELIMITER ;
//注意,创建过程需要管理员分配权限
//补充:delimiter是mysql定义的结束标志,在mysql客户端中结束标记默认是分号(;).
如果一次输入的语句较多,并且语句中间有分号,这时需要新指定一个特殊的结束符。
delimiter $$ 表示mysql用$$表示mysql语句结束,过程结束后肯定会有一句delimiter ;
表示恢复成默认的。
例如:
DELIMITER $$
CREATE
PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
BEGIN
DELETE FROM emp WHERE empno=num;
SELECT COUNT(*) FROM emp INTO r;
END$$
DELIMITER ;
-
参数
in:输入参数
out:输出参数
inout:输入输出参数 -
调用存储过程 call
语法:call myTestPro(9527,@rr)
查询结果: select @rr
- 删除存储过程 drop
语法: drop procedure myTestPro; - 查看存储过程 show
show procedure status\G; – 查看所有的存储过程状态
show create procedure 存储过程名字\G; – 查看创建存储过程的语句 - 带有IF逻辑的存储过程if then elseif else
DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str='星期一';
ELSEIF num=2 THEN --注意elseif 连在一块
SET str='星期二';
ELSEIF num=3 THEN
SET str='星期三'; -- 注意要用分号结束
ELSE
SET str='输入错误';
END IF; -注意要结束if 后面有分号
END $$
- 带有循环的存储过程 while do
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE; -- 要记得结束循环
SET result=vsum;
END $
- 其他循环的写法(了解)
例如: REPEAT 循环
DELIMITER $
CREATE PROCEDURE proRepeat(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i=i+1;
UNTIL i>=5 -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
END REPEAT; -- 记着结束
SET outnum=i;
END $
CALL proRepeat(@num);
SELECT @num;
例如:loop 循环
DELIMITER $
CREATE PROCEDURE proLoop(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
myloop:LOOP -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
SET i=i+1;
IF i>=5 THEN
LEAVE myloop; -- 根据我的循环标号中断循环 leave 是中断循环的意思
END IF;
END LOOP;
SET outnum=i;
END $
CALL proLoop(@num);
SELECT @num;
- 控制循环的两个关键字
leave 相当于java中的 break `
iterate相当于java中的continue`
- 变量
全局变量(内置变量):可以在多个会话中去访问他
-- 查看所有全局变量: show variables
-- 查看某个全局变量: select @@变量名
-- 修改全局变量: set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results:mysql服务器输出数据的编码
SET character_set_client=gbk –设置数据库编码
SELECT @@character_set_client --查看数据库编码
-- 会话变量: 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!
-- 定义会话变量: set @变量=值
-- 查看会话变量: select @变量
-- 局部变量: 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失!!
定义局部变量的语法:DECLARE i INT DEFAULT 1;
给变量设置值 set i=10;
触发器 Trigger
- 概念
触发器:数据库中的一个对象,相当于JS中的监听器,触发器可以监听 增删改 三个动作 比如说我想监听一张表,只要我增删改了这张表中的数据,我就可以触发这个触发器,去往另外一张表中记录一下日志 - 语法
DELIMITER $$
CREATE
TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `mytestdb`.`<Table Name>`
FOR EACH ROW
BEGIN
END$$
DELIMITER ;
BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
FOR EACH ROW 行级触发,每操作一行就触发
- 简单的演示:
我往一张表test中插入了数据,在日志表中loggger 中添加一条记录
DELIMITER $$
CREATE TRIGGER `mytestdb`.`MyTri3` AFTER DELETE ON test FOR EACH ROW
BEGIN
INSERT INTO logger VALUES(NULL,"你删除了一条数据",NOW());
END$$
DELIMITER ;
- old和new
old.字段 可以获取到被监听表中字段的旧值
new.字段 可以获得被监听表中的更新后的字段的新值 比如插入新值或者修改旧值
例如:往一张表t1中添加一条新的数据,另外的一张表t2也要添加一条同样的数据
DELIMITER $$
CREATE
TRIGGER 'mytestdb'.'myTri6' AFTER INSERT
ON 'mytestdp'.'t1'
FOR EACH ROW BEGIN
INSERT INTO T2 VALUES(new.id,new.username,new.age);
END$$
DELIMITER
例如:修改一张表t1中的数据,另外一张表中的数据t2也要进行修改
DELIMITER $$
CREATE
TRIGGER 'mytestdb'.'MyTri7' AFTER UPDATE
ON 'mytest'.t1
FRO EACH ROW
BEGIN
UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id;
END$$
DELIMITER
视图
-
概念
有结构(有行有列),但是没有结果(不存在真实的数据)的虚拟的表
虚拟表的结构来源不是自己定义的,而是从对应的基表中得来的 -
创建视图的语法
create view 视图名 seletct (这里面可以是一张表或者多张表的普通查询,也可以是多表查询)
例如:单表查询
create view 视图名 seletct * from student;
例如:多表查询 -
查看视图 视图==一张虚拟的表,查表的语法完全适用
-
视图一旦被创建,系统就会在对应的数据库文件下创建结构文件:frm文件.
-
视图的使用:视图的存在只是为了查询,可以用表的语句进行查询
-
删除视图: drop view 视图名
-
修改视图:视图本身不可以修改但是,视图的来源是可以修改的
-
视图的意义