Mysql学习大全(涵盖所有需要用到的mysql知识,附有图文)

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)
11001001
21002002
31003001

口诀1:多对多,三张表,关系表两个外键

5)第三范式

学生编号(PK)姓名班级编号班级名
1001张三011班
1002李四022班
1003王五033班
1004赵六033班

以上表:班级学生关系表,1对多关系,一个教室多个学生

满足:1范式,2范式(因为主键不是复合主键,没有产生部份依赖)

不满足:3范式(产生了传递依赖)

1班依赖01编号,01依赖1001,产生传递依赖

怎么设计一对多?(分成两张

  • 班级表

班级编号(PK)名称
011班
022班
033班
  • 学生表

学生编号(PK)名字班级编号(FK)
1001张三01
1002李四02
1003王五03
1004赵六03

口诀2:一对多(一个班级对应多个学生),两张表,多的表(学生表)加外键

口诀3:一对一,外键唯一,将庞大的表拆成两个,其中一个表加上外键和unique约束

6)总结:三范式是理论上的,实际与理论有偏差,最终目的满足客户需求,有时拿冗余换执行速度,在sal中,表与表连接次数越多,效率越低(笛卡尔积现象)。有时存在冗余,但是为了减少表连接次数,也是合理的,对于开发人员来说,sql语句编写难度也会降低。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值