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 语法

标签：car   ams   插入   ada   city   ddl   tween   china   order