sql基础语法

ID	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

sql对于大小写不敏感

select

SELECT 列名称 FROM 表名称

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

🌰：

SELECT LastName,FirstName FROM Persons

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

---

SELECT * FROM Persons

星号（*）是选取所有列的快捷方式。

distinct

关键词 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT 列名称 FROM 表名称

🌰：

Company	OrderNumber
IBM	3532
W3School	2356
Apple	4698
W3School	6953

SELECT DISTINCT Company FROM Orders 

Company
IBM
W3School
Apple

where

作为条件判断

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

操作符	描述
=	等于
<>或！=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

🌰：

LastName	FirstName	Address	City	Year
Adams	John	Oxford Street	London	1970
Bush	George	Fifth Avenue	New York	1975
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

SELECT * FROM Persons WHERE City='Beijing'

LastName	FirstName	Address	City	Year
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

ps：加引号代表字符，否则是数值

AND & OR

🌰：

LastName	FirstName	Address	City
Adams	John	Oxford Street	London
Bush	George	Fifth Avenue	New York
Carter	Thomas	Changan Street	Beijing
Carter	William	Xuanwumen 10	Beijing

SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing
Carter	William	Xuanwumen 10	Beijing

ORDER BY 

对结果集进行排序，默认升序，DESC降序

🌰：

Company	OrderNumber
IBM	3532
W3School	2356
Apple	4698
W3School	6953

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

Company	OrderNumber
W3School	2356
W3School	6953
IBM	3532
Apple	4698

INSERT INTO

用于向表格中插入新的行。

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

🌰：

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing

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

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing

---

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

🌰：

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

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-Elysees

UPDATE

修改表中的数据

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

🌰：

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

LastName	FirstName	Address	City
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

DELETE

DELETE FROM 表名称 WHERE 列名称 = 值

 🌰：

DELETE FROM Person WHERE LastName = 'Wilson' 

LastName	FirstName	Address	City
Gates	Bill	Xuanwumen 10	Beijing