快速入门MySQL知识点（老杜MySQL）

KXXindy

已于 2022-02-28 16:29:28 修改

阅读量896

点赞数 1

文章标签： mysql 数据库学习

于 2022-02-25 16:24:21 首次发布

本文链接：https://blog.csdn.net/Kxxindy/article/details/123135741

版权

MySQL入门

sql、DB、DBMS的关系

DB: DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS: DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer...）
SQL: 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。 SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。 SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）

sql、DB、DBMS之间的关系

DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。 DBMS -(执行)-> SQL -(操作)-> DB

表

表（table）是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。

一个表包括行和列：

行：被称为数据/记录(data)
列：被称为字段(column)
- 每一个字段应该包括字段名、数据类型、相关的约束三种属性

SQL语句分类

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。

DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。

DDL（数据定义语言）：create drop alter，对表结构的增删改。

TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction) DCL（数据控制语言）: grant授权、revoke撤销权限等。

存储引擎(了解内容)

完整的建表语句 CREATE TABLE t_x(s id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF8

什么是存储引擎呢？存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

查看当前mysql支持的存储引擎？ show engines \G

常见的存储引擎

Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO MyISAM这种存储引擎不支持事务。 MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。 MyISAM采用三个文件组织一张表： xxx.frm（存储格式的文件） xxx.MYD（存储表中数据的文件） xxx.MYI（存储表中索引的文件）优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。缺点：不支持事务。

Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES 优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。表的结构存储在xxx.frm文件中数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。

Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO 缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。优点：查询速度最快。以前叫做HEPA引擎。

常用命令

导入数据步骤 第一步：登录mysql数据库管理系统 dos命令窗口： mysql -uroot -p333 第二步：查看有哪些数据库 show databases; (这个不是SQL语句，属于MySQL的命令。) 第三步：创建属于我们自己的数据库 create database bjpowernode; (这个不是SQL语句，属于MySQL的命令。) 第四步：使用bjpowernode数据 use bjpowernode; (这个不是SQL语句，属于MySQL的命令。) 第五步：查看当前使用的数据库中有哪些表？ show tables; (这个不是SQL语句，属于MySQL的命令。) 第六步：初始化数据 mysql> source D:\course\05-MySQL\resources\bjpowernode.sql

sql脚本：当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。注意：直接使用source命令可以执行sql脚本。 sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

删除数据库：drop database bjpowernode;

当这个表存在的话删除：drop table if exists t_student;

查看表结构：use bjpowernode;（要查看前先要使用该数据库）

show tables;（查看有哪些表）

desc dept;（查看某张表的具体结构）

查看表中的数据：mysql> select * from emp;

查看当前使用的是哪个数据库：mysql> select database();

查看mysql的版本号：mysql> select version();

结束一条语句：\c 命令

退出mysql：exit 命令

查看创建表的语句：show create table emp;

注意：

任何一条sql语句以“;”结尾。sql语句不区分大小写，一般用小写编写。
在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

定义语言（DDL）

create drop alter，对表结构的增删改。

创建表

语法： create table 表名( 字段名1 数据类型（+约束）, 字段名2 数据类型, 字段名3 数据类型, .... );

创建的每一个字段应该包括字段名、数据类型、相关的约束三种属性

例：create table t_student( no bigint, name varchar(255) not null,//varchar后通常会加限定长度，表示这个字符字符最大长度 sex char(1), classno varchar(255), birth char(10) );

表的复制：将查询结果当做表创建出来。

语法格式：create table 表名 as select语句;

命名格式：

表名一般建议以t_或者_tbl开始。
所有的标识符都是小写，单词和单词之间用下划线连接

数据类型

int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型（对应Java中的java.sql.Date类型）
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object） ......

注意：在实际的开发中，当某个字段中的数据定长时，例如：性别、生日等都是采用char。当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

日期格式（date和datetime）：

%Y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

date类型可以看作特殊的varchar类型，有一定的格式要求

date和datetime两个类型的区别：

date是短日期：只包括年月日信息

默认格式： %Y-%m-%d

datetime是长日期：包括年月日时分秒信息

默认格式： %Y-%m-%d %h:%i:%s

若date为排序字段，日期越新的越大

约束(Constraint)

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的 合法性、有效性、完整性。

列级约束：只针对某个字段的约束，写在指定字段后

语法格式: 字段名+约束

表级约束：针对单个或多个字段联合的约束，写在所有字段最后

语法格式: 在括号内的最后加约束（字段1，字段2 ...）

非空约束(not null)
- 约束的字段不能为NULL，not null约束只有列级约束。没有表级约束。
唯一约束(unique)
- 唯一约束修饰的字段（可以多个字段）具有唯一性，不能重复。但可以为NULL。
- 多个字段联合起来添加1个约束unique 【表级约束】，在末尾加unique(usercode,username)
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）。表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
- 主键相关的术语 主键约束 : primary key 主键字段 : id字段添加primary key之后，id叫做主键字段主键值 : id字段中的每一个值都是主键值。
- 主键的分类
  
  根据字段数量来划分：
  - 单一主键（推荐的，常用的。）
  - 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
  根据主键性质来划分：
  - 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
  - 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
- 注意：
  1. 一张表的主键约束只能有1个。（1个就够了，多个主键导致不直到以哪个主键为唯一标识）
  2. 复合主键不需要掌握
  3. mysql提供主键值自增（非常重要）。主键字段自动维护一个自增的数字，从1开始，以1递增。
    
    create table t_user( id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。 username varchar(255) );
    
    Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
外键约束(foreign key)：...（简称FK）
- 外键相关术语 外键约束: foreign key 外键字段：添加有外键约束的字段外键值：外键字段中的每一个值。
- 语法格式：foreign key(classno) references t_class(cno)
- 外键的作用：保证外键字段中的值都是外键引用的表的字段的值
  
  业务背景：当一个表中存在大量的冗余（数据重复，浪费空间或数据多余）数据时，这个表的设计是失败的
  
  第一种方案：一张表存储所有数据 no(pk) name classno classname
  
  1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班 2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班 3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班 4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班 5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班缺点：冗余。【不推荐】
  
  第二种方案：两张表（班级表和学生表） t_class 班级表 cno(pk) cname
  
  101 北京大兴区经济技术开发区亦庄二中高三1班 102 北京大兴区经济技术开发区亦庄二中高三2班
  
  t_student 学生表 sno(pk) sname classno(该字段添加外键约束fk)
  
  1 zs1 101 2 zs2 101 3 zs3 102 4 zs4 102 5 zs5 102
- 父表和子表
  
  被外键引用的表称为父表，有外键约束的表称为子表
  
  t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
  
  操作顺序要求：删除数据的时候，先删除子表，再删除父表。添加数据的时候，先添加父表，在添加子表。创建表的时候，先创建父表，再创建子表。删除表的时候，先删除子表，在删除父表。
- 注意：
  1. 被引用的字段不一定是主键，但至少具有unique约束。
  2. 外键可以为NULL。
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

删除表

drop table 表名; // 这个通用。表结构也会被删除 drop table if exists 表名; // oracle不支持这种写法。

表结构的修改

使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

查询语言（DQL）

查询语句，凡是select语句都是DQL。

简单查询

完整sql查询语句：

语句	执行顺序
select	6
from	1
join…on	2
where	3
group by	4
having	5
order by	7
limit	8

一条完整的mysql执行语句总结：从指定表中获取数据（from），根据连接条件连接（on），再根据筛选条件（where）过滤，第一次过滤后的数据后按指定字段分组（group by），分组后可再次过滤（having），最后读取数据的指定字段（select）得到结果集，对每个字段都可以用函数处理或数学运算，对结果集排序（order by），limit截取要显示的结果集（注意：分组函数不能在where后面用，因为此时还未分组）

select

读取数据的指定字段

字段可以参与数学运算。所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。需要用到ifnull()函数

如查询员工的年薪：select ename,sal * 12 from emp;
查询结果重命名：as关键字，可省略。当别名中有中文加引号，标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。 select ename,sal * 12 as 年薪 from emp; // 错误 select ename,sal * 12 as '年薪' from emp;
查询结果集的去重： 使用distinct关键字，可以结合多个字段使用。表示去除查询后面字段得到的结果集的重复数据，注意distinct只能出现在所有字段的最前面。
对于已经分组的数据，只能选取分组数据的共有字段和分组函数。（在Oracle中，select列表中所有未包含在组函数中的列都应该包含在group by子句中，但是包含在group by子句中的列却不必一定包含在select列表中）
select后如果直接跟“字面量/字面值”，会根据表结构生成一列该字面量的表

where

后面跟查询条件，筛选数据

条件：

通过指定字段和数据比较>,<,=,!=(<>)
字段 between A and B 左小右大，左闭右开。除了可以使用在数字方面之外，还可以使用在字符串方面。
is null或is not null

在数据库当中NULL不是一个值，代表什么也没有，为空。空不是一个值，不能用等号衡量。

在判断条件中必须使用 is null或者is not null
and和or，就是与或
in和not in （同or） in（值）in后面的值不是区间，是具体的值。
模糊查询like 两个符号：主要用于根据字符的查询，%代表任意多个字符，_代表任意1个字符，当需要找出名字中有下划线时，需要用到转义符\

select ename from emp where ename like '%A%';

order by:

排序（升序、降序）

语法：order by 字段1 asc（升序）/desc（降序），字段2 asc/desc;

注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

group by

按照某个字段或者某些字段进行分组。多个字段可以联合起来一块分组，越靠前字段起主导作用

having

是对分组之后的数据进行再次过滤。

注意：

分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。（显示各个分组的共有属性或唯一属性）

函数

对每条数据的字段进行操作，其结果可以用于sql语句的任一位置，最后执行

单行处理函数

对单行数据进行操作，输入一行，输出一行。

常见单行处理函数：

函数	作用
ifnull(可能为NULL的数据,被当做什么处理)	处理NULL
upper	转换大写
substr(被截取的字符串，起始下标，截取的长度)	取子串，起始下标从1开始
concat(a,b)	字符串拼接
length	取长度
trim	去除括号内字符串的空格
str_to_date（'字符日期','日期格式'）	将字符串varchar类型转换成日期date类型，通常在insert语句中使用
date_format（'日期类型数据','日期格式'）	格式化日期，将日期date类型转换成具有一定格式的字符串varchar类型，通常在select语句中使用，设hi展示的日期
now	获取当前时间，默认格式的长日期
format（数字,'格式' ）	设置千分位 select ename，format(sal,'$999,999') as sal from emp;
round(字面值，四舍五入到的位数，0为个位)	四舍五入
rand()	生成随机数 select round(rand()100,0) from A;//生成100以内的随机数*
case…when…then…when…then…else…end	分情况操作筛选出的数据但不影响原表。MySQL中的if…else语句，case字段，when条件，then后面相当于进入if的执行语句

关于日期和字符串转换的两个函数：

str_to_date函数

例：insert into t_user(id, name,birth) values (1,'zhangsan',str_to_date('01-10-1990'));

注意：如果提供的日期字符串满足%Y-%m-%d形式，则不需要使用此函数转换

date_format函数：

不适用该函数时数据库会对日期进行默认的日期格式化，将date类型转换成varchar类型。并且采用的日期格式：'%Y-%m-%d'

select id, name,date_format(birth,%Y/%m/%d') as birth from t_user;

分组函数

所对“某一组”数据进行操作。分组函数还有另一个名字：多行处理函数。多行处理函数的特点：输入多行，最终输出的结果是1行。

count 计数

count(*):不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关） count(comm): 表示统计comm字段中不为NULL的数据总数量。
sum 求和
avg 平均值
max 最大值
min 最小值

注意：

分组函数自动忽略NULL。
SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中，因为group by是在where执行之后才会执行的。

连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

根据表的连接方式来划分，包括：

内连接：
- 等值连接
- 非等值连接
- 自连接
外连接：
- 左外连接（左连接）
- 右外连接（右连接）
全连接（很少用！）

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。可以加条件进行过滤。避免了笛卡尔积现象，但不会减少记录的匹配次数。只不过显示的是有效记录。

内连接（无主副表之分）

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。 AB两张表没有主副之分，两张表是平等的。

等值连接：条件是等量关系。
非等值连接：连接条件中的关系是非等量关系。
自连接：一张表看做两张表。自己连接自己。

语法: A inner join // inner可以省略 B on 连接条件

外连接 （有主副表）

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中没有数据和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。

语法: A outer left/right join // outer是可以省略的。 B on 连接条件

多表连接查询：

A join B join C on ...

表示：A表和B表先进行表连接，连接之后的临时表继续和C表进行连接。

union关键字： 可以将查询结果集相加行相加

子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

select ..(select). from ..(select). where ..(select).

where子句中使用子查询：把子查询的结果嵌入判断条件
from后面嵌套子查询：子查询的结果当作临时表，需要把临时表重命名才能调用临时表的属性

疑问？表连接中on能不能用分组函数，不行，执行顺序不对

select grade,deptno from emp e join salgrade s on avg(e.sal) between s.losal and s.hisal group by deptno;因为表连接先于分组

例：找出每个部门平均薪水的等级。

select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
select后面嵌套子查询。

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

等同于：

select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

分页查询

limit：用于截取结果集中的部分数据。是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

语法：imit startIndex, length startIndex表示起始位置，从0开始，0表示第一条数据。length表示取几个

常见用法：

每页显示pageSize条记录：第pageNo页：(pageNo - 1) * pageSize, pageSize

操作语言（DML）

insert delete update，对表当中的数据进行增删改。

增删改查有一个术语：CRUD操作 Create（增） Retrieve（检索） Update（修改） Delete（删除）

插入数据

语法格式： insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)；

查询结果插入到一张表中 insert into 表1 select 字段 from 表2 ;

要求：

字段的数量和值的数量相同，并且数据类型要对应相同。
字段可以省略不写，但是后面的value对数量和顺序都有要求。
当只插入部分字段值时，其他字段值自动会补NULL
一次插入多行数据

insert into t_student (no,name,sex,classno,birth) values (3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

注意：当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法再修改，insert语句插入数据了，只能使用update进行更新。

将查询结果当做表创建出来。语法： create table 表名 as select语句;

修改数据

语法格式： update 表名 set 字段名1=值1,字段名2=值2... where 条件;

注意：1. 没有where条件整张表数据全部更新。

值可以是原表字段参与数学运算

删除数据

语法格式： delete from 表名 where 条件;

注意：没有条件全部删除。

删除表中全部数据（重点） truncate table 表名; // 表被截断，不可回滚。永久丢失。不会删除表结构

事务

一个事务是一个完整的业务逻辑单元，不可再分。

例子：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句： update t_act set balance = balance - 10000 where actno = 'act-001'; update t_act set balance = balance + 10000 where actno = 'act-002'; 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？不需要事务。但实际情况不是这样的，通常一个事务需要多条DML语句共同联合完成。

事务的作用：为了保证数据的完整性，安全性。

事务相关的语句：只有DML语句。（insert delete update） 因为它们这三个语句都是和数据库表当中的“数据”相关的。的存在

事务语法格式：开启事务：start transaction;

提交事务：commit;

事务的特性 四大特性：ACID A: 原子性：事务是最小的工作单元，不可再分。 C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。 I ：隔离性：事务A与事务B之间具有隔离。 D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）

实时更新。对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

脏读（Dirty Read）现象：表示读到了脏的数据。

注意：在企业开发中一般以read committed开始
第二级别：读已提交（read committed） 对方事务提交后更新。对方事务提交之后的数据我方可以读取到。这种隔离级别解决了: 脏读现象。读已提交存在的问题是：不可重复读。

不可重复读：比如在事务开启之后，第一次读取到的数据是3条，当前事务还未结束，再次读取时，可能因为其他事务修改了数据且已提交而导致数据变成4条，数据不和事务开始时一致。每一次读到的数据是绝对真实的。
第三级别：可重复读（repeatable read）

我方事务结束前永不更新，提交后更新

可重复读：事务开启之后，不管多久，每一次在事务A中读取到的数据都是一致的，即时其他事务已经修改了数据并提交了，但当前事务读取到的数据没事没有发生改变。每一次读到的数据是幻象，不真实的。

这种隔离级别解决了：不可重复读问题。这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读/串行化读（serializable）

需要事务排队。不能并发

解决了所有问题。效率低。

注意：

mysql事务默认情况下是自动提交的（只要执行任意一条DML语句则提交一次。）

关闭自动提交 start transaction;
mysql数据库默认的隔离级别是：可重复读。

oracle数据库默认的隔离级别是：读已提交。
设置全局隔离级别：

set global transaction isolation level 隔离等级;

查询当前隔离级别：

select @@tx_isolation;

索引

检索方式：在数据库方面，查询一张表的时候有两种检索方式：

全表扫描
根据索引检索（效率很高）

语法格式：

创建索引对象： create index 索引名称 on 表名(字段名); 删除索引对象： drop index 索引名称 on 表名;

添加索引是给某一个字段，或者说某些字段添加索引。

给薪资sal字段添加索引： create index emp_sal_index on emp(sal);

mysql> explain select ename,sal from emp where sal = 5000; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

字段添加索应满足条件：

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。查询语句比较多（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据该字段查询）

注意：

索引虽然可以提高检索效率，但是不能随意添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
主键和具有unique约束的字段自动会添加索引

根据主键查询效率较高。尽量根据主键检索。

索引的分类

单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

索引失效：

模糊查询的时候，第一个通配符使用的是%，索引失效。
使用or的时候，如果两边没有同时拥有索引，索引失效。
使用符合索引的时候，没有使用左侧的字段查找，索引失效。
在where中索引字段参加了数学运算，索引失效。
在where中索引字段使用了函数，索引失效。
......

视图(view)

视图是由数据库中的一个表或多个表导出的虚拟表，但它还是会以文件的形式保存，清空内存也不会清除，方便用户对数据的操作。

站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

视图的作用

简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。假设有非常复杂的SQL语句，且需要在不同的位置上重复使用，可以把该复杂SQL语句以视图对象的形式新建，在需要编写这条SQL语句的位置直接使用它的视图对象，大大简化开发并且有利于后期的维护，因为修改时也只需要修改视图对象映射的SQL语句即可。
安全性

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

语法格式：

create view my_view as select empno,ename from emp; drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来。语法类似于表复制

区别于表复制：表复制是新创建出来一张新表，对新表的操作不会影响原表，而创建视图后，虽然视图也是一个文件，会保存到硬盘上，但是可以对视图进行CRUD操作，通过视图影响原表数据的，不是直接操作的原表

DBA命令（看文档）

数据导出：

不用进入mysql，直接在dos执行

在windows的dos命令窗口中执行：（导出整个库） mysqldump bjpowernode>绝对路径+文件名. sql -uroot –p123 在windows的dos命令窗口中执行：（导出指定数据库当中的指定表） mysqldump bjpowernode emp>绝对路径+文件名. sql -uroot –p123

导入数据：

需要进入mysql

create database bjpowernode; use bjpowernode; source D:\bjpowernode.sql

数据库设计三范式

设计表的依据。按照这个三范式设计的表不会出现数据冗余。

第一范式：任何一张表都应该有主键，保证每一行唯一。并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖（会造成数据冗余）。

比如多对多关系

确定主键：（业务主键）

学生编号(PK) 教师编号(PK) 学生姓名教师姓名
1001 001 张三王老师
1002 002 李四赵老师
1003 001 王五王老师
1001 002 张三赵老师
以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

解决方案如下：

学生信息表

学生编号（PK）学生姓名
1001 张三
1002 李四
1003 王五

教师信息表

教师编号（PK）教师姓名
001 王老师
002 赵老师

教师和学生的关系表

学生编号(PK) fkà学生表的学生编号教师编号(PK) fkà教师表的教师编号
1001 001
1002 002
1003 001
1001 002

如果一个表是单一主键，那么它就符合第二范式，部分依赖和主键有关系

以上是一种典型的“多对多”的设计，三张表，关系表两个外键。
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

学生编号（PK）学生姓名班级编号班级名称
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 六 03 一年三班
从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：

学生信息表

学生编号（PK）学生姓名班级编号（FK）
1001 张三 01
1002 李四 02
1003 王五 03
1004 六 03

班级信息表

班级编号（PK）班级名称
01 一年一班
02 一年二班
03 一年三班
以上设计是一种典型的一对多的设计，两张表，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键

注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。