一、约束
1、列级约束
#常见约束
/*
NOT NULL:非空
该字段的值是必填的,如果不设置该约束,该字段默认是可以为空
DEFAULT:默认
该字段有默认值
CHECK:检查,mysql语法支持,但效果不支持
该字段值可以加以限制,比如年龄可以控制在0-120之间
PRIMARY KEY:主键
该字段值不可以重复
1、不可以为空
2、一个表中只能有一个主键,但是可以多个字段做组合主键
UNIQUE:唯一约束
该字段值不可以重复
1、可以为空
2、一个表中可以有多个唯一键,可以有组合唯一键
FOREIGN KEY:外键
用于限制多表的关系
1、从表的该字段值必须来自于主表的关联列的值
2、从表和主表的关联列的类型必须一样
3、主表的关联列必须是主键
4、在从表中设置外键
5、一个表中可以添加多个外键
6、插入数据时,先插入主表
删除数据时,先删除从表
分类:
位置 支持
列级约束 列的后面 除了外键
表级约束 表的后面 除了非空和默认
#CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束
2、表级约束
#添加表级约束
#语法
CREATE TABLE stuinfo(
id INT ,
stuName VARCHAR(20),
gender CHAR,
age INT UNSIGNED,
【CONSTRAINT 约束名】约束类型(字段)
);
3、修改表时添加约束
#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 约束名] 约束类型 (字段);
4、修改表时删除约束
#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;
二、事物概念
1、事务的概念
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。
2、事务的特点
事务的ACID(acid)属性
(1) 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
(2)一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
(3)隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
(4)持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响
3、事物的使用
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句(自动提交)
用户会话正常结束
系统异常终了
SET autocommit=0;#取消自动提交
START TRANSACTION;#开启事务
ROLLBACK;#回滚事务
COMMIT;#提交事务
4、数据库的隔离级别
(1)对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
(2)数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
(3)一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.
5、数据库提供的 4 种事务隔离级别
Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
6、在 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;
三、事物实操
1、事物-sql
概念:数据库区分于其他文件系统的一种特征
好处:
希望执行一些操作时,要么同时执行,要么同时不执行,最终达到数据的一致性
特性:【★】
ACID
分类:
1、隐式事务
比如:insert、update、delete本身就是一个事务
2、显式事务
特征:具有明显的开启和结束的标记
set autocommit=0; 取消隐式事务自动提交的功能
start transaction;开启事务
…增删改查sql语句
commit;提交事务
rollback;回滚事务
并发事务容易出现的问题:
脏读:一个事务读到了另一个事务未提交的数据
不可重复读:在一个事务执行期间,两次查询读到的结果不一致
幻读:一个事务进行插入操作,没有提交。另一个事务读到了没有提交的数据
如何解决?
read uncommitted: 脏读、不可重复、幻读
read committed:不可重复读、幻读
repeatable read:幻读(mysql5.5版本之前会出现幻读,但5.5之后不会出现)
serializable:串行化(性能极低)
mysql默认的是repeatable read
如何查看隔离级别?
select @@tx_isolation;
如何设置隔离级别?
set @@tx_isolation=‘read-uncommitted’;
set [global] transaction isolation level read uncommitted;
*/
SHOW VARIABLES LIKE ‘autocommit’;
#开启事务
SET autocommit=0;#取消自动提交
START TRANSACTION;#开启新事务,可选
INSERT INTO beauty VALUES(NULL,‘付明明2’,‘女’,NOW(),‘112’,NULL,NULL);
ROLLBACK;#回滚事务
COMMIT;#提交事务
SELECT * FROM beauty;
#演示delete和truncate在事务中的区别
delete可以回滚, truncate不能回滚
2、并发事物 dos 演示
SELECT * FROM admin
INSERT INTO admin VALUES(1,‘xiaoming’,‘000000’)
dos1
mysql -uroot -proot
mysql > use test;
mysql> set autocommit = 0;
mysql> insert into account values(11,’john’,’000’)
mysql>commit
mysq> set transaction isolation level read uncommitted; //最小的隔离级别 //没有效果需要重启dos
mysql> set autocommit = 0;
mysql > insert into account values(11,’jhok,’8989’’)
dos2
mysql -uroot -proot
mysql > use test;
mysql > select @@tx_isolation
SERIALIZABLE
mysq> set transaction isolation level read uncommitted;
mysql> set global transaction isolation level read uncommitted; //第二种设置方式// 没有效果需要重开dos
mysql> set autocommit = 0;
mysql > select * from account;
四、视图概念
1、什么是视图
视图:MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果
2、视图应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
3、示例
CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;
4、视图的好处
使用简单
安全
数据独立
5、创建或者修改视图
创建视图的语法:
create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]
修改视图的语法
alter view view_name
As select_statement
[with|cascaded|local|check option]
6、视图更新性
视图的可更新性和视图中查询的定义有关系,以下类型的视图是不能更新的。
(1)包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
(2)常量视图
(3)Select中包含子查询
(4)join
(5)from一个不能更新的视图
(6)where子句的子查询引用了from子句中的表
7、删除视图的语法
用户可以一次删除一个或者多个视图,前提是必须有该视图的drop权限。
drop view [if exists] view_name,view_name …[restrict|cascade]
8、查看视图结构的语法
show tables;
如果需要查询某个视图的定义,可以使用show create view命令进行查看
show create view view_name \G
视图实操
1、视图的创建和使用
#视图
/*
视图:基于原始表得到的一个虚拟的查询结果集
视图和表的区别:
- 表中的数据占用物理空间,视图不占用。
- 表中的数据是实际存在的,视图的数据是执行查询时动态生成的
视图的好处:
1、提高查询语句的重用性,调用简单
2、安全
3、独立
视图的应用场景:
1、多个地方用到相同的查询结果
2、查询结果使用了比较复杂的sql语句
一、创建视图:
create [or replace] view 视图名
as
select 语句;
特点:
1、视图的使用和表的使用是一致的,都支持增删改查,而且语法一样
2、如果视图的查询语句中包含一些特殊关键字,则不支持增删改
分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
二、修改视图
#方式1.如果视图存在,则修改;如果视图不存在,则创建
CREATE OR REPLACE VIEW 视图名
AS
新的查询语句
#方式2.修改视图
ALTER VIEW 视图名 AS
新的查询语句
三、删除视图
可以同时删除多个视图。
语法:drop view 视图1,视图2;
*/
#一、创建视图
CREATE VIEW myv1
AS
SELECT * FROM beauty
WHERE id>3;
#二、使用视图
#1.查询
SELECT * FROM myv1;
SELECT * FROM beauty;
#2.插入
INSERT INTO myv1 VALUES(14,‘罗大磊’,‘男’,‘1998-1-1’,‘111’,NULL,NULL);
INSERT INTO myv1 VALUES(2,‘马小宁’,‘男’,‘1998-1-1’,‘111’,NULL,NULL);
#3.更新
UPDATE myv1 SET NAME=‘宋茜’ WHERE id=6;
#4 .删除
DELETE FROM myv1 WHERE id=14;
#具备以下特点的视图不允许更新
/*包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
*/
#① 关键字
CREATE VIEW myv2
AS
SELECT COUNT(*),boyfriend_id
FROM beauty
GROUP BY boyfriend_id;
SELECT * FROM myv2;
UPDATE myv2 SET boyfriend_id = 10 WHERE boyfriend_id=1;
#②常量视图
DROP VIEW myv3;
CREATE VIEW myv3
AS
SELECT ‘china’ c;
SELECT * FROM myv3;
UPDATE myv3 SET c=‘Jap’;
#③select中包含子查询
CREATE VIEW myv4
AS
SELECT (
SELECT MAX(boyfriend_id)
FROM beauty
) a;
SELECT * FROM myv4;
UPDATE myv4 SET a=100;
#④join update好用 但是insert 不好用
CREATE VIEW myv5
AS
SELECT NAME,boyname
FROM beauty b
JOIN boys bo ON b.boyfriend_id=bo.id;
SELECT * FROM myv5;
UPDATE myv5 SET NAME=‘安娜’ WHERE NAME=‘热巴’;
INSERT INTO myv5 VALUES(‘刘晓婷’,‘刘婷婷’);
#⑤from一个不能更新的视图
CREATE VIEW myv6
AS
SELECT * FROM myv5;
SELECT * FROM myv6;
INSERT INTO myv6 VALUES(‘刘晓婷’,‘刘婷婷’);
#⑥where子句的子查询引用了from子句中的表
2、视图的修改和删除
#修改视图
#方式一:
CREATE OR REPLACE VIEW myv1
AS
SELECT * FROM beauty;
SELECT * FROM myv1;
#方式二:
ALTER VIEW myv1
AS
SELECT id,NAME
FROM beauty;
#四、删除视图
#DROP VIEW 视图名1,视图名2,…
DROP VIEW myv1,myv2,myv3,myv4,myv5;
#五、查看视图结构
DESC myv1;
SHOW CREATE VIEW myv1;
五、存储过程
1、什么是存储过程?
存储过程:
事先经过编译并存储在数据库中的一段sql语句的集合。
使用好处:
1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率
2、创建存储过程和函数
(1)创建存储过程
create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body
(2)创建函数
create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body
proc_parameter:
[in|out|inout] param_name type
Func_paramter:
param_name type
Type:
任何有效的mysql数据类型
Characteristic:
language sql(默认,且推荐)
|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
Rountine_body:
有效的sql 过程语句
3、调用存储过程或函数
(1)调用存储过程
call 存储过程名(参数列表)
(2)调用函数
Select 函数名(参数列表)
4、修改存储过程或函数
修改存储过程:
alter procedure 存储过程名 [charactristic…]
修改函数:
alter function 函数名 [charactristic…]
characteristic:
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
5、删除存储过程或函数
说明:一次只能删除一个存储过程或者函数,并且要求有该过程或函数的alter routine 权限
删除存储过程:
drop procedure [if exists] 存储过程名
删除函数:
drop function [if exists] 函数名
6、查看存储过程或函数
1.查看存储过程或函数的状态:
show {procedure|function} status like 存储过程或函数名
2.查看存储过程或函数的定义:
show create {procedure|function} 存储过程或函数名
3.通过查看information_schema.routines了解存储过程和函数的信息(了解)
select * from rountines where rounine_name =存储过程名|函数名
sqlyog 这个第三方客户端,不支持BEGIN 和 END,但是navicat这个第三方客户端支持begin和end
存储过程是不能修改的,非要修改,只能删除了,重新建
#存储过程
#一、创建
#语法:
#CREATE PROCEDURE 存储过程的名称(【IN/OUT/INOUT】 参数名 参数类型)
#BEGIN
主体
#END
#类似于java的方法
#修饰符 返回类型 方法名(参数列表){
方法体
#}
###1.参数对应的in、out、inout的意思
#IN 代表该参数 作为输入(传统意义的参数)
#OUT 代表该参数 作为输出 (传统意义的返回值)
#INOUT 代表该参数既可以作为输入又可以作为输出 (参数+返回)
#支持:多个IN、多个OUT、多个INOUT
#支持:无参
###2.主体
#增删改查语句都可以、变量、循环、条件等
#每条SQL语句结尾分号
#DELIMITER 结束符
#默认结束符:分号,假如不想分号结尾,则可以自己设置
#二、调用存储过程
/*
①调用无参的存储过程
CALL 存储过程名()
②调用in类型参数的存储过程
CALL 存储过程名(常量值)
③调用out类型参数的存储过程
call 存储过程名(@变量名)
s
e
l
e
c
t
@
变
量
名
select @变量名
select@变量名
④调用inout类型参数的存储过程
set @变量名=值
C
A
L
L
存
储
过
程
名
(
@
变
量
名
)
CALL 存储过程名(@变量名)
CALL存储过程名(@变量名)
SELECT @变量名 $$
#三、用户变量:
定义语法: set @变量名 ;
赋值语法:
方式一:普通赋值
set @变量名:=值;或set @变量名=值;
select @变量名:=值;
方式二:通过查询结果为变量赋值
select 字段|表达式 into 变量名
from 表名 【where 条件】
六、标识列
#标识列
/*
又称为自增长列
语法:
AUTO_INCREMENT
特点:设置为标识列的字段,值不用手动插入,自动会有序列值,不用担心重复问题
①标识列字段的类型必须为数值型
②标识列字段必须为一个key
主键或唯一或外键
③一个表中至多有一个标识列
④ 如果用delete删除,标识列的值从断点开始
如果用truncate删除,标识列的值从1开始
七、流程控制
1、变量
#流程控制结构
/*
顺序
分支
循环
一、变量
二、分支
三、循环
*/
#一、变量
#1、用户变量
#声明并赋值语法:
SET @变量名=值;
或
SET @变量名:=值;
或
SELECT @变量名:=值;
#赋值:
方式一:
同上述三种
方式二:
SELECT 字段|常量|表达式 INTO 变量
【FROM 表】;
#使用语法:
SELECT @变量名;
#2、局部变量
#声明的语法:
DECLARE 变量名 类型 【DEFAULT 值】;
#赋值的语法:
方式一:
同上述三种
方式二:
SELECT 字段|常量|表达式 INTO 变量
【FROM 表】;
#使用语法:
SELECT 变量名;
#注意:用户变量和局部变量的区别
作用域 声明位置
用户变量 比较大 可以在BEGIN END 的外面也可以在里面
局部变量 只能在声明该变量的BEGIN END中 只能在BEGIN END 的里面
2、分支
#分支结构
#1、IF函数
#语法:
SELECT IF(10>3,‘哈哈’,‘嘎嘎’);
#应用在:BEGIN END 的里面和外面都行
#2、CASE END
#应用在:BEGIN END 的里面和外面都行
#3、CASE END
#类似于switch
#4、IF ELSEIF ELSE
#类似于多重IF
#语法:
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
ELSEIF 条件3 THEN 语句3;
ELSE 语句n;
END IF ;
3、循环
循环结构
#1、WHILE结构
#语法:
【标签:】WHILE 循环条件 DO
循环体;
END WHILE 【标签】;
#2、REPEAT结构
#语法:
【标签:】REPEAT
循环体;
UNTIL 结束条件 END REPEAT 【标签】;
#while和repeat的对比
执行顺序 循环条件 语法不同
#WHILE 先判断后执行 循环成立的条件 WHILE关键字
#REPEAT 先执行后判断 循环结束的条件 REPEAT关键字