数据库概述

• MySQL就是数据库管理系统，专门用于管理数据的。
• 目的是简化对文件的操作，通过简单的SQL语句即可做到对文件的增删改查。
• 常见的数据库管理系统：

Oracle、MySQl、DB2、Sybase、MS SqlServer（支持标准SQL的数据库管理系统）

• 其中，Oracle是昂贵的，数据安全级别较高的，传统企业常用的
• MySQL的便捷免费开源，让其成为互联网产业最常用的数据库管理系统。
• MySQL数据库的端口：3306
• 表：参考Excel

SQL：

结构化查询语言，是一门标准通用的高级语言，适用于所有数据库产品。

DBMS：

Database Management System，数据库管理系统

DB：

DataBase，数据库，实际在硬盘上以文件的形式存在。

MySQL部分命令

• 登录

mysql -u用户名 -p密码

• 列出数据库：

show databases；

• 创建数据库（传入的名字都会转换为小写）

create database 名字；

• 使用数据库

ues 数据库名字；

• 列出当前数据库中的表

show tables；

• 初始化数据：（初始化数据前要先选择使用数据库）

source sql；

• 删除数据库

drop database 数据库名字；

• 查看表结构

desc 表名字；

9、\c 终止语句

10、show creat table emp；

查看建表语句

11、distinct，去重

12、union，链接

        这个关键字常用于拼接两张不关联的表。有关联数据的拼接可以使用其他关键字。

SQL语句分类

DQL ：

Data Query Language 数据查询语句，用来在数据库中查询的语言，select就在这里；

DML：

Data Manipulation Language 数据操作语言，一般用来做表中数据的增加和删除；

insert、delete、updata

DDL：

Data Difinition Language 数据定义语言，用于对表的结构进行增删改；

create、drop、alter

TCL：

事务控制语言

DCL：

Data Control Language 数据控制语言，一般是DBA才会用zd到的数据库用户管理及日常维护语言。grant授权、revoke撤销权限

SQL语句中的执行顺序

select    5、列出/找出

        ….

from     1、在…表中

        …

where    2、条件

        …

group by  3、分组

        …

having    4、过滤

        …

order by  6、排序

        …

limit        7、取几个

        …

表查询

简单的DQL

• 查询：

select 字段名1,字段名2………….from 表名；

• 查出的月薪乘12

select empno, ename, sal*12 from emp;

• 查询出来的字段显示为中文或其他字符

select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;

• 条件查询：

select 要检索的字段名………..from 表明 where 条件；

• 模糊查询：

ename like ‘A%’

所有名字开头是A的员工

ename like ‘_A%’

所有名字第二个字母是A的员工

ename like ‘%\_%’

所有名字包含下划线的员工

• 包含in

select * from emp where job in (‘manager’,’salesman’);

职业为’manager’,’salesman’繁荣员工；

• 排序：

排序使用order by语句，后面跟上排序字段

select * from emp order by sal;

按照薪水升序排序

select * from emp order by sal,job；

按照薪水升序排序，薪水相同时再按照工作类型排序

若要手动指定排序顺序，在字段名后加asc代表升序，加desc代表降序。

• 转换为其他字段名：

as 其他想要编程的名字。

SQL中的运算符

SQL中的单行函数

• 用法：在字段名中用小括号括起来，填入函数名。
• 多行处理函数：
• select ifnull(comm,0) from emp;

列出津贴，若津贴为null则转换为0。

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

列出所有人的年薪。

SQL中的多行处理函数

注意：多行处理函数自动忽略null

          where关键字后面不能使用分组函数

使用方法：写出关键字然后加小括号，小括号中填入字符名。

select max(sal) from emp;

在emp表中找出薪水最高的人。

分组查询

• 主要涉及到的两个关键字：group by和having
• select sum(sal),job from emp group by job;

找出每个工作岗位的月薪总计

• 当一条SQl语句中含有group by的话，select后面只能跟有分组函数以及参与分组的字段。5以后的MySQL以及Oracle会报错。
• group by可以联合分组，后面跟多个字段名。
• having可以理解为，分组后再执行的where。

列出所有工作岗位的平均工资并降序排列

列出工资高于平均工资的员工

列出不同部门中，不同职业的最高月薪，并按照部门降序排列。

select job,deptno,max(sal) from emp group by deptno,job order by deptno desc;

找出每个部门的平均薪资，并且只显示平均薪资大于2000的数据。

连接查询

• 连接查询就是多张表联合取出数据，实际开发不会只有一张表。
• 只有一张表的话会造成数据冗余。就像一直重复一群人都是中国人一样。
• 链接查询的分类：

内连接：

等值链接、非等值链接、自链接

外连接：

    左外链接、右外连接

全连接

按照年代分类：

         SQL92（基本不用，老旧的语法）

         SQL99（主流）

• 表的别名；

from 后面表的名字添加其他的字符串：elect e.ename,d,dname from emp e,dept d；

添加别名可以提高执行效率，让可读性更好

内连接

• 多表链接查询会出现笛卡尔积现象，也就是查询出来的结果是这几张表的记录条数之积。
• 避免笛卡尔积现象的方法：添加条件进行过滤

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;（老语法）

等值连接：条件是等量关系的链接。（join on）

select

e.ename,d.dname

from

dept d

inner join（inner可以省略，但加上去可读性更好）

emp e

on

e.deptno = d.deptno;

        非等值链接：就是连接条件不是比较是否相等

        select

                e.ename,s.grade

        from

                emp e

        inner join

                salgrade s

        on

                e.sal between s.losal and s.hisal;

• 将表emp连接至dept，on后面加连接条件。
• 这种语法比92好的原因是结构更清晰，连接条件与筛选条件分开，要对链接后的数据在进行筛选，后面可以添加额外的where关键字。
• 自链接：自己链接自己，一张表根据不同的数据看成两张表

需求：将表中的领导编号全部换为领导名字。

select

         e.ename as ‘员工’, m.ename as ‘领导’

from

         emp m

inner join

         emp e

on

        m.empno = e.mgr;

外连接

• 外链接与内连接的区别：

内链接没有主副之分，两张表中的数据不匹配就不显示。

外连接有主副之分，主要查主表，捎带查附表，数据匹配则一起显示，数据不匹配，则附表会模拟出null来匹配主表

• 外连接有左右连接之分，左连接有对应的右连接的写法，右连接也有相应的左连接的写法。
• 案例：
  1. 找出没有员工的部门（主表部门）

select

        d.dname,e.deptno

from

        emp e

right join

        dept d

on

        e.deptno = d.deptno

where

        e.deptno is null;

1. 找出所有员工，附带查询他们的领导（主表员员工，附表领导）

select

        e.ename ‘员工’,m.ename ‘上级’

from

emp e

left join

        emp m

on

        e.mgr = m.empno;

多表查询

• 语法：就是多写几个join…on结构。
• 案例：
  1. 列出每一个员工的部门名称以及工资等级

select

        e.ename,d.dname,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;

1. 列出每个员工的部门名称、工资等级以及上级领导的名字

select

        e.ename,d.dname,s.grade,e2.ename

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 e2

on

        e.mgr = e2.empno;

子查询

• 子查询就是select语句嵌套select语句
• select的嵌套可以用于：

where、 from、 select关键字后面

• 案例：
  1. 列出每个部门平均薪水的薪资等级（P38）

select

        a.dname,s.grade

from

(select avg(e.sal) avgsal,d.dname from emp e join dept d on e.deptno = d.deptno group by e.deptno) as a

join

        salgrade s

on

1. avgsal between s.losal and s.hisal

每个部门的平均薪水：

select

        avg(e.sal) avgsal,d.dname

from

        emp e

join

        dept d

on

        e.deptno = d.deptno

group by

        e.deptno;

1. 列出每个部门平均的薪水等级

select

        avg(l.grade),d.dname

from

(select e.ename,s.grade,e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal) l

join

dept d

on

        d.deptno = l.deptno

group by

        l.deptno

;

每个人的薪水等级：

select

        e.ename,s.grade,e.deptno

from

        emp e

join

        salgrade s

on

        e.sal between s.losal and s.hisal

Limit

• limit是MySQL特有的，其他数据库不通用
• 作用是取出查询结果集的部分数据。
• 语法：

limit startIndex，length

stratIndex表示起始位置，从0开始。

length表示去多少个数据。

• 通用的标准分页SQL语句：

每页显示pageSize条记录

第pageNo页：

         (pageNo – 1)*pageSize,paageSize

表操作

创建表

语法：

creat table 表名(

         字段名1 数据类型 default 默认值,

         字段名2 数据类型default 默认值.

                字段名3 数据类型default 默认值,

                ……

                )

                ENGINE = 引擎名 DEFAULT CHARSET = 字符集名;

其中，引擎名默认是InnoDB，字符集名默认是UTF8。

若不使用default关键字，则默认值一般都是null

MySQL常用数据类型

char与varhar的选择：

        若要存入数据库中的数据是长度不会变的数据，如生日，性别等，可以使用char。

        若存入数据库中的数据长度是不确定的，建议使用varchar，如姓名地址等。

BLOB以及COLB：

        不能直接插入数据，要以流的方式放入数据。

        BLOB可以用于存储图片，视频等。

        CLOB可以用于存储简介等长度较长的字符串对象。

建表时候的表名：

一般以t_或者tbl_为开头，可读性更高。因为数据库中还有表以外的数据形识

删除表：

        drop table if exists 表名;

        如果表存在则删除该表。

插入、修改、删除数据

插入语法：

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

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

        insert into 表名字 select语句;

        第二中写法中要注意值的顺序默认对应默认的字段名。

        而再第一种写法中传入的值的顺序只需要对应传入的字段名。

        第三钟写法是将查询结果插入表中。

在第一种写法中，若不传入对应的值，则表会插入在创建表时的默认值

注意：使用了insert关键字后，不能再次插入语句。只能使用update进行修改。

另外也可以一次插入多行数据，要在values关键字后面多用几个小括号传入数据即可。

修改语法：

        update 表明 set 字段名1 = 值1，字段名2 = 值2……where 修改条件;

        若不添加where关键字，则会修改表中所有的数据。

删除语法：

        delete from 表名 where 条件;

        若不添加where关键字，则会删除表中所有数据。

        若要删除大表

        truncate table 表名;

        这种方式适合删除数据量很大的表，但这种删除方式不可回滚，数据永久丢失。

约束

• 创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是保证数据的完整性、合法性以及有效性
• 常见的约束
  1. 非空约束（not null）：约束的字段不能为null
  2. 唯一约束（unique）：约束的字段不能重复
  3. 主键约束（primary key）：约束的字段不能为null也不能重复
  4. 外键约束（foreign key）：
  5. 检查约束（check）：仅在Oracle数据库中支持的约束，MySQL不支持该约束。
• 列级约束：直接在字段后面加约束关键字

creat table 表名(

         字段名1 数据类型 unique,

         字段名2 数据类型not null.

         字段名3 数据类型default 默认值,

         ……

         );

• 表级约束：声明完字段名字后单独添加约束关键字以及要被约束的字段

creat table 表名(

         字段名1 数据类型 default 默认值,

         字段名2 数据类型default 默认值.

         字段名3 数据类型default 默认值,

         ……

         unique(字段名1,字段名2……)

         );

主键约束(PK)

• 主键约束可以以列级约束或者表级约束的方式添加。
• 相关术语：
  1. 主键约束
  2. 主键字段
  3. 主键值
• 主键的作用：
  1. 表的三范式设计中有要求，每个表都应该有主键。
  2. 相当于一条记录在一张表中的唯一标识。
• 主键的分类：
  1. 按照数量分类：
     1. 唯一主键（推荐使用）
     2. 复合主键（多个字段联合起来做一个主键）
  2. 按照性质分类：
     1. 自然主键（与该表对应的业务无任何关系的自然数）
     2. 业务主键（主键值与表的业务有关联。比如银行卡号或身份证号做主键）

• 一张表的主键只能有一个
• 主键自增：

MySQL中支持主键自增机制，在primary key关键字后面添加auto_increment即可。

外键约束(FK)

• 添加语法：

foreign key(字段名) reference 父表名(字段名)

含义：该字段中的值全部引用与父表中的某一字段名。

• 相关术语：
  1. 外键约束
  2. 外键字段
  3. 外键值
• 外键的作用：
  1. 让一张表的子表的数据相对父表更具有规范性，有利于联合查询。
  2. 维护表之间的关系，保证参照的完整性。
• 一个表的外键必须来源于父表的主键，或者是被修饰为不可重复的字段。
• 外键字段可以为null，但不推荐设置为null。
• 联合查询时，一般会以表的外键来进行连接。

事务

• 事务的定义：

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

比如要执行两条同时成功或失败的DML语句，不可再分，必须同时成功或失败。

那么这两条DML语句就是一个不可再分的完整逻辑单元。

• 与事务相关的语句只有DML语句，因为DML语句与数据库中的数据的安全有直接的关系。为了保证数据的准确性、完整性、安全性，我们需要使用事务机制。
• 相关术语：提交commit、回滚rollback、保存点savepoint
• 事务机制的应用是建立在多条DML语句同时执行的前提上的。
• 事物的特性（ACID）：
  1. 原子性Atomicity：事务是最小的工作单元，不可再分。
  2. 一致性Consistency：保证事务中的DML语句必须同时成功或失败，事务前后数据 的完整性必须一致
  3. 隔离性Isolation：不同的事务间具有隔离。
  4. 持久性Durability：数据最后必须要持久化到硬盘中，事务才算结束。
• MySQL中的的事务默认是自动提交的。执行任意一条DML语句就会提交。

关闭自动提交则要输入start transaction来开启事务，事务结束后要重新输入。

事务结束前可以使用rollback进行回滚。

事务的隔离级别

设置事务的全局隔离级别：

        set global transaction isolation level 级别名称;

        需要重新登陆才会生效。

查看隔离级别：

老版本：select @@global.tx_isolation;

新版本；select @@global.transaction_isolation,@@transaction_isolation;

第一级别：读未提交read uncommitted

即对方事务尚未提交的数据，当前事务可以读取到。

容易发生脏读（Dirty read）现象，表示读到了脏数据。

这种级别基本不用。

脏数据：脏数据表示的是另一个事务的临时数据，这种数据极不问题，当另一事务的数据提交或回滚后，脏数据就会变化。依据脏数据所作的操作是不正确的。

第二级别：读已提交read committed

对方事务提交后的数据才可以被我方事务读取到。

这种方式解决了脏读现象。

但没有解决不可重复读的问题。

这种级别是主流的安全级别，适用于大多数系统。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。简单地说就是别的事务会影响当前事务的数据，导致当前事务对数据执行逻辑的时候，每次读到的数据可能会不一样。

第三级别：可重复度repeatable read

读取数据前会对当前数据作备份，只读取备份的数据。

因为每次读取的都是自己备份的数据，所以解决了不可重复读得问题。

但没有解决幻读 (phantom read)问题。

资源消耗较高的级别，带来了更多的性能消耗。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

第四级别：序列化/串行化读

所有事务排队执行，最严格的级别。

解决所有问题。

资源消耗最大的级别。

索引

• 索引的概述：

索引相当于一本书的目录，可以快速定位数据资源。在数据量庞大的时候快速检索。

查询一张表的时候有两种方式：全表扫描、依据索引检索。

• 缩小了扫描范围是索引提高检索效率的根本原因，底层使用的数据结构是B+Tree（多路搜索树）。
• 使用索引要考虑多方面因素，因为数据一旦改动，索引是要重新维护的，消耗维护成本。
• 适合添加索引的环境：
  1. 数据量相对庞大
  2. 添加索引的字段极少进行DML操作，避免索引维护。
  3. 添加索引的字段常出现在where子句中。
• 注意：主键以及具有unique约束的字段自动添加索引。
• 查看当前SQL的执行计划：

explain SQL语句;

在出来的表格中的type字段中可以看到扫描类型，all为全表扫描。

表格中的rows字段可以查看检索了多少条数据。

• 给字段添加索引;

creat index 索引名称 on 表名(字段名)；

• 删除索引：

DROP INDEX 索引名称 ON 表名;

• 实现原理：

当添加索引后，会在索引所在字段生成一个地城实现了排序分区的B_tree数据结构的排序表。索引会获取数据在表中的“物理地址，检索到数据后返回相关的物理地址，直接通过物理地址定位到硬盘中的相关数据，以获得更高的效率”

• 索引的分类：
  1. 单一索引：单个字段添加索引
  2. 复合索引：多个字段联合添加一个索引
  3. 主键索引：主键上自动添加索引
  4. 唯一索引：有unique约束的字段自动添加索引

10、索引失效：

当使用模糊查询，且第一个通配符使用的是“%“，此时索引失效，只能使用全表扫描。

视图

• 视图是一种以不同方式看待表的方法。有时候也被称为虚拟表，但是对视图进行CRUD会影响原表中的数据。
• 视图的作用：
  1. 通过简化，访问数据变得简单
  2. 对不同用户展示不同的内容
  3. 隐藏表的具体细节，只展示表的大致结构。（面向视图检索）
• 创建视图：

create view 视图名 as DQL语句;

创建视图只能通过DQL语句进行创建。

可在DQL语句中对字段进行改名以实现隐藏原表字段名的效果。

• 删除视图：

drop view 视图名;

• 视图的CRUD:

与对表的CRUD相同，将from后的表名改为视图名即可。

6、通过视图查询不会真正意义上的提高检索效率，真要提高还得看索引.。

DBA命令

• 常用于导出导入数据进行数据备份以及数据转移。
• 导出：（在dos命令窗口中执行）
  1. 导出整个数据库：

mysqldump 数据库名>导出路径以及文件名.sql -u用户名 -p密码

mysqldump sinot> D:\JAVA\sinot.sql -uroot -p159326

1. 导出数据库中的某个表：

mysqldump 数据库名 表名>导出路径以及文件名.sql -u用户名 -p密码

mysqldump sinot demp> D:\JAVA\sinot_demp.sql -uroot -p159326

• 导入：
  1. 先创建数据库并使用
  2. source sql脚本所在路径;

三范式

• 设计范式，就是设计表的依据，主要目的是减少数据冗余。
• 三范式的的第二范式建立在一范式的基础之上，第三范式建立在第二范式之上。
• 一范式：

任何一张表都要有主键，且每个字段都有1原子性，不可再分。

• 二范式：

所有非主键字段要完全依赖主键，不能产生部分依赖。

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

• 三范式：

所有非主键字段要直接依赖主键，不能产生传递依赖。

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

• 提醒：设计表是并不是一定要完全遵守三范式，表的联合越多，查询效率越低，有时候需要牺牲数据库空间以获取查询速度，主要以客户需求为主。
• 一对一的设计
  1. 主键共享：两张表公用一个主键，其中一张表的主键既有外键约束也有主键约束。
  2. 外键唯一：两张表都有各自的主键，其中一张表有单独的外键，该外键以另一张表的主键作父表字段名，并且添加unique约束。也就是一对多的设计，表额外加了唯一性约束变为一对一的设计。

注意：

SQL语句都是以分号结尾

SQL语句不区分大小写

SQL中字符串用单引号括起来

count(*)与count(字段名)的区别：加星号代表计算某个字段的总记录条数，加具体字段名代表计算该字段名中不为null的数据数量。

只能用于所有字段名前。