SQL语句之基础篇

SQL基础语句

1 创建操作

• 创建数据库和表

#创建数据库
CREATE DATABASE database_name
#创建表
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
OrderDate date DEFAULT GETDATE() #设置默认值
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)#创建多列私有约束
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') # check约束，限制列中的值的范围。
)

• PRIMARY KEY

# 添加私有约束
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
# 删除私有约束条件
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

• 外键

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
# 给已存在的表添加外键
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

• 添加索引

CREATE INDEX PersonIndex ON Person (LastName) 
CREATE INDEX PersonIndex ON Person (LastName DESC)
CREATE INDEX PersonIndex ON Person (LastName, FirstName)

• 添加列

ALTER TABLE Persons ADD Birthday date

• 修改列的数据结构

ALTER TABLE Persons ALTER COLUMN Birthday year

2 条件选择

• SELECT

SELECT LastName,FirstName FROM Persons
SELECT * FROM Persons

• DISTINCT

SELECT DISTINCT  Company FROM Persons

• WEHRE

SELECT * FROM Persons WHERE SEX='man'
SELECT * FROM Persons WHERE ANG BETWEEN 20 AND 30
SELECT * FROM Persons WHERE City LIKE 'Beijing'
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

• 通配符

SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
SELECT * FROM Persons WHERE City LiKE '[AL]%'
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

• IN的用法

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

• as

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

• ORDER BY

# 筛选排序
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber #升序
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

3 更新表数据

• INSERT

#插入行
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
#指定列插入数据
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

• UPDATE

#条件选择更新
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
#条件选择删除行
DELETE FROM Person WHERE LastName = 'Wilson' 
DELETE * FROM table_name

4 表的关联

• INNER JOIN–内连接，返回两个表都有关键字的行,与JOIN一致
• LEFT JOIN --关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
• RIGHT JOIN – 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行
• FULL JOIN – 只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

• UNION----合并两个或多个 SELECT 语句的结果集，不包含重复（请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

• UNION ALL —合并所有结果集，包含重复

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

• SELECT INTO ,语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

5 视图

• CREATE VIEW
  在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

• 示例1：

#创建视图[Current Product List]
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
#查询视图[Current Product List]
SELECT * FROM [Current Product List]

• 示例2：

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName