查询语句

SELECT：查询一个／多个／全部字段，也可以在列上做运算或链接，列别名和表别名。查询分为条件查询、排序查询、分组查询、子查询。

一、基本语法

1.基本SELECT语法：

SELECT 字段(s) 
FROM 表名

2.查询一个字段

SELECT sname
FROM student

3.查询多个字段(字段间用逗号隔开)

SELECT sname,age
FROM student

4.查询所有列(*号代替查询字段)

SELECT *
FROM student

5.列上做四则运算

SELECT sname age*12
FROM student

注意：如果是加法运算，有空值时，查询结果也为空

6.连接(||)

SELECT sname||'_常用名',age
FROM student

7.SQL语句规则
SEL语言不区分大小写；一条语句可以写在一行或多行；关键字不能缩写和分行；每句间建议分行；习惯使用缩进以提高可读性

二、条件查询

1.使用WGHRER关键字可以将不满足条件的数据过滤掉

SELECT 字段(s) 
FROM 表名
WHERE 条件

如：

SELECT sname,age
FROM student 
WHERE age = 20

2.字符和日期

字符和日期要用''；字符区分大小写，日期格式敏感(默认格式DD-MON-RR)

SELECT sname,age
FROM student 
WHERE sname = 'TESTME'

SELECT sname,age
FROM student
WHERE birthday BETWEEN TO DATE('1990-01-01','YYYY-MM-DD') AND TO DATE('1999-12-31')

3.比较运算符

操作符	解释
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

SELECT sname,age
FROM student
WHERE age >=18

4.条件查询

操作符	解释
BETWEEN AND	两个值中间
IN	包含与
LIKE	用于模糊查询
NULL	空

5.BETWEEN AND

SELECT sname,age
FROM student
WHERE birthday BETWEEN TO DATE('1990-01-01','YYYY-MM-DD') AND TO DATE('1999-12-31')

6.IN(多个条件)

SELECT sname,age
FROM student
WHERE age IN (5,10,15,20)

7.LIKE (模糊查询，多用于处理字符串)

% 表示0个或多个字符， _ 表示只是匹配一个

SELECT snaem,age
FROM student
WHRER sname LIKE '刘%'      //查询班级里所有刘姓学生

SELECT sname,age
FROM student
WHERE sname LIKE '_明%' //查询第二个字为明的学生

8.NULL

使用NULL来判断空值(IS NULL 或则 IS NOT NULL)

SELECT sname,age
FROM student 
WHERE age IS NULL

9.逻辑运算

操作符	解释
AND	逻辑并
OR	逻辑或
NOT	逻辑否

SELECT sname,age
FROM student 
WHERE age >= 18 AND sname = '刘%'

SELECT sname,age
FROM student 
WHERE age >= 18 OR sname = '刘%'

SELECT sname,age
FROM student 
WHERE age NOT IN (15,30)

10.排序查询（放在最后做，对数据没有侵略性）

使用ORDER BY 进行排序，默认是升序（ASC-升序；DESC降序）

SELECT sname,age
FROM student 
ORDER BY birthday

SELECT sname,age
FROM student 
ORDER BY birthday DESC //降序

SELECT sname studentname,age
FROM student 
ORDER BY studentname //按别名排序

SELECT sanme,age
FROM student
ORDER BY sId,dirthday DESC //多个排序条件(优先级从左至右)

11.分组查询

分组函数是对表中一组记录进行操作，每组值返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组可能是整个表分为一个组，也可能根据条件分成多组。

分组函数常用到的函数：AVG、COUNT、MAX、MIN、SUM

语法：

SELECT [column,] group_function(column) ...
FROM TABLE
WHERE [condition]
[GROUP BY column]
[HAVING group_function(column) expression]
[ORDER BY column | group_function(column) expression]

SELECT AVG(score),MAX(score),MIN(score),SUM(score)
FROM score
WHERE scourseid = 1  //对数值型数据使用AVG、SUM

SELECT MIN(birthday),MAX(birthday)
FROM student //对任意数据类型使用MIN、MAX

SELECT COUNT(*) //返回总数
FROM student
WHERE age = 18

SELECT COUNT(birthday) //返回birthday不为空的总数
FROM student
WHERE age = 18

SELECT COUNT(DISTINCT birthday) //DISTINCT返回birthday不为空且不重复的总数
FROM student
WHERE age = 18

注意事项：如果指定了 SELECT DISTINCT，那么 ORDER BY 子句中的项就必须出现在选择列表中，否则会出现错误。
比如SQL语句：SELECT DISTINCT Company FROM Orders order by Company ASC是可以正常执行的。
但是如果SQL语句是：SELECT DISTINCT Company FROM Orders order by Num ASC是不能正确执行的，在ASP中会提示“ORDER BY 子句与 (Num) DISTINCT 冲突”错误。
SQL语句修改成：SELECT DISTINCT Company,Num FROM Orders order by Num ASC可以正常执行。

SELECT AVG(NVL(score,0))
FROM student
WHERE sId = 10

NVL函数说明：

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以就有了NVL2函数。

拓展：NVL2函数:Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为NULL，则返回E2。

注意事项：E1和E2必须为同一数据类型，除非显式的使用TO_CHAR函数。

例1：NVL(TO_CHAR(numeric_column), 'some string') 其中numeric_column代指某个数字类型的值。

例2：NVL(yanlei777,0) > 0
NVL(yanlei777, 0) 的意思是 如果 yanlei777 是NULL， 则取 0值

GROUP BY：把表中的数据分成若干组，GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。
它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

如下表student:

sId	sname	age
1	刘	20
1	王	30
2	张	30
2	黄	40
3	李	10
3	催	20

SELECT sId,SUM(age) as ages
FROM student
GROUP BY sId

执行以上代码得到数据为：

sId	ages
1	50
2	70
3	30

SELECT sId,SUM(age) as ages
FROM student
GROUP BY sId
ORDER BY SUM(age) DESC

执行以上代码得到数据为：

sId	ages
2	70
1	50
3	30

SELECT sId,SUM(age) as ages,score
FROM student
GROUP BY sId
ORDER BY SUM(age) DESC

注：以上代码执行错误，原因是在SELECT指定的字段要么就要包含GROUP BY语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

注意：

a.因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。

b.包含在GROUP BY子句中的列不必包含在SEELECT列表中

c.不能在WHERE中使用聚合函数，但可以在HAVING子句中使用聚合函数

过滤分组：HAVING子句

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

SELECT sId,SUM(age) as ages
FROM student
GROUP BY sId
HAVING SUM(age) >= 60

执行以上代码得到数据

sId	ages
2	70

SELECT sId,SUM(age) as ages
FROM student
GROUP BY sId
HAVING SUM(age) >= 50
ORDER BY SUM(age) DESC

执行以上代码得到数据

sId	ages
2	70
1	50

12.子查询

子查询：允许把一个查询嵌套在另一个查询中，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

子查询可以包含普通SELECT可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或者do。

子查询位置：SELCECT 中、FRIOM 后、HWHERE 中，GROUP BY 和ORDER BY 中无实用意义。

分类：

a.标量子查询：返回单一值的标量，最简单的形式。

b.列子查询：返回的结果集是 N 行一列。

c.行子查询：返回的结果集是一行 N 列。

d.表子查询：返回的结果集是 N 行 N 列。

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询。

如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。

a.标量子查询：

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧。

SELECT * 
FROM table1
WHERE column1 = (
    SELECT MAX(column2)
    FROM table2
)

SELECT * 
FROM article 
WHERE uid = (
    SELECT uid 
    FROM user
    WHERE status=1 
    ORDER BY uid DESC 
    LIMIT 1
)

b.列子查询

列子查询是子查询返回的结果集是 N行一列，该结果通常来自对表的某个字段查询返回。

可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

可以使用 IN、ANY、SOME 和 ALL 操作符，但不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

SELECT sname
FROM tab1
WHERE sId IN(
    SELECT uid 
    FROM user
    WHERE sex='男'
)

SELECT s1
FROM table1 WHERE s1 > ANY (
    SELECT s2
    FROM table2
)

SELECT s1 
FROM table1
WHERE s1 > ALL (
    SELECT s2 
    FROM table2
)

特殊情况：如果 table2 为空表，则 ALL 后的结果为 TRUE；如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。

注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 
FROM table1 
WHERE s1 > (
    SELECT s2 
    FROM table2
)

SELECT s1 
FROM table1 
WHERE s1 > ALL (
    SELECT MAX(s1) 
    FROM table2
)

c.行子查询

行子查询:子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

SELECT * 
FROM table1 
WHERE (1,2) = (
    SELECT column1, column2 
    FROM table2
)

SELECT *
FROM article
WHERE (title,content,uid) = (
    SELECT title,content,uid
    FROM blog 
    WHERE bid=2
)

d.表子查询

表子查询:子查询返回的结果集是 N 行 N 列的一个表数据。

SELECT * 
FROM article
WHERE (title,content,uid) IN (
    SELECT title,content,uid
    FROM blog
)

ANY进行子查询

ANY的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。

如“6 > ANY(15, 3, 25, 30)”，由于6>3，所以，该该判断会返回TRUE；只要6与集合中的任意一个进行比较，得到TRUE时，就会返回TRUE。

select table1.customer_id,city,count(order_id)
from table1 join table2
on table1.customer_id=table2.customer_id
where table1.customer_id<>'tx' and table1.customer_id<>'9you'
group by customer_id
having count(order_id) >
any (
select count(order_id)
from table2
where customer_id='tx' or customer_id='9you'
group by customer_id);

使用IN进行子查询

使用IN进行子查询，这在日常写sql的时候经常遇到。IN的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。

IN是ANY的别名，在使用ANY的地方，我们都可以使用“IN来进行替换。有了IN，对应的就有NOT IN；NOT IN和<>ANY意思不同，和<>ALL的意思一样。

使用ALL进行子查询

ALL必须与比较操作符一起使用。ALL的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。

如“10 >ALL(2, 4, 5,)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >ALL(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。

<>ALL的同义词是NOT IN，表示不等于集合中的所有值

使用SOME进行子查询

SOME是ANY的别名，用的比较少。

独立子查询

独立子查询是指不依赖外部查询而运行的子查询。

//查询男生的班级id
SELECT class_id
FROM table2
WHERE student_id 
IN(
    SELECT student_id
    FROM table1
    WHERE sex='男'
);

将子查询单独拿出来也是可以单独执行，即子查询与外部查询没有任何关系。

SELECT *
FROM table1
WHERE sex='男' AND EXISTS
(
    SELECT *
    FROM table2
    WHERE table1.student_id = table2.student_id
);

将子查询单独拿出来就无法单独执行了，由于子查询依赖外部查询的某些字段，这就导致子查询就依赖外部查询，就产生了相关性。

对于子查询的效率问题。当执行SELECT语句时，可以加上EXPLAIN关键字，用来查看查询类型，查询时使用的索引以及其它其他信息。

//查询男生的班级id
EXPLAIN SELECT class_id
FROM table2
WHERE student_id 
IN(
    SELECT student_id
    FROM table1
    WHERE sex='男'
);

说明：如果子查询部分对集合的最大遍历次数为n，外部查询的最大遍历次数为m时，使用独立子查询，它的遍历 次数记为：O(m+n)。而如果使用相关子查询，它的遍历 次数可能会达到O(m+m*n)。可以看到，效率就会成倍的下降；所以，大伙在使用子查询时，一定要考虑到子查询的相关性。

EXISTS谓词

EXISTS是一个非常牛叉的谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其 它谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN，对于EXISTS来说，UNKNOWN就是FALSE。

例：获得城市为hangzhou，并且存在订单的用户。

select *
from table1
where city='hangzhou' and exists
                (select *
                from table2
                where table1.customer_id=table2.customer_id);

关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外， 还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样，SQL优化 器会选择相同的执行计划。

说到了IN和EXISTS几乎是一样的，但是，就不得不说到NOT IN和NOT EXISTS，对于输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN就会得到NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。

子查询的优化

很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语 句很灵活，但子查询的执行效率不高。

子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在MySQL中可以使用连接查 询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

使用连接(JOIN)来代替子查询：

SELECT *
FROM t1
WHERE t1.a1 NOT IN (SELECT a2 FROM t2 )

优化：

SELECT *
FROM t1
LEFT JOIN t2 ON t1.a1=t2.a2
WHERE t2.a2 IS NULL

SELECT *
FROM article WHERE (title,content,uid) IN (
    SELECT title,content,uid
    FROM blog
)

优化：

SELECT * 
FROM article
INNER JOIN BLOG NO (
    article.title=blog.title 
    AND
    article.content=blog.content 
    AND
    article.uid=blog.uid
)