MySQL数据库从入门到跑路(二)

| 分类于

前言

前一篇关于数据库的文章总结了所有的常用基本操作,此文将围绕雇员信息表(oracle 9i的经典测试表)以习题为主的讲解方式来总结一下MySQL的内置函数,复合查询,内外连接,最后将针对索引特性,事务管理,视图特性以及用户管理进行详细总结

雇员信息表

雇员信息表(oracle 9i的经典测试表)总共分为三张分表EMP员工表,DEPT部门表,SALGRADE工资登记表
表具体内容如下图

之后的很多内容都是围绕着这三张表展开学习的~

group by子句的使用

在select中使用group by子句可以对指定列进行分组查询

显示每个部门的平均工资和最高工资

每个部门为分组查询的指定列,显示他们的平均工资和最高工资

1
2
3
select deptno,avg(sal),max(sal) 
from emp 
group by deptno;

显示每个部门每种岗位的平均工资和最低工资

每个部门的每个岗位作为分组查询的指定列

1
2
3
select deptno,job,avg(sal),min(sal) 
from emp 
group by deptno,job;

显示平均工资低于2000的部门和他的平均工资

在这里多出来了一个条件,平均工资低于2000,在此之前我们遇到需要判定条件的情况都是使用where,在group by子句中有专用来判定条件的关键词having,用法和where基本一样

1
2
3
4
select deptno,avg(sal) as myavg 
from emp 
group by deptno 
having myavg<2000;

内置函数

MySQL像Java一样,也为我们提供了很多的方法,方便我们的查询工作

日期函数

当前日期:current_date()

获得年月日

1
select current_date();

当前时间:current_time()

获得时分秒

注意:当前日期指得是当前的日子,当前时间指的是此时的时间

当前时间戳:current_timestamp

获得时间戳

1
select current_timestamp();

返回datetime参数的日期部分:date(datetime)

假设我们有如下的表:

使用如下SQL语句

1
2
3
SELECT ProductName, DATE(OrderDate) AS OrderDate
FROM Orders
WHERE OrderId=1;

结果:

在date中添加时期或时间:date_add(date,interval d_value_type)

interval 后的数值单位可以是i:year,minute,second,day
在日期的基础上加日期

1
select date_add('2018 12-25',interval 10 day);

在date中减去日期或时间:date_sub(date,interval d_value_type)

在日期的基础上减去时间

1
select date_sub('2019-01-04',interval 10 day);

两个日期的差,单位是天:detediff(datel,date2)

计算两个日期之间相差多少天

1
select datediff('2019-01-04','2018-12-25');

要注意这里是第一个时间减去第二个时间,如果第一个时间小于第二个时间是会为负数的

当前日期时间:now()


返回结果和时间戳的一样

简单案例一:创建一个生日表

step1:创建一张表记录生日

1
2
3
4
create table bir(
	id int primary key auto_increment,
	birthday date
);

step2:以当期日期作为生日插入表中

1
insert into bir(birthday) values (current_date());

step3:查看

简单案例二:创建一个留言板

step1:创建一个留言板

1
2
3
4
5
create table msg(
	id int primary key auto_increment,
	content varchar(30) not null,
	sendtime datetime
);

step2:插入数据

1
2
insert into msg (content,sendtime) values('第一条留言',now()),('第二条留言',now());
insert into msg (content,sendtime) values('第三条留言',now());

step3:查看

显示所有留言信息,发布时间只显示日期,不显示时间

1
select content,date(sendtime) from msg;

查询在2分钟内发布的帖子

1
select * from msg where date_add(sendtime,interval 2 minute)>now();

在两分钟内发布的消息则要求发布时间加上两分钟大于我们当前的时间

字符串函数

charset(返回字符串的字符集)

获取emp表的ename列的字符集

concat(连接字符串)

要求显示student表中的信息,显示格式为:”xxx的语文是xxx分,数学xxx分”

1
select concat(name,'的语文是',chinese,'分','数学是',math,'分') as '分数' from student;

length(string)

求学生表中学生姓名占用的字节数

1
select length(name),name from student;

replace(字符串替换)

将emp表中所有名字中有S的替换成’上海’
select ename,replace(ename,’S’,’上海’) from emp;

substring(截取字符串)

截取emp表中ename字段的第二个到第三个字符

lcase(转换成小写)/ucase(转换成大写)

以首字母小写的方式显示所有员工姓名
思路:将人名的第一个字母摘出来变为小写并和从第二个字母处开始的字符串进行连接

1
select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;

数学函数

绝对值

1
select abs(-21);

向上取整

1
select ceiling(23.04);

向下取整

1
select floor(23.7);

保留两位小数(小数四舍五入)

1
2
-- 两个参数,第一个参数是原数,第二个参数是要保留的位数
select format(12.34576,2);

产生随机数

1
select rand();

rand()函数只能生成0-1之间的随机小数,如果想要生成0到10,0到100之间的就要rand()*相应的值

1
2
3
4
5
6
-- 生成0-100之间的小数
select format(rand()*100,2);

-- 生成0-100之间的整数(采用四舍五入的规则)
select format(rand()*100,2);
select floor(rand()*100);

其他函数

user()查询当前用户

查询结果是指用户从哪里来的

1
select user();

md5(str)对一个字符串进行md5摘要,摘要后得到一个32位字符串

摘要实际上就是一种该数据库的加密手段

显示当前正在使用的数据库

1
select database();

password()函数,MySQL数据库使用该函数对用户加密

1
select passwod('root');

ifnull(val1,val2) 如果val1为null,返回val2,否则返回val1的值

1
2
select ifnull('abc','265');
select ifnull(null,'22');

复合查询

多表查询

此节多以习题的形式来演示
在实际的应用开发中数据往往来自不同的表,所以需要多张表同时进行查询.我们使用一开始所导入的三张表进行下面的操作

显示雇员名,雇员工资以及所在部门的名字

按照题干要求我们写出如下的语句

1
2
3
-- 查询雇员名,雇员工资及部门编号从下列两个表中
select emp.ename,emp.sal,emp.deptno
from emp,dept;

此时结果:

我们可以看到每一个人的信息都显示了四次,这可不是我们需要的结果,我们想要的只是每一个人的信息依次显示一次,为什么会造成这样的结果呢?
这是因为如上的查询方式没有过滤方式,从两个表中查询的数据有重叠(两个表中都有deptno),于是数据库在进行查询操作时将两个表中的内容进行了集合运算,得到了一个笛卡尔积

为了解决如上问题,我们需要添加一个过滤条件,让emp表中的deptno=dept表中的deptno字段的记录
更改SQL语句,如下:

1
2
3
select emp.ename,emp.sal,emp.deptno
from emp,dept
where emp.deptno = dept.deptno

显示部门号为10的部门名,员工名和工资

显示各个员工的姓名,工资,及工资级别

1
2
3
select emp.ename,emp.sal,salgrade.grade
from emp,salgrade
where emp.sal between losal and hisal;

自连接

自连接是指在同一张表连接查询,即要查询的信息都在一张表中,且各种信息之间具有关联
案例:显示员工FORD的上级领导的编号和姓名
使用子查询的方式

1
select empno,ename from emp where emp.empno = (select mgr from emp where ename='FORD');

使用多表查询(自查询)

1
2
3
4
5
-- 使用到表的别名(这个别名是自己起的)
select leader.empno,leader.ename
from emp leader,emp worker
where leader.empno = worker.mgr
and worker.ename='FORD';

子查询

子查询是指嵌套在其他sql语句中的select语句,也叫嵌套查询

单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

多行自查询

返回多行记录的子查询

例题1:in关键字的使用

查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
首先我们需要知道的是10号部门的工作都有哪些(子查询)

1
2
-- 在这里进行去重操作,因为一个部门中可能会有多个人进行同样的工作
select distinct job from emp where deptno=10;


如上图,10号部门所承担所有工作种类如图

题干中要求查询和10号部门的工作相同的雇员信息,我们的第一步查找返回的结果有三个(体现了返回多行记录),意味着我们接下来要找出其他部门也从事这三个工作中的任意一个工作的雇员

这时候就要用上子查询中多行子查询经常使用的关键字:in
in的作用:使用in的时候，会把子句中的查询作为结果缓存下来，然后在主查询中对每个记录分别进行查询,in相当于多个or的连接体
要注意的是in只能返回一个字段
在此题中主查询会依次查询in条件中的每一种情况(即三种工作)

接下来进行主查询

1
2
3
select ename,job,sal,deptno
from emp
where job in (select distinct job from emp where deptno=10) and deptno != 10;

最终结果:

例题2:all关键字的使用

显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
第一步:根据题意我们首先需要知道部门30的所有员工的工资情况

1
select sal from emp where deptno=30;

查询结果:

第二步:将第一步的查询结果作为条件使用关键字all进行主查询
关键字all:当查询关键字设定为ALL时，即所有的条目均要同时满足设定条件

1
2
3
4
-- 主查询
select ename,sal,deptno
from emp
where sal> all(select sal from emp where deptno=30);

最终结果:

例三:any关键字的使用

显示工资比部门30中任意员工的工资高的员工的姓名,工资和部门号
此题和上一题的区别在于例二中查询的目标对象是要比30部门中的任何一个人的工资都高,而此题是要求目标对象的工资只要高于30部门中的任何一个就可以了
第一步:依然是查出来30部门的所有人的工资情况:

1
select sal from emp where deptno=30;

第二步:以第一步做条件,使用关键字any做主查询
any关键字:顾名思义,任意,只要有一个条件满足,则满足
在此题中,第一步查询出了部门30的所有人的工资,使用any关键字只要满足大于其中一个人的工资便成立
主查询:

1
2
3
select ename,sal,deptno 
from emp 
where sal>any(select sal from emp where deptno=30);

查询结果:

多列子查询

单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句

例:查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人

1
2
3
4
select ename 
from emp 
where (deptno,job)=(select deptno,job from emp where ename='SMITH')
and ename <> 'SMITH';

查询结果:

在from子句中使用子查询

from型子查询:把内层的查询结果当成临时表,供外层sql再次查询.查询结果可以当成表看待.临时表要使用一个别名

例一:显示高于自己所在部门平均工资的员工的姓名,部门,工资,平均工资

step1:查询各个部门的平均工资,并将列重命名构成一张临时表

1
select avg(sal) as asal,deptno as dt from emp group by deptno;

临时表如下:

step2:在from子句中使用子查询

1
2
3
select ename,deptno,sal,format(asal,2)
from emp,(select avg(sal) as asal,deptno as dt from emp group by deptno) as tmp
where emp.sal>tmp.asal and emp.deptno=tmp.dt;

查询结果加分析:

例二:查找每个部门工资最高的人的姓名,工资,部门,最高工资

思路与例一相同

例三:显示每个部门的信息(部门名,编号,地址)和人员数量

方法一:使用多表

1
2
3
4
select dept.dname,dept.deptno,dept.loc,count(*) as '部门人数'
from emp,dept 
where emp.deptno=dept.deptno
group by dept.deptno;


方法二:使用子查询

1
2
3
select dept.dname,dept.deptno,dept.loc,tmp.mycnt
from dept,(select count(*) as mycnt,deptno from emp group by deptno) as tmp
where dept.deptno=tmp.deptno;

两种方法,结果都相同

合并查询

为了合并多个select的执行结果,可以使用集合操作符union,union all

union

该操作符用于取得两个结果集的并集.当使用该操作符时,会自动去掉结果集中的重复行
案例:将工资大于2500或职位是MANAGER的人找出来

1
2
select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job='MANAGER'; -- 去掉了重复记录

union all

该操作符用于取得两个结果集的并集.当使用该操作符时,不会去掉结果集中的重复行

表的内外连接

表的连接分为内连和外连

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中使用的最多的连接查询
标准格式:

1
select 字段 from 表1 inner join 表2 连接条件 and 其他条件;

例;显示SMITH的名字和部门名称

1
2
3
4
5
6
7
8
9
--之前的写法
select ename,dname 
from emp,dept 
where ename='SMITH' and emp.deptno=dept.deptno;

--用标准的内连接写法
select ename,dname 
from emp inner join dept 
on emp.deptno=dept.deptno and ename='SMITH';

外连接

外连接分为左外连接和右外连接

左外连接

如果联合查询,左侧的表完全显示我们就说是左外连接
语法:

1
select 字段名 from 表名1 left join 表名2 on 连接条件

实例:

1
2
3
4
5
-- 建两张表
create table stuu (id int, name varchar(30)); -- 学生表
insert into stuu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); 
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

查询所有学生的成绩,如果这个学生没有成绩,也要将学生的个人信息显示出来

1
2
-- 当左边表和右边表没有匹配时,也会显示左边表的数据
select * from stuu left join exam on stu.id=exam.id;

右外连接

和左外连接相反,如果联合查询,右侧的表完全显示
例:对stuu表和exam表联合查询,把所有的成绩都显示出来,即使这个成绩没有学生与他对应,也要显示出来

1
select * from stuu right join exam on stu.id=exam.id;

索引特性

索引:用来提高数据库的性能,不用加内存,不用改程序,不用调sql,只要执行正确的create index,查询速度就可能成百上千倍的提升.但是,凡事有利必有弊,查询速度的提高是以插入,更新,删除的速度为代价的,这些写操作,增加了大量的io.所以他的价值,在于提高一个海量数据的检索速度
常见索引有如下几种:
主键索引
唯一索引
普通索引
全文索引

索引具体用途举例:

假设我们此时有一张8000000条记录的表,此时我们想要查找表中员工编号为998877的员工

1
select * from emp where empno=998877;

我们可以看到这一条查询耗时了4.93秒,可能会有人认为4.93秒不算太慢,可是这仅仅是在本机一个人来操作,如果放在公网中,加入同时有10000个人并发查询,那么很有可能数据库就崩溃导致死机了(在实际的开发中,我们认为一个查询语句的速度要保证在0.1秒之内)
如何解决这样缓慢的查询呢?
那便是创建索引

1
2
-- 给员工号创建索引
alter table emp add index(empno);

然后重复上面的查询过程,看看这时候的查询耗时多少

1
select * from emp where empno=998877;

此时查询的速率已经明显提升了不止一点,如果是在一个大数据的环境下,那可真是大效率的优化

基本原理

这里不对原理进行详细的说明,想要了解可以看看这个大神的文章:https://zhuanlan.zhihu.com/p/23624390
只说明一点,当没有创建索引的时候,数据在磁盘存储器上是无序放置的,一行一行的排列的很整齐,跟我们认知中的表基本是一样的,这样在查询时就只能一条一条数据的进行遍历,O(n)的时间复杂度使得查询十分的缓慢.
如果给表加上了索引,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是平衡树的结构,也就是说,整个表就变成了一个索引,这时查找一个数据的时间复杂度就变为了O(logN),于是查找的效率便得到的惊人的提升

索引的一些说明:
1.占用磁盘空间
2.当添加一条记录,除了添加到表中,还要维护二叉树,速度有影响
3.当我们添加一个索引,不能够解决所有查询问题,需要分别给字段建立索引
4.索引是以空间换时间

创建索引

主键索引(聚集索引)

创建主键索引的三种方式

1
2
3
4
5
6
7
8
9
-- 在创建表的时候直接在字段名后指定primary key
create table user1(id int primary key,name varchar(30));

-- 在创建表的最后,指定某列或某几列为主键索引
create table user2(id int,name varchar(30),primary key(id));

--创建表之后在添加主键
create table user3(id int,name varchar(30));
alter table user3 add primary key(id);

主键索引的特点:
一个表中,最多只能有一个主键索引
主键索引的效率很高
创建主键索引的列,他的值不能为null,且不能重复
主键索引的列基本上是int

唯一索引(非聚集索引)

唯一索引的创建和主键索引相似

1
2
3
4
5
6
7
8
9
-- 表定义时在某列后直接指定unique唯一属性
create table user4(id int primary key,name varchar(30) unique);

-- 创建表,在表的后面指定某列或几列为unique
create table user5(id int primary key,name varchar(30),unique(name));

-- 创建表后子指定唯一索引
create table user6(id int primary key,name varchar(30));
alter table user6 add unique(name);

唯一索引的特点:
1.一个表中可以有多个唯一索引
2.查询效率高
3.如果在某一列建立唯一索引,必须保证这列不能有重复数据
4.如果一个唯一索引上指定not null,等价于主键索引

普通索引的创建

三种创建普通索引的方式

1
2
3
4
5
6
7
8
9
10
-- 在表的定义最后,指定某列为索引
create table user8(id int primary key,name varchar(30),email varchar(30),index(name));

-- 创建表之后指定某列为普通索引
create table user9(id int primary key,name varchar(30),email varchar(30));
alter table user9 add index(name);

-- 创建一个索引名为 idx_name的索引
create table user9(id int primary key,name varchar(30),email varchar(30));
create index idx_name on user10(name);

普通索引的特点:
一个表中可以有多个普通索引,开发中比较常用
如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引

全文索引的创建

当对文章字段或有大量文字的字段进行索引时,会使用到全文索引.mysql提供全文索引机制,但是要求表的存储引擎必须是MyISAM,并且默认的全文索引支持英文,不支持中文.如果对中文进行全文检索,可以使用sphinx的中文版(core
seek)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建一张表,插入数据,并创建全文索引
create table articles(
	id int unsigned auto_increment not null primary key,
	title varchar(200),
	body text,
	fulltext(title,body)
)engine=MyISAM;

-- 插入数据
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'), 
    ('Optimizing MySQL','In this tutorial we will show ...'), 
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 
    ('MySQL vs. YourSQL','In the following database comparison ...'), 
    ('MySQL Security','When configured properly, MySQL ...');

● 查询有没有database数据
使用之前我们用的查询方式:

1
select * from articles where body like '%database%';


使用这种方式是肯定可以找到的,但是我们可以用explain工具看一下到底用没用到索引

● 使用全文索引

1
2
select * from articles
where match(title,body) against ('database');


在使用explain来分析下这个sql语句

查询索引

● 方法一:show keys from 表名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show keys from goods\G 
    *********** 1. row *********** 
           Table: goods    <= 表名 
      Non_unique: 0        <= 0表示唯一索引 
        Key_name: PRIMARY  <= 主键索引
    Seq_in_index: 1
     Column_name: goods_id <= 索引在哪列
       Collation: A 
     Cardinality: 0 
        Sub_part: NULL
          Packed: NULL
            Null:
      Index_type: BTREE   <= 以二叉树形式的索引
         Comment:
1 row in set (0.00 sec)

● 第二种方法:show index from 表名;
● 第三种方法:desc 表名; —>直接查看表结构

删除索引

● 第一种方法,删除主键索引:

1
alter table 表名 drop primary key;

● 第二种方法,其他索引的删除:

1
alter table 表名 drop index 索引名; --索引名就是show keys from 表名中的key_name字段

例如删除user10的索引:

1
alter table user10 drop index idx_name;

● 第三种方法:drop index 索引名 on 表名

1
drop index name on user8;

索引的创建原则

● 比较频繁作为查询条件的字段应该创建索引
● 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
● 更新非常频繁的字段不适合作创建索引
● 不会出现在where子句中的字段不该创建索引

事务管理

什么是事务?

事务是数据库系统中执行的一个工作单位,他是由用户定义的一组操作序列.一个事务可以是一组SQL语句,一条SQL语句或者整个程序,一个应用程序可以包括多个事务.通俗点讲,事务就是一组由DML语句组成SQL语句模块;这些语句在逻辑上存在相关性,这一组DML语句要么全部成功要么全部失败,是一个整体.只要当这一组DML语句全部执行完成,那么才能够说这个事务被完成.MySQL提供事务这种机制,保证我们达到这样的效果.事务还规定不同的客户端看到的数据是不相同的

事务基本操作举例

这里用一个银行转账的问题来具体说明下什么是事务,事务应该怎么进行操作
1.首先,创建一张测试表

1
2
3
4
5
create table account(
	id int primary key,
	name varchar(50) not null default '',
	balance decimal(10,2) not null default 0.0
);

2.在了解一下几个有关事务的关键词
● 开始一个事务

1
start transaction;

● 创建一个保存点

1
savepoint 保存点名;

● 回到保存点

1
rollback to;

(如果没有设置保存点直接就回到了事务的开始)
3.事务演示
目的:现在张三和李四都向银行卡里存了50万,然后张三想要将其中的25万转账给李四
银行操作:
先将两人的50万都存入银行写进数据库中

然后进行转账操作,在张三的账户中扣除25万,在李四的账户中加上25万
但是由于工作人员的操作事务,在李四的账户中少加了5万

这时候就可以返回到我们之前的保存点point2

然后再重新进行数据的处理,最终完成并提交事务

4.使用事务的好处
在上例中使用事务的好处就很明显.事务的成功取决于事务单元帐户相互依赖的操作行为是否能全部执行成功，只要有一个操作行为失败，整个事务将失败。例如上例中张三和李四的银行账户金额都是500000元，张三需要把自己帐户中的250000元人民币转到李四的账户上。这个过程看似简单，实际上涉及了一系列的数据库操作，但是可以简单地视为两步基本操作，即从张三的帐户金额中扣除250000元，以及将李四帐户中金额添加250000元人民币。假设第1步数据库操作成功，而第二步失败的话，将导致整个操作失败，并且张三帐户金额将被扣除250000元人民币,转账没成功,钱还没了,这谁顶的住….事务机制就可以避免此类情况，以保证整个操作的完成，如果某步操作出错，之前所作的数据库操作则将全部失效。

事务操作注意事项

● 如果没有设置保存点,也可以回滚,但是只能回滚到事务的开始,直接使用rollback(前提是事务还没有提交)
● 如果一个事务被提交了(commit),则不可以进行回滚(rollback)
● 可以选择回滚到哪个保存点(例如本例中的point1和point2都可以选择性的回滚)
● InnoDB支持事务,MyISAM不支持事务,MySQL数据库默认的存储引擎就是InnoDB

事务的隔离级别

当有多个客户端同时操作数据库中的某张表,MySQL为我们提供了隔离级别让不同的客户端或者线程进行隔离操作,使得不同的事务在操作数据时,具有隔离性,从而保证数据的一致性

无隔离性会造成的问题

脏读

所谓脏读是指是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务
也访问这个数据，然后使用了这个数据。
举个栗子:
1.张三的原工资为5000元,但是有一天财务人员在操纵数据库时不小心将他的工资修改为了8000元(但未提交事务)
2.恰好这时张三没事查了查自己的工资发现自己的工资是8000元,他十分高兴,认为这个月涨工资了
3.过了一会,财务发现了自己的这个错误,回滚了事务,将他的工资重新修改回了5000
像这样,张三记取的工资数8000就是一个脏数据,这种现象就叫做脏读.

不可重复读

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就 发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
简单点说就是a事务在一个事务中重复读取某个数据但是因为事务b对数据的修改造成事务a重复读取的数据不一致的现象
同样举个栗子:
1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成
2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.
3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000
由此Mary两次读取的自己的工资不一致的这种现象叫做不可重复读

解决办法:如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题

幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发 生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例子:
1.a工作员的任务是将一个有十条数据的工资表中每个人都加上1000元
2.在a工作员进行整表操作的时候,b工作人员向表中又插入了一条数据
3.此时a工作人员完成了之前十人工资加1000的操作,此时他回过头来检查自己的工作发现怎么少修改了一条数据,但是又清楚记得自己明明是对整表进行了修改
这种好像幻觉一样的操作,叫做幻读
解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题

事务的隔离级别

为了解决上面的这些无事务隔离出现的问题,MySQL为我们带来了事务的隔离级别

在这里我们演示一下可串行化隔离级别
● 首先要会查看当前的隔离级别

1
select @@tx_isolation;


● 然后设置隔离级别为可串行化(serializable)
此时一个事务中只允许一个线程进行访问,具体看图中解释

MySQL默认的隔离级别是可重复读,一般情况下不要修改
可串行化隔离级别原理图:

事务的ACID特性

事务是单个的工作单元。如果某个事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。一个逻辑工作单元必须有ACID属性，只有这样才能成为一个事务。ACID属性有以下4个属性。
● 原子性:事务是应用中最小的执行单元,是应用中不可再分的最小逻辑执行体
● 一致性:事务执行的结果,必须使数据库从一个一致性状态,变到另一个一致性状态.当数据库只包含事务成功提交的结果时,数据库处于一致性状态.如果系统运行发生中断,某个事务尚未完成而被迫中断,而已经完成的事务对数据库的修改已经被写入库中,此时数据库就处于一种不一致的状态,因此一致性是通过原子性来保证的
● 隔离性:各个事务的执行互不干扰，任意一个事务的内部操作对其他并发事务都是隔离的。也就是说，并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能互相影响。
● 持久性:持久性是指一个事务一旦被提交，它对数据库所做的改变都要记录到永久存储其中（如：磁盘）.

视图

什么是视图?

试图是一个虚拟表,其内容由查询定义.同真实表一样,视图包含一系列带有名称的列和行数据.视图的数据变化会影响到基表,基表的数据变化也会影响到试图

视图的主要作用

1.视图可以用来保证安全,只让用户看到我们想要他们看到的东西
2.可以把一些复杂的查询包装成一个视图,这样在下一次想要查询同样的数据的时候就可以直接查询视图,而不必在输入复杂的查询命令进行查询了

视图的基本使用

● 创建视图语法

1
create view 视图名 as select语句;

● 事例
创建视图:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建视图
create view v_ename_dname as select ename,dname 
from emp,dept 
where emp.deptno=dept.deptno;

-- 查看视图
select * from v_ename_dname;

-- 顺表查看一下原表
select ename,dname 
from emp,dept 
where emp.deptno=dept.deptno;

--此时发现视图内容其实就是原表的内容


● 修改视图,对基表数据会有影响

1
2
3
4
5
-- 修改视图
update v_ename_dname set dname='SALES' where ename='CLARK';

-- 查看基表变化
select * from emp where ename='CLARK';

● 修改基表,对视图有影响

1
2
3
4
5
-- 对基表进行修改
update EMP set deptno=20 where ename='JAMES';

-- 查看视图中的数据变化
select * from v_ename_dname where ename='JAMES';


● 删除视图
drop view 视图名

注意

视图是一个虚拟的表,即实际上是不存在这个表的,他是他在形式表现上又和一张真正的表一样,以及对他的操作都和表一样
我们可以查看一下所有的表

可以看到,视图是作为表出现在数据库中的,但由于他又不是一个真正的表,所以在给视图取名时最好加上前缀 v_…. 这样便于阅读,一看就知道是个视图

视图规则和限制

1.与表一样,视图必须唯一命名(不能出现同名视图或表名)
2.创建视图数目无限制,但要考虑复杂查询创建为视图之后的性能影响
3.视图不能添加索引,也不能有关联的触发器或者默认值
4.视图可以提高安全性,必须具有足够的访问权限
5.order by可以用在视图中,但是如果从该视图检索数据select中也含有prder by,那么该视图中的prder by将被覆盖
6.视图可以和表一起使用

用户管理

同Linux一样,MySQL也有权限限制,如果每一个人都可以使用root权限,随意操纵数据库,那么是极其不安全的,所以这时就要使用MySQL的用户管理

用户

用户信息

MySQL中的用户信息,都存储在系统数据库mysql中的user表中


我们针对性的查询一下三个字段的信息

字段解释:
● host:表示这个用户可以从哪个主机登陆,如果是localhost,表示只能从本机登陆
● user:用户名
● authentication_string:用户密码通过password函数加密后的
● *_priv:用户拥有的权限

创建用户

● 语法:

1
2
-- 这里的登陆主机指可以登录的用户的id地址,如果写成%那么在任何主机都可以访问
create user '用户名'@'登陆主机/ip' identified by '密码';

创建用户事例:
创建一个用户名为luoluo只能在本机登陆,密码为123456的用户

1
create user 'luoluo'@'localhost' identified by '123456';

此时在重新查看用户信息

删除用户

语法:

1
drop user '用户名'@'主机名'

删除刚刚创建的用户luoluo

1
drop user 'luoluo'@'localhost'


此时用户名为luoluo的用户就不存在了

用户密码修改

● 自己改自己的密码

1
set password=password('新的密码');

● root用户修改指定用户的密码

1
2
3
4
set password for '用户名'@'主机名'=password('新的密码');

-- 例如给luoluo改密码
set password for 'luoluo'@'localhost'=password('abcdef');

数据库权限

当我们使用root权限创建一个新的用户的时候,这个用户是不具有什么权限的,他对我们主机上的数据库是没有办法进行太多操作的

想要对更多数据库进行操作,就要让管理员给他更多的数据库权限
MySQL数据库提供的权限列表:

给用户授权

1
2
-- 权限列表,多个权限用逗号分开
grant 权限列表 on 库.对象名 to '用户名'@'登陆地址' [identified by '密码'] -- identified by 可选(下面说他的作用)

一些说明
● 赋予所有权限

1
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限

● .:代表本系统中的所有数据库的所有对象(表,视图,存储过程等)
● 库.*:表示某个数据库中的所有数据对象(表,视图,存储过程等)
● identified by可选.如果用户存在,赋予权限的同时修改密码,如果用户不存在,就是创建用户
权限赋予举例:
上面我们看到luoluo这个用户是看不到同其他数据库的,我们现在仅仅给他查看scott数据库,dept表的权限,整个过程如下图

● 如果发现赋权限后,没有生效,执行刷新指令

1
flush privileges;

如果还是没有生效,就关闭客户端,重新进入

回收权限

给了一个用户什么权限,我们当然还能收回这个权限

1
revoke 权限列表 on 库.对象名  from '用户名'@'登陆位置';

示例:

1
2
-- 回收luoluo对数据库的所有权限
revoke all on scott.dept from 'luoluo'@'localhost';

数据库知识总结暂时到此结束~