当前位置:Gxlcms > 数据库问题 > sql 语法

sql 语法

时间:2021-07-01 10:21:17 帮助过:13人阅读

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   

人气教程排行