mysql学习笔记
概述
专业名词
-
数据库(DataBase/DB)
按照一定格式存储一些文件的组合。
顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中存储了具有特定格式的数据
-
数据库管理系统(DataBaseManagement/DBMS)
专门用来管理数据库中数据的。
常见的数据库管理系统:MySQL、Oracle、MS SQLServer、sybase...
-
SQL(结构化查询语言)
程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查。
SQL是一套标准,程序员主要学习的就是SQL数据。
SQL可以在多种数据库管理系统中执行,最终来完成数据库的数据管理。
-
三者之间的关系
DBMS --执行--> SQL --操作--> DB
-
CRUD
- C:Create(增)
- R:Retrieve(查)
- U:Update(改)
- D:Delete(删)
表(table)
表是一种结构化的文件,可以用来存储特定类型的数据。
表是数据库中最基本的单元。
数据库当中是以表格的形式表示数据的,因为比较直观。
任何一张表都有行和列:
行(row):被称为数据/记录
列(column):被称为字段
常用命令
1.启动和停止mysql服务
- 命令:
//启动、停止mysql服务
net start mysql
net stop mysql
//其他服务的启动和停止都可以采用以下的命令
net start 服务名称
net stop 服务名称
2.登录mysql数据库
2.1本地登录(显示编写密码)
- 命令:
mysql -u用户名 -p密码
注:root是超级用户管理员
2.2本地登录(隐藏密码登录)
- 命令:
mysql -u用户名 -p //【回车后便可输入密码】
Enter password: ***
3.常用命令
命令名 | 作用 |
---|---|
exit | 退出mysql |
show databases; | 查看mysql中存在的数据库 |
show tables; | 查看数据库中存在的表 |
create database 数据库名称; | 创建数据库 |
use 数据库名称; | 使用某个数据库 |
select database(); | 查看当前使用的是哪个数据库 |
select version(); | 查看mysql数据库的版本号 |
desc 表名; | 查看表的结构 |
show create table 表名; | 查看表的创建语句 |
\c | 终止正在编写的语句 |
4.DBA常用命令
重点掌握:数据的导入和导出
4.1数据导入
//1.使用数据库
use 数据库名称;
//2.导入数据
source 文件路径;
- sql文件中编写了大量的sql文件
执行sql文件时,该文件中说有的sql语句全部被执行
如何执行sql语句:
source 脚本路径\脚本文件名.sql;
4.2数据导出
//导出数据库
mysqldump 数据库名称>文件路径 -uroot -p密码;
//导出数据库中的某张表
mysqldump 数据库名称 表名>文件路径 -uroot -p密码;
学习用表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
函数
一、数据处理函数
- 特点:一个输入对应一个输出。
说明:
substr函数中,起始下标从1开始,没有0
字符串的拼接不可以用 “+”,应使用concat函数
在所有数据库当中,只要有null参与的数学运算,最终结果都是null
trim函数,只能去除首尾的空格,不可以去掉中间的空格
mysql的日期格式:%Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒
timestampdiff函数的间隔类型:
SECOND 秒,MINUTE 分钟, HOUR 小时,DAY 天,WEEK 星期
MONTH 月,QUARTER 季度, YEAR 年
单行处理函数名 | 说明 |
---|---|
low() | 转换小写 |
upper() | 转换大写 |
substr(被截取字符串,起始下标,截取长度) | 取子串 |
concat(str1, str2) | 拼接字符串 |
length() | 取长度 |
trim() | 去空格 |
round(数据,保留位数) | 四舍五入 |
rand() | 生成随机数 |
ifnull(数据,被当做哪个值) | 处理null |
str_to_date(str,日期格式) | varchar转换成date |
date_format(date,日期格式) | date转换成varchar |
now() | 获取当前时间 |
timestampdiff(间隔类型, 前一个日期, 后一个日期) | 计算时间差 |
查询 1981-02-20入职的员工
select * from emp where hiredate = '1981-02-20';
select * from emp where hiredate = str_to_date('02-20-1981', '%m-%d-%Y');
补充:case...when...then...when...then...else...end
//当员工的工作岗位是MANAGER的时候,工资上调10%;
//当工作岗位是SALESNMAN的时候,工资上调50%,其他正常。
select ename,job,sal as oldsal,
(case job when 'MANAGER' then sal*1.1 when 'SALESNMAN' then sal*1.5 else sal end) newsalfrom emp;
二、分组函数
- 特点:输入多行,最终输出一行。
注意:
-
分组函数在使用的时候先进行分组,然后才能用。【所以不能直接在where子句中使用】
-
如果没有对数据进行分组,整张表默认为一组。
-
分组函数自动忽略null,不需要提前对null进行处理。
多行处理函数名 | 说明 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
一些说明:
count(具体字段):表示统计该字段下所有不为null的元素的总数。
count(*):统计表当中的总行数。
SQL语句
一、DQL
数据查询语言(凡是带有select关键字的都是查询语句)
1.简单查询
- 查询一个字段
select 字段名 from 表名;
//示例:查询员工姓名
select ename from emp;
- 查询多个字段
select 字段名1, 字段名2... from 表名;
//示例:查询员工的编号和姓名
select empno, ename from emp;
- 查询所有字段
select * from 表名;
注:java连接数据库的时候,不建议使用,效率低,可读性差
建议写明字段,这样可读性强
2.起别名
- 使用as关键字起别名:【起别名不会修改原表列名】
select 字段名 as 别名 from 表名;
as关键字可以省略:
select 字段名 别名 from dept;
-
字段可以使用数学表达式
-
假设起别名时,别名里面有空格或中文,解决方法:
select deptno,dname 'dept name' from dept;//加单引号【推荐】select deptno,dname "dept name" from dept;//加双引号
在所有的数据库中,字符串统一使用单引号括起来。
单引号是标准,双引号在oracle数据库中用不了。
3.条件查询
- 语法格式:
select 字段1,字段2...from 表名where 条件;
- 运算符
运算符 | 说明 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between...and... | 两个值之间 |
is null | 为null |
and | 并且 |
or | 或者 |
in | 包含,相当于多个or |
not | 取非,主要用于is或in中 |
like | 模糊查询 |
注意点:
使用between...and...时,必须遵循左小右大。注意它是包含最大最小值的。
在数据库中,null不能使用等号进行衡量。
因为数据库中的null代表什么也没有,不是一个值,所以不能使用等号衡量。
and和or同时出现时,and优先级更高。若想让or先执行,需要加小括号。
模糊查询时,%匹配任意个(包括0)字符,下划线匹配一个字符。
查找字段中有下划线的,可以使用转义符。
4.排序
- 语法格式
select 字段1,字段2...from 表名order by 字段1,字段2...
说明:
- 默认为升序asc
- 指定降序在字段后添加 desc
- 按照多个字段排序时,排在前面的字段起主导地位
了解:根据字段的位置也可以进行排序
select ename,sal from emp order by 2;//按照查询结果的第二列排序//健壮性差,不建议在开发中这样写
5.分组查询
- 语法结构
select 字段名...from 表名 group by 字段名...;
在一条select语句当中,如果有group by语句的话,select后面只能跟:
参加分组的字段,以及分组函数。其他的一律不能跟。
- 使用having可以对分完组之后的数据进一步的过滤
- having不能单独使用,必须和group by联合使用。
- 优先选择where。【where的效率相对于having要高】
6.小总结(单表查询)
6.1、语句执行顺序
select ...from ...where ...group by ...having ...order by ...//执行顺序:from-->where-->group by-->having-->select-->order by
6.2、去重(distinct)
- 只能出现在所有字段的最前方
- 出现在两个字段之前,表示将两个字段联合起来去重
6.3、select使用细节说明
select后面可以跟某个表的字段名,也可以跟字面量。
//select后面跟字面量的情况select 21000 as num from dept; +-------+ | num | +-------+ | 21000 | | 21000 | | 21000 | | 21000 | +-------+mysql> select 'abc' as bieming from emp; +---------+ | bieming | +---------+ | abc | | abc | | abc | +---------+
7.连接查询(多表查询)
- 概念:
从多张表联合起来查询数据,也可以叫做跨表查询
当两张表进行连接查询时,没有任何条件限制的时候,
最终查询结果条数是两张表条数的乘积。这种现象被称为:笛卡尔积现象。
如何避免?连接时添加条件,满足这个条件的记录被筛选出来
注意:只是最终查询结果减少,设计匹配的次数并没有减少
7.1内连接
- 特点:
-
被连接的两张表,没有主次关系,是平等的。
-
完成能够匹配上这个条件的数据查询出来。
- 等值连接
查询每个员工所在部门名称,显示员工名和部门名?
select e.ename, d.dnamefrom emp einner join //inner可以省略,保留可提高可读性 dept don e.deptno = d.deptno; //条件是等量关系,所以被称为等值连接
- 非等值连接
找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
select e.ename, e.sal, s.gradefrom emp einner join //inner可以省略,保留可提高可读性 salgrade son e.sal between s.losal and hisal; //条件不是一个等量关系,称为非等值连接
- 自连接
查询员工的上级领导,要求显示员工名和对应的领导名?
select a.ename, b.enamefrom emp ainner join //inner可以省略,保留可提高可读性 emp bon a.mgr = b.empno; //技巧:一张表看做两张表
7.2外连接
- 特点:
- 在外连接表中,两张表连接,产生了主次关系。
- 把主表的全部数据查询出来,捎带着关联查询左边的表。
- 外连接的查询条数一定是 >= 内连接的查询结果
- 右外连接(右连接)
查询每个员工所在部门名称,显示全部的部门名和员工名?
select e.ename, d.dnamefrom emp eright outer join //outer可以省略,保留可以提高可读性 dept don e.deptno = d.deptno;
- 左外连接(左连接)
查询每个员工的上级领导,要求显示所有员工的名字和领导名?
select e.ename, l.enamefrom emp eleft outer join //outer可以省略,保留可以提高可读性 emp lon e.mgr = l.empno;
7.3多表连接
- 语法格式:
select ...from ajoin bon //a和b的连接条件join con //a和c的连接条件right join don //a和d的连接条件
找出每个员工的部门名称以及工资等级,还有上级领导,
要求显示员工名、领导名、部门名、薪资、薪资等级?
select e.ename, e.sal, d.dname, s.grade, l.enamefrom emp ejoin dept d on e.deptno = d.deptnojoin salgrade son e.sal between s.losal and s.hisalleft join emp lon e.mgr = l.empno;
8.子查询
- 概念:
select语句中嵌套select语句,被嵌套的select语句被称为子查询。
子查询可以出现在select、from、where中。
- where子句中的子查询
找出比最低工资高的员工姓名和工资?
select ename, salfrom empwhere sal > (select min(sal) from emp);
- from子句中的子查询
找出每个岗位的平均工资的薪资等级?
select t.*, s.gradefrom (select job, avg(sal) avgsal from emp group by job) tjoin salgrade son t.avgsal between s.losal and s.hisal;
- select子句中的子查询(了解即可)
找出每个员工的部门名称,要求显示员工名,部门名?
select e.ename,e.deptno, (select d.dname from dept d where e.deptno = d.deptno) dnamefrom emp e;
注意:这种子查询一次只能返回一次结果。多于一条,就报错。
9.union
- 作用:合并查询结果集,效率较高
查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = 'MANAGER'unionselect ename,job from emp where job = 'SALESMAN';
注意事项:
- union在进行结果集合并的时候,要求两个结果集的列数相同。
- 结果集合并时,列和列的数据类型也要一致。【mysql允许,oracle报错】
10.limit
-
作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
-
用法:
//完整用法:limit startIndex, length; //startIndex从0开始//缺省用法:limit n; //取前n个
按照薪资降序,取出排名在前5名的员工?
select ename,salfrom emporder by sal desclimit 5;
按照薪资降序,取出工资排名在[3-5]名的员工?
select ename,salfrom emporder by sal desclimit 2,3;
- 分页:
每页显示pageSize记录
第pageNo页:limit (pageNo - 1) * pageSize, pageSize
11.大总结
select ...from ...where ...group by ...having ...order by ...limit ...//执行顺序://from-->where-->group by-->having-->select-->order by-->limit
二、DML
数据操作语言(凡是对表当中的数据进行增删改的都是DML)
主要操作表中的数据data
insert 增
delete 删
update 改
1.数据类型
- varchar(最长255)
可变长度的字符串,动态分配空间
速度低
- char(最长255)
定长字符串,分配固定空间
速度快
- int(最长11)
数字中的整型,相当于java中的int
默认长度为11
- bigint
数字中的长整形,相当于java中的long
- float
单精度浮点型数据
- double
双精度浮点型数据
- date
短日期类型日期
默认格式:%Y-%m-%d
- datetime
长日期类型日期
默认格式:%Y-%m-%d %h-%i-%s
- clob(Character Large OBject)
字符大对象,最多可以存储4G的字符串。比如:存储一篇文章
超过255个字符的都要采用CLOB字符大对象来存储
- blob(Binary Large OBject)
二进制大对象,专门用来存储图片、声音、视频等流媒体数据
往BLOB类型的字段上插入数据时,需要用到IO流
2.插入数据(insert)
- 语法格式:
insert into 表名(字段名1,字段名2...) values(值1,值2...);//插入多条数据insert into 表名(字段名1,字段名2...) values(),()...();insert into 表名 select * from src_table;//将查询结果插入到一张表中
说明:
- 字段名和值要一一对应。数量要对应,数据类型也要对应。
- insert语句但凡执行成功,必然会多一条路。
- 前面的字段名可以省略,省略时表示都写上。
3.修改数据(update)
- 语法格式:
update 表名 set 字段名1=值1, 字段名2=值2... where 条件;注意:没有条件限制会导致所有数据全部更新
+++
4.删除数据(delete)
- 语法格式:
delete from 表名 where 条件;//注意:没有条件限制,整张表的数据会全部删除
删除数据的原理:
- 表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放
- 优点:删除效率比较低
- 缺点:支持回滚
三、DDL
数据定义语言(凡是带有create、drop、alter的都是DDL)
DDL主要操作的是表的结构。不是表中的数据
create:新建,等同于增
drop:删除
alter:修改
1.建表(create)
- 语法:
creat table 表名( 字段名1 数据类型; 字段名2 数据类型 default 默认值;//可通过default关键字设置默认值 字段名3 数据类型;);//快速建表create table des_table as select * from src_table;//将查询结果复制到新建表中
说明:
- 创建表的时候,必须先要选择数据库。
- 表名:建议以t_ 或者 tbl_开始,可读性强。见名知意。
- 字段名:见名知意。
2.删表(drop)
- 语法:
drop table 表名; //当这张表不存在的时候会报错//如果表存在,则删除drop table if exists 表名;
3.删数据(truncate)
- 语法:
truncate table 表名;//把表中的数据全部删除,表还在
删除数据的原理:
- 表被一次截断,物理删除
- 优点:快速
- 缺点:不支持回滚
四、TCL
事务控制语言
commit:事务提交
rollback:事务回滚
五、DCL
数据控制语言
grant:授权
revoke:撤销权限
约束
-
概念:
在创建表的时候,可以给表中的字段加上一些约束,来保证这个表中数据的完整性和有效性
约束类型:
- 非空约束 not null
- 唯一性约束 unique
- 主键约束 primary key(PK)
- 外键约束 foreign key(FK)
- 检查约束 check【mysql不支持,oracle支持】
1.非空约束:not null
create table t_vip( id int, name varchar(255) not null);1.被not null约束的字段不能为null2.not null只有列级约束,没有表级约束
2.唯一性约束:unique
create table t_vip( id int unique, //列级约束 name varchar(255) , email varchar(255), unique(name, email) //表级约束,多个字段联合唯一);1.mysql中,如果一个字段同时被unique和not null约束,该字段自动变成主键字段
3.主键约束:primary key
create table t_vip( //单一主键(建议使用) //auto_increment 可以自动维护一个主键值 id int primary key auto_increment, //列级约束 name varchar(255), email varchar(255), //复合主键 //primary kye(id, name)//表级约束);1.主键值是每一行记录的唯一标识。2.任何一张表都应该有主键,没有主键,表无效。3.主键的特征:not null + unique4.一张表,主键约束只能添加一个。(主键只能有1个)5.主键值建议使用int,bigint,char等类型,不建议使用varchar做主键
主键还可以分为 自然主键和业务主键
自然主键:主键值是一个自然数,和业务没关系
业务主键:主键值和业务紧密关联,例如银行卡号
实际开发中,使用自然主键比较多。
4.外键约束
create table t_class( classno int primary key, classname carchar(255));create table t_student( no int primary key auto_increament, name varchar(255), con int, foreign key(con) references t_class(classno));1.子表中的外键引用父类的某个字段,被引用的这个字段不一定是主键,但至少具有unique约束
存储引擎(了解内容)
- 概念:
存储引擎是MySQL中特有的一个术语,其他数据库中没有
实际上是一个表存储/组织数据的方式
不同的存储引擎,表存储数据的方式不同
- 如何添加“存储引擎”
在建表的时候,可以在最后的小括号的右边使用:
ENGINE来指定存储引擎;
CHARSET来指定字符编码方式。
默认存储引擎:InnoDB
默认字符编码方式:utf8
- 查看引擎类型:
show engines \G
常用存储引擎
1.MyISAM存储引擎
-
使用三个文件表示每张表。
格式文件——存储表结构的定义(mytable.frm)
数据文件——存储表行的内容(mytable.MYD)
索引文件——存储表上的索引(mytable.MYI)
【对于一张表来说,只要是主键,或者是加有unique约束的字段上都会自动创建索引】
-
可被转换为压缩、只读表来节省空间。
-
不支持事务机制,安全性低。
2.InnoDB存储引擎
mysql默认的存储引擎
特征:
-
每个InnoDB表在数据库目录中以 .frm格式文件存在
-
InnoDB表空间 tablespace被用于存储表的内容。
表结构是一个逻辑名称。表空间存储数据 + 索引。
-
提供一组用来记录事务性活动的日志文件
用COMMIT(提交)、SAVEPOINT和ROLLBACK(回滚) 支持事务处理
-
在Mysql服务器崩溃后提供自动回复
-
支持外键及引用的完整性,包括级联删除和更新
-
提供全ACID兼容
-
多版本(MVCC)和行级锁定
最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读
不能很好的节省存储空间
3.MEMORY存储引擎
前称为:HEAP引擎
其数据存储在内存中,且行的长度固定。故效率很快
特征:
- 在数据库目录内,每个表均以 .frm格式的文件表示
- 表数据及索引被存储在内存中
- 表级锁机制
- 不能包含TEXT 或 BLOB字段
优点:查询效率是最高的,不需要和硬盘交互
缺点:不安全,关机之后消失。因为数据和索引都是在内存当中
事务(transaction)
-
概念:事务是一个完整的业务逻辑,是最小的工作单位,不可再分。
-
本质:一个事物其实就是多条DML语句同时成功,要么同时失败
只有DML语句才会有事务这一说,其他语句和事务无关。
mysql中,默认是支持自动提交事务的。即每执行一条DML语句,就提交一次
//关闭mysql的自动提交机制start transaction;
InnoDB存储引擎提供了一组用来记录事务性活动的日志文件
在事务的执行过程中,每一条DML语句的操作都会记录到“日志文件”中
在事务的执行过程中,我们既可以提交事务,也可以回滚事务
-
提交事务 commit
1.清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中
2.提交事务标志着,事务的结束。并且是一种全部成功的结束
-
回滚事务 rollback
1.将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
2.回滚事务标志着,事务的结束。并且是一种全部失败的结束。
3.回滚永远都是只能回滚到上一次的提交点
+++
特征
- A:原子性
说明事务是最小的工作单元。不可再分
- C:一致性
所有事务要求,在同一个数据当中,所有操作必须同时成功,或者同时失败,以保证数据的一致性
- I:隔离性
A事务和B事务之间具有一定的隔离性
- D:持久性
事务最终结束的一个保障。
事务提交,就相当于将没有保存到硬盘上的数据,保存到硬盘上。
+++
事务间的隔离级别
查看隔离等级select @@tx_isolation;设置隔离等级set global transaction isolation level 隔离等级;
读未提交
read uncommitted——没有提交就读到了
概念:事务A可以读取到事务B未提交的数据
存在问题:脏读现象(Dirty Read)
这种隔离级别一般都是理论上的
+++
读已提交
read committed——提交之后才能读到
概念:事务A只能读取到事务B提交之后的数据
解决了脏读现象,但存在不可重复读取数据的问题
在这种隔离级别下,每一次读到的数据是绝对真实的
oracle数据库中,默认的隔离级别就是read committed
+++
可重复读
repeatable read——提交之后也读不到,读取的数据永远都是刚开始事务时的数据
概念:事务A开启之后,不管是多久,每一次在事务A中读取到的数据都是一样的。
即使事务B将数据修改并提交,事务A读取的数据还是没有发生改变。
解决了不可重复读取数据的问题,但存在幻影读。
mysql数据库中,默认的隔离级别就是repeatable read
+++
序列化/串行化
serializable
效率最低。解决了所有的问题
这种隔离级别表示事务排队,不能并发
每一次读取到的数据都是最真实的
索引
索引是在数据库表的字段上添加的,是为了提高查询效率的一种机制
索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引
mysql数据库中,索引也是需要排序的。mysql中索引是一个B-Tree数据结构
遵循左小右大原则存放,采用中序遍历来获取数据
- 索引分类:
- 单一索引:一个字段上添加索引
- 复合索引:两个字段或多个字段上添加索引
- 主键索引:主键上添加索引
- 唯一性索引:具有unique约束的字段上添加索引
注意:唯一性比较弱的字段上,添加索引的用处不大
- 注意点:
-
在任何数据库当中,主键和被unique约束的字段,都会自动创建索引对象
-
在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
-
在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式存在
-
MyISAM存储引擎中,索引存储在一个 .fyi文件中
-
InnoDB中,索引存储在一个逻辑名称叫tablespace的当中
-
MEMORY中,索引存储在内存当中
-
- 添加索引的情况:
-
数据量庞大
-
该字段经常出现在where的后面,以条件的形式存在。即该字段总是被扫描
-
该字段很少出现DML操作。【DML之后,索引需要重新排序】
建议:不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能
创建、删除索引
//创建索引create index emp_ename_index on emp(ename);给empl的ename字段添加索引,起名为emp_ename_index//删除索引drop index emp_ename_index on emp;将emp表上的emp_ename_endex索引对象删除
查询SQL语句是否使用了索引
explain SQL语句;type=ALL时,说明没有用到索引type=ref时,说明使用了索引
索引失效
情况一:create index emp_ename_index on emp(ename);select * from emp where ename like '%T';原因:模糊匹配当中以“%”开头了情况二:creat index emp_ename_index on emp(ename);select * from emp where ename = 'KING' or job = 'MANAGER';原因:使用or时,如果其中一边没有索引,那么另一边的索引也会失效情况三:creat index emp_job_sal_index on emp(job, sal);select * from emp where sal = 800;原因:使用复合索引时,没有使用左侧的列查找,索引失效情况四:create index emp_sal_index on emp(sal);select * from emp where sal+1 = 800;原因:在where中,索引列参加了运算,索引失效情况五:create index emp_ename_index on emp(ename);select * from emp where lower(ename) = 'smith';原因:在where中,索引列使用了函数
视图(view)
基本介绍
概念:视图是一种根据查询定义4的数据库对象,用于获取想要看到和使用的局部数据
可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作
在实际开发中,可以把一些很复杂的SQL语句以视图对象的形式新建
面向视图开发的时候,使用视图的时候可以向使用table一样
可以对视图进行增删改查等操作。
视图对象不是在内存当中,而是存储在硬盘上的,不会消失
+++
创建、删除视图对象
//创建create view dept_view as select * from dept; //as后面必须是DQL语句//删除drop view dept_view;
+++
作用
作用:方便,简化开发,利于维护
视图的特点:通过对视图的操作,会影响到原表数据
数据库设计三范式
概念:数据库的设计依据。教授如何进行数据库表的设计。
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分
第二范式:建立在第一范式的基础上,要求所有非主键字段完全依赖主键
不要产生部分依赖
第三范式:建立在第二范式的基础上,要求所有非主键字段直接依赖主键
不要产生传递依赖
多对多,三张表,关系表两个外键
一对多,两张表,多的表加外键
一对一,外键唯一
数据库设计三范式是理论上的
实践和理论有时候是有偏差的
最终目的都是为了满足客户的要求,有的时候会拿冗余换执行速度
并且对于开发人员来说,SQL语句的编写难度也会降低