Mysql学习记录
Q:安装后如何启动mysql?
A:mysql-uroot -p111111//设置的用户名:root和密码:111111
1.mysql常用命令
-
show databases; //查看有哪些数据库
-
use 库名; //选择某个数据库,表示正在使用xx数据库
-
create database 库名; //创建数据库
-
exit; //退出数据库
-
show tables; //查看数据库有哪些表
-
select version(); //查看mysql版本号
-
select database(); //查看所用的数据库
-
\c //终止输入
注意:命令不区分大小写,不见分号不执行
数据库基本单元是表:table
什么是表?为什么用表来存储数据?
因为表比较直观,任何一张表都有行(row:被称为数据/记录)和列(column:被称为字段)
(了解)
1.每个字段都有字段名,数据类型,约束等信息;
2.字段名可以理解是一个普通的名字;
3.数据类型:字符串,数字,日期等;
4.约束:唯一性约束(不可重复);
2.Sql语句分类
1)DQL(data query language):数据查询语言(带有select关键字都是查询语句)
2)DML(Data manipulation language):数据操作语言(凡是对表中数据进行增删改查的都是DML)
增:insert ;删:delete;改:update
3)DDL(Data Definition Language):数据定义语言(带有create,drop,alter都是DDL,主要操作表结构(字段),不是操作表的数据
create:新建; alter:修改 ; drop:删除
4)TCL(Transaction Control Language):事务控制语言(包括事务提交:commit,事务回滚:rollback
5)DCL:数据控制语言(包括授权grant,撤销权限revoke
3.前期准备
1)数据导入
source+路径(路径不要有中文)
2)查看表中数据
select *from 表名;
三张表:
-
部门表
-
员工信息表
-
薪资等级表
3)不看数据,只看表结构
desc 表名;
4.DQL语句
1)查询一个字段
select 字段名 from表名;
select, from为关键字;字段名和表名为标识符
2)查询多个字段?(使用“,”隔开)
select 字段1,字段2 from 表名;
3)查询所有字段
select * from表名;
4)给查询的列起别名
select deptno, dname (as)deptname from dept;//这里的as可以省略
使用关键字as起别名,
注意:只是将查询的结果显示为deptname,原表列名还是dname。
select不会进行修改操作,只负责查询
Q:假设起的别名中有空格,怎么解决?
A:select deptno,dname 'dept_name' from dept;//用单引号或者双引号括起来,就可以打空格(最好使用单引号)
注意:所有的数据库中,字符串统一使用单引号括起来,单引号是标准,双引号在oracle数据库中用不了,在mysql中可以使用
5)计算员工年薪?
select ename ,sal*12 from emp;//结论:字段可以使用数学表达式
注意:别名为中文时,用单引号括起来
5.条件查询
1)什么时条件?
不是将表中所有数据都查出来,是查找符合条件的
语法:select 字段1,字段2,字段3 from 表名 where 条件;
例如:查找sal=800的员工姓名和编号
select empno,ename from sal=800;
注意:<>和!=在mysql中的含义一样
例题1:查询sal在[2450,3000]之间的姓名和编号
-
select empno,ename from emp where sal>=2450 and sal<=3000;
-
select empno,ename from emp where sal between 2450 and 3000;
例题2:查询员工名字,薪资,要求按薪资升序,如果薪资一样,在按名字升序排列
-
select ename,sal from emp order by sal asc,ename asc;
关键字执行顺序:from,where,select,order by
6.数据处理函数
1)称为单行处理函数,特点:一个输入,对应一个输出
相对的,还有多行处理函数,特点:多个输入对应一个输出
2)常见的单行梳理函数
-
Lower
-
upper
-
substr
-
length
-
trim 去空格
-
str_to_date 将字符串转换为日期
-
date-format 格式化日期
-
format 设置千分位
-
round 四舍五入
-
rand() 生成随机数
-
if null 将null设置成具体值
-
concat 字符串拼接
例题1:查询那些员工津贴为null
select empno,ename,sal,comm from emp where comm is null;
注:数据库中null不能用等号衡量,需要使用is null,因为数据库中 null代表什么也没有,不是一个值!!查询不为null,就用is not null
例题2:查询工资大于2500,并且部门编号为10或者20部门的员工
select empno,ename,sal from emp where sal>2500 and (deptno=10 or deptno=20);
注:and和or同时出现,and优先级比or高,如果要or先执行,加小括号,对不确定的优先级,加小括号避免歧义!!
例题3:查询工作岗位是,manager和salesman的员工
select empno,ename,job from emp where job='manager' or job='salesman';
select empno, name, job from emp where job in ('manager' , 'salesman);
注意:1.in包含,详单与多个or,in不是一个区间,,后面跟具体的值,not in 表示不包含这几个值
2.所有数据库中只要null参与的数学运算,最终结果都是null
例题4:计算员工年薪,包括奖金
select ename ,(sal+ifnull(comm,0))*12 as yearsal from emp;
例题5:保留小数
select round(1234.567,0)as result from emp;
注意:这里0表示保留0位小数,-1表示保留到10位,1保留一位小数
select round(rand()*100,0) from emp; 求100以内的随机数
补充:1.排序
例题4:查询所有员工薪资,降序
select ename,sal from emp order by sal desc;
注意:如果不排序,默认是升序asc
补充:2.字面量or字面值
select 'abc' as ename from emp; 会生成14条abc
注意:select 可以跟某个表的字段名,也可以跟字面量
补充:3.case...when...then...when...then...else...end
例题5:当员工工作岗位是,manager时,工资上调10%,是salesman,上调50%
注意:该操作不会修改数据库,只是显示工作上调后的结果
select ename,job,(case job when 'manager' then sal1.1 when 'salesman'then sal1.5 else sal end)as newsal from emp;
7.模糊查询
like,支持%或者下划线匹配,%匹配任意多个字符,下划线匹配任意一个字符(%和_都是特殊符号)
例题1:找出名字中含有0
select ename from emp where ename like '%0%';
例题2:找出名字中以T结尾的
select ename from emp where ename like '%T';
例题3:找出名字以K开头的
select ename from emp where ename like 'K%';
例题4:找出第二个字母是A的
select ename from emp where ename like '_ A%';
例题5:找出名字中带有'_'的(用/进行转义)
select ename from emp where ename like '%/_%';
8.分组函数(多行处理函数)group by
输入多行,输出一行
-
count 计数
-
sum 求和
-
arg 平均值
-
max 最大值
-
min 最小值
注意:使用时,必须先分组才能使用,琐事没有对数据进行分组,整张表默认为一组
分组函数使用注意:1.自动忽略null,不需要对null提前处理
2.count(*)与count(具体字段)区别?
-
前者统计所有行数,因为每行记录不可能都为null
-
后者表示该字段下所有不为null的元素总数
3.分组函数不能直接使用在where语句中
4.所有分组函数可以组合在一起使用
9.分组查询***
1)什么是分组查询
实际应用中,某些情况需要先分组,对每一组数据进行操作,这是要用到分组查询
2)所有关键字组合,这些关键字的执行顺序是?
select...from...where...group by...order by(顺序不能颠倒)
执行顺序:from-->where-->group by-->select-->order by
Q:分组函数不能直接使用在where语句中
A:因为必须先分组在使用,where执行时,还没有分组,所以where后不能用分组函数
例题1:找出每个工作岗位的工资和
select job,sum(sal)from emp group by job;
执行顺序:从emp表中查询诗句,根据job字段分组,对每一组进行sum(sal)
重点注意:在一条select语句中,若有group by 的语句,select只能跟:参与分组的字段,以及分组函数,其他一律不能跟
例题2:找出每个部门不同岗位的最高工资
select deptno,job,max(sal) from emp group by deptno,job;
技巧:两个字段联合成一个字段看
例题3:找出每个部门最高薪资,显示最高工资>3000的
-
select deptno max(sal) from emp group by deptno having max(sal)>3000;
-
select deptno, max(sal) from emp where sal>3000 group by deptno;
注意:使用having可以对分组之后的数据进一步过滤,having不能单独使用,必须和group by一起使用,不能代替where
总结:方法一的效率较低,可以先将大于3000的都找出来在分组,where和having都能用,优先选where
例题4:where没有办法使用的情况!!!
找出每个部门的平均工资,要求显示平均工资高于2500的
select deptno , avg(sal) from emp group by deptno having avg(sal)>2500;
为什么不可以用where:
因为where后面不可以使用分组函数,where执行时,还没有分组,所以where后不可以跟分组函数
执行顺序:from-->where-->group by-->having-->select-->order by
10.去除重复记录(注:原表数据不作更改,只是查询结果去重
distinct 只能出现在字段最前方
select distinct job,deptno from emp;(表示将job和deptno联合去重)
11.连接查询
1)什么是连接查询?
从一张表单独查询,称为单表查询
emp表和dept表联合起来查询,从emp表中取出员工名字,从dept表中取出部门名字
连接查询也称为跨表查询
2)连接查询分类
根据表连接方式:
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
3)当两张表进行连接查询时,没有任何条件的限制会发生什么现象?
例如:查询每个员工所在部分名称?
当两张表进行连接查询,没有任何条件限制,最终查询结果条数为两张表条数的乘积,这种现象称为笛卡尔积现象
4)如何避免笛卡尔积现象
连接时加条件,满足这个条件的记录被筛选出来
注意:最终查询的结果条数是14条,但是匹配过程中,匹配次数没有减少!!
例:select ename,dname from emp,dept where emp.deptno=dept.deptno;
优化技巧!给表起别名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;(SQL 92语法)
注:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数
5)内连接之等值连接
例题1:查询每个员工所在部门的名称,显示员工名和部门名
实际上:是emp e和dept d进行连接,条件是e.deptno=d.peptno
SQL 92语法:
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
SQL 99语法
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;(这里的inner可以省略)
两者区别:92语法结构不清晰,表的连接条件和后期进一步筛选条件都放在where后面,
99语法:表连接条件独立,在on之后,若要进一步筛选,后续继续添加where条件
6)内连接之非等值连接
例题2:找出每个员工的工资等级,要求显示员工名,薪资,薪资等级
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
7)内连接之自连接
例题3:查询每个员工上级领导,要求显示员工名和对应领导名(技巧:一张表看成两张表)
select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr=b.empno;
on 后面的条件是:员工的领导编号=领导的员工标号
8)外连接(右外连接)
例题4:员工对应部门(包括没有的部门名字)
select e.ename, d.dname from emp e right join dept d on e.deptno=d.deptno;
总结:
1.right:表示将join关键字右边的这种表看成主表,主要是为了将这张表的数据全部查询出来,捎带着查询左边的表
2.外连接:两张表连接,产生了主次关系;
内连接:A和B连接,AB两张表之间没有主次关系;
3.带有right是右外连接,又叫右连接,left同理,任何一个右连接都有左连接写法,反之亦然;
4.join之前可以加outer,也可以省略,与inner一样;
5.外连接的查询条数一定是>=内连接的查询结果条数
9)三张表的连接,四张表的连接
语法:select... from a join b on a 和b的连接条件 join c on a和c的连接条件 on a和d的连接条件;
一条sql语句内连接和外连接可以混合,都可以出现
例题1:找出每个员工的部门名称,以及工资等级,显示员工名,部门名,工资,工资等级
select e.ename,d.dname,e.sal,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;
12.子查询
1)什么是子查询?
select 语句中嵌套select语句,被嵌套的语句称为子查询
2)子查询出现在哪里?
select...(select) from ...(select)where...(select)
3)where子句的子查询
例题1:找出比最低工资高的员工姓名和工资
思路:找出最低工资(800);找出大于800的;合并
select ename,sal from emp where sal>(select min(sal)from emp);
4)from子句的子查询
注意:from后面的子查询,可以将子查询的查询结果当作一张临时表(技巧)
例题2:找出每个岗位的平均工资的薪资等级
步骤:1.找出每个岗位平均工资(按照岗位分组求平均值)
select job,avg(sal) from emp group by job;(把该结果当作一张真实存在的表)
2.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;
注意:括号中avg(sal)一定要起别名,因为avg是一个函数,不可以做一个字段,
t.*表示输出t的所有字段!!
5)select后面出现的子查询(了解)
例题3:找出每个员工的部门名称,要显示员工名,部门名
select e.ename,e.deptno,(select d.dname from dept d where e.deptno=d.deptno)as dname from emp e;
注意:子查询返回记录条数得和著查询保持一致,select中嵌套select子查询,只能一次返回一条结果,多于一条就出错
6)union合并查询结果集
例题4:查询工作岗位是manager和salesman 的员工
方法一:where job='manager'or job='salesman';
方法2:where, job in ('manager','salesman');
方法3:select ename,job from emp where job='manager' union select ename,job from emp where job='salesman';
总结:union这种写法效率高,对于表连接来说,每一次连接信标,匹配的次数满足笛卡尔积,union可以减少匹配的次数,在减少匹配次数的情况下,可以完成两个结果集的拼接
a连接b连接c :a=10条;b=10条;c=10条
匹配次数=1000;
a连接b一个结果:10*10=100
a连接c一个结果:10*10=100,使用union:匹配200次
注:1.union在结果集进行合并时,要求两个结果集列数相同,
2.结果集合并时要求列与列的数据类型保持一致
13.limit(***)
1)将查询结果一部分取出来,通常使用在分页查询中
分页作用是为了提高用户的体验,因为一次全部都查询出来,用户体验差,可以一页一页翻页看
2)limit怎么用
例题1:按照薪资降序,取出排名前5的员工
select ename ,sal from emp order by sal desc limit 5;
完整写法:limit 0,5; limit startindex ,length
缺省写法:limit 5; 取前五
注:mysql中limit在order by之后执行
例题2:取出工资在3~5名的员工
select ename,sal from emp order by sal desc limit 2,3;
3)分页
每页显示三条记录
第一页:limit 0,3
第二页:limit3,3
第三页:limit6,3
....
每页显示pagesize条记录
第pageno页:limit (pageno-1)*pagesize,pagesize;
14.DDL语言 data define langurage,包括:create,drop,alter
1)表的建立create
建表的语法格式:create table +表名(字段名1,数据类型,字段名2,数据类型...)
注:最后一个字段数据类型结束不用加',' 表名:建议以t_、
或者tbl_开始,可读性强
2)mysql数据类型
-
varchar:可变长字符串 varchar(10)根据数据长度动态分配空间
优点:节省空间
缺点:动态分配,速度慢
-
char:定长字符串 不管实际长度,分配固定长度空间存储数据
优点:不需要动态分配空间,速度快,使用不恰当,会导致空间浪费
缺点:使用不当导致空间浪费
-
int :=java的int,最长11位
-
bigint:数字长整型,java中的lang
-
float:=java的浮点数
-
double:=java的浮点数double
-
datetime:长日期,类型
-
date:短日期
-
clob(character large object):字符大对象,最多可以存储4G的字符串 例如:一篇文章(超过255字符的)
-
blob(binary large object)大进制大对象,存储图片,声音,视频等等流媒体数据 例如:插入一个图片,视频,需要使用IO流
3)创建学生表
create table t_stu(
num int(3),
name varchar(32),
sex char(1),
age int(3),
email varchar (255));
4)删除表:
drop table t_stu; 这种方法在表不存在的时候会报错
drop table if exists t_stu; 存在的话,删除
5)快速创建表(了解)
create table emp_2 as select *from emp;
原理:将一个查询结果当作一张表新建,可以完成表的快速复制
6)表结构修改(了解,不重点,需求很少,开发进行中,修改表结构成本高)
使用alter
15.DML语句(insert, update,delete)
1)插入数据 insert
语法格式:insert into +表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
注:字段名要和值一一对应,数量要对应,数据类型也要对应
insert into t_stu(num,name,sex,age,email)values(1,'张三','m',20,'zhangsan@123.com');
注:insert 语句但凡执行成功,都会多一条记录,没有给其他字段指定值的话,默认是null
如果给地段加上default,可以指定默认值
create table t_stud(
num int(3),
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar (255));
注:语句中的字段名可以省略,但是values都要写上,因为前面字段名省略,等于都写上了
2)insert 插入日期
数字格式化:format(了解)
select ename,format (sal,'$999,999')as sal from emp;加入千分位
转换函数
-
str_to_date:字符串varchar转换成date类型,通常使用在insert后面,因为插入的时候需要一个日期类型,需要转换函数
-
date_formate:将date转换成具有一定格式的varchar类型
注:数据库中有一条命名规范,所有标识符都是小写,单词和单词之间用下划线进行衔接
str_to_date:('字符串日期','日期格式')
mysql日期格式:%y 年;%m 月;%d 日;%h时;%i分;%s秒
str_to_date('01-10-1990','%d'-%m-%y')
注意:如果提供的日期符号是这个格式,%y-%m-%d,str_to_date函数就不会变了,这是默认的格式
date_formate
这个函数可以将日期类型转换成特定的格式字符串
date_formate(birth,'%m/%d/%y')
(注:这个函数通常在查询日期方面使用,设置展示的日期格式 ,
sql语句会进行默认的日期格式化,自动将数据库中的date类型转换成varchar类型,并采用默认的日期格式:'%y-%m-%d')
3)date和datetime的区别
前者短日期,只包括年月日,后者长日期,包括年月日,时分秒
默认格式:
-
date:%y-%m-%d
-
datetime:%y-%m-%d %h:%i:%s
在mysql获得当前时间:now()函数(datetime格式)
4)insert一次插入多条数据
insert into t_user (id,name,birth,create_time)values
(1,'zs1','1980-10-01',now()),
(2,'zs2','1980-10-02',now()),
(3,'zs3','1980-10-03',now()),;
5)修改update
语法:update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...where 条件;
注:没有where语句会导致所有数据全部更新
6)删除delete
delete from 表名 where 条件;
没有条件,整张表数据都会删除
7)快读删除表中数据?
-
delete from dept;
这种方式比较慢
原理:表中数据被删除,但在硬盘上的真实存储空间不会被释放,这种删除缺点:删除效率低,优点:支持回滚,后悔了可以恢复
-
truncate table emp;
truncate语句删除数据原理:物理删除,效率较高,表被一次截断,缺点:不支持回滚,优点:快
-
drop table 表名; 这种是删除表,不是删除数据
16.创建表的约束(♥♥♥)
1)什么是约束?
加入约束,保证数据完整性,有效性
2)常见约束
-
非空约束 not null
-
唯一性约束 unique
-
主键约束 primary key (简称pk)
-
外键约束 foreign key(简称fk)
-
检查约束 check(mysql不支持,oracle支持)
3)非空约束 not null
4)唯一性约束 unique
不能重复,但是可以为null
新需求:name和email两个字段联合唯一
语法:
create table t_vip(
id int,
name varchar(255),
email varchar (255),
unique (name,email));
注:1.约束直接加到列之后称为列级约束,
2.没有添加到列之后,称为表级约束,需要给多个字段联合添加某一个约束时使用表级约束
3.not null没有表级约束;
4.not null与unique可以联合;
5.在mysql中,如果一个字段同时被not null和unique约束的话,自动变成主键字段,oracle不一样
5)pk 主键约束 primary key
术语:
-
主键约束
-
主键字段
-
主键值
什么是主键?作用?
主键值是每一行记录的唯一标识(身份证号)
主键特征:not null +unique
(复合主键)推荐使用单一主键!!
create table t_vip(
id int,
name varchar(255),
email varchar (255),
primary key (id,name)
);
注:一个表只能有一个主键!主键值推荐使用int bigint char ,不推荐使用varchar,通常是定长的
主键的分类
1)
-
单一主键
-
复合主键
2)
-
自然主键:主键值是一个自然数,和业务没关系
-
业务逐渐:主键值和业务关联,例如:银行卡账号做主键值
注:开发中,自然主键使用广泛,因为主键只需要不重复就行,不需要有意义,业务主键不好,因为当业务改变时,可能会影响主键值,尽量使用自然主键
mysql中,有一种机制可以帮助自动维护一个主键值
create table t_vip(
id int primary key auto increment,//表示自增,以1开始,以1递增
name varchar(255),
email varchar (255),
);
6)外键约束(FK)
术语?
-
外键约束
-
外键字段
-
外键值
班级表
学生表
注:当cno字段没有任何约束的时候,会导致数据失效,可能会出现102,但是102班级并不存在,为了保证cno职位100或者101,需要给cno添加外键约束,(FK)cno引用t_class的classno
注意:t_class是父表,t_student是子表
删除表:先删除子,在删除父表
创建表:先创建父表,在创建子表
删数据:先删子表数据,再删父表数据
插入数据,先插父表数据,再插子表数据
create table t_student(
no int(3),
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar (255)
cno int,
foreign key (cno)references t_class(classno));
Q:zi子表的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
A:不一定是主键,但必须具有unique属性,
因为,可以将父表的主键换成别的字段,自然classno就不是主键了,但是父表的字段必须是unique字段,因为如果不唯一,就会查到两条记录,产生歧义
Q:外键可以为null吗?
A:可以
17.存储引擎
1)什么是存储引擎,有什么用?
存储引擎是mysql中特有的一个属于,其他数据库没有
实际上存储引擎是一个表存储/组织数据的方法
2)如何给表添加指定的存储引擎?
在建表的最后,小括号右边使用Engine指定存储引擎,default charset +指定字符编码方法
MySQL默认的引擎是InnoDB,编码:UTF-8
3)查看mysql支持哪些存储引擎?
show engines \g
mysql支持9大存储引擎,5.5.36支持8个,版本不同,支持情况不同
4)常用的存储引擎
-
MyISAM:
特征:三个文件表示每个表
格式文件:结构定义(mytable.frm)
数据文件:数据表行的内容(mytable.MYD)
索引文件:索引类似是一本书的目录(mytable.MYI)
提示:对一张表来说,只要是逐渐,或者加有unique约束的字段,都会自动创建索引
优势:可以被转换成压缩,只读表来节省空间
-
InnoDB:mysql默认的存储引擎,也是一个重量级的存储引擎
该存储引擎支持事务,支持数据库崩溃后的自动回复机制
特点:非常安全
特征:1.每个InnoDB表在数据库目录中以.frm格式文件表示
2.InnoDB表空间tablespace被用于存储表的内容
表空间是一个逻辑名称,存储数据+索引
3.提供一组用来记录事务活动的日志文件
4.用commit(提交)savepoint还有rollback(回滚)支持事务处理
5.提供全ACID兼容
6.在mysql服务器崩溃后提供自动恢复
7.多版本(MVCC)和行级锁定
8.支持外键及引用的完整性,包括级联删除和更新
特点:支持事务,保证数据安全,效率不高,且不可压缩,不能转换为只读,不能很好的节省空间,MyISAM不支持事务
-
MEMORY:表数据存储在内存中,且行的长度固定,所以是的引擎速度很快
特征:1)数据库目录内,每个表均以.frm格式文件表示
2)date+index在memory中,目的:快,查询快
3)表级锁机制
4)不包含BLOB字段或TEXT
以前被称为HEAP引擎
优点:查询效率最高,
缺点:不安全,关键后数据消失
18.事务
1)什么是事务?
一个事务是一个完整的业务逻辑,最小的工作单元,不可再分,要么同时成功,要么同时失败
2)DML语句才会有事务一说,其余和事务无关(增:insert ;删:delete;改:update)
只要操作涉及增删改操作,那就一定要考虑安全问题,数据安全第一位!!
3)假设所有业务只要一条DML语句就能完成,还有必要存在事务机制吗?
正是因为做某件事需要多条DML语句联合才能完成,所以需要事务机制的存在;如果任何一件复杂事都能一条DML语句搞定,事务就没有存在的价值。
本质上:一条事务时多条DML语句同时成功或者同时失败
4)事务时如何做到多条DML语句同时成功同时失败的?
InnoDB:提供一组用来记录事务性活动的日志文件
事务开启:事务执行过程中,每一条DML语句都会记录到事务性活动的日志文件中
insert,delete,update:事务执行过程中,既可以提交事务,也可以混滚事务
事务结束:
-
提交事务?清空事务活动性文件,将数据全部持久化到数据库表中,提交事务标志着事务的结束,并且是成功的结束。
-
回滚事务?清空事务活动性文件,但会将所有DML操作全部撤销,回滚标志事务结束,并且是一种全部失败的结束
5)如何提交/回滚事务
-
commit;提交
-
rollback;回滚(只能回滚到上一次提交点)
事务对应单词:transaction
mysql默认事务为自动提交(每执行一条DML语句,提交一次)
这种自动提交不符合开发习惯,因为一个业务需要多条DML共同执行才可以完成,为了保证数据安全,必须同时成功在提交
如何关闭自动提交?
先执行start transaction;//开启事务,关闭自动提交机制
DML语句;.....
commit;//提交事务
rollback;//回到commit之后的语句
6)事务包括哪些特性
A:atonamic原子性(事务是最小的工作单元,不可再分)
C:一致性consistence(所有事物要求,在同一事物中,所有操作必须同时成功,或者同时失败,以保证数据的一致性
I:隔离性(事务a和事务b有一定的隔离性,a事务操作一张表,b事务操作会怎么样?(多线程并发访问)
D:持久性(事务最终结束的一个保障,事务提交,相当于将没有保存到硬盘上的数据保存到硬盘上
7)重点:隔离性
a与b教室中的一道墙,墙的厚度表示隔离级别
四个级别:
-
读未提交:(read uncommitted)级别最低
-
读已提交:(read committed)
-
可重复读:(repeatable read)
-
串行化/序列化:(serializable)级别最高
1)事务a可以读到事务b未提交的数据(没提交就读到了)
这种隔离级别会出现脏读 dirty read现象,称为读到了脏数据,该级别一般是理论上的,大多数数据库隔离级别都是2档起步
2)事务a可以读到事务b提交之后的数据(开启事务之后,第一次读到的数据是三条,当前事务还没有结束,可能第二次读取的时候,读取的是四条,3!=4,称为不可重复读。
该隔离级别解决了脏读现象,但是存在不可重复读数据的问题
(这种级别是比较真实的数据,每一次读到的数据绝对真实,oracle默认级别)
3)事务a开启之后,无论多久,每一次事务a读到的数据都是一致的(提交之后也读不到,永远读取的都是刚开启事务时的数据)
即使事务b将数据已经修改,并且提交,事务a读取到的数据还是没发生改变,这就是重复读
可重复读解决了不可重复读问题,但存在幻影读,每次读取的数据都是幻象,不够真实
例子:早上九点开启事务,只要事务不结束,到晚上九点,读到的数据都一样
(mysql默认的事务级别就是可重复读)
4)最高的隔离级别,效率最低,解决所有问题
该级别表示事务排队,不能并发!
(类似于线程同步synchronized,事务同步)
每次读取的数据是最真实的,效率最低
8)验证各种级别
查询隔离级别:select @@ tx-isolation;
设置级别:set global transaction isolation level read uncommitted;
19.索引
1)什么是索引?
索引添加到数据库的字段上,为了提高查询效率存在的一种机制,一张表的一个字段可以添加一个索引,也可以多个字段连个起来添加索引,详单与一本书目录,为了缩小扫描范围
查找的两种方式
-
全盘扫面
-
通过索引扫描,在局部性扫描,快速查找
注:使用过程中,目录需要排序,因为排过序会有区间查找一说(缩小扫描范围)
mysql中索引需要排序,这个排序和Treeset数据结构相同,底层是一个自平衡的二叉树(索引是b-tree结构)
2)实现原理
-
主键上会自动添加索引对象,id是pk,在mysql中,某一个字段有unique约束的话,也会自动创建索引对象
-
在任何一张表的任何一条记录,在硬盘存储上都有一个硬盘物理存储编号
-
mysql中索引是单独的对象,不同的引擎以不同的形式存在,在MYISAM中,索引村塾在.MYI,在InnoDB中,索引存储在逻辑名称为tablespace中,在MEMORY,存储在内存中,无论存储在哪里,在mysql中都是一个树形式存在。(自平衡二叉树:b-tree)
3)主键上,unique字段上都会添加索引(mysql)
什么条件下,会考虑给字段添加索引?
-
数据量大
-
字段经常出现在where后面,以条件形式存在,总是被扫描
-
该字段很少DML操作,(因为DML之后,索引要重新排列)
建议不要太多索引,因为索引需要维护,太多会降低系统性能,建议通过主键或unique约束的字段去查询,效率较高
4)创建索引/删除,语法?
创建索引:create index emp_ename_index on emp(ename);
删除索引:drop index emp_ename_index on emp;
5)查看sql语句是否使用索引进行检索
explain sekect *from emp where ename ='KING';
查看扫描了多少条记录,查看记录row值或者看type字段,若为all,则是全表扫描
6)索引失效
-
select *from emp where ename like '%T';
ename 即使添加了索引也不会走索引,为什么?
因为模糊匹配中,以'%'开头了!尽量避免模糊查询的时候,以'%'开始,这是一种优化策略!!
-
使用or的时候会失效,若要使用or,那么要求or两边的条件字段都要有索引,一边有也会失效,不建议使用or的原因
-
使用复合索引,没有使用左侧的列查找,索引失效
什么是复合索引?
两个字段或者多个字段联合起来添加一个索引
create index emp_job_sal_index on emp(emp,sal);
explain select *from emp where job='manager';索引扫描
explain select *from emp where sal=800;索引失效,全盘扫描
-
在where中索引列参与运算,索引失效
create index emp_sal_index on emp(sal);
explain select *from emp where sal=800;走索引
explain select *from emp where sal+1=800;索引失效
-
where中索引列使用函数
explain select *from emp where lower(ename)='smith';索引失效
7)索引是各种数据库优化的重要手段,优先考虑索引
分类:
-
单一索引
-
复合索引
-
主键索引
-
唯一性索引//在唯一性比较弱的字段添加索引,用处不大
20.视图(view)
1)定义
站在不同角度看待同一份数据
2)创建/删除视图
create view emp_view as select *from emp;
drop view emp_view;
注:只有DQL语句才能以view的形式创建(as之后必须是DQL语句)
3)视图作用
可以对视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作!!(特点:操作view会影响原表)
4)view对象在开发中作用:
sql语句复杂,需要在不同位置反复使用,每次使用都需要重新写,类似于引用,将大量的sql语句做成view,用到这些sql语句时使用view,简化开发。并且利于后期维护,修改时只需要修改view映射的sql语句
以后面向view开发时,使用view等同于使用table,对view进行增删改查(CRUD 查:create,检索:retrive,update,delete)
view存储在硬盘上,而不是内存中
21.数据库的三范式
1)数据库范式定义:数据库设计依据,教你怎么设计数据表
2)三个范式(重点)
-
要求任何一张表,必须有主键,每个字段原子性不可再分
-
要求所有非主键字段,完全依赖主键,不要产生部份依赖(建立在第一范式的基础上)
-
(建立在第二范式的基础上)要求所有非主键字段直接依赖主键,不要产生传递依赖
(使用上述的三范式进行数据库表的设计,可以避免数据冗余,空间浪费)
3)第一范式(最重要,最核心,所有表设计都系要满足)
要求任何一张表,必须有主键,每个字段原子性不可再分
4)第二范式
-
学生表
学生编号(PK) | 名字 |
---|---|
1001 | 张三 |
1002 | 李四 |
1003 | 王五 |
-
教师表
教师编号(PK) | 名字 |
---|---|
001 | 王老师 |
002 | 赵老师 |
不满足第二范式,因为张三依赖1001,王老师依赖001,产生部份依赖(数据冗余,空间浪费)
添加第三张表
-
学生,教师关系表
id(PK) | 学生编号(FK) | 教师编号(FK) |
---|---|---|
1 | 1001 | 001 |
2 | 1002 | 002 |
3 | 1003 | 001 |
口诀1:多对多,三张表,关系表两个外键
5)第三范式
学生编号(PK) | 姓名 | 班级编号 | 班级名 |
---|---|---|---|
1001 | 张三 | 01 | 1班 |
1002 | 李四 | 02 | 2班 |
1003 | 王五 | 03 | 3班 |
1004 | 赵六 | 03 | 3班 |
以上表:班级学生关系表,1对多关系,一个教室多个学生
满足:1范式,2范式(因为主键不是复合主键,没有产生部份依赖)
不满足:3范式(产生了传递依赖)
1班依赖01编号,01依赖1001,产生传递依赖
怎么设计一对多?(分成两张)
-
班级表
班级编号(PK) | 名称 |
---|---|
01 | 1班 |
02 | 2班 |
03 | 3班 |
-
学生表
学生编号(PK) | 名字 | 班级编号(FK) |
---|---|---|
1001 | 张三 | 01 |
1002 | 李四 | 02 |
1003 | 王五 | 03 |
1004 | 赵六 | 03 |
口诀2:一对多(一个班级对应多个学生),两张表,多的表(学生表)加外键
口诀3:一对一,外键唯一,将庞大的表拆成两个,其中一个表加上外键和unique约束
6)总结:三范式是理论上的,实际与理论有偏差,最终目的满足客户需求,有时拿冗余换执行速度,在sal中,表与表连接次数越多,效率越低(笛卡尔积现象)。有时存在冗余,但是为了减少表连接次数,也是合理的,对于开发人员来说,sql语句编写难度也会降低。