Mysql基础

视频来自：老杜带你学-Mysql入门基础

1.mysql常用命令

登录mysql:mysql -u root -p123456
退出：exit
查看数据库: show databases;
怎么使用某个数据库test: use test;
创建数据库：create database fkddata;
查看数据库下有哪些表：show tables;
将sql文件导入：source 路径;
只看表结构，不看表的数据：desc 表名;
查看数据数据库版本：select version();
查看当前是哪一个数据库:select databse;

2.基本SQL语句

1.查询: select

简单查询：select 字段名 from 表名;
- 查询多个字段用','隔开;
- 使用as关键字起别名，别名有空格或者是中文用引号'dept name','薪资'
- 字段可以使用数学表达式;
条件查询： selct 字段 from 表名 where 条件;
- 条件:>, <, !=, 两个值之间between ... and ...（>= value1 and <= value2） ,is (not) null
  and并且, 或者or,(and 和 or同时出现，and 优先级比较高)
  in:包含具体的值，相当于多个or (...in(a1,a2,a3))
  not:可以取非， not in, is not null
  like:称为模糊查询,支持%和下划线匹配。%匹配任意字符。下划线：一个下划线只能匹配一个字符。(姓张:张%)
  select ename from emp where ename like '张%';

排序：

单个字段排序:select ename,sal from emp order by sal asc(desc);//默认升序
多个字段排序:select ename,sal from emp order by sal asc, ename asc;

2.操作表中数据：增删改: insert;delete;update

3.操作表的结构：增删改create;drop;alter

4.事务控制语言:commit;rollback

5.数据控制语言:grant;revoke

3.数据(单行)处理函数

lower：转换小写 upper：转换大写

select lower(ename) from emp;

substr(被截取的子串，起始下标，截取的长度): 取子串

select substr(ename,1,1) as ename from emp;

注意：起始下标从1开始

concat(str1,str2)：字符串拼接

select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)-1))) as result from emp order by ename;

length: 取长度

select length(ename) as ename from emp;

trim:去除前后空格

str_to_date：将字符串转换成日期；varchar–>date

语法格式：str_to_date(‘字符串日期’，‘日期格式’)

mysql的日期格式：%Y %m %d %h %i %s

str_to_date('01-10-1990', '%d-%m-%Y');
insert into t_user values('fkd', '1999-01-28', 23);（默认%Y-%m-%d不用转换）

通常用在insert中

date_format：格式化日期；date–> varchar

查询的时候可以以一种特定的格式进行展示

语法格式：date_format(日期数据, ‘格式’)

不指定格式类型会自动格式化为’1999-01-28’

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

format(数字, 保留小数位数):

format(sal, ‘$999,999’);

round 四舍五入：round(数，保留小数位数)

select round(123.567,0) as result from emp;

rand()：不大于1的随机数

100以内随机数:round(rand()*100,0)

ifnull：可以将null转换为一个具体的值，ifnull(含null的数据，新值)

null只要参与数学运算，最终结果一定是null，

select ename, (sal+ ifnull(comm,0))*12 as yearsal from emp ;

case … when…then…when…then…else…end，类似switch函数

当员工的工作岗位是manager的时候，工资上调10%；当工作岗位是salesman的时候，工资上调50%，其他正常（注意：不修改数据库，只是将查询的结果显示为工资上调）

select ename, job,sal as oldsal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as newsal from emp order by newsal;

4.多行处理(分组)函数

输入多行，最终输出一行

5个：计数count(), 求和sum(), 平均值avg(), 最大值max(), 最小值min()

注意：

1. 分组函数自动忽略null，不需要提前对null进行处理

2. count(具体字段)：表示统计该字段下所有不为null的元素的总和

   count(*): 统计表中总行数(数据总数)

3. 分组函数不能够直接使用在where子句中。

找出比最低工资高的员工信息？
select ename,sal from emp where sal > min(sal);  X  为什么分组函数不能用在where后：
因为分组函数使用的时候必须先分组才能使用，where执行的时候还没进行分组。

4. 所有分组函数可以组合起来一起用

select sum(sal), min(sal), max(sal), avg(sal), count(*) from emp;

分组查询：

select … from … where … group by…having … order by…

执行顺序:1.from 2. where 3.group by 4.having 5.select 6.order by

从某张表中查询数据，首先经过where条件筛选出有价值的数据，对这些数据进行分组，分组之后可以使用having继续筛选，select查询出来，最后排序输出。

找出每个工作岗位的工资和？

思路：按照工作岗位进行分组，然后对工资进行求和。
select job, sum(sal) from emp group by job;

重点结论：在一条select语句当中，如果有group by 语句的话，select后面只能跟：参加分组的字段，以及分组函数，其他一律不能跟。

找出每个部门的最高薪资？

select deptno,max(sal) as maxsal from emp group by deptno;

找出每个部门，不同工作岗位的最高薪资？

多个字段分组：select deptno,job,max(sal) as maxsal from emp group by deptno,job order by deptno;

找出每个部门最高薪资，要求显示最高薪资大于3000的？

• 使用having 可以对分完组之后的数据进行进一步的过滤，having不能单独使用，必须和group by 联合使用。

select deptno, max(sal) as maxsal from emp where sal > 3000 group by deptno;
select deptno, max(sal) as maxsal from emp group by deptno having max(sal)>3000;

优化策略：优先使用where，再考虑having

• 去除重复关键字 distinct。出现在所有字段的最前方(表示联合去除重复)

select count(distinct job) from emp;

5. 连接查询

emp表和dept表中联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

表连接会出现笛卡尔积现象，要加条件限制。表的连接次数越多，效率越低。

sql92表的表的连接条件和筛选条件结构不清晰。

5.1 内连接

内连接两张表没有主次关系

等值连接：

查询员工名和部门名

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

非等值连接：

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and hisal;

自连接：

查询员工的上级领导，要求显示员工名和对应的领导名

技巧：将一张表看成两张表，取不同别名

select a.ename,b.ename from emp a join emp b on a.mgr = b.empno;//没有king

5.2 外连接

在外连接中产生了主次关系，某个表是主表，另一个捎带查出来

• 右外连接(右连接)：将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左表。
• 左连接同右连接，可以转换，看哪个是主表

查询员工名和部门名，没有员工的部门也要显示

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

查询每个员工的上级领导，要求显示所有员工的名字和领导的名字。

select a.ename, b.ename from emp a left join emp b on a.mgr = b.empno;//含king

5.3 多表连接

select ... from a join b on a和b的连接条件 join c on a和c的连接条件 (right) join d on a和d的连接条件;

找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

select e.ename '员工名', l.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 
left join emp l on e.mgr = l.empno;

6. 子查询

select语句中嵌套select语句，被嵌套的select语句被称为子查询

6.1 where语句中

嵌套语句查询出的是一个可判断的值

查询比最低工资高的员工姓名和工资

第一步：查询最低工资 第二步：找出大于800的 第三步：合并

select ename, sal from emp where sal > (select min(sal) from emp);

6.2 from子句中的子查询

可以将子查询的查询结果当做一张临时表(技巧)

找出每个岗位的平均工资的薪资等级

select e.*, s.grade 
from (select job, avg(sal) avgsal from emp group by job) e 
join salgrade s 
on e.avgsal between s.losal and hisal;

6.3 select后面出现的子查询

找出每个员工的部门名称，要求显示员工名，部门名？(了解)

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

7. union和limit

7.1union合并查询结果集

查询工作岗位是Manager和salesman的员工

select ename, job from emp where job = 'manager' union select ename, job from emp where job = 'salesman';

union相当于加法运算，需要注意进行结果集合并的时候，要求两个结果集的列数要求

7.2 limit分页

limit是将查询结果集的一部分取出来，通常使用在分页查询当中

完整用法：limit startIndex, length （起始下标为0）

缺省用法：limit 5；取前5

按照薪资排序，去除排名在前5名的员工？

select ename, sal from emp order by sal desc limit 5;

• 分页

每页显示pageSize条记录，第pageNo页：

limit(pageNo - 1) * pageSize , pageSize

String sql = "select ... limit"+ startIndex + "," + pageSize;

DQL大总结 : select … from …join …on… where … group by…having … order by…limit…

8. 表

8.1 建表

create table 表名(字段名1 数据类型，字段名2 数据类型，字段3 数据类型)；

数据类型：
varchar(10)：可变长度的字符串，会根据实际的数据长度动态分配(速度慢),姓名:varchar(10)，最长255
char：定长字符串，分配固定长度去存储数据(速度快),性别:char(1)，最长255
int：最长11，数字中整数型
bigint:数字中长整型，等同于java中的long
float:单精度浮点型数据
double:双精度浮点型数据
date:短日期  只包含年月日信息。  默认格式：%Y-%m-%d
datetime:长日期   包含年月日时分秒的信息。 默认格式：%Y-%m-%d %h:%i:%s   获取系统当前时间：now()
clob(character large object):字符大对象，最多存储4G的字符串，存储一篇文章
blob(binary large object):二进制大对象，专门用来存储图片、声音、视频等流媒体数据。插入时需要IO流。

创建一个学生表？ 学号、姓名、年龄、性别、邮箱地址

create table t_student(
	sno int,
    name varchar(32),
    sex char(1) default 'M',
    age int(3) default 18,
    email varchar(255)
);

删除表：

drop table (if exists) t_student;

插入数据

语法格式：insert into 表名(字段名1，字段名2，字段名3) values(值1，值2，值3),(),();//数量数据类型一一对应

insert into t_student(sno, name, sex, age, email) values(1, 'fkd', 'm', '23', '11@qq.com');
insert into t_student values(2, 'jsy', 'F', '22', "22@qq.com");

//一次插入多条记录
insert into t_test(tname, birth, createtime) 
values ('kfd', '1999-01-28', now())，
('jsy','1999-07-28', now());

注意：insert语句但凡执行成功，就会多一行记录。没有其他字段的指定值的话，默认值是NULL

8.2 update修改

语法格式：update 表名 set 字段名1= 值1，字段名2 = 值2， 字段名3 = 值3，… where 条件;

注意：没有条件限制会导致所有该字段的数据全部更新。

update t_user set birth = '1998-12-12', age = 24 where name = 'fkd';
update t_user set age = 18;

8.3 delete修改

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

注意：没有条件会删除所有数据。

delete from t_user where name = 'fkd';

8.4 对表结构的增删改

借用navicat工具，设计表

8.5 创建表加入约束

约束的作用是保证表中的数据完整、有效

1.非空约束：not null

not null约束的字段不能为NULL

create table t_vip(
	id int,
    name vachar(255) not null
);

2.唯一性约束：unique

unique约束的字段不能重复，但是可以为NULL(列级约束)

create table t_vip(
	id int,
    name varchar(255) not null unique，
    email varchar(255)
)

name和email联合起来具有唯一性？(表级约束)

create table t_vip(
	id int,
    name varchar(255)，
    email varchar(255)，
    unique(name, email)
)

3.主键约束：primary key(PK)

主键值是每一行记录的身份标识，身份证号。任何一张表都应该有主键，没有主键是非法的。

主键的特征：Not null + unique

create table t_vip(
	id int primary key,//单一主键(建议)
    name varchar(255)
);

create table t_vip(
	id int,
    name varchar(255),
    primary key(id, name)//复合主键
);

一张表主键约束只能添加一个，主键值建议用int, bigint, char

• 自然主键：主键值是一个自然数，和业务没关系。使用较多，因为主键只需要做到不重复就行，不需要有意义。

• 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。

在mysql中有一种自增机制可以帮助自动维护主键值：

create table t_vip(
	id int primary key auto increament,
    name varchar(255)
);

4.外键约束：foreign key(FK)

​ 当cno字段没有任何约束的时候，可能会导致数据无效。为了保证cno字段的值都是100和101，需要给cno添加外键约束，那么cno字段就是外键字段，cno字段中的每一个值都是外键值。

​ 即cno用FK引用t_class中的classno.其中t_class表是父表，t_student是子表。

注意：删除顺序？先删子再删父。插入顺序？先插父再插子。

create table t_class(
	classno int primary key,
    classname varchar(255)
);
create table t_student(
	no int primary key,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
);

注意：外键可以为null，外键不一定是主键但至少具有唯一性约束

9. 存储引擎

表存储，组织数据的一种方式，可以在建表时指定存储引擎。

CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4

ENGINE来指定存储引擎：默认InnoDB，CHARSET来指定这张表的字符编码方式：默认字符编码方式utf-8，使用GBK可以插入中文。

9.1MyISAM存储引擎

• 使用三个文件表示每个表：

  • 格式文件 — 存储表结构的定义（mytable.frm）
  • 数据文件 — 存储表行的内容（mytable.MYD）
  • 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

• 可被转换为压缩、只读表来节省空间

• MyISAM存储引擎特点：

  • 可被转换为压缩、只读表来节省空间这是这种存储引擎的优势！！！！
  • MyISAM不支持事务机制，安全性低。

9.2InnoDB存储引擎

• 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。

• InnoDB支持事务，支持数据库崩溃后自动恢复机制。

• InnoDB存储引擎最主要的 特点是：非常安全。

• 它管理的表具有下列主要特征：

  • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
  • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
  • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
  • 多版本（MVCC）和行级锁定
  • 支持外键及引用的完整性，包括级联删除和更新

• InnoDB最大的特点就是支持事务：以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，

• 不能很好的节省存储空间。

9.3MEMORY存储引擎

• 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
• MEMORY 存储引擎以前被称为HEAP 引擎。
• MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
• MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

10. 事务

一个事务(transaction)其实就是一个完整的业务逻辑，是一个最小的业务单元，不可再分。

• 一个完整的业务逻辑？假设转账，从A账户转账10000元到B账户。

将A账户的钱减去10000元(upadate语句)；将B账户的钱加上10000元(update语句);以上必须同时成功或者失败。

只有DML(insert, delete, update)三个语句和事务有关系，是对数据进行增删改，涉及数据安全问题。

• 到底什么是事务？

一个事务是多条DML语句同时成功或者同时失败。

10.1 InnoDB实现事务

事务是怎么做到多条DML语句同时成功或者失败的呢？

• InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

• 在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。

• 在事务的执行过程中，我们可以提交事务，也可以回滚事务。

• 提交事务 commit;

  • 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
  • 提交事务标志着，事务的结束。并且是一种全部成功的结束。

• 回滚事务 rollback; （回滚永远都是只能回滚到上一次的提交点！）

  • 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
  • 回滚事务标志着，事务的结束。并且是一种全部失败的结束。

• 将mysql的自动提交机制(每执行一条DML，则提交一次)关闭掉

  start transaction;
  

  这种自动提交不符合开发习惯，因为一个业务通常需要多条DML语句共同执行，为了保证数据的安全，必须要求同时成功后再提交。

10.2事物的四个特性

A：原子性
说明事务是最小的工作单元。不可再分。

C：一致性
所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

I：隔离性
A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙，这道墙就是隔离性。
A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？

D：持久性
事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

10.3事务的隔离性

事务和事务之间四个隔离级别 :

读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》

• 事务A可以读取到事务B未提交的数据。
• 这种隔离级别存在的问题就是：脏读现象！(Dirty Read)我们称读到了脏数据。
• 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

读已提交：read committed《提交之后才能读到》

• 事务A只能读取到事务B提交之后的数据。
• 这种隔离级别解决了解决了脏读的现象。
• 这种隔离级别不可重复读取数据。
  • 在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。
• 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
• oracle数据库默认的隔离级别是：read committed

可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》

• 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
• 可重复读可能会出现幻影读。每一次读取到的数据都是幻象。不够真实！
• 早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！读到的是假象。不够绝对的真实。
• mysql中默认的事务隔离级别就是这个！！！！！！！！！！！
  • 银行总账select时仍可执行转账事物

序列化/串行化：serializable（最高的隔离级别）

• 这是最高隔离级别，效率最低。解决了所有的问题。
• 这种隔离级别表示事务排队，不能并发！
• synchronized，线程同步（事务同步）每一次读取到的数据都是最真实的，并且效率是最低的。

设置全局隔离级别：set global isolation level read uncommitted;
开启事务： Start transaction;

11. 索引

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

• 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

• 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

• MySQL在查询方面主要就是两种方式：全表扫描 ；根据索引检索。

• 在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。

• 遵循左小又大原则存放。采用中序遍历方式遍历取数据。

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

• 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

• 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

• MySQL在查询方面主要就是两种方式：全表扫描 ；根据索引检索。

• 在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。

• 遵循左小又大原则存放。采用中序遍历方式遍历取数据。

索引的实现原理？

• 提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象
• 提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
• 提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式

什么条件下，我们会考虑给字段添加索引呢？

1. 数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
2. 该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
3. 该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

• 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
• 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。**

创建索引:

create index emp_ename_index on emp(ename);

删除索引

drop index emp_ename_index on emp;

查看一个SQL语句是否使用了索引？

explain select * from emp where ename = 'king';

11.2索引失效

失效的第1种情况：
	select * from emp where ename like '%T';
	ename上即使添加了索引，也不会走索引，为什么？
		原因是因为模糊匹配当中以“%”开头了！
		尽量避免模糊查询的时候以“%”开始。
		这是一种优化的手段/策略。
	myql> explain select * from emp where ename like '%T';

失效的第2种情况：
	使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有
	索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个
	字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
	mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
	优化：采用union

失效的第3种情况：
	使用复合索引的时候，没有使用左侧的列查找，索引失效
	什么是复合索引？
		两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
	
	create index emp_job_sal_index on emp(job,sal);
	
	mysql> explain select * from emp where job = 'MANAGER';
	+----+-------------+-------+------+-------------------+---------------
	| id | select_type | table | type | possible_keys     | key           
	+----+-------------+-------+------+-------------------+---------------
	|  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | 
	+----+-------------+-------+------+-------------------+---------------
	
	mysql> explain select * from emp where sal = 800;
	+----+-------------+-------+------+---------------+------+---------+--
	| id | select_type | table | type | possible_keys | key  | key_len | 
	+----+-------------+-------+------+---------------+------+---------+--
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | 
	+----+-------------+-------+------+---------------+------+---------+--

失效的第4种情况：
	索引列参加了运算，或者函数，索引失效
	select * from emp where sal+1 = 800;
	select * from emp where lower(ename) = 'smith';

索引是数据库进行优化的重要手段。索引在唯一性越强的字段上效率越高。

12.视图

• 视图view:站在不同的角度去看待同一份数据。

表复制：mysql> create table dept2 as select * from dept;
dept2表中的数据：mysql> select * from dept2;
创建视图对象：
	create view dept2_view as select * from dept2;
删除视图对象：
	drop view dept2_view;

注意：只有DQL(select)语句才能以view的形式创建。
	create view view_name as 这里的语句必须是DQL(select)语句;

12.1视图的作用

• 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！
• 视图的特点：通过对视图的操作，会影响到原表数据。

//面向视图查询
select * from dept2_view; 

// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

// 面向视图删除
mysql> delete from dept2_view;

//创建多张表关联的视图对象
create view emp_dept_view as select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptno;
//面向视图更新
update emp_dept_view set sal = 1000 where dname = 'counting';

12.2视图在开发中的作用

• 假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。(把表中某一部分挑出来)
  每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
  可以把这条复杂的SQL语句以视图对象的形式新建。
  在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
  并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
  修改视图对象所映射的SQL语句。
• 我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
• 可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

DBA指令：新建用户(create)，授权(grant)，回收权限(revoke)，导入导出。

导出：在DOS命令行中
mysqldump fkddatabase (emp) >D:\fkd.sql -uroot -p123456
导入：先登录mysql,创建数据库
source F:\fkddatabase.sql;

13.数据库三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。

• 声明：三范式是面试官经常问的，所以一定要熟记在心！

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

13.1第一范式

学生编号 学生姓名 联系方式
	------------------------------------------
	1001		张三		zs@gmail.com,1359999999
	1002		李四		ls@gmail.com,13699999999
	1001		王五		ww@163.net,13488888888
	
以上是学生表，满足第一范式吗？
	不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话

学生编号(pk) 学生姓名	邮箱地址			联系电话
----------------------------------------------------
1001				张三		zs@gmail.com	1359999999
1002				李四		ls@gmail.com	13699999999
1003				王五		ww@163.net		13488888888

13.2第二范式

	学生编号 		学生姓名 	教师编号 教师姓名
	----------------------------------------------------
	1001			张三		001		王老师
	1002			李四		002		赵老师
	1003			王五		001		王老师
	1001			张三		002		赵老师
这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！

分析以上的表是否满足第一范式？
	不满足第一范式。

怎么满足第一范式呢？修改

学生编号+教师编号(pk)		学生姓名  教师姓名
----------------------------------------------------
1001			001				张三			王老师
1002			002				李四			赵老师
1003			001				王五			王老师
1001			002				张三			赵老师

学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
	不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
	产生部分依赖有什么缺点？
		数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。

为了让以上的表满足第二范式，你需要这样设计：
	使用三张表来表示多对多的关系！！！！
	学生表
	学生编号(pk)		学生名字
	------------------------------------
	1001					张三
	1002					李四
	1003					王五
	
	教师表
	教师编号(pk)		教师姓名
	--------------------------------------
	001					王老师
	002					赵老师

	学生教师关系表
	id(pk)			学生编号(fk)			教师编号(fk)
	------------------------------------------------------
	1						1001						001
	2						1002						002
	3						1003						001
	4						1001						002

• 多对多关系设计口诀：三张表，关系表两个外键！！！！！！！！！！！！！！！

13.3第三范式

	学生编号（PK） 			学生姓名  班级编号  	班级名称
---------------------------------------------------------

		1001				张三		01			一年一班
		1002				李四		02			一年二班
		1003				王五		03			一年三班
		1004				赵六		03			一年三班

以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。

分析以上表是否满足第一范式？
	满足第一范式，有主键。

分析以上表是否满足第二范式？
	满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。

分析以上表是否满足第三范式？
	第三范式要求：不要产生传递依赖！
	一年一班依赖01，01依赖1001，产生了传递依赖。
	不符合第三范式的要求。产生了数据的冗余。

那么应该怎么设计一对多呢？

	班级表：一
	班级编号(pk)				班级名称
	----------------------------------------
	01								一年一班
	02								一年二班
	03								一年三班

	学生表：多

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

• 一对多，两张表，多的那表加外键！！！！！！！！！！！！（员工表）

13.4 设计口诀

• 多对多，三张表，关系表两外键。

• 一对多，两张表，多的表加外键。

• 一对一，数据量大，外键加唯一性约束。

关于三范式的实际使用

数据库设计三范式是理论上的，实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

面试的时候把这句话说上：他就不会认为你是初级程序员了！

注意

1.在数据库中的字符串采用单引号括起来。
2.数据库中null为什么也没有，所以不能用=来衡量,有null参与的数学运算结果也是null
3.模糊查询的数据中包含下划线：用\_转义
4.数据库中把字段名误写为具体值(字面值)时，会用该具体值按照表的结构。
5.快速创建表，将查询结果建表
    create table result as select * from emp;
6.将查询结果插入一张表
    insert into dept_bak select * from dept;
7.快速删除表中数据
    delete删除支持回滚，但缺点是删除数据所在的硬盘真实存储空间不会释放，效率较低。
    truncate物理删除，效率高但不支持回滚。
    truncate table dept_bak;
8.xxxx.sql被称为sql脚本文件，可以批量执行sql语句   初始化数据库
    source 绝对路径
9. 查看mysql支持哪些存储引擎
    show engines \G

• 在mysql当中怎么计算两个日期的“年差”，差了多少年？

  • TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

    timestampdiff(YEAR, hiredate, now())

    间隔类型：
    SECOND 秒，
    MINUTE 分钟，
    HOUR 小时，
    DAY 天，
    WEEK 星期
    MONTH 月，
    QUARTER 季度，
    YEAR 年

）

13.4 设计口诀

• 多对多，三张表，关系表两外键。

• 一对多，两张表，多的表加外键。

• 一对一，数据量大，外键加唯一性约束。

关于三范式的实际使用

数据库设计三范式是理论上的，实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

面试的时候把这句话说上：他就不会认为你是初级程序员了！

注意

1.在数据库中的字符串采用单引号括起来。
2.数据库中null为什么也没有，所以不能用=来衡量,有null参与的数学运算结果也是null
3.模糊查询的数据中包含下划线：用\_转义
4.数据库中把字段名误写为具体值(字面值)时，会用该具体值按照表的结构。
5.快速创建表，将查询结果建表
    create table result as select * from emp;
6.将查询结果插入一张表
    insert into dept_bak select * from dept;
7.快速删除表中数据
    delete删除支持回滚，但缺点是删除数据所在的硬盘真实存储空间不会释放，效率较低。
    truncate物理删除，效率高但不支持回滚。
    truncate table dept_bak;
8.xxxx.sql被称为sql脚本文件，可以批量执行sql语句   初始化数据库
    source 绝对路径
9. 查看mysql支持哪些存储引擎
    show engines \G

• 在mysql当中怎么计算两个日期的“年差”，差了多少年？

  • TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

    timestampdiff(YEAR, hiredate, now())

    间隔类型：
    SECOND 秒，
    MINUTE 分钟，
    HOUR 小时，
    DAY 天，
    WEEK 星期
    MONTH 月，
    QUARTER 季度，
    YEAR 年