《MySQL必知必会》读书笔记,对基础数据库语句做了总结,方便以后查看
本篇包含前11章内容
使用MySQL
连接
- 主机名
- 端口(默认端口3306)
- 合法用户名
- 用户口令(密码)
选择数据库
USE <数据库名>;
了解数据库和表
SHOW DATABASES; -- 显示所有数据库列表
SHOW TABLES; -- 显示当前数据库的表的列表
SHOW COLUMNS FROM <表名>; -- 显示表中的列属性信息
检索数据
SELECT语句
SELECT <列名> FROM <表名>; -- 检索单个列(无序)
SELECT <列名1,列名2,...> FROM <表名>; -- 检索多个列
SELECT * FROM <表名>; -- 检索所有列
检索不同的行
SELECT DISTINCT <列名> FROM <表名>; -- 去重
限制结果
SELECT <列名> FROM <表名> LIMIT <N>; -- 返回结果不超过N行
SELECT <列名> FROM <表名> LIMIT <N,M>; -- 返回从第 N 行开始的 M 条结果
SELECT <列名> FROM <表名> LIMIT <M> OFFSET <N>; -- 返回从第 N 行开始的 M 条结果
使用完全限定的表名
SELECT <表>.<列> FROM <表>;
SELECT <表>.<列> FROM <数据库>.<表>;
排序检索数据
排序数据
SELECT <列> FROM <表> ORDER BY <列>;
SELECT <列1, 列2, ...> FROM <表> ORDER BY <列x, 列y, ...>;
指定排序方向
SELECT <列> FROM <表> ORDER BY <列> DESC;
SELECT <列1, 列2, ...> FROM <表> ORDER BY <列x> DESC <列y, ...>;
DESC
关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每个列指定DESC
关键字
与DESC相反的关键字是ASC(ASCENDING),升序是默认的
过滤数据
使用WHERE子句
SELECT <列...> FROM <表> WHERE <列> = <值>;
WHERE子句的位置:在同时使用ORDER BY
和WHERE
子句时,应该让ORDER BY
位于WHERE之后,否则将会产生错误
WHERE 子句操作符
操作符 | 说明 |
---|---|
= | 等于 |
<>、 != | 不等于 |
< 、 > | 小于、 大于 |
<= 、 >= | 小于等于、大于等于 |
BETWEEN | 在指定两个值直接 |
在匹配过程中,默认不区分大小写
SELECT <列...> FROM <表> WHERE <列> BETWEEN <X> AND <Y>; -- X<=...<=Y
- 空值检测
SELECT <列...> FROM <表> WHERE <列> IS NULL;
数据过滤
组合WHERE子句
MySQL允许给出多个WHERE
子句,可以以AND
子句的方式或OR
子句两种方式使用
SELECT <列...> FROM <表> WHERE <列1> = <值> AND <列2> = <值>;
SELECT <列...> FROM <表> WHERE <列1> = <值> OR <列2> = <值>;
SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符,可以用括号解决
IN操作符
功能和OR
相同,小括号里面是一个列表清单
SELECT <列...> FROM <表> WHERE <列> IN (<值1>, <值2>, ...);
- 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)
- IN操作符一般比OR操作符清单执行更快
- IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句
NOT操作符
NOT :WHERE
子句中用来否定后跟条件的关键字
MySQL 支 持 使 用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
用通配符进行过滤
LIKE操作符
%
表示任何字符出现任意次数,包括0次
SELECT <列...> FROM <表> WHERE <列> LIKE 'jet%'; -- 以jet开头的所有
请注意字符的头或者尾空格可能会干扰通配符匹配
下划线 _
匹配单个字符,用法和 %
类似
使用通配符技巧
通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
- 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
用正则表达式进行搜索
基本字符匹配
SELECT <列> FROM <表> WHERE <列> REGEXP '<正则表达式>';
REGEXP
后所跟的东西作为正则表达式处理,默认不区分大小写,需要区分时,在REGEXP
后面跟上BINARY
关键字。
- 进行OR匹配:使用
|
操作符,表示匹配其中之一,例如REGEXP '1000|2000'
- 匹配几个字符之一:使用一组用
[ ]
括起来的字符,例如REGEXP '[123]Tom'
- 匹配范围:使用
-
定义一个范围,例如[0-9]
表示数字0到9,a-z
匹配任意的字母字符 - 匹配特殊字符:使用
\\
作为前导,转义
元字符 | 说明 |
---|---|
\\f |
换页 |
\\n |
换行 |
\\r |
回车 |
\\t |
制表 |
\\v |
纵向制表 |
- 匹配字符类
类 | 说明 |
---|---|
[: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] ) |
- 匹配多个实例
元字符 | 说明 |
---|---|
* |
0个或多个匹配 |
+ |
1个或多个匹配(等于{1,} ) |
? |
0个或1个匹配(等于{0,1} ) |
{n} |
指定数目的匹配 |
{n,} |
不少于指定数目的匹配 |
{n,m} |
匹配数目的范围(m不超过255) |
例如:[[:digit:]]{4}
匹配连在一起的任意4位数字
- 定位符
元字符 | 说明 |
---|---|
^ |
文本的开始 |
$ |
文本的结尾 |
[[:<:]] |
词的开始 |
[[:>:]] |
词的结尾 |
例如:'^[0-9\\.]'
只在 .
或任意数字为串中第一个字符时才匹配它们
^的双重用途 ^
有两种用法。在集合中(用[
和]
定义),用它来否定该集合,否则,用来指串的开始处。
创建计算字段
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
拼接字段
拼接(concatenate):将值联结到一起构成单个值
多数DBMS使用+
或||
来实现拼接,MySQL则使用Concat()
函数来实现。
SELECT Concat(<列>,<字符或者字符串>,……) FROM <表> ORDER BY <列>;
Concat()
需要一个或多个指定的串,各个串之间用逗号分隔。字符串使用单引号括起来
Trim
函数 MySQL除了支持RTrim()
(去掉串右边的空格),还支持LTrim()
(去掉串左边的空格)以及Trim()
(去掉串左右两边的空格)。如, Trim(country_name)
使用别名
SELECT Concat(<列>,<字符或者字符串>,……) AS <别名> FROM <表> ORDER BY <列>;
也可以对表和列命名别名
执行算数计算
可以在SELECT
语句中对多个列进行加、减、乘、除、小括号操作,并给结果字段命名别名
SELECT pro_id, quantity, item_price, quantity*item_price AS expanded_price From orderitems WHERE order_num = 2005;
使用数据处理函数
使用函数
大多数SQL实现支持以下类型的函数
- 用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数
- 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数
- 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数
- 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数
文本处理函数
函数 | 说明 |
---|---|
Left() |
返回串左边的字符 |
Length() |
返回串的长度 |
Locate() |
找出串的一个子串 |
Lower() |
将串转换为小写 |
LTrim() |
去掉串左边的空格 |
Right() |
返回串右边的字符 |
RTrim() |
去掉串右边的空格 |
Soundex() |
返回串的SOUNDEX值 |
SubString() |
返回子串的字符 |
Upper() |
将串转换为大写 |
SOUNDEX
是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。
日期和时间处理函数
函 数 | 说 明 |
---|---|
AddDate() |
增加一个日期(天、周等) |
AddTime() |
增加一个时间(时、分等) |
CurDate() |
返回当前日期 |
CurTime() |
返回当前时间 |
Date() |
返回日期时间的日期部分 |
DateDiff() |
计算两个日期之差 |
Date_Add() |
高度灵活的日期运算函数 |
Date_Format() |
返回一个格式化的日期或时间串 |
Day() |
返回一个日期的天数部分 |
DayOfWeek() |
对于一个日期,返回对应的星期几 |
Hour() |
返回一个时间的小时部分 |
Minute() |
返回一个时间的分钟部分 |
Month() |
返回一个日期的月份部分 |
Now() |
返回当前日期和时间 |
Second() |
返回一个时间的秒部分 |
Time() |
返回一个日期时间的时间部分 |
Year() |
返回一个日期的年份部分 |
数值处理函数
函 数 | 说 明 |
---|---|
Abs() |
返回一个数的绝对值 |
Cos() |
返回一个角度的余弦 |
Exp() |
返回一个数的指数值 |
Mod() |
返回除操作的余数 |
Pi() |
返回圆周率 |
Rand() |
返回一个随机数 |
Sin() |
返回一个角度的正弦 |
Sqrt() |
返回一个数的平方根 |
Tan() |
返回一个角度的正切 |