SQL必知必会学习
1.了解SQL
- SQL是一种专门用来与数据库通信的语言
- 数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）
- 表（table） 某种特定类型数据的结构化清单
- 模式（schema） 关于数据库和表的布局及特性的信
- 列（column） 表中的一个字段。所有表都是由一个或多个列组 成的。
- 数据类型（datatype） 所容许的数据的类型。每个表列都有相 应的数据类型，它限制（或容许）该列中存储的数据。
- 行（row） 表中的一个记录。
- 主键（primary key） 一一列（或一组列），其值能够唯一区分表 中每个行。
- 作为主键地条件
- 任意两行都不具有相同的主键值
- 每个行都必须具有一个主键值（主键列不允许NULL值）
- 习惯
- 不更新主键列中的值；
- 不重用主键列的值；
- 不在主键列中使用可能会更改的值。
- 作为主键地条件
2.了解MySQL
- MySQL是一种DBMS，即它是一种数据库软件
- DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS
- 客户机—服务器应用分为两个不同的部分。
- 服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机
- 客户机是与用户打交道的软件。
- 客户机—服务器应用分为两个不同的部分。
- MySQL Administrator（MySQL管理器）是一个图形交互客户机，用 来简化MySQL服务器的管理。
- Server Information（服务器信息）显示客户机和被连接的服务器的 状态和版本信息
- Service Control（服务控制）允许停止和启动MySQL以及指定服务 器特性；
- User Administration（用户管理）用来定义MySQL用户、登录和权 限；
- Catalogs（目录）列出可用的数据库并允许创建数据库和表
3.使用MySQL
- 连接
- 所需信息
- 主机名（计算机名）——如果连接到本地MySQL服务器，为localhost
- 端口（如果使用默认端口3306之外的端口）；
- 一个合法的用户名；
- 用户口令（如果需要）
- 选择数据库 use crashcourse;
- 了解数据库和表
- show databases;
- show table;
- show coloumns;
- DESCRIBE customers;是 SHOW COLUMNS FROM customers;的一种快捷方式。
- SHOW STATUS，用于显示广泛的服务器状态信息
- SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创 建特定数据库或表的MySQL语句
- SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安 全权限
- SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消
4.检索数据，SELECT语句
- 多条SQL语句必须以分号（；）分隔
- SQL语句不区分大小写，但有些标识符（如数据库名、表名、列名）可能不同
- 在处理SQL语句时，其中所有空格都被忽略
- 列名之间加上逗号
- 检索所有列（*）
- 最好不使用，检索不需要的列通常会降低检索和应 用程序的性能
- 能检索未知列
- SELECT DISTINCT 列名，只返回不同（唯一）的值
- 限制结果 用LIMIT子句
- 使用完全限定的表名
5.排序检索数据
- 指定列名排序-ORDER BY子句，位于FROM子句之后；LIMIT子句位于ORDER BY 之后
- 指定排序方向
- 降序排序必须指定DESC关键字
- 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字
- 使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值
select a
from b
order by a desc
limit 1
6.过滤数据
- 使用WHERE子句
- 只检索所需数据需要 指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）
- SQL过滤与应用过滤(看不懂？？？)
- WHERE子句的位置， 在同时使用ORDER BY和WHERE子句时，应 该让ORDER BY位于WHERE之后
- WHERE子句操作符
| 操 作 符 | 说 明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| > | 大于 |
| >= | 大于等于 |
| between | 在指定的两个值之间 |
- 空值检查
- IS NULL 无值，它与字段包含0、空字符串或仅仅包含 空格不同
- NULL与不匹配:在过滤数据时，一定要验证返回数据中确实给出了被 过滤列具有NULL的行
7.数据过滤
- 组合WHERE子句
- 操作符（operator） 用来联结或改变WHERE子句中的子句的关键 字。也称为逻辑操作符（logical operator）
- AND操作符：检索满足所有给定条件的行
- OR操作符：检索匹配任一条件的行
- 计算次序：使用圆括号明确地分组相应的操作符。
- IN操作符
- IN操作符与OR功能相同
- 优点
- 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观
- 在使用IN时，计算的次序更容易管理（因为使用的操作符更少
- IN操作符一般比OR操作符清单执行更快。
- IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建 立WHERE子句
select a
from b
where c IN（1002，1001）
order by a;
- NOT操作符
select a
from b
where c NOT IN（1002，1001）
order by a;
8.用通配符进行过滤
- 通配符（wildcard） 用来匹配值的一部分的特殊字符
- 搜索模式（search pattern） ① 由字面值、通配符或两者组合构 成的搜索条件。
- LIKE操作符
- % 表示任何字符出现任意次数
- _ 下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
- 使用通配符的技巧
- 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。
- 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据
select a
from b
where c like '_sajdnj%';
9.用正则表达式进行搜索 REGEXP子句
- 9.1本字符匹配
检索列c包含文本1000的所有行
select c
from b
where c REGEXP '1000';
- LIKE与REGEXP的重要差别
- 结果：lIKE '1000'不返回，数据REGEXP '1000'能返回
- 原因：，LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回（除非使用 通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回
- 补充：REGEX使用^和$定位符可匹配整个列值（从而起与LIKE相同作用）
- 正则表达式匹配不区分大小写，为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'
-
9.2 进行OR匹配 REGEX’1000 | 2000 |3000’将匹配1000或2000或3000
-
9.3匹配几个字符之一
- REGEX’[123] Ton’:[123]定义一组字符匹配1或2或3，因此，1 ton和2 ton都匹配且返回没有3 ton）
-
9.5匹配范围
- [123456789]
- [1-9],[a-z]
-
9.6匹配特殊字符
-匹配特殊字符，必须用\为前导。\\表示查找\，\-表示查找-，\.表示查找.。
-\也用来引用元字符（具有特殊含义的字符）元字符 说明 \f 换页 \n 换行 \r 回车 \t 制表 \v 纵向制表 -
9.7匹配字符类
- 存在找出你自己经常使用的数字、所有字母字符或所有数字字母字 符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类 （character class）
类 说 明 [:alnum:] 任意字母和数字（同[a-zA-Z0-9]） [:alpha:] 任意字符（同[a-zA-Z]） [:blank:] 空格和制表（同[\t]） [:cntrl:] ASCII控制字符（ASCII 0到31和127） [:digit:] 任意数字（同[0-9]） [:graph:] 与[:print:]相同，但不包括空格 [:lower:] 任意小写字母（同[a-z]） [:print:] 任意可打印字符 [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符 [:space:] 包括空格在内的任意空白字符（同[\f\n\r\t\v]） [:upper:] 任意大写字母（同[A-Z]） [:xdigit:] 任意十六进制数字（同[a-fA-F0-9]） -
9.8匹配多个实例
元 字 符 说 明 * 0个或多个匹配 + 1个或多个匹配（等于{1,}） ? 0个或1个匹配（等于{0,1}） {n} 指定数目的匹配 {n,} 不少于指定数目的匹配 {n,m} 匹配数目的范围（m不超过255） - 例子：
- 正则表达式\([0-9] sticks?\)
-结果：TNT (1 stick)
TNT (5 sticks)
-原因：\(匹配)，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\)匹配)。没有?，匹配stick和sticks会非常困难。 - 正则表达式[[:digit:]]{4}
结果: JetPack 1000
JetPack 2000
原因:，[:digit:]匹配任意数字，因而它为数字的一个集 合。{4}确切地要求它前面的字符（任意数字）出现4次，所以 [[:digit:]]{4}匹配连在一起的任意4位数字
- 正则表达式\([0-9] sticks?\)
- 例子：
-
9.9定位符
元 字 符 说 明 ^ 文本的开始 $ 文本的结尾 [[:<:]] 词的开始 [[:>:]] 词的结尾 -
例子：
- 正则表达式1
- 结果：.5 ton anvi1
1 ton anvi1
2 ton anvi1
- 匹配串的开始。因此，[0-9\.]只在.或任意数字为串中第 一个字符时才匹配它们。没有^，则还要多检索出4个别的行（那 些中间有数字的行）。- 补充：
- ^的双重用途 ^有两种用法。在集合中（用[和]定义），用它 来否定该集合，否则，用来指串的开始处。
- 使REGEXP起类似LIKE的作用 本章前面说过，LIKE和REGEXP 的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位 符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样。
- 补充：
10.创建计算字段
- 字段（field） 基本上与列（column）的意思相同，经常互换使 用，不过数据库列一般称为列，而术语字段通常用在计算字段的 连接上
- 客户机与服务器的格式 可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的.
- 拼接字段Concat()函数
- 拼接（concatenate） 将值联结到一起构成单个值
- 代码
select Concat(vend_name,'(',vend_country，'('） from ventors oder by vend_name - 结果：
ACME（USA)
ANVIL R US (USA) - 原因：Concat()拼接串，即把多个串连接起来形成一个较长的串。 Concat()需要一个或多个指定的串，各个串之间用逗号分隔 上面的SELECT语句连接以下4个元素：
- 存储在vend_name列中的名字
- 包含一个空格和一个左圆括号的串
- 存储在vend_country列中的国家
- 包含一个右圆括号的串
-Trim()函数:
- RTrim()函数去掉值右边的所有空格；
- LTrim()去掉串左边的空格；
- Trim()去掉串左右两边的空格
-
使用别名 用AS关键字赋予
- 代码
select Concat(vend_name,'(',vend_country，'('）AS A from ventors oder by vend_name - 常见的用途;
- 在实际的表列名包含不符合规定的字符（如空格）时重新命名它
- 在原来的名字含混或容易误解时扩充它
- 导出列 : 别名有时也称为导出列
- 代码
-
执行计算
11.使用数据处理函数
-
函数没有SQL的可移植性强
-
大多数SQL实现支持以下类型的函数:
- 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数
- 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算） 的数值函数
- 用于处理日期和时间值并从这些值中提取特定成分（例如，返回 两个日期之差，检查日期有效性等）的日期和时间函数
- 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本 细节）的系统函数
-
文本处理函数
函 数| 说 明 ---|--- Left() | 返回串左边的字符 Length() | 返回串的长度 Locate() | 找出串的一个子串 Lower() | 将串转换为小写 LTrim() | 去掉串左边的空格 Right() | 返回串右边的字符 RTrim() | 去掉串右边的空格 Soundex() | 返回串的SOUNDEX值 SubString() | 返回子串的字符 Upper() | 将串转换为大写- 补充 Soundex() ：
- SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对 SOUNDEX的支持
- Soundex(Y.Lie)
- 结果：Y Lee
- 补充 Soundex() ：
-
日期和时间处理函数
函 数 | 说 明
—|---
AddDate() | 增加一个日期（天、周等）
AddTime() | 增加一个时间（时、分等）
CurDate() | 返回当前日期
CurTime() | 返回当前时间
Date() | 返回日期时间的日期部分
DateDiff() | 计算两个日期之差
Date_Add() | 高度灵活的日期运算函数
Date_Format()| 返回一个格式化的日期或时间串
Day() | 返回一个日期的天数部分
DayOfWeek() | 对于一个日期，返回对应的星期几
Hour() | 返回一个时间的小时部分
Minute() | 返回一个时间的分钟部分
Month() | 返回一个日期的月份部分
Now() | 返回当前日期和时间
Second() | 返回一个时间的秒部分
Time() | 返回一个日期时间的时间部分
Year() | 返回一个日期的年份部分- 例子：
检索出2005年9月下的 所有订单- where Date(a) between ‘2005-09-01’ and ‘2005-09-30’
- where year(a) = 2005 and month(a) =9
- 例子：
-
数值处理函数
函 数 | 说 明
—|---
Abs() | 返回一个数的绝对值
Cos() | 返回一个角度的余弦
Exp() | 返回一个数的指数值
Mod() | 返回除操作的余数
Pi() | 返回圆周率
Rand() | 返回一个随机数
Sin() |返回一个角度的正弦
Sqrt() |返回一个数的平方根
Tan() |返回一个角度的正切
12.汇总数据
-
12.1 聚集函数
- 聚集函数（aggregate function） 运行在行组上，计算和返回单 个值的函数
- 检索例子;
- 确定表中行数（或者满足某个条件或包含某个特定值的行数
- 获得表中行组的和。
- 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
-
12.2 SQL聚集函数
函 数| 说 明 | 对NULL的计算 |
—|---
AVG() |返回某列的平均值| 忽略 |
COUNT() |返回某列的行数 | COUNT() 不忽略，COUNT(*) 忽略 |
MAX() |返回某列的最大值| 忽略 |包括返回文本列中的最大值
MIN() |返回某列的最小值| 忽略 |包括返回文本列中的最小值
SUM() 返回某列值之和 | 忽略 | -
12.2聚集不同值
- 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认 行为）
- 只包含不同的值，指定DISTINCT参数
-
12.3 组合聚集函数
- 建议取别名
13.分组数据
-
创建分组 GROUP BY子句
- 规定：
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组，数据将在后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
- 使用ROLLUP
- 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，
- 语法：GROUP BY a WITH ROLLUP
- 规定：
-
过滤分组 HAVING
- HAVING支持所有WHERE操作
-
分组和排序
ORDER BY GROUP BY 排序产生的输出 分组行。但输出可能不是分组的顺序 任意列都可以使用(甚至非选择的列也可以使用） 只可能使用选择列或表达式列，而且必须使用每个选择列表达式不而且必须使用每个选择 列表达式 不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用 -
SELECT子句顺序
子 句 是否必须使用 SELECT 要返回的列或表达式 是 FROM 从中检索数据的表 仅在从表选择数据时使用 WHERE 行级过滤 否 GROUP BY 分组说明 仅在按组计算聚集时使用 HAVING 组级过滤 否 ORDER BY 输出排序顺序 否 LIMIT 要检索的行数 否
14.使用子查询
-
子查询（subquery），即嵌套在其他查询中的查
-
语法：
1.where a in (select....); 2.select a, (select count(*) from b where c.e=d.e) AS f from g order by a;- 列必须匹配
- 虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、 不等于（<>）等。
- 子查询和性能 这里给出的代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效 的方法
-
作为计算字段使用子查询
- 相关子查询（correlated subquery） 涉及外部查询的子查询
- 需完全限定列名
- 逐渐增加子查询来建立查询
15.联结表
- 关系表；要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联
- 外键（foreign key） 外键为某个表中的一列，它包含另一个表 的主键值，定义了两个表之间的关系
- 优点：
- 供应商信息不重复，从而不浪费时间和空间
- 如果供应商信息变动，可以只更新vendors表中的单个记录，相 关表中的数据不用改动
- 由于数据无重复，显然数据是一致的，这使得处理数据更简单
- 优点：
- 可伸缩性（scale） 能够适应不断增加的工作量而不失败。设 计良好的数据库或应用程序称之为可伸缩性好
- 外键（foreign key） 外键为某个表中的一列，它包含另一个表 的主键值，定义了两个表之间的关系
- 联结
- 联结是一种机制，用来在一条SELECT语句中关联表
- 需维护引用完整性
- 创建联结
- 需完全限定列名
- WHERE子句的重要性
- 应该保证所有联结都有WHERE子句
- 笛卡儿积
- 内部联结
- 联结多个表
19.插入数据insert
- 插入方式：
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
20.更新和删除数据
-
update更新数据：
-
更新类型：
- 更新表中特定行
- 更新表中所有行。
-
update语句组成部分：
- 要更新的表
- 列名和它们的新值；
- 确定要更新行的过滤条件。
update customers set cust_name = 'the fund' cust_email = '465494@fudd.com' where cust_id =1005 -
在UPDATE语句中使用子查询
UPDATE语句中可以使用子查 询，使得能用SELECT语句检索出的数据更新列数据 -
IGNORE关键字
如果用UPDATE语句更新多行，并且在更新这些 行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 （错误发生前更新的所有行被恢复到它们原来的值）。为即使是发 生错误，也继续进行更新，可使用IIGNORE关键字，如下所示： UPDATE IGNORE customers… -
为了删除某个列的值，可设置它为NULL
-
-
DELETE 删除数据
- 删除类型：
- 从表中删除特定的行
- 从表中删除所有行
- 语法：
DELETE FROM customers WHERE cust_id =10006; - 不要省略WHERE子句
- 删除表的内容而不是表
- 更快的删除
- 如果想从表中删除所有行，不要使用DELETE。 可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更 快（TRUNCATE实际是删除原来的表并重新创建一个表，而不 是逐行删除表中的数据）。
- 删除类型：
-
更新和删除的指导原则
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE或DELETE语句。
- 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围
- 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进 行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不 正确。
- 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行
- 小心使用 MySQL没有撤销（undo）按钮。
0-9\. ↩︎
2218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
