SQL常用命令

一、select

1.查询

select * from 表名称
select LastName,FirstName from Persons

2.查询结果去重

select distinct Company from Orders

3.有条件地查询

select * from Persons where City='Beijing'
select * from Persons where Year>1965

4.多条件查询 （and 多条件同时满足，or 只要有一个成立）

select * from Persons where FirstName='Thomas' and LastName='Carter'

select * from Persons where (FirstName='Thomas' or LastName='Carter'

select * from Persons where (FirstName='Thomas' or FirstName='William')and LastName='Carter'

5.对结果集进行排序

正序：select Company, OrderNumber from Orders order by Company

以字母顺序显示公司名，并以数字顺序显示顺序号，优先第一位:

select Company, OrderNumber from Orders order by Company, OrderNumber

倒序排序：select Company, OrderNumber from Orders order by Company desc

逆字母顺序显示公司名称，并以数字顺序显示顺序号:

select Company, OrderNumber from Orders order by Company desc, OrderNumber asc

6.TOP / LIMIT

sql server使用top规定要返回的记录的数目，mysql使用limit

sql server：

select top 2 * from Persons
select top 50 percent * from Persons

mysql:

select * from Persons limit 2

7.LIKE

like 操作符用于在 where 子句中搜索列中的指定模式

select * from Persons where City like '%g'

 通配符

8.IN 操作符

IN 操作符允许在 WHERE 子句中规定多个值。

select * from Persons where LastName in ('Adams','Carter')