自考2012版管理系统中计算机应用 第四章 数据库系统SQL 查询语句

1.查询所有字段语法： SELECT * FROM 表名称 命令：SELECT * FROM Persons

2.查询数据表的指定列

语法： SELECT 列名称 FROM 表名称 命令：SELECT LastName, FirstName FROM Persons

4.不输出重复行的查询

语法： SELECT DISTINCT 列名称 FROM 表名称 命令：SELECT DISTINCT Company FROM Orders

5.查询结果排序输出语法： ORDER BY [ASC /DESC] 命令：SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

6.只输出部分行的查询

语法： TOP 数字或百分比 命令：SELECT TOP 2 * FROM Persons SELECT TOP 50 PERCENT * FROM Persons

7.满足逻辑条件的查询

语法： SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 命令：SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

8.查询含有空值NULL的记录

语法： IS NULL 和 IS NOT NULL 命令：SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL(IS NOT NULL )

9.控制输出方向

语法： SELECT column_name(s) INTO new_table_name [INexternaldatabase] FROM old_tablename

命令：SELECT LastName,Firstname INTO Persons_backup FROM Persons

Persons_backup 表

10.使用统计函数查询

语法： AVG() SUM() COUNT() MIN() MAX() 命令：SELECT AVG(OrderPrice) AS OrderAverage FROM OrdersSELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter‘ SELECT MAX(OrderPrice) AS LargestOrderPrice FROM OrdersSELECT SUM(OrderPrice) AS OrderTotal FROM Orders

11.分组查询

语法： group by 命令：SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

13.模糊查询

语法： [NOT] LIKE 命令：SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

14.使用BETWEEN AND查询

语法： BETWEEN ... AND 命令：SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

15.使用IN 查询

语法： SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) 命令：SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

高级查询---连接查询

SELECT http://www.77cn.com.cnstName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P

高级查询---嵌套查询IN ANY ALL EXISTS