【SQL】基础知识整理

---

实际开发中，出现在代码中的sql包括：insert ,delete, update ,select
增删改查有一个术语：CRUD操作
即：create retrieve (检索)update delete

在MySQL当中，凡是标识符是可以使用飘号 ` 括起来的，但最好不要用，不通用。
MySQL默认使用的存储引擎是InnoDB方式–可以自己设定
存储引擎这个名字只有在MySQL中存在（Oracle中有对应的机制，不叫存储引擎，没有特殊的名字，就是“表的存储方式”）
默认采用的字符集是UTF8

---

基于wamp–cmd操作–（以下mysql命令，不是sql语言）
1.从c盘切换到d盘–d:
2.输入路径–cd: D:\wamp64\bin\mysql\mysql5.7.14\bin
3.mysql -u账户 -p密码，为防止密码隐私，可以直接mysql -u账户 -p
4.成功后，界面是：

5.展示所有数据库–show databases;
6.创建数据库–create database 数据库名；
7.使用数据库–use 数据库名字；
8.查看当前使用数据库中有哪些表–show tables;
9*（可选）.初始化数据库数据–source sql脚本的路径;
10.删除数据库–drop database 数据库名;
11.展示数据库中的表–show tables;
12.查表的结构–desc 表名；

13.查看当前使用的是哪个数据库–select database();
14.查看mysql的版本号–select version()；
15.结束一条语句–\G --不过用；也可以
16.退出mysql–exit
17.查看创建表的语句–show create table 表名；
18.查看引擎–show engines
19.查看sql语句的执行计划，可以看到查看了几条(rows)等等–explain sql语句

---

//两种写法一样
SELECT * FROM `alarm_all` WHERE `alarm_id`='2' OR `alarm_id`='4'

SELECT * FROM `alarm_all` WHERE `alarm_id` IN('2','4')

//_表示任意一个字符，%表示多个字符
SELECT * FROM `alarm_all` WHERE `machine_id`LIKE '_3%'

//如果是收_是否含有时候，用\_
SELECT * FROM `alarm_all` WHERE `alarm_level`LIKE'%\_%'

//默认Order by 升序
//asc表示升序，desc表示降序
//排序优先级自左向右，如比较data5,只有data5都相同的时候才会进行deal_num
SELECT * FROM `test_sht` WHERE 1 ORDER BY`data5`ASC,`deal_num`DESC

select  3
from  1
where   2
order by   4
SELECT `data5` AS nihao FROM `test_sht` WHERE 1 ORDER BY nihao
//即在select中执行了as，在order by可以使用别名，当然原本的名字也可以使用
//别名使用过程中可以不用as
//注意别名不要加引号，否则没有效果

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注：所有分组函数都是对某"某一列"数据进行操作的，且自动忽略null,包括--等
但是count（*）则不考虑null，直接统计记录总条数
SELECT SUM(`data5`) FROM `test_sht` WHERE 1

数据库中进行运算时候，只要有null参与的运算结果一定为null,需要采用ifnull(可能为null的列名，设定的数字)
如SELECT `data5`+2 FROM `test_sht` WHERE 1
SELECT ifnull(`data5`,0)+2 FROM `test_sht` WHERE 1

分组函数不可以直接使用在where中，因为group by是在where执行之后才会执行，而分组函数是在分组才进行执行，现在还没有分组，所以无法在where中执行

group by :按照某个字段或者某些字段进行分组
having：having是对分组之后的数据进行再次过滤
注意：分组函数一般都会跟group by联合使用，也是为什么称之为分组函数的原因，
并且任何一个分组函数都是在group by语句执行完之后才执行；
当一条sql语句没有group by的话，整张表的数据都会自成一组；
当一条语句中有group by 的话，select后面只能跟分组函数和参与分组的字段，如自动生成的id编码无法出现在select中，否则会报错；
如果有多个group by条件，可以直接用逗号隔开，不存在优先级；
having是过滤，可以在里面增加分组函数，但要写的话必须有group by
select     5
from       1
where       2
group by    3
having      4
order by    6

在进行一行数据进行结构设置时候，如果设置可以空，且默认NULL的话
在where时候进行判断，无法通过=进行，需要用is null,或者is not null
SELECT * FROM `test_sht` WHERE `data5`IS NOT null

SELECT DISTINCT `data5` FROM `test_sht` WHERE 1
可以去掉重复出现的数据，但是要注意distinct 只能出现在所有字段的最前面，如果后面有2个或者多个字段，则表示对满足所有字段的整体字段进行去重；
distinct的最前面可以跟分组函数进行同步使用
SELECT COUNT(DISTINCT `data5`) FROM `test_sht` WHERE 1

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

//如：给表加别名，方便where判断，且可读性强
SELECT
  b.`model_id`,a.id
FROM
  `test_sht` a ,`test_sht1` b
  where
  a.id=b.id

sql92(太老)：
SELECT
  b.`model_id`,
  a.id
FROM
  `test_sht` a,
  `test_sht1` b
WHERE
  a.id = 5 AND a.id = b.id


sql99(常用)：
//inner可以省略，带着可读性好一点
SELECT
  b.`model_id`,
  a.id
FROM
  `test_sht` a
inner JOIN
  `test_sht1` b
ON
  a.id = b.id
WHERE
  a.id = 5
//内容一样，只是99语法更加清晰，join on内容是对于表的处理，where是进行接下来的过滤
//结果：2列，1列model_id，1列id，但注意如果2张表筛选后得到记录条数不同，则会出现重复的情况

内连接之非等值连接：最大特点：条件是非等量关系，比如将上面on后面的内容加个between a and b ，则是非等值连接

内连接：假设a和b表进行连接，使用内连接的话，凡是a表和b表能够匹配上的记录查询出来，这就是内连接，如果没有匹配的，则不显示。ab两张表没主副之分，两张表平等

外连接：假设a和b表进行连接，使用外连接的话，ab两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表的数据进行匹配上，副表自动模拟出null与之匹配。
外连接分类？
左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表
外连接最重要的特点是：
主表的数据无条件的全部查询出来

//right join则表示右连接，left join左连接
//outer可以省略
SELECT
  b.`model_id`,
  a.id
FROM
  `test_sht` a
RIGHT outer JOIN
  `test_sht1` b
ON
  a.id = b.id

//a表先和b连接，连接后得出结果，a表在和c表连接
SELECT
  b.`model_id`,
  a.id,
  c.id
FROM
  `test_sht` a
JOIN
  `test_sht1` b
ON
  a.id = b.id
RIGHT JOIN
  `test_sht2` c
ON
  a.id = c.id
  where
  a.id=3

//注意：
//union是将结果进行拼接到同一列，
//其次，2个查询的列数必须相同，即前面查1列，后面也查1列
SELECT id FROM test_sht UNION SELECT id FROM test_sht1

select     5
from       1
where       2
group by    3
having      4
order by    6
limit     7

SELECT id FROM test_sht LIMIT 1,1

建表语句的语法格式
create table 表名(
字段名1  数据类型，
字段名2  数据类型，
字段名3  数据类型，
。。。
)

CREATE TABLE test_sht3( id int, num int )
//default 当数据inset时候如果为空，则id列默认为1
CREATE TABLE test_sht3(id INT DEFAULT 1, num INT)
16.1加非空约束
CREATE TABLE test_sht10(id INT NOT null DEFAULT 1, num INT)
16.2加唯一约束
  //一个unique
	CREATE TABLE test_sht10(id INT UNIQUE , num INT)
	此时如果
	INSERT INTO `test_sht10`(`id`, `num`) VALUES(10, 10)
	INSERT INTO `test_sht10`(`id`, `num`) VALUES(10, 10)
	会报错，因为id具有唯一性
  //多个unique
	//单个字段一个个添加约束unique，称为：列级约束
	CREATE TABLE test_sht10(id int UNIQUE,num int UNIQUE)
	此时是增加2个unique约束，id或者num任何一个出现重复就会报错
	//多个字段联合起来添加1个约束unique，称为：表级约束
	CREATE TABLE test_sht10 (id int ,num int ,UNIQUE(id,num))
	此时是增加1个unique约束，在id和num同时出现重复时候才会报错，只是一个重复不会报错
16.3加主键约束
CREATE TABLE test_sht10 (id int PRIMARY key ,num int )//列级约束
CREATE TABLE test_sht11(id INT , num INT,PRIMARY KEY(id))//表级约束
INSERT INTO `test_sht10`(`num`) VALUES(9)//报错
此时如果插入数据,如果id没有插入数据，或者id是null的话，会报错

主键相关术语：
以
CREATE TABLE test_sht10 (id int PRIMARY key ,num int )
INSERT INTO `test_sht10`(`id`, `num`) VALUES(1, 9)为例
主键约束--primary key
主键字段--id
主键值--1

主键的作用？
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键，且一张表的主键约束只有一个。
主键的作用是：主键值是这行记录在这张表当中的唯一标识（就像每个人用不同身份证号来区别人一样）

主键的分类？
根据主键字段的字段数量来划分：
	单一主键（推荐使用，常用的）
	复合主键（多个字段联合起来添加的一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）如：CREATE TABLE test_sht11(id INT , num INT,PRIMARY KEY(id,num))  则只有一个主键，需要id和num都不可以重复，且都不可以null,如果其中有个重复是可以的
根据主键性质来划分：
	自然主键：主键值最好就是一个和业务没有任何关系的自然数
	业务主键：主键值和系统的业务挂钩，例如：拿银行卡的卡号做主键，拿着身份证号码作为主键					（不推荐用），最好不要拿着和业务挂钩的字段最为主键，因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复

mysql提供主键值自增：（非常重要）
CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int )//id字段自动维护一个自增的数字，从1开始，以1递增
提示：Oracle当中也提供了一个自增机制，叫做：序列（sequence）
16.4加外键约束
关于外键约束的相关术语 ：
	外键约束：foreign key
	外键字段：添加有外键约束的字段
	外键值：外键字段中的每一个值
	CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int );
	CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(id))
	首先创建test_sht10--父表，其次创建test_sht11--字表，然后添加数据也是先添加父表的，如果子表中good的值不在父表中id的值，则会报错。
	注意：外键使用2张表存储引擎都要是INNODB，默认的MyISAM不支持外键,但MyISAM引擎速度比INNODB快。因此：
	CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int )ENGINE INNODB;
	CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(id))ENGINE INNODB

外键值可以是null?
	可以为null
	
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
		不一定必须是主键，但至少具有unique约束,即：
		CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int UNIQUE )ENGINE INNODB;
	CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(num))ENGINE INNODB
16.5
数据类型常用：
bigint 长整型
int 整数型
float 浮点型
char 定长字符串--实际开发中，当某个字段中的数据长度不发生变化时候，是定长的，比如：性别、生日等，都采用char
varchar 可变字长字符串--当一个字段的数据长度不确定，如：简介、姓名都采用varchar
data  日期类型
blob  二进制大对象（存储图片，视频等流媒体信息）
clob   字符大对象（存储较大文本，比如，可以存储4g的字符串）

约束
	非空约束（not null）:约束字段不能为null
	唯一约束（unique）:约束字段不能重复，但可以为null
	主键约束（primary key）：约束的字段既不能为null，也不能重复（简称pk）
	外键约束（foreign key）:(简称fk)
	检查约束（check）：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持

DROP TABLE IF EXISTS test_sht3

语法格式：
insert into 表名(字段1，字段2，。。。) values (值1，值2，。。。)

要求:字段的数量和值的数量相同，并且数据类型要对应相同

如果
insert into 表名 values (值1，值2，。。。)
则要求值和字段一一对应，且值的数量和字段的数量相同

一次插入多行数据
INSERT INTO `test_sht3`(`id`, `num`) VALUES(1,2),(3,3)

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

//将test_sh3的id，num列全部复制到test_sht4中，且创建表test_sh4
CREATE TABLE test_sht4 as SELECT id,num FROM test_sht3

//将test_sh4中id列数据插入到test_sht5中id列
INSERT INTO `test_sht5`(`id`) SELECT id FROM `test_sht4`

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

UPDATE `test_sht1` SET `id` = 7, `model_id` = 55 WHERE `id`=1

语法格式：
delete from 表名 where 条件//delete删除数据可恢复，可回滚，但如果数据大的话比较慢

DELETE FROM `test_sht1` WHERE `id`=2

怎么删除大表？
TRUNCATE TABLE test_sht5//表被截断，不可回滚，永久丢失，需要谨慎

---

InnoDB:
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
表的结构存储在***.frm文件中
数据存储在tablespace这样的的表空间中（逻辑概念），无法被压缩，无法转换为只读
在MySQL服务器崩溃后提供自动恢复的功能
支持级联删除和级联更新

---

MEMORY：
缺点：不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快
以前叫做HEPA引擎

为什么？因为他们这三个语句都是和数据库表当中的“数据”相关的，事务的存在是为了保存数据的完整性、安全性。

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

事务的特性？
事务包括四大特性：
原子性：事务是最小的工作单元，不可再分
一致性：事务必须保证多条DML语句同时成功或者同时失败
隔离性：事务和事务之间是隔离的
持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束

关于事务之间的隔离性？
事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别：读未提交（read uncommitted）–演示添加链接描述
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据；
读未提交存在脏读（Dirty Read）现象：表示读到了脏数据，即数据不稳定，一断电就没有了。

第二级别：读已提交（read committed）–演示添加链接描述
对方事务提交之后的数据我方可以读取到；
读已提交存在的问题是：不可重复读，即对方一提交就读到新的，无法做到数据一直是相同的。
但解决了：脏读现象。

第三级别：可重复读（repeatable read）–演示：添加链接描述
这种隔离级别解决了：不可重复读的问题。
但存在的问题是：读取到的数据是幻象的，只要对方提交过，即使对方已经删除或者增加，读取到的数据依旧还是原数据，类似于读取备份数据。

第四级别：序列化读/串行化读（serializable）–演示：添加链接描述
解决了所有的问题；
效率低，需要事务排队

oracle数据库默认的隔离级别是：读已提交
mysql数据库默认的隔离级别是：可重复读

mysql事务默认情况下是自动提交的
自动提交？即只要执行任意一条DML语句则提交一次。
如何关闭自动提交？用 start transaction

mysql数据库操作流程：
表示当正常执行DML时候，会自动提交，此时如果回滚，即输入 rollback ，没有任何用处，表中数据还是一样的，因为数据已经自动提交到了内存里面；
当输入start transaction后，此时会关闭自动提交，事务机制开启，假设此时的数据是a,在执行DML后，这时候如果输出，数据是DML后的数据b，如果此时回滚，即输入rollback，然后输出，数据为a，且事务机制结束。因为数据还没有提交内存里面去；
当输入start transaction后，此时会关闭自动提交，事务机制开启，假设此时的数据是a,在执行DML后，这时候如果输出，数据是DML后的数据b，如果此时commit，即数据提交，此时输出数据，则会是b。如果此时回滚，即输入rollback，则输出的数据为b，因为数据已经提交了，而回滚只能回滚到上一次的数据提交点。

索引为什么可以提高检索效率？
其实最根本的原理是缩小了扫描的范围
索引虽然可以提高检索效率，但是不可以随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的。比如，表中的数据经常被修改这样就不合适添加索引，因为数据一旦修改，索引需要重新排序，进行维护
添加索引是给某一个字段，或者说某些字段添加索引

SELECT `id`, `data5`, `deal_num` FROM `test_sht` WHERE `data5`=5

当data5字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描data5字段中所有的值，而当添加索引的时候，以上sql语句会根据索引扫描，快速定位

什么时候考虑给字段添加索引？（满足什么条件）
数据量庞大（根据客户的需求，满足线上的环境）
该字段很少的DML操作（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中（经常根据哪个字段查询）

注意：主键和具有unique约束的字段会自动添加索引，所以根据主键查询效率较高，尽量根据主键检索。

增加索引：

create index 索引名字 on 需要添加索引的表名（添加索引的字段名）
create index emp_sal_index on emp(sal)

删除索引对象

drop index 索引名字 on 需要添加索引的表名
drop index emp_sal_index on emp

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

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

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

索引什么时候失效？
模糊查询的时候，第一个通配符使用的是

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

create view 视图名 as DQL语句，即select语句
drop view 视图名

对视图进行增删改查，会影响到原表数据，（通过视图影响原表数据的，不是直接操作的原表），可以对视图进行CRUD操作，但是创建视图必须用DQL语句，即select语句

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

将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）
mysqldump 数据库名> 输出到这个sql脚本文件的路径，其实就是保存路径+取名(假设为D:\a.sql) -u用户名 -p密码

在windows的dos命令窗口中执行：（导出指定数据库中的指定表）
mysqldump 数据库名 表名> 输出到这个sql脚本文件的路径，其实就是保存路径+取名(假设为D:\a.sql) -u用户名 -p密码

导入数据
1.create database 数据库名
2.use 数据库名
3.source 脚本文件（D

三范式内容：
第一范式：
任何一张表都应该有主键，并且每一个字段原子性不可再分
如：

图中没有主键，其次联系方式可以再分，因此不符合第一范式
修改后：

---

注：原子性：指事务的不可分割性，一个事务的所有操作要么不间断地全部被执行，要么一个也没有执行。

---

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

因此不满足第二范式，当然最好不要用复合主键。解决方案：

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

不满足第三范式，解决办法：

---

一对多，多对多表格设计在上面。
一对一表格设计，2种方案:
外键唯一、主键共享
外键唯一：

主键共享：

---

完–hello world
视频参考：
添加链接描述