MySQL数据库基础(一)

发表于

数据库： 是一个以某种有组织的方式存储的数据集合。

表： 某种特定类型数据的结构化清单。在相同的数据库中的每个表都有一个名字，用来标识自己，此名字是唯一的。

列： 表中的一个字段，所有表都是由一个或多个列组成的。

数据类型： 所容许的数据的类型。每个表列都有相应的数据类型，它限制该列中存储的数据。

行： 表中的一个记录。表中的数据是按行存储的。

主键： 能够唯一区分表中每个行。不是必需的，但是使用主键，可以保证更新或删除只涉及表中特定行。
表中的任何列都可以作为主键，只有它满足以下条件：

  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值(主键列不允许NULL值)

SQL 一种专门用来与数据库通信的语言。

使用MySQL

选择数据库

在执行任意数据库操作前，需要选择一个数据库，可使用USE关键字。必需先使用USE打开数据库，才能读取其中的数据。

例如，使用crashcourse数据库：

1
USE crashcourse;

查看数据库和表

SHOW DATABASES; 返回可用数据库的一个列表。包含在这个列表中的可能是MySQL内部使用的数据库。

SHOW TABLES; 返回当前选择的数据库内可用表的列表。

DESC customers; 显示表列，也可以使用如下命令：

1
SHOW COLUMNS FROM customers;

SHOW COLUMNS要求给出一个表名(上面中使用的是FROM customers)，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息以及其他信息。

SHOW STATUS; 用于显示广泛的服务器状态信息。

SHOW CREATE DATABASE db_name; 和 SHOW CREATE TABLE tbl_name;，分别用来显示创建特定数据库或表

SHOW GRANTS; 用来显示授予用户(所有用户或特定用户)的安全权限。

SHOW ERRORS;和SHOW WARNINGS; 用来显示服务器错误或警告信息。

检索数据

SELECT 语句用于从一个或多个表中检索信息。

检索单个列

所需的列名在SELECT关键字之后，FROM关键字指出从其中检索数据的表名。如下：

1
SELECT prod_name FROM products;

上述语句利用SELECT语句从products表中检索一个名为prod_name的列。

检索多个列

在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。

1
SELECT prod_id, prod_name, prod_price FROM products;

检索所有列

在实际列名的位置使用星号(*)通配符，则返回表中所有列。

1
SELECT * FROM products;

检索不同的行

使用DISTINCT关键字，它必须直接放在列名的前面。

1
SELECT DISTINCT vend_id FROM products;

DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。

限制结果

为了返回第一行或前几行，可使用LIMIT子句。

1
SELECT prod_name  FROM products LIMIT 5;

LIMIT 5表示返回不多于5行。

可指定要检索的开始行和行数，如下：

1
SELECT prod_name  FROM products LIMIT 5, 5;

LIMIT 5, 5表示返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。

所以，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

检索出来的第一行为行0而不是行1。因此LIMIT 1, 1将检索出第二行而不是第一行。

使用完全限定的表名

使用完全限定的名字来引用列（同时使用表名和列字）。

1
SELECT products.prod_name  FROM products;

表名也可以是完全限定的(假定products表位于crashcourse数据库中):

1
SELECT products.prod_name  FROM crashcoruse.products;

排序检索数据

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

1
2
SELECT prod_name FROM products 
ORDER BY prod_name;

上述语句对prod_name列以字母顺序排序数据。

按多个列排序

为了按多个列排序，只要指定列名，列名之间用逗号分开即可（就像选择多个列时所做的那样）。

1
2
SELECT prod_id, prod_price, prod_name FROM products 
ORDER BY prod_price, prod_name;

上述语句，按其中两个列对结果进行排序——首先按prod_price，然后再按prod_name排序。仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。

指定排序方向

默认是按升序排序(从A到Z)，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

1
2
SELECT prod_id, prod_price, prod_name FROM products 
ORDER BY prod_price DESC;

多个列排序，下面的例子以prod_price降序排序，然后再对产品名排序：

1
2
SELECT prod_id, prod_price, prod_name FROM products 
ORDER BY prod_price DESC, prod_name;

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列仍然按默认的升序排序。

过滤数据

使用WHERE字句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示：

1
2
SELECT prod_name, prod_price FROM products 
WHERE prod_price = 2.50;

这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为2.50的行。

WHERE字句的位置： 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

WHERE字句操作符

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间
检查单值
1
2
select prod_name, prod_price FROM products 
WHERE prod_name = 'fuses';

MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配。
单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不
用引号。

不匹配检查
1
2
SELECT vend_id, prod_name FROM products 
WHERE vend_id <> 1003;

等价于：

1
2
SELECT vend_id, prod_name FROM products 
WHERE vend_id != 1003;
范围值检查

为了检查某个范围的值，可使用BETWEEN操作符。它需要两个值，即范围的开始值和结束值。这两个值必须用AND关键字分隔。

1
2
SELECT prod_name, prod_price FROM products 
WHERE prod_price BETWEEN 5 AND 10;
空值检查

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。

NULL与字段包含0、空字符串或仅仅包含空格不同。

1
SELECT prod_name FROM products WHERE prod_price IS NULL;

数据过滤

组合WHERE字句

MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

AND操作符

1
2
SELECT prod_id, prod_price, prod_name FROM products 
WHERE vend_id = 1003 AND prod_price <= 10;

上述语句检索vend_id为1003且prod_price小于等于10的所有prod_id、prod_price、prod_name。

OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。

1
2
SELECT prod_name, prod_price FROM products 
WHERE vend_id = 1002 OR vend_id = 1003;

计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。

1
2
3
4
SELECT prod_name, prod_price FROM products 
WHERE vend_id = 1002 
OR vend_id = 1003 
AND prod_price >= 10;

在处理OR操作符前，优先处理AND操作符。上述语句等价于：

1
2
3
SELECT prod_name, prod_price FROM products 
WHERE vend_id = 1002 
OR (vend_id = 1003 AND prod_price >= 10);

如果要先处理OR操作符，可以修改为：

1
2
3
SELECT prod_name, prod_price FROM products 
WHERE (vend_id = 1002 OR vend_id = 1003) 
AND prod_price >= 10;

在WHERE子句中使用圆括号： 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。

IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。

1
2
3
SELECT prod_name, prod_price FROM products 
WHERE vend_id IN (1002, 1003) 
ORDER BY prod_name;

等价于：

1
2
3
SELECT prod_name, prod_price FROM products 
WHERE (vend_id = 1002 or vend_id = 1003)
ORDER BY prod_name;

IN比OR操作符的优点：

  • IN操作符的语法更清楚且更直观。
  • 计算的次序更容易管理（因为使用的操作符更少）。
  • IN操作符一般比OR操作符清单执行更快。
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

1
2
3
SELECT prod_name, prod_price FROM products 
WHERE vend_id NOT IN (1002, 1003) 
ORDER BY prod_name;

用通配符进行过滤

LIKE操作符

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号（%）通配符

%表示任何字符出现任意次数。例如，为了找出所有prod_name以jet开始的prod_id、prod_name，可使用以下SELECT语句：

1
2
SELECT prod_id, prod_name FROM products 
WHERE prod_name LIKE 'jet%';

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

1
2
SELECT prod_id, prod_name FROM products 
WHERE prod_name LIKE '%anvil%';

'%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

  • %还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符
  • %通配符不能匹配NULL

下划线（_）通配符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

1
2
SELECT prod_id, prod_name FROM products 
WHERE prod_name LIKE '_ ton anvil';

使用通配符的技巧

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

  • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  • 不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

用正则表达式进行搜索

正则表达式

元字符 说明
^ 文本的开始
$ 文本的结尾
. 除 “\n” 之外的任何单个字符
* 0个或多个匹配
+ 1个或多个匹配(等于{1,})
? 0个或1个匹配(等于{0,1})
a|b|c 匹配 a 或 b 或 c
[…] 匹配所包含的任意一个字符
[^…] 匹配未包含的任意字符
{n} 指定数目匹配
{n, } 不少于指定数目的匹配
{n, m} 匹配数目的范围(m不超过255)

基本匹配

检索列prod_name包含文本1000的所有行：

1
2
3
SELECT prod_name FROM products
WHERE prod_name REGEXP '1000' 
ORDER BY prod_name;

进行OR匹配

检索列prod_name包含文本1000或2000的所有行：

1
2
3
SELECT prod_name FROM products 
WHERE prod_name REGEXP '1000|2000' 
ORDER BY prod_name;

匹配几个字符之一

[123]定义一组字符，它的意思是匹配1或2或3:

1
2
3
SELECT prod_name FROM products 
WHERE prod_name REGEXP '[123] Ton' 
ORDER BY prod_name;

正则表达式[123] Ton为[1|2|3] Ton的缩写，也可以使用后者:

1
2
3
SELECT prod_name FROM products 
WHERE prod_name REGEXP '[1|2|3] Ton' 
ORDER BY prod_name;

匹配范围：

1
2
3
SELECT prod_name FROM products 
WHERE prod_name REGEXP '[1-5] Ton' 
ORDER BY prod_name;

[1-5]定义了一个范围，这个表达式意思是匹配1到5。

匹配特殊字符

为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。

1
2
3
SELECT vend_name FROM vendors 
WHERE vend_name REGEXP '\\.' 
ORDER BY vend_name;

匹配字符类

经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类。以下列出字符类以及它们的含义：

类 说明
[: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]）

匹配多个实例

1
2
3
SELECT prod_name FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)' 
ORDER BY prod_name;

[0-9]匹配任意数字，sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\\)匹配)

1
2
3
SELECT prod_name FROM products 
WHERE prod_name REGEXP '[[:digit:]]{4}' 
ORDER BY prod_name;

[:digit:]匹配任意数字，因而它为数字的一个集合，[[:digit:]]{4}匹配连在一起的任意4位数字。

创建计算字段

拼接字段

在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

1
2
3
SELECT Concat(vend_name, '(', vend_country, ')') 
FROM vendors 
ORDER BY vend_name;

Concat()需要一个或多个指定的串，各个串之间用逗号分隔。上面的SELECT语句连接以下4个元素：

  • 存储在vend_name列中的名字
  • 包含一个空格和一个左圆括号的串
  • 存储在vend_country列中的国家
  • 包含一个右圆括号的串

可以使用MySQL的RTrim()函数来删除数据右侧多余的空格：

1
2
3
SELECT Concat(vend_name, '(', RTrim(vend_country), ')') 
FROM vendors 
ORDER BY vend_name;

Trim函数： MySQL除了支持RTrim()（去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）

使用别名

SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。

1
2
3
4
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') 
AS vend_title 
FROM vendors 
ORDER BY vend_name;

执行算术运算

计算字段的另一常见用途是对检索出的数据进行算术计算。

1
2
3
4
SELECT prod_id, quantity, item_price, quantity*item_price 
AS expanded_price 
FROM orderitems
WHERE order_num = 20005;

expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算
列，就像使用其他列一样。

使用数据处理函数

文本处理函数

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

将vend_name转换为大写：

1
2
3
SELECT vend_name, Upper(vend_name) AS vend_name_upcase 
FROM vendors 
ORDER BY vend_name;

日期和时间处理函数

常用的日期和时间处理函数:

函数 说明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分 
1
2
SELECT cust_id, order_num FROM orders 
WHERE Date(order_date) = '2005-9-1';

不要将给出的日期与整个列值进行比较，必须使用Date()函数。因为列值可能为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败。

1
2
SELECT cust_id, order_num FROM orders 
WHERE order_date = '2005-9-1';

检索出2005年9月下的所有订单:

1
2
3
4
SELECT cust_id, order_num
FROM orders 
WHERE Date(order_date) 
BETWEEN '2005-9-1' AND '2005-9-30';

另外一种办法（一种不需要记住每个月中有多少天或不需要操心闰年2月的办法）：

1
2
3
4
SELECT cust_id, order_num 
FROM orders 
WHERE Year(order_date) = 2005 
AND Month(order_date) = 9;

Year()是一个从日期（或日期时间）中返回年份的函数。类似，Month()从日期中返回月份。因此，WHERE Year(order_date)= 2005 AND Month(order_date) = 9检索出order_date为2005年9月的所有行。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期—时间处理函数的使用那么频繁。

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

汇总数据

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

AVG()函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()函数忽略列值为NULL的行。

1
SELECT AVG(prod_price) AS avg_price FROM products;

AVG()也可以用来确定特定列或行的平均值。

1
2
3
SELECT AVG(prod_price) AS avg_price 
FROM products 
WHERE vend_id = 1003;

COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  • 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

返回customers表中的总数:

1
SELECT COUNT(*) AS num_cust FROM customers;

只对具有cust_mail的计数：

1
SELECT COUNT(cust_email) AS num_cust FROM customers;

MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名。

MAX()返回products表中最贵的物品的价格:

1
SELECT MAX(prod_price) AS max_price FROM products;

MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名。

MIN()返回products表中最便宜物品的价格:

1
SELECT MIN(prod_price) AS max_price FROM products;

SUM()函数

SUM()用来返回指定列值的和（总计）。

1
2
3
SELECT SUM(quantity) AS items_ordered 
FROM orderitems 
WHERE order_num = 20005;

SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

1
2
3
SELECT SUM(quantity*item_price) AS total_price 
FROM orderitems 
WHERE order_num = 20005;

聚集不同值

下面所述内容在MySQL 4.x中不能正常运行。

使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

1
2
3
SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products 
WHERE vend_id = 1003;

DISTINCT必须使用列名，不能用于计算或表达式。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。

组合聚集函数

SELECT语句可根据需要包含多个聚集函数。

1
2
3
4
5
6
SELECT COUNT(*) 
AS num_items, MIN(prod_price) 
AS price_min, MAX(prod_price) 
AS price_max, AVG(prod_price) 
AS price_avg 
FROM products;