终极庆庆SQL练习

终极庆庆SQL练习 

col，select

 

select * from表 where 条件  order by ASC升序/DESC降序 　　limit   1, 2. 从第一个开始 取两个数

SELECT col,col,col 找什么？

FROM table 从哪找？

WHERE col 条件 条件是啥？

条件：数字(where)

当查找条件col是数字

select * from table where col = 1;

Operator	Condition	SQL Example	解释
=, !=, < ,<=, >, >=	Standard numerical operators	col != 4	等于 大于 小于
BETWEEN … AND …	Number is within range of two values (inclusive)	col BETWEEN 1.5 AND 10.5	在 X 和 X之间
NOT BETWEEN … AND …	Number is not within range of two values (inclusive)	co NOT BETWEEN 1 AND10	不在 X 和 X之间
IN (…)	Number exists in a list	col IN (2, 4, 6)	在 X 集合
NOT IN (…)	Number does not exist in a list	col NOT IN (1, 3, 5)	不在 X 集合

条件：文本(where)

当查找条件col是文本

select * from table where col like '%jin';

Operator	Condition	SQL Example	解释
=	Case sensitive exact string comparison (notice the single equals)	col = "abc"	等于
!= or <>	Case sensitive exact string inequality comparison	col != "abcd"	不等于
LIKE	Case insensitive exact string comparison	col LIKE "ABC"	等于
NOT LIKE	Case insensitive exact string inequality comparison	col NOT LIKE "ABCD"	不等于
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)	col LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS")	模糊匹配
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)	col LIKE "AN_" (matches "AND", but not "AN")	模糊匹配单字符
IN (…)	String exists in a list	col IN ("A", "B", "C")	在集合
NOT IN (…)	String does not exist in a list	co NOT IN ("D", "E", "F")	不在集合

排序(rows)

需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2

Operator	Condition	SQL Example	解释
ORDER BY	.	ORDER BY col ASC/DESC	按col排序
ASC	.	ORDER BY col ASC/DESC	升序
DESC	.	ORDER BY col ASC/DESC	降序
LIMIT OFFSET	.	LIMIT num_limit OFFSET num_offset	从offset取limit
ORDER BY	.	ORDER BY col1 ASC,col2 DESC	多列排序

join:连表(table)

当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1

Operator	Condition	SQL Example	解释
JOIN .. ON ..	.	t1 JOIN t2 ON t1.id = t2.id	按ID连成1个表
INNER JOIN	.	t1 INNER JOIN t2 ON t1.id = t2.id	只保留id相等的row
LEFT JOIN	.	t1 LEFT JOIN t2 ON t1.id = t2.id	保留t1的所有row
RIGHT JOIN	.	t1 RIGHT JOIN t2 ON t1.id = t2.id	保留t2的所有row
IS/IS NOT NULL	.	col IS/IS NOT NULL	col是不是为null

算式(select / where)

当需要对select的col 或 where条件的col 经过一定计算后才能使用
select *,col*2 from table where col/2 > 1

Operator	Condition	SQL Example	解释
+ - * / %	.	col1 + col2	col加减乘除
substr	.	substr(col,0,4)	字符串截取
AS	.	col * 2 AS col_new	col取别名
...			还有很多

统计（select）

对查找的rows需要按col分组统计的情况
select count(*),avg(col),col from table where col > 1 group by col

Operator	Condition	SQL Example	解释
COUNT(*), COUNT(column)	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.	count(col)	计数
MIN(column)	Finds the smallest numerical value in the specified column for all rows in the group.	min(col)	最小
MAX(column)	Finds the largest numerical value in the specified column for all rows in the group.	max(col)	最大
AVG(column)	Finds the average numerical value in the specified column for all rows in the group.	avg(col)	平均
SUM(column)	Finds the sum of all numerical values in the specified column for the rows in the group.	sum(col)	求和
GROUP BY	.	group by col,col2	分组
HAVING	.	HAVING col>100	分组后条件

子表 (table)

一次select的结果rows作为下一次select的临时table才能得到最终结果
select * from (select * from table where col > 1) as tmp where col < 1

Operator	Condition	SQL Example	解释
（select -）as tmp		（select -）as tmp	select结果做子表
in（select -）		in（select -）	select结果做条件
avg（select -）		avg（select -）	select结果做条件

 

 

DISTINCT 语法介绍，我们拿之前的 Movies表来说，可能很多电影都是同一年Year发布的，如果你想要按年份排重，一年只能出现一部电影到结果中， 你可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回。写作：DISTINCT Year

选取出唯一的结果的语法

SELECT DISTINCT column, another_column, … FROM mytable WHERE condition(s);

因为 DISTINCT 语法会直接删除重复的行, 我们还会学习 GROUP BY 语句， GROUP BY 也会返回唯一的行，不过可以对具有相同的 属性值的行做一些统计计算，比如：求和.

结果排序 （Ordering results）

在实际的数据表中，数据添加的时候不是完全顺序的，比如我们实际的Dog表，不会是按狗的身高从小到大去添加数据，当数据量成千上万之后，如果结果不做任何排序，结果会看起来很错乱.

为了解决结果排序问题, 我们可以用 ORDER BY col_name 排序的语法来让结果按一个或多个属性列做排序.

结果排序（ordered results）

SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC;

ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序，对数字来说就是升序 1，2，3，... 或降序 ... 3,2,1 . 对于文本列，升序和降序指的是按文本的字母序。

通过Limit选取部分结果

LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。你可以想象一下从一条长绳子剪下一小段的过程，我们通过 OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度。

limited查询

SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;

你可以想象一下一个新闻网站的新闻条目数据，他们在页面上是按热度和时间排序的，每一个页面只显示10条数据，在所有这些属性都是不断变化的情况下。我们可以想见通过SQL的ORDER LIMIT OFFSET 句法，我们可以根据要求从数据库筛选出需要的新闻条目.

 

使⽤用动态 SQL 可简化代码的开发，减少开发者的⼯工作量量，程序可以⾃自动根据业务参数来决定 SQL 的组

成

动态SQL;

if 标签可以⾃自动根据表达式的结果来决定是否将对应的语句句添加到 SQL 中，如果条件不不成⽴立则不不添加， 如果条件成⽴立则添加。 

where 标签可以⾃自动判断是否要删除语句句块中的 and 关键字，如果检测到 where 直接跟 and 拼接，则 ⾃自动删除 and，通常情况下 if 和 where 结合起来使⽤用。 

trim 标签中的 prefix 和 suffix 属性会被⽤用于⽣生成实际的 SQL 语句句，会和标签内部的语句句进⾏行行拼接，如 果语句句前后出现了了 prefixOverrides 或者 suffixOverrides 属性中指定的值，MyBatis 框架会⾃自动将其删 除。 

set 标签
set 标签⽤用于 update 操作，会⾃自动根据参数选择⽣生成 SQL 语句句。 

foreach 标签可以迭代⽣生成⼀一系列列值，这个标签主要⽤用于 SQL 的 in 语句句。 

 

数据库范式(normalization)

数据库范式是数据表设计的规范，在范式规范下，数据库里每个表存储的重复数据降到最少（这有助于数据的一致性维护），同时在数据库范式下，表和表之间不再有很强的数据耦合，可以独立的增长 (ie. 比如汽车引擎的增长和汽车的增长是完全独立的). 范式带来了很多好处，但随着数据表的分离，意味着我们要查询多个数据属性时，需要更复杂的SQL语句，也就是本节开始介绍的多表连接技术。这样SQL的性能也会面临更多的挑战，特别是当大数据量的表很多的情况下.

如果一个实体（比如Dog）的属性数据被分散到多个数据表中，我们就需要学习如何通过 JOIN连表技术来整合这些数据并找到我们想要查询的数据项.

用JOINs进行多表联合查询

主键(primary key), 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复复（想象你的身份证号码)。一个最常见的主键就是auto-incrementing integer(自增ID，每写入一行数据ID+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同 主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。具体我们用到 JOIN 关键字。我们先来学习 INNER JOIN.

用INNER JOIN 连接表的语法

SELECT column, another_table_column, … FROM mytable （主表） **INNER JOIN another_table （要连接的表）    ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)** WHERE *condition(s)* ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。此时，你可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）. 还有一个理解INNER JOIN的方式，就是把 INNER JOIN 想成两个集合的交集。

常见统计函数

下面介绍几个常用统计函数:

Function	Description
COUNT(*), COUNT(*column*)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(*column*)	找column最小的一行.
MAX(*column*)	找column最大的一行.
AVG(*column*)	对column所有行取平均值.
SUM(*column*)	对column所有行求和.
Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

分组统计

GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了. GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数.

用分组的方式统计

SELECT AGG_FUNC(*column_or_expression*) AS aggregate_description, … FROM mytable WHERE *constraint_expression* **GROUP BY column**;

到目前为止，我们的Query查询已经有点复杂了，不过还好我们已经基本把查询语法介绍完了。在 GROUP BY 分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出>100万的年份？）

一个不常用的语法 HAVING 语法将用来解决这个问题，他可以对分组之后的数据再做SELECT筛选.

用HAVING进行筛选

SELECT group_by_column, AGG_FUNC(*column_expression*) AS aggregate_result_alias, … FROM mytable WHERE *condition* GROUP BY column **HAVING \*group_condition\***;

HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉 HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .

小贴士?

如果你不用GROUP BY语法, 简单的WHERE就够用了.

 

完整的SELECT查询

SELECT DISTINCT column, AGG_FUNC(*column_or_expression*), … 
FROM mytable    JOIN another_table      ON mytable.column = another_table.column  
WHERE *constraint_expression* 
GROUP BY column   
HAVING *constraint_expression*   
ORDER BY *column* ASC/DESC   
LIMIT *count* OFFSET *COUNT*;

1. FROM 和 JOINs

FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

2. WHERE

我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式

3. GROUP BY

如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

4. HAVING

如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

5. SELECT

确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.

6. DISTINCT

如果数据行有重复DISTINCT 将负责排重.

7. ORDER BY

在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.

8. LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

结论

不是每一个SQL语句都要用到所有的句法，但灵活运用以上的句法组合和深刻理解SQL执行原理将能在SQL层面更好的解决数据问题，而不用把问题 都抛给程序逻辑.

转自SQL自学网