SQL经典教程-第一篇：基础

SQL经典教程-第一篇：基础

第一篇 SQL基础

1.1 Select 查询

select语句

SELECT LastName,FirstName FROMPersons

SELECT * FROM Persons

SELECT DISTINCT Company FROM Orders 

WHERE 条件子句

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

SELECT * FROM Persons WHERE City='Beijing'

操作符

描述

=

等于

<>（某些版本用!=）

不等于

>

大于

<

小于

>=

大于等于

<=

小于等于

BETWEEN

在某个范围内

LIKE

搜索某种模式

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'

ORDER BY 语句

--用于根据指定的列对结果集进行排序。默认按照升序对记录进行排序，如果您希望按照降序对记录进行排序，可以使用DESC 关键字。

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

1.2 Insert 插入

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

1.3 Update 更新

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'

WHERE LastName = 'Wilson'

1.4 Delete 删除

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Person WHERE LastName = 'Wilson'