首页 > 代码库 > sql 语法
sql 语法
DML：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
DDL:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
SELECT column_name FROM TableName
SELECT * FROM TableName
SELECT LastName,FirstName FROM Persons
SELECT DISTINCT Company FROM Orders
SELECT * FROM Persons
WHERE (FirstName=‘Thomas‘ OR FirstName=‘William‘) AND LastName=‘Carter‘
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
INSERT INTO Persons VALUES (‘Gates‘, ‘Bill‘, ‘Xuanwumen 10‘, ‘Beijing‘)
INSERT INTO Persons (LastName, Address) VALUES (‘Wilson‘, ‘Champs-Elysees‘)
UPDATE Person SET Address = ‘Zhongshan 23‘, City = ‘Nanjing‘ WHERE LastName = ‘Wilson‘
DELETE FROM Person WHERE LastName = ‘Wilson‘
DELETE FROM TableName
SELECT TOP number|percent column_name(s) FROM TableName
SELECT column_name(s) FROM TableName LIMIT number
SELECT column_name(s) FROM TableName WHERE ROWNUM <= number
SELECT * FROM Persons WHERE City NOT LIKE ‘%lon%‘
SELECT * FROM Persons WHERE LastName LIKE ‘C_r_er‘
SELECT * FROM Persons WHERE City LIKE ‘[!ALN]%‘
SELECT * FROM Persons WHERE LastName IN (‘Adams‘,‘Carter‘)
SELECT * FROM Persons WHERE LastName BETWEEN ‘Adams‘ AND ‘Carter‘
SELECT column_name(s) FROM table_name AS alias_name (表别名)
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName=‘Adams‘ AND p.FirstName=‘John‘
SELECT column_name AS alias_name FROM table_name （列别名）
SELECT LastName AS Family, FirstName AS Name FROM Persons
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
//INNER JOIN 与 JOIN 是相同的
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
SELECT E_Name FROM Employees_China
UNION SELECT E_Name FROM Employees_USA
SELECT E_Name FROM Employees_China
UNION ALL SELECT E_Name FROM Employees_USA
SELECT LastName,FirstName
INTO Persons_backup IN ‘Backup.mdb‘
FROM Persons
WHERE City=‘Beijing‘
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
CREATE DATABASE database_name
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)
)
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 UNIQUE (Id_P,LastName)
)
ALTER TABLE Persons
ADD UNIQUE (Id_P)
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons
DROP INDEX uc_PersonID
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
sql 语法