SQL

简介
1. structure query language, 结构化查询语言，用于访问和处理数据库
2. 用途
  1. 查询
  2. 取数
  3. 插入新记录
  4. 更新记录
  5. 删除记录
  6. 创建数据库
  7. 创建新表
  8. 创建存储过程
    1. 类似于函数，可接收参数
    2. 好处：更快，更安全，可复用
  9. 创建视图
    1. 类似于虚拟表，由一个或多个基表组成，对表的一种抽象
    2. 好处
      1. 可实现应用程序和表之间的解耦，尽量减少一边的变动对另外一边的影响
      2. 更方便实现权限的控制
  10. 设置表、存储过程、视图的权限
语法
1. 说明
  1. SQL 对大小写不敏感；
  2. 每条 SQL 语句之间，有些数据库需要使用分号分隔，有些不需要
  3. SQL 可以分成两部分，DML 和 DDL
    1. DML，数据操作语言
      1. SELECT, 从数据库中选择数据
      2. UPDATE，更新数据库中的数据
      3. DELETE，删除数据库中的数据
      4. INSERT INTO，向数据库插入数据
    2. DDL，数据定义语言
      1. CREATE DATABASE，创建新数据库
      2. ALTER DATABASE, 修改数据库
      3. CREATE TABLE，创建新表
      4. ALTER TABLE, 修改表
      5. DROP TABLE，删除表
      6. CREATE INDEX，创建索引（搜索键）
      7. DROP INDEX，删除索引
2. SQL SELECT 语句
  1. 用于从表中选取数据，结果被存储在一个结果表中（结果集）
  2. 语法
    1. SELECT 列名称 FROM 表名称
    2. SELECT 列名称1, 列名称2 FROM 表名称
    3. SELECT * FROM 表名称（注：* 号表示所有列）
3. SQL SELECT DISTINCT 语句
  1. 从表中选择数据后，返回唯一不同的值，即去掉重复项
  2. 语法
    1. SELECT DISTINCT 列名称 FROM 表名称
4. SQL WHERE 子句
  1. 从表中有条件的选取数据
  2. 语法
    1. SELECT 列名称 FROM 表名称 WHERE 列运算符值
  3. 运算符
    1. =，等于
    2. <>，不等于（某些版本可用 !=）
    3. ，大于
    4. <，小于
    5. =，大于等于
    6. <=，小于等于
    7. BETWEEN，在某个范围内
    8. LIKE，搜索某种模式？
  4. 例子
    1. SELECT 列名称 FROM 表名称 WHERE city=’Beijing’
      1. city 是表中某一列的名称
      2. 由于 Beijing 是字符串，所以使用引号，如果是数值，则不需要用引号
5. SQL AND & OR 运算符
  1. 用于两个或以上的条件对记录进行过滤
    1. 条件1 AND 条件2，需要满足两个条件
    2. 条件1 OR 条件2，只需要满足其中一个条件
  2. 示例
    1. SELECT * FROM Persons WHERE FirstName=’Thomas’ AND LastName=’Carter’
    2. SELECT * FROM Persons WHERE FirstName=’Thomas’ OR LastName=’Carter’
  3. 也可以将两个运算符结合起来，使用圆括号来组成复杂的表达式
    1. SELECT * FROM Persons WHERE (FirstName=’Thomas’ OR FirstName=’William’) AND LastName=’Carter’
6. SQL ORDER BY 子句
  1. 用于对结果集进行排序，默认升序（ASC），降序用 DESC
  2. 示例
    1. SELECT Company, OrderNumber FROM Orders ORDER BY Company
    2. SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
    3. SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
    4. SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
7. SQL INSERT INTO 语句
  1. 用于向表中插入新行
  2. 语法
    1. INSERT INTO 表名称 VALUES （值1，值2，……）
    2. INSERT INTO 表名称（列1，列2，……） VALUES （值1，值2，……）
      1. 指定要插入数据的列
  3. 示例
    1. INSERT INTO Persons VALUES (‘Gates’, ‘Bill’, ‘Xuanwumen 10’, ‘Beijing’)
    2. INSERT INTO Persons (LastName, Address) VALUES (‘Wison’, ‘Champs-Elysees’)
8. SQL UPDATE 语句
  1. 用于修改表中的数据
  2. 语法
    1. UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
  3. 示例
    1. UPDATE Persons SET FirstName=’Fred’ WHERE LastName=’Wilson’
      1. 更新一行中的一个列
    2. UPDATE Persons SET Address=’Zhongshan 23’, City=’Nanjing’ WHERE LastName=’Wilson’
      1. 更新一行中的多个列
9. SQL DELETE 语句
  1. 用于删除表中的行
  2. 语法
    1. DELETE FROM 表名称 WHERE 列名称=某值
  3. 示例
    1. 删除某行
      1. DELETE FROM Person WHERE LastName=’Wilson’
    2. 删除所有行（不删除表的情况下）
      1. DELETE FROM 表名称
      2. DELETE * FROM 表名称
10. SQL TOP 子句
  1. 用于设定要返回的记录的数目（如果表很大，可能只需要返回部分）
  2. 语法
    1. SELECT TOP 数字（或百分比） FROM 表名称
    2. 不同数据库此语法可能略有不同，不是所在数据库都支持 TOP 语法
  3. 示例
    1. MySQL
      1. SELECT * FROM Persons LIMIT 5
    2. Oracle
      1. SELECT * FROM Persons WHERE ROWNUM <=5
    3. SQL
      1. SELECT TOP 2 * FROM Persons
      2. SELECT TOP 50 PERCENT * FROM Persons
11. SQL LIKE 操作符
  1. 用于在 WHERE 子句中搜索列中的指定模式
  2. 语法
    1. SELECT 列名称 FROM 表名称 WHERE 列名称 LIKE 模式
  3. 示例
    1. 选择城市以”N”开头的记录
      1. SELECT * FROM Persons WHERE City LIKE ‘N%’
    2. 选择城市以”g”结尾的记录
      1. SELECT * FROM Persons WHERE City LIKE ‘%g’
    3. 选择城市包含”lon”的记录
      1. SELECT * FROM Persons WHERE City LIKE ‘%lon%’
    4. 选择城市中不包含’lon’的记录（使用 NOT LIKE）
      1. SELECT * FROM Persons WHERE City NOT LIKE ‘%lon%’
12. SQL 通配符
  1. 在搜索数据库中的数据时，可以使用 SQL 通配符
  2. 通配符可以用来代替一个或者多个字符，通配符必须与 LIKE 运算符一起使用
    1. “%”，替代一个或者多个字符
    2. “_”，替代一个字符
    3. [charlist]，字符列中的任何单一字符
    4. [^charlist] 或者 [!charlist] ，不在字符列中的任何单一字符
  3. 示例
    1. 以任意一个字符开头，后面接着’eorge’
      1. SELECT * FROM Persons WHERE FirstName LIKE ‘_eorge’
    2. 以 C 开头，后面接着 r，再接任意字符，最后以 er 结尾
      1. SELECT * FROM Persons WHERE FirstName LIKE ‘C_r%er’
    3. 选取城市以A、L、N 开头
      1. SELECT * FROM Persons WHERE City LIKE ‘[ALN]%’
    4. 选取城市不以A、L、N 开头
      1. SELECT * FROM Persons WHERE City LIKE ‘[!ALN]%’
13. SQL IN 操作符
  1. 用于在 WHERE 子句中规定多个值
  2. 语法
    1. SELECT 列名称 FROM 表名称 WHERE 列名称 IN （值1，值2，……）
  3. 示例
    1. SELECT * FROM Persons WHERE LastName IN (‘Adams’, ‘Carter’)
14. SQL BETWEEN 操作符
  1. 用在 WHERE 子句，用于选取两个值之间的数据范围
  2. 语法
    1. SELECT 列名称 FROM 表名称 WHERE 列名称 BETWEEN 值1 AND 值2
    2. 不同的数据库对 BETWEEN…AND… 的理解不同，有些包括前后，有些只前无后，有些不包括前后
    3. 可以使用 NOT BETWEEN … AND … 来选取范围之外的数据；
  3. 示例
    1. SELECT * FROM Persons WHERE LastName BETWEEN ‘Adams’ AND ‘Carter’
15. SQL Alias 别名
  1. AS 可以用来为列名称或表名称指定别名，目的是让 SQL 容易阅读和书写（通过将语句重复出现的单词用简称来替代）
  2. 语法
    1. SELECT 列名称 FROM 表名称 AS 表别名
    2. SELECT 列名称 AS 列别名 FROM 表名称
  3. 示例
    1. 表名称别名
      1. SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName=’Adams’ AND p.FirstName=’John’
    2. 列名称别名
      1. SELECT FirstName AS FAMILY, LastName AS NAME FROM Persons
      2. 在出来的结果集中，原列名称 FirstName 和 LastName 会分别显示为别名 FAMILY 和 NAME
16. SQL JOIN
  1. 用于从两个或者多个表中查询数据
  2. 语法
    1. SELECT 列1，列2，… FROM 表1 INNER JOIN 表2 ON 列1=列2
      1. 取两个表的交集
    2. SELECT 列1，列2，…FROM 表1 LEFT JOIN 表2 ON 列1=列2（有些数据库叫 LEFT OUTER JOIN）
      1. 取表1的完全集，如果表2中没有对应值，则默认NULL
    3. SELECT 列1，列2，…FROM 表1 RIGHT JOIN 表2 ON 列1=列2 （有些数据库叫 RIGHT OUTER JOIN）
      1. 取表2的完全集，如果表1中没有对应值，则默认NULL
    4. SELECT 列1，列2，…FROM 表1 FULL JOIN 表2 ON 列1=列2 （有些数据库叫 FULL OUTER JOIN）
      1. 取两个表的并集，包括两个表的全部行，不管有没有对应值，没有则默认NULL
    5. SELECT 列1，列2，…FROM 表1 CROSS JOIN 表2
      1. 取两个表的笛卡尔乘积，表1*表2的集合，慎用
  3. 示例
    1. INNER JOIN，交集，两个表都需要匹配
      1. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
    2. LEFT JOIN，左表所有行，右表有匹配行则返回右表值，没有匹配行则返回NULL
      1. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_p ORDER BY Persons.LastName
    3. RIGHT JOIN, 右表所有行，左表有匹配行则返回左表值，没有匹配行则返回NULL
      1. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
    4. FULL JOIN，则要其中某个匹配存在，则返回行，没有值默认NULL
      1. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
  4. 说明
    1. 可以通过增加条件，如 WHERE 列名称=某值来对结果集进一步筛选
17. SQL UNION
  1. 合并两个或多个 SELECT 语句的结果集
    1. UNION，会去除重复的行
    2. UNION ALL，不会去重，展示所有
    3. 两个 SELECT 语句必须有相同数量的列，列的数据类型相同，列的顺序相同
  2. 语法
    1. SELECT 列名称 FROM 表1 UNION SELECT 列名称 FROM 表2
  3. 示例
    1. SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
18. SQL SELECT INTO
  1. 用于从一个表中选取数据，插入到另外一个表中（可以是在另外的数据库）
  2. 语法
    1. 复制一个列或者多个列
      1. SELECT 列名称 INTO 表名称2 FROM 表名称1
      2. 示例：SELECT * INTO new_table_name FROM old_table_name
    2. 插入到另外数据库
      1. SELECT 列名称 INTO 表名称2 IN 数据库2 FROM 表名称1
      2. 示例：SELECT * INTO Persons IN ‘Backup.mdb’ FROM Persons
    3. 可以增加 WHERE 子句
      1. 示例：SELECT LastName, FirstName INTO Persons_Backup FROM Persons WHERE City=’Beijing’
    4. 可以使用 JOIN 连接多个表
      1. 示例：SELECT Persons.LastName, Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P
19. CREATE DATABASE
  1. 创建数据库
  2. 语法
    1. CREATE DATABASE 数据库名
  3. 示例
    1. CREATE DATABASE my_db
20. CREATE TABLE
  1. 创建表
  2. 语法
    1. CREATEA TABLE 表名称
      1. （
        
        列名称1 数据类型1，
        
        列名称2 数据类型2，
        
        列名称3 数据类型3，
      2. ）
  3. 数据类型
    1. integer(size), int(size), smallint(size), tinyint(size)
      1. 仅容纳整数，size 用来设定最大位数
    2. decimal(size, d), numeric(size,d)
      1. 容纳带有小数的数字，size 用来设定数据的最大位数，d 设置小数点后的最大位数
    3. char(size)
      1. 容纳字符串（可容纳字母、数字及特殊字符）
      2. size 设定字符串的最大长度
    4. varchar(size)
      1. 容纳可变长度的字符串
      2. size 设定字符串的最大长度
    5. date(yyyymmdd)
      1. 容纳日期
  4. 示例
    1. CREATE TABLE Persons
      1. (
        
        Id_P int,
        
        LastName varchar(255),
        
        FirstName varchar(255),
        
        Address varchar(255),
        
        City varchar(255)
      2. )
21. SQL Contrains 约束，用来约束限制加入表的数据的类型
  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT
22. SQL NOT NULL
  1. 约束不接受空值，即如果没有值，则无法向表中插入数据
  2. 示例
    1. CREATE TABLE Persons
      1. (
        
        Id_P int NOT NULL,
        
        LastName varchar(255) NOT NULL,
        
        FirstName varchar(255),
        
        Address varchar(255),
        
        City varchar(255)
      2. )
23. SQL UNIQUE
  1. 约束唯一标识表中的每条记录
  2. UNIQUE 和 PRIMARY KEY 都可以用来保证唯一性
  3. 一个表可以有多个 UNIQUE 约束，但只可以有一个PRIMARY KEY
  4. 示例
    1. MySQL
      1. 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)
        
        )
    2. SQL Server / Oracle / MS Access
      1. CREATE TABLE Persons
        
        (
        
        Id_P int NOT NULL UNIQUE,
        
        LastName varchar(255) NOT NULL
        
        FirstName varchar(255),
        
        Address varchar(255),
        
        City varchar(255)
        
        )
  5. 如果需要约束多个唯一标识
    1. UNIQUE (Id_P) 更改为 UNIQUE (Id_P, LastName)
  6. 如果需要给约束命名，
    1. 语法：
      1. CONSTRAINT 约束名称 UNIQUE 列名称
    2. 示例
      1. CONSTRAINT uc_PersonID UNIQUE (Id_P, LastName)
  7. 如果想要在表被创建后，再补上约束，可以使用 ALTER
    1. 语法
      1. ALTER TABLE 表名
      2. ADD UNIQUE （列名称）
    2. 示例
      1. ALTER TABLE Persons
      2. ADD UNIQUE (Id_P)
  8. 如果要撤销 UNIQUE
    1. MySQL
      1. 语法
        
        ALTER TABLE 表名
        
        DROP INDEX 约束名称
      2. 示例
        
        ALTER TABLE Persons
        
        DROP INDEX uc_PersonID
    2. SQL Server / Oracle / MS Access
      1. 语法
        
        ALTER TABLE 表名
        
        DROP CONSTRAINT 约束名称
      2. 示例
        
        ALTER TABLE Persons
        
        DROP CONSTRAINT uc_PersonID
24. SQL PRIMARY KEY 约束
  1. PRIMARY KEY 约束唯一标识表的记录，一个表必须有一个 PRIMARY KEY，也只能有一个，PRIMARY KEY 不能为空，且值需要是唯一的
  2. 创建主键
    1. MySQL
      1. 语法
        
        PRIMARY KEY 列名称
      2. 示例
        
        CREATE TABLE Persons
        
        (
        
        Id_P int NOT NULL,
        
        FirstName varchar(255),
        
        LastName varchar(255),
        
        PRIMARY KEY (Id_P)
        
        )
    2. SQL Server / Oracle / MS Access
      1. 语法
        
        列名称值类型 PRIMARY KEY
      2. 示例
        
        CREATE TABLE Persons
        
        (
        
        Id_P int NOT NULL PRIMARY KEY,
        
        FristName varchar(255)
        
        )
  3. 如果需要给主键命名,MySQL / SQL Server / Oracle / MS Access 都一样
    1. 语法
      1. CONSTRAINT 约束名称 PRIMARY KEY （列名称）
    2. 示例
      1. CREATE TABLE Persons
        
        (
        
        Id_P int NOT NULL,
        
        FristName varchar(255),
        
        CONTRAINT pk_PersonID PRIMARY KEY (Id_P, LastName)
        
        )
  4. 给已存在的表创建主键约束
    1. 语法
      1. ALTER TABLE 表名称
      2. ADD PRIMARY KEY （列名称）
    2. 示例
      1. ALTER TABLE Persons
      2. ADD PRIMARY KEY (Id_P)
  5. 撤销主键
    1. MySQL
      1. 语法
        
        ALTER TABLE 表名称
        
        DROP PRIMARY KEY
      2. 示例
        
        ALTER TABLE Persons
        
        DROP PRIMARY KEY
    2. SQL Server / Oracle / MS Access
      1. 语法
        
        ALTER TABLE Persons
        
        DROP CONSTRAINT 约束名称
      2. 示例
        
        ALTER TABLE Persons
        
        DROP CONSTRAINT pk_PersonID
25. SQL FOREIGN KEY
  1. 外键，一个表的外键指向另外一个表的主键
  2. 可预防破坏表之间连接的动作
  3. 也可防止非法数据插入外键列，因为它必须是指向的表中的值之一
  4. 创建外键
    1. MySQL
      1. 示例
        
        CREATE TABLE Orders
        
        (
        
        Id_O int NOT NULL,
        
        OrderNo int NOT NULL,
        
        Id_P int,
        
        PRIMARY KEY (Id_O)
        
        FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
        
        )
    2. SQL Server / Oracle / MS Access
      1. CREATE TABLE Orders
        
        (
        
        Id_O int NOT NULL PRIMARY KEY,
        
        OrderNo int NOT NULL,
        
        Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
        
        )
  5. 命名外键
    1. 语法
      1. CONTRAINT fk_PerOrders
  6. 为已存在的表添加外键
    1. 语法
      1. ALTER TABLE 表1
      2. ADD FOREIGN KEY (列1）REFERENCES 表2（列2）
  7. 撤销外键
    1. MySQL
      1. 语法
        
        ALTER TABLE 表名称
        
        DROP FOREIGN KEY 约束名称
    2. SQL Server / Oracle / MS Access
      1. 语法
        
        ALTER TABLE 表名称
        
        DROP CONSTAINT 约束名称
26. SQL CHECK 约束
  1. 此约束用来限制表中某列的值范围，比例大于0的整数
  2. 语法
    1. CHECK （列名称表达式）
    2. CONSTRAINT 约束名称 CHECK （列名称表达式）
  3. 示例
    1. CREATE TABLE Persons
      1. (
        
        Id_P int NOT NULL CHECK (Id_P > 0),
        
        FirstName varchar(255) NOT NULL,
        
        LastName varchar(255),
      2. )
    2. CONSTRAINT chk_Person CHECK (Id_P>0 AND City=’Sandnes’)
    3. ALTER TABLE Persons
      1. ADD CHECK (Id_P>0)
      2. ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=’Sanders’)
      3. ALTER TABLE Persons
        
        ADD CHECK (Id_P>0)
        
        ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=’Sanders’)
    4. ALTER TABLE Persons
      1. DROP CHECK chk_Person
      2. DROP CONSTRAINT chk_Person
27. SQL DEFAULT 约束
  1. 用于向列中插入默认值
  2. 语法
    1. 列名称数据类型 DEFAULT 默认值（默认值支持系统函数，如GETDATE()）
  3. 示例
    1. CREATE TABLE Persons
      1. (
        
        Id_O int NOT NULL,
        
        OrderNo int NOT NULL,
        
        OrderDate date DEFAULT GETDATE(),
        
        City varchar(255) DEFAULT “Sandnes”,
      2. )
    2. ALTER TABLE Persons
      1. ALTER City SET DEFAULT ‘sandnes’
      2. ALTER City DROP DEFAULT ‘sandne’
28. SQL CREATE INDEX
  1. 用于给表创建索引，有索引的表搜索查询会更高效，但表更新会变慢，因为索引也要更新；因此，一般只给高频查询的表加索引
  2. 语法
    1. CREATE INDEX 索引名称
      1. ON TABLE 表名称（列名称）
    2. CREATE UNIQUE INDEX 索引名称
      1. 唯一索引，表示表中的两个行不能有相同的索引值
  3. 示例
    1. CREATE INDEX PersonIndex
      1. ON Person(LastName)
      2. ON Person(LastName DESC)，降序
      3. ON Person(LatsName, FirstName)，索引两列
29. SQL DROP 语句
  1. 用于删除表、索引、数据库等
  2. 语法
    1. DROP TABLE 表名称
    2. ALTER TABLE 表名称
      1. DROP INDEX 索引名称
    3. DROP DATABASE 数据库名称
    4. TRUNCATE TABLE 表名称（只删除数据，不删除表）
30. SQL ALTER 语句
  1. 用于在表中增加、删除、修改列
  2. 语法
    1. ALTER TABLE 表名称（增加列）
      1. ADD 列名称数值类型
    2. ALTER TALBE 表名称（删除列）
      1. DROP COLUMN 列名称
    3. ALTER TABLE 表名称（修改列的数据类型）
      1. ALTER COLUMN 表名称数据类型
  3. 疑问
    1. 如何修改列名称？
31. SQL AUTO_INCREMENT 语句
  1. 用来设定某个列，在每次插入新记录时，自动创建字段的值
  2. 语法
    1. MySQL
      1. CREATE TABLE Persons
        
        (
        
        P_Id int NOT NULL AUTO_INCREMENT,
        
        LastName varchar(255) NOT NULL,
        
        FirstName varchar(255),
        
        PRIMARY KEY (P_Id),
        
        )
  3. 说明
    1. 默认从1开始，每次递增1；如果想从100开始递增，则如下
      1. ALTER TABLE 表名称 AUTO_INCREMENT=100
    2. 当在表中插入新记录时，不必为递增的列赋值，它会自动创建值
32. SQL VIEW 视图
  1. 视图是基于 SQL 语句的结果集的可视化的表
  2. 注：视图总是显示最新的数据。每当用户查询视图时，数据库引擎通过 SQL 语句来重建数据
  3. 语法
    1. CREATE VIEWE 视图名称
      1. SELECT 列名称
      2. FROM 表名称
      3. WHERE 条件
  4. 示例
    1. 创建
      1. CREATE VIEW [Current Product List] AS
      2. SELECT ProductID, ProductName
      3. FROM Products
      4. WHERE Discontinued=No
    2. 查询
      1. SELECT * FROM [Current Product List]
    3. 撤消
      1. DROP VIEW 视图名称
33. SQL DATE 函数
  1. 如果数据只包含日期，情况就会很简单；如果包含时间，情况就会比较复杂，且不容易维护，因此若非必要，尽量避免使用时间，如果实在需要，建议拆分成两个字段来存储比较好
  2. 语法
    1. MySQL
      1. NOW()：返回当前的日期和时间
      2. CURDATE()：返回当前的日期
      3. CURTIME()：返回当前的时间
      4. DATE()：提取表达式的日期部分
      5. EXTRACT()：返回日期或时间的单独部分
      6. DATE ADD()：给日期添加时间间隔
      7. DATE SUB()：给日期减去时间间隔
      8. DATEDIFF()：返回两个日期之间的间隔
      9. DATE FORMAT()：用不同的格式显示日期
34. SQL NULL
  1. NULL 值表示未知数据，表中的列默认可以存储NULL值
  2. 使用 IS NULL 和 IS NOT NULL 来判断值是否为 NULL
  3. NULL 与 0 不等价，它们完全是两个概念
  4. NULL 不能使用一些运算符，例如 >, <, =
  5. 示例
    1. SELECT FirstName, LastName, Address FROM Persons
    2. WHERE Address IS NOT NULL
35. SQL NULL 函数
  1. ISNULL(), NVL(), IFNULL(), COALESCE()，这四个函数用来将 NULL 值转化为0，不过它们使用在不同的数据库中
  2. 语法
    1. ISNULL(列名称，0），微软
    2. NVA(列名称，0），Oracle
    3. IFNULL(列名称，0），或者 COALESCE(列名称，0），MySQL
  3. 示例
    1. SELECT ProductName, UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder, 0)
36. SQL 数据类型
  1. MySQL
    1. text 类型
      1. CHAR(size)，固定长度的字符串
      2. VARCHAR(size)，可变长度的字符串
      3. TINYTEXT，最大长度为255个字符的字符串
      4. TEXT，最大长度为65,535个字符的字符串
      5. MEDIUMTEXT，最大长度为16,777,215个字符的字符串
      6. LONGTEXT，最大长度为4,294,967,295个字符的字符串
      7. BLOB，用于BLOBs（Binary Large Objects，二进制大对象，常用来存储二进制的大文件，例如一张图片，一个音频等），最多存放65536字节的数据
      8. MEDIUMBLOB，最多16777215字节的数据
      9. LONGBLOB，最多4294967295字节的数据
      10. ENUM（x, y, z, etc.），允许你输入可能值的列表，可以在ENUM列表中列出最大65535个值；如果列表中不存在插入的值，则插入空值；这些值是按照输入的顺序存储的
      11. SET，与ENUM类似，SET最多只能包含65个列表项，不过SET 可存储一个以上的值；
    2. number 类型
      1. TINYINT(size)，常规，有正负，-128到+127；无正负符号，0到+128；可用 size 规定最大位数（符号通过添加UNSIGNED属性来标记）
      2. INT(size)，常规-32768到32767，无符号，0到65535
      3. SMALLINT(size)，常规-8388608到+8388607，无符号，0到16777215
      4. MEDIUMINT(size)，常规-2147483648到2147483647，无符号，0到4294967295
      5. BIGINT(size)，常规-9223372036854775808到9223372036854775807；无符号，0到18446744073709551615
      6. FLOAT(size, d)，带有浮动小数点的小数字，size 规定最大位数，d 规定小数点右侧的最大位数
      7. DOUBLE(size, d)，带有浮动小数点的大数字，同上
      8. DECIMAL(size, d)
        
        作为字符串存储的 DOUBLE 类型，小数点右侧位数固定
        
        size 用来指定最大位数，d 规定小数点右侧的最大位数
        
        最多支持28位，最后一位四舍五入
        
        好处：不存在精度的损失，常用于银行账目计算
        
        说明：数值存储范围越小的精度越高，存储数据范围越大，精度就越不准确
    3. date 类型
      1. DATE()：日期，格式YYYY-MM-DD，范围从1000-01-01到9999-01-01
      2. DATETIME()：时间，格式YYYY-MM-DD HH-MM-SS
      3. TIMESTAMP()：时间戳，使用Unix 纪元（1970-01-01 00:00:00 UTC）至今的描述来存储，格式：YYYY-MM-DD HH:MM:SS
      4. TIME()：时间，格式HH:MM:SS，支持范围从”-838:59:59”到”838:59:59”
      5. YEAR()：2位或4位格式的年，4位格式所允许的值为1901到2155；2位格式所允许的值：70到69，表示1970到2069
37. SQL 服务器 - RDBMS
  1. DBMS, database management system，数据库管理系统，提供各种函数用来对数据进行增删改查，使我们有能力在数据库中提取、修改或者存贮信息；
  2. RDBMS, relational database maganement system，关系数据库管理系统
38. SQL 函数
  1. 语法：SELECT 函数名(列) FROM 表名
  2. 合计类函数，Aggregate，例如：
    1. AVG（列名），返回某列的平均值；
    2. SUM（列名），返回某列的合计值；
    3. COUNT（列名），返回某列的行数（不含NULL值）
    4. MAX（列名），返回某列的最大值；
    5. MIN（列名），返回某列的最小值；
  3. 单一类函数，Scalar
    1. UCASE（c），将某个域转化成大写；
    2. LCASE（c），将某个域转化成小写；
    3. LEN（c），返回某个域的文本长度；
    4. ROUND（c，decimals），将某个数值域进行指定小数位数的四舍五入；
    5. MOD（x, y），返回除法操作的余数；
    6. NOW（），返回当前的系统日期；
    7. FORMAT（c, format），改变某个域的显示方式；
    8. DATEDIFF（d, date1, date2），用于执行日期计算；
39. SQL AVG 函数
  1. 用途：返回表格中“数值”列的平均值，NULL值不列入计算；
  2. 语法
    1. SELECT AVG（列名） FROM 表名
  3. 示例
    1. SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
      1. 结果集：OrderAverage, 950
    2. SELECT Customers FROM Orders
      1. WHERE OrderPrice > (AVG(OrderPrice) FROM Orders)
40. SQL COUNT 函数
  1. 用途：返回匹配指定条件的行数
  2. 语法：
    1. SELECT COUNT（列名） FROM 表名
    2. SELECT COUNT （*） FROM 表名，返回表中的记录数（所有行都计算在内）；
    3. SELECT COUNT （DISTINCT 列名），返回指定列不同值的数目（即值相同会被视为忽略，只计算一次）
  3. 示例
    1. SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer=”Carter”
    2. SELECT COUNT(*) AS NumberOfOrders FROM Orders
  4. 注意
    1. count 之后有带一个 AS，用来对结果集进行列标题的命名；
41. SQL FIRST 函数
  1. 用途：返回符合指定条件的查找结果集的指定列的第一个值（注：可以使用ORDER BY 对结果集进行排序）
  2. 语法：
    1. SELECT FIRST（列名） FROM 表名
  3. 示例
    1. SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
42. SQL LAST 函数
  1. 用途：返回符合指定条件的查询结果集的指定列的最后一个值（注：可以使用 ORDER BY 结果集进行排序）
  2. 语法
    1. SELECT LAST（列名） FROM 表名
  3. 示例
    1. SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
43. SQL MAX 函数
  1. 用途：返回指定列的最大值（注：也可以用于文本列，可获得按字母排序的最大或最小值，NULL 值不计算在内）
  2. 语法
    1. SELECT MAX（列名）AS 新列名 FROM 表名
  3. 示例
    1. SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
44. SQL MIN 函数
  1. 用途：返回指定列的最小值（注：可用于文本列，NULL 值不计算在内）
  2. 语法
    1. SELECT MIN（列名） AS 新列名 FROM 表名
  3. 示例
    1. SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
45. SQL SUM 函数
  1. 用途：返回指定列的总和（注：只能是数值列）
  2. 语法
    1. SELECT SUM（列名） AS 新列名 FROM 表名
  3. 示例
    1. SELECT SUM(OrderPrice) AS TotalPrice FROM Orders
46. SQL GROUP BY 语句
  1. 用途：用于对结果集，按指定列（一个或多个）的条件，进行分类统计
  2. 语法
    1. 单列
      1. SELECT 列名1，合计函数（列名2） FROM 表名 GROUP BY 列名1
    2. 多列
      1. SELECT 列名1，列名2，合计函数（列名3） FROM 表名 GROUP BY 列名1，列名2
  3. 示例
    1. 单列
      1. SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer
    2. 多列
      1. SELECT Customer, Orderdate, SUM(OrderPrice) FROM Orders GROUP BY Customer, Orderdate
47. SQL HAVING 子句
  1. 用途：解决合计函数和 WHERE 关键字无法一起使用的问题
  2. 语法
    1. SELECT 列名，合计函数（列名）FROM 表名 HAVING 合计函数（列名）条件
  3. 示例
    1. SELECT Customer, SUM(OrderPrice) FROM Orders
      1. GROUP BY Customer HAVING SUM(OrderPrice)<2000
    2. SELECT Customer, SUM(OrderPrice) FROM Orders
      1. WHERE Customer=’Bush’ OR Customer=’Carter’
      2. GROUP BY Customer
      3. HAVING SUM(OrderPrice)>1500
48. SQL UCASE 函数
  1. 用途：将字段的值转换成大写
  2. 语法：
    1. SELECT UCASE（列名） FROM 表名
  3. 示例
    1. SELECT UCASE(LastName) as LastName, FirstName FROM Persons
49. SQL LCASE 函数
  1. 用途：将字段的值转换成小写
  2. 语法
    1. SELECT LCASE（列名） FROM 表名
  3. 示例
    1. SELECT LACASE(FirstName) as FirstName, LastName FROM Persons
50. SQL MID 函数
  1. 用途：用于从字段中提取指定长度的字符
  2. 语法
    1. SELECT MID（列名，start（超始值，长度）） FROM 表名
    2. 长度的参数可选，如果不指定，表示从起始位置往后的所有剩余字符
  3. 示例
    1. SELECT MID(City, start(1,3)) AS ShortCity FROM Persons
51. SQL LEN 函数
  1. 用途：用于返回字符串的长度
  2. 语法
    1. SELECT LEN（列名） AS 新列名 FROM 表名
  3. 示例
    1. SELECT LEN(City) AS CityLength FROM Persons
52. SQL ROUND 函数
  1. 用途：用于将数值进行四舍五入到指定的小数位数
  2. 语法
    1. SELECT ROUND（列名，小数位数） FROM 表名
  3. 示例
    1. SELECT ProductName, ROUND(UnitPrice, 0) AS UnitPrice FROM Products
53. SQL NOW 函数
  1. 用途：用于获取当前的日期和时间（注：如果使用SQL，则应用 getdate 函数）
  2. 语法
    1. SELECT NOW() FROM 表名
  3. 示例
    1. SELECT Product, UnitPrice, NOW() as Perdate FROM Products
54. SQL FORMAT 函数
  1. 用途：用于将值按指定格式进行转化
  2. 语法
    1. SELECT FORMAT（列名，格式） FROM 表名
  3. 示例
    1. SELECT ProductName, UnitPrice, FORMAT(NOW(), ‘YYYY-MM-DD’) as PerDate FROM Products

工具

#SQL

SQL

https://ccw1078.github.io/2014/04/15/SQL/

作者

ccw

发布于

2014年4月15日

许可协议

数据库系统概念上一篇