《MySQL必知必会》读书笔记
- 第1章 了解SQL
- 第2章 MySQL简介
- 第3章 使用MySQL
- 第4章 检索数据:SELECT
- 第5章 排序检索数据 :ORDER BY DESC ASC
- 第6章 过滤数据: WHERE 单一过滤
- 第7章 数据过滤 : WHERE 字据组合过滤 AND OR IN NOT
- 第8章 用通配符进行过滤:LIKE % _
- 第9章 用正则表达式进行搜索:REGEXP
- 第10章 创建计算字段
- 第11章 使用数据处理函数
- 第12章 汇总数据
- 第13章 分组数据
- 第14章 使用子查询
- 第15章 联结表
- 第16章 创建高级联结
- 第17章 组合查询 UNION
- 第18章 全文搜索
- 第19章 插入数据:INSERT
- 第20章 更新和删除数据:UPDATE DELETE
- 第21章 创建和操纵表
- 第22章 使用视图
- 第23章 使用存储过程
- 第24章 使用游标
- 第25章 使用触发器
- 第26章 管理事务处理
- 第27章 全球化和本地化
- 第28章 安全管理
- 第29章 数据库维护
- 第30章 改善性能
第1章 了解SQL
- 数据库(database):保存有组织的数据的容器(通常是一个文件或一组文件)
- 数据库软件应称为DBMS(数据库管理系统),数据库是通过DBMS创造和操作的容器
- 表:某种特定类型数据的结构化清单
存储在表中的数据是一种类型的数据或者一个清单。决不可将顾客的清单和订单的清单存储在同一个数据库表中,这样做将使以后的检索和访问变得很困难,应该创建两个表,一个清单一个表。
数据库中每个表都有唯一的名字- 模式:关于数据库和表的布局及特性信息
如可以存储什么样的数据,数据如何分解,各部分信息如何命名等- 列:表中的一个字段,所有表都是由一个或多个列组成的,每个列都有相应的数据类型
- 数据类型:所容许的数据类型,每个表列都有相应的数据类型,它限制该列中存储的数据
- 行:表中的一个记录
- 主键:一列,其值能够唯一区别表中的每个行,主键方便更新或删除表中特定行;表中任何列或者多列组合都可以作为主键,主键需满足以下条件:
任何两行都不具有相同的主键值
每个行都必须具有一个主键值(主键列不允许NULL值)使用主键的最好习惯:
不更新主键列的值
不重用主键列的值
不在主键列中使用可能会更改的值
- SQL/structured Query Language: 结构化查询语言,专门用来与数据库通信
第2章 MySQL简介
- MySQL:一种DBMS,一种数据库软件,数据所有存储,检索,管理和处理实际上由数据库软件——DBMS(数据库管理系统)完成
- MySQL命令行实用程序:
命令输入在MySQL>之后
命令用;或者\g结束,换句话说,仅按Enter不执行命令;
输入help 或\h获得帮助,也可以输入更多的文本获得特定命令帮助(如,help select)
输入quit 或 exit 退出命令行实用程序
第3章 使用MySQL
- 连接MySQL,需要以下信息:
主机名(计算机名)——本地为localhost
端口(使用默认端口3306之外的端口)
一个合法的用户名
用户口令- 选择数据库
关键字:MySQL语言组成部分的一个保留字,绝不要用关键字命名一个表或列
input | USE crashcourse
output | Database changed- 常用命令:
常用命令 | 说明 |
---|---|
show DataBases; | 展示当前数据库 |
USE dataBASESName; | 使用某个数据库 |
SHOW Tables; | 获取数据库中表的列表 |
show columns from tables; | 显示表列 |
describe tables1; | 显示表列 |
- MySQL执行时,默认不区分大小写
第4章 检索数据:SELECT
- SELECT 从一个或多个表中检索数据
SELECT prod_name from products;
# 单列检索未排序,顺序随机- SQL 关键字大写,其他小写,方便阅读
select prod_id,prod_name,prod_price FROMproducts;
# 多列检索SELECT * FROM products;
# 检索所有列,少用*通配符,降低检索效率,可检索出未知列SELECT DISTINCT vend_id FROM products;
# 去重检索某列SELECT prod_name FROM products LIMIT 5;
# 返回不多于五行
SELECT prod_name FROM products LIMIT 5,5;
# 返回行5开始后5行,第一行为行0
LIMIT 4 OFFSET 3 == LIMIT 3,4
SELECT products.prod_name FROM products;
# 限定表名提取列
SELECT products.prod_name FROM crashcourse.products;
# 限定库表、表列提取
第5章 排序检索数据 :ORDER BY DESC ASC
SELECT prod_name FROM products ORDER BY prod_name;
# 提取某列并按该列字母排序- 多列排序
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price,prod_name;
# 优先按prod_price 排序,当price相同时再按name排序- 指定排序顺序
SELECT prod_id,prod_name,prod_price FROM products ORDER BY prod_price DESC,PROD_NAME ASC;
# DESC 从大到小,由 Z-A,ASC 升序,默认升序- 获取某列最大值
SELECT prod_name,prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
第6章 过滤数据: WHERE 单一过滤
SELECT prod_name, prod_price FROM products WHERE prod_price = 250 ORDER BY prod_name;
# order by 在 where 之后- WHERE 子句 操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的指定值之间 |
- 一些实例:
SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 and 10;
- 空值检查
SELECT prod_name FROM products WHERE prod_price IS NULL;
# 返回没有价格的所有产品
第7章 数据过滤 : WHERE 字据组合过滤 AND OR IN NOT
- 每添加一个过滤条件使用一个AND
SELECT prod_id,prod_name,prod_price FROM products WHERE ved.id = 1003 AND prod_price <= 10;
- or 匹配任意给定条件行
- 计算次序 AND 比 OR 优先执行,混用时用括号区分
- IN WHERE中用来指定要匹配的清单关键字,用逗号分隔清单,功能与OR相似,但一般比OR执行更快
SELECT prod_id,prod_name,prod_price FROM products WHERE ved.id IN (1002,1003,1006) ORDER BY prod_name;
- NOT IN 匹配IN清单外的数据
第8章 用通配符进行过滤:LIKE % _
- 通配符:用来匹配值的一部分的特殊字符,结合LIKE操作符使用
- 搜索模式:由字面值,通配符或者两者组合构成的搜索条件
- 百分号通配符:表示任意字符出现任意次数
SELECT prod_id, prod_name, FROM products WHERE prod_name LIKE 'jet%';
‘%anvil%’ 包含anvil的任意值
‘b%e’以b开头,e结尾的任意值
%通配符不可以匹配NULL值- 下划线通配符:匹配单个字符而不是多个字符,不能多也不能少
SELECT prod_id, prod_name, FROM products WHERE prod_name LIKE '_jet';
- 通配符使用技巧
a. 不要过度使用通配符,其他操作符可达到想同目的,优先其他
b. 确定需要使用时,尽量不要放在开始处,通配符至于搜索模式开始处,运行最慢
c. 仔细注意统配符位置,如果放错,返回结果不尽如人意。
第9章 用正则表达式进行搜索:REGEXP
- 基本字符匹配 . 表示匹配任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
与LIKE区别:WHERE prod_name REGEXP ‘1000’ ,列值匹配,返回‘1000’所在行
WHERE prod_name LIKE ‘1000’,匹配整个列不返回’1000‘行
- 区分大小写:BINARY
WHERE prod_name REGEXP BINARY 'JetPack.00';
- 进行OR匹配,正则用 |
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
- 匹配n个字符之一,使用中括号 [123] 123 任选一
[^123]匹配除这些字符外的其他东西- 匹配范围 [1-9] [a-z] 匹配任意数字,字母
- 匹配特殊字符,转义字符\
类别 | 说明 |
---|---|
\\. | 表示查找 . |
\\- | 表示查找- |
\\\ | 匹配\ |
\\f | 换页 |
\\v | 纵向制表 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
- 匹配字符类:
类别 | 说明 |
---|---|
[: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 "HELLO
REGEXP ‘[0-9]’
第10章 创建计算字段
- 拼接字段CONCAT()
SELECT CONCAT (vend_name, '(',vend_country,')') FROM vendors ORDER BY vend_name;
- 去除空格:去除右侧空格RTrim(),左侧LTrim(),两边Trim()
SELECT CONCAT (RTrim(vend_name), '(',vend_country,')') FROM vendors ORDER BY vend_name;
3.使用别名 AS
SELECT CONCAT (RTrim(vend_name), '(',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name;
- 算术计算 + - * /
SELECT prod_id,quantity,item_price, quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 2005;
- 测试计算
SELECT 3*2
返回6
SELECT Trim('abc')
返回abc
SELECT Now()
返回当前日期和时间
第11章 使用数据处理函数
- 文本处理函数 Upper() 将文本转换为大写
SELECT vend_name,Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
常用文本处理函数
常用文本处理函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转化为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的字符 |
Soundex() | 返回串的SOUNDEX值(发音相似的值) |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
- 日期和时间处理函数
常用日期处理函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天,周等) |
AddTime() | 增加一个时间(时,分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间日期部分 |
DateDiff() | 计算两个时间之差 |
Date_Add() | 日期计算 |
Date_Formate() | 返回格式化日期或时间串 |
Day() | 返回日期天数 |
Day of Week() | 返回对应星期几 |
Hour() | 返回小时 |
Minute() | 返回分钟 |
Month() | 返回月份 |
Now() | 返回当前日期时间 |
Second() | 返回秒 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回日期的年部分 |
SELECT cust_id,order_num FROM orders WHERE Date(order_date) = '2005-09-01';
日期必须为yyyy-mm-dd格式
- 数值处理函数
常用数值处理函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
第12章 汇总数据
- 聚集函数:运行在行组上,计算和返回单个值的函数
聚集函数 | 说明 |
---|---|
AVG() | 返回某列平均值 |
COUNT() | 返回某列行数 |
MAX() | 返回某列最大值 |
MIN() | 返回某列最小值 |
SUM() | 返回某列值和 |
SELECT AVG(prod_price) AS avg_price FROM products WHERE VEND_ID = 1003;
#未获得多列平均值,必须使用多个AVG()函数
AVG()函数忽略NULL值行
Count(*) 对表中所有行数目进行统计,包含NULL行
Count(column)对具体列行数进行统计,不包含NULL行
MAX() MIN() SUM() 忽略NULL值行
- 聚集不同值 DISTINCT 必须用于列名
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE VEND_ID = 1003;
# 去重后求列均值
- 组合聚集函数
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, MVG(prod_price) AS price_avg FROM products;
第13章 分组数据
- 创建分组 GROUP BY
SELECT vend_id,count(*) AS num_prods FROM products GROUP BY vend_id;
# 对vend_id 列每一个类别数目分别进行统计输出
GROUP BY vend_id WITH ROLLUP;
# 得到每个分组以及每个分组汇总级别的值- 过滤分组 HAVING
SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*) >= 2;
# HACING在分组后过滤,WHERE在分组前过滤- 分组和排序 ORDER BY GROUP BY
SELECT order_num,SUM(quantity*item_price) AS order_total FROM oderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
- SELECT子句顺序
SELECT子句顺序 | 说明 |
---|---|
SELECT | 返回的列或表达式 |
FROM | 从中检索数据的表 |
WHERE | 行级过滤 |
GROUP BY | 分组说明 |
HAVING | 组级过滤 |
ORDER BY | 排序顺序 |
LIMIT | 检索行数 |
第14章 使用子查询
- 不同表之间相同列关联子查询过滤列必须匹配
SELECT cust_name,cust_concat FROM customers WHERE cust_id IN (SELECT cust_id FROM order_num IN(SELECT order_num FROM orderitems WHERE PROD_ID = 'TNT2'));
- 作为计算字段使用子查询
显示customers表中每个客户订单总数,订单与相应客户ID存储在orders中
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
# 注意限制有歧义的列名cust_id
第15章 联结表
- 外键:外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
- 内部联结:INNER JOIN
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
# 返回结果同1相同- 多表连接
SELECT vend_name,prod_name,prod_price FROM orderitems,vendors,products WHERE vendors.vend_id = products.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 2005;
第16章 创建高级联结
- 表别名:只在查询执行中使用,与列别名不同,表别名不返回客户机
SELECT CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title FROM vendors AS v, orders AS o WHERE v.vend_id = o.vend_id;
- 自联结:单表查询某一结果,根据结果进行第二次查询
SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
SELECT p1.prod_id, p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.vend_id = 'DTNTR';
# 以上两种方法输出结果相同,但一般第二种运行速度更快- 自然联结:多表联结,至少某一列多次出现,排除多次出现,使每个列仅出现一次,通过select table1.* 实现
- 外部联结:
LEFT OUTER JOIN
从左边表选择所有行
RIGHT OUTER JOIN
从右边表选择所有行
第17章 组合查询 UNION
- 组合查询使用两种基本情况 UNION
在单个查询中从不同的表返回类似结构的数据
在单个表执行多个查询,按单个查询返回数据
SELECT vend_id FROM products WHERE prod_price <=5 UNION SELECT vend_id FROM products WHERE vend_id IN (1001,1002);
SELECT vend_id FROM products WHERE prod_price <=5 or vend_id IN (1001,1002);
# 以上两种方式返回结果相同
- UNION规则
UNION 必须由两条或两条以上的SELECT语句组成,语句之间用UNION隔开
UNION中每个查询必须包含相同的列,表达式或聚集函数
列数据类型必须兼容:类型不必完全相同(例如不同的数值类型或不同的日期类型)
- UNION 自动取消重复行
UNION ALL显示所有行,不取消重复行- UNION组合查询时,只能在最后一条SELECT语句后使用ORDER BY子句
第18章 全文搜索
第19章 插入数据:INSERT
- 数据插入
INSERT INTO customers VALUES( NULL, 'Pep E','100 Main Street',.....);
INSERT INTO customers(cust_name,cust_address,cust_city,.....) VALUES( 'Pep E','100 Main Street',.....);
# 指定列,即使表的结构改变,INSERT仍正常工作- 插入多行
INSERT INTO customers(cust_name,cust_address,cust_city,.....) VALUES( 'Pep E','100 Main Street',.....),( 'M.Martian','42 Galaxy Way',.....);
- 插入检索出的数据
INSERT INTO customers(cust_name,cust_address,cust_city,.....) SELECT cust_id,cust_concat,...FROM custnew;
# 将custnews表中的数据接入customers中。
第20章 更新和删除数据:UPDATE DELETE
- 更新某个单元格值
UPDATE customers SET cust_mail = 'elmer@fudd.com' WHERE CUST_ID = 1005;
- 更新多个单元格值
UPDATE customers SET cust_name = 'The Fudds',cust_email = 'elmer@fudd.com' WHERE cust_id = 1005;
# 为删除某个值可设置为SET cust_main = NULL
,UPDATE以WHERE 子句结束,否则更新整个表- 删除数据
删除一行DELETE FROM customers WHERE cust_id = 1006
删除表所有行TRUNCATE TABLE
语句更快- 更新和删除使用指导原则
除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE
保证每个表都有主键
在对UPDATE或DELETE语句使用WHERE子句前,应先用SELECT进行测试,防止错误
使用强制实施引用完整性的数据库,这样MySQL 将不允许删除具有其他表相关联的数据行
第21章 创建和操纵表
- 表创建基础:
新表的名字,在关键字CREATE TABLE之后给出
新标的名字和定义,用逗号分隔
CREATE TABLE customers IF NOT EXISTS( cust_id int not NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_address char(50) NULL, ... ... PRIMARY KEY(cust_id) ENGINE = InnoDB;
- 类型介绍
类型 | 说明 |
---|---|
空值 | 除指定NOT NULL,默认NULL |
主键 | PRIMARY KEY |
自增长 | AUTO_INCREMENT #0,1,2,3… |
默认值 | DEFAULT 1 |
引擎类型 | ENGINE = InnoDB |
引擎类型:
INNODB: 事物处理引擎,不支持全文搜索,
MEMORY: 功能等同于MyISAM ,但数据存储在内存中,运行快,适用于临时表
MyISAM:支持全文搜索,但不支持事务处理
- 更新表 ALTER TABLE
a. 表更改基础:
ALTER TABLE
之后给出要更新的表名(该表必须存在)
所做更改的表
b. 表添加列
ALTER TABLE vendors ADD vend_phone CHAR(20);
c. 表删除列
ALTER TABLE vendors DROP COLUMN vend_phone;
d.定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(order_num) REFERENLES orders(order_num); 备注:ALTER TABLE 前应先备份数据
- 删除表
DROP TABLE customers2;
- 重命名表
单表重命名
RENAME TABLE customers2 TO customers;
多表重命名
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products TO products;
第22章 使用视图
- 使用视图的原因:
重用SQL语句
简化复杂的SQL操作,编写查询后,可以方便的重用它而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
- 视图规则和限制
与表一样,视图必须唯一命名
对于可以创建的视图数目没有限制
为了创建视图,必须具有足够的访问权限
视图可以嵌套,可以利用从其他视图中检索的数据查询来构造一个视图
ORDER BY 可以用在视图中,但被引用时会被外部ORDER BY覆盖
视图不能索引,也不能有关联的触发器和默认值
视图可以和表一起使用
- 使用视图
a. 创建视图
用CREATE VIEW
语句创建视图
b. 查看视图
SHOW CREATE VIEW viewname;
c. 删除视图
DROP VIEW viewname;
d. 更新视图
- 先用DROP再用CREATE
- 直接用CREATE OR REPLACE VIEW,若更新视图不存在,直接创建新视图
- 利用视图简化复杂的联结
CREATE VIEW productcustomers AS SELECT cust_name,cust_concat,prod_id FROM customers, orders,orderitems WHERE customers.cust_id = orders.cust_id;
使用视图查询,订购了产品TNT2的客户
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
- 使用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS SELECT CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') FROM vendors ORDER BY vend_name;
SELECT * FROM vendorlocations;
- 使用视图过滤不想要的数据
CREATE VIEW customeremaillist AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_email IS NOT NULL;
SELECT * FROM customeremaillist;
使用视图与计算字段
第23章 使用存储过程
第24章 使用游标
第25章 使用触发器
- 触发器:设定某条语句在事件发生时自动执行(动态更新数据)
响应以下任意语句DELETE、INSERT 、UPDATE
,其他MySQL 语句不支持触发器- 创建触发器的4条信息
唯一的触发器名
触发器关联的表
触发器应该响应的活动(DELETE、INSERT、UPDATE)
触发器何时执行(处理之前或之后)
CREATE TRIGGER
创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH Row SELECT 'product added';
创建了一个 newproduct 触发器,在 INSERT后发生(AFTER),每插入一条数据均执行(FOR EACH Row)输出 product added
每个表最多支持6个触发器(DELETE、INSERT、UPDATE
)在前在后
- 删除触发器
DROP TRIGGER newproduct;
- INSERT触发器、DELETE触发器、UPDATE触发器,原书缺页待完善
第26章 管理事务处理
- 事务管理:用来维护数据库的完整性,保证成批的MySQL要么完全执行,要么完全不执行
保证一组数据批量完成或不完成操作,防止数据部分处理而不自知为后续工作带来麻烦
事务处理关键词汇:
类别 | 说明 | 详解 |
---|---|---|
transaction | 事务 | 指一组SQL语句 |
rollback | 回退 | 指撤销指定SQL语句的过程 |
commit | 提交 | 指将未存储的SQL语句结果写入数据库表 |
savepoint | 保留点 | 事务处理中设置的临时占位符,你可以对它发布回退 |
- 使用 ROLLBACK
SELECT * FROM products;
# 显示products不为空
START TRANSACTION;
# 开始事务处理
DELETE FROM products;
# 删除所有行
SELECT * FROM products;
# 显示products为空
ROLLBACK;
# 回退START TRANSACTION 后所有语句
SELECT * FROM products;
# products不为空- 使用COMMIT
START TRANSACTION;
# 开始事务处理
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
# 若第一条DELETE成功第二条失败,commit 不提交(该指定自动撤消)- 使用保留点
设置保留点,支持回退部分事务处理
SAVEPOINT delete1;
ROLLBACK TO delete1;
# 保留点越多越好,保留点执行一条ROLLBACK或COMMIT后自动释放- 更改默认提交行为
默认的MySQL行为是自动提交所有更改,即对表的操作
SET autocommit = 0;
不管有没有commit 语句,都不会提交更改,
直到SET autocommit = 1;
第27章 全球化和本地化
数据库中表的数据用不同语言和字符集存储,需要不同的排序与检索方法
类别 | 说明 |
---|---|
字符集 | 字母和符号的集合 |
编码 | 某个字符集成员的内部指示 |
校对 | 规定字符如何比较的指令 |
SHOW CHARACTER SET;
# 显示所有可用字符集及其描述和默认校队
SHOW COLLAITION;
# 显示所有可用校队及适用字符集
第28章 安全管理
- 访问控制
不同用户使用权限不同
现实工作中,绝不能使用root(它对整个MySQL服务器具有完全控制)- 管理用户
查看当前用户账号列表:USE Mysql; SELECT user FROM user;
a. 创建用户账号
CREATE USER ben IDENTIFIED BY 'p@$$uOrd';
# IDENTIFIED BY 'p@$$uOrd’口令或有项
b. 删除用户账号
DROP USER bforta;
# Mysql 5 前选用REMOVE删除与账号相关的权限,再删除账号
c.设置访问权限
查看用户权限SHOW GRANTS FOR bforta;
授予权限 GRANT
GRANT SELECT ON crashcourse.* TO bforta;
# 用户bforta在crashcouse数据库中具有SELECT 只读权限
删除权限 REMOVE
REMOVE SELECT ON crashcourse.* FROM bforta;
# 被撤销权限必须存在
访问权限层次细分看原文图表
d. 更改口令
SET PASSWORD FOR bforta = Password('n3dsf343');
SET PASSWORD = Password ('ssf34344');
# 设置自己的口令,不指定用户名时,SET PASSWORD 更新当前登录用户口令
第29章 数据库维护
- 备份数据
- 数据库维护,保证数据库正确和正常运行
- 诊断启动问题
- 查看日志文件
第30章 改善性能
1.关键的生产DBMS应运行在自己的专用服务器上;
2. M有SQL默认设置一段时间后需调整内存分配,缓冲区大小等
查看当前设置:SHOW VAKIABLES;
SHOW STATUS;
3. MySQL 是一个多用户多线程DBMS,
SHOW PROCESSLIST;
显示所有活动进程(线性ID和执行时间),KILL 命令终止某个特定进程
4. 多种方法编写同一条SELECT, 试验联结、并子查询找出最优解
5. 使用explain ,让Mysql解释它将如何执行一条SELECT语句
6. 存储比一条条执行其中各条Mysql语句快
7. 总是使用正确的数据类型
8. 不要使用SELECT *(除非你真正需要每个列)
9. DELAYED 关键字,把控制返回给调用程序,若有可能将执行
10. 导入数据时,关闭自动提交,删除索引(包括FULLTEXT索引),导入后再重建
11. 去除数据库中重复数据
12. 将SELECT 中多条 OR 语句用 多条SELECT 和 UNION 连接替代
13. LIKE很慢,尽量使用FULLTEXT