SQL学习笔记

• SQL 语言按照功能划分成以下的 4 个部分：

• DDL，英文叫做 Data Definition Language 数据定义语言
  它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。

• DML，英文叫做 Data Manipulation Language，数据操作语言
  比如增加、删除、修改数据表中的记录

• DCL，英文叫做 Data Control Language，数据控制语言
  我们用它来定义访问权限和安全级别

• DQL，英文叫做 Data Query Language，数据查询语言，
  我们用它查询想要的记录，它是 SQL 语言的重中之重

关系数据库管理软件RDBMS

MariaDB----MySQL的作者开发的免费版本。

Oracle与MySql 执行语句方式： 语法检查—>语义检查---->解析器—>优化器—>执行

• 了解 DDL 的基础语法，它如何定义数据库和数据表；
• 使用 DDL 定义数据表时，都有哪些约束性；
• 使用 DDL 设计数据库时，都有哪些重要原则。

在执行create，drop, alter 语句不需要commit 就可以完成执行

修改表结构
ALTER TABLE player ADD (age int(11));

ALTER TABLE player RENAME COLUMN age to player_age

ALTER TABLE player MODIFY (player_age float(3,1));

ALTER TABLE player DROP COLUMN player_age;

唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如我们在 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。

• 需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束

• 1.数据表的个数越少越好
  RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

• 2.数据表中的字段个数越少越好
  字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

• 3.数据表中联合主键的字段个数越少越好
  设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

• 4.使用主键和外键越多越好
  数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

• SELECT 查询的基础语法；

• 如何排序检索数据；

• 什么情况下用SELECT*，如何提升 SELECT 查询效率？

起别名 as

SQL：SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros;

• 查询常数
  SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。你可能会问为什么我们还要对常数进行查询呢？SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。比如说，我们想对 heros 数据表中的英雄名进行查询，同时增加一列字段platform，这个字段固定值为“王者荣耀”，可以这样写：

SQL：SELECT ‘王者荣耀’ as platform, name FROM heros

单引号说明引号中的字符串是个常数，否则 SQL 会把王者荣耀当成列名进行查询，但实际上数据表里没有这个列名，就会引起错误。如果常数是英文字母，比如’WZRY’也需要加引号。如果常数是个数字，就可以直接写数字，不需要单引号，比如：

SQL：SELECT 123 as platform, name FROM heros

SQL：SELECT DISTINCT attack_range FROM heros

• 1. DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错。
• 2. DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 69 条，因为这 69 个英雄名称不同，都有攻击范围（attack_range）这个属性值。如果你想要看都有哪些不同的攻击范围（attack_range），只需要写DISTINCT attack_range即可，后面不需要再加其他的列名了。

如何排序检索数据

当我们检索数据的时候，有时候需要按照某种顺序进行结果的返回，比如我们想要查询所有的英雄，按照最大生命从高到底的顺序进行排列，就需要使用 ORDER BY 子句。
使用 ORDER BY 子句有以下几个点需要掌握：

1. 排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
2. 排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。如果没有注明排序规则，默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。
3. 非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序。
4. ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。
5. 在了解了 ORDER BY 的使用语法之后，我们来看下如何对 heros 数据表进行排序。假设我们想要显示英雄名称及最大生命值，按照最大生命值从高到低的方式进行排序：

SQL：SELECT name, hp_max FROM heros ORDER BY hp_max DESC

返回检索记录 limit ，top, oracle 中使用RowNum

SELECT 的执行顺序查询是 RDBMS 中最频繁的操作。我们在理解 SELECT 语法的时候，还需要了解 SELECT 执行时的底层原理。只有这样，才能让我们对 SQL 有更深刻的认识。其中你需要记住 SELECT 查询时的两个顺序：1. 关键字的顺序是不能颠倒的：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY .

执行顺序

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

考虑到在实际的工作中，我们往往不需要全部的列名，因此你需要养成良好的习惯，写出所需的列名

• 学会使用 WHERE 子句，如何使用比较运算符对字段的数值进行比较筛选；
• 如何使用逻辑运算符，进行多条件的过滤；
• 学会使用通配符对数据条件进行复杂过滤。

当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符。

你能看出来通配符还是很有用的，尤其是在进行字符串匹配的时候。不过在实际操作过程中，我还是建议你尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配。即使你对 LIKE 检索的字段进行了索引，索引的价值也可能会失效。如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用LIKE '%太%'或LIKE '%太’的时候就会对全表进行扫描。如果使用LIKE ‘太%’，同时检索的字段进行了索引的时候，则不会进行全表扫描。

算术函数
字符串函数
日期函数
转换函数

SELECT CONCAT(‘abc’, 123)，运行结果为 abc123。
SELECT LENGTH(‘你好’)，运行结果为 6。
SELECT CHAR_LENGTH(‘你好’)，运行结果为 2。
SELECT LOWER(‘ABC’)，运行结果为 abc。
SELECT UPPER(‘abc’)，运行结果 ABC。
SELECT REPLACE(‘fabcd’, ‘abc’, 123)，运行结果为 f123d。
SELECT SUBSTRING(‘fabcd’, 1,3)，运行结果为 fab。

SELECT CURRENT_DATE()，运行结果为 2019-04-03。
SELECT CURRENT_TIME()，运行结果为 21:26:34。
SELECT CURRENT_TIMESTAMP()，运行结果为 2019-04-03 21:26:34。
SELECT EXTRACT(YEAR FROM ‘2019-04-03’)，运行结果为 2019。
SELECT DATE(‘2019-04-01 12:00:05’)，运行结果为 2019-04-01。

这里需要注意的是，DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较，我会在后面的例子中讲具体的原因。

SELECT CAST(123.123 AS INT)，运行结果会报错。
SELECT CAST(123.123 AS DECIMAL(8,2))，运行结果为 123.12。SELECT COALESCE(null,1,2)，运行结果为 1。
CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

这里有一个有关命名规范的建议：
关键字和函数名称全部大写；
数据库名、表名、字段名称全部小写；
SQL 语句必须以分号结尾。

聚集函数都有哪些，能否在一条 SELECT 语句中使用多个聚集函数；
如何对数据进行分组，并进行聚集统计；
如何使用 HAVING 过滤分组，HAVING 和 WHERE 的区别是什么。

SQL：SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000

，这时COUNT(role_assist)会忽略值为 NULL 的数据行，而 COUNT(*) 只是统计数据行数，不管某个字段是否为 NULL

需要说明的是 AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。比如

SQL：SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

需要说明的是，我们需要先把 name 字段统一转化为 gbk 类型，使用CONVERT(name USING gbk)，然后再使用 MIN 和 MAX 取最小值和最大值。

SELECT COUNT(*), role_assist FROM heros GROUP BY role_assist

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC

当我们创建出很多分组的时候，有时候就需要对分组进行过滤。你可能首先会想到 WHERE 子句，实际上过滤分组我们使用的是 HAVING。HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。

SQL: SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

• 子查询可以分为关联子查询和非关联子查询。我会举一个 NBA 数据库查询的例子，告诉你什么是关联子查询，什么是非关联子查询；
• 子查询中有一些关键词，可以方便我们对子查询的结果进行比较。比如存在性检测子查询，也就是 EXISTS 子查询，以及集合比较子查询，其中集合比较子查询关键词有 IN、SOME、 ANY 和 ALL，这些关键词在子查询中的作用是什么；
• 子查询也可以作为主查询的列，我们如何使用子查询作为计算字段出现在 SELECT 查询中呢？

子查询虽然是一种嵌套查询的形式，不过我们依然可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询。

数据表字段,最好做好字段说明文档,以便日后维护

Oracle中使用Date类型

1. select *from tb where date>‘2020-12-16’; 这种写法时错误的。进行日期比较要使用Date函数，不可以将Date 与字符串进行比较
2. 日期比较与between 连用时，注意，只包含左边边界值，不包含右边边界值。
   例如：
   select *from tb where btime>=TO_DATE(‘2020-07-22’,‘yyyy-mm-dd’) and btime<=to_date(‘2020-10-01’,‘yyyy-mm-dd’);—包含左边界值
   也即是 btime>=2020-07-22 and btime<2020-10-01