SQL

2020-02-29

#sql

主键

作用是用来唯一标识一行。可以用一列，也可以用多列，总之是要唯一。

select

不带from子句的select语句：可用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

在写的时候通常是从select开始写，但是程序自己在处理的时候是先从from开始读

通配符： %表示任何字符出现任意次数,_用途和百分号一样，只是只匹配单个字符，而不是多个。[]匹配括号中的单个字符，这个有点像正则表达式里那个方括号，比如[jm]%匹配任何j或者m开头的字符串。

联结 join：用联结而不要用子查询的方式，因为大多数DBMS处理联结要更快

几个join的区别：

left join：左边表的行要全部返回
right join: 右边表的行要全部返回

上面说的全部返回是指，两个表有不匹配on条件的行的情况，两种join才会有区别，而如果两个表的每一条都能匹配到on条件，那么两个join的结果是没有区别的
left join 的基础上还有一种，返回左表的全部且除开两个表满足on条件的那部分，只要在后面加上where table.key is null即可，如：
```
select * from user LEFT JOIN career on user.id = career.user_id where career.user_id IS NULL;
```
right join也同理。
inner join：只返回两个表有匹配到on条件的行

full outer join:

mysql不支持这种语法，可以将left join和right join结合一起，并使用union来去重，如：

SELECT * FROM user LEFT JOIN career on user.id = career.user_id UNION SELECT * FROM user RIGHT JOIN career on user.id = career.user_id;e

返回两表的独有：

在上面full outer join的基础上修改

SELECT * FROM user LEFT JOIN career on user.id = career.user_id WHERE career.user_id IS NULL UNION SELECT * FROM user RIGHT JOIN career on user.id = career.user_id WHERE user.id IS NULL;

组合查询 union：多数情况下，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。

分页：数据量很大的时候，需要分页显示。通过LIMIT OFFSET 子句实现。

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

表示先按score倒序查询，然后分3页，显示第1页。因为结果集索引值从0开始，因此0就代表第一条数据，也就是会显示第一页，如果总共有10条数据，分3页要显示第二页的话，offset就是3，因为第二页的第一条数据的索引值是3。有对应的公式：offset= pageSize * (pageIndex - 1) 。如果offset超过最大值，不会报错，会返回一个空的结果。

聚合查询：sql提供了专门的一些聚合函数进行统计总数，平均值之类的计算，这就叫聚合查询。比如count函数。

SELECT AVG(score) average FROM students WHERE gender = 'M';求所有男生的平均成绩

分组查询：比如统计一班的人数可以用`select count(*) from students where class_id=1，那统计其他的班的人数岂不每次都要改一下class_id？这个时候就可以用分组。

select count(*) from students group by class_id

而如果要统计各班男女个数，这个时候，首先要分班，然后还有分性别，然后统计每组数量。

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

索引（index）

是帮助MySql高效获取数据的数据结构。索引的本质是数据结构。“排好序的快速查找数据结构”。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。如果没有特别申明，一般都是指的B Tree，多路搜索树（并不一定是二叉的）。

通常来说数据不会真的删除首先一个是为了数据的完整性，好做数据分析。还有一个就是为了索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优势

提高数据检索的效率，降低数据库的IO成本

通过索引对数据进行排序，降低数据的排序成本，降低了CPU的消耗
劣势

索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的

虽然索引大大提高了查询速度，同时却会降低更新的速度（insert，update，delete）因为更新表时，不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段

索引只是提高效率的一个因素，如果数据量太大，就需要花时间研究建立最优秀的索引

B树

非叶子节点不存储真实数据，只存储指引搜索方向的数据项。比如最上面的节点，P1表示小于17的，P2表示大于17小于35的，P3表示大于35的。而注意，这里的17和35并不真实存在于数据表中。

对于这样树，原则上是尽量扩展宽度，而不要加深深度，因为越深就表示磁盘的IO越多。

真实情况是，3层的B树，可以表示上百万的数据，如果上百万的数据查找只需要3次IO，性能提高将是巨大的。

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
where条件里用不到的字段不创建索引

哪些情况不要建索引

数据太少。通常只有在上百万的时候才考虑索引
修改频繁的表
数据重复且平均的表字段，建索引效果不大

有一个公式，比如一个字段有10万条数据，但是只有true和false两个值，且每个值的分布概率大约为50%，那么这种建索引的意义就不大。如果2000条数据，里面有1900多个值，就可以考虑建索引

视图

即虚拟表。虚拟表只包含动态检索数据的查询，他本身并不包含数据。一些好处：

重用SQL语句
简化复杂的SQL操作
使用表的一部分而不是整个表
保护数据，可以授予用户访问表的特定部分，而不是整个表的访问权限
视图可以返回和地层表的格式不同的数据要特别注意性能问题，因为不包含数据，所以每次使用都要进行所有的检索。如果有多个联结创建了复杂的视图或者嵌套了视图，性能可能会下降的很厉害。
```
CREATE VIEW some_table_name AS SELECT name, age from student
```
然后就可以some_table_name当作一个表来使用了

存储过程

编写存储过程比一般的SQL语句要难，因此很多管理员会限制编写存储过程，作为一个安全措施。

M1安装docker mysql

官方的那个不支持m1，下面这个可以。保险起见，选了5.7

docker pull mysql/mysql-server:5.7

docker run --name=mysql1 -p 3306:3306 -d mysql/mysql-server:5.7

run执行后，会需要一段时间启动，可以通过docker ps -a来查看状态，如果显示healthy就表示OK了。

第一次会自动生成密码，然后可以查看这个密码：

 docker logs mysql1 2>&1 | grep GENERATED

用上述查看到到密码进入：

docker exec -it mysql1 mysql -uroot -p

进入后马上修改密码:

ALTER USER 'root'@'localhost' IDENTIFIED BY ‘your_password’;

host的客户端可能连不上，出现not allow之类的，然后执行了下面这两个就解决了：

CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
grant all on *.* to 'root'@'%';