SQL学习之基础语法篇

20 Feb 2017

可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。

SELECT - 从数据库表中获取数据  
INSERT INTO - 向数据库表中插入数据  
UPDATE - 更新数据库表中的数据    
DELETE - 从数据库表中删除数据    


SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库  
ALTER DATABASE - 修改数据库  
CREATE TABLE - 创建新表  
ALTER TABLE - 变更(改变)数据库表  
DROP TABLE - 删除表  
CREATE INDEX - 创建索引(搜索键)  
DROP INDEX - 删除索引  

SELECT

语法: SELECT column_name(s) FROM table_name

// 从名为 "Users" 的数据库表获取名为"Name" 和 "Age" 的列的内容
SELECT Name,Age FROM Users
  • 关键词 DISTINCT 用于返回唯一不同的值。
    语法: SELECT DISTINCT column_name(s) FROM table_name

  • WHERE 子句用于规定选择的标准。
    语法: SELECT column_name(s) FROM table_name WHERE 列 运算符 值

//从名为 "Users" 的数据库表选取居住城市为北京的用户
SELECT * FROM Users WHERE City='Beijing'

❗️注意:SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号)。如果是数值,请不要使用引号。

  • LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
    语法: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

结合通配符,示例如下:

// 通配符 % 替代一个或多个字符
SELECT * FROM Users WHERE Name LIKE 'Y%' //以 Y 开头
SELECT * FROM Users WHERE Name LIKE '%y' //以 y 结尾
SELECT * FROM Users WHERE Name LIKE '%an%'//包含 an
SELECT * FROM Users WHERE Name NOT LIKE '%an%' //不包含 an

// 通配符 _ 仅替代一个字符
SELECT * FROM Users WHERE Name LIKE '_ucy'

// 通配符 [charlist] 字符列中的任何单一字符
SELECT * FROM Users WHERE Name LIKE '[ABC]%' //以 A 或 B 或 C 开头

// 通配符[^charlist] 或 [!charlist] 不在字符列中的任何单一字符
SELECT * FROM Users WHERE Name LIKE '[!ABC]%' //不以 A 或 B 或 C 开头
  • IN 操作符用于在 WHERE 子句中规定多个值。
    语法: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
SELECT * FROM Users WHERE Name IN ('David','Flora')
  • BETWEEN 操作符在 WHERE 子句中使用,作用是选取介于两个值(数值、文本或者日期)之间的数据范围。
    语法: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

  • AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。

SELECT * FROM Users WHERE Name='Lee' AND Age=20
SELECT * FROM Users WHERE Name='Lee' OR Age=20
SELECT * FROM Users WHERE (Name='Lee' OR Name='Tom') AND Age=20
  • ORDER BY 语句用于对结果集进行排序。(默认按照升序对记录进行排序。降序排列使用 DESC 关键字)
//以字母顺序显示用户名称
SELECT Name, Age FROM Users ORDER BY Name
//以字母顺序显示用户名称,并以数字顺序显示年龄
SELECT Name, Age FROM Users ORDER BY Name, Age
//以字母逆序显示用户名称,并以数字顺序显示年龄
SELECT Name, Age FROM Users ORDER BY Name DESC, Age ASC
  • TOP 子句用于规定要返回的记录的数目。
    语法: SELECT TOP number|percent column_name(s) FROM table_name
    MySQL 语法: SELECT column_name(s) FROM table_name LIMIT number

  • ALIAS 别名。通过使用 SQL,可以为列名称和表名称指定别名(Alias)。
    表的 SQL Alias 语法: SELECT column_name(s) FROM table_name AS alias_name
    列的 SQL Alias 语法: SELECT column_name AS alias_name FROM table_name

//假设两个表分别是:"Persons" 和 "Product_Orders"。分别为它们指定别名 "p" 和 "po"。
//列出 "John Adams" 的所有定单。
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
  • JOIN 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
//引用两个表
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 
//使用 Join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。如果没有匹配,就不会列出这些行。

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

LEFT JOIN 关键字
从左表 (tablename1) 那里返回所有的行,即使在右表 (tablename2) 中没有匹配的行。

RIGHT JOIN关键字
从右表 (tablename2) 那里返回所有的行,即使在左表 (tablename1) 中没有匹配的行。

FULL JOIN 关键字
从左表 (tablename1) 和右表 (tablename2) 那里返回所有的行。

  • UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

❗️注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
UNION 操作符默认选取不同的值。如果允许重复的值可以使用 UNION ALL。
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

  • SELECT INTO 语句可用于创建表的备份复件。
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

INSERT INTO

INSERT INTO 语句用于向表格中插入新的行。
语法: INSERT INTO table_name VALUES (v1, v2,....)

也可以指定所要插入数据的列: INSERT INTO table_name (c1, c2,...) VALUES (v1, v2,....)

INSERT INTO Users VALUES ('Lucy', 26, 'Beijing')
INSERT INTO Users (Name, City) VALUES ('Mary', 'Shanghai')

UPDATE

Update 语句用于修改表中的数据。
语法:UPDATE table_name SET column_name = value WHERE column_name = value

//更新某一行中一个列
UPDATE Users SET Age = 35 WHERE Name = 'Mary'
//更新某一行中若干列
UPDATE Users SET City = 'Shenzhen', Age = 30 WHERE Name = 'Mary' 

DELETE

DELETE 语句用于删除表中的行。
语法:DELETE FROM table_name WHERE column_name = value

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的: DELETE FROM table_name 或者:DELETE * FROM table_name

CREATE DATABASE

用于创建数据库。 语法: CREATE DATABASE database_name

CREATE TABLE

用于创建数据库中的表。语法:

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

数据类型(data_type)规定了列可容纳何种数据类型。

int(size)  仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d) 容纳带有小数的数字。"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size) 容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。在括号中规定字符串的长度。
varchar(size) 容纳可变长度的字符串(可容纳字母、数字以及特殊的字符)。在括号中规定字符串的最大长度。
date(yyyymmdd) 容纳日期。
  • Constraints 约束

NOT NULL 约束强制列不接受 NULL 值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。

UNIQUE 约束唯一标识数据库表中的每条记录。

//MySQL 在 "Persons" 表创建时在 "Id_P" 列创建 UNIQUE 约束:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

//如需命名 UNIQUE 约束, 为多个列定义 UNIQUE 约束,请使用下面的 SQL 语法:
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

//当表已被创建时,如需在 "Id_P" 列创建 UNIQUE 约束,请使用下列 SQL:
ALTER TABLE Persons
ADD UNIQUE (Id_P)

//如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。 每个表都应该有且只有一个主键。
❗️注意:UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。每个表可以有多个 UNIQUE 约束,但是只能有一个 PRIMARY KEY 约束。

PRIMARY KEY (Id_P)

FOREIGN KEY 约束,指向另一个表中的 PRIMARY KEY。

FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

CHECK 约束用于限制列中的值的范围。

CHECK (Id_P>0)

DEFAULT 约束用于向列中插入默认值。

City varchar(255) DEFAULT 'Sandnes'
  • Auto-increment 会在新记录插入表中时生成一个唯一的数字。
    MySQL语法:
CREATE TABLE Users
(
 Id int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (Id)
 )

默认地,开始值是 1,每条新记录递增 1。要让序列以其他的值起始,使用语法: ALTER TABLE Users AUTO_INCREMENT=100

CREATE INDEX

在表中创建索引。在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
语法:CREATE INDEX index_name ON table_name (column_name)

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。 CREATE UNIQUE INDEX index_name ON table_name (column_name)

DROP

通过使用 DROP 语句,可以轻松地删除索引、表和数据库。

DROP INDEX
MySQL 的语法: ALTER TABLE table_name DROP INDEX index_name

DROP TABLE
DROP TABLE 表名称

如果仅需要除去表内的数据,但并不删除表本身,可以 TRUNCATE TABLE 表名称

DROP DATABASE
DROP DATABASE 数据库名称

ALTER

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

ALTER TABLE table_name
ADD column_name datatype

SQL 函数

内建 SQL 函数的语法是:SELECT function(列) FROM 表

  • AVG() 平均数(NULL 不计入)SELECT AVG(column_name) FROM table_name
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
  • COUNT() 函数返回匹配指定条件的行数。(NULL 不计入) SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) AS NumberOfOrders FROM Orders

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
  • FIRST() / LAST() 返回指定的字段中第一个/最后一个记录的值,可使用 ORDER BY 语句对记录进行排序。SELECT FIRST/LAST(column_name) FROM table_name

  • MAX() / MIN() 返回一列中的最大/小值。SELECT MAX/MIN(column_name) FROM table_name

  • SUM() 返回数值列的总数(总额)。SELECT SUM(column_name) FROM table_name

  • GROUP BY() 语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

示例:

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
  • HAVING子句。WHERE 关键字无法与合计函数一起使用。
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
  • UCASE() / LCASE() 函数把字段的值转换为大/小写。

  • MID() 函数用于从文本字段中提取字符。
    SELECT MID(column_name,start[,length]) FROM table_name

  • LEN() 函数返回文本字段中值的长度。SELECT LEN(column_name) FROM table_name

  • ROUND() 函数用于把数值字段舍入为指定的小数位数。 SELECT ROUND(column_name,decimals) FROM table_name

  • FORMAT() 函数用于对字段的显示进行格式化。 SELECT FORMAT(column_name,format) FROM table_name

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

📚补充说明

  • SQL 对大小写不敏感!

参考资料:w3school