高级操作

SELECT TOP字句

• 用于规定要返回的记录数目
• 对于拥有数千条记录的大型表来说，是非常有用的
• 语法：

  SELECT column_name(s) FROM table_name LIMIT number;
  

  

LIKE操作符

• LIKE操作符用于在WHERE字句中搜索列中的指定模式
• 语法：

  SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
  

  

通配符

• 通配符可以用于替代字符串中的任何其他字符
• 通配符与LIKE操作符一同使用 | 通配符 | 描述 | | ------------------------ | -------------------------- | | % | 替代0个或多个字符 | | _ | 替代一个字符 | | [charlist] | 字符列中的任何单一字符 | | ^charlist或!charlist | 不在字符列中的任何单一字符 |
• 使用技巧：
  • 不要过度使用通配符，如果其他操作能达到相同的目的，应该使用其他操作符
  • 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处 （把通配符置于开始处，搜索起来是最慢的）
  • 仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据

IN操作符

• IN操作符允许在WHERE字句中规定多个值
• 允许在IN中嵌套SELECT语句
• 语法

  SELECT column_name(s) FROM table_name WHERE column_name IN (values1, values2, ...);
  

  

BETWEEN操作符

• BETWEEN操作符选取介于两个值之间的数据范围内的值
• 值可以为
  • 数值
  • 文本
  • 日期
• 语法

  SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
  

  

  **请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
  在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
  在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。 在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。 因此，请检查您的数据库是如何处理 BETWEEN 操作符！

• 优点：
  • 在有很多合法选项时，IN操作符的语法更清楚、直观
  • 相对AND & OR，IN的求值顺序更容易管理
  • IN操作符一般比一组OR操作符执行更快
  • IN的最大优点是可以包含其他SELECT语句，可以更动态的建立WHERE字句

计算字段

CONCAT

SELECT Concat(string & column_name(s)) 
FROM table_name;

别名

• 在SQL中，可以位表名称或列名称指定别名
• 创建别名是为了让列名称的可读性更强
• 语法

  SELECT column_name(s) AS alias_name FROM table_name;
  SELECT column_name AS alias_name FROM table_name AS alias_name;
  

  
• 使用别名的用处：
  • 在查询中涉及超过一个表
  • 在查询中使用了函数
  • 列名称很长或可读性差
  • 需要把两个列或多个列结合在一起

连接(JOIN)

- JOIN字句用于把来自两个或多个的表行结合起来，基于这些表之间的共同字段

INNER JOIN

• INNER JOIN关键字在表中存在至少一个匹配时返回行
• 语法：

  SELECE column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name =table2.column_name;
  
  SELECE column_name(s) FROM table1 JOIN table2 ON table1.column_name =table2.column_name;
  

  

  在使用JOIN时，ON与WHERE条件的区别 1. on条件是在生成临时表时使用的条件，他不管ON中的条件是否位真，都会返回左边表中的记录 2. WHERE条件是在临时表生成好后，再进行条件的过滤

LEFT JOIN

• LEFT JOIN关键字从左表(table1)返回所有的行，即时右表(table2)中没有匹配
  • 如果右表中没有匹配，则结果为NULL
• 语法

  SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
  

  

RIGHT JOIN

• RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配
  • 如果左表中没有匹配，则结果为 NULL
• 语法：

  SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
  

  

FULL OUTER JOIN

• FULL OUTER JOIN关键字只要左表和右表其中一个表存在匹配，则返回行
• 结合了LEFT JOIN和RIGHT JOIN
• 语法：

  SELECT column_name(s) 
  FROM table1 
  FULL OUTER JOIN table2 
  ON table1.column_name = table2.column_name;
  

  MySQL不支持FULL OUTER JOIN

UNION操作符

• UNION操作符合并两个或多个SELECT语句的结果
• UNION内部的每个SELECT语句必须拥有相同数量的列
  • 列也必须拥有相似的数据类型
  • 同时每个SELECT语句中的列的顺序必须相同
• 语法：

  SELECT column_name(s) FROM table1
  UNION
  SELECT column_name(s) FROM tbale2;
  

  如果允许重复的值，使用UNION ALL UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名

SELECT INTO语句

• SELECT INTO将一个表复制信息到另一个表
• 语法：

  SELECT * 
  INTO newtable 
  [IN externaldb] FROM table1;
  
  SELECT column_name(s) 
  INTO newtable 
  [IN externaldb] FROM table1;
  

  

INSERT INTO SELECT

• INSERT INTO SELECT语句可以从一个表赋值数据，然后把数据插入到一个已存在的表中，目标表中任何已存在的行都不受影响
• 语法

  INSERT INTO table2 
  SELECT * FROM table1;
  
  INSERT INTO table2 (column_name(s)) 
  SELECT column_name(s) FROM table2;
  

  

SELECT INTO FROM与INSERT INTO SELECT语句的区别： - SELECT INTO FROM要求目标表不存在，因为在插入时会自动创建 - INSERT INTO SELECT要求目标表存在

CREATE TABLE

约束

• 约束用于规定表中的数据规则
• 约束可以在创建表时规定(CREATE TABLE)，或者在表创建之后规定(ALTER TABLE)
• CREATE TABLE + CONSTRAIN语法：

  CREATE TABLE table_name{
      column_name1 datatype(size) constrain_name;
      ...
      column_namen datatype(size) constrain_name;
  }
  

• 添加约束：ALTER TABLE + ADD CONSTRAIN语法：

  -- 添加单列约束
  ALTER TABLE table_name 
  ADD constrain_name column_name;
  
  -- 添加多列约束
  ALTER TABLE table_name 
  ADD CONSTRAIN constrain_name constrain_type (column_name(s));
  

• 修改约束：ALTER TABLE + MODIFY CONSTRAIN语法：

  ALTER TABLE table_name 
  MODIFY column_name datatype constrain_name;
  

• 删除约束：ALTER TABLE + DROP CONSTRAIN语法：

  ALTER TABLE table_name 
  DROP CONSTRAIN column_name 
  

• 约束名称：
  • NOT NULL：指示某列不能存储NULL值
  • UNIQUE：保证某列的每行必须有唯一值
  • PRIMARY KEY：NOT NULL与UNIQUE的结合，有助于更容易更快速的找到列表中的一个特定的记录
  • FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性
  • CHECK：保证列中的值符合指定的条件
  • DEFAULT：规定没有给列赋值时的默认值

NOT NULL

• NOT NULL约束强制列不接受NULL值
• 如果不向字段添加值，就无法插入新纪录或更新记录

UNIQUE约束

• UNIQUE约束唯一表示数据库表中的每条记录
• UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一性的保证

  每个表可以有多个UNIQUE约束，但是只能有一个PRIMARY KEY约束

• 语法：
  • 在创建表的时候添加约束

    -- mysql
    CREATE TABLE table_name{
        column_name1 datatype constrain_type,
        column_name2 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        UNIQUE (column_name(s))
    }
    
    CREATE TABLE table_name{
        column_name1 datatype constarin_type,
        column_name2 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        CONSTRAIN constrain_name 
        UNIQUE (column_name(s))
    }
    

  • 在已存在的表上添加约束

    ALTER TABLE table_name 
    ADD UNIQUE (column_name);
    
    ALTER TABLE table_name 
    ADD CONSTRAIN constrain_name UNIQUE (column_name(s));
    

  • 删除约束

    ALTER TABLE table_name 
    DROP INDEX constrain_name;
    

PRIMARY KEY

• PRIMARY KEY约束唯一标识数据库中的每条记录
• 每个表都应该有一个主键，并且每个表只能有一个主键
• 语法
  • 在创建表时添加约束

    -- 单列
    CREATE TABLE table_name{
        column_name1 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        PRIMARY KEY (column_name)
    }
    -- 多列
    CREATE TABLE table_name{
        column_name1 datatype constrain_name,
        ...
        column_namen datatype constrain_name,
        CONSTRAIN constrain_name 
        PRIMARY KEY (column_names)
    }
    

  • 在已存在的表上添加约束

    ALTER TABLE table_name 
    ADD PRIMARY KEY (column_name);
    
    ALTER TABLE table_name 
    ADD CONSTRAIN constrain_name PRIMARY KEY (column_name(s));
    

  • 撤销约束

    ALTER TABLE table_name 
    DROP PRIMARY KEY;
    

FOREIGN KEY

• 一个表中的FOREIGN KEY指向另一个表中的UNIQUE KEY（唯一约束的键）
• 语法
  • 在创建表时添加约束

    CREATE TABLE table_name{
        column_name1 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        FOREIGN KEY (column_name) 
        REFERENCES table_name(column_name)
    }
    

  • 在已存在的表上添加约束

    ALTER TABLE table_name 
    ADD FOREIGN KEY (column_name) 
    REFERENCES table_name(column_name);
    
    ALTER TABLE table_name 
    ADD CONSTRAIN constrain_name 
    FOREIGN KEY (column_name(s)) 
    REFERENCES table_name(column_name);
    

  • 撤销

    ALTER TABLE table_name
    DROP FOREIGN KEY constrain_name;
    

CHECK

• CHECK约束用于限制列中的值的范围
• 如果
  • 对单个列定义CHECK约束，那么该列只允许特定的值
  • 对多个列定义CHECK约束，那么此约束会基于行中其他的值在特定的列中对列进行限制
• 语法：
  • 在创建表时添加约束

    -- 单个
    CREATE TABLE table_name{
        column_name1 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        CHECK (column_name operation value)
    };
    --多个
    CREATE TABLE table_name{
        column_name1 datatype constrain_type,
        ...
        column_namen datatype constrain_type,
        CONSTRAIN cosntrain_name CHECK (column_name operaion value AND other_expression)
    };
    

  • 在已存在的表上添加约束

    ALTER TABLE table_name
    ADD CHECK (column_name operation value);
    
    ALTER TABLE table_name
    ADD CONSTRAIN constrain_name CHECK (column_name operaion value AND other_expression);
    

  • 撤销约束

    ALTER TABLE table_name
    DROP CHECK constrain_name;
    

DEFAULT

• DEFAULT约束用于向列中插入默认值
  • 如果没有其他值，那么将默认添加默认值
• 语法
  • 创建表时添加约束

    CREATE TABLE table_name{
        column_name1 datatype DEFAULT value|function,
        ...
        column_namen datatype constrain_type 
    }
    

  • 对已有的表添加约束
  • 撤销约束

CREATE INDEX

• CREATE INDEX语句用于在表中创建索引
• 用户无法看到索引，只能被用来加速搜索/查询
• 语法

  CREATE INDEX index_name
  ON table_name (column_name);
  
  CREATE UNIQUE INDEX index_name
  ON table_name (column_name);
  

  

DROP

• 撤销索引、撤销表以及撤销数据库
• 语法

  DROP type name;
  

  TRUNCATE TABLE table_name语句仅仅删除表内的数据，并不删除表本身

ALTER TABLE

• ALTER TABLE语句用于在已有的表中添加、删除或修改列
• 语法：
  • 添加列

    ALTER TABLE table_name
    ADD column_name datatype;
    

  • 删除列

    ALTER TABLE table_name
    DROP column_name;
    

  • 修改列的数据类型

    ALTER TABLE table_name
    MODIFY COLUMN column_name datatype;
    

AUTO_INCREMENT

• AUTO_INCREMENT 会在新记录插入表中时生成一个唯一的数字
• 语法

  CREATE TABLE table_name{
      column_name1 datatype constrain_name AUTO_INCREMENT,
      ....
      column_namen datatype constrain_name,
  }
  

视图

• 视图是基于SQL语句的结果集的可视化表
• 视图中的字段就是来自一个或多个数据库中的真实的表中的字段
• 可以向视图添加SQL函数、WHERE以及JOIN语句，也可以呈现数据（当作一个表来使用）
• 应用：
  • 重用SQL语句
  • 简化复杂的SQL操作
  • 使用表的一部分而不是全部
  • 保护数据，可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
  • 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
• 规则与限制
  • 视图必须唯一命名
  • 对于视图的数目没有限制
  • 创建视图，必须具有足够的访问权限
  • 视图可以嵌套
  • 许多DBMS禁止在视图查询中使用ORDER BY字句
  • 视图不能索引
  • 有些DBMS把视图作为只读的查询

CREATE VIEW

• 语法：

  CREATE VIEW view_name AS
  SELECT column_name(s)
  FROM table_name
  WHERE conditions;
  

  视图总是显示最新的数据，每当用户查询视图时，数据库引擎通过使用SQL语句重建数据

CREATE OR REPLACE VIEW

• 更新视图
• 语法

  CREATE OR REPLACE VIEW view_name AS
  SELECT column_name(s)
  FROM table_name
  WHERE condition;
  

DROP VIEW

• 删除视图

DATE函数

NULL函数

函数

文本处理函数

SOUNDEX是一个将任何文本串转为描述器语音表示的字母数字模式的算法，考虑了类似的发音字符和音节，是的能对字符串进行发音比较而不是字母比较

日期和时间处理函数

• 在mysql中使用year()ormonth等等进行日期查询

数值处理函数

聚集函数

• 对某些行运行的函数，计算并返回一个值

GROUP BY语句

• GROUP BY语句可以结合一些聚合函数来使用，
  • 根据一个或多个列对结果集进行分组
• 语法：

  SELECT column_name, aggregate_function(column_name)  
  FROM table_name  
  WHERE column_name operator value  
  GROUP BY column_name;
  

  
• 一些重要的规定：
  • GROUP BY字句可以包含任意数目的列，因而可以对分组进行嵌套，更细致的进行数据分组
  • 如果在GROUP BY字句中嵌套了分组，数据将在最后指定的分组上进行汇总（在建立分组时，指定的所有列都一起计算，所以不能从个别的列取回数据）
  • GROUP BY字句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY字句中指定相同的表达式，不能使用别名
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）
  • 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
  • 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回，如果列中有多行NULL值，她们将分为一组
  • GROUP BY字句必须出现在WHERE字句之后，ORDER BY字句之前

HAVING字句

• 增加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;
  

  HAVING与WHERE的区别 - WHERE在数据分组前进行过滤 - HAVING在数据分组后进行过滤

EXISTS运算符

• EXISTS运算符用于判断查询字句是否有记录
• 语法：

  SELECT column_name(s)
  FROM table_name
  WHERE EXISTS
  (SELECT column_name FROM table_name WHERE condition);
  

  

SELECT字句顺序