MySQL 的基本概念与使用
Contents
本文将介绍 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,以下命令分别指定了:用户名、密码、主机名以及端口号。
|
|
注意:每条命令在最后使用英文;
或\g
结束,使用quit
或exit
退出命令行程序。
使用名为 crashcourse 的数据库:
|
|
显示所有数据库:
|
|
显示 TABLES 数据库内的表的列表:
|
|
显示 cuntomers 表中的列:(同DESCRIBE customers;
语句)
|
|
基本使用
检索数据
注意:SQL 语句不区分大小写,一般将所有的 SQL 关键字使用大写,而对所有的列和表名使用小写,这样更易于阅读和调试。此外,在处理 SQL 语句时,其中所有的空格都被忽略,一般将 SQL 语句分成多行,以便于阅读和调试。
从 products 表中检索一个名为 prod_name 的列:
|
|
检索多个列
:
|
|
使用通配符*
检索所有列
:
|
|
使用关键字DISTINCT
检索不同的行
该关键字必须放在列名之前:
|
|
使用LIMIT
指示 MySQL 返回不多于指定的行数:
|
|
指定要检索的开始行
和行数
,第一个参数为开始的位置
,第二个参数为要检索的行数
:
|
|
使用完全限定
的表名和列名:
products.prod_name
表示products
表下的prod_name
列;
crashcourse.products
表示crashcourse
数据库下的products
表。
|
|
使用ORDER BY
子句对prod_name
列中的数据进行排序:
|
|
对多个列进行排序,首先按照prod_price
进行排序,然后按照prod_name
进行排序:
|
|
使用DESC
关键字进行降序(Z~A),先对prod_price
进行降序,再对prod_name
进行升序排序:
若不指定
DESC
关键字,则默认升序(A~Z)排序;升序排序的关键字为ASC
。
|
|
使用ORDER BY
和LIMIT
找出prod_price
列中的最大值:
注意:
ORDER BY
子句应在FROM
语句之后,LIMIT
应在ORDER BY
子句之后。
|
|
过滤数据
使用WHERE
子句中指定的搜索条件对数据进行过滤:
注意:
ORDER BY
子句应在WHERE
之后。WHERE 子句操作符:
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 介于指定的两个值之间,与 AND 配合使用
|
|
在WHERE
子句中,如果是字符串类型的列,则需要用单引号''
括起来,如果是数值类型的列,则不需要:
|
|
使用IS NULL
子句返回没有价格(空的prod_price
字段,而不是价格为 0)的所有产品:
|
|
使用WHERE
子句中的关键字AND
来指示检索满足所有给定条件的行:
|
|
使用OR
检索匹配任一条件的行:
|
|
由AND
和OR
联合使用引发的计算次序问题:
|
|
以上语句会被解释为:由供应商 1003 直到的任何价格为 10 美元(含)以上的产品,或者由供应商 1002 制造的任何产品,而不管其价格如何。
可是我想实现的是:列出价格为 10 美元(含)以上且由 1002 或 1003 制造的所有产品。
为什么会出现这样的情况呢?
原因是当AND
关键字OR
组合在一起使用时,AND
的计算次序优先级较高。解决此问题的方法是使用括号即可,如下所示:
|
|
在WHERE
子句中使用IN
操作符来指定条件范围,范围中的每个条件都可以进行匹配:
|
|
以上WHERE
语句等价于使用OR
关键字:WHERE vend_id = 1002 OR vend_id = 1003
。
使用IN
操作符的好处:
- 语法更清楚更直观;
- 计算次序更容易管理;
IN
操作符比OR
操作符执行地更快;IN
操作符可以包含其它SELECT
语句。
使用NOT
关键字在WHERE
子句中否定后跟条件:
|
|
通配符
使用通配符%
找出以词jet
起头的产品:
%
表示任何字符出现任意次数,即 0 个字符、1 个或多个字符。但
%
不能匹配值为NULL
的列。
|
|
使用_
通配符匹配一个字符:
|
|
数据处理函数
使用Concat()
函数拼接两个列,即将多个串连接起来形成一个较长的串:
|
|
使用RTrim()
函数删除数据右侧多余的空格:
|
|
使用关键字AS
为列起别名:
|
|
使用+
、-
、*
、/
执行算数计算:
|
|
使用Upper()
函数将文本转换为大写:
|
|
常用
文本
处理函数:
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 值。
使用组合聚集函数:
|
|
分组数据
使用GROUP BY
子句进行分组(对每个组进行聚集,而不是对整个结果进行聚集):
注意:
GROUP BY
子句可以包含任意数目的列;- 如果在
GROUP BY
子句中嵌套了分组,数据将在最后规定的分组上进行汇总;GROUP BY
子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数);- 除聚集计算语句外,
SELECT
语句中的每个列都必粗在GROUP BY
子句中给出;- 如果分组列中具有
NULL
值,则NULL
将作为一个分组返回;GROUP BY
子句必须出现在WHERE
子句之后,ORDER BY
子句之前。
|
|
之前的WHERE
用于过滤行,而HAVING
用于过滤分组:
WHERE
与HAVING
的区别:
WHERE
在数据分组前进行过滤;HAVING
在数据分组后进行过滤。
|
|
使用WHERE
和HAVING
列出具有 2 个(含)以上、价格为 10(含)以上的产品的供应商:
|
|
ORDER BY
和GROUP BY
的区别:
ORDER BY GROUP BY
排序产生的输出 分组行,但输出可能不是分组的排序
任意列都可以使用 只可能使用选择列或表达式列,而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用,则必须使用
检索总计订单价格大于等于 50 的订单的订单号和总计订单价格,并按总计订单价格排序输出:
|
|
子句顺序说明:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
子查询
使用子查询
:
子查询总是从内向外处理。其次,在 WHERE 子句中使用子查询,应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。
|
|
联结表
使用联结
的方式,确定要联结
的所有表以及它们之间的关联关系:
其中:
prod_name
和prod_price
在同一个表中,而vend_name
在另一个表中。
|
|
使用INNER
、JOIN
关键字进行内部联结
:
|
|
联结
多个表:
|
|
高级联结
给表名起别名
可以缩短 SQL 语句以便于在单条 SELECT 语句中多次使用相同的表:
|
|
使用自联结
进行查询:
|
|
使用自然联结
进行查询:
内部联结
返回所有数据,甚至会多次出现相同的列。
自然联结
排除多次出现,使每个列只返回一次,使用时仅能选择那些唯一的列。
|
|
使用LEFT OUTER
关键字进行外部联结
查询:
如果联结包含了那些在相关表中没有关联行的行,则这种类型的联结称为
外部联结
。在使用
OUTER JOIN
时,必须使用RIGHT
或LEFT
关键字指定包括其所有行的表。
RIGHT
指出的是OUTER JOIN
右边的表,而LEFT
指出的是OUTER JOIN
左边的表。
|
|
组合查询
使用UNION
操作符来组合数条 SQL 查询,即给出多条 SELECT 语句,将它们的结果组合成单个结果集:
注意:
UNION
必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字UNION
分隔。
UNION
中的每个查询必须包含相同的列、表达式或聚集函数。列数据类型必须兼容,类型不必完全相同,但必须是 DBMS 可以隐含的转换的类型。
使用
UNION
关键字能够将重复的行进行去除,而使用UNION ALL
则显示所有重复的行。如果使用
ORDER BY
关键字进行排序,则该关键字只能处于最后一条 SELECT 语句之后。
|
|
插入数据
使用INSERT
语句插入完整的一行:
注意:
不管是否给出表中的列名,都必须给出 VALUES 的正确数目。
如果想要使用
INSERT
语句插入完整的多行,可以使用多条INSERT
语句,多条语句之间用分号间隔,从而一次提交它们。
|
|
更新数据
使用UPDATE
语句更新表中特定的行或所有的行:
注意:
在执行更新语句时,不要忘记使用
WHERE
语句,切记。在使用
UPDATE
语句更新多行时,假如在更新行中的一行或多行时出现一个错误,则整个UPDATE
操作将被取消。此时可以使用IGNORE
关键字,即使是发生了错误,也会继续更新。例如UPDATE INGNORE customers...
|
|
删除数据
使用DELETE
语句删除特定的行或多行:
注意:
在执行更新语句时,不要忘记使用
WHERE
语句,切记。
DELETE
语句不需要列名或通配符,DELETE
语句是删除整行而不是删除列。如果想要删除指定的列,则需使用UPDATE
语句。如果想要删除表中的所有行,应使用
TRUNCATE TABLE
语句,因为TRUNCATE
实际上是删除原来的表并重新创建一个表,而不是逐行删除表中的数据。
|
|
创建和操作表
使用CREATE TABLE
语句创建一张表:
NULL
值是没有值或缺值,不是空串。
|
|
使用DEFAULT
关键字在创建表时指定默认的值:
|
|
ENGINE=InnoDB
语句用于指定 MySQL 使用InnoDB
内部引擎,如下:
InnoDB
是一个可靠的事务处理引擎,它不支持全文本搜索。
MEMORY
将数据存储在内存(而非磁盘)中,速度很快。
MyISAM
是一个性能极高的引擎,支持全文本搜索,但不支持事务处理。
使用ALTER TABLE
语句更新表,增加一个名为vend_phone
的列:
|
|
使用ALTER TABLE
语句删除表中的列:
|
|
使用ALTER TABLE
语句更新外键:
|
|
使用DROP TABLE
删除表(删除整个表而不是其内容):
|
|
使用RENAME TABLE
语句重命名一个表:
|
|
视图
视图
是虚拟的表,与包含数据的表不同的是,视图是包含使用时动态检索数据的查询。
视图的使用(好处):
- 重用 SQL 语句,简化复杂的 SQL 操作;
- 使用表的组成部分而不是整个表;
- 保护数据、更改数据格式和表示形式。
视图的增删改查
操作:
|
|
创建一个名为productcustomers
的视图:
|
|
使用刚才创建的视图:
|
|
存储过程
存储过程
是可以为以后的使用而保存的一条或多条 MySQL 语句的集合。
使用CALL
执行存储过程,需要给出变量名:
查询时,使用类似于
SELECT @pricelow;
进行相应的查询。
|
|
创建存储过程:
其中,
BEGIN
和END
用于限定存储过程体。
|
|
删除存储过程:
|
|
在存储过程使用变量:
|
|
游标
使用游标
可以在检索出来的行中前进或后退一行或多行,游标
仅能用于存储过程。
使用DECLARE
创建游标:
|
|
打开与关闭游标:
|
|
触发器
触发器
是 MySQL 响应DELETE
、INSERT
、UPDATE
语句而自动执行(触发)的一条 MySQL 语句。
使用CREATE TRIGGER
语句创建触发器:
该触发器在
INSERT
语句执行成功后才执行。视图不支持触发器。
|
|
删除触发器:
|
|
事务处理
- 事务(transaction):指一组 SQL 语句;
- 回退(rollback):指撤销指定 SQL 语句的过程;
- 提交(commit):指将未存储的 SQL 语句结果写入数据库表;
- 保留点(savepoint):指事务处理中设置的临时占位符,可以对它发布回退。
使用ROLLBACK
来撤销 SQL 语句:
不能回退
SELECT
语句,不能回退CREATE
或DROP
操作。
|
|
使用COMMIT
进行明确的提交:
COMMIT
语句仅在不出错时写出更改。
|
|
创建保留点,以便在回退时,MySQL 知道要回到何处:
|
|
以上。