day05-mysql-约束、视图、存储过程

最新推荐文章于 2023-03-22 09:37:46 发布

Kshmr丶丶

最新推荐文章于 2023-03-22 09:37:46 发布

阅读量225

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/lorry001/article/details/107573714

版权

约束

分类

NOT NULL：非空
该字段的值是必填的，如果不设置该约束，该字段默认是可以为空
DEFAULT:默认
该字段有默认值
CHECK:检查，mysql语法支持，但效果不支持
该字段值可以加以限制，比如年龄可以控制在0-120之间
PRIMARY KEY:主键
该字段值不可以重复
1、不可以为空
2、一个表中只能有一个主键，但是可以多个字段做组合主键
UNIQUE:唯一约束
该字段值不可以重复
1、可以为空
2、一个表中可以有多个唯一键，可以有组合唯一键

FOREIGN KEY:外键
用于限制多表的关系

  1、从表的该字段值必须来自于主表的关联列的值
  2、从表和主表的关联列的类型必须一样
  3、主表的关联列必须是主键
  4、在从表中设置外键
  5、一个表中可以添加多个外键
  6、插入数据时，先插入主表
    删除数据时，先删除从表

约束	位置	支持
列级约束	列的后面	除了外键
表级约束	表的后面	除了非空和默认

列级约束：

创建表时添加约束：

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束

)

添加列级约束

  create table stuinfo(
  	id int not null unique,#非空约束+唯一约束
  	stuname varchar(20) unique,#唯一约束
  	gender char default '男',#默认约束
  	age int unsigned check (age between 0 and 120)#检查约束
  );

2.关于插入多个唯一或主键

create table stuinfo(
	id int not null unique,#非空约束+主键约束
	stuname varchar(20) unique,#唯一约束
	gender char default '男',#默认约束
	age int unsigned check (age between 0 and 120)#检查约束
);

表级约束

添加表级约束

语法

create table stuinfo(
	id int ,
	stuname varchar(20),
	gender char,
	age int unsigned,
【constraint 约束名】约束类型(字段)
);

案例

create table grade(
	id int primary key,
	gradename varchar(20)
)


drop table if exists stuinfo;
create table stuinfo(
	id int not null,
	stuname varchar(20),
	gender char default '男',
	age int unsigned,
	gradeid int,
	primary key(id,stuname),#组合主键：id+stuname
	constraint uq unique(age),#唯一键
	constraint fk_stuinfo foreign key(gradeid) references grade(id)#外键约束
);

修改表时添加约束

案例：在未加任何约束的情况下
1.添加非空

ALTER TABLE stuinfo MODIFY COLUMN gender CHAR NOT NULL;

2.添加默认

ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED DEFAULT 18;

3.添加主键
①方式一：列级约束的做法

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②方式二：表级约束的做法
语法：

ALTER TABLE 表名 ADD [CONSTRAINT 约束名]  约束类型 (字段);

案例：

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一

①方式一：列级约束的做法

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) UNIQUE;

②方式二：表级约束的做法

ALTER TABLE stuinfo ADD CONSTRAINT uq_stuinfo UNIQUE(stuname);

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_grade FOREIGN KEY(gradeid) REFERENCES grade(id);

修改表时删除约束（重写一遍，不加约束）

1.删除非空

alter table stuinfo modify column gender char ;

2.删除默认

alter table stuinfo modify column age int unsigned;

3.删除主键

alter table stuinfo modify column id int ;
alter table stuinfo drop primary key;

4.删除唯一

alter table stuinfo modify column stuname varchar(20);
alter table stuinfo drop index uq_stuinfo;

查看指定表的所有索引

show index from stuinfo;

5.删除外键

alter table stuinfo drop foreign key fk_stuinfo_grade;

事务

概念：

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
希望执行一些操作时，要么同时执行，要么同时不执行，最终达到数据的一致性
事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。

事务的特点：

 1. 原子性（Atomicity）
	原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 

2. 一致性（Consistency）
	事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事物的使用

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句（自动提交）
用户会话正常结束
系统异常终了

set autocommit=0;#取消自动提交
start transaction;#开启事务
rollback;#回滚事务
commit;#提交事务

事务的隔离级别

脏读：一个事务读到了另一个事务未提交的数据
不可重复读：在一个事务执行期间，两次查询读到的结果不一致
幻读：一个事务进行插入操作，没有提交。另一个事务读到了没有提交的数据

解决：

read uncommitted：脏读、不可重复、幻读
read committed:不可重复读、幻读
repeatable read:幻读（mysql5.5版本之前会出现幻读，但5.5之后不会出现）
serializable：串行化（性能极低）
mysql默认的是repeatable read

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: repeatable read

在 MySql 中设置隔离级别

（1）每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别

（2）查看当前的隔离级别: SELECT @@tx_isolation;

（3）设置当前 mySQL 连接的隔离级别:  
	set  transaction isolation level read committed;

（4）设置数据库系统的全局的隔离级别:
	 set global transaction isolation level read committed;
	set global transaction isolation level repeatable read;

视图

视图的概念

视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，
并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

视图和表的区别：

1. 表中的数据占用物理空间，视图不占用。
2. 表中的数据是实际存在的，视图的数据是执行查询时动态生成的

应用场景

多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

视图的好处

使用简单
安全
数据独立

创建视图

create view my_v1
as
select studentname,majorname
from student s
inner join major m
on s.majorid=m.majorid
where s.majorid=1;

修改视图

alter view view_name
As select_statement
 [with|cascaded|local|check option]

删除视图

drop view [if exists]  view_name,view_name …[restrict|cascade]

查看视图

show tables;
如果需要查询某个视图的定义，可以使用show create view命令进行查看
show create view view_name \G

存储过程

什么是存储过程？

存储过程：

事先经过编译并存储在数据库中的一段sql语句的集合。

使用好处：

 1、简化应用开发人员的很多工作
 2、减少数据在数据库和应用服务器之间的传输
 3、提高了数据处理的效率

创建存储过程和函数

（1）创建存储过程
语法：

	CREATE PROCEDURE 存储过程的名称(【IN/OUT/INOUT】 参数名 参数类型)
	BEGIN
		主体
	END

参数对应的in、out、inout的意思
IN 代表该参数作为输入（传统意义的参数）
OUT 代表该参数作为输出（传统意义的返回值）
INOUT 代表该参数既可以作为输入又可以作为输出（参数+返回）
支持：多个IN、多个OUT、多个INOUT
支持：无参

（2）创建函数

create function 函数名([func_parameter[,…]])
 returns type
 [characteristic…]routine_body

调用存储过程或函数

（1）调用存储过程
call 存储过程名(参数列表)

①调用无参的存储过程 
	CALL 存储过程名()
②调用in类型参数的存储过程
	CALL 存储过程名(常量值)
③调用out类型参数的存储过程
	call 存储过程名（@变量名） $$
	select @变量名  $$
④调用inout类型参数的存储过程
	set @变量名=值 $$
	CALL 存储过程名（@变量名） $$
	SELECT @变量名 $$

（2）调用函数
Select 函数名(参数列表)

用户变量

定义语法： set @变量名 ;
赋值语法：
	方式一：普通赋值
	set @变量名:=值;或set @变量名=值;
	select @变量名:=值;

	方式二：通过查询结果为变量赋值
	select 字段|表达式 into 变量名
	from 表名 【where 条件】

修改存储过程或函数

修改存储过程：

alter procedure 存储过程名  [charactristic…]

修改函数：

alter function 函数名  [charactristic…]

删除存储过程或函数

说明：
一次只能删除一个存储过程或者函数，并且要求有该过程或函数的alter routine 权限

删除存储过程：

   drop procedure [if exists] 存储过程名

删除函数：

   drop function [if exists] 函数名

查看存储过程或函数

1.查看存储过程或函数的状态：

   show {procedure|function} status like 存储过程或函数名

2.查看存储过程或函数的定义：

   show create {procedure|function} 存储过程或函数名

3.通过查看information_schema.routines了解存储过程和函数的信息（了解）

select * from rountines where rounine_name =存储过程名|函数名

案例：

一、创建无参的存储过程

案例：创建存储过程，实现查询beauty表的大于3号id的记录

delimiter $$
create procedure mypro1()
begin
	select * from beauty where id>3;
end $$

调用存储过程
call mypro1()$$

解决乱码
set names gbk$$

二、创建in类型参数的存储过程

案例：根据女神名称，查询男神信息

create procedure mypro2(in bname varchar(20))
begin
	select bo.* from boys bo
	join beauty b on bo.id=b.boyfriend_id
	where b.name=bname;
end $$

三、创建多个in类型参数的存储过程

create procedure mypro3(in username varchar(20),in password varchar(20))
begin
	select count(*) from admin 
where admin.username=username and admin.password=password;
end $$

四、创建out类型参数的存储过程

create procedure mypro4(in username varchar(20),in password varchar(20),out result int)
begin
	select count(*) into result from admin 
where admin.username=username and admin.password=password;
end $$

调用存储过程
select @a $$   查看返回的值
call mypro4('','',@a) $$

五、创建out类型参数的存储过程

create procedure mypro5(in a int,in b int,out c int,out d int)
begin
	set c=a*2;
	set d=b*2;
end $$

调用存储过程
call mypro5('','',@a,@b) $$
select @a $$

六、创建inout类型参数的存储过程

create procedure mypro6(inout age int)
begin
	set age=age*3;
end $$

删除存储过程

drop procedure 存储过程名
delimiter ;
drop procedure mypro6 ;

查看某个存储过程

show create procedure 存储过程名
show create procedure mypro1;

流程控制、函数略…

Kshmr丶丶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
day05-mysql-约束、视图、存储过程

约束分类NOT NULL：非空该字段的值是必填的，如果不设置该约束，该字段默认是可以为空DEFAULT:默认该字段有默认值CHECK:检查，mysql语法支持，但效果不支持该字段值可以加以限制，比如年龄可以控制在0-120之间PRIMARY KEY:主键该字段值不可以重复1、不可以为空2、一个表中只能有一个主键，但是可以多个字段做组合主键UNIQUE:唯一约束该字段值不可以重复1、可以为空2、一个表中可以有多个唯一键，可以有组合唯一键FOREIGN K
复制链接

扫一扫