MySQL数据库的知识总结与整理

数据库

1.概述

数据库DB（DataBase）：数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。简单的说就是用来存储和管理数据的仓库
分类：
关系型数据库：存储的格式可以直观的反映实体之间的关系，常见的Mysql，SqlServer、Oracle
非关系型数据库：指的是分布式的、非关系型的、不保证遵循ACID原则的数据存储系统。常见的有NoSQL、Redis

2.Mysql数据库的安装

服务器安装：端口号默认使用3306，字符集默认使用Latin1（最好使用utf-8），密码使用root（一般）
客户端安装：sqlYog等软件
客户端有两种：DOS窗口/可视化工具
检验：

3.Mysql数据库的使用

客户端连接服务器，使用服务器里的数据

4.数据库的结构

数据库的结构：数据库–>表–>记录（行，列（字段）组成的）

SQL语句

全称是结构化的查询语句，对数据库里的数据进行各种操作，常见操作就是增删改查CRUD
CRUD操作：新增Create、查询Retrieve、修改Update、删除Delete
结构化查询语言是一种数据库查询和程序设计语言，用于存放数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

1.分类

DML（Data Manipulation Language）数据操作语言，是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
DDL：（Data Definition Language）数据库定义语言，创建表，创建库
是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成
DCL：（Data Control Language）数据库控制语言，细粒度的管理操作数据库的权限
在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。
DQL：（Data Query Language）数据库的查询语言
是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词
注意：SQL不区分大小写

2.常用的操作

数据库操作

查看所有数据库：show databases;
使用数据库：use 库名；
创建数据库：create database 库名；
删除数据库：drop database 库名；

表的操作

查看表的结构：desc 表名；
查看表：先指定使用的库：use 库名;然后执行show tables;
创建表：create table 表名(字段名 字段类型(长度)，字段2,字段3…);
修改表：alter table 表名 add column字段名 字段类型(字段长度) where 条件;
删除表：drop table 表名;

字段的操作

查询:select * from 表名;select 字段1，字段2…from 表名;
创建：insert into 表名 values(字段1的值，字段2的值…);
注意;值的个数必须与表里字段的个数一致，值的类型要相互对应
先解决中文乱码问题，否则会报错，解决方案：set names gbk;（固定格式）

修改：update 表名 set 字段名=新值;–修改所有 （可以加where条件进行限制）
注意：如果是字符串类型就要加’’或””

删除：delete from 表名;–删除表里的所有数据

3.数据类型

命名规则

1.字段名必须以字母开头，尽量不要使用拼音
2.长度不能超过30个字符（不同数据库，不同版本会有不同）
3.不能使用SQL的保留字，如where,order,group
4.只能使用如下字符az、AZ、0~9、$ 等
5.Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
6.多个单词用下划线隔开，而非java语言的驼峰规则

字符

char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
大文本: 大量文字（不推荐使用，尽量使用varchar替代）
以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

数字

tinyint,int整数类型
float,double小数类型
numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
decimal和numeric表示精确的整数数字

日期

date 包含年月日
time时分秒
datetime包含年月日和时分秒
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

4.字段约束

1.主键约束（primary key）：字段值必须唯一且不能为null,通常情况下，每张表都会有主键。
添加主键约束，例如将id设置为主键
表设计的原则是：表必须有主键，作为每条记录的唯一标识
主键自增策略：auto_increment,通常会使用自增策略，主键的值交给数据库维护管理（从1开始，++）
2.非空约束：（not null）如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
3.唯一约束：（unique）如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
添加唯一约束，例如为username添加唯一约束及非空约束：
4.默认约束default，给指定字段设置默认值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,
sex VARCHAR(10) DEFAULT ‘男’,#设置默认值
age INT DEFAULT 18
);
5.外键约束foreign key
#外键约束：foreign key,通过指定的sql语句描述两张表的关系
#约束：子表添加记录时id必须取自主表
#删除主表记录时，必须没有被子表用着
#怎么确定子表或者主表？子表中有外键的SQL
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
);
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#表明和那张表的那个字段有关系
#foregin key(本表的主键)references 关联表（主键）
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
6.检查约束 check
#检查约束：check给指定字段设置合法值的检查约束
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主键，自动递增
age INT,
CHECK(age>0 AND age<200)#设置检查
);

基本函数

常用的
lower:数据全转小写
upper :数据全转大写
length:数据的长度
substr :截取字符串#substr(1,2,3)包含开始位置(截取字段名-1，开始位置-2，截取长度)
concat:拼接字符串，可以有多个参数
replace:替换，#replace(要替换的字段,原来的字符,新的的字符)
ifnull:判断是否为null，null不参与数据的运算，例如：30000+null=null
30000+ifnull(字段,0)=30000
#ifnull 判断是否为null,null不参与数据的运算
#如果为null替换成0
round：（四舍五入）
ceil：（向上取整）
floor：（向下取整）
#round四舍五入 & ceil向上取整 & floor向下取整
SELECT comm ,ROUND(comm),CEIL(comm),FLOOR(comm)FROM emp;

Now()–获取当前的日期：年月日时分秒
Curdate()–获取年月日
Curtime()–获取时分秒
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW())
,SECOND(NOW());
Year年/month月/day日/hour时/minute分/second秒
Select UUID();

转义字符：\ 例如查询xi’an select ‘xi\’an’;

#练习--函数
#UPPER全转大写 LOWER全转小写
SELECT ename,UPPER(ename),LOWER(ename) FROM emp;
#length求长度(一个字母算一个字符,一个汉字算三个字符)
SELECT ename,LENGTH(ename),LENGTH(job) FROM emp;
#concat(1,2,3)拼接字符串-1是字段名2和3都是要拼的内容
SELECT CONCAT(ename,'123','上班族') FROM emp;
#查部门名称
SELECT dname FROM dept;
#substr(1,2,3)截取字符串-1是字段名2是开始位置(数字段里的个数，从1开始，与下标无关)3是总长度
SELECT dname,SUBSTR(dname,2,2) FROM dept;
#replace(1,2,3)替换-1是字段名2是要被替换的3是新的数据
SELECT dname,REPLACE(dname,'e','888') FROM dept;
#ifnull(1,2)判断是否为null,如果是null就替换成0-1是字段名2是要替换的值
SELECT sal,SUM(sal+IFNULL(comm,0))salary FROM emp  
GROUP BY empno;
#round四舍五入/ceil向上取整/floor向下取整
SELECT comm,ROUND(comm) FROM emp;
SELECT comm,CEIL(comm)FROM emp;
SELECT comm,FLOOR(comm)FROM emp;
#now/CURDATE/CURTIME
SELECT NOW();#年月日时分秒
SELECT CURDATE();#年月日
SELECT CURTIME();#时分秒
#year/month/day/hour/minute/second
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),
MINUTE(NOW()),SECOND(NOW());

条件查询

Distinct:使用distinct关键字，去除重复的记录行
Where:满足条件的才查出来，先过滤，再查询
注意：where不能使用列别名
多条件查询：
Where and:同时满足两个条件
Where or:满足其中一个条件即可
select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename=‘tony’ and deptno=2 --相当于两个条件的&关系

select * from emp where ename=‘tony’ or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
in()：在满足条件里，相当于or
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);
Like:模糊查询
通配符%代表0到n个字符，通配符下划线_代表1个字符
Null：is null：查询是null的，过滤字段值为空的
Is not null：查询不是null的，过滤字段值不为空的
Between and：介于…到…之间 （包含区间值）
Limit:分页查询
select * from emp limit 2 --列出前两条
select * from emp limit 1,2 --从第二条开始,展示2条记录
select * from emp limit 0,3 --从第一条开始,展示3条记录–前三条
#第一个数字是开始位置（从n+1条数据开始）第二条数据是要列出的条数
Order by:排序
SELECT * FROM emp ORDER BY sal;#默认升序（ASC）
SELECT * FROM emp ORDER BY sal DESC;#desc倒序（降序）

#练习--查询
#查询部门地址
SELECT loc FROM dept;
#DISTINCT用来给结果去重distinct
SELECT DISTINCT loc FROM dept;

#where满足条件的才查出来
#查询deptno等于1的部门记录
SELECT * FROM dept WHERE deptno=1;
#查询地址在一区的部门记录\
SELECT * FROM dept WHERE loc='一区';
#查询地址在二区的部门名称
SELECT dname FROM dept WHERE loc='二区';
#根据名称查询部门记录
SELECT * FROM dept WHERE dname='operations';
#根据名称和地址 查询部门记录
SELECT * FROM dept WHERE dname='operations' AND loc='二区';
#WHERE loc='二区' and dname='research2'#并且关系
#或者关系
SELECT * FROM dept WHERE dname='operations' OR loc='二区';

#查询工资>8000的员工信息
SELECT * FROM emp WHERE sal>8000;
#查询工资是8000的或者工资是3000的员工信息
SELECT * FROM emp WHERE sal=8000 OR  sal=3000;
# sal=8000 or sal=3000
 #作用意义同上
SELECT * FROM emp WHERE sal IN(8000,3000);

#like 像,模糊查询,通常配合%作为占位符
#查询名字里包含a的员工信息
SELECT * FROM emp WHERE ename LIKE '%a%';
#ename like '%a%' #包含a,动态匹配0~n个字符
#以l开头,动态匹配0~n个字符

#查询mgr是null的员工信息
SELECT * FROM emp WHERE mgr IS NULL;
#查询sal在3000到10000内的员工信息
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000;
#查询2017年到2019年入职的员工信息
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2017 AND 2019;
#limit分页
SELECT * FROM emp LIMIT 3;#查询前三条数据
SELECT * FROM emp LIMIT 1,3;#查询从第二个数据开始，并且查询3条数据
#order by 排序
SELECT * FROM emp ORDER BY sal;#默认升序(ASC)
SELECT * FROM emp ORDER BY sal DESC;#倒序

聚合函数（5个）

把一列的值全部取出来，聚合起来，分析最大值，最小值，平均值，求和，求个数
常见的聚合函数：max() min() avg() sum() count()
聚合列与非聚合列不能同时使用
SELECT sal,SUM(sal),AVG(sal) FROM emp; 错误
#count()求总个数
SELECT COUNT(comm) FROM emp;#不统计null–低效
SELECT COUNT(*)FROM emp;#都统计–低效
SELECT COUNT(1)FROM emp;#都统计–高效

分组函数

group by:用于对查询结果进行分组统计
having ：分完组之后再进行过滤
什么时候必须分组？
查询的结果中出现聚合列和非聚合列，就必须要分组
按照什么分组合理？
通常按照非聚合列分组
什么是聚合列？使用了聚合函数max min count sum avg

SELECT job,MAX(sal)FROM emp 
WHERE MAX(sal)>8000 #先过滤再分组高效，但是where里不能用聚合函数
GROUP BY job
#HAVING MAX(sal)>8000 
ORDER BY MAX(sal) ;#升序排序

多表连接

业务需求：一张表已经无法满足业务需求，需要两张表或者多张表进行关联

笛卡尔积

方式一：笛卡尔积：必须描述两张表的关联关系，不然会造成冗余

#方式一：笛卡尔积，
SELECT * FROM emp,dept;#查出所有数据
SELECT * FROM emp,dept
#select * from emp,dept WHERE 两张表的关联关系;
#表名.字段名
WHERE dept.deptno=emp.deptno AND dept.dname='accounting';

join

方式二：join
内连接：inner join 两边都满足的交集
左连接：left join 是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。
右连接：right join 是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

#inner join/left join/right join
#工作中，常用的是小表 left join 大表，小表驱动大表
#inner join(内连接)#两边都满足的交集
SELECT * FROM emp e
INNER JOIN dept d #两个表的关联
ON e.deptno=d.deptno#描述字段
WHERE d.dname='accounting';
#left join(左连接)左边的所有和右边满足了的
SELECT * FROM emp e
LEFT JOIN dept d #两个表的关联
ON e.deptno=d.deptno#描述字段
WHERE d.dname='accounting';
#right join（右连接）右边的所有和左边满足了的
SELECT * FROM emp e
RIGHT JOIN dept d #两个表的关联
ON e.deptno=d.deptno#描述字段
WHERE d.dname='accounting';

子查询

方式三：子查询
将查询的结果作为条件，即当一个查询是另一个查询的条件时，称之为子查询。
可以允许多层嵌套查询

#练习1:查询李军的总得分
#笛卡尔积
SELECT SUM(degree) FROM scores s1,students s2
WHERE s1.sno=s2.`sno`
AND s2.sname='李军';
#join
SELECT SUM(degree)FROM scores s1
LEFT JOIN students s2
ON s1.sno=s2.sno
WHERE s2.sname='李军';
#子查询
SELECT SUM(degree) FROM scores s1 WHERE sno=(
SELECT sno FROM students WHERE sname='李军'
);
#练习2:查询易天负责的课程名称
#笛卡尔积
SELECT cname FROM courses c,teachers t
WHERE c.tno=t.tno
AND t.tname='易天';
#join
SELECT cname FROM courses c
LEFT JOIN teachers t
ON c.tno=t.tno 
WHERE t.tname='易天';
#子查询
SELECT cname FROM courses c WHERE tno=(
SELECT tno FROM teachers t
WHERE t.tname='易天'
);

事务（Transaction）

1.概述

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。
保证SQL语句要么全执行成功，要么全执行失败

2.事务的特性

有四个特性ACID
A是原子性：是指多条SQL是一个院子，密不可分，如果正确，就操作了数据；如果有错误，就会发生回滚，回到事务执行之前，不会结束在中间的某个环节
C是一致性：保证了数据的一致性和完整性
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
I是隔离性：保证多线程并发时的数据安全，多个操作之间是被隔离的
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
D是持久性：是指数据CDU的影响是持久生效的
持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3.事务的隔离级别

隔离级别:读未提交 读已提交 可重复读 串行化
从前往后,性能越来越差,安全性越来越高.MySQL默认是可重复读

读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
读提交（read committed） Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

MySQL的隔离级别说明

在默认情况下，MySQL每执行一条sql语句，都是一个单独的事务。如果需要在一个事务中包含多条sql语句，那就需要手动开启事务和结束事务

开启事务：start transaction；
结束事务：commit（提交事务）或rollback（回滚事务）
在执行sql语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以执行多条sql语句，最后要结束事务，commit表示提交，即事务中的多条sql所做出的影响会持久到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前所做的所有操作都被撤销了！

索引（index）

作用

好处：
索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率
为了提高数据库的查询效率，可以使用索引
那么给那些字段设计索引呢？查询需要量比较大，字段值比较大的时候。。
坏处：索引本身也是一张表，表里的数据和真正的表里的数据是重复的，浪费了空间
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

定义

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查询算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的 形式存放在磁盘中。目前大多数索引都采用BTree树方式构建

分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

索引的操作

查看索引

#查看索引，主键会自动创建索引
#show index from 表名;
show index from dept;

创建索引

#创建单值索引
#create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引
#创建唯一索引
#alter table 表名 add unique(字段);
alter table emp add unique(ename);
#创建复合索引
#alter table 表名 add index 索引名(字段1，字段2）;
Alter table emp add index many_index(ename,job,hiredate);

删除索引

#alter table 表名 drop index 索引名;
Alter table emp drop index many_index;

使用索引

explain#用来观察SQL的执行计划，主要看有没有用索引
#（观察两列结果：key和possible）
SELECT * FROM emp WHERE ename=‘leo’;#背后会用ename的索引

视图（view）

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
简单的说，视图就是由一个或多个表组成的虚拟表

视图的作用

• 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全性。通过视图用户只能查询和修改他们所能见到的数据。但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上：
  使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。
• 逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。

创建视图

Create view 视图名 as 查询语句

使用视图

SELECT * FROM emp_view;
#直接查的视图，视图就是一张特殊的表，最好用于查询

SQL优化

1.尽量使用字段名替换*
2.做表设计时，字段的类型最好是varchar代替char
3.字段里的值尽量数字代替字符串
4.尽量把过滤条件精细，能用and不用or
5.索引的设计，最多5个，不能太多
6.模糊查询，尽量要确定开始元素，让索引生效
7.查询尽量避免返回大量数据
8.避免在where子句中使用！=或<>操作符
9.去重distract过滤字段要少
10.where中使用默认值代替null
11.不要有超过5个以上的表连接
12.多表连接时尽量是小表驱动大表
13.尽量使用union all替代union
14.删除冗余和重复的索引