本文将介绍 MySQL(My Structured Query Language)数据库的基本概念与以及在日常开发中的使用,包括对数据的增删改查操作、子查询、联结表、组合查询、存储过程、游标、触发器、事务管理等知识的总结。

基本概念

数据库(database):保存有组织的数据的容器,通常是一个文件或一组文件。

数据库管理系统(DBMS):用于管理数据库的软件。所以,MySQL 是一种 DBMS,即它是一种数据库软件。

表(table):某种特定类型数据的结构化清单,每个表都有一个名字唯一的表名。

列(column):表中的一个字段,所有表都是由一个或多个列构成的,且每个表列都有相应的数据类型。

行(row):表中的一个记录。

主键(primary key):一列(或一组列),其值能够 唯一区分 表中的每个行,也称为

表中的任意列都可以作为主键,且满足以下条件:

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

外键(foreign key):某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。

关键字(key word):即 MySQL 语言中的保留字,请不要使用关键字来命名一个表或列。

自动增量:通常用于当某些表中的列需要唯一值的时候。在每个行添加到表中时,MySQL 可以自动地为每个行分配下一个可用编号,不用在添加一行时手动分配唯一值。

子句(clause):SQL 语句由子句构成,有些子句是必须的,而有些子句是可选的。一个子句通常由一个关键字和所提供的数据组成。

NULL(no value):无值,它与字段包含 0、空字符串或仅仅包含空格不同。

操作符(operator):用来联结或改变 WHERE 子句中的子句的关键字,也称逻辑操作符(logical operator)。

通配符(wildcard):用来匹配值的一部分的特殊字符。

搜索模式(seach pattern):由字面值、通配符或两者组合构成的搜索条件。

聚集函数(aggregate function):运行在行组上,计算和返回单个值的函数。

完全限定列名:在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔表名和列名)。

笛卡尔积(cartesian priduct):由没有联结条件的表关系返回的结果称为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

基本命令

在 DOS 命令下使用 MySQL,以下命令分别指定了:用户名、密码、主机名以及端口号。

1
mysql -u username -p password -h myserver -P 9999;

注意:每条命令在最后使用英文;\g结束,使用quitexit退出命令行程序。

使用名为 crashcourse 的数据库:

1
USE crashcourse;

显示所有数据库:

1
SHOW DATABASES;

显示 TABLES 数据库内的表的列表:

1
SHOW TABLES;

显示 cuntomers 表中的列:(同DESCRIBE customers;语句)

1
SHOW COLUMNS FROM customers;

基本使用

检索数据

注意:SQL 语句不区分大小写,一般将所有的 SQL 关键字使用大写,而对所有的列和表名使用小写,这样更易于阅读和调试。此外,在处理 SQL 语句时,其中所有的空格都被忽略,一般将 SQL 语句分成多行,以便于阅读和调试。

从 products 表中检索一个名为 prod_name 的列:

1
2
SELECT prod_name
FROM products;

检索多个列

1
2
SELECT prod_id, prod_name, prod_price
FROM products;

使用通配符*检索所有列

1
2
SELECT *
FROM products;

使用关键字DISTINCT检索不同的行该关键字必须放在列名之前:

1
2
SELECT DISTINCT vend_id
FROM products;

使用LIMIT指示 MySQL 返回不多于指定的行数:

1
2
3
SELECT prod_name
FROM products
LIMIT 5;

指定要检索的开始行行数,第一个参数为开始的位置,第二个参数为要检索的行数

1
2
3
SELECT prod_name
FROM products
LIMIT 5,5;

使用完全限定的表名和列名:

products.prod_name表示products表下的prod_name列;

crashcourse.products表示crashcourse数据库下的products表。

1
2
SELECT products.prod_name
FROM crashcourse.products;

使用ORDER BY子句对prod_name列中的数据进行排序:

1
2
3
SELECT prod_name
FROM products
ORDER by prod_name;

对多个列进行排序,首先按照prod_price进行排序,然后按照prod_name进行排序:

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

使用DESC关键字进行降序(Z~A),先对prod_price进行降序,再对prod_name进行升序排序:

若不指定DESC关键字,则默认升序(A~Z)排序;升序排序的关键字为ASC

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

使用ORDER BYLIMIT找出prod_price列中的最大值:

注意:ORDER BY子句应在FROM语句之后,LIMIT应在ORDER BY子句之后。

1
2
3
4
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

过滤数据

使用WHERE子句中指定的搜索条件对数据进行过滤:

注意:ORDER BY子句应在WHERE之后。

WHERE 子句操作符:

=          等于
<>         不等于
!=         不等于
<          小于
<=         小于等于
>          大于
>=         大于等于
BETWEEN    介于指定的两个值之间,与 AND 配合使用
1
2
3
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 25.0;

WHERE子句中,如果是字符串类型的列,则需要用单引号''括起来,如果是数值类型的列,则不需要:

1
2
3
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

使用IS NULL子句返回没有价格(空的prod_price字段,而不是价格为 0)的所有产品:

1
2
3
SELECT prod_name
FROM products
WHERE prod_price IS NULL;

使用WHERE子句中的关键字AND来指示检索满足所有给定条件的行:

1
2
3
SELECT prod_id, prod_price, prod_name
FROM products
WHERE 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;

ANDOR联合使用引发的计算次序问题:

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

以上语句会被解释为:由供应商 1003 直到的任何价格为 10 美元(含)以上的产品,或者由供应商 1002 制造的任何产品,而不管其价格如何。

可是我想实现的是:列出价格为 10 美元(含)以上且由 1002 或 1003 制造的所有产品。

为什么会出现这样的情况呢?

原因是当AND关键字OR组合在一起使用时,AND的计算次序优先级较高。解决此问题的方法是使用括号即可,如下所示:

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

WHERE子句中使用IN操作符来指定条件范围,范围中的每个条件都可以进行匹配:

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

以上WHERE语句等价于使用OR关键字:WHERE vend_id = 1002 OR vend_id = 1003

使用IN操作符的好处:

  • 语法更清楚更直观;
  • 计算次序更容易管理;
  • IN操作符比OR操作符执行地更快;
  • IN操作符可以包含其它SELECT语句。

使用NOT关键字在WHERE子句中否定后跟条件:

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

通配符

使用通配符%找出以词jet起头的产品:

%表示任何字符出现任意次数,即 0 个字符、1 个或多个字符。

%不能匹配值为NULL的列。

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

使用_通配符匹配一个字符:

1
2
3
SELECT prod_id, prod_name
WHERE products
WHERE prod_name LIKE '_ to anvil';

数据处理函数

使用Concat()函数拼接两个列,即将多个串连接起来形成一个较长的串:

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

使用RTrim()函数删除数据右侧多余的空格:

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

使用关键字AS为列起别名:

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

使用+-*/执行算数计算:

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

使用Upper()函数将文本转换为大写:

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

常用文本处理函数:

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

聚集函数

常用聚集函数:

AVG()          返回某列的平均值
COUNT()        返回某列的行数
MAX()          返回某列的最大值,忽略列值为 NULL 的行
MIN()          返回某列的最小值,忽略列值为 NULL 的行
SUM()          返回某列值之和,忽略列值为 NULL 的行

COUNT()函数的使用方式:

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

使用组合聚集函数:

1
2
3
4
5
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;

分组数据

使用GROUP BY子句进行分组(对每个组进行聚集,而不是对整个结果进行聚集):

注意:

  • GROUP BY子句可以包含任意数目的列;
  • 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总;
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数);
  • 除聚集计算语句外,SELECT语句中的每个列都必粗在GROUP BY子句中给出;
  • 如果分组列中具有NULL值,则NULL将作为一个分组返回;
  • GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
1
2
3
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

之前的WHERE用于过滤行,而HAVING用于过滤分组:

WHEREHAVING的区别:

  • WHERE在数据分组前进行过滤;
  • HAVING在数据分组后进行过滤。
1
2
3
4
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >=2;

使用WHEREHAVING列出具有 2 个(含)以上、价格为 10(含)以上的产品的供应商:

1
2
3
4
5
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2;

ORDER BYGROUP BY的区别:

ORDER BY                    GROUP BY
排序产生的输出       分组行,但输出可能不是分组的排序
任意列都可以使用     只可能使用选择列或表达式列,而且必须使用每个选择列表达式
不一定需要           如果与聚集函数一起使用,则必须使用

检索总计订单价格大于等于 50 的订单的订单号和总计订单价格,并按总计订单价格排序输出:

1
2
3
4
5
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

子句顺序说明:

SELECT
FROM 
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

子查询

使用子查询

子查询总是从内向外处理。其次,在 WHERE 子句中使用子查询,应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。

1
2
3
4
5
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

联结表

使用联结的方式,确定要联结的所有表以及它们之间的关联关系:

其中:prod_nameprod_price在同一个表中,而vend_name在另一个表中。

1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

使用INNERJOIN关键字进行内部联结

1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

联结多个表:

1
2
3
4
5
SELECT prod_name, vend_name, peod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;

高级联结

给表名起别名可以缩短 SQL 语句以便于在单条 SELECT 语句中多次使用相同的表:

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';

使用自联结进行查询:

1
2
3
4
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, produvts AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

使用自然联结进行查询:

内部联结返回所有数据,甚至会多次出现相同的列。

自然联结排除多次出现,使每个列只返回一次,使用时仅能选择那些唯一的列。

1
2
3
4
5
6
SELECT c.*, o.order_num, o.order_date,
       oi.prod_id, oi.quantity, oi_item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';

使用LEFT OUTER关键字进行外部联结查询:

如果联结包含了那些在相关表中没有关联行的行,则这种类型的联结称为外部联结

在使用OUTER JOIN时,必须使用RIGHTLEFT关键字指定包括其所有行的表。

RIGHT指出的是OUTER JOIN右边的表,而LEFT指出的是OUTER JOIN左边的表。

1
2
3
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

组合查询

使用UNION操作符来组合数条 SQL 查询,即给出多条 SELECT 语句,将它们的结果组合成单个结果集:

注意:

  1. UNION必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字UNION分隔。

  2. UNION中的每个查询必须包含相同的列、表达式或聚集函数。

  3. 列数据类型必须兼容,类型不必完全相同,但必须是 DBMS 可以隐含的转换的类型。

  4. 使用UNION关键字能够将重复的行进行去除,而使用UNION ALL则显示所有重复的行。

  5. 如果使用ORDER BY关键字进行排序,则该关键字只能处于最后一条 SELECT 语句之后。

1
2
3
4
5
6
7
8
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001, 1002)
ORDER BY vend_id, prod_price;

插入数据

使用INSERT语句插入完整的一行:

注意:

  1. 不管是否给出表中的列名,都必须给出 VALUES 的正确数目。

  2. 如果想要使用INSERT语句插入完整的多行,可以使用多条INSERT语句,多条语句之间用分号间隔,从而一次提交它们。

1
2
INSERT INTO Customers
VALUES (NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);

更新数据

使用UPDATE语句更新表中特定的行或所有的行:

注意:

  1. 在执行更新语句时,不要忘记使用WHERE语句,切记。

  2. 在使用UPDATE语句更新多行时,假如在更新行中的一行或多行时出现一个错误,则整个UPDATE操作将被取消。此时可以使用IGNORE关键字,即使是发生了错误,也会继续更新。例如UPDATE INGNORE customers...

1
2
3
UPTATE customers
SET cust_email = 'elemer@fudd.com'
WHERE cust_id = 10005;

删除数据

使用DELETE语句删除特定的行或多行:

注意:

  1. 在执行更新语句时,不要忘记使用WHERE语句,切记。

  2. DELETE语句不需要列名或通配符,DELETE语句是删除整行而不是删除列。如果想要删除指定的列,则需使用UPDATE语句。

  3. 如果想要删除表中的所有行,应使用TRUNCATE TABLE语句,因为TRUNCATE实际上是删除原来的表并重新创建一个表,而不是逐行删除表中的数据。

1
2
DELETE FROM customers
WHERE cust_id = 10006;

创建和操作表

使用CREATE TABLE语句创建一张表:

NULL值是没有值或缺值,不是空串。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE customers
(
    cust_id      int       NOT NULL AUTO_INCREMENT,
    cust_name    char(50)  NOT NULL,
    cust_address char(50)  NULL,
    cust_city    char(50)  NULL,
    cust_state   char(5)   NULL,
    cust_zip     char(50)  NULL,
    cust_country char(50)  NULL,
    cust_contact char(50)  NULL,
    cust_email   char(255) NULL,
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

使用DEFAULT关键字在创建表时指定默认的值:

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
    order_num  int           NOT NULL,
    order_item int           NOT NULL,
    prod_id    char(10)      NOT NULL,
    quantity   int           NOT NULL DEFAULT 1,
    item_price decimal(8,2)  NOT NULL,
    PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

ENGINE=InnoDB语句用于指定 MySQL 使用InnoDB内部引擎,如下:

InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索。

MEMORY将数据存储在内存(而非磁盘)中,速度很快。

MyISAM是一个性能极高的引擎,支持全文本搜索,但不支持事务处理。

使用ALTER TABLE语句更新表,增加一个名为vend_phone的列:

1
2
ALTER TABLE vendors
ADD vend_phone CHAR(20);

使用ALTER TABLE语句删除表中的列:

1
2
ALTER TABLE vendors
DROP COLUMN vend_phone;

使用ALTER TABLE语句更新外键:

1
2
3
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

使用DROP TABLE删除表(删除整个表而不是其内容):

1
DROP TABLE customers2;

使用RENAME TABLE语句重命名一个表:

1
RENAME TABLE customers2 TO customers;

视图

视图是虚拟的表,与包含数据的表不同的是,视图是包含使用时动态检索数据的查询。

视图的使用(好处):

  1. 重用 SQL 语句,简化复杂的 SQL 操作;
  2. 使用表的组成部分而不是整个表;
  3. 保护数据、更改数据格式和表示形式。

视图的增删改查操作:

1
2
3
4
5
6
7
CREATE VIEW viewname;

DROP VIEW viewname;

CREATE OR REPLACE VIEW viewname;

SHOW CREATE VIEW viewname;

创建一个名为productcustomers的视图:

1
2
3
4
5
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

使用刚才创建的视图:

1
2
3
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

存储过程

存储过程是可以为以后的使用而保存的一条或多条 MySQL 语句的集合。

使用CALL执行存储过程,需要给出变量名:

查询时,使用类似于SELECT @pricelow;进行相应的查询。

1
2
3
CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

创建存储过程:

其中,BEGINEND用于限定存储过程体。

1
2
3
4
5
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

删除存储过程:

1
DROP PROCEDURE productpricing;

在存储过程使用变量:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2),
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END;

游标

使用游标可以在检索出来的行中前进或后退一行或多行,游标仅能用于存储过程。

使用DECLARE创建游标:

1
2
3
4
5
6
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR 
    SELECT order_num FROM orders;
END;

打开与关闭游标:

1
2
3
OPEN ordernumbers;

CLOSE ordernumbers;

触发器

触发器是 MySQL 响应DELETEINSERTUPDATE语句而自动执行(触发)的一条 MySQL 语句。

使用CREATE TRIGGER语句创建触发器:

该触发器在INSERT语句执行成功后才执行。

视图不支持触发器。

1
2
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

删除触发器:

1
DROP TRIGGER newproduct;

事务处理

  1. 事务(transaction):指一组 SQL 语句;
  2. 回退(rollback):指撤销指定 SQL 语句的过程;
  3. 提交(commit):指将未存储的 SQL 语句结果写入数据库表;
  4. 保留点(savepoint):指事务处理中设置的临时占位符,可以对它发布回退。

使用ROLLBACK来撤销 SQL 语句:

不能回退SELECT语句,不能回退CREATEDROP操作。

1
2
3
4
5
6
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

使用COMMIT进行明确的提交:

COMMIT语句仅在不出错时写出更改。

1
2
3
4
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

创建保留点,以便在回退时,MySQL 知道要回到何处:

1
SAVEPOINT delete1;

以上。