周到的网站建设,上海专业网站建站公,广州做营销网站公司,承德做网站约束
完整性约束
为防止不规范数据进入数据库中#xff0c;在用户对数据进行插入#xff0c;修改#xff0c;删除操作时#xff0c;MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件
约束条件约束描述PRIMARY KEY主键约束NOT NULL非空约束#xff0c;字段不…约束
完整性约束
为防止不规范数据进入数据库中在用户对数据进行插入修改删除操作时MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件
约束条件约束描述PRIMARY KEY主键约束NOT NULL非空约束字段不能为空UNIQUE唯一约束字段值唯一CHECK检查约束检查取值范围DEFAULT默认值约束提供默认值AUTO_INCREMENT自动增加约束约束字段的值自动递增FOREIGN KEY外键约束约束表与表之间关系
列级约束
CREATE TABLE t_student(
sno INT(4) PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(5) NOT NULL,
sex CHAR(1) DEFAULT 男 CHECK(sex男 || sex女),
eamil VARCHAR(15) UNIQUE
);表级约束
CREATE TABLE t_student(
sno INT(4),
sname VARCHAR(5),
sex CHAR(1),
eamil VARCHAR(15),
CONSTRAINT pk_stu PRIMARY KEY (sno),
CONSTRAINT ck_stu_sex CHECK (sex男||sex女),
);外键约束
外键约束是用来实现数据库表的参照完整性的可以使两张表紧密结合起来特别是针对修改或者删除的级联操作时会保证数据的完整性外键是指表中某个字段的值依赖于另一张表中某个字段值而被依赖的值必须具有主键约束或者唯一约束。被依赖的表称为父表或者主表设置外键约束的表称为子表或者从表
-- 创建主表
CREATE TABLE t_class(
cno INT(4) PRIMARY KEY auto_increment,
cname VARCHAR(10) NOT NULL,
room CHAR(4)
);-- 创建子表
CREATE TABLE t_student(
cno INT(4) PRIMARY KEY auto_increment,
sname VARCHAR(5) NOT NULL,
classno int(4)
)-- 设置外键约束 可以在创建表约束也可以后面添加
CREATE TABLE t_student(
cno INT(4) PRIMARY KEY auto_increment,
sname VARCHAR(5) NOT NULL,
classno int(4)
CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)
)
ALTER TABLE t_student ADD CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)外键策略
no action 不允许操作
-- 先将对应键改为null
UPDATE t_student set classno NULL WHERE classno 2
-- 再删除从表
DELETE FROM t_class WHERE cno 2cascade级联操作操作主表影响从表的外键信息
-- 删除已有的约束
ALTER TABLE t_student DROP FOREIGN KEY fk_stu_classno;
-- 添加新的再删除或者更改时将已有的置为null
ALTER TABLE t_student ADD CONSISTENT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno) on UPDATE CASCADE ON DELETE CASCADEset null置空操作
-- 删除已有的约束
ALTER TABLE t_student DROP FOREIGN KEY fk_stu_classno;
-- 置新添加外键约束
ALTER TABLE t_student ADD CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno) ON UPDATE CASCADE ON DELETE CASCADE;函数
MySQL中提供了大量的函数来简化用户对数据库的操作比如字符串处理日期的运算数值的运算
单行函数
单行函数是指对每一条记录输入值进行运算并得到相应的计算结果然后返回给用户也就是说每条记录作为参数传递计算得到每条记录返回值
SELECT empno,ename,LOWER(ename),UPPER(iob) FROM emp;
-- 转换大小写常用单行函数主要包括字段串函数数值函数日期与时间函数流程函数以及其他函数
多行函数
SELECT MAX(empno),MIN(empno),COUNT(empno),SUM(empno),AVG(empno) FROM emp;
-- 计算出总共的数据结果多表查询
需要对多张表进行查询而这多张表需要使用外键和主键关联在一起然后使用连接查询来查询多-张表中满足的要求连接查询类型 cross natural using on
-- 交叉连接
SELECT * FROM emp CROSS JOIN dept;
-- 缺点没有指定字段所属表效率低-- 指定来自哪个表
SELECT e.DEPTNO,e.IOB,d.LOC
FROM emp e
NATURAL JOIN dept d;-- 自然连接 natural join 缺点自动匹配表中所有同名列
-- 解决USING
SELECT *
FROM emp e
INNER JOIN dept d
USING(DEPTNO)-- using缺点关联的字段必须是同名的
-- 解决 内连接 ON
SELECT *
FROM emp e
INNER JOIN dept d
ON (e.deptno d.deptno)-- 内连接缺点只显示匹配信息
-- 外连接可以显示不匹配信息
-- 左外连接 左边全展示
SELECT *
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno d.deptno
-- 右外连接 右边全展示
SELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno d.deptno
-- 全外连接 全部展示MySQL 不支持
SELECT *
FROM emp e
FULL OUTER JOIN dept d
ON e.deptno d.deptno
-- 解决全外连接
SELECT *
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno d.deptno
UNION
SELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno d.deptnoSELECT *
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno d.deptno
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;子查询
单行子查询
执行多条select语句
-- 先执行括号里的在执行外面的
SELECT * FROM emp WHERE sal (SELECT sal FROM emp WHERE eanme jack)多行子查询
-- 查询deptno 20里的job 在deptno 10里面有的数据
SELECT * FROM emp
WHERE deptno 20
AND job in (SELECT job FROM emp WHERE deptno 10)-- 查询任意一个都小
SELECT *
FROM emp
WHERE sal ANY(SELECT sal FROM emp WHERE job clerk)
AND job ! clerk;事务
事务是用来维护数据库完整性的它能够保证一系列MySQL操作要么全执行要么全不执行
CREATE TABLE account(
id INT PRIMARY KEY auto_increment,
uname VARCHAR(10) NOT NULL,
balance DOUBLE
)SELECT * FROM account;INSERT INTO account VALUES (null,小明,2000),(NULL,丽丽,3000);-- 丽丽向小明转200
UPDATE account SET balance balance - 200 WHERE id 1;
UPDATE account SET balance balance 200 WHERE id 2;-- 手动开启事务
START TRANSACTION;
UPDATE account SET balance balance - 200 WHERE id 1;
UPDATE account SET balance balance 200 WHERE id 2;
-- 手动回滚 刚才操作回滚
ROLLBACK;
-- 手动提交
COMMIT;脏读
当一个事务正在访问数据并且对数据进行了修改而这种修改还没提交到数据库中这时另一个事务也访问了这个数据因为这个数据还没提交另一个事务访问到的是脏数据依据脏数据做的操作时不正确的
不可重复读
指在一个事务内多次读同一数据在这个事务还没结束时另一个事务也访问了数据在第一个事务多次读数据之间第二个的修改导致第一个事务两次读取不一样因此称为不可重复读
幻读
幻读与不可重复读类似它发生在一个事务读取了几行数据另一个事务插入一些数据时。在随后的查询中第一个事务就会发现多了一些原本不存在的记录
事务隔离
解决脏读不可重复读幻读问题
隔离级别脏读不可重读读幻读READ UNCOMMITTED√√√READ COMMITTED×√√REPEATABLE READ××√SERIALIZABLE×××
-- 查看默认事务隔离级别
SELECT transaction_isolation;-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;视图
将多个表整合到一个视图中这样只用关注数据结构而不用关注表之间联系
-- 创建视图 可以修改表中数据
CREATE VIEW myView01
AS
SELECT * FROM emp WHERE deptno 20;
-- 查看视图
SELECT * FROM myView01;-- 创建/替换多表视图 不能修改表中数据
CREATE OR REPLACE VIEW myView02
AS
SELECT * FROM emp JOIN dept on emp.DEPTNO dept.DEPTNO
WHERE sal 2000存储过程
将存储语句封装起来需要时调用
CREATE PROCEDURE myPro1(name VARCHAR(10))
BEGINif name IS NULL OR name THENSELECT * FROM emp;ELSESELECT * FROM emp WHERE ename LIKE CONCAT(%,name,%);END IF;
END;
-- 删除存储过程
DROP PROCEDURE myPro1;
-- 调用存储过程
CALL myPro1(R)-- FOUND_ROWS() 内置函数返回查询的条数
CREATE PROCEDURE myPro1(in name VARCHAR(10),out num INT(3))
BEGINif name IS NULL OR name THENSELECT * FROM emp;ELSESELECT * FROM emp WHERE ename LIKE CONCAT(%,name,%);END IF;SELECT FOUND_ROWS() INTO num;
END;
