读书笔记系列1——MySQL必知必会
文章目录
读书笔记系列1——MySQL必知必会MySQL官方文档：/doc/第一章 数据库基础 *.11.23*概念定义：书中样例：第三章 使用MySQL *.11.24*关键操作Use、Show第四章 检索数据Select *.11.26*第五章 排序检索数据order by *.11.28*第六章 过滤数据1where *.11.29*第七章 过滤数据2组合条件 *.11.29*第八章 通配符进行过滤 *.11.29*通配符：主要是针对字符串(文本)类数值，指定搜索模式进行筛选第九章 正则表达式进行搜索 *.11.29*正则表达式第十章 创建计算字段 *.11.30*计算字段：将不同列数据进行转换、计算或格式化第十一章 数据处理函数 *.11.30*第十二章 汇总数据 *.11.30*第十三章 分组数据 *.11.30*分组数据：将数组分为多个逻辑组，以便对每个组进行聚集计算第十四章 子查询 *.12.1*子查询：嵌套查询，如果后续查询需要用到前面查询的结果，可以利用子查询用一个语句实现第十五章 联结表 *.12.1*关系表：把信息分解成多个表，一类数据建一个表，各个表通过一些值相互关联，尽量保证同样的信息不会重复存储联结：在一条select语句中联结多个表返回一组输出第十六章 创建高级联结 *.12.2*第十七章 组合查询 *.12.2*组合查询：将多条Select语句的结果并在一起第十八章 全文本搜索 *.12.2*第十九章 插入数据INSERT *.12.3*第二十章 更新UPDATE和删除DELETE数据 *.12.3*第二十一章 创建和操纵表 *.12.3*第二十二章 使用视图 *.12.5*视图：虚拟的表，自身不包含数据，能够将其它表的组成部分包装成视图，即特定查询的封装第二十三章 使用存储过程 *.12.5*存储过程：为以后的使用而保存的一条或多条MYSQL语句的集合，可将其视为批文件或其它编程语言中的函数第二十四章 使用游标 *.12.6*游标：迭代器，每次执行一条并且自动向下递进第二十五章 使用触发器 *.12.6*触发器：中断，事件发生时自动执行某条或某些语句第二十六章 管理事务处理 *.12.6*事务处理：锁/原子操作，保证成批的MySQL操作要么完全执行，要么完全不执行，能够保证数据库不包含不完整的操作结果第二十七章 全球化和本地化 *.12.6*第二十八章 安全管理 *.12.7*安全管理：用户权限第二十九章 数据库维护 *.12.7*第三十章 改善性能 *.12.7*MySQL官方文档：/doc/
第一章 数据库基础.11.23
概念定义：
数据库：保存有组织的数据的容器(通常是一个文件或一组文件)。
我们不会直接访问数据库，而是通过数据库软件，替我们访问数据库
表：某种特定类型数据的结构化清单。
顾客清单和订单清单应该放在不同的表中，表类似于同一类文件；
每个表有唯一名字作为标识，不同数据库中的表可以有相同名字。
模式：关于数据库和表的布局及特性的信息。
描述表的这些特性：数据在表中如何存储，比如可以存储什么样的数据，数据如何分解，各部分信息如何命名等。
列：表中的一个字段，表示由一个或多个列组成。
表中的每一列存储着一条特定信息：例如，在顾客表中，一个列存储顾客编号，另一列存储姓名，地址、城市、邮政编码等分别存储在不同的列中。
每一列有相应的数据类型，如存储物品数应该用数值类型，存储日期、文本、注释、金额等应该用恰当的数据类型进行规定。
行(记录)：表中水平方向，例如，顾客表每行存储一个顾客，行数为记录的总数。
主键：一列，其值能够唯一区分表中每个行。
如顾客编号列是顾客表的主键，订单表可以使用订单ID。
主键两个条件(如果有，但是可以没有主键)：
唯一性：每一行的主键值唯一存在性：每个行必须有一个主键值，不能为NULL
还可以使用多个列作为主键，这时候所有主键列值的组合必须唯一
主键使用良好习惯：
不更新主键列中的值不重用主键列的值不在主键列中使用可能会更改的值（如：如果使用名字作为主键来标识某个供应商，当该供应商合并和更改了名字，就必须更改这个主键）
书中样例：
整体情况：
随身物品推销商使用的订单录入系统，用来完成以下几个任务：
管理供应商管理产品目录管理顾客列表录入顾客订单
六个表：
vendors表——销售产品的供应商
每个供应商在这个表中有一个记录，供应商ID(vend_id)列作为这个表的主键，为一个自动增量字段。
products表——产品目录表
每行为一个产品，每个产品有唯一的ID(主键为prod_id)，通过vend_id关联到其供应商，vend_id定义一个外键，关联到vendors的vend_id。
customers表——顾客信息表
每个顾客有唯一ID，作为其主键，cust_id也是一个自动增量字段。
orders表——顾客订单(不包含订单细节)
每个订单编号唯一(order_num作为主键)，cust_id关联到customer表中相应顾客；order_num自动增量字段，cust_id定义一个外键。
order-items表——每个订单中的实际物品
每个订单中的每个物品占一行，orders表中的每一行在order-items表有一行或多行；这个表由订单号加订单物品唯一标识(order_num和order_item作为主键)；order_num关联到orders表中，prod_id关联到products表。
product-notes表——特定产品的相关注释
并非所有产品都有相关注释，而有的产品可能有许多相关的注释。
note_id作为主键，列note_text必须为FULLTEXT搜索进行索引
由于这个表使用全文本搜索，因此必须指定ENGINE=MYISAM。
第三章 使用MySQL.11.24
关键操作Use、Show
Use
选择/切换数据库
Use [crashcourse]；
Show
显示信息
show databases；
显示所有可用的数据库
show tables;
返回当前选择的数据库中可用的表
show columns from [customers];
或
describe [customers];
返回指定的表中每个列，包含列名、数据类型、是否允许NULL、该列是否是主键、默认值以及其它信息(如auto_increment1)
show status；
显示服务器状态信息
show create database;
show create table;
显示创建特定数据库或表的MySQL语句
show grants;
显示授予用户的安全权限
show errors
show warnings;
第四章 检索数据Select.11.26
Select
检索数据
Select [prod_name] from [products];
从products表中检索prod_name列，未经过排序以及筛选
Select [prod_id==,== prod_name==,== prod_price] from [products];
检索多个列
Select * from [products];
检索所有列
Selectdistinct[vend_id] from [products];
检索某一列，具有相同值的行只显示一次
Select [prod_name] from [products] limit [5];
返回指定列的前几行
Select [prod_name] from [products] limit [m,n];
=
Select [prod_name] from [products] limit [n OFFSET m];
返回指定列的第m行之后的n行
完全限定的表名/列名
如crashcourse.products, products.prod_name等
第五章 排序检索数据order by.11.28
直接用Select…From…检索得到的列没有特定顺序
利用order by…子句对检索的列进行排序输出
Select prod_name from productsorder byprod_name;
按多个列进行排序：
Select prod_id, prod_price, prod_name from products
order by prod_name, prod_price;
先按prod_name排序，如果多个行有相同的prod_name值时再根据prod_price排序
默认升序(A-Z，0-9)排列，可以指定DESC关键字降序(Z-A，9-0)排列
Select prod_id, prod_price, prod_name from products
order by prod_price DESC;
DESC关键字只针对前面的一个列名有效，如
Select prod_id, prod_price, prod_name from products
order by prod_price DESC，prod_name;
先按prod_price降序排列(贵的排在前面)，价格相同的行再按prod_name升序排列；
如果想在多个列上进行降序排序，必须对每个列指定DESC关键字
order by + limit：找出某列最高或最低的值
Select prod_price from products
order by prod_price DESC
limit 1;
注意几个子句关键词的顺序不能改变
第六章 过滤数据1where.11.29
Where
指定搜索条件(过滤条件)，跟在from子句之后
相等条件筛选
Select * from products
where prod_price = 2.5
order by vend_id;
其它条件筛选
<>与！=相同
Between需要和and搭配使用，检索范围为闭区间，如[5,10]
Select prod_name, prod_price from products
where prod_price BETWEEN 5 AND 10;
空值检查（NULL）
Select cust_id from customers
where cust_emailISNULL;
[注：使用不等式条件筛选时无法筛选出NULL值的行，NULL为数据缺失，但不能确定是否匹配]
第七章 过滤数据2组合条件.11.29
AND
Select prod_id, prod_price, prod_name from products
where vend_id = 1003 AND prod_price <= 10;
可以添加多个过滤条件，多加几个AND把各个条件连接在一起
OR
Select prod_id, prod_price, prod_name from products
where vend_id = 1002 OR vend_id = 1003;
AND与OR的优先级顺序
默认情况下AND优先计算，可以加==()==进行限制
Select prod_name, prod_price from products
where (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;
检索由生产商1002或1003生产，并且价格在10元及以上的产品
如果不加括号，代表由1003生产且价格在10元以上，或1002生产的全部产品
IN
指定符合筛选要求的条件范围，在（）中穷举出来，与OR语句的作用相同
Select prod_name, prod_price from products
where vend_id IN (1002,1003)
NOT
否定其后的所有条件
Select prod_name, prod_price from products
where vend_id NOT IN (1002,1003);
NOT只支持对IN、Between、Exists子句取反
第八章 通配符进行过滤.11.29
通配符：主要是针对字符串(文本)类数值，指定搜索模式进行筛选
搜索模式由字面值、通配符或两者组合构成（类似正则表达式）
利用通配符进行过滤时必须使用LIKE操作符
%
表示任何字符出现任意次数，也可以0次
'jet%'表示匹配所有以jet开头的文本
Select prod_id, prod_name from products
where prod_nameLIKE ‘jet%’;
'%anvil%'表示匹配所有包含anvil的文本
's%e’表示匹配所有s开头e结尾的文本
%不能匹配到值为空NULL的行
_
匹配任意的单个字符(匹配任意字符的一次出现)，不能匹配0次出现
‘_ ton anvil’
第九章 正则表达式进行搜索.11.29
正则表达式
正则表达式：用来匹配文本的特殊的字符串集合
利用正则表达式进行过滤时必须使用REGEXP操作符
LIKE和REGEXP的差别：
LIKE ‘1000’
LIKE需要完全匹配字符串全部内容，除非使用通配符
REGEXP ‘1000’
REGEXP匹配字符串中的一部分，如果字符串中一部分出现也会被返回；
REGEXP也可以通过^和$定位符(anchor)匹配整个字符串
‘.000’
.表示匹配任意一个字符
可以匹配到 ‘JetPack 1000’和’JetPack 2000’
‘|’，‘或’
'1000|2000’表示搜索两个串之一，包含1000和包含2000的字符串都会被返回
两个以上的或条件：‘1000|2000|3000’
‘[ ]’
匹配特定字符，用[]括起来
‘[123] Ton’可以匹配到’1 ton anvil’，‘2 ton anvil’，‘3 ton anvil’，[123]表示匹配1或2或3，与[1|2|3]相同
‘[^ ]’
‘[^123] ton anvil’可以匹配到包含*处不为1、2、3的’* ton anvil’至少一次的字符串
‘[ - ]’
‘[0-9]与’[0123456789]‘相同，可以是’[1-3]’，’[a-g]'等
‘\\’
转义：匹配特殊字符需要’\\‘为前导，如’\\-’，’\\.’
引用元字符(具有特殊含义的字符)
为了匹配反斜杠’\‘本身，需要使用’\\\’
MySQL与标准的正则表达式有差别，需要两个反斜杠表示转义字符，标准的使用一个，因为MySQL自己解释一个，正则表达式库解释另一个。
匹配字符类
限定匹配的次数
‘\\([0-9] sticks?\\)’ 匹配 左括号-零到九中的一个数字-空格-stick-s可有可无-右括号
‘[[:digit:]]{4}’ 匹配连在一起的任意4位数字
定位符(匹配字符串中特定位置的文本)
‘^[0-9\\.]’ 匹配以数字或小数点开始的所有字符串通过’^…$'作为开始和结束的正则表达式，可以使得REGEXP与LIKE起到一样的作用
可以在不使用数据库表的情况下用SELECT测试正则表达式，0为没有匹配，1为匹配，如
Select ‘hello’ REGEXP ‘^[0-9]’
返回0，不能匹配
第十章 创建计算字段.11.30
计算字段：将不同列数据进行转换、计算或格式化
concat()
Select concat(vend_name, ’ (’, vend_country, ‘)’) from vendors;
利用Concat()函数进行拼接，按照name (country)格式输出
返回计算后的单个列(计算字段)
RTrim()
Select concat(RTrim(vend_name), ’ (’, RTrim(vend_country), ‘)’) from vendors;
RTrim()去掉字符串右边的所有空格
Trim()去掉字符串左边的所有空格
Trim()去掉字符串左右两边的所有空格
AS
Select concat(RTrim(vend_name), ’ (’, RTrim(vend_country), ‘)’)
AS vend_title
from vendors;
给计算字段形成的新列赋予一个别名(又称导出列)
别名其它用途：
实际表列名包含不符合规定的字符时重命名原来的名字含混不清时扩充它
算数运算
Select prod_id, quantity, item_price,
quantity*item_price AS expanded_price
from orderitems where order_num = 20005;
检索订单号为20005中的所有物品并计算总价
MySQL支持的其它基本运算操作符：
()可以用来区分优先顺序
可以不使用数据库表简单地访问和处理表达式，如
Select 3*2； 返回6
Select Trim('abc ')；返回abc
Select Now()； 返回当前日期和时间
第十一章 数据处理函数.11.30
不同DBMS的函数差异很大，在MySQL中使用函数的程序可移植性较差
文本处理函数
如：RTrim()去除字符串右边的空格
Upper()将文本转换为大写
select vend_name, Upper(vend_name) AS vend_name_upcase from vendors;
Soundex()将字符串转换为发音
如customers表中顾客联系名为Y Lee，但是实际上我们只知道发音，不知道如何拼写，可以搜索他的发音
Select cust_name, cust_contact from customers
where Soundex(cust_contact) = Soundex(‘Y Lie’);
日期和时间处理函数
日期格式：yyyy-mm-dd，如9月1日写为-09-01
MySQL中存储日期的数据类型为datetime，包含’-09-01 11:30:05’
如果直接检索where order_date = ‘-09-01’，检索不到，
应该使用where Date(order_date) = '-09-01’检索匹配部分日期的行。
检索9月份所有订单：
Select cust_id, order_num from orders
where Year(order_date) = AND Month(order_date) = 9;
数值处理函数
第十二章 汇总数据.11.30
聚集函数
AVG()
Select AVG(prod_price) AS avg_price from products;
返回products表中所有产品的平均价格
Select AVG AS avg_price from products
where vend_id = 1003;
返回供应商为1003的产品的平均价格
AVG()只能用来确定单个列的平均值，计算多个列的平均值需要使用多个AVG()函数
值为NULL的行在计算中被忽略
Count()
Select count(*) AS num_cust from customers;
对所有行计数，不管包含的是NULL还是非空值，总共有五个顾客返回5
Select count(cust_email) AS num_cust from customers;
指定列名，则对该列值非空的行进行计数，5个顾客中有三个有电子邮箱地址返回3
MAX()
Select MAX(prod_price) AS max_price;
返回指定列中最大值，必须指定列名
忽略NULL值
MIN() 与MAX()类似
SUM()
Select SUM(item_price*quantity) AS items_ordered from orderitems
where order_num = 20005;
返回指定列的值的和，例子为计算订单号为20005的订单总金额
忽略NULL值
聚集不同值
Select AVG(DISTINCT prod_price) AS avg_price from products
where vend_id = 1003;
只计算商家编号为1003所有产品不同值的平均值
组合聚集函数
Select COUNT(*) AS num_items,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
from products;
第十三章 分组数据.11.30
分组数据：将数组分为多个逻辑组，以便对每个组进行聚集计算
GROUP BY创建分组
Select vend_id, COUNT(*) AS num_prods from products
GROUP BY vend_id;
对每个vend_id计算一次COUNT(*)
HAVING过滤分组
where过滤行，HAVING过滤分组；
where在数据分组前进行过滤，HAVING在数据分组后进行过滤。
Select cust_id, COUNT(*) AS orders from orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
筛选出至少有两个订单的所有顾客。
Select vend_id, COUNT(*) AS num_prods from products
where prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
筛选出具有两个以上价格在大于等于十元的产品的供应商
GROUP BY和ORDER BY区别
Select order_num, SUM(quantity*item_price) AS ordertotal from orderitems
group by order_num
having SUM(quantity*item_price) >= 50
order by ordertotal;
筛选订单总计价格在50元以上的订单号和总结订单价格，按照订单号进行分组，按照总价格进行排序
Select子句顺序：
第十四章 子查询.12.1
子查询：嵌套查询，如果后续查询需要用到前面查询的结果，可以利用子查询用一个语句实现
子查询作为过滤条件
列出订购TNT2物品的所有客户：
分步检索：
查询包含物品TNT2的所有订单编号查询上一步中订单编号对应的顾客ID
子查询
Select cust_id from orders
where order_num IN
(Select order_num from orderitems where prod_id = ‘TNT2’)
MySQL内部也是分步执行的，先查询第一步结果，然后转换成带有逗号的格式放进外层的语句中
子查询可以用于IN，=，<，>等
子查询中包含计算字段
显示customers表中每个顾客的订单总数
分布检索：
从customers表中检索顾客列表对检索出的每个顾客，统计其在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;
子查询执行5次，因为检索出customers表中的5个顾客；
每一次执行子查询，高亮部分判断一次orders表中orders.cust_id = customers.cust_id的行数，并统计总数
**完全限定列名：**customers.cust_id避免歧义
**相关子查询：**涉及外部查询的子查询
第十五章 联结表.12.1
关系表：把信息分解成多个表，一类数据建一个表，各个表通过一些值相互关联，尽量保证同样的信息不会重复存储
一个表中的主键可以唯一标识对应的行，并且可以作为其它表的外键，与这个表相关联
联结：在一条select语句中联结多个表返回一组输出
相等联结
Select vend_name, prod_name, prod_price from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;
vend_name在vendors表中，prod_name和prod_price在products表中，用相等联结将两个表中的行对应匹配上，如果不加where条件进行联结，则会返回14*6=84行
内部联结
Select vend_name, prod_name, prod_price from vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;
与相等联结一致，另一种写法
联结多个表
select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id
AND ordertimes.prod_id = products.prod_id
AND order_num = 20005;
where子句定义了两个联结条件，第三个用来过滤订单20005中的物品
第十六章 创建高级联结.12.2
表别名
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’
表别名只在查询执行中使用，不返回客户机
列别名返回到客户机
自联结
查询生产DTNTR物品的供应商生产的其他物品
子查询方案
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.prod_id = ‘DTNTR’;
products表在from子句出现两次，具有二义性，通过使用表别名消除二义性
自然联结
所有内部联结都是自然联结
外部联结
Select customers.cust_id, orders.order_num
from custmers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
外部联结包括没有关联行的行，比如customers表中10002顾客没有下订单，在orders表中没有他的cust_id，使用外部联结会补充10002顾客的行，orders表相应位置的值为NULL
联结和聚集函数配合使用
Select c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ord
from customers as c INNER JOIN orders as o
ON c.cust_id = o.cust_id
GROUP BY c.cust_id;
第十七章 组合查询.12.2
组合查询：将多条Select语句的结果并在一起
UNION
Select vend_id,prod_id,prod_pirce from products where prod_price<=5
UNION
Select vend_id,prod_id,prod_pirce from products where vend_id IN (1001,1002);
UNION连接的每个查询必须包含相同的列、表达式或者聚集函数
列数据类型必须兼容
包含或取消重复的行
UNION默认去除重复的行，如果想返回所有行，使用UNION ALL
组合查询结果排序
UNION组合查询时最多使用一条ORDER BY语句，放在最后，对组合查询整体的结果进行统一排序
第十八章 全文本搜索.12.2
常用的搜索引擎：MyISAM(支持全文本搜索)、InnoDB(不支持)
启用全文本搜索支持
创建表时
create table productnotes
(
note_text text NULL,
FULLTEXT(note_text)
)ENGINE=MyISAM;
或者之后指定FULLTEXT
进行全文本搜索
Match()指定被搜索的列，Against()指定要使用的搜索模式
Select note_text from productnotes
where Match(note_text) Against(‘rabbit’);
全文本搜索默认不区分大小写
全文本搜索相对于LIKE的优势：
效率更高会根据匹配的相似度进行排序，排在前面的更可能是我们真正需要的行
查询扩展
Match() Against(’’ WITH QUERY EXPANSION)
搜索某个词，使用查询扩展时会找出直接匹配的以及其它可能有关联的行
Select note_text from productnotes
where Match(note_text) Against(‘anvils’ WITH QUERY EXPANSION);
布尔文本搜索
Match() Against(’’ IN BOOLEAN MODE)
布尔文本搜索没有FULLTEXT索引也可以使用
布尔文本搜索性能很差
提供信息：
要匹配的词要排斥的词(即使包含要匹配的词也不返回)排列提示(匹配词的重要等级)表达式分组另外内容
Select note_text from productnotes
where Match(note_text) Against(‘heavy -rope*’ IN BOOLEAN MODE);
-rope*：排除rope
布尔操作符
布尔方式不会按照匹配的相似度排序
第十九章 插入数据INSERT.12.3
插入完整的行
简单方法
INSERT INTO Customers
Values(NULL,
‘Pep E. LaPew’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
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);
插入多行
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_state,
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;
从custnew中将所有数据导入customers
INSERT和SELECT语句中的列名可以不同，按照位置对应起来SELECT语句可包含WHERE子句过滤数据
第二十章 更新UPDATE和删除DELETE数据.12.3
UPDATE
UPDATE customers
SET cust_name = ‘The Fudds’,
cust_email = ‘elmer@’
WHERE cust_id = 10005;
基本UPDATE语句三部分构成：
要更新的表列名和它们的新值，可以同时更新多列确定要更新行的过滤条件(如果没有where语句指定行则会更新所有行)
UPDATE语句可以使用子查询
为了去除某个列的值，可以设置为NULL
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
DELETE
DELETE FROM customers
WHERE cust_id = 10006;
删除某一行，用WHERE语句筛选指定，如果缺少where语句则删除表中所有行(可以用Truncate Table直接删除所有行，且不删除表，和delete from customers作用相同)
Delete删除整行而不是某一列位置上的数据，删除指定列需要用UPDATE语句
第二十一章 创建和操纵表.12.3
创建表CREATE TABLE
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL DEFAULT ‘Liu’,
cust_address char(50) NULL,
PRIMARY KEY (cust_id)
)ENGINE=InnoDB;
如果由多个列组成主键：(主键列必须使用NOT NULL的列)
PRIMARY KEY (order_num, order_item)
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引(如，指定它为主键)
更新表
在已有表上添加一列(需要指定数据类型)
ALTER TABLE vendors
ADD vend_phone CHAR(20)；
删除刚刚添加的列
ALTER TBALE vendors
DROP COLUMN vend_phone;
可以通过更新表定义外键
删除表
DROP TABLE customers2;
重命名表
RENAME TABLE customers2 TO customers;
Rename table backup_customers to customers,
backup_vendors to vendors,
backup_products to products;
第二十二章 使用视图.12.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;
使用视图
Select cust_name, cust_contact
from productcustomers
where prod_id = ‘TNT2’;
查看创建视图的语句
show create view productcustomers;
删除视图
Drop view productcustomers;
更新视图
Create or replace view productcustomers
AS …;
通过视图格式化检索出数据的格式
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;
代替计算字段的视图
CREATE VIEW orderitemexpanded AS
Select order_num, prod_id, quantity, item_price,
quantity*item_price AS expanded_price
From orderitems;
检索订单20005的详细内容，使用：
Select * From orderitemexpanded
Where order_num = 20005;
第二十三章 使用存储过程.12.5
存储过程：为以后的使用而保存的一条或多条MYSQL语句的集合，可将其视为批文件或其它编程语言中的函数
创建存储过程
如果在命令行：首先使用DELIMITER //将语句分隔符临时修改为//
DELIMITER //
Create PROCEDURE productpricing()
BEGIN
Select Avg(prod_price) AS priceaverage
From products;
END //
DELIMITER ;
其它地方可以不用修改分隔符
Create PROCEDURE productpricing()
BEGIN
Select Avg(prod_price) AS priceaverage
From products;
END;
执行存储过程
CALL productpricing();
删除存储过程
Drop PROCEDURE productpricing;
或
Drop PROCEDURE productpricing IF EXISTS;
使用参数：
存储过程的参数：pl ph pa参数类型：OUT(从存储过程中传出，返回给调用者)、IN(传递给存储过程)、INOUT(传入和传出)每个参数自己的数据类型：decimal(十进制)，与表中数据类型的定义相同INTO关键字：把结果保存到相应的变量中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;
调用这个存储过程：
@pricelow, @pricehigh, @priceaverage是自己定义的变量所有MySQL变量都需要以@开始CALL productpricing(@pricelow, @pricehigh, @priceaverage);
显示存储过程调用的结果
Select @priceaverage;
Select @pricehigh, @pricelow, @priceaverage;
检查存储过程
指定特定的存储过程，查看它的创建语句
Show create procedure ordertotal;
查看所有的存储过程的相关信息(何时、谁创建、哪个数据库中、安全类型等)
Show procedure status;
限定查看的存储过程
show procedure status Like ‘ordertotal’;
第二十四章 使用游标.12.6
游标：迭代器，每次执行一条并且自动向下递进
ordertotal存储过程：
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
输入订单号，统计该订单总金额并输出
processorders存储过程：
CREATE PROCEDURE processorder()
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;
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8,2));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o,t);
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END;
第二十五章 使用触发器.12.6
触发器：中断，事件发生时自动执行某条或某些语句
创建触发器
创建触发器需要的4条信息：
唯一的额触发器名触发器关联的表；触发器的触发事件(可以响应的事件有：DELETE、INSERT和UPDATE)触发器何时执行(处理之前或之后)
在MySQL8.0上实验不通过
第二十六章 管理事务处理.12.6
事务处理：锁/原子操作，保证成批的MySQL操作要么完全执行，要么完全不执行，能够保证数据库不包含不完整的操作结果
支持事务处理的引擎
MyISAM不支持
InnoDB支持
事务处理相关的概念
Rollback回退
Select * from ordertotals;
START transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
注：ROLLBACK只能在一个事务处理内使用(执行一条START transaction命令之后)，如果不加占位符，默认回退的位置就是在START transaction命令处
COMMIT
使用事务处理后，如果原子操作执行完成(不需要回退)，正式提交需要COMMIT
这在除了事务处理块以外的其它地方都是隐含了自动的COMMIT语句
保留点
SAVEPOINT delete1;
每个保留点都单独唯一命名，创建占位符以便可以选择回退到的地方
ROLLBACK TO delete1；
回退时的命令
RELEASE SAVEPOINT
明确释放保留点，或者在事务处理完成后自动释放
更改默认的提交行为
SET autocommit=0;
第二十七章 全球化和本地化.12.6
语言和字符集相关术语
字符集：字母和符号的集合编码：某个字符集成员的内部表示校对：规定字符如何比较
查看所有支持的字符集
SHOW character set;
查看所有支持的校对列表
SHOW collation;
创建表时可以对使用的字符集以及校对进行设置
对整个表指定一个字符集和一个校对顺序
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
对每个列设置
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebre_general_ci;
在SELECT时设置
SELECT * FROM customers
ORDER BY lastname,firstname COLLATE latin1_general_cs;
第二十八章 安全管理.12.7
安全管理：用户权限
查看所有用户账号
USE mysql;
SELECT user FROM user;
创建用户账号
CREATE USER ben IDENTIFIED BY ‘password’;
重命名用户账号
RENAME USER ben TO bforta;
删除用户账号
DROP USER bforta;
查看用户的访问权限
SHOW GRANTS FOR bforta;
授予用户权限
给出以下信息：
要授予的权限被授予访问权限的数据库或表用户名
GRANT SELECT ON crashcourse.* TO bforta;
合并多个授权
GRANT SELECT，INSERT ON crashcourse.* TO bforta;
撤销用户权限
REVOKE SELECT ON crashcourse.* FROM bforta;
权限列表
更改用户密码
SET PASSWORD FOR bforta = Password(‘new_password’);
设置自己的密码
SET PASSWORD = Password(‘my_new_password’);
第二十九章 数据库维护.12.7
数据备份
备份前使用FLUSH TABLES语句刷新未写数据
在打开或使用状态下的几种备份方案：
mysqldump命令转储所有数据库内容到某个外部文件mysqlhotcopy命令从一个数据库复制所有数据MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件，数据可以用RESTORE TABLE复原
数据库维护
检查表键是否正确
ANALYZE TABLE orders;
CHECK TABLE orders, orderitems;
诊断启动问题
查看日志文件
第三十章 改善性能.12.7
可能需要调整内存分配、缓冲区大小
查看当前设置，使用
SHOW VARIABLES;
和
SHOW STATUS;
多进程，查看活动进程
SHOW PROCESSLIST;
杀死进程
KILL + 进程号(如19);
**自动增量：**某些列需要唯一值，如顾客ID。在每个行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配。需要在CREATE语句创建表时把它作为表定义的组成部分。 ↩︎
