目录
二、数据库管理系统 (DataBaseManagement,DBMS)
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件 回滚事务标志着,事务的结束。并且是一种全部失败的结束。(三)特性
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)(一)、创建视图对象
一、数据库(DataBase,DB)
定义:存储数据的仓库(一堆文件里存储了具有特定格式的数据)
二、数据库管理系统 (DataBaseManagement,DBMS)
定义:管理数据库中的数据
常见管理系统:MySQL、Oracle、My sqlServer、DB2、sybase
三、SQL
定义:结构化查询语言,DBMS通过执行SQL完成对数据库中数据的增删改查。
我们所要学习的也是SQL。它可以在MySQL、Oracle、My sqlServer、DB2中使用。
四、数据库与管理系统关系
DBMS 通过执行SQL语句 管理 DB
五、MySQL
通常MySQL是默认“自动”启动状态,只有启动MySQL才能使用。
(我的电脑-右键-管理-服务和应用程序-服务-找MySQL服务)
六、MySQL安装
首次安装按照大部分博客或视频即可,卸载后二次安装若界面不一样安装:Connector J 8.0 、MySQL Workbench 8.0 、MySQL Service 8.0.xx (注意卸载干净,修改安装地址)(设置为UTF8)
七、常用指令
(注意!!SQL 不见;不执行 ";"表示结束 。SQL语句不区分大小写)
net start mysql; | 启动mysql |
net stop mysql; | 停止mysql |
exit; | 退出mysql |
use xxx; | 使用名叫 xxx 的数据库 |
create database xxx; | 创建名叫 xxx 的数据库 |
show database; | 显示所有数据库 |
select version(); | 查看mysql数据库的版本号 |
八、关于SQL语句
(一)分类
DQL(数据查询语言):select...
DML(数据操作语言):对表中的数据进行增删改;insert,delete,update(改)
DDL(数据定义语言):主要操作表的结构,不是表中的数据;create,drop(删除),alter
TCL(事务控制语言):commit(事务提交),rollback(事务回滚)
DCL(数据控制语言):grant(授权),revoke(撤销权限)
!数据库中的所有标识符命名必须全部是小写,单词和单词之间使用_进行衔接。
九、DQL具体操作
(一)导入数据库
需要先进入数据库,才能导入数据库;(且路径中不能有中文)
mysql> use bjpowernode;
mysql> show tables;
mysql> source E:\ideaproject\mysqldemoproject\bjpowernode.sql
(二)查表
查询表的数据
mysql> select * from dept; (dept为表的名称)
查询表的结构
mysql> desc dept; (desc 为 describe)
(三)条件查询
select dname(字段名) from dept(表名); | 查询表中的一个字段 |
select deptno,dname from dept; | 查询多个字段(用,隔开即可) |
select * from dept; | 查询所有字段(用*即可) |
(使用*查询,效率低(需要一定的时间)、可读性差,在实际开发中不建议使用)
select dname as deptname from dept; | 将dept表中查得dname字段显示为deptname;(只是显示结果改变,原表中的还为dname) |
select dname as 'dept name' from dept; | 字段包含空格需要用''括字段 |
select sal*10 from emp; | 字段可以使用数学表达式(显示的结果为薪资*10) |
select ename,sal from emp where sal = 5000; | 查询薪资为5000的 |
select ename,sal from emp where sal >= 2000 and sal <= 3000; | 查询薪资2000到3000的,包括2000和3000 |
select ename,sal from emp where sal between 2000 and 3000; | 查询薪资2000到3000的,包括2000和3000(语句必须遵循左小右大) |
select ename,comm from emp where comm is null; | 查询值为空的(不能用=null) |
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); | 查询薪资大于2500,并且deptno为10或者20 |
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; | 查询工作岗位是MANAGER和SALESMAN |
select empno,ename,job from emp where job in('MANAGER' , 'SALESMAN'); | 查询工作岗位是MANAGER和SALESMAN |
select ename from emp where ename like '%o%'; | 查询名字带有一个o的 |
select ename from emp where ename like '%\_%'; | 查询名字中有_的(需要加转义字符\) |
select ename,sal from emp order by sal; | 查询所有员工工资,并按工资排序(默认升序,指定升序asc)ascend |
select ename,sal from emp order by sal desc; | 查询所有员工工资,并按工资排序(desc 降序)descend |
select ename,sal from emp order by sal asc,ename asc; | 查询所有员工工资,并按工资升序,若工资一样,按名字升序 |
1.在数据库中null不能使用=进行衡量,需要使用is null。在数据库中null代表什么也没有,不是一个值,所以不能使用=衡量。不为空则is not null
2.and和or同时出现时,and 优先级更高,若要让or先执行,需要添加小括号()。在开发中不确定优先级,就加小括号。
3.in包含,相当于多个or(not in 表示不是xxx,不是xxx)
4.like为模糊查询,%匹配任意多个字符,_代表任意一个字符
5.关键字书写顺序:select...from...where...order by...
语句执行顺序:from ——> where ——> select ——> order by (排序总是在最后执行)
(四)、数据处理行数
也称单行处理函数,特点:一个输入对应一个输出(多行处理函数是多个输入对应一个输出)
select lower(ename) as ename from emp; | 转小写;查询emp中的ename字段,并且显示结果为ename的大写(转小写为 upper) |
select substr(ename,1,1) as ename from emp; | substr取子串(被截取的字符串,起始下标,截取的长度);(起始下标从1开始) |
select ename from emp where substr(ename,1,1) = 'A'; | 截取emp表中名字首字母为A/a的 |
select length(ename) enamelength from emp; | 取名字长度,并且显示为enamelength |
select * from emp where = trim(' KING'); | trim去空格 |
select round(1236.567,1) as result from emp; | round四舍五入;从表中找1236.567并且保留1位小数 |
select round(rand()*100,0) from emp; | rand()生成0-1的随机数; 生成0到100以内的随机数,且无小数位 |
select ename,(sal + ifnull(comm,0)) *12 as yearsal from emp; | ifnull当comm为null时,令comm值为0;*12计算年薪(as 在emp表中显示字段yearsal,不改变原表内容) |
1.null只要参与运算,最终结果一定是null,为了避免这个现象,使用ifnull函数。ifnull(数据,为null时所替代数据的值)
str_to_date | 将字符串转换成日期 |
date_format | 格式化日期 |
format | 设置千分位 |
case...when...then...when...then...else...end |
(五)、分组函数
也称多行处理函数,特点:输入多行,最终输出一行。
count(具体字段) | 计数;表示统计该字段下所有不为null的元素的总数 |
sum | 求和 |
avg | 平均值 |
select max(sal) from emp; | 最大值 |
select min(sal) from emp; | 最小值 |
!!①分组函数在使用时必须先进行分组,然后才能用。若未对数据进行分组,则整张表默认为一组;
②分组函数自动忽略null,不需要对null进行处理;
③分组函数不能够直接使用在where子句中(因为where执行时还没有分组)
(六)、分组查询(重要!)
当需要先进行分组,再对分组后的每一组数据进行操作时,则使用分组查询。
select...from...group by... | |
select deptno,max(sal) from emp group by deptno having max(sal) > 3000; | 从emp中对deptno进行分组,找deptno,和sal最大值,并且sal最大值大于3000 |
select deptno,max(sal) from emp where sal > 3000 group by deptno; | 先从emp表中筛选sal大于3000的,再按deptno分组,选择显示deptno,max(sal) |
①关键字书写顺序:select...from...where...group by...having...order by...
②执行顺序:from ——> where ——> group by ——> having ——> select ——> order by
③使用having可以对分完组之后的数据进一步过滤,having不能单独使用,必须和group by一起使用。
④优化策略:where和having优先选择where,where完成不了的再选择having
eg. 找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位外,且按平均薪资降序
select
job, avg(sal) as avgsal
from
emp
where
job <> 'MANAGER'
group by
job
having
avg(sal) > 1500
order by
avgsal desc;
“<>”是 !=
(七)、distinct
select distinct job,deptno from emp; | distinct 去除重复字段;distinct 只能出现在字段的前面。(这里表示job、deptno两个字段联合起来去重) |
select count(distinct job) from emp; | 统计有几种工作岗位 |
(八)、连接查询
从一张表中单独查询为单表查询;跨表查询,多张表联合起来查询数据称为连接查询。
(1)分类
1、根据语法:SQL92、SQL99
2、根据连接方式:
①内连接:等值连接、非等值连接、自连接
②外连接:左外连接(左连接)、右外连接(右连接)、全连接
(2)笛卡尔积现象
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积。
避免笛卡尔积现象,则需加上限制条件。但是匹配的时候,任进行了表与表条数的乘积次,只是最后查询的结果显示为所需条数。
(3)提升效率 — 给表起别名
emp ——> e ; dept ——> d
eg.下图(为SQL92)
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
(4)内连接
内连接A、B两张表无主次关系
1、等值连接(连接的条件是等值关系)
代码见上图,查询的是两个表的编号相同(下图为SQL99)
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
!SQL92结构不清晰,表的连接条件和后期进一步筛选的条件都放到了where后面
SQL99连接的条件是独立的,连接之后如果还需要进一步筛选再往后面添加where
select
...
from
a表
join
b表
on
a和b表的连接条件
2、非等值连接(条件不是一个等量关系)
eg.下图,条件为e表薪资在losal和hisal之间
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
3、自连接
粗略理解为:一张表当作两张表(起别名a,b)通过a的某个段名查找b的某个段名
eg.下图条件为:员工表的领导编号 = 领导表的员工编号
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
(5)外连接
1、右外连接
右:表示将join关键字右边的这张表看成主表;目的将这张表的数据全部查询出来,捎带着关联查询左边的表。
select
e.ename,d.name
from
emp e
right join
dept d
on
e.deptno = d.deptno;
2、右外连接
表示左边的表是主表。
任何一个右连接都有左连接的写法;任何一个左连接都有右连接的写法。
(outer可省略,带着可读性强。)
select
e.ename,d.name
from
dept d
left outer join
emp e
on
e.deptno = d.deptno;
(6)内连接 vs 外连接
例如查询员工编号对应的领导编号(内连接不会显示领导对应的领导编号为null,内连接需要两个表匹配不显示null;而外连接将员工表作为主表,会用员工表中的领导编号去查领导表中的编号)
(7)多表连接
内连接和外连接可以同时出现,可以混合
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
(8)练习
1、找出每个员工的部门名称以及工资等级及领导名,要求显示员工名、领导名、部门名、薪资、薪资等级
select
e.ename, d.dname, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
join
dept d
on
e.deptno = d.deptno
left join
emp l
on
e.mgr = l.empno;
(9)子查询
即select语句中嵌套select语句,被嵌套的select语句被称为子查询。
select
..(select).
from
..(select).
where
..(select).
1、where中的子查询
eg.如下图,其中()中的select先执行
select
ename,sal
from
emp
where
sal > (select min(sal) from emp);
2、from子句中的子查询
from后面的子查询可以将子查询的查询结果当作一张临时表。
eg.下图,from:按emp中的job进行分组,查询job和sal的平均值并且将平均值别名为avgsal,这张表别名为t表
select
t.*,s.grade
from
(select job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
3、union
对于表连接而言,union的效率更高一些。通常进行表连接,每连接一次新表匹配的次数遵循笛卡尔积,但是union可以减少匹配的次数,在减少匹配次数的情况下还可以完成两个结果集的拼接。
eg.a(10条)连接b(10条),a连接c(10条)。普通:10*10*10=1000次。union:10*10 + 10*10 = 200次
即union把乘法变成了加法
4、limit(重要!)
将查询结果集的一部分取出,通常使用在分页查询中。eg.百度一次显示10条消息。需要看更多的消息可以按页码转跳。
分页:提高用户体验。
limit startIndex,length (开始的下标,显示的长度)
例如下方为显示前5条,起始下标默认从0开始
select
enane,sal
from
emp
order by
sal desc
limit 5;
!①mysql中limit在order by之后执行
②每页显示pagesize记录公式:第pageno页为 limit (pageno -1)*length , length
(10)DQL总结(很重要!)
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序:
from ——> where ——> group by ——> having ——> select ——> order by ——> limit
十、DML
主要用于表结构的创建修改
(一)创建表格
建表语法格式
create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型);
①表名:建议以t_或者tb1_开始,可读性强。见名知意。
②字段名:见名知意。
③表名和字段名都属于标识符。
④常见数据类型:
varchar (可变长度的字符串,会根据实际的数据长度动态分配空间;最长255),
char (定长字符串,不管实际的数据长度为多少,都分配固定长度的空间去存储数据;使用不恰当时可能造成空间的浪费;最长255),
int (数字中的整数型;最长11),
bigint (数字中的长整型),
flout (单精度浮点型数据),
double (双精度浮点型数据),
date (短日期类型),
datetime (长日期),
clob (字符大对象;最多可存储4G的字符串;超过255个字符都要用它来存储;character large object),
blob (二进制大对象;专门用来存储图片、声音、视频等流媒体数据;binary large object)
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
(二)删除表格
第一种删除:当表不存在时会报错;
第二种删除:当这张表存在,则删除
drop table t_student;
drop table if exists t_student;
(三)插入数据
(1)语法格式
insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
select * from t_student;
!insert语句只要执行成功就会多一条记录,若没有给其它字段指定值即默认为null。可以在建表时用default指定默认值。(如下图,sex处指定默认值为m)
create table t_student(
no int,
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar(255)
);
字段名可以全部省略(即默认全部),但是值必须全部写上,且一一对应。
insert into t_student values(1,'zhangsan','m',20,'zhangsan@123.com');
(2)数字格式化
①格式 :format(数字,'格式');如下图,表示显示小数点后三位
select ename,format(sal,'$999,999') as sal from emp;
(3)日期
①str_to_date('字符串日期','日期格式'):将字符串varchar类型转换成date类型
②data_format:将date类型转换成具有一定格式的varchar字符串类型
③MySQL的日期格式:%Y 年;%m 月;%d 日;%h 时;%i 分;%s 秒
insert into t_student(no,name,sex,age,date) values(1,'zhangsan','m',20,str_to_date('01-10-1990','%d-%m-%Y'));
符合格式(mysql默认格式为 %Y-%m-%d)则不需要转型,如下:
insert into t_student(no,name,sex,date) values(1,'zhangsan','m','1990-10-01');
(java中的日期格式:yyyy-MM-dd HH:mm:ss SSS)
④date是短日期,包括年月日;datetime是长日期,包括年月日时分秒。
mysql默认长日期:%Y-%m-%d %H:%i:%s
⑤获取系统时间:now() 所获取的时间带有时分秒,是datetime类型
insert into t_student(no,name,sex,date,create_time) values(1,'zhangsan','m','1990-10-01',now());
(4)插入多条数据
create table t_user(id int(11),name varchar(32),birth date,create_time datetime);
desc t_user;
insert into t_user(id,name,birth,create_time) values(1,'zds','1990-02-27',now()),(2,'dfa','1997-01-01',now());
(四)修改 update
①语法格式
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
!没有条件限制会导致所有数据全部更新
update t_user set name='jack',birth = '1999-02-23',create_time = now() where id = 2;
(五)删除 delete
①语法格式
delete from 表名 where 条件;
! 若没有条件,则整张表的数据会全部删除
delete from t_user where id = 2;
delete from t_user;
(六)快速创建表(了解)
create table emp2 as select * from emp;
原理:将一个查询结果当作一张新建的表,起到表的快速复制作用。表快速创建出来同时表中的数据也存在了。
(七)快速删除表中的数据
1、delete(DML语句)
①删除较慢;
②表中的数据被删除了,但这个数据在硬盘上的真实存储空间不会被释放(即删除了表格中的数据,但是表格未被删除)
③支持回滚,后悔了可以再恢复数据
2、truncate(DDL语句,重要)
①删除效率高,表被一次截断,物理删除(数据被彻底删除,但是表格未被删除)
!删除表格是drop table 表名;
(八)表结构的修改
即添加、修改、删除一个字段。使用alter,为DDL语句。
①表设计好后对表结构进行修改成本高、此责任由设计人员承担;
②若需要修改表结构,可以使用工具
(九)约束 constraint
在创建表的时候,可以给表中的字段加上一些约束,来保证表中数据的完整性、有效性。
分类:非空约束(not null)、唯一性约束(unique)、主键约束(primary key,PK)、外键约束(foreign key,FK)、检查约束(check,mysql不支持,oracle支持)
1、非空约束
即不能为空,not null在字段后加即可
create table t_student(
id int not null,
number int
);
! xxx.sql这种文件被称为sql脚本文件,该文件中编写了大量的sql语句。当我们执行sql脚本文件时,该文件中的所有sql语句会全部执行。即批量执行sql语句可以使用sql脚本文件。不能用文本打开sql脚本文件,文件过大可能会死机,在MySQL中执行以下语句:
source D:\Demo\a.sql
2、唯一性约束
即数据的唯一性,不能有重复。unique在字段后加即可(为列级约束)。数据可以为null,null不算重复。多个字段联和起来唯一性(叫表级约束)见下图:
create table t_student(
id int,
name carchar(255),
email varchar(255),
unique(name,email)
);
!在mysql中,当一个字段同时被not null和unique约束时,该字段自动变成主键字段。(Oraole中不这样)
3、主键约束(非常重要)
主键约束:是一种约束
主键字段:该字段上添加了主键约束,这种字段叫做主键字段
主键值:主键字段中的每一个值都叫做主键值
①主键值是每一行记录的唯一标识;是每一行记录的身份证号(即主键只能有一个)。
②任何一张表都应该有主键,没有主键表无效
③特征:not null unique;(主键值不能为空,同时也不能重复)
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255)
);
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id)
);
④分类:
单一主键:一个字段做主键;
复合主键:多个字段联合起来做主键。(在实际开发中不建议使用复合主键)
⑤主键值建议使用:int、bigint、char等类型
不建议使用:varchar做主键。主键值一般都是数字,一般都是定长。
⑥分类:
自然主键:主键值是一个自然数,和业务没关系;
业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。
在实际开发中使用自然主键比较多。一旦主键和业务挂钩,当业务发生改变的时候可能会影响到主键值。
4、外键约束 FK
创建了两张表,两张表(父表、子表)各自有一个主键,子表中有一个外键,该外键是父表的主键。
①删除表的顺序:先删除子表,再删除父表;创建表顺序:先创建父表,再创建子表。表中数据的添加或删除同理。
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(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);
②外键所引用的值可以不是主键,但是必须具有唯一性。外键值可以为空。
十一、存储引擎(了解)
存储引擎是MySQL中特有的一个术语,其它数据库中没有(Oracle中有但不叫这个名字)。实际上就是一个表存储/组织数据的方式。不同的存储引擎,表存储数据的方式不同。
①未指定则默认,如下图(默认)
ENGINE:指定存储引擎
CHARSET:指定这张表的字符编码方式
show create table t_student;
CREATE TABLE `t_student` (
`no` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
②查看mysql所支持的存储引擎
show engines \G
③MyISAM存储引擎
其所管理的表有如下特征:
使用三个文件表示每个表:格式文件(存储表结构的定义mytable.frm)、数据文件(存储表行的内容mytable.MYD)、索引文件(存储表上索引mytable.MYI)
对于一张表来说,只要是主键或者加有unique约束的字段上会自动创建索引。
MyISAM不支持事务机制,安全性低。
④InnoDB存储引擎
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本(MVCC)和行级锁定
– 支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。
十一、事务
一个事务其实就是一个完整的业务逻辑;只有DML语句(insert、delete、update)才有事务这一说。
①InnoDB存储引擎:提供一组用来记录事务性活动的日志文件
事务开启了:
insert
insert
insert
delete
update
update
update
事务结束了!
②在事务的执行过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中,我们可以提交事务,也可以回滚事务。
(一)提交事务
commit;
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。提交事务标志着,事务的结束。并且是一种全部成功的结束。
在mysql中默认是每执行一次DML语句则自动提交事务一次。在开发中保证数据的安全性必须同时多条DML语句成功后再一块提交,以下命令可以将自动提交机制关闭:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(20,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,20)
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,0)
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
(二)回滚事务
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
回滚事务标志着,事务的结束。并且是一种全部失败的结束。
(三)特性
①原子性A
事务是最小的工作单元。不可再分
②一致性C
所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,以保证数据的一致性
③隔离性I
A事务和B事务之间具有一定的隔离
④持久性D
事务最终结束的一个保障。事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘上!
(四)隔离
1、级别
①读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》
定义:事务A可以读取到事务B未提交的数据。
存在的问题:脏读现象!(Dirty Read,我们称读到了脏数据)
!这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!
②读已提交:read committed《提交之后才能读到》
定义:事务A只能读取到事务B提交之后的数据。解决了脏读的现象。
存在的问题:不可重复读取数据。即在事务开启之后,第一次读到的数据是3条,当前事务还没有
结束,可能第二次再读取的时候,读到的数据是4条,3不等于4称为不可重复读取。
!这种隔离级别是比较真实的数据,每一次读到的数据是绝对的真实。oracle数据库默认的隔离级别是:read committed
③可重复读:repeatable read《提交之后也读不到,永远读取的都是刚开启事务时的数据》
定义:事务A开启之后,不管是多久,每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改,并且提交了,事务A读取到的数据还是没有发生改变,这就是可重复读。解决了不可重复读取数据。
存在的问题:可能会出现幻影读。每一次读取到的数据都是幻象。不够真实!早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个!!!!!!!!!!!
④序列化/串行化:serializable(最高的隔离级别)
这是最高隔离级别,效率最低。解决了所有的问题。这种隔离级别表示事务排队,不能并发synchronized,线程同步(事务同步)每一次读取到的数据都是最真实的,并且效率是最低的。
即事务A在处理表A时,但是未提交。事务B此时查询不到表A,只有当事务A提交以后才能查询到
2、查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.01 sec)
3、设置隔离级别
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.03 sec)
十二、索引
索引是在数据库表的字段上添加的,是为了提高查询效率的一种机制,相当于一本书的目录。一张表的一个字段可以添加一个索引,也可以多个字段联合起来添加索引。
在mysql当中,主键上,以及unique字段上都会自动添加索引的!!!!
什么条件下,我们会考虑给字段添加索引呢?
①数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)
②该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。
③该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)
!不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。
(一)创建索引
给emp表的ename字段添加索引,起名:emp_ename_index。如下图:
查看一个SQL语句是否使用了索引进行检索(若type为ALL则说明没有使用索引)
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_ename_index | emp_ename_index | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
(二)删除索引
drop index emp_ename_index on emp;
(三)索引失效
1、%在首位
select * from emp where ename like '%T';
因为模糊匹配当中以“%”开头了!尽量避免模糊查询的时候以“%”开始。这是一种优化的手段/策略。
2、or
explain select * from emp where ename = 'KING' or job = 'MANAGER';
使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会实现。
3、复合索引
使用复合索引的时候,没有使用左侧的列查找,索引失效。两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。
4、where当中索引列参加了运算
create index emp_sal_index on emp(sal);
5、where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
十三、视图
站在不同的角度去看待同一份数据。
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)
(一)、创建视图对象
mysql> create view dept2_view as select * from dept2;
(二)、删除视图对象
mysql> drop view dept2_view;
只有DQL语句才能以view的形式创建。create view view_name as 这里的语句必须是DQL语句;
增删改查,又叫做:CRUD。
C:Create(增)
R:Retrive(查:检索)
U:Update(改)
D:Delete(删)
十四、数据库设计范式
(一)3范式
1、第一范式
要求任何一张表必须有主键,每一个字段原子性不可再分。
2、第二范式
建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖。
3、第三范式
建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖。
(二)总结表的设计
①一对多:
一对多,两张表,多的表加外键!!!!!!!!!!!!
②多对多:
多对多,三张表,关系表两个外键!!!!!!!!!!!!!!!
③一对一:
在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。
一对一,外键唯一!!
!!在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说,sql语句的编写难度也会降低。