使用MySQL
USE
数据库名称;
:选择需要使用的数据库
SHOW DATABASES;
:返回可用数据库列表
SHOW TABLES;
:返回当前数据库中可用表的列表
SHOW COLUMNS FROM
表名;
:返回一个表中的内容
SHOW STATUS;
:显示服务器状态信息
SHOW GRANTS;
:用于显示授予用户的安全权限
SHOW ERRORS, SHOW WARNINGS;
:用于显示服务器的错误或警告信息
检索数据
SELECT prod_name FROM products; -- 从表products中检索列prod_name
SELECT prod_id, prod_name, prod_price FROM products; -- 从表products中检索多个列
SELECT * FROM products; --从表products中检索所有列,`*`代表通配符,表示匹配所有列
SELECT DISTINCT vend_id FROM products; -- 返回表中某列不同的行(即对于重复的值只输出一个),如果从多个列中检索不同的行,则会按输出最多的列来输出
SELECT prod_name FROM products LIMIT 5; -- 返回某列中的5行
SELECT prod_name FROM products LIMIT 5, 5; -- 返回从第5行开始的接续5行,前一个数为开始行,若返回的行数超过表中最大行数则返回最多的行
SELECT products.prod_name FROM products; -- 使用表名和列名的形式完全限定某列
排序检索数据
SELECT prod_name FROM products ORDER BY prod_name; --对ORDER BY后面的列数据进行排序,并输出排序后的检索列,此处检索列和排序列一致,也可以使用其他列来排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name; -- 表示先按照prod_price排序,再按照prod_name排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; -- 按价格降序排序,字串的降序为(Z-A),默认按升序(A-Z)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name; -- 表示按照prod_price字段降序排序后再按prod_name字段升序排列(默认),即DESC关键字只对其前面紧邻的关键字起作用
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; -- 组合使用,表示返回列中的最大值(此处为最贵的物品),通常用于查找最大值和最小值
在排序中大小写字母默认是一样的级别,如A
和a
顺序应该一样,若确实需要设置大小写不同的顺序,需要请求数据库管理员修改,无法通过ORDER BY
实现
过滤数据
在同时使用WHERE
和ORDER BY
时需要将ORDER BY
置于WHERE
的后面
SELECT prod_name FROM products WHERE prod_price = 2.5; -- 检索价格为2.5的行,其中=表示条件操作符,后面的过滤字段(WHERE后面的字段)可以和前面的检索字段(SELECT后面的字段)不一样
WHERE
的所有条件操作符如下表所示
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个之间 |
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'; -- fuses为字符串,需要使用单引号括起来
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; --检索价格在5到10之间的数据(包括开始5和结束10),数据可以是数值或日期等
SELECT prod_name FROM products WHERE prod_price IS NULL; -- 用于检查具有空值的列并返回所在的行,空值与0,空格,空字符串不同
数据过滤
多条件过滤数据,操作符:AND, OR, IN, NOT
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; -- 过滤同时满足两个条件的数据,两个条件可以是不同字段下的不同条件操作,返回满足条件的行
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003; -- 过滤满足任意一个条件的数据,返回满足条件的行
WHERE
中多个条件时的计算次序:当多个AND
和OR
混合使用时优先处理AND
操作符,如需要改变这种优先级,可以采用小括号的形式将需要优先执行的操作符包含
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; -- 该语句会先处理后面的AND操作符,再处理OR操作符
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; -- 该语句会先处理括号中的OR操作符,再处理AND操作符
在使用混合操作符的WHERE
子句中应该尽量使用小括号对其进行分组,避免产生歧义
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name; -- IN操作符表示过滤其前面的字段的值在后面括号中的数据,
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name; -- 表示不匹配IN后面清单中的数据,NOT操作符用于否定其后面的任何条件
IN
操作符与OR
操作符一致,具有的优点如下:
- 在使用长的合法的清单(小括号中的值)时,
IN
操作符的语法更清楚且更直观 - 在使用
IN
时,计算次序更容易管理 IN
操作符一般比OR
操作符清单执行更快IN
可以包含其他的SELECT
语句,使得能够更动态的建立WHERE
子句
用通配符过滤
通配符是用来匹配值的一部分的特数字符,要匹配的字符用单引号包括
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; -- %表示匹配任意字符,该语句表示检索prod_name中以jet开头的所有数据,%可以放置在多个位置用于匹配,%不能匹配NULL
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%jet%'; -- 表示匹配包含jet值的行
SELECT prod_name FROM products WHERE prod_name LIKE 's%e'; -- 匹配以s开头和e结尾的数据,%可以匹配0个、一个和多个字符
通配符_
,该通配符用法与%
一致,不同的是该符号有且只能匹配一个字符,即不可多余一个也不能没有
通配符使用技巧:
- 不要过度使用,如果其他操作可以实现同样的目的优先使用其他操作
- 除绝对必要外,不要把通配符置于搜索开始处,会导致搜索速度极大变慢
- 注意使用通配符的位置,错误的位置会导致错误的结果输出
正则表达式
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; -- 在REGEXP后面所跟的东西作为正则表达式,也可以使用.000,'.'表示任意字符,如果要区分大小写匹配,可以在REGEXP后面使用关键字BINARY
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'; -- '|'表示或的意思,即匹配行中包括1000或2000的数据,可以多次使用该符号,如'| a | b | c |'
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; -- 表示匹配方括号中任意一个字符与后面字符组合的数据,类似于`[ 1 | 2 | 3 ] Ton`,不同于`1 | 2 | 3`,前者表示一个集合,后者是字符集,会匹配整个字符串,如需要匹配集合以外的数据,可以使用`[^xxx]`,表示匹配`xxx`以外的数据
集合也可以使用连字符要表示,如[0-9]
表示匹配0到9中的任意一个数字,[a-z]
表示匹配任意一个小写字母、
如果需要匹配特殊字符. | [] - \
等,可以使用\\
来进行转移,如REGEXP '\\.';
:表示匹配包含.
的数据,\\
也可以用来引用元字符(具有特殊含义的字符)
元字符 | 说明 |
---|---|
\\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) |
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name; -- `\\(`和`\\)`表示匹配小括号`()`,`[0-9]`表示匹配数字,`?`表示前面有一个`s`或者没有,即可匹配的结果有`stick`和`sticks`
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name; -- `[:digit:]`表示任意数字,表示一个集合,`[[:digit:]]`表示匹配任意数字,`{4}`表示其前面的字符(数字)连续出现4次,该语句表示匹配连接在一起的连续4个数字(使用一个中括号也可以:[:digit:]{4}),功能同下语句
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;
定位元字符: 用于特定位置的匹配
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name; -- 表示匹配以数字或`.`开头的数据
注意'^[]'和'[^]'
的区别,前者为文本开头,后者为取反
创建计算字段
Concat()
函数:可用于拼接多个字段列之间的数据,如将第i
列和第j
列以某种形式拼接在一起输出到客户机,在客户机上做拼接操作会浪费大量时间,而在数据库检索时采用拼接操作会更加高效。
SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name; -- Concat用于拼接多个字符,输出结果为vend_name、(、vend_country、)拼接的结果,vend_name(vend_country),拼接时各字符用逗号隔开
RTrim()
函数:用于去掉数据右测多余的空格以整理数据,同时还可以使用LTrim()
和Trim()
来去掉左侧空格或两侧空格
列别名:将拼接的数据赋予别名,以便于客户机可以采用新的列名引用它
SELECT RTrim(vend_name) FROM vendors; -- 表示去掉检索到的vend_name字段数据的右测空格
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name; -- 将拼接后的vend_name(vend_country)数据命名为vend_title
算术计算:对检索出的数据进行算术计算
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
测试计算:可以使用SELECT
测试函数与计算,如SELECT 3*2;
返回6,SELECT NOW();
返回当前时间
MySQL
算术操作符包括加减乘除(+,-,*,/
),优先顺序与四则运算一致
数据处理函数
文本处理函数:
Upper()
函数:将本文转换为大写
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name; -- 将字段vend_name中的文本转换为大写并命名为vend_name_upcase
常用的文本处理函数
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
LTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX 值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
Soundex()
函数用于将文本串转换为描述其语音表示的字母数字模式的算法,可以对串进行发音比较而不是字母比较。
SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y.Lie'; -- 若cust_contact中存在`Y.Lie`,但错误输入不存在的`Y.Lee`时,无法匹配,可以使用Soundex()函数来进行发音匹配,如下
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lee'); -- 由于采用Soundex()函数,因此可以匹配到`Y.Lie`数据行
常用日期和时间函数,首选日期格式为:yyyy-mm-dd
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天、周等) |
AddTime() | 增加一个时间(时、分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年分部分 |
SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01'; -- 表示检索到日期为`2005-09-01`的数据,由于日期的数据类型为`datetime`,会包含日期和时间,如果时间会为`00:00:00`,及`2005-09-01 00:00:00`,上述匹配会成功,如果时间不为0的情况下,上述匹配将会失败,更好的做法如下
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01'; -- 仅提取日期时间的日期部分进行匹配
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; -- 匹配一个月内的数据
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9; -- 功能与上一条语句一样,该语句的优点是不需要记住每月有多少天
数值处理函数
一般主要用于代数、三角或几何运算,常用数值处理函数如下
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
汇总数据
AVG()
函数:该函数可以返回某列、某些行的均值,若需要对所有列求均值,需要多次使该函数,该函数忽略NULL
行
SELECT AVG(prod_price) AS avg_price FROM products; -- 返回prod_price列的均值并命名为avg_price
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003; -- 返回vend_id特定行(或几行)对应prod_price数据的均值,即返回某些行的均值
COUNT()
函数:用于返回表中行数和特定行的数目,COUNT(*)
表示统计所有行数,不管行中是否包含空值NULL
还是非空值,COUNT(column)
表示对特定列中所有值进行统计,忽略NULL
值
SELECT COUNT(*) AS num_cust FROM customers; -- 表示返回表customers中的总行数,即客户总数
SELECT COUNT(cust_email) AS num_cust FROM customers; -- 表示统计cust_email列中非空值的行数,即邮件地址不为空的客户总数
MAX()
函数:返回列中的最大值,需要指定列名
SELECT MAX(prod_price) AS max_price FROM products; -- 返回prod_price列中的最大值,即最贵的物品的价格,忽略值为NULL的行
MIN()
函数:功能与MAX()
函数相反,用于与之相同
SUM()
函数:返回指定列的和,用法和MAX()
函数相同
SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
组合聚集函数:组合使用多个函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS min_price,
MAX(prod_price) AS max_price,
AVG(prod_price) AS avg_price FROM products; -- 返回表的总行数,prod_price列的最小值、最大值和均值
分组数据
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; -- 可以对vend_id中的数据自动分组,使用GROUP BY子句时需要将其放在WHERE的后面,ORDER BY的前面,如果分组列中有NULL时,空值将会被单独分为一组
SELECT cust_id, COUNT(*) AS num_orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; -- 返回分组后数量大于等于2的分组,HAVING和WHERE不同,WHERE是先过滤再分组,而HAVING则是先分组再过滤
SELECT
子句顺序
子句(顺序) | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 否 |
FROM | 从检索数据的表 | 仅在从表中选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
子查询
适用于MySQL4.1
及以后的版本
多个查询语句嵌套使用,用于关系表中多表联合查询,查询结果由内向外逐步返回
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'); --先从表 orderitems 检索满足物品要求的 order_num, 在从表 orders 中检索满足 order_num 要求的 cust_id
相关子查询:涉及外部查询的子查询,列明产生歧义时需要使用完全限定列名,即表名.
列名
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name; --orders.cust_id = customers.cust_id表示从不同表中利用相同的列名检索(由于两个cust_id产生歧义,因此需要使用完全限定列名)
联结表
在多个表中通过字段之间的关系查询,根据关系分表存储具有节省内存资源、管理简单、修改方便的优点
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; --采用完全限定列名实现关系查询,该方式称为等值查询,也可以使用内联语法代替WHERE子句
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
笛卡尔积:当等值连接不是WHERE
子句时,输出数量为两个表行数的乘积
多表联结
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 2005;
高级联结
给表起别名的优点:缩短SQL
语句;允许在单条SELECT
语句中多次使用相同的表
-- 别名的使用
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';
自联结:使用表别名实现表与自身的联结
-- 使用WHERE子句和子查询实现同一个表多次查询
SELECT prod_id , prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DINTR');
-- 使用别名实现自联结,同一个表(products)采用两个别名(p1,p2),此处必须指定是那个别名,若都是用products会产生歧义
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
外部联结
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; --与内部联结不同的是外部联结包含没有关联的行,使用该语法时必须使用LEFT或RIGHT关键字指定其包含所有行的表,LEFT指定从左边的表(customers)选择所有行
带聚集函数的联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; -- 统计每个客户的下单数量,不包括未下单的(0)
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; -- 统计每个客户的下单数量,包括未下单的(0)
组合查询
使用场景:在单个查询中从不同的表返回类似结构的数据;对单个表执行多个查询,按单个查询返回数据
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); -- 使用关键字UNOIN联合查询,输出结果为两次查询结果的并集
-- 与上述语句同等功能的WHERE子句实现
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001, 1002);
UNION
使用规则
- 必须由两条及以上的
SELECT
组成,语句之间使用关键字UNION
分隔 UNION
中每个查询必须包含相同的列、表达试或聚集函数(次序可以不同)- 列数据类型必须兼容
UNION ALL
:单独使用UNION
时查询的并集中重复的结果被取消,即只保留一条结果,如果想不取消重复的行可以使用UNION ALL
,该工作是WHERE
子句完成不了,即WHERE
子句总是取消重复的行
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002); -- 返回结果不取消
组合查询结果排序:可以使用ORDER BY
子句对查询结果进行排序,但该子句只能存在一个且必须放在最后一个SELECT
语句之后
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; -- 使用ORDER BY 排序
全文本搜索
启用全文本搜索:通常在创建表是启用全文本搜索,也可以在后续指定,仅支持MyISAM
数据库引擎
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
进行全文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit'); -- match() 用于指定被搜索的列,该值必须和FULLTEXT包含的值一致,如果指定多个列,需要按次序全部列出, Against() 指定要使用的搜索表达试,此处为要搜索的文本
-- 上述例子对应的LIKE子句实现
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';
全文本和LIKE
的区别:LIKE
检索返回的数据不是按顺序的,而全文本搜索返回按一定等级排序的数据,等级由行中词的数目、唯一词的数目、整个索引中词发总数以及包含该词的行的数目计算,且搜索速度快
扩展查询
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); -- 找出关于anvils的注释(不一定包含该词)
布尔文本搜索:检索要匹配的词、要排斥的词(如果某行包含该词则不返回,即使包含其他指定词也如此)、排列提示(指定某些词比其他词更重要,更重要的词等级更高)、表达试分组、其他内容(此种搜索方式可以在不使用FULLTEXT
情况下使用)
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); -- -rope*指出排除包含rope*的行,即使包含heavy也不返回该行
全文本布尔操作符
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含且增加等级值 |
< | 包含且减少等级值 |
() | 把词组成句子表达试(允许这些子表达式作为一个组被包含、排除和排列等) |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
"" | 定义一个短语(匹配整个短语以便包含或排除这个短语) |
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE); -- 搜索包含rabbit和bait的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE); -- 搜索包含rabbit和bait中至少一个词的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE); -- 搜索包含rabbit bait这个短语的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE); -- 搜索rabbit和carrot并增加前者等级,降低后者等级
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(combination)' IN BOOLEAN MODE); -- 搜索匹配safe和combination的行并降低后者等级
插入数据
INSERT
:用于插入行到数据库表,有如下几种方式:
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
-- 插入单行数据
INSERT INTO customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL); -- 根据表列次序插入数据,在数据为空时用NULL代替,不能不写,该种方式过于依赖次序,是一种不安全的方式,当表中结构改变后,需要大量修改插入语句,应使用如下更为安全的插入语句
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL); -- 该方式虽然繁琐,但是更安全,
省略列:以更安全的方式插入数据时,可以省略某些满足如下条件的列
- 列定义为允许
NULL
值(无或空值) - 在表的定义中该处默认值,如果在插入时不给定值,将使用默认值
-- 插入多行数据:可以使用多条INSERT语句插入多行数据,每条语句用分号结束,以下提供一条插入语句插入多行数据写法
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), ('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA'); -- 每组值使用一个圆括号括起来,中间用逗号隔开
-- 插入检索出的数据:如从将另一个表中的数据插入到当前表中(两个表具有相同的结构)
INSERT INTO customers (cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew; -- 检索出的数据按照前面插入的字段顺序依次插入,需要保证不能重复的列的值不重复,如cust_id, 否则插入出错,插入的数据行数为custnew中的数据,SELECT语句可以包含WHERE子句来过滤要插入的数据
更新和删除数据
更新(UPDATE
)数据两种方式:
- 更新表中特定行
- 更行表中所有行
UPDATE
基本组成:
- 要更新的表
- 列名和它们的新值
- 确定要更新行的过滤条件
注意使用WHERE
子句,否则将更新整个表
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; -- 更新id为10005客户的邮件地址
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; -- 更新多个列,使用逗号隔开,如果不加WHERE限制,将会更新所有行
-- 如果在更新多个行时遇到错误则会恢复到修改之前,可以使用IGNORE关键字来忽略其中的某些错误,使得其他的行可以更新成功
UPDATE IGNORE customers...
-- 为了删除某个值可以将其设置为NULL
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
删除(DELETE
)数据两种方式:
- 删除表中特定行
- 删除表中所有行
注意使用WHERE
子句,否则将删除整个表中的内容(不是表)
DELETE FROM customers WHERE cust_id = 10006; -- DELETE是删除整行而不是整列,如果需要删除整列可以使用UPDATE语句,如果想删除整个表中的内容可以使用速度更快的TRUNCATE TABLE语句,实质上是直接删除表后再重建相同的表,而不是逐行删除表中的数据
创建和操纵表
在使用CREATE TABLE
创建表时需要给出新表的名字,以及用逗号分隔的表列的名字和定义,创建新表时不能使用已经存在的表名,否则会出错
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(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
-- 如果想要在表名不存在时才创建新表,可以使用IF NOT EXISTS
CREATE TABLE tablename IF NOT EXISTS ...
-- 使用 NOT NULL:表示该列在插入数据或更新数据时必须要有值,否则操作将会报错
-- 使用 NULL:默认为NULL,表示该列的值可有可无
-- PRIMARY KEY:指定表的主键,表示该列的值在表(该列中)必须唯一且不能为NULL,若使用多个主键则多个列的组合必须唯一,创建多个主键可以使用逗号分隔开,如PRIMARY KEY (cust_id, cust_name)
-- AUTO_INCREMENT:当表列中只需要值唯一且没有其他意义时,如行编号,每增加一行数据就给一个新的不重复的编号,此时可以使用AUTO_INCREMENT,每个表只允许有一个AUTO_INCREMENT的列(通常作为主键),如果某次使用INSERT插入该列的值(合法)时,后续的自动增量将从手动插入的值开始增加,可以使用LAST_INSERT_ID()函数获得最后一个AUTO_INCREMENT值,如SELECT LAST_INSERT_iD();
-- 指定默认值
CREATE TABLE tablename
(
fieldname int NOT NULL DEFAULT 1 -- 在创建表示指定默认值,当不给该值赋值时默认使用1填充该值,MySQL只支持常量不支持函数作为默认值
) ENGINE=InnoDB;
引擎类型:MySQL
中有多种引擎,在使用诸如SELECT
语句时在引擎内部处理请求,不同的任务选择不同的引擎可以获得良好的功能和灵活性,在创建表时指定引擎,也可以不指定(即省略ENGINE=
语句)而使用默认值(很可能是MyISAM
)
InnoDB
:该引擎是一个可靠的事务处理引擎,不支持全文本搜索MyISAM
:该引擎是一个性能极高的引擎,支持全文本搜索,但不支持事务处理MEMORY
:该引擎在功能等同于MyISAM
,但数据存储在内存而不是磁盘中,所以操作速度很快,适用于临时表
外键不能跨引擎使用
更新表(ALTER TABLE
):在表中存储数据以后不宜再更改表,因此在设计表时需要考虑完整以便于后期不在对表进行大的改动,修改表结构时需要给出如下信息
- 在
ALTER TABLE
之后给出要更改的表名,表名必须存在,否则将会报错 - 所做更改的列表
ALTER TABLE vendors ADD vend_phone CHAR(20); -- 在vendors表中添加一个名为vend_phone的列,必须确定其数据类型
ALTER TABLE vendors DROP COLUMN vend_phone; -- 删除vendors表中的vend_phone的列
-- ALTER TABLE 一种常见的用途是定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
小心使用ALTER TABLE
,在做改动前应先做数据备份
删除表(DROP TABLE
):删除表而不是删除内容
DROP TABLE tablename; -- 删除表没有确认,删除后将永久删除该表,表不存在会报错
重命名表(RENAME TABLE
):
RENAME TABLE customers TO customers1; -- 重命名单个表(需要重命名的表必须存在)
RENAME TABLE customers TO backup_customers,
vendors TO backup_vendors,
products TO backup_products;
使用视图
适用于MySQL5
及以后的版本
- 重用
SQL
语句 - 简化
SQL
操作,在编写查询后,可以方便的重用它而不必知道它基本的查询细节 - 使用表的组成部分而不是整个表
- 保护数据,可以给用户授予表特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
视图相当于一个虚拟的表,本身不包含数据,而是包含根据需要检索数据的查询,在视图创建之后可与表基本相同的方式使用它们,视图的规则和限制如下:
- 与表一样,视图必须唯一命名
- 对于可以创建的视图数目没有限制
- 为了创建视图,必须具有足够的访问权限
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询中来构造一个视图、
ORDER BY
可以用在视图中,但如果从该视图检索数据的SELECT
语句中也包含ORDER BY
,那么该视图中的ORDER BY
将被覆盖- 视图不能索引,不能有关联的触发器或默认值
- 视图可以和表一起使用
视图的创建
- 视图用
CREATE VIEW
语句来创建 - 使用
SHOW CREATE VIEW viewname;
来查看创建视图的语句 - 用
DROP
删除视图,语法为DROP VIEW viewname;
- 更新视图时,可以先用
DROP
再用CREATE
,也可以直接用CREATE OR REPLACE VIEW
,后者表示如果要更新的视图不存在则创建一个视图,如果存在则替换掉存在的视图
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; -- 返回已订购了任意产品的所有客户的列表
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name; -- 按特定格式检索出的数据,如果需要多次使用这种格式的数据,创建一个视图
CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL; -- 过滤没有电子邮件的用户
使用视图与计算字段
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005; -- 检索某个订单中的物品,计算每种物品的总价格
CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems; -- 计算每个订单中每种物品的总价
更新视图:通常视图用于检索而不是更新
视图的可以使用INSERT, UPDATE, DELETE
对数据进行更新,更新一个视图将更新其基表,如果MySQL
不能正确地确定被更新的及数据,则不允许更新视图,即视图定义有如下操作则不能更新视图:
- 分组(使用
GROUP BY
和HAVING
) - 联结
- 子查询
- 并
- 聚集函数(
Min(), Count(), Sum()
) DISTINCT
- 导出(计算)列
使用存储过程
适用于MySQL5
及以后的版本,存储过程实际上是一种函数
优点:简单、安全、高性能;缺点:编写复杂、可能没有创建存储过程的权限,只有使用权限
执行存储过程:
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage); -- 计算并返回最低、最高和平均价格,productpricing为存储过程名称,括号内为接收的参数
-- 调用后不会显示数据,需要用SELECT语句检索数据
SELECT @pricelow; -- 检索单个值
SELECT @pricelow, @pricehigh, @priceaverage; -- 检索多个值
创建存储过程:
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products; -- 创建一个返回平均价格的存储过程
END;
-- 语句说明:
-- CREATE PROCEDURE productpricing()创建一个名为productpricing的存储过程,括号内可以写入参数也可以不写(不写时仍需要加上小括号)
-- BEGIN END用来限定存储过程体
当在命令行创建存储过程时如果在程序内使用;
,则需要临时改变语句分隔符,否则出现语法错误
-- 如上述的创建过程内使用`;`分隔符,这在命令行程序中回车后会产生错误,需要做如下修改
DELIMITER // -- 将结束分隔符临时改为// (只要不是/即可)
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END // -- 上面的创建语句使用新的结束分隔符,因此才程序内部使用`;`才能正确执行
DELIMITER ; -- 将结束分隔符修改为`;`
删除存储过程:
DROP PROCEDURE productpricing; -- 删除存储过程不需要加上小括号(),只需要给出存储过程名称即可
DROP PROCEDURE IF EXISTS productpricing; -- 表示存储过程存在时删除,不存在时不报错,上一条语句当存储过程不存在时会报错
使用参数:将存储过程体内的执行结果返回到特定的变量上
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8, 2), -- DECIMAL(a, b) a表示数字总长度(整数部分超过a-b位会报错),b表示数字小数部分长度,若不指定b或b小于2,则自动补为2位,若超过2位则按设置的长度截取
OUT ph DECIMAL(8, 2), -- 不能通过一个参数返回多个行和列
OUT pa DECIMAL(8, 2) -- OUT用于从存储过程传出一个值,IN用于从外部传递给存储过程,INOUT传入和传出
)
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;
-- 如果是在命令行执行程序,需要临时修改结束分隔符
CREATE PROCEDURE ordertotal(
IN onnumber INT,
OUT ototal DECIMAL(8, 2)
)
BEGIN
SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onnumber INTO ototal;
END;
-- 调用存储过程
CALL ordertotal(20005, @total); -- 需要传入一个参数(IN)和列出输出(OUT)结果的参数
智能存储过程:考虑要获得一个与ordertotal
一样的订单合计,但是需要针对其中的某些客户增加税收等操作,此时可以按如下步骤执行
- 获得原合计
- 把营业税有条件的添加到合计
- 返回合计(修改后的)
-- Name: ordertotal
-- Parameters: onnumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onnumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax' -- COMMENT关键字可有可无,有时将在SHOW PROCEDURE STATUS的结果中显示
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8, 2); -- 声明局部变量
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- GET the order total
SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onnumber INTO total;
-- Is this taxable
IF taxable THEN
SELECT total + (total / 100 * taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END;
-- 调用上述存储过程
CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;
使用游标
适用于MySQL5
及以后的版本,用于在检索出来的行中前进或后退一行或多行,是一种交互式应用,MySQL
中的游标只能使用在存储过程
- 在使用前必须声明(定义)
- 声明后需要打开游标以供使用
- 对于填有数据的游标,根据需要取出(检索)各行
- 在结束游标使用时,必须关闭游标
创建游标
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders; -- 声明游标并定义相应的SELECT语句
END;
打开关闭游标
-- 打开游标
OPEN ordernumbers; -- 在处理open语句时执行查询,存储检索出的数据以供浏览和滚动
-- 关闭游标
CLOSE ordernumbers -- close释放游标使用的所有内部内存和资源,因此每个游标不再需要时都应该关闭
游标在关闭后,若不重新打开则无法使用,而声明只需一次,若不手动关闭,在存储过程结束(END
)时会自动关闭
CREATE PROCEDURE processorders()
BEGIN
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Open the cursor
OPEN ordernumbers;
-- Close the cursor
CLOSE ordernumbers;
使用游标
-- 例1:从游标中检索第一行
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT,
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumbers INTO o; -- FETCH用来检索当前行的order_num(自动从第一行开始)到名为o的变量中,即此处将第一行赋给o
CLOSE ordernumbers;
END;
-- 例2:循环检索数据,从第一行到最后一行
CREATE PROCEDURE processoeders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8, 2);
DECLARE ordernumbers CURSOR -- 局部变量必须在定义游标之前定义,而句柄必须在游标定义之后定义,否则将会出错
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 定义一个CONTINUE HANDLER句柄,当后面的条件出现时执行该行代码,当SQLSTATE ‘02000’出现时执行 SET done = 1,此处的条件指的是未找到一个条件,当REPEAT由于没有更多的行提供给其循环时会出现此条件
CREATE TABLE IF NOT EXISTS ordertotals(
order_num INT,
total DECIMAL(8, 2)
);
OPEN ordernumbres;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o, 1, t); -- 调用其他存储过程计算每个订单的带税合计并存储到新建的表中
INSERT INTO ordertotals(order_num, total) VALUES(o, t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END;
触发器
适用于MySQL5
及以后的版本,适用于某条语句在事件发生时自动执行的情况
触发器是MySQL
响应DELETE, INSERT, UPDATE
中任意语句而自动执行的一条MySQL
语句(或位于BEGIN
和END
语句之间的一组语句),其他的MySQL
语句不支持触发器
创建触发器:创建时需要给出4条信息
- 唯一的触发器名
- 触发器关联的表
- 触发器应该响应的活动(
DELETE, INSERT, UPDATE
) - 触发器何时执行(处理之前或之后)
最好保持每个数据库的触发器名唯一,只有表才支持触发器,视图和临时表不支持触发器
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @newpro; -- 创建名为newproduct的新触发器,此处表示在每次对products插入数据行时输出Product added
删除触发器
DROP TRIGGER newproduct; -- 触发器不能更新和覆盖,修改一个触发器必须删除后重新创建
INSERT
触发器:该触发器在INSERT
语句执行之前或之后执行
- 在
INSERT
触发器内可引用一个名为NEW
的虚拟表,访问被插入的行 - 在
BEFORE INSERT
触发器中,NEW
中的值也可以被更新(允许更改被插入的值) - 对于
AUTO_INCREMENT
列,NEW
在INSERT
执行之前包含0,在INSERT
执行之后包含新的自动生成值
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @newder; -- 当在orders中插入一行数据时会自动分配一个新的订单号并保存到order_num,触发器从NEW.order_num取得这个值并返回,由于每次都会生成新的订单号,因此返回的都是最新的订单号
-- 测试 使用SELECT @newder查看返回值
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);
DELETE
触发器:该触发器在DELETE
语句之前或之后执行
- 在
DELETE
触发器代码内,可以应用一个名为OLD
的虚表访问被删除的行 OLD
中的值全都是只读的,不能被更新
CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES (OLD.order_num, OLD.order_date, OLD.cust_id);
END; -- 在任意订单被删除前执行此触发器,使用一条SELECT将OLD中的值保存到一个名为archive_orders的存档表中(需要用与orders相同的列名创建archive_orders表)
UPDATE
触发器:该触发器在UPDATE
语句之前或之后执行
- 在
UPDATE
触发器代码中,可以引用一个名为OLD
的虚拟表访问以前的值,引用一个名为NEW
的虚拟表访问更新的值 - 在
BEFORE UPDATE
触发器代码中,NEW
中的值可能也被更新(允许更改将要用于UPDATE
语句中的值) OLD
中的值全部都是只读的,不更被更新
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state); -- 每次更新时都将对应的vend_state设为大写
管理事务处理
事务处理用来保证成批的MySQL
操作要么完全执行要么完全不执行,避免某条语句(或一次操作)执行中途出错而不知情的情况,可以维护数据库的完整性,在InnoDB
引擎下支持事务处理,MyISAM
不支持
- 事务(
transaction
):指一组SQL
语句 - 回退(
rollback
):指撤销指定SQL
语句的过程,不可以回退SELECT, CREATE, DROP
操作 - 提交(
commit
):指将为存储的SQL
语句结果写入数据库 - 保留点(
savepoint
):指事务处理中设置的临时占位符(placeholder
),可以对其发布回退
使用ROLLBACK
:使用该命令来回退(撤销)MySQL
语句
SELECT * FROM ordertotals;
START TRANSACTION; -- 标识事务的开始
DELETE FROM ordertotals; -- 删除表中的所有行
SELECT * FROM ordertotals;
ROLLBACK; -- 回退START TRANSACTION之后的所有语句,只能在一个事务处理内使用
SELECT * FROM ordertotals;
使用COMMIT
:通常MySQL
语句是自动执行提交(写或保存)操作的,而在事务处理中,需要进行明确的提交操作
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; -- 用来提交上述的事务处理操作,如上面的两条删除语句其中一条失败则不会提交,删除失败
-- COMMIT和ROLLBACK语句执行后,事务会自动关闭
使用保留点:在复杂的事务处理中可能需要实现部分事务的提交和回退,此时需要设置一个占位符用于知晓回退到那个位置,该占位符也称为保留点
-- 创建占位符
SAVEPOINT delete1; -- 每个保留点都有其唯一的名字,便于回退时知道回退到何处
-- 回退到刚才创建的delete1
ROLLBACK TO delete1;
-- 释放保留点:在ROLLBACK和COMMIT后自动释放,也可以使用RELEASE SAVEPOINT明确释放保留点
-- 保留点可以任意多的设置,越多越能灵活的进行回退
更改默认提交:针对每个连接可以使用SET autocommit = 0;
设置不自动提交更改,而是使用COMMIT
手动提交。
安全管理
MySQL
服务器的安全基础:应该给用户有限且合适的使用权限,如某些用户只能有读写权限、删除、更新权限等,以防止用户在使用过程中因误操作而导致数据库异常,应该尽可能少用root
登录,仅在绝对需要时才使用它
管理用户
USE mysql;
SELECT user FROM user; -- 查看当前用户列表,在名为mysql的数据库中存在一个user表,保存所有用户的账户
创建账号
CREATE USER ben IDENTIFIED BY 'p@$$word'; -- 创建一个账号并给出一个登录口令,也可以不用给出口令(IDENTIFIED BY 'p@$$word')
RENAME user ben TO bforta; -- 重命名一个用户
删除账号
DROP USER bforta; -- 删除一个账号及其有关权限,MySQL5之前的该语句只能删除账号而不能删除其有关权限,需要先使用REVOKE删除与账号有关的权限,然后再删除账号
设置访问权限(GRANT
):新设置的用户没有访问权限,只能登录MySQL
,不能执行任何数据库操作,使用GRANT
语句设置权限需要给出以下信息
- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名
SHOW GRANTS FOR bforta; -- 查看赋予用户的权限
GRANT SELECT ON crashcourse.* TO bforta; -- crashcourse(crashcourse.* )所有表的SELECT权限,用户具对该数据库中的所有数据具有只读权限
REVOKE SELECT ON crashcourse.* FROM bforta; -- 撤销上述语句赋予的SELECT权限,撤销权限时必须具备该权限,否则将报错
-- 多次授权:可以使用逗号分隔需要授权的权限
GRANT SELECT, INSERT ON crashcourse.* TO bforta;
GRANT
和REVOKE
可以在如下几个层次上控制访问权限:
- 整个服务器,使用
GRANT ALL
和REVOKE ALL;
- 整个数据库,使用
ON database.*;
- 特定的表,使用
ON database.table;
- 特定的列
- 特定的存储过程
更改口令
SET PASSWORD FOR bforta = Password('n3w p@$$word'); -- 使用SET PASSWORD更改口令,口令必须传到Password()函数内进行加密
SET PASSWORD = Password(`n3w p@$$word`); -- 不指定用户名时用于设置当前登录用户的口令
改善性能
- 应该多试验联结、并、子集等操作来实现复杂查询语句,找出最佳的方法
- 执行存储过程比按单条语句执行要快
- 应该注意使用的数据类型是否正确
- 不检索比需求多的数据,即少用
SELECT *
检索所有数据(除非需要) LIKE
很慢,通常使用FULLTEXT
而不是LIKE
MySQL
数据类型
串数据类型:存储常用的串数据类型,如名字、地址、电话号码和邮编等
数据类型 | 说明 |
---|---|
CHAR | 1~255 个字符的定长串,长度必须在创建时指定,否则默认为CHAR(1) |
ENUM | 接受最多64K 个串组成的一个预定义集合的某个串 |
LONGTEXT | 与TEXT 相同,但最大长度为4GB |
MEDIUMTEXT | 与TEXT 相同,但最大长度为16K |
SET | 接受最多64个串组成的一个预定义集合的零个或多个串 |
TEXT | 最大长度为64K 的变长文本 |
TINYTEXT | 与TEXT 相同,但最大长度为255字节 |
VARCHAR | 长度可变,最多不超过255 字节,如果创建时指定为VARCHAR(n) ,则可存储0 到n 个字符的变长串(n<=255 ) |
任何形式的串数据类型,串值都必须包括在引号内(最好为单引号)
数字型数据使用的基本规则:若数值要用于计算(如求和、均值等)则使用数值数据类型,如果作为字符串使用,则应该保存在串数据类型中(可能只包含数字,如电话和邮编等)
数值数据类型:存储数值,UNSIGNED
关键字允许存储不带该关键字时两倍大小的值(除BIT
和BOOLEAN
外均可用UNSIGNED
)
数据类型 | 说明 |
---|---|
BIT | 位字段,1~64 位 |
BIGINT | 整数值 |
BOOLEAN(BOOL) | 布尔标志,为0 或1 |
DOUBLE | 双精度浮点值 |
FLOAT | 单精度浮点值 |
INT(INTEGER) | 整数值,支持-2147483648~2147483647 (UNSIGNED:0~4294967285 ) |
MEDIUMINT | 整数值,支持-8388608~8388607(UNSIGNED:0~16777215 ) |
REAL | 4字节浮点值 |
SMALLINT | 整数值,支持 -32768~32767(UNSIGNED:0~65535) |
TINYINT | 整数值,支持 -128~127(UNSIGNED:0~255) |
DECIMAL(DEC) | 精度可变浮点值 |
数值类型数据不需要括在引号内,对于货币数值,通常使用DECIMAL(8, 2)
日期和时间数据类型:存储日期和时间值
数据类型 | 说明 |
---|---|
DATE | 表示1000-01-01~9999-12-31 的日期,格式为YYYY-MM-DD |
DATETIME | DATE 和TIME 的组合 |
TIMESTAMP | 功能和DATETIME 相同(范围更小) |
TIME | 格式为HH:MM:SS |
YEAR | 用2 位数字表示,范围是70(1970)~69(2069) ,用4 位数字表示,范围是1901~2155 年 |
二进制数据类型:可存储任何数据(包括二进制信息),如图像、多媒体和字处理文档等
数据类型 | 说明 |
---|---|
BLOB | Blob 最大长度位64KB |
MEDIUMBLOB | Blob 最大长度位16MB |
LONGBLOB | Blob 最大长度位4GB |
TINYBLOB | Blob 最大长度位255 字节 |