高级操作
SELECT TOP字句
- 用于规定要返回的记录数目
- 对于拥有数千条记录的大型表来说,是非常有用的
- 语法:
LIKE操作符
LIKE
操作符用于在WHERE
字句中搜索列中的指定模式- 语法:
通配符
- 通配符可以用于替代字符串中的任何其他字符
- 通配符与
LIKE
操作符一同使用 | 通配符 | 描述 | | ------------------------ | -------------------------- | |%
| 替代0个或多个字符 | |_
| 替代一个字符 | |[charlist]
| 字符列中的任何单一字符 | |^charlist
或!charlist
| 不在字符列中的任何单一字符 | - 使用技巧:
- 不要过度使用通配符,如果其他操作能达到相同的目的,应该使用其他操作符
- 在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处 (把通配符置于开始处,搜索起来是最慢的)
- 仔细注意通配符的位置,如果放错地方,可能不会返回想要的数据
IN操作符
IN
操作符允许在WHERE
字句中规定多个值- 允许在
IN
中嵌套SELECT
语句 - 语法
BETWEEN操作符
BETWEEN
操作符选取介于两个值之间的数据范围内的值- 值可以为
- 数值
- 文本
- 日期
- 语法
**请注意,在不同的数据库中,BETWEEN 操作符会产生不同的结果!
在某些数据库中,BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中,BETWEEN 选取介于两个值之间且包括两个测试值的字段。 在某些数据库中,BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。 因此,请检查您的数据库是如何处理 BETWEEN 操作符! - 优点:
- 在有很多合法选项时,
IN
操作符的语法更清楚、直观 - 相对
AND & OR
,IN
的求值顺序更容易管理 IN
操作符一般比一组OR
操作符执行更快IN
的最大优点是可以包含其他SELECT
语句,可以更动态的建立WHERE
字句
- 在有很多合法选项时,
计算字段
CONCAT
别名
- 在SQL中,可以位表名称或列名称指定别名
- 创建别名是为了让列名称的可读性更强
- 语法
- 使用别名的用处:
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或可读性差
- 需要把两个列或多个列结合在一起
连接(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
- 如果右表中没有匹配,则结果为
- 语法
RIGHT JOIN
RIGHT JOIN
关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配- 如果左表中没有匹配,则结果为
NULL
- 如果左表中没有匹配,则结果为
- 语法:
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
语句中的列的顺序必须相同
- 语法:
如果允许重复的值,使用
UNION ALL
UNION
结果集中的列名总是等于UNION
中第一个SELECT
语句中的列名
SELECT INTO语句
SELECT INTO
将一个表复制信息到另一个表- 语法:
INSERT INTO SELECT
INSERT INTO SELECT
语句可以从一个表赋值数据,然后把数据插入到一个已存在的表中,目标表中任何已存在的行都不受影响- 语法
SELECT INTO FROM
与INSERT INTO SELECT
语句的区别: -SELECT INTO FROM
要求目标表不存在,因为在插入时会自动创建 -INSERT INTO SELECT
要求目标表存在
CREATE TABLE
约束
- 约束用于规定表中的数据规则
- 约束可以在创建表时规定(
CREATE TABLE
),或者在表创建之后规定(ALTER TABLE
) CREATE TABLE + CONSTRAIN
语法:- 添加约束:
ALTER TABLE + ADD CONSTRAIN
语法: - 修改约束:
ALTER TABLE + MODIFY CONSTRAIN
语法: - 删除约束:
ALTER TABLE + DROP CONSTRAIN
语法: - 约束名称:
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)) }
- 在已存在的表上添加约束
- 删除约束
- 在创建表的时候添加约束
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) }
- 在已存在的表上添加约束
- 撤销约束
- 在创建表时添加约束
FOREIGN KEY
- 一个表中的
FOREIGN KEY
指向另一个表中的UNIQUE KEY
(唯一约束的键) - 语法
- 在创建表时添加约束
- 在已存在的表上添加约束
- 撤销
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) };
- 在已存在的表上添加约束
- 撤销约束
- 在创建表时添加约束
DEFAULT
DEFAULT
约束用于向列中插入默认值- 如果没有其他值,那么将默认添加默认值
- 语法
- 创建表时添加约束
- 对已有的表添加约束
- 撤销约束
CREATE INDEX
CREATE INDEX
语句用于在表中创建索引- 用户无法看到索引,只能被用来加速搜索/查询
- 语法
DROP
- 撤销索引、撤销表以及撤销数据库
- 语法
TRUNCATE TABLE table_name
语句仅仅删除表内的数据,并不删除表本身
ALTER TABLE
ALTER TABLE
语句用于在已有的表中添加、删除或修改列- 语法:
- 添加列
- 删除列
- 修改列的数据类型
AUTO_INCREMENT
AUTO_INCREMENT
会在新记录插入表中时生成一个唯一的数字- 语法
视图
- 视图是基于SQL语句的结果集的可视化表
- 视图中的字段就是来自一个或多个数据库中的真实的表中的字段
- 可以向视图添加SQL函数、
WHERE
以及JOIN
语句,也可以呈现数据(当作一个表来使用) - 应用:
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的一部分而不是全部
- 保护数据,可以授予用户访问表的特定部分的权限,而不是整个表的访问权限
- 更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
- 规则与限制
- 视图必须唯一命名
- 对于视图的数目没有限制
- 创建视图,必须具有足够的访问权限
- 视图可以嵌套
- 许多DBMS禁止在视图查询中使用
ORDER BY
字句 - 视图不能索引
- 有些DBMS把视图作为只读的查询
CREATE VIEW
- 语法:
视图总是显示最新的数据,每当用户查询视图时,数据库引擎通过使用SQL语句重建数据
CREATE OR REPLACE VIEW
- 更新视图
- 语法
DROP VIEW
- 删除视图
DATE函数
NULL函数
函数
文本处理函数
SOUNDEX
是一个将任何文本串转为描述器语音表示的字母数字模式的算法,考虑了类似的发音字符和音节,是的能对字符串进行发音比较而不是字母比较
日期和时间处理函数
- 在mysql中使用
year()
ormonth
等等进行日期查询
数值处理函数
聚集函数
- 对某些行运行的函数,计算并返回一个值
GROUP BY语句
GROUP BY
语句可以结合一些聚合函数来使用,- 根据一个或多个列对结果集进行分组
- 语法:
- 一些重要的规定:
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
运算符用于判断查询字句是否有记录- 语法: