avatar
童琦杰
Feb 16, 2017Technology

SQL - 基础

关键字: DISTINCT

用于返回唯一不同的值。

sql
SELECT DISTINCT column1, column2, ... 
  FROM table_name

关键字: TOP、LIMIT、ROWNUM

用于规定要返回的记录的数目。

SQL Server:

sql
SELECT TOP 2 * 
  FROM table_name
SELECT TOP 50 PERCENT * 
  FROM table_name

MySQL:

sql
SELECT * 
  FROM table_name 
 LIMIT 5

Oracle:

sql
SELECT * 
  FROM table_name 
 WHERE ROWNUM <= 5

操作符: [NOT] LIKE

用于在WHERE子句中搜索列中的指定模式。

sql
SELECT * 
  FROM table_name 
 WHERE column_name LIKE 'N%' -- 其中'%'为SQL通配符

SQL通配符必须与LIKE运算符一起使用。

  • %: 替代一个或多个字符

  • _: 仅替代一个字符

  • [charlist]: 字符列中的任何单一字符

  • [^charlist]或[!charlist]: 不在字符列中的任何单一字符

操作符: [NOT] IN

用于在WHERE子句中筛选多个值。

sql
SELECT * 
  FROM table_name 
 WHERE column_name IN ('Adams','Carter')

操作符: [NOT] BETWEEN...AND

用于选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

sql
SELECT * 
  FROM table_name 
 WHERE column_name BETWEEN 'Adams' AND 'Carter'

需要注意的是，不同的数据库对BETWEEN...AND操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人，但不包括 "Adams" 和 "Carter"；某些数据库会列出介于"Adams"和"Carter"之间并包括"Adams"和"Carter"的人；而另一些数据库会列出介于"Adams"和"Carter"之间的人，包括"Adams"，但不包括"Carter"。

别名: AS

用于为列名称和表名称指定别名。

sql
SELECT po.OrderID, p.LastName, p.FirstName 
  FROM Persons AS p, Product_Orders AS po 
 WHERE p.LastName='Adams' AND p.FirstName='John'
SELECT LastName AS Family, FirstName AS Name 
  FROM Persons

连接: JOIN

根据返回结果的不同，连接可以分为以下几种类型。

  • JOIN或INNER JOIN: 返回两个表中匹配连接条件的记录

  • LEFT JOIN或LEFT OUTER JOIN: 返回左表所有记录和右表中匹配连接条件的记录

  • RIGHT JOIN或RIGHT OUTER JOIN: 返回右表所有记录和左表中匹配连接条件的记录

  • FULL JOIN: 返回两张表中的所有记录

JOIN或INNER JOIN

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

LEFT JOIN或LEFT OUTER JOIN

sql
SELECT column_name(s) 
  FROM table_name1 LEFT JOIN table_name2 
    ON table_name1.column_name = table_name2.column_name
SELECT column_name(s) 
  FROM table_name1 LEFT OUTER JOIN table_name2 
    ON table_name1.column_name = table_name2.column_name

RIGHT JOIN或RIGHT OUTER JOIN

sql
SELECT column_name(s) 
  FROM table_name1 RIGHT JOIN table_name2 
    ON table_name1.column_name = table_name2.column_name
SELECT column_name(s) 
  FROM table_name1 RIGHT OUTER JOIN table_name2 
    ON table_name1.column_name = table_name2.column_name

FULL JOIN

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

操作符: UNION [ALL]

用于合并两个或多个SELECT语句的结果集。其中，SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。

默认地，UNION操作符选取不同的值。如果允许重复的值，请使用UNION ALL。

sql
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

子查询

子查询是指一个SELECT语句中嵌套另一个SELECT语句，它能返回单个值或多个值。子查询必须放在括号内。

sql
SELECT ProductName
  FROM Product 
 WHERE Id IN (SELECT ProductId 
                FROM OrderItem
               WHERE Quantity > 100)
SELECT FirstName, LastName, 
       OrderCount = (SELECT COUNT(O.Id) FROM [Order] O WHERE O.CustomerId = C.Id)
  FROM Customer C

关键字: ANY和ALL

关键字ANY和ALL用于WHERE或HAVING语句中，并且操作返回多个值的子查询结果。

  • ANY: 若子查询结果中任意一个值满足条件则返回True

  • ALL: 若子查询结果中所有值满足条件则返回True

sql
SELECT ProductName
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE Quantity = 1)
SELECT DISTINCT FirstName + ' ' + LastName AS CustomerName
  FROM Customer, [Order]
 WHERE Customer.Id = [Order].CustomerId
   AND TotalAmount > ALL 
       (SELECT AVG(TotalAmount)
          FROM [Order]
         GROUP BY CustomerId)

EXISTS

用于检测子查询返回的结果中是否存在值，若子查询返回一个或多个值则返回true。

sql
SELECT CompanyName
  FROM Supplier
 WHERE EXISTS
       (SELECT ProductName
          FROM Product
         WHERE SupplierId = Supplier.Id 
           AND UnitPrice > 100)

语句: ORDER BY [DESC]

sql
SELECT Id, CompanyName, City, Country
  FROM Supplier
 WHERE Country IN ('USA', 'Japan', 'Germany')
 ORDER BY Country ASC, CompanyName DESC

语句: GROUP BY

GROUP BY语句将查询结果按一个或多个列分组，每个组返回一条记录。

sql
SELECT SUM(O.TotalPrice), C.FirstName, C.LastName
  FROM [Order] O JOIN Customer C 
    ON O.CustomerId = C.Id
 GROUP BY C.FirstName, C.LastName
 ORDER BY SUM(O.TotalPrice) DESC

语句: HAVING

HAVING语句用于过滤由GROUP BY语句返回的结果，因此它只能与GROUP BY语句一起使用。

sql
SELECT COUNT(Id), Country 
  FROM Customer
 WHERE Country <> 'USA'
 GROUP BY Country
HAVING COUNT(Id) >= 9
 ORDER BY COUNT(Id) DESC

语句: OFFSET-FETCH

OFFSET-FETCH语句提供对分页查询的支持。OFFSET指定查询结果的偏移行数，FETCH指定选取的行数。

sql
SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS
SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS
 FETCH NEXT 5 ROWS ONLY

约束: NOT NULL

强制字段始终包含值，如果不向字段添加值，就无法插入新记录或者更新记录。

sql
CREATE TABLE Persons
(
    Id INT NOT NULL
)

约束: UNIQUE

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

MYSQL:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    UNIQUE (Id)
)

SQL Server/Oracle:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL UNIQUE
)

允许命名UNIQUE约束，以及为多个列定义UNIQUE约束。

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    CONSTRAINT UI_PERSON_ID_NAME UNIQUE (Id, LastName)
)

约束: PRIMARY KEY

唯一标识数据库表中的每条记录，主键必须包含唯一的值，不能包含NULL值，每个表都应该有一个主键，并且每个表只能有一个主键。

MYSQL:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    PRIMARY KEY (Id)
)

SQL Server/Oracle:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL PRIMARY KEY
)

允许命名PRIMARY KEY约束，以及为多个列定义PRIMARY KEY约束。

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    CONSTRAINT PK_PERSON_ID_NAME PRIMARY KEY (Id, LastName)
)

约束: FOREIGN KEY

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

MYSQL:

sql
CREATE TABLE Orders
(
    Id INT NOT NULL,
    PersonId INT,
    PRIMARY KEY (Id),
    FOREIGN KEY (PersonId) REFERENCES Persons(Id)
)

SQL Server/Oracle:

sql
CREATE TABLE Orders
(
    Id INT NOT NULL PRIMARY KEY,
    PersonId INT FOREIGN KEY REFERENCES Persons(Id)
)

允许命名FOREIGN KEY约束，以及为多个列定义FOREIGN KEY约束。

sql
CREATE TABLE Orders
(
    Id INT NOT NULL,
    PersonId INT,
    PRIMARY KEY (Id),
    CONSTRAINT FK_ORDER_REF_PERSON FOREIGN KEY (PersonId) REFERENCES Persons(Id)
)

约束: CHECK

用于限制列中的值的范围。

MYSQL:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    CHECK (Id > 0)
)

SQL Server/Oracle:

sql
CREATE TABLE Persons
(
    Id INT NOT NULL CHECK (Id > 0)
)

允许命名PRIMARY KEY约束，以及为多个列定义PRIMARY KEY约束。

sql
CREATE TABLE Persons
(
    Id INT NOT NULL,
    City VARCHAR(255),
    CONSTRAINT CK_PERSON CHECK (Id > 0 AND City = 'Sandnes')
)

约束: DEFAULT

用于向列中插入默认值。

sql
CREATE TABLE Orders
(
    Id INT NOT NULL,
    OrderDate DATETIME DEFAULT GETDATE()
)
© 2015-2022 tongqijie.com 版权所有沪ICP备17000682号