查询语句

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
)
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 159,716评论 4赞 364
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,558评论 1赞 294
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,431评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,127评论 0赞 209
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,511评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,692评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,915评论 2赞 313
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,664评论 0赞 202
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,412评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,616评论 2赞 245
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,105评论 1赞 260
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,424评论 2赞 254
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,098评论 3赞 238
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,096评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,869评论 0赞 197
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,748评论 2赞 276
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,641评论 2赞 271

推荐阅读更多精彩内容

  • SQL之常用的查询语句
    首先需要注意的是：查询语句select再怎么操作都不会改变表中的数据！！ 1、查询语句的语法格式 select 列...
    jimmywife阅读 2,081评论 0赞 4
  • mysql 子查询
    一、子查询定义 定义： 子查询允许把一个查询嵌套在另一个查询当中。 子查询，又叫内部查询，相对于内部查询，包含内部...
    我是强强阅读 3,074评论 0赞 4
  • SQL查询语句之子查询1
    【子查询】 1、 子查询的各个使用形式。 2、 分析子查询的主要意义。 实际上，对于整个的SQL语法而言，所有的组...
    发觉原来我只是250阅读 659评论 0赞 0
  • 周钧亲子日记第5篇
    今天所有工作基本步入正轨，但也非常忙碌。送完学生后，回到教室一看，周钧作业已经写完，自己缩在角落里看书。看着他那舒...
    涓涓_c81f阅读 207评论 0赞 3
  • 这里有一首让你狂舞又喜极而泣的歌
    我梦想着，有一天，要准备这样一个房间： 不用很大，20㎡左右，一眼望去，一方纯净的世界，本白的窗纱环绕四周，从天花...
    曼诗阅读 773评论 0赞 12