SELECT LastName FROM Persons
一定要记住,SQL 对大小写不敏感!
?SELECT - 从数据库表中获取数据
?UPDATE - 更新数据库表中的数据
?DELETE - 从数据库表中删除数据
?INSERT INTO - 向数据库表中插入数据
?CREATE DATABASE - 创建新数据库
?ALTER DATABASE - 修改数据库
?CREATE TABLE - 创建新表
?ALTER TABLE - 变更(改变)数据库表
?DROP TABLE - 删除表
?CREATE INDEX - 创建索引(搜索键)
?DROP INDEX - 删除索引
SELECT DISTINCT Company FROM Orders
如需从 Company" 列中仅选取唯一不同的值,我们需要使用 SELECT DISTINCT 语句:
文本值:
这是正确的:
SELECT * FROM Persons WHERE FirstName='Bush'
这是错误的:
SELECT * FROM Persons WHERE FirstName=Bush
数值:
这是正确的:
SELECT * FROM Persons WHERE Year>1965
这是错误的:
SELECT * FROM Persons WHERE Year>'1965'
ORDER BY 语句用于对结果集进行排序。
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
DELETE FROM Person WHERE LastName = 'Wilson'
SELECT * FROM Persons
WHERE City LIKE 'N%'
SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
?JOIN: 如果表中有至少一个匹配,则返回行
?LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
?RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
?FULL JOIN: 只要其中一个表中存在匹配,就返回行
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)
SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表应该都一个主键,并且每个表只能有一个主键。
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
)
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
CREATE INDEX PersonIndex
ON Person (LastName DESC)
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
面试 数据库 sql 语句
最新推荐文章于 2024-08-06 16:10:08 发布