简单sql语法(下)
分组统计
- 如果需要获取表中一部分行的统计值需要使用
GROUP BY
子句,GROUP BY
子句在生成结果集时生成多个分类汇总 - 如果使用了
GROUP BY
子句进行分组查询,SELECT
查询的列必须包含在GROUP BY
子句中或者包含在聚合函数中 - 执行含有
GROUP BY
子句的SELECT
查询的步骤:
(1)数据库系统首先执行FROM
子句
(2)如果在SELECT
查询中存在WHERE
子句,那么根据其中的条件,从结果集中筛选出比较结果为FALSE
的行
(3)根据GROUP BY
子句指定的分组字段将结果集进行分组
(4)最后根据SELECT
子句的值为每组生成查询结果中的一行 GROUP BY
子句可以根据多列进行分组且数目没有上限,唯一的限制是组合列必须是查询数据表中的列- 如果在
GROUP BY
子句中使用的字段的别名会出现错误因为执行GROUP BY
子句之前还没有执行SELECT
子句,为了解决这个问题可以使用子查询 - 在
GROUP BY
子句中使用表达式时,用到的字段必须是相同数据类型的,否则会出现错误 GROUP BY
子句中可以使用ROLLUP
关键字对字段的几种可能性进行分组,使用ROLLUP(A,B,C)
系统首先对A、B、C进行分组操作,然后对A和B进行分组操作,之后对A进行分组操作,最后对全表进行分组操作,在SQL Server中还能使用WITH ROLLUP
关键字CUBE
关键字与ROLLUP
关键字作用相同,在SQL Server中还能使用WITH CUBE
关键字WHERE
子句和HAVING
子句主要区别如下:
(1)WHERE
不能放在GROUP BY
后面,而HAVING
可以
(2)HAVING
是与GROUP BY
连在一起使用的,放在GROUP BY
后面,此时的作用相当于WHERE
子句
(3)WHERE
后面的条件里不能有聚合函数,而HAVING
子句可以
总的来说,WHERE
子句在数据分组前进行过滤,而HAVING
子句在数据分组后进行过滤
简单子查询
- 子查询的语法与普通的
SELECT
查询的语法相同,子查询可以包含联合、WHERE
子句、HAVING
子句和GROUP BY
子句 - 子查询的语法规则如下:
(1)子查询的SELECT
必须使用圆括号括起来
(2)不能包括COMPUTE或FOR BROWSE子句
(3)如果同时指定TOP
子句,则可能只包括ORDER BY
子句
(4)子查询最多可以嵌套32层
(5)任何可以使用表达式的地方都可以使用子查询,只要它返回的是单个值
(6)如果某个表只出现在子查询中而不出现在外部查询中,那么该表中的列就无法包含在输出中 - 子查询与其他
SELECT
语句之间的区别:
(1)子查询不仅可以使用子查询FROM
子句中的表,还可以使用子查询FROM
子句中表的任何列
(2)子查询必须返回单一数据列
(3)子查询不能有ORDER BY
子句
(4)子查询必须由一个SELECT
语句组成,不能将多个SQL语句
用UNION
组合起来作为一个子查询 - 聚合函数都返回单个值,在子查询中应用聚合函数,并将该函数返回的结果应用到
WHERE
子句的查询条件中
多行子查询
IN子查询
是指在外层查询和子查询之间用IN
连接,判断某个属性列是否在子查询的结果中,其返回的结果中可以包含零个或者多个值,要是想达成相反的效果可以使用NOT IN子查询
EXISTS子查询
的功能是判断子查询的返回结果中是否有数据行,如果子查询返回的结果是空集则EXISTS
失败,NOT EXISTS
成功- 一些带
EXISTS
或者EXISTS
的子查询不能被其他形式的子查询等价替换,但所有带IN、ANY、ALL
和比较运算符的子查询都能用带EXISTS
的子查询等价替换 SOME、ANY、ALL
是量词,允许将比较运算符左边的单值与生成单列但多行结果集的子查询相比较。
多表连接
- 内连接就是使用比较运算符进行表与表之间列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,内连接可以用来组合两个或者多个表中的数据
- 根据使用的比较方式不同,可以将内连接分为三类:
(1)等值连接:在连接条件中使用等于运算符比较被连接的列
(2)不等值连接:在连接条件中使用除了等于运算符以外的其他比较运算符比较被连接的列
(3)自然连接:它是等值连接的一种特殊情况,用来把目标中重复的属性列去掉 - 等值连接的两种语法分别为:
SELECT fieldlist FROM table1,table2 WHERE table1.column=table2.column 或 SELECT fieldlist FROM table1 [INNER] JOIN table2 ON table1.column=table2.column
- 自然连接只有在两个表中有相同名称的列且列的含义相似时才能使用。
- 在使用中如果为表指定了别名,那么在该SQL语句中对该表的所有显示引用都必须使用别名,而不能使用表名。如果连接中的多个表中有相同名称的列存在,要求必须使用表名或别名来限制列名(表名.列名)。
- 如果需要显示表中的所有记录,包括那么不符合连接条件的记录,此时需要使用外连接,使用外连接可以方便地在连接结果中包含某个表中的其他记录,外连接的查询结果是内连接查询结果的扩展
- 外连接以指定的数据表为主体,将主体表中不符合连接条件的数据也一并输出,可以分为三种:
(1)左外连接(LEFT JOIN
):表示在结果中包含左表中不满足条件的数据
(2)右外连接(RIGHT JOIN
):表示在结果中包含右表中不满足条件的数据
(3)全外连接(FULL JOIN
):表示在结果中包括左表和右表中不满足条件的数据 - SQLite数据库支持左外连接,但不支持右外连接,左外连接和右外连接唯一的区别就是所关联表的顺序,通过调整
FROM
子句中表的顺序,就可以使左外连接转换为右外连接 - MySQL、Access和SQLite数据库不支持全外连接
- 自连接是指同一个表同自身进行连接,同一个表在
FROM
子句中被列出两次,为了区别必须给每个表提供一个别名来区别这两个副本 - 交叉连接是两个表的笛卡尔乘积的另一个名称,交叉连接会将第一个表的每一行与第二个表的每一行相匹配,交叉连接中的列是原表中的列的数量的总和,行是原表中的行数的积,通过
CROSS JOIN
关键字来完成操作,并且忽略ON
条件 - 组合查询通过
UNION
操作符来完成,可以执行多个SELECT
查询语句并将多个查询的结果作为一个查询结果集返回,使用UNION
操作符需要注意以下几点:
(1)使用UNION
操作符必须由两个或两个以上的SELECT
语句组成,语句之间用UNION
关键字分隔
(2)要求每个SELECT
语句中列的数目必须相同,而且对应位置上的列的数据类型必须相同或者兼容
(3)最后结果集中的列名是由第一个SELECT
语句中的列名决定的 - 使用
UNION
进行组合查询时可以对一个表进行多个查询,也可以对不同的表进行多个查询,不过只能使用一个ORDER BY
子句并且得放在最后一个SELECT
语句之后,所使用的排序列名必须是第一个SELECT
语句中的列名 UNION
会从最后的结果集中自动去除重复的行,如果希望返回重复的行可以使用UNION ALL
而不是UNION
插入数据
- 使用
INSERT
语句可以向表中插入数据,语句如下:INSERT [INTO] table_or_view [(column_list)] Values (value_list)
- 如果采用省略列名列表的方式,则必须为每一列提供一个值,各列必须以它们在表中出现的次序进行填充,如果某一列没有值且该列允许为NULL值,则可以使用NULL值对该列进行填充,如果在创建数据表时通过
DEFAULT
关键字为列定义默认值,这时如果输入时没有提供这一列数据就会用默认值填充 - 不管使用哪种
INSERT
语法,VALUES
值列表中的数目都必须正确,如果不提供列名,则必须给表中的每一个列提供一个值,如果提供列名,则必须给列出的每个列提供一个值,否则就会产生错误信息 - 使用
INSERT
语句可以向表中一次插入多行数据,只要将值列表用逗号分隔开,INSERT
语句还可以将SELECT
语句的查询结果插入到数据表中,此时VALUES
子句指定的是一个SELECT
查询的结果集,语法格式如下:INSERT [INTO] table_name SELECT {*|fieldname1[,fieldname1...]} FROM table_source [WHERE search_condition]
此时需要table_source与table_name两个表的结构完全一致,但不要求列名匹配
- 在SQL Server中可以使用
SELECT INTO
语句将已存在的数据表中的信息复制到所要创建的数据表中,INSERT SELECT
可以将数据添加到一个已经存在的表中,而SELECT INTO
则可以将数据复制到一个新表中,语法格式如下:SELECT [select_list] INTO new_table FROM table_name WHERE search_condition
不管从多少个表中查询数据,数据只能插入到一个表中
- 在MySQL和Oracle中,将已存在的数据表中的数据复制到所要创建的数据表中使用的是
CREATE TABLE SELECT
语句,语法格式如下:CREATE TABLE new_table AS SELECT [select_list] FROM table_name WHERE search_condition
更新和删除数据
UPDATE
语句用来修改表中的数据,使用UPDATE
语句的基本语法如下:UPDATE table_name SET column1=value1,column2=value2,... WHERE search_condition
- 虽然
UPDATE
语句只允许改变单个表中的列值,但是在UPDATE
语句的WHERE
子句中可以使用任何可用的表,因此可根据其他表中的相关值来决定目标表中要更新的数据行 DELETE
语句用来删除表中的数据,语法格式如下:DELETE [FROM] {table_name|view_name} [WHERE search_condition]
DELETE
语句可以删除表中的指定行或所有行,但是DELETE
语句并不会删除表本身TRUNCATE TABLE
语句也可以用来删除表中的所有行,等同于不带WHERE
子句得DELETE
语句,如果要删除表中所有行建议使用TRUNCATE TABLE
语句因为效率更高,语法格式如下:TRUNCATE TABLE table_name