老杜MySQL视频学习记录_笔记
本文是MySQL数据库笔记，是动力节点教学总监杜老师讲述。
视频地址：https://www.bilibili.com/video/BV1fx411X7BD?p=1
文章目录
一、SQL语句分类
DQL：数据查询语句(Data Query Language)
select
DML：数据操作语言(Data Manipulation Language)
对表中的数据进行增删改
insert delete update
主要操作的是表中的数据
DDL：数据定义语言(Data Definition Language)
create、drop、alter
DDL主要操作的表的结构，不是表中的数据。
TCL：事务控制语言(Transactional Control Language)
事务提交：commit
事务回滚：rollback
DCL：数据控制语言(Data Control Language)
授权：grant
撤销：revoke
二、常用命令
1.查看MySQL版本：
mysql -V
mysql --version
select version();
2.创建数据库
create database <数据库名称>;
使用：use <数据库名>;
3.查看当前使用的数据库
select database();
4.终止一条语句
键入\c
5.退出mysql
exit、quit
三、查看表结构
1.查看现有的数据库
show databases;
2.查看当前缺省的数据库
use <database name>;
3.查看当前使用的数据库
select database();
4.查看当前库中的表
show tables;
5.查看其他库中的表
show tables from <database name>;
6.查看表结构
desc <table name>;
7.查看表的创建语句
show create table <table name>;
四、简单的查询
1.查询一个字段
select <field> from <table name>;
2.查询多个字段
select <field1>,<field2> from <table name>;
3.查询全部字段
select * from <table name>;
4.计算员工的年薪
select empno, ename, sal*12 from emp;
5.起别名
select empno as '员工编号', ename as '员工姓名', sal*12 as '年薪' from emp;
注意:字符串必须添加单引号 | 双引号
五、条件查询(where)
1.等号操作
查询工资=5000的员工
select empno,ename,sal from emp where sal=5000;
查询字符串必须加上引号
查询job="manager"的员工
select empno, ename from emp where job="manager";
2.<>符操作
查询薪水不等于5000的员工
select empno,ename from emp where sal<>5000;
3.between…and…操作符
查询薪水为1600到3000的员工(第一种方式，采用>=和<=)
select empno,ename from emp where sal>=1600 and sal<=3000;
查询薪水为1600到3000的员工(第一种方式，采用between … and …) 闭区间
select empno,ename from emp where sal between 1600 and 3000;
4.is null
空和空字符串不是一回事，null必须用is来比较
select empno,ename from emp where comm is null;
5.and
工作岗位为MANAGER,薪水大于2500的员工
select * from emp where job="manager" and sal>2500;
6.or
太简单了。略。
7.表达式的优先级
查询薪水大于1800，并且部门代码为20或30的员工
select * from emp where sal>1800 and (deptno=20 or deptno=30);
没把握尽量用括号
8.in
in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些
查询出job为manager或者job为salesman的员工
select * from emp where job in("manager","salesman");
9.not
查询出薪水不包含1600和薪水不包含3000的员工
select * from emp where sal<>1600 and sal<>3000;
select * from emp where not(sal=1600 or sal=3000);
select * from emp where sal not in(1600,3000);
查出津贴不为null的所有员工
select * from emp where comm is not null;
10.like
Like可以实现模糊查询，like支持%和下划线匹配
%匹配任意字符出现的个数
下划线只匹配一个字符
Like 中的表达式必须放到单引号中|双引号中
查询姓名以M开头的所有员工
select * from emp where ename like "M%";
查询姓名以N结尾的所有的员工
select * from emp where ename like "%N";
查询姓名中包含O的所有的员工
select * from emp where ename like "%o%";
查询姓名中第二个字符为A的所有员工
select * from emp where ename like "_a%";
六、排序数据
1.单一字段排序
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。
按照薪水由小到大排序(系统默认由小到大)
select * from emp order by sal;
取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)
select * from emp where job="manager" order by sal;
按照多个字段排序，如：首先按照job排序，再按照sal排序
select * from emp order by job,sal;
2.手动指定顺序排序
手动指定按照薪水由小到大排序
select * from emp order by sal asc;
手动指定按照薪水由大到小排序
select * from emp order by sal desc;
3.多个字段排序
按照job和薪水倒序
select * from emp order by job desc,sal desc;
4.使用字段的位置来排序
按照薪水升序 不建议使用位置
select * from emp order by 6;
七、数据处理函数/单行处理函数
| 函数名 | 作用 |
|---|---|
| count | 求和 |
| avg | 取平均数 |
| max | 取最大的数 |
| min | 取最小的数 |
| lower | 转换小写 |
| upper | 转换大写 |
| substr | 取子串(substr(被截取的字符串，起始下标，截取的长度)) |
| length | 取长度 |
| trim | 去空格 |
| str_to_date | 将字符串转换成日期 |
| data_format | 格式化日期 |
| format | 设置千分位 format(数字,‘格式’) |
| round | 四舍五入 |
| rand() | 生成随机数 |
| ifnull | 可以将null转换成一个具体的值 |
注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm这个字段中不为空的元素总数。
注意：分组函数不能直接使用在where关键字后面。
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
1.count
统计该字段下所有不为NULL的元素的总数
取得所有的员工数
select count(*) from emp;
取得津贴不为null员工数
select count(comm) from emp;
取得工作岗位的个数
select count(distinct job) from emp;
2.sum
sum可以取得某一个列的和，null会被忽略
取得薪水的合计
select sum(sal) from emp;
取得津贴的合计
select sum(comm) from emp;
取得薪水的合计(sal+comm)
select sum(sal+IFNULL(comm,0))) from emp;
3.substr
找出员工名字第一个字母是A的员工信息
select ename from emp where substr(ename,1,1) ="A";
4.trim去前后空格
select ename from emp where ename=trim(" king");
5.round四舍五入
select round(12345.678,1) from emp;
6.ifnull
select ename,(sal+ifnull(comm,0))*12 from emp;
7.case…when…then…when…then…else…end
当员工的工作岗位是MANAGER的时候，工资上调10%,当工作岗位是SALESMAN的时候，工资上调50%，其他正常。 select不会修改原来的数据
select ename,job,(case job when "manager" then sal*1.1 when "salesman" then sal*1.5 else sal end) as newsal from emp;
八、分组函数
必须先进行分组才能使用，否则就是整张表。
分组查询主要涉及到两个子句，分别是：group by 和 having
1.group by
取得每个工作岗位的工资合计，要求显示岗位名称和工资合计?
select job,sum(sal) from emp group by job;
找出每个部门最高薪资大于3000的？
select deptno,max(sal) from emp where sal>3000 group by deptno;
2.having
如果想对分组数据再进行过滤需要使用 having 子句
取得每个岗位的平均工资大于 2000 ？
select job,avg(sal) from emp group by job having avg(sal)>2000;
3.distinct
去除重复记录。如果出现在所有字段的前方表示所有字段联合起来去除重复记录？
select distinct job from emp;
统计工作岗位的数量？
select count(distinct job) from emp;
大总结（单表查询）
执行顺序？
- from 从某张表中查询数据
- where 先经过where条件筛选出有价值的数据
- group by 对这些有价值的数据进行分组
- having 分组之后可以使用having进行筛选
- select select查询出来
- order by 最后排序输出
找出每个岗位的平均薪资，要求显示薪资大于1500的并保留1位小数，除MANAGER岗位之外，要求按照平均薪资降序排
select job,round(avg(sal),1) avgsal from emp where job<>"manager" group by job having avg(sal)>1500 order by avgsal desc;
九、连接查询
连接查询：也可以叫跨表查询，需要关联多个表进行查询
根据表连接的方式：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接(左连接)
右外连接(右连接)
全连接(略)
如果多表查询没有加条件，就会出现出笛卡尔积现象
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。
1.内连接之等值连接
查询每个员工所在的部门名称，显示员工名和部门名？
sql92语法
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
sql99语法
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选条件都放到了where后面
sql99的优点：表连接的条件是独立的，连接之后如果还需要进一步的筛选，再往后添加where条件
2.内连接之非等值连接
找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
3.内连接之自连接
查询员工的上级领导，要求显示员工名和对应的领导名？
select e.ename '员工名',m.ename '领导名' from emp e join emp m on m.mgr=e.empno;
4.外连接(右外连接)
在外连接当中两张表产生了主次关系
right代表什么: 表示将右边的这张表看成主表，主要是为了将右边这张表的数据全查出来，捎带着关联左边的表。
查询所有部门对应的员工姓名？
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
5.外连接(左外连接)
带有left的是左外连接
查询每个员工的上级领导，要求显示所有员工的名字和领导名？
select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr=b.empno;
6.三张表、四张表连接
语法：
select … from a
join b on a和b的连接条件
left join c on a 和c的连接条件
join d on a和d的连接条件;
找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级、领导名字？
select e.ename,d.dname,e.sal,s.grade,l.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between losal and hisal left join emp l on e.mgr=l.empno;
十、子查询
子查询就是嵌套的 select 语句，可以理解为子查询是一张表
1.子查询出现在哪里？
select
…(select)
from
…(select)
where
…(select)
2.where子句中的子查询
找出比最低工资高的员工姓名和工资？
select ename,sal from emp where sal > (select min(sal) from emp);
3.from子句中的子查询
注意：from后面的子查询，可以将子查询的结果当成一张临时表。
找出每个岗位的平均薪资的薪资等级
select t.job,s.grade,t.a from (select job,avg(sal) a from emp group by job) t join salgrade s on t.a between losal and hisal;
4.select后面出现的子查询
找出员工的部门名，要求显示员工名，部门名？
select e.ename,(select d.dname from dept d where d.deptno=e.deptno ) as dname from emp e;
十一、union、limit
union的效率更高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
1.union
查询工作岗位是manager或者salesman
select ename,job from emp where job="manager" or job="salesman";
select ename,job from emp where job in("manager","salesman");
select ename,job from emp where job="manager" union select ename,job from emp where job="salesman";
2.limit
limit是将查询结果集的一部分取出来
完整用法：limit startIndex,length
按照薪资降序，取出前5条数据
select ename,sal from emp order by sal desc limit 0,5；
3.分页
每页显示3条记录
第1页： limit 0,3 [0 1 2]
第2页： limit 3,3 [3 4 5]
第3页： limit 6,3 [6 7 8]
每页显示pasgeSize条记录
第pageNo页： limit (pageNo-1)*pageSize,pagesize;
十二、表
1.表的创建(DDL)
语法格式
create table tableName(
columnName dataType(length),
………………
columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';
2.关于MySQL中的数据类型
部分常用类型：
| 类型 | 描述 |
|---|---|
| char(长度) | 定长字符串，存储空间大小固定，适合作为主键或外键 |
| varchar(长度) | 变长字符串，存储空间等于实际数据空间 |
| double(有效数字位数,小数位) | 数值型 |
| float(有效数字位数,小数位) | 数值型 |
| int(长度) | 整数 |
| bigint(长度) | 长整型 |
| Date | 日期型 |
| BLOB | Binary Large Object（二进制大对象） |
| CLOB | Character Large Object (字符大对象) |
3.创建一个学生表
学号、姓名、年龄、性别、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1) default('m'),
age int(3),
email varchar(255)
);
4.插入数据insert (DML)
语法格式：
inser into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3);
注意：
字段名和值要一一对应
insert语句但凡执行成功，那么必然会多一条记录
字段名省略的话，就相当于都写上了~ 所以值也要都写上！
insert into t_student(no,name,sex,age,email) values(1,'jack','m',21,'test@163.com');
insert into t_student values(2,'tom','m',22,'test1@163.com');
一次插入多条记录：
insert into t_student values(2,'tom','m',22,'test1@163.com'),(3,'zs','m','9','test2@163.com');
将查询结果插入到一张表当中
create table dept_bak as select * from dept;
查询结果必须符合表的结构
insert into dept_bak select * from dept;
5.insert插入日期
数字格式化：format(数字,‘格式’)
str_to_date: 将字符串varchar类型转换为date类型,具体格式 str_to_date (字符串，匹配格式)
date_format:将date类型转换成具有一定格式的字符串类型
create table t_user(
id int,
name varchar(32),
birth date
);
插入数据
insert into t_user(id,name,birth) values(1,'jack',str_to_date('12-06-2021','%d-%m-%Y'));
如果输入的字符串是YYYY-mm-dd,还可以自动转换
insert into t_user(id,name,birth) values(2,'tom','2021-6-12');
查询的时候还可以以某个特定的日期格式展示
date_format
select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
6.date和datetime两个类型的区别
date是短日期：只包括年月信息
datetime是长日期：包括年月日时分秒信息
drop table if exists t_user;
create table t_user(
id int,
name varchar(32),
birth date,
create_time datetime
);
mysql短日期默认格式：%Y-%m-%d
mysql长日期格式：%Y-%m-%d %h:%i:%s
now()函数可以获取当前系统时间，是datetime类型的格式
insert into t_user(id,name,birth,create_time) values(1,'jack','2021-6-12','2021-6-12 19:50:22');
insert into t_user(id,name,birth,create_time) values(2,'tom','2021-6-12',now());
7.修改update语句(DML)
语法格式：
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
update t_user set name="zhangsan",birth="2020-01-01" where id=1;
注意：如果不加where条件默认更新表中的所有数据。
8.删除delete语句(DML)
语法格式：
delete from 表名 where 条件
delete from t_user where id=2;
注意：如果不加where条件默认删除表中的所有数据。
9.表快速复制
原理：
将一个查询结果当做一张表创建
这个可以完成表的快速复制
表创建出来，同时表中的数据也存在了
create table t_user_bak as select * from t_user;
10.删除表
drop table t_student; //如果存在就删除
drop table if exists t_student; //如果不存在就不会报错
11.快速删除表中的数据
不支持回滚
truncate table 表名;
12.对表结构的增删改查
采用 alter table 来增加/删除/修改表结构，不影响表中的数据
一般不用，实际开发中，需求一旦确定之后，表结构确定之后，很少进行表的修改。因为开发进行中的时候，修改表结构，成本比较高。这里简单了解一下，如果修改结构可以用可视化工具。
1.添加字段
如需求发生改变，需要向t_user中加入联系电话字段，字段名称为：contact_tel类型为varchar(40)
alter table t_user add contact_tel varchar(40);
2.修改字段
如：name 无法满足需求，长度需要更改为 100
alter table t_user modify name varchar(100);
如 sex 字段名称感觉不好，想用 gender 那么就需要更爱列的名称
alter table t_user change sex gender char(2) not null;
3.删除字段
如：删除联系电话字段
alter table t_user drop contact_tel;
十三、约束
约束对应的单词：constraint
在创建表的时候，我们可以给表中的字段加上一些约束，来保证表中的数据完整性、有效性
约束的作用就是为了保证：表中的数据有效！！
1.常见的约束？
非空约束：not null
唯一性约束：unique
主键约束：primary key
外键约束：foreign key
检查约束：check(mysql不支持，oracle支持)
1.1非空约束：not null
非空约束not null约束的字段不能为NULL。
drop table if exists t_user;
create table t_user(
id int,
name varchar(255) not null
);
insert into t_user(id,name) values(1,'zhangsan');
insert into t_user(id,name) values(2,'lisi');
---------------------------------------------------
insert into t_user(id,name) values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
1.2唯一性约束：unique
唯一性约束的unique约束的字段不能重复，但是可以为null
drop table if exists t_user;
create table t_user(
id int,
name varchar(255) unique
);
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'ls');
insert into t_user(id) values(3);
insert into t_user(id) values(4);
--------------------------------------------------------
insert into t_user(id,name) values(3,'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'name'
name和email联合起来具有唯一性
如果约束没有添加到字段后面，成为表级约束
drop table if exists t_user;
create table t_user(
id int,
name varchar(255),
email varchar(255),
unique(name,email)
);
insert into t_user(id,name,email) values(1,'zhangsasn','123@qq.com');
insert into t_user(id,name,email) values(2,'zhangsasn','1234@qq.com');
-------------------------------------------------------------------
insert into t_user(id,name,email) values(2,'zhangsasn','1234@qq.com');
ERROR 1062 (23000): Duplicate entry 'zhangsasn-1234@qq.com' for key 'name'
在mysql中，如果一个字段同时被not null和unique约束，自动变成主键字段。
1.3主键约束：primary key
相关术语：主键约束、主键字段、主键值
主键值是每一行记录的唯一标识
任何一张表都应该有主键，建议使用int、bigint、char等类型
主键值不能是null同时也不能重复
drop table if exists t_user;
create table t_user(
id int primary key,
name varchar(255)
);
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'ls');
---------------------------------------------------------
insert into t_user(id,name) values(1,'wz');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
可以俩主键联合起来做表级约束，这样的主键叫复合主键，比较复杂。建议单一主键。
主键除了单一主键和复合主键之外，还可以这样分类：
自然主键：主键值是一个自然数，和业务没关系
单一主键：主键值和业务紧密相连，例如银行卡号。
主键值可以采用auto_increment自动维护
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
name varchar(255)
);
insert into t_user(name) values('zs');
insert into t_user(name) values('ls');
select * from t_user;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | ls |
+----+------+
1.4外键约束：foreign key
相关术语：外键约束、外键字段、外键值
子表中的一个字段引用父表中的一个字段。如果删除表，应该先删子表。
这里学生表中的cno字段引用班级表中的classno字段
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
id int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);
insert into t_class(classno,classname) values(100,'某高级中学1班');
insert into t_class(classno,classname) values(101,'某高级中学2班');
insert into t_student(name,cno) values('zhhangsan',100);
insert into t_student(name,cno) values('lisi',100);
insert into t_student(name,cno) values('wangwu',100);
insert into t_student(name,cno) values('tom',101);
insert into t_student(name,cno) values('jack',101);
insert into t_student(name,cno) values('jreey',101);
------------------------------------------------------------
insert into t_student(name,cno) values('wangwu',109);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`))
字表中的外键引用父表中的某个字段，被引用的字段不一定是主键，至少具有unique约束(唯一性)。
外键值可以为null。
2.增加/删除/修改表约束
2.1删除约束
删除外键约束：alter table 表名 drop foreign key 外键 (区分大小写);
删除主键约束：alter table 表名 drop primary key;
删除约束：alter table 表名 drop key 约束名称;
2.2添加约束
添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);
添加主键约束：alter table 表 add constraint 约束名称 primary key 表(主键字段);
添加唯一性约束：alter table 表 add constraint 约束名称 unique 表(字段)
2.3修改约束，其实就是修改字段
alter table t_student modify student_name varchar(30) unique;
十四、存储引擎(了解)
存储引擎是MySQL中特有的术语。
存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。
数据库中的各表均被（在创建表时）指定的存储引擎来处理。
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
在建表的时候可以在最后的")"的右边使用：
engine来指定存储引擎 。默认是InnoDB
charset来指定这张表的字符编码方式。默认是：utf-8
为了解当前服务器中有哪些存储引擎可用，可使用 SHOW ENGINES 语句 。
1.常用存储引擎
MyISAM
它管理的表具有以下特征：
– 使用三个文件表示每个表：
• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）
– 灵活的 AUTO_INCREMENT 字段处理
– 可被转换为压缩、只读表来节省空间
InnoDB
它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新
MEMORY
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非 常快。
• MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
• MEMORY 存储引擎以前被称为 HEAP 引擎
2.选择合适的存储引擎
• MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只 读表。
• 如果查询中包含较多的数据更新操作，应使用 InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合 操作提供了良好的并发机制。
• 可使用 MEMORY 存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。
十五、事务(重点)
事务就是一个完整的业务逻辑，是一个最小的工作单元。
假如转账：从A账户向b账户转10000.
将A账户的钱减去10000(update语句)
将B账户的钱加上10000(update语句)
这就是一个完整的业务逻辑。
以上就是最小的业务单元，不可再分。要么同时成功，要么同时失败。
只有DML语句才有事务：insert update delete。一旦涉及增删改查就要考虑安全问题。
事务是怎么做到多条DML语句同时成功或同时失败的呢？
InnoDB存储引擎： 提供一组用来记录事务性活动的日志文件
事务开启：
insert
insert
delete
update
insert
事务结束！
在事务的执行过程中,每一条DML的操作都会记录到“事务性活动的日志文件”。
我们可以提交事务，可以回滚事务。
提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。标志着事务全部成功结束。
回滚事务：将之前所有的DML操作全部撤销，并清空事务性活动的日志文件。标志着事务全部失败的结束。
2.提交/回滚事务
提交事务：commit; 语句 （MySQL默认自动提交事务）
回滚事务：rollback; 语句 （回滚永远只能回滚到上一次的提交点）
关闭自动提交事务
start transaction;
3.事务四个特性
A：原子性
说明事务是最小的工作单元。不可再分。
C：一致性
所有事物要求，在同一个事务中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
I：隔离性
A事务和B事务之间具有一定的隔离
D：持久性
失误最终结束的一个保障。事务提交，就相当于没有保存到硬盘上的数据保存到硬盘上。
隔离性
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
– 脏读取（Dirty Read） 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
– 不可重复读（Non-repeatable Read） 在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
–幻像读（Phantom Read） 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。
InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
– 读未提交（READ UMCOMMITTED） 允许一个事务可以看到其他事务未提交的修改。(最低的隔离级别)
– 读已提交（READ COMMITTED） 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
– 可重复读（REPEATABLE READ） 确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改。（银 行总账） 该隔离级别为 InnoDB 的缺省设置。
– 串行化（SERIALIZABLE） 【序列化】 将一个事务与其他事务完全地隔离。 (最高的隔离级别)
十六、索引
十七、视图
视图可以看成是一张新表。
视图修改的是表的原数据，可以直接操作表。
视图的创建
create view 视图名 as DQL语句;
可以对视图进行CRUD，修改的是表的数据。
十八、DBA命令
1.创建用户
CREATE USER username IDENTIFIED BY 'password';
2.授权
命令详解
mysql> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
1) dbname=*表示所有数据库
2) tbname=*表示所有表
3) login ip=%表示任何 ip
4) password 为空，表示不需要密码即可登录
5) with grant option; 表示该用户还可以授权给其他用户
细粒度授权
首先以 root 用户进入 mysql，然后键入命令：grantselect,insert,update,delete on *.* to p361 @localhost Identified by "123";
如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为 "%"。
粗粒度授权
我们测试用户一般使用该命令授权，
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;
privileges 包括：
1) alter：修改数据库的表
2) create：创建新的数据库或表
3) delete：删除表数据
4) drop：删除数据库/表
5) index：创建/删除索引
6) insert：添加表数据
7) select：查询表数据
8) update：更新表数据
9) all：允许任何操作
10) usage：只允许登录
3.回收权限
命令详解
revoke privileges on dbname[.tbname] from username;
revoke all privileges on *.* from p361;
use mysql
select * from user
进入 mysql 库中
修改密码;
update user set password = password('qwe') where user = 'p646';
刷新权限;
flush privileges
4.导出
导出整个数据库
在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
导出指定库下的指定表
在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123
5.导入
登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql;
十九、数据库设计的三范式
第一：要求任何一张表必须有主键，每一个字段原子性不可再分。
口诀：一对一，外键唯一。
第二：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
口诀：多对多，三张表，关系表，两个外键。
第三：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
口诀：一对多，两张表，多的表加外键。
扫一扫
223
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
