SQL语句

数据库SQL语句总结

【1】SQL语言

可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分:

SELECT - 从数据库表中获取数据

UPDATE - 更新数据库表中的数据

DELETE - 从数据库表中删除数据

INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。

SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更(改变)数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引(搜索键)

DROP INDEX - 删除索引

【2】常用SQL语句简单总结

(1)SELECT 列名称 FROM 表名称

(2)SELECT * FROM 表名称

(3)SELECT LastName,FirstName FROM Persons

(4)SELECT * FROM Persons

(5)SELECT DISTINCT 列名称 FROM 表名称

(6)SELECT DISTINCT Company FROM Orders

(7)SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

(8)SELECT * FROM Persons WHERE City='Beijing'

(9)SELECT * FROM Persons WHERE FirstName='Bush'

(10)SELECT * FROM Persons WHERE Year>1965

(11)SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

(12)SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

(13)SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

(14)SELECT Company, OrderNumber FROM Orders ORDER BY Company

(15)SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

(16)SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

(17)SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

(18)INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

(19)INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

(20)INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

(21)UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

(22)UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

(23)UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

(24)DELETE FROM 表名称 WHERE 列名称 = 值

(25)DELETE FROM Person WHERE LastName = 'Wilson'

(26)DELETE FROM table_name

(27)DELETE * FROM table_name

(28)TOP

  SELECT TOP number|percent column_name(s) FROM table_name

  SELECT TOP 2 * FROM Persons

  SELECT TOP 50 PERCENT * FROM Persons

(29)MySQL语法

  SELECT column_name(s) FROM table_name LIMIT number

  SELECT * FROM Persons LIMIT 5

(30)Oracle语法

  SELECT column_name(s) FROM table_name WHERE ROWNUM <= number

  SELECT * FROM Persons WHERE ROWNUM <= 5

(31)LIKE

  SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

  SELECT * FROM Persons WHERE City LIKE 'N%'

  SELECT * FROM Persons WHERE City LIKE '%g'

  SELECT * FROM Persons WHERE City LIKE '%lon%'

  SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

(32)

  SELECT * FROM Persons WHERE City LIKE 'Ne%'

  SELECT * FROM Persons WHERE City LIKE '%lond%'

  SELECT * FROM Persons WHERE FirstName LIKE '_eorge'

  SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

  SELECT * FROM Persons WHERE City LIKE '[ALN]%'

  SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

(33)IN

  SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

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

(34)BETWEEN AND

  SELET column_name(s)

  FROM table_name

  WHERE column_name

  BETWEEN value1 AND value2

  SELECT * FROM Persons

  WHERE LastName

  BETWEEN 'Adams' AND 'Carter'

  SELECT * FROM Persons

  WHERE LastName

  NOT BETWEEN 'Adams' AND 'Carter'

(35)表别名

  SELECT column_name(s)

  FROM table_name

  AS alias_name

(36)列别名

  SELECT column_name AS alias_name

  FROM table_name

(37)AS

  SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName

  FROM Persons, Product_Orders

  WHERE Persons.LastName='Adams' AND Persons.FirstName='John'

  SELECT po.OrderID, p.LastName, p.FirstName

  FROM Persons AS p, Product_Orders AS po

  WHERE p.LastName='Adams' AND p.FirstName='John'

(38)

  SELECT LastName AS Family, FirstName AS Name

  FROM Persons

(39)INNER JOIN

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

  FROM Persons, Orders

  WHERE Persons.Id_P = Orders.Id_P

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

  FROM Persons

  INNER JOIN Orders

  ON Persons.Id_P = Orders.Id_P

  ORDER BY Persons.LastName

(40)LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

  FROM Persons

  LEFT JOIN Orders

  ON Persons.Id_P=Orders.Id_P

  ORDER BY Persons.LastName

(41)RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行,即使在左表 (Persons) 中没有匹配的行。

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

  FROM Persons

  RIGHT JOIN Orders

  ON Persons.Id_P = Orders.Id_P

  ORDER BY Persons.LastName

(42)FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配,或者如果 "Orders" 中的行在表 "Persons" 中没有匹配,这些行同样会列出。

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

  FROM Persons

  FULL JOIN Orders

  ON Persons.Id_P = Orders.Id_P

  ORDER BY Persons.LastName

(43)UNION这个命令无法列出在中国和美国的所有雇员。在上面的例子中,我们有两个名字相同的雇员,他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

  SELECT E_Name FROM Employees_China

  UNION

  SELECT E_Name FROM Employees_USA

(44)UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。

  SELECT E_Name FROM Employees_China

  UNION ALL

  SELECT E_Name FROM Employees_USA

(45)SELECT INTO 语句可用于创建表的备份复件。

  SELECT *

  INTO Persons_backup

  FROM Persons

  SELECT LastName,FirstName

  INTO Persons_backup

  FROM Persons

  SELECT LastName,Firstname

  INTO Persons_backup

  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

(46)

  CREATE DATABASE database_name

  CREATE DATABASE my_db

(47)

  CREATE TABLE Persons

  (

    Id_P int,

    LastName varchar(255),

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)

  )

(48)NOT NULL 约束

  CREATE TABLE Persons

  (

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)

  )

(49) UNIQUE 约束

  CREATE TABLE Persons

  (

    Id_P int NOT NULL UNIQUE,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)

  )

  ALTER TABLE Persons

  ADD UNIQUE (Id_P)

(50) PRIMARY KEY 约束

  CREATE TABLE Persons

  (

    Id_P int NOT NULL PRIMARY KEY,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)

  )

   ALTER TABLE Persons

   ADD PRIMARY KEY (Id_P)

(51)FOREIGN KEY 约束

  CREATE TABLE Orders

  (  

    Id_O int NOT NULL PRIMARY KEY,

    OrderNo int NOT NULL,

    Id_P int FOREIGN KEY REFERENCES Persons(Id_P)

  )

  ALTER TABLE Orders

  ADD FOREIGN KEY (Id_P)

  REFERENCES Persons(Id_P)

  ALTER TABLE Orders

  DROP CONSTRAINT fk_PerOrders

(52)CHECK 约束

  CREATE TABLE Persons

  (

    Id_P int NOT NULL CHECK (Id_P>0),

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)

  )

  ALTER TABLE Persons

  ADD CHECK (Id_P>0)

  ALTER TABLE Persons

  DROP CONSTRAINT chk_Person

(53)DEFAULT 约束

  CREATE TABLE Persons

  (  

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255) DEFAULT 'Sandnes'

  )

  ALTER TABLE Persons

  ALTER COLUMN City SET DEFAULT 'SANDNES'

  ALTER TABLE Persons

  ALTER COLUMN City DROP DEFAULT

(54)CREATE INDEX 语句

  CREATE INDEX index_name

  ON table_name (column_name)

  CREATE UNIQUE INDEX index_name

  ON table_name (column_name)

  CREATE INDEX PersonIndex

  ON Person (LastName)

  CREATE INDEX PersonIndex

  ON Person (LastName DESC)

  CREATE INDEX PersonIndex

  ON Person (LastName, FirstName)

(55)DROP INDEX 语句

  ALTER TABLE table_name DROP INDEX index_name

  DROP TABLE 表名称

  DROP DATABASE 数据库名称

  TRUNCATE TABLE 表名称   

  TRUNCATE TABLE 命令(仅仅删除表格中的数据)

(56)ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

  ALTER TABLE table_name

  ADD column_name datatype

  ALTER TABLE table_name

  DROP COLUMN column_name

  ALTER TABLE table_name

  ALTER COLUMN column_name datatype

  ALTER TABLE Persons

  ADD Birthday date

  ALTER TABLE Persons

  ALTER COLUMN Birthday year

  ALTER TABLE Person

  DROP COLUMN Birthday

(57)Auto-increment 会在新记录插入表中时生成一个唯一的数字。

  CREATE TABLE Persons

  (

    P_Id int NOT NULL AUTO_INCREMENT,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),

    PRIMARY KEY (P_Id)

  )

  MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

  默认地,AUTO_INCREMENT 的开始值是 1,每条新记录递增 1。

(58)在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。

  CREATE VIEW view_name AS

  SELECT column_name(s)

  FROM table_name

  WHERE condition

  SQL DROP VIEW Syntax

  DROP VIEW view_name

(59)Date 函数

  SELECT * FROM Orders WHERE OrderDate='2008-12-26'

(60)IS NULL

  SELECT LastName,FirstName,Address FROM Persons

  WHERE Address IS NULL

(61)数据

  Text 用于文本或文本与数字的组合。最多 255 个字符。  

  Memo Memo 用于更大数量的文本。最多存储 65,536 个字符。

  注释:无法对 memo 字段进行排序。不过它们是可搜索的。

  Byte 允许 0 到 255 的数字。 1 字节

  Integer 允许介于 -32,768 到 32,767 之间的数字。 2 字节

  Long 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 4 字节

  Single 单精度浮点。处理大多数小数。 4 字节

  Double 双精度浮点。处理大多数小数。 8 字节

  Currency 用于货币。支持 15 位的元,外加 4 位小数。

  提示:您可以选择使用哪个国家的货币。 8 字节

  AutoNumber AutoNumber 字段自动为每条记录分配数字,通常从 1 开始。 4 字节

  Date/Time 用于日期和时间 8 字节

  Yes/No  逻辑字段,可以显示为 Yes/No、True/False 或 On/Off。

  在代码中,使用常量 True 和 False (等价于 1 和 0)

  注释:Yes/No 字段中不允许 Null 值

  1 比特

  Ole Object 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects) 最多 1GB

  Hyperlink 包含指向其他文件的链接,包括网页。  

  Lookup Wizard 允许你创建一个可从下列列表中进行选择的选项列表。 4 字节

(62)AVG 函数

  SELECT AVG(column_name) FROM table_name

(63)COUNT() 函数返回匹配指定条件的行数。

  SELECT COUNT(*) FROM table_name

  SELECT COUNT(column_name) FROM table_name

  SELECT COUNT(DISTINCT column_name) FROM table_name

(64)FIRST() 函数返回指定的字段中第一个记录的值。

  SELECT FIRST(column_name) FROM table_name

(65)LAST() 函数返回指定的字段中最后一个记录的值。

(66)MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

  SELECT MAX(column_name) FROM table_name

(67)MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

  SELECT MIN(column_name) FROM table_name

(68)SUM 函数返回数值列的总数(总额)。

  SELECT SUM(column_name) FROM table_name

(69)GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。

  SELECT column_name, aggregate_function(column_name)

  FROM table_name

  WHERE column_name operator value

  GROUP BY column_name

  SELECT Customer,SUM(OrderPrice) FROM Orders

  GROUP BY Customer

(70)在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。

  SELECT column_name, aggregate_function(column_name)

  FROM table_name

  WHERE column_name operator value

  GROUP BY column_name

  HAVING aggregate_function(column_name) operator value

  SELECT Customer,SUM(OrderPrice) FROM Orders

  GROUP BY Customer

  HAVING SUM(OrderPrice)<2000

  SELECT Customer,SUM(OrderPrice) FROM Orders

  WHERE Customer='Bush' OR Customer='Adams'

  GROUP BY Customer

  HAVING SUM(OrderPrice)>1500

(71)UCASE 函数把字段的值转换为大写。

  SELECT UCASE(column_name) FROM table_name

  SELECT UCASE(LastName) as LastName,FirstName FROM Persons

(72)MID 函数用于从文本字段中提取字符。

  SELECT MID(column_name,start[,length]) FROM table_name

  SELECT MID(City,1,3) as SmallCity FROM Persons

(73)LEN 函数返回文本字段中值的长度。

  SELECT LEN(column_name) FROM table_name

  SELECT LEN(City) as LengthOfCity FROM Persons

(74)ROUND 函数用于把数值字段舍入为指定的小数位数。

  SELECT ROUND(column_name,decimals) FROM table_name

  SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

(75)NOW 函数返回当前的日期和时间。

  SELECT NOW() FROM table_name

  SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

(76)FORMAT 函数用于对字段的显示进行格式化。

  SELECT FORMAT(column_name,format) FROM table_name

  SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate

  FROM Products

【3】参考资料

1. 《 SQL语句语法

2. 《 常用经典SQL语句大全

 

Good  Good  Study, Day  Day  Up.

顺序  选择  循环  总结 

posted @ 2017-02-09 15:36  kaizenly  阅读(374)  评论(0编辑  收藏  举报
打赏