做网站需要多少钱 网络服务,网站建设的专业知识,旅游网站建设维护,网站之家查询-- 最好的选择不是最明智的#xff0c;而是最勇敢的#xff0c;最能体现我们真实意愿的选择。 MySQL数据库基础知识点总结
一、概念 数据库#xff1a;DataBase#xff0c;简称DB。按照一定格式存储数据的一些文件的组合顾名思义: 存储数据的仓库#xff0c;实际上就是一…-- 最好的选择不是最明智的而是最勇敢的最能体现我们真实意愿的选择。 MySQL数据库基础知识点总结
一、概念 数据库DataBase简称DB。按照一定格式存储数据的一些文件的组合顾名思义: 存储数据的仓库实际上就是一堆文件。这些文件中存储了具有特定格式的数据。 数据库管理系统DataBaseManagement简称DBMS。数据库管理系统是专门用来管理数据库中数据的数据库管理系统可以对数据库当中的数据进行增删改查。 常见的数据库管理系统: MySQL、 OracleMS SqlServer、 DB2、 sybase等 SQL: 结构化查询语言程序员需要学习SQL语句程序员通过编写SQL语句然后DBMS负责执行SQL语句最终来完成数据库中数据的增删改查操作。 以上三者关系 DBMS --(执行)-- SQL --(操作)-- DB 二、基础 在Windows操作系统中使用命令来启动和停止MySQL服务 net stop MySQL;
net start MySQL;
#其他服务的启停也可以使用以上命令修改服务名称即可使用客户端登录MySQL数据库前提MySQL安装了服务启动了 使用bin目录下的mysql.exe命令来连接mysql数据库服务器 本地登录显示编写密码 mysql -uroot -p123 #root是用户名123是密码本地登录隐藏密码 mysql -uroot -p #p后面不加密码直接回车表table 数据库中是以表格的形式表示数据的 任何一张表都有行和列 行row被称为数据/记录。 列column被称为字段。[每一个字段都有字段名、数据类型、约束等属性] SQL语句的分类 DQL数据查询语言凡是带有select关键字的都是查询语句 select… DML数据操作语言凡是对表当中的数据进行增删改的都是DML insert增 delete删 update改 注意DML主要是操作表中的数据data。 DDL数据定义语言凡是带有create、drop、alter的都是DDL create新建等同于增 drop删除 alter修改 注意DDL主要是操作表结构。 TCL事务控制语言 commit事务提交 rollback事务回滚 DCL数据控制语言 grant授权 revoke撤销权限 …… 导入.sql数据 source D:\document\mysql\node.sql #路径中不要有中文MySQL常用命令 *以下命令不区分大小写且命令要有;才能执行 退出mysql exit查看mysql中有哪些数据库 show databases; #以英文分号结尾
#mysql默认自带了4个数据库选择使用某个数据库 use 数据库名;创建数据库 create database 数据库名;查看某个数据库下的表 show tables;查看表中的数据 select * from 表名;查看表的结构不看表的数据 desc 表名; # “describe 表名;” 的缩写查看MySQL数据库的版本号 select version();查看当前使用的数据库 select database();增删改查又叫做CRUD Create Retrive Update Delete 三、查询
一单表查询
1、简单查询
查询一个字段
select 字段名 from 表名;查询多个字段
select 字段1,字段2,... from 表名;查询所有字段
select * from 表名; #这种方式效率低、可读性差在实际开发中不建议使用
select 所有字段名 from 表名;给查询的列起别名
select 字段名 (as) 别名 from 表名;
#as可以省略原表列名不变只是将查询的字段显示为别名
#如果起的别名有空格可以用单引号or双引号将别名括起来对查询的字段进行运算操作
select 字段表达式 from 表名;
#比如 “select sal*12 as 年薪 from emp;” 注意在所有的数据库中字符串统一使用单引号括起来。单引号是标准双引号在Oracle数据库中用不了在MySQL中可以使用 select后面可以跟某个表的字段名(可以等同看做变量名)也可以跟字面量/字面值(数据)。 2、条件查询
条件查询需要用到where语句where必须放到from语句表的后面
select 字段1字段2... from 表名 where 条件;运算符说明等于或!不等于小于小于等于大于大于等于between...and...两个值之间等同于and要遵循左小右大闭区间is null为nullis not null表示不为空null不能用进行衡量and并且or或者in包含相当于多个ornot in表示不在这个范围内in后面加的是具体的值不是区间notnot可以取非主要用在is或in中likelike称为模糊查询支持%或下划线匹配 %匹配任意个字符 下划线一个下划线只匹配一个字符 \为转义字符 and和or的优先级and or。如果想让or先执行需要加()。 3、排序 单字段排序 select 字段 from 表名 order by 字段; #默认升序
select 字段 from 表名 order by 字段 desc; #指定降序
select 字段 from 表名 order by 字段 asc; #指定升序多字段排序 select 字段 from 表名 order by 字段a (desc/asc), 字段b (desc/asc); #先按照字段a进行排序序号相同的再按照字段b进行排序根据字段位置进行排序 select 字段 from 表名 order by 2; #2表示第2列按照第2列进行排序
#不建议使用这种方式列的顺序会改变不健壮条件查询排序 select ... from ... where ... order by ...; #关键字顺序不能变
#执行顺序from、where、select、order by4、数据处理函数/单行处理函数
单行处理函数一个输入对应一个输出
函数含义lower转换小写upper转换大写substr取子串 [ substr(被截取的字符串, 起始下标, 截取的长度) ]length取长度trim去空格str_to_date将字符串varchar类型转换成date类型date_format格式化日期。将date类型转换成具有一定格式的varchar字符串类型。 格式date_format(日期类型数据, 日期格式) 这个函数通常使用在查询日期方面。设置展示的日期格式。format设置千分位。format(数字, 格式)round四舍五入rand生成0~1的随机数ifnull可以将null转换成一个具体值[ ifnull(字段, 如果为null被当作哪个值) ]concat进行字符串的拼接case...when...then...when...then...else...end当怎样就怎么做当怎样就怎么做其余情况怎么做 实例 select lower(name) as name from student;
select substr(name,1,1) as name from student; #将会输出所有name的第1个字符
select name from student where substr(name,1,1)A; #相当于select name from student where name like A%;
select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name)-1)) as result from student; #将name字段的数据首字母大写
select round(1236.567, 2) as result form student; #round(数据, 保留小数位数)生成的结果行数为student表的行数
select name,job,sal as oldsal,(case job when MANAGER then sal*1.1 when SALEMAN then sal*1.5 else sal end) as newsal from emp;*在所有数据库中只要有null参与的数学运算结果就为null MySQL的日期格式 符号含义%Y年%m月%d日%h时%i分%s秒 5、分组函数/多行处理函数
多行处理函数输入多行输出一行。
函数含义count计数sum求和avg平均值max最大值min最小值 实例 select max(sal) from emp;分组函数在使用的时候必须先进行分组然后才能使用。如果没有对数据进行分组默认整张表为一组。 注意事项 分组函数自动忽略NULL你不需要提前对null进行处理。 count(*)和count(具体字段)的区别 count(*)统计表中的总行数。只要一行数据中有一列不为NULL则这行数据有效 count(具体字段)表示统计该字段下所有不为NULL的元素的总数。 分组函数不能直接使用在where子句中。 6、分组查询重点 分组查询语法 select ... from ... group by ...实例 #找出每个工作岗位的工资和
select job,sum(sal) from emp group by job;#找出每个部门不同工作岗位的最高薪资
#技巧两个字段联合成一个字段看
select deptno,job,max(sal) from emp group by deptno,job;重要结论 在一条select语句中如果有group by语句的话select后面只能跟参加分组的字段、分组函数。其他的不能跟。 having having可以对分完组后的数据再次进行过滤。 having不能单独使用不能代替where只能和group by搭配使用。 #找出每个部门最高薪资并显示最高薪资大于3000的
select deptno,max(sal) from emp group by deptno having max(sal)3000;
#或者先使用where过滤
select deptno,max(sal) from emp where sal3000 group by deptno;优化策略where和having优先选择where。 执行顺序
select ... from ... where ... group by ... having ... order by ...
#以上关键字只能按照这个顺序来不能颠倒以上语句的执行顺序from、where、group by、having、select、order by 实例 #找出每个岗位的平均薪资要求显示平均薪资大于1500的除MANAGER岗位之外要求按照平均薪资降序排
select job,avg(sal) as avgsal
from emp
where job MANAGER
group by job
having avg(sal)1500
order by avgsal desc;7、查询结果去重distinct
select distinct 字段 from 表名;distinct只能出现在所有字段的前面 distinct出现在两个字段之前则表示两个字段联合起来去除 实例 #统计工作岗位数量
select count(distinct job) from emp;二连接查询
*** 重点**
1、连接查询 连接查询分类 根据语法的年代分类SQL92、SQL99重点学习SQL99 根据表连接的方式分类内连接等值连接非等值连接自连接、外连接左外连接/左连接右外连接/右连接、全连接 笛卡尔积现象当两张表进行连接查询没有任何条件限制时最终查询结果的条数是两张表条数的乘积。数学现象 如何避免 连接时加条件满足这个条件的记录将会被筛选出来。 #各个员工对应的部门
select e.ename,d.dname from emp e,dept d where e.deptnod.deptno; #SQL92语法
#SQL99语法在‘内连接之等值连接’中此时最终查询的结果条数变少了匹配次数没有减少还是两张表的条数的乘积。通过笛卡尔积现象得出表的连接次数越多效率越低尽量避免表的连接次数。 2、内连接
等值连接 SQL99语法内连接 select ...
from a (inner) join b
on a和b的连接条件
where 筛选条件
#inner可以省略加上可读性更强表示内连接实例 #各个员工对应的部门
select e.ename,d.dname from emp e join dept d on e.deptnod.deptno; #SQL99语法将sql92中from后面的,换成joinwhere换成了onSQL92和SQL99对比 SQL92缺点结构不清晰表的连接条件和后期进一步筛选的条件都放到了where后面用and连接。 SQL99优点表连接的条件时独立的连接之后如果还需要进一步筛选则再往后添加where 在on后面是一个等值条件所以称为等值连接
非等值连接
非等值连接on后面的条件不是等值关系。 实例 #找出每个员工的薪资等级并显示员工名、薪资、等级
select e.ename, e.sal, s.grade
from emp e (inner) join salgrade s
on e.sal between s.losal and s.hisal;自连接
自连接技巧一张表看作两张表。 实例 #查询员工的上级领导要求显示员工名和对应的领导名
select a.ename as 员工名, b.ename as 领导名
from emp a join emp b
on a.mgrb.empno;
#将一张表看成两张表3、外连接 外连接与内连接的区别内连接中连接的两张表没有主次关系平等的在外连接中两张表连接产生主次关系。主要看join前面有无right/left来区分 带有right的是右外连接又叫做右连接。 带有left的是左外连接又叫做左连接。 任何一个右连接都有左连接的写法。 任何一个左连接都有右连接的写法。 外连接的查询结果条数一定是内连接的查询结果条数 实例 #各个员工对应的部门同时将没有员工对应的部门也显示出来
select e.ename,d.dname
from emp e right (outer) join dept d
on e.deptnod.deptno;
#右外连接这里的right表示将join关键字右边的这张表看成主表主要是为了将这张主表的数据全部查询出来捎带着关联查询左边的表emp。outer可以省略加上可读性就强一些表示外连接。 4、多表连接 语法 select ...
froma
joinb
ona和b的连接条件
joinc
ona和c的连接条件
joind
ona和d的连接条件
...一条SQL中内连接和外连接可以混合都可以出现。 实例 #找出每个员工的部门名称以及工资等级还有上级领导要求显示出员工名、领导名、部门名、薪资、薪资等级
select e.ename, l.ename, e.sal, d.dname, s.grade
from emp e
join dept d on e.deptnod.deptno
join salgrade s on e.sal between s.losal and s.hisal
left join emp l on e.mgrl.empno;三子查询
子查询select语句中嵌套select语句配嵌套的select语句称为子查询。
在where语句中使用子查询 实例 # 找出比最低工资高的员工名字和工资
select enamesal
from emp
where sal (select min(sal) from emp);在from语句中使用子查询
from后面的子查询可以将子查询的查询结果当作一张临时表。 实例 #找出每个岗位的平均工资的薪资等级
select t.*, s.grade
from (select job, avg(sal) as avgsal from emp group by job) t
on t.avgsal between s.losal and s.hisal; #这里不能使用t.avg(sal)在select语句中使用子查询
// 这个内容不需要掌握掌握即可 实例 #找出每个员工的部门名称要求显示员工名部门名
select e.ename, (select d.dname from dept d where e.deptnod.deptno) as dname from emp e;对于select后面的子查询来说这个子查询只能一次返回一条结果多于一条就报错了比如 select e.ename, e.deptno, (select dname from dept) as dname from emp; #报错四union union用于合并查询结果集 实例 #查询工作岗位是MANAGER和SALESMAN的员工
select enamejob from emp where jobMANAGER
union
select enamejob from emp where jobSALESMAN; #使用union
#或者
select enamejob from emp where jobMANAGER or jobSALESMAN;
#或者
select enamejob from emp where job in (MANAGER, SALESMAN);以上例子union的效率要高一些。对于表连接来说每连接一次新表其匹配次数都满足笛卡尔积。union可以减少匹配的次数并且完成两个结果集的拼接。 a 连接 b 连接 a 10条记录 b 10条记录 c 10条记录 匹配次数是: 1000 a 连接 b一个结果: 10 * 10 -- 100次 a 连接 C一个结果: 10*10 - 100次 使用union的话是: 100次 100次 200次。 (union把乘法变成了加法运算) 使用union的注意事项 两个要合并的结果集的列数要相同结果集合并时列的数据类型要相同。MySQL可以不同Oracle不行 五limit limit是将查询结果集的一部分取出来通常使用在分页查询中。 分页的作用提高用户体验因为一次性全都查出来用户体验差。可以一页一页翻页看。 用法 完整用法limit startIndex,length; startIndex是起始下标length是长度。起始下标从0开始。 缺省用法limit 5; 这是取前5默认起始下标为0。 注意MySQL中limit在order by之后执行 分页 每页显示pageSize条记录第pageNum页 limit (pageNum-1)*pageSize,pageSizeDQL语句大总结 select ...
from ...
where ...
group by ...
having ...
order by ...
limit ...执行顺序from-where-group by-having-select-order by-limit 四、表
1、表的创建建表create 建表属于DDL语句DDL包括create、drop、alter 语法格式 create table 表名(字段名1 数据类型,字段名2 数据类型,...
);表名建议以 t_ 或者 tbl_ 开始可读性强。表名和字段名都属于标识符 MySQL中常见的数据类型 数据类型含义varchar可变长的字符串 比较智能 可根据实际的数据长度动态分配空间 最长255位。 优点节省空间 缺点需要动态分配空间速度慢char定长字符串 不管实际的数据长度是多少都分配固定长度的空间去存储数据。 最长255位。 优点不需要动态分配空间速度快 缺点使用不恰当时可能会导致空间浪费int数字中的整数型。等同于Java中的int。 最长11位。bigint数字中的长整型。等同于Java中的long。float单精度浮点型数据。double双精度浮点型数据。date短日期类型。只包括年月日信息。 mysql短日期默认格式%Y-%m-%ddatetime长日期类型。包括年月日时分秒信息。 mysql长日期默认格式%Y-%m-%d %h:%i:%sclob字符大对象(Character Large Object)。最多可以存储4G的字符串。 超过225个字符的都要采用CLOB字符大对象来存储。 比如存储一篇文章存储一个简介说明。blob二进制大对象(Binary Large Object)。专门用来存储图片、声音、视频等流媒体数据。 往BLOB类型的字段上插入数据时需要使用IO流。 在数据类型后面加括号()是表示该字段的建议长度。 创建表时指定默认值在字段的数据类型后加 **default 默认要取的值 **。 数据库的命名规范所有标识符都小写单词和单词之间使用下划线进行衔接。 快速创建表表的复制 create table 要创建的表名 as select 字段名 from 要复制的表名 ...;原理将一个查询结果当作一张表新建可以完成表的快速复制。表创建出来同时表中的数据也存在了。 2、删除表drop 语法 drop table 表名; #当这张表不存在的时候会报错
drop table if exists 表名; #当表不存在时不会报错3、插入数据insert 语法 insert into 表名(字段名1, 字段名2, 字段名3, ...) values(值1, 值2, 值3,...);注意字段名和值的数量和数据类型要一一对应。每执行成功一次insert语句记录就会增加一条。如果没有给其他字段指定值其值默认为NULL创建表时已经指定默认值的除外。insert语句中的字段名可以省略相当于所有的字段名都被写上了。 mysql中获取系统当前时间now()函数获取的时间有时分秒信息是datetime类型 插入多条记录 insert into 表名1(字段名1, 字段名2, 字段名3, ...) values
(值1, 值2, 值3,...),
(值1, 值2, 值3,...),
...;将查询结果插入到一张表中 insert into 要插入的表名 select 字段名 from 要查询的表名; #很少用查询结果要符合要插入的表的数据类型 4、修改 updateDML 语法格式 update 表名 set 字段名1值1, 字段名2值2, ... where 条件;注意如果没有条件限制会导致全部数据更新。 5、删除数据 deleteDML 语法格式 delete from 表名 where 条件;注意如果没有条件整张表的数据会被全部删除。 delete删除数据的原理表中的数据被删除了但是这个数据在硬盘上的真实存储空间不会被释放。 缺点删除效率低。 优点支持回滚rollback删除后可以恢复数据。 快速删除表中的数据truncate truncate table 表名;缺点不支持回滚。 优点快速表被一次截断物理删除。 truncate是删除表中的数据表还在 delete属于DML语句truncate属于DDL语句。 6、对表结构的增删改
* 这个内容不重要 对表结构的修改添加一个字段删除一个字段删改一个字段 修改表结构是不需要写到Java代码中的。 使用alter语句。属于DDL语句 7、约束重点 约束constraint在创建表的时候就可以给表中的字段加上一些约束来保证这个表中数据的完整性、有效性。 约束包括哪些 非空约束not null 唯一性约束unique 主键约束primary key简称PK 外键约束foreign key简称FK 检查约束checkmysql不支持Oracle支持
1非空约束not null
非空约束not null约束的字段不能为NULL。 实例 create table t_vip(id int,name varchar(225) not null
);这里的name不能为null。 小tip xxx.sql这种文件被称为sql脚本文件。 sql脚本文件中编写了大量的sql语句。 sql脚本被执行时该文件的所有sql语句都会被执行。 批量执行sql语句时就能使用sql脚本文件。 source .sql文件路径not null只有列级约束没有表级约束。
2唯一性约束unique
单独字段唯一列级约束 实例 create table t_vip(id int,name varchar(255) unique,email varchar(255)
);每一条数据的name不能重复但可以为null。 两个字段联合唯一表级约束 在字段后面加上unique(字段名1, 字段名2) 实例 create table t_vip(id int,name varchar(255),email varchar(255),unique(name, email)
);需要给多个字段联合起来添加某个约束的时候需要使用表级约束。 unique和not null联合使用 实例 create table t_vip(id int,name varchar(255) not null unique
);mysql desc t_vip; ----------------------------------------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------------------------------------------------- | id | int(11) | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | ----------------------------------------------------------------------------------------- 在mysql中如果一个字段同时被not null和unique约束该字段就会自动变成主键字段。Oracle中不一样) 3主键约束primary key重点 相关术语 主键约束一种约束。 主键字段加上了主键约束的字段。 主键值主键字段中的每一个值。 主键 主键值是每一行记录的唯一标识。相当于身份证号任何一张表都要有主键没有主键则表无效。主键的特征not null unique主键值不能为null也不能重复 主键分类 (1)单一主键、复合主键 (2)自然主键主键值是一个自然数和业务没关系。 业务主键主键值和业务紧密关联例如使用银行卡号做主键值。 在实际开发中自然主键使用的比较多。主键只需要做到不重复即可不需要意义。主键如果跟业务挂钩业务一改变就会影响到主键。 单一主键实例 #列级约束
create table t_vip(id int primary key,name varchar(255)
);
#表级约束
create table t_vip(id int,name varchar(255),primary key(id)
);以上id为主键。 复合主键实例 create table t_vip(id int,name varchar(255),email varchar(255),primary key(id, name)
);以上id和name联合起来做复合主键。 在实际开发中不建议使用复合主键建议使用单一主键。复合主键比较复杂 一个表中主键约束只能添加一个不能加两个。主键只能有一个 建议使用intbigintchar等类型做主键值不建议用varchar来做主键。 在mysql中有一种可以自动维护一个主键的机制 create table t_vip(id int primary key auto_increment,name varchar(255)
);使用auto_increment后不需要在insert的时候给主键赋值主键会自动从1开始自增赋值。 4外键约束foreign key重点
相关术语 外键约束一种约束。 外键字段加上了外键约束的字段。 外键值外键字段中的每一个值。 语法 在字段定义后面加上 foreign key(要约束的字段名) references 被引用的表名(被引用的字段名)实例 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)
);被引用的表是父表/主表。 引用了父表的表是子表/从表。 顺序 删除表的顺序先删子再删父 创建表的顺序先创建父再创建子 插入数据的顺序先插入父再插入子 子表中的外键引用的父表中的某个字段被引用的这个字段可以不是主键但一定要有unique。被引用的字段具有唯一性外表值可以为null。 五、存储引擎
1、存储引擎的使用 存储引擎一个表存储/组织数据的方式。不同的存储引擎表存储的方式不同。mysql叫存储引擎Oracle不这么叫 数据库中的各表均被指定的存储引擎来处理。在创建表时 服务器可用的引擎依赖于以下因素MySQL的版本、服务器在开发时如何被配置、启动选项 给表指定存储引擎 在建表的时候可以在最后小括号 ) 的右边使用ENGINE来指定存储引擎默认为InnoDBCHARSET来指定这张表的字符编码方式默认为utf8。 create table 表名(...
)ENGINE存储引擎 default CHARSET字符编码方式;查看当前服务器中可使用的存储引擎 show engine \G版本不同支持情况不同。
2、常用的存储引擎 MyISAM 使用三个文件表示每个表 格式文件——存储表结构的定义mytable.frm 数据文件——存储表行的内容mytable.MYD 索引文件——存储表上的索引mytable..MYI 灵活的AUTO_INCREMENT字段处理。 优点可被转换成压缩、只读表来节省空间。 tips: 对于一张表来说只要有主键或者加有unique约束的字段上会自动船舰索引。 InnoDB MySQL默认的存储引擎。 - 每个InnoDB表在数据库目录以.frm格式文件表示 - InnoDB表空间tablespace被用于存储表的内容表空间是一个逻辑名称。表空间存储数据索引 - 提供一组用来记录事务性活动的日志文件 - 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理 - 提供全ACID兼容 - 在MySQL服务器奔溃后提供自动恢复 - 多版本(MVCC)和行级锁定 - 支持外键及引用的完整性包括级联删除和更新 - 缺点效率不是很高不能压缩不能转换为只读。不能很好的节省存储空间 - 特点最大特点支持事务支持数据库奔溃后自动恢复机制。安全 MEMORY 使用MEMORY存储引擎的表其数据存储在内存中且行的长度固定这两个特点使得MEMORY存储引擎非常快。 - 在数据库目录内每个表均以.frm格式的文件表示 - 表数据及索引被存储在内存中 - 表级锁机制 - 不能包含TEXT或BLOB字段 - 优点查询效率是最高的。不需要和硬盘交互。 - 缺点不安全关机之后数据消失。因为数据和索引都是在内存中。 六、事务
* 重要
1、概述 事务transaction一个事务就是一个完整的业务逻辑。是一个最小的工作单元不可再分。 什么是一个完整的业务逻辑 假设是转账从A账户向B账户中转账1w 将A账户的钱减去1wupdate语句 将B账户的钱加上1wupdate语句 这就是一个完整的业务逻辑。 以上的操作就是一个最小的工作单元这两个update语句要么同时成功要么同时失败这样才是一个完整的转账过程才能保证钱是正确的。 只有DML语句insert、delete、update才会有事务这一说其他语句和事务无关 因为只有以上的三个语句是对数据库表中数据进行增删改的。只要操作涉及到数据的增删改就一定要考虑事务。数据安全第一位 说到底一个事务本质上就是多条DML语句同时成功或者同时失败 如果所有的事务都只要一条DML语句就能完成的话就没有必要存在事务机制了。正是因为做某件事务的时候需要多条DML语句共同联合起来才能完成所以才需要事务。 在事务的执行过程中每一条DML操作都会记录到“事务性活动的日志文件”中。 2、事务的提交与回滚 在事务的执行过程中可以提交事务也可以回滚事务。 提交事务 清空事务性活动的日志文件将数据全部彻底持久化到数据库表中。 提交事务标志着事务的结束。并且是一种全部成功的结束。 回滚事务 将之前所有的DML操作全部撤销并且清空事务性活动的日志文件。 回滚事务标志着事务的结束。并且是一种全部失败的结束。 提交事务commit mysql默认情况下是支持自动提交事务的每执行一条语句则提交一次事务。不利于开发 怎么将mysql的自动提交机制关闭呢 #在执行事务前先开启事务
start transaction;手动提交事务 commit; #提交事务回滚事务rollback回滚永远只能回滚到上一次 rollback; #回滚事务回滚到start transaction之前或commit之后或是上条语句之后。 3、事务的隔离级别 事务的特性 A:原子性说明事务是最小的工作单元不可再分。 C:一致性所有事务要求在同一个事务中所有操作必须同时成功或者同时失败以保证数据的一致性。 I:隔离性A事务和B事务之间具有一定的隔离。 D:持久性事务最终结束的一个保障。事务提交就相当于将没有保存到硬盘上的数据保存到硬盘上。 事务的隔离性 隔离级别 相当于A教室和B教室之间有一道墙这道墙越厚表示隔离级别越高。 事务与事务之间的隔离级别 读未提交read uncommitted最低的隔离级别【没有提交就读到了】 事务A可以读取到事务B未提交的数据。 脏读现象事务A可以读取到事务B的脏数据。 这种隔离级别一般是理论上的大多数的数据库隔离级别都是二档起步。 读已提交read committed【提交之后才读到】 事务A只能读到事务B提交之后的数据。 缺点不可重复读取数据。在事务A开启之后事务B可能还有还未提交的事务正在进行中此时第一次读到的数据就与后面读到的数据不同了即事务A每次读取的数据不一定相等。 优点解决了脏读现象。且这种隔离级别每次读到的数据都是真实的。 Oracle数据库默认的隔离级别read committed 可重复读repeatable read【提交之后也读不到永远读取的都是刚开启事务时读到的数据】 事务A开启之后不管是多久每次在事务A中读到的数据都是一致的。即使事务B已经将数据改变并且提交了事务A读取到的数据还是没有发生改变。 优点解决了不可重复读取数据。 缺点会出现幻影读。每一次读取到的数据都是幻象不真实。 mysql中默认的事务隔离级别repeatable read 序列化/串行化serializable最高的隔离级别 这种隔离级别最高效率最低。 表示事务排队不能并发 每一次读到的数据都是最真实的并且效率是最低的。 设置全局事务隔离级别 set global transaction isolation level read uncommitted; #设置全局事务隔离级别可以将read committed换成其他隔离级别查看隔离级别 select tx_isolation;七、索引 概述 索引索引是在数据库表的字段上添加的是为了提高查询效率存在的一种机制。 一张表的一个字段可以添加一个索引多个字段联合起来也可以添加索引。 索引相当于一本书的目录是为了缩小扫描范围而存在的一种机制。 MySQL在查询方面主要有两种方式全表扫描、根据索引检索。 注意在mysql数据库中的索引也是需要排序的并且这个索引的排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树遵循左小右大原则存放采用中序遍历方式遍历取数据。在索引当中是一个B-Tree数据结构。表中的字段不会动是索引进行排序 索引是各种数据库进行优化的重要手段。 索引的实现原理 在任何数据库上主键上都会自动添加索引对象。 在mysql中一个字段上如果有unique约束的话也会自动创建索引对象。 在任何数据库中任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。 在mysql中索引是一个单独的对象。不同的存储引擎以不同的形式存在在MyISAM存储引擎中索引存储在一个.MYI文件中。在InnoDB中索引存储在一个逻辑名称叫tablespace的表中。在MEMORY存储引擎中索引存储在内存中。不管索引存储在哪索引在MySQL中都是以一个树的形式存在自平衡二叉树B-Tree。 索引的实现原理就是缩小扫描的范围避免全表扫描。 什么条件下会考虑给字段添加索引 条件1数据量庞大 条件2该字段经常出现在where后面即该字段总是被扫描 条件3该字段很少使用DMLinsert、delete、update操作因为使用DML后索引需要重新排序 建议不要随意添加索引因为索引也是要维护的太多了反而会降低系统的性能。 建议通过主键查询建议通过unique约束的字段进行查询效率较高。 索引相关语法 创建索引 create index 索引名 on 表名(字段名);删除索引 drop index 索引名 on 表名;在MySQL中查看一个SQL语句是否使用了索引进行检索 explain 语句;索引失效 失效的第一种情况模糊匹配中以%开头 实例 select * from emp where ename like %T;以上语句索引失效即使有添加索引也不会使用索引。因为模糊匹配中以%开头了。所以尽量避免模糊查询的时候以%开头。一种优化的策略 失效的第二种情况使用or的时候其中一个字段没有索引 使用or的时候只有or左右的两个字段都有索引索引才不会失效。如果其中一边有一个字段没有索引则索引失效。建议少用or。 失效的第三种情况使用复合索引时没有使用左侧的列查找 使用复合索引的时候如果没有使用左侧的列查找索引就会失效。 复合索引两个字段或者更多的字段联合起来添加一个索引。 create index 索引名 on 表名(字段名1,字段名2);失效的第四种情况在where中索引参加了运算 实例 select * from emp where sal1 800; #索引失效失效的第五种情况在where中的索引列使用了函数 实例 select * from emp where lower(ename) smith; #索引失效... ... 索引的分类 单一索引在一个字段上添加索引 复合索引两个字段或者更多的字段上添加索引 主键索引主键上添加索引 唯一性索引具有unique约束的字段上添加索引唯一性比较弱的字段上添加索引用处不大 八、视图 视图view站在不同的角度去看待同一份数据。 创建视图对象create view create view 视图名 as DQL语句; #as后面只能是DQL语句实例 create view dept2_view as select * from dept;删除视图drop view drop view 视图名;注意只有DQL语句才能以view 的形式创建。 视图的特点通过对视图的增删改会影响原表的数据。相当于引用 使用视图的时候可以像使用table一样 视图的作用 简化sql语句简化开发利于维护。可以将复杂的sql语句以视图对象的形式新建 九、DBA命令 数据的导入和导出数据的备份 数据的导入 mysqldump 数据库名要保存的.sql文件绝对路径 -u用户名 -p密码; #导出整个数据库
mysqldump 数据库名 表名要保存的.sql文件绝对路径 -u用户名 -p密码; #导出数据库中指定的表以上命令在Windows的dos命令窗口中运行不是在MySQL中运行。 数据的导出 # 1、创建数据库
create database 数据库名;
# 2、使用数据库
use 数据库名;
# 3、初始化数据库
source .sql文件路径;以上命令在MySQL上运行。 十、数据库设计的三范式
* 面试常问
数据库设计范式数据库表的设计依据。
1、第一范式要求任何一张表必须有主键每一个字段原子性不可分。最核心、最重要的
2、第二范式建立在第一范式的基础之上要求所有非主键字段完全依赖于主键不要产生部分依赖。数据冗余空间浪费 tips多对多三张表关系表两个外键。 3、第三范式建立在第二范式的基础之上要求所有非主键字段必须直接依赖主键不要产生传递。 tips一对多两张表多的表加外键。 一对一一对一外键唯一。 数据库设计三范式是理论上的。 实践和理论有的时候是有偏差的。 最终的目的都是为了满足客户的需求有的时候会拿冗余换执行速度。 因为在sql中表和表之间连接次数越多效率越低。笛卡尔积 而且对于开发人员来说sql语句的编写难度也会降低。
