MySQL_进阶
约束
约束：对表中的数据进行限定，保证数据的正确性、有效性和完整性。 分类： 1.主键约束:primary key 注意： 1.含义：非空且唯一 2.一张表只能有一个字段为主键 3.主键就是表中记录的唯一标识 a.在创建表时添加主键约束,如:create table stu(id int prinmary key,name varchar(20)); b.删除主键:alter table stu drop primary key; c.创建表后添加主键:alter table stu modify id int primary key; d.自动增长:如果某一列是数值类型的，使用auto_increment可以来完成自动增长 1.创建表时添加,如:create table stu(id int auto_increment,name varchar(20)); 2.删除:alter table stu modify id int; 3.创建表后添加:alter table stu modify id int auto_increment; 2.非空约束:not null 1.在创建表时添加约束 在数据类型后直接写 not null 如：create table stu(id int,name varchar(20) not null); 2.创建表后，改变表，如：alter table stu modify name varchar(20) not null; 3.唯一约束:unique值不能重复 1.在创建表时添加约束 2.删除唯一约束:alter table stu drop index 列名; 3.在创建表后添加唯一约束:alter table stu modify 列名 数据类型 unique; 4.外键约束:foreign key :让表与表产生关系 a.在创建表时添加外键 例:create table 表名( ... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) ); b.删除外键:alter table 表名 drop foreign key 外键名 c.创建表之后:alter table 需要加外键的表名 add constraint 外键名 foreign key (外键列名称) references 主表名称(主表列名称) 5.级联操作: alter table 需要加外键的表名 add constraint 外键名 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade on delete cascade;
级联在外键的基础上添加条件 完成两表直接的同时修改或同时删除 分类：（功能，有了之后改一个级联都改，删一个级联都删） 1.级联更新 on update cascade 2.级联删除 on delete cascade
数据库的设计与备份还原
数据库的设计 1.多表之间的关系 分类： 1.一对一 2.一对多或多对一，如部门与员工的关系(一个部门有多个员工) 3.多对多的关系，如：学生和课程 实现关系: 1.一对多或多对一: 在多的一方建立外键，指向一的一方的主键 2.多对多: 多对多的实现关系需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键 3.一对一（不常用，因为可以合成一张表）: 可以在任意一方添加唯一外键（unique）指向另一方的主键 2.数据库设计的范式 设计数据库时需要遵循的一些规范 a.第一范式:每一列都是不可分割的原子数据 每一列不可分割成多个子列 存在的问题: 1.数据冗余 2.数据添加存在添加失败 3.数据删除会删除其他数据 b.第二范式:第一范式+非码属性必须完全依赖于码（消除非主属性对码的部分函数依赖） 1.函数依赖:a--->b，如果通过a属性的值(属性组)可以确定唯一b的值，则称b依赖于a 如：学号--->姓名；(学号，课程名称)--->课程成绩 -- 其中括号内的称为属性组 2.完全函数依赖:a--->b，如果a是一个属性组，则b属性值的确定需要依赖a属性组中的所有属性 3.部分函数依赖:a--->b，如果a是一个属性组，则b属性值的确定只需要依赖a属性组中的某一些值即可 4.传递函数依赖:a--->b，b--->c，如果通过a属性（属性组）的值， 可以确定唯一b属性的值，再通过b属性（属性组）的值，可以确定唯一c属性值，则称c传递函数依赖于a 5.码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码 主属性：码属性组中的所有属性 非主属性：除过码属性组的属性 C.第三范式:第二范式+任何非主属性不依赖于其他非主属性（消除传递依赖） 数据库的备份还原 1.命令： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径 source 保存路径 恢复数据库内容 2.图形化：...
多表查询
* 查询语法： select 列名列表 from 表名列表 where.... * 示例表 # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); # 创建员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), -- 性别 salary DOUBLE, -- 工资 join_date DATE, -- 入职日期 dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) ); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1); * 笛卡尔积： * 有两个集合A,B .取这两个集合的所有组成情况。 * 要完成多表查询，需要消除无用的数据 * 多表查询的分类： 1. 内连接查询： 1. 隐式内连接：使用where条件消除无用数据 * 例子： -- 查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 查询员工表的名称，性别。部门表的名称 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`; SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`; 2. 显式内连接： * 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件 * 例如： * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`; 3. 内连接查询： 1. 从哪些表中查询数据 2. 条件是什么 3. 查询哪些字段 2. 外链接查询： 1. 左外连接： * 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件； * 查询的是左表所有数据以及其交集部分。 * 例子： -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`; 2. 右外连接： * 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件； * 查询的是右表所有数据以及其交集部分。 * 例子： SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`; 3. 子查询： * 概念：查询中嵌套查询，称嵌套查询为子查询。 -- 查询工资最高的员工信息 -- 1 查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; -- 2 查询员工信息，并且工资等于9000的 SELECT * FROM emp WHERE emp.`salary` = 9000; -- 一条sql就完成这个操作。子查询 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp); * 子查询不同情况 1. 子查询的结果是单行单列的： * 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= = * -- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp); 2. 子查询的结果是多行单列的： * 子查询可以作为条件，使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'; SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2; -- 子查询 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'); 3. 子查询的结果是多行多列的： * 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
事务
1. 事务的基本介绍 1. 概念： * 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。 2. 操作： 1. 开启事务： start transaction; 2. 回滚：rollback; 3. 提交：commit; 3. 例子： CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); SELECT * FROM account; UPDATE account SET balance = 1000; -- 张三给李四转账 500 元 -- 0. 开启事务 START TRANSACTION; -- 1. 张三账户 -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2. 李四账户 +500 -- 出错了... UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- 发现执行没有问题，提交事务 COMMIT; -- 发现出问题了，回滚事务 ROLLBACK; 4. MySQL数据库中事务默认自动提交 * 事务提交的两种方式： * 自动提交： * mysql就是自动提交的 * 一条DML(增删改)语句会自动提交一次事务。 * 手动提交： * Oracle 数据库默认是手动提交事务 * 需要先开启事务，再提交 * 修改事务的默认提交方式： * 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交 * 修改默认提交方式： set @@autocommit = 0; 2. 事务的四大特征： 1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。 2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。 3. 隔离性：多个事务之间。相互独立。 4. 一致性：事务操作前后，数据总量不变 3. 事务的隔离级别（了解） * 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。 * 存在问题： 1. 脏读：一个事务，读取到另一个事务中没有提交的数据 2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。 3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。 * 隔离级别： 1. read uncommitted：读未提交 * 产生的问题：脏读、不可重复读、幻读 2. read committed：读已提交 （Oracle） * 产生的问题：不可重复读、幻读 3. repeatable read：可重复读 （MySQL默认） * 产生的问题：幻读 4. serializable：串行化 * 可以解决所有的问题 * 注意：隔离级别从小到大安全性越来越高，但是效率越来越低 * 数据库查询隔离级别： * select @@tx_isolation; * 数据库设置隔离级别： * set global transaction isolation level 级别字符串; * 演示： set global transaction isolation level read uncommitted; start transaction; -- 转账操作 update account set balance = balance - 500 where id = 1; update account set balance = balance + 500 where id = 2;
浙公网安备 33010602011771号