约束
分类
-
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;