MySQL

MySQL概述

SQL、DB、DBMS分别是什么？他们之间的关系

DB：DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）

DBMS：DataBase Management System（数据库管理系统，常见的有：MySQL，Oracle，DB2，Sybase，SqlServer...）

SQL：结构化查询语言，是一门标准通用的语言。

DBMS负责执行SQL语句，通过执行SQL语句来操作DB中的数据

什么是表?

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

? 一个表包括行和列，行被称为数据（data）或者是记录，列被称为字段（column）

每个字段应该包括哪些属性？

? 字段名、数据类型，相关的约束

SQL语句的分类

• DQL（数据查询语言）：查询语句，凡是select语句都是DQL
• DML（数据操作语言）：insert、delete、update，对表中数据的增删改
• DDL（数据定义语言）：create、drop、alter，对表结构的增删改
• TCL（事务控制语言）：commit提交事务，rollback回滚事务
• DCL（数据控制语言）：grant授权，revoke撤销权限等

MySQL常用命令，DDL语句

• 登录mysql数据管理系统
  • dos命令窗口：mysql -root -root
• 查看当前MySQL版本号
  • select version()；
• 查看有哪些数据库
  • show databases；（这不是SQL语句，属于MySQL命令）
• 创建表
  • create database 表名；（MySQL命令）
• 使用表
  • use 表名；（MySQL命令）
• 查看当前数据库有哪些表
  • show tables；（MySQL命令）
• 初始化数据
  • source sql文件的绝对路径（以sql结尾，这样的文件被称为sql脚本）
• 删除数据库
  • drop database 表名；
• 查看表结构
  • desc 表名；

简单查询

MySQL执行顺序

SELECT语句定义

一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下：
SQL代码

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] 

SELECT语句执行顺序

SELECT语句中子句的执行顺序与SELECT语句中子句的输入顺序是不一样的，所以并不是从SELECT子句开始执行的，而是按照下面的顺序执行：

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果 

MySQL比较运算符

聚合函数知识点

• 聚合函数有：avg平均值，sum求和，max最大值，min最小值，count计数
• 聚合函数又叫多行处理函数，把多行数据进行处理，最后返回一行
• 聚合函数自动忽略NULL值，在数据库语言中，NULL参与运算，这个运算结果一定为NULL
• 对聚合函数的结果进行二次筛选的时候，条件 应该跟在having后 而不是where后面。
• 聚合函数一般和group by联合使用，在group by执行完之后再执行

单行处理函数

ifnull(可能出现null的字段,把这个null当作什么来处理)

group by 和 having

group by：按照某个字段或者某些字段进行分组

having：对分组之后的数据进行再次过滤

group by可以多个字段分组，只要在字段后面加,就好了。优先前面的

#案例：找出每个部门不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job

当一条语句后面有group by时，select后面只能跟参加分组的字段和分组函数

去重关键字distinct

? distinct只能出现在所有字段的最前方，如果后面有多个字段，就是多个字段联合去重（多个字段不重复的，第一个字段重复，但是第二字段不重复就不算重复）

案例：统计岗位的数量

select count(distinct job) from emp;

连接查询

连接查询的分类

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

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

内连接

等值连接

最大的特点：条件是等量关系

案例：查询每个员工的部门名称，要求显示员工名和部门名

SQL92语法
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno

SQL99语法
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno

99语法更清晰，因为表连接的条件和where条件分离了

非等值连接

最大的特点：条件是非等量关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级

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

inner表示内连接，可以省略不写

自连接

最大的特点是：一张表看作两张表，自己连接自己

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名

select a.ename as ‘员工名‘,b.ename as ‘领导名‘ from emp a inner join emp b on a.mgr = b.empno;

外连接

什么是外连接？和内连接的区别？

内连接：

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

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

外连接的分类

左外连接：表示左边的这张表是主表

右外连接：表示右边的这张表是主表

左外连接有右外连接的写法，右连接也会有对应的左连接的写法

案例：找出每个员工的上级领导？（所有员工必须全部查出来）

select a.ename ‘员工‘,b.ename ‘领导‘ from emp a left join emp b on a.mgr = b.empno

案例：找出哪个部门没有员工

select d.* from emp e right join dept d on e.deptno = d.deptno where e.empno is null;

三张表怎么连接查询？

案例：找出每个员工的部门名称以及工资等级

select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join e.sal between s.losal and s.hisal;

增加难度：找出每个员工的部门名称、工资等级以及上级领导（如果没有上级领导也要展示数据）

select e.ename ‘员工名称‘,d.dname,s.grade,e1.ename ‘领导名称‘ from emp e join dept d on e.deptno = d.deptno join e.sal between s.losal and s.hisal left join emp e1 on e.mgr = e1.empno;

子查询

什么是子查询？子查询可以出现在哪里？

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

子查询可以出现在select 后面，from后面，where后面

where语句中使用子查询

where语句中使用子查询就是相当于把查询结果当成一个条件

案例：找出高于平均薪资的员工信息

select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询

from后面嵌套子查询就是相当于把查询出来的结果当成一个新表

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

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;

union

可以将查询结果集相加

案例：找出工作岗位是SALESMAN和MANAGER的员工

第一种：select ename,job from emp where job = ‘MANAGER‘ or job = ‘SALESMAN‘;

第二种：select ename,job from emp where job in(‘MANAGER‘,‘SALESMAN‘);

使用union：

select ename,job from emp where job = ‘MANAGER‘ union select ename,job from emp where job = ‘SALESMAN‘

如果是两张毫无相干的表，可以使用union连接，但是查询的列必须相同

limit分页

limit是MySQL特有的，其他数据库没有，不通用。

limit取结果集的部分数据，这是它的作用。

语法机制：limit starIndex,length ，starIndex表示起始位置，length表示取几个

案例：找出工资前五名的员工

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

直接写一个数字表示默认从第1个开始。下标从0开始。

通用的标准分页SQL

每页显示3条记录：

第1页：0，3

第2页：3，3

第3页：6，3

规律：

每页显示pageSize条记录：

第pageNum页：(pageNum-1)*pageSize,pageSize

表的创建

建表语句的语法格式：

create table 表名(
    字段名1 数据类型 default 1, //default表示默认值，如果插入语句没有插入字段1，默认插入1
    字段名2 数据类型,
    字段名3 数据类型,
    ...
);

关于MySQL当中字段的数据类型（一下只说常见的）

insert插入数据

语法格式：

insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

要求：字段的数量和值的数量相同，并且数据类型对应，表名后面可以不表明要插入的字段，默认全部插入，也可以写部分字段，其他字段为null

一次插入多行数据

insert into t_student(no,name,sex,classno,birth) 
values
(3,‘rose‘,‘1‘,‘高一三班‘,‘1997-11-09‘),
(4,‘jack‘,‘0‘,‘高一三班‘,‘1997-10-22‘);

可以一次插入多行，用逗号隔开

表的复制

语法：

create table 表名 as select语句;

将查询结果作为表创建出来

insert into 表名 select语句;

将查询结果插入到一张表中

update修改数据

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

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

delete删除数据

语法格式：

delete from 表名 where 条件;

注意：没有条件全部删除

怎么删除大表？

truncate table 表名;

用truncate删除后数据永久丢失，不可回滚

约束（Constraint）

什么是约束？常见的约束有哪些？

在创建表的时候，可以给表的字段添加相应的约束，约束就是为了保证表中的数据的唯一性，合法性，有效性等等

常见的约束：

• 非空约束（not null），约束的字段不能为NULL
• 唯一约束（unique），约束的字段不能重复，但可以为NULL
• 主键约束（primary key），约束的字段既不能为NULL，也不能重复（简称PK）
• 外键约束（foreign key）
• 检查约束（check）Oracle数据库有check约束，但是MySQL没有，目前MySQL不支持check约束

主键自增

auto_increment

create table t_user(
	id int primary key auto_increment,
    username varchar(255)
);

外键约束

为什么要用外键？用一个案例来解释

业务背景：请设计数据库表，来维护学生和班级的信息

第一种方案：一张表存储所有的信息

缺点：班级信息是固定只有那么几个，这么做会使数据【冗余】，不推荐

第二种方案：两张表（学生表和班级表），用外键来连接两个表的关系

t_class 班级表

t_student 学生表

t_student中的cno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class表叫做父表。

删除数据时先删除子表，再删除父表，添加数据时，先添加父表，再添加子表

创建表时先创建父表，再创建子表，删除表时先删除子表，再删除父表

以上两张表的建表语句

create table t_class(
	cno int,
    cname varchar(255),
    primary key(cno)
);

create table t_student(
	sno int primary key,
    sname varchar(255),
    cno int,
    foreign key(cno) references t_class(cno)
);

事务（Transaction）

什么是事务？

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

比如：银行账户转账，从A账户向B账户转账1000，需要执行两条update语句。

update t_act set balance = balance - 1000 where actno = ‘act-001‘;
update t_act set balance = balance + 1000 where actno = ‘act-002‘;

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

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

只有DML语句才和事务有关系（insert，delete，update）

事物的四大特性

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

一致性：事务必须保证数据的一致性，要么全部成功，要么全部失败。

隔离性：事务A与事务B之间具有隔离。

持久性：数据必须持久化到硬盘文件中。

关于事务的隔离性

事物之间存在隔离级别，理论上隔离级别包括4个：

1. 读未提交(read uncommitted)：对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读现象，表示读到了脏数据。
2. 读已提交(read committed)：对方事务提交之后的数据，我们可以读取到，读已提交可以解决脏读，但是会存在不可重复读。
3. 可重复读(repeatable read)：这种隔离级别解决了不可重复读问题，这种隔离级别存在的问题是：读取到的数据是幻想。
4. 序列化读/串行化读：解决了所有问题。效率低，需要事务排队。

Oracle数据库默认的隔离级别是：读已提交

MySQL数据库默认的隔离级别是：可重复读

演示事务

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

怎么关闭自动提交？start transaction，这也是开启事务

事务的提交和回滚

start transaction开启事务

commit提交事务

rollback回滚事务（回滚到上一次事务结束的点）

提交事务和回滚事务都会导致事务结束

索引

什么是索引？有什么用？

? 索引就相当于一本书的目录，通过目录就可以快速的找到对应的资源，在数据库方面，查询一张表的时候，有两种查询方式。

第一种方式是全表扫描。

第二种方式是根据索引检索（效率很高）。

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

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

怎么创建和删除索引对象？

创建索引

create index 索引名 on 表名(字段名);

删除索引

drop index 索引名 on 表名;

什么时候考虑给字段添加索引？

• 数据量庞大
• 该字段很少的DML操作
• 该字段经常出现在where子句中
• 主键和具有unique约束的字段会自动添加索引，根据主键查询效率较高，尽量根据主键检索。

查看sql语句的执行计划

可以在sql语句前面加 explain关键字来查看该sql的执行计划

索引的实现原理

索引底层采用的数据结构是：B+Tree

? 通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

索引的分类

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

模糊查询时，第一个通配符使用的是%，这个时候索引是失效的，会全盘扫描

视图（view）

什么是视图？

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

怎么创建视图？删除视图？

创建视图：

create view 视图名 as select语句;

只有DQL语句才能以视图对象的方式创建出来

删除视图：

drop view 视图名;

面向视图操作

对视图进行增删改查会影响原表数据。通过视图影响原表数据的，不是直接操作的原表。

视图的增删改查和表的增删改查语句是一样的

视图的作用

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

数据库设计三范式

第一范式：任何一张表都应该有主键，每一个字段原子性不可再分。

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

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

MySQL

原文：https://www.cnblogs.com/lxs1204/p/14413463.html