数据库的设计:
1、多表之间的关系
一对一:例如:人和身份证,实现方式:可以在任意一方添加唯一外键指向另一方的主键
一对多:例如:部门和员工,实现方式:在多的一方建立外键,指向一的一方的主键
多对多:例如:学生和课程,实现方式:需要借助第三张中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
2、数据库设计范式
概念:设计数据库时,需要遵循的一些规范,要遵循后边的范式要求,必须先遵循钱包的所有范式要求
分类:1、第一范式:每一列都是不可分割的原子数据项
2、第二范式:在第一范式的基础上,非码属性必须完全依赖于候选码,消除部分依赖
函数依赖:如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖A
完全函数依赖:如果A是一个属性组,则B属性值得确定需要依赖A属性组中所有的属性值
部分函数依赖:如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中的某一些值即可
传递函数依赖:如果通过A属性(属性组)的值,可以确定唯一B属性的值,通过B属性(属性组)的值可以确定唯一C属性的值
码:如果在一张表中,一个属性或属性组,被其他属于属性所完全依赖,则称这个属性(属性组)为该表的码
3、第三范式:在第二范式的基础上,任何非主属性不依赖于其他非主属性,消除传递依赖
数据库的备份和还原:
命令行:备份 :mysqldump -u用户名 -p密码 数据库名称 > 保存路径
还原:1、登陆数据库
2、创建数据库
3、使用数据库
4、执行文件。source 文件路径
多表查询:
笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况
要完成多表查询,需要消除无用的数据
多表查询的分类:
1、内连接查询
隐式内连接:使用where条件消除无用数据
例如:select 列名组 from 表名1,表名2 where 表名1.列 = 表明2.列名
显式内连接:
select 列名组 from 表名1 inner join 表名2 on 表名1.列 = 表名2 .列
内连接查询:1、从哪些表中查询数据
2、条件是什么
3、查询哪些字段
2、外连接查询
左外连接:
select 字段列表 from 表1 left outer join 表2 on 条件;
查询的是左表所有数据以及其交集部分
右外连接:
select 字段列表 from 表1 right outer join 表2 on 条件
查询的是右表所有数据以及其交集部分
3、子查询
概念:查询中嵌套查询
子查询的结果是单行单列的
子查询可以作为条件,使用运算符其判断
例如:select *from emp where emp.salary < (select avg(aslary) from emp)
子查询的结果是多行单列的
例如:select * from emp where dept_id in(select id from dept where name = 财务部 or name = 市场部)
子查询的结果是多行多列的
这里的子查询作为一张虚拟表
例如:select *from dept t1 ,(select *from emp where emp.join_date >“2011-11-11”)t2 where t1.id = t2.dept_id;
事务:
基本介绍:如果一个包含多个步骤的业务操作,被一个事务管理,要么都成功,要么都失败
事务的操作:1、开启事务,start transaction;
2、回滚,rollback;
3、提交,commit;
例如:start transaction ;
update account set balance = balance -500 where name = zhang;
update account set balance = balance + 500 where name = li;
commit;
rollback;
在MySQL数据库中事务默认自动提交
一条DML语句会自动提交一次事务,开启事务后,需要手动提交
修改事务的默认提交方式:select @@ autocommit; 1代表自动提交,0代表手动提交
set @@ autocommit = 0;修改为手动提交
四大特征:1、原子性,是不可分割的最小操作单位
2、持久性,事务提交或者回滚后,数据库会发生持久化的保持数据
3、隔离性,多个事务之间,相互独立
4、一致性,事务操作前后数据总量不变
隔离级别:多个事务之间隔离的,相互独立的,但是如果多个事务操作同一批数据,则会发生一些问题,设置不同的隔离级别就可以解决这些问题
存在的问题:1、脏读:一个事务读取到另一个事务中,没有提交的数据
2、不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
3、幻读 :一个事务操作数据表中所有的记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别分类:1、read uncommitted:读未提交,会产生脏读,虚读,幻读
2、read committed :读已提交,会产生虚读,幻读
3、repeatable read :可重复读,会产生幻读(MySQL 默认级别)
4、serializable:串行化,都不产生
注意:隔离级别从小到大,安全性越来越高,效率越来越低
数据库设置隔离级别:查询隔离级别 select @@ tx_isolation;
设置隔离级别 set global transaction isolation level 级别字符串
DCL:
管理用户:
1、添加用户:create user ‘用户名’@‘主机名’ identified by ‘密码’;
2、删除用户:drop user ‘用户名’@‘主机名’;
3、修改用户密码:update user set password = password(’新密码‘)where uesr = ’用户名‘;
set password for ’用户名‘@’主机名‘ =password(’新密码‘);
4、查询用户:use mysql ;select * from user;
注意:主机名可以用通配符%,代表可以远程访问
权限管理:
1、查询权限:show grants for ’用户名‘@’主机名‘;
2、授予权限:grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’
grant all on *.* to ‘用户名’@‘主机名’;授予所有表的所有权限
3、撤销权限:revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;