SQL必知必会 | 2

一、使用DDL创建数据库&数据表时需要注意什么？

CREATE DROP AlTER 增删改

1.对数据库进行定义
CREATE DATABASE nba;//创建一个名为nba的数据库
DROP DATABASE nba;//删除一个名为nba的数据库

2.对数据表进行定义
CREATE TABLE [table_name](字段名 数据类型， …)

CREATE TABLE player(
player_id int(11) NOT NULL AUTO_INCREMENT,
player_name varchar(255) NOT NULL
);
11和255是显示长度，与类型包含的数值范围大小无关。

我们通常很少自己写 DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。在这里我推荐使用 Navicat，它是一个数据库管理和设计工具，跨平台，支持很多种数据库管理软件，比如 MySQL、Oracle、MariaDB 等。基本上专栏讲到的数据库软件都可以使用 Navicat 来管理。

1.player表添加age字段
ALTER TABLE player ADD (age int(11));

2.修改player表的age字段名为player_age
ALTER TABLE player RENAME COLUMN age to player_age;

3.修改字段的数据类型
ALTER TABLE player MODIFY (player_age float(3,1));//作者的报错？
ALTER TABLE player MODIFY COLUMN player_age float(3,1);//改写为这个？

4.删除字段
ALTER TABLE player DROP COLUMN player_age;

数据表的常见约束：
1.主键约束
主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。
2.外键约束
外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。
3.唯一性约束
唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如我们在 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。
4.NOT NULL约束
对字段定义了NOT NULL，即表明该字段不应为空，必须有取值。
5.DEFAULT
表明了字段的默认值。如果在插入数据的时候，这个字段没有取值，就设置为默认值。
6.CHECK约束
用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE。

1.数据表的个数越少越好
RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship
Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。
2.数据表中的字段个数越少越好
字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。
3.数据表中联合主键的字段个数越少越好
设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。
4.使用主键和外键越多越好
数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

二、检索数据：你还在SELECT * 么？

1.SELECT查询的基础语法
查询列：
SELECT 列名 FROM 表名
查询多列：
SELECT 列名1, 列名2, 列名3 FROM 表名
查询所有列：
SELECT * FROM 表名

给列名、表名起别名，简化原有名称

对heros数据表中的英雄名进行查询，同时增加一列字段platform，这个字段固定值为“王者荣耀”：
SELECT ‘王者荣耀’ as platform, name FROM heros
ps:如果常数是个字符串或者字母，需要添加单引号；如果是数字，则不需要单引号。否则SQL会把王者荣耀当做列名来查询，但是实际上数据表没有这个列名，就会引起错误。

从查询结果中去除重复行：
SELECT DISTINCT 列名 FROM 表名
ps:多个列名去重时，只会把满足多列名的行去掉

2.如何排序检索数据
多列排序：
SELECT name, hp_max FROM heros ORDER BY hp_max DESC

ps:1.ORDER BY后可以有一个或多个列名，多列名排序时，会按照第一个列先排序，当第一列的值相同时，再按照第二列进行排序，以此类推。
2.ORDER BY后的ASC代表递增排序，DESC代表递减排序。
3.ORDER BY可以使用非选择列排序，即使在SELECT后没有这个列名，你可以放到ORDER BY后面进行排序。
4.ORDER BY通常位于SELECT语句的最后一条子句，否则会报错。

显示英雄名称和最大生命值，第一排序最大法力从低到高，当最大法力值相等的时候按照第二排序进行，即最大生命值从高到低的方式进行排序：
SELECT name, hp_max FROM heros ORDER BY mp_max, hp_max DESC

约束返回结果的数量：
1.MySQL、PostgreSQL、MariaDB、SQLite的关键字：LIMIT，且需要放到SELECT语句的最后面
SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

2.SQL Server、Access的关键字：TOP
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

3.DB2的关键字：FETCH FIRST 5 ROW ONLY
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROW ONLY

4.Oracle的关键字：ROWNUM
SELECT name, hp_max FROM heros WHERE ROWNUM <= 5 ORDER BY hp_max DESC
ps:这条语句会先取出来前5条数据行，然后再按照hp_max从高到低的顺序进行排序。这样产生的结果和上述方法的并不一样：先排序再取5行。

SELECT查询时的两个顺序：
1.关键字的顺序不能颠倒
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY
2.SELECT语句的执行顺序(在MySQL和Oracle中，SELECT执行顺序基本相同)
FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

1、FROM子句组装数据
2、WHERE子句进行条件筛选
3、GROUP BY分组
4、使用聚集函数进行计算；
5、HAVING筛选分组；
6、计算所有的表达式；
7、SELECT 的字段；
8、ORDER BY排序
9、LIMIT筛选

3.什么情况下用SELECT *，如何提升SELECT查询效率？
如果只是练习，或者对数据表进行探索，是可以使用SELECT *的。但是在生产环境下，不推荐直接使用SELCT *进行查询。

三、数据过滤：SQL数据过滤都有哪些方法？

提升查询效率的方式：1）约束返回结果的数量；2）指定筛选条件进行过滤。

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

比较运算符：

含义	运算符
等于	=
不等于	<>或!=
小于	<
小于等于(不大于)	<=或!>
大于	>
大于等于(不小于)	>=或!<
不小于	!<
在指定两个数值之间	BETWEEN
为空值	IS NULL

注意：需要查看使用的DBMS是否支持这些运算符，不同的DBMS支持的运算符可能是不同的。

WHERE子句的基本格式：
SELECT…(列名) FROM…(表名) WHERE…(子句条件)

查询所有最大生命值大于6000的英雄：
SELECT name, hp_max FROM heros WHERE hp_max > 6000

查询所有最大生命值在5399和6811之间的影英雄：
SELECT name, hp_max FROM hero WHERE hp_max BETWEEN 5399 AND 6811

对heros表中的hp_max字段进行空值检查：
SELECT name, hp_max FROM heros WHERE hp_max IS NULL

逻辑运算符：

含义	运算符
并且	AND
或者	OR
在指定条件范围内	IN
非	NOT

筛选最大生命值大于6000，最大法力值大于1700的英雄，然后按照最大生命值和最大法力值之和从高到低排序：
SELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700

查询主要定位或者次要定位是法师或是射手的英雄，同时英雄的上线时间不在2016-01-01到2017-01-01之间：
SELECT name, role_main, role_assist, hp_max, mp_max, birthdata
FROM heros
WHERE (role_main IN (‘法师’, ‘射手’) OR role_assist IN (‘法师’, ‘射手’))
AND DATE(birthdate) NOT BETWEEN ‘2016-01-01’ AND ‘2017-01-01’
ORDER BY (hp_max + mp_max) DESC

如果我们要检索文本中包含某个词的所有数据，就需要使用通配符。
查找英雄名中包含“太”字的英雄：
SELECT name FROM heros WHERE name LIKE '%太%'

注意：不同DBMS对通配符的定义不同。有些DBMS和配置可能还要考虑区分大小写。

查询除了第一个字以外，包含‘太’字的英雄：
SELECT name FROM heros WHERE name LIKE '_%太%'

ps：实际操作中，尽量少用通配符，它需要消耗数据库更长的时间来进行匹配。即使你对LIKE检索的字段进行了索引，索引的价值也可能失效。