MySQL入门笔记整理
author by :https://blog.csdn.net/aihubei
1.什么是数据库
数据库：长期保存于计算机内且有组织的数据集合，通常是一个文件或者一组文件。以下是命令行中的程序示例：
show databases;
# 选用特定数据库
use 数据库名;
# 显示服务器状态信息
show status;
数据表：特定类型的数据的结构化清单，关系型数据库对应二维表。
show tables;
模式：有关数据库和表的布局以及特性的信息总称。
列：数据表中的一个字段，所有的表都由一个或者多个列组成。
show columns from 表名;
# 或者显示表结构
desc 表名；
# 或者
describe 表名;
数据类型：当前数据库中各字段容许的数据的类型。数据类型有助于正确排序数据，且有益于磁盘优化。
? data types;
行：数据表中的一个记录，有时候也叫作数据库记录。
主键：关键字为primary key，一列或者一组列，其值可以用于唯一区分表中的每个行。通常可以在建表或修改表时设置主键。
- 任意两行都具有不同的主键值
- 每个行都必须拥有一个主键值，主键列不允许为空–null
- 使用多个列作为主键时，其列的组合必须唯一，单个列的值可以重复。
SQL：结构化查询语言，structured query language。
常见的数据库软件：MySQL，Oracle， SQL Server等。其服务端口号依次为：3306， 1521， 1433。
2.检索数据
多条SQL语句需要使用分号(;)进行分隔，或者使用\g
SQL语句不区分大小写
检索不同的行，需要使用关键字distinct进行去重。
# 检索单个列
select prod_name
from products;
# 检索多个列,使用逗号间隔
select prod_id, prod_name, prod_price
from products;
# 检索所有列
# 一般不推荐使用通配符(*)，检索不必要的列会降低检索和程序性能
select *
from products;
# 检索不同的行
# distinct 关键字置于列名之前
select distinct vend_id
from products;
# 注意：distinct关键字作用于所有的列
# distinct之后的列都会进行去重操作
select distinct vend_id, vend_name,
查询结果限定，比如返回结果的前几行或者第一行，使用limit关键字：
# 返回前5行
select prod_name
from products
limit 5;
# 紧接着返回后续的5行,第一个数为开始位置，第二个数为行数
select prod_name
from products
limit 5, 5;
# 返回第一行
select prod_name
from products
limit 1;
# 返回第二行
select prod_name
from products
limit 1, 1;
当行数不够的时候，MySQL将仅仅返回其拥有的行数。
# 没有足够的行
select prod_name
from products
limit 10, 5;
# 如果实际上只有13行，则仅返回到13行为止
或者
# 从第三行开始，取四行
select prod_name
from products
limit 4 offset 3;
使用带有完全限定的列名：
# 使用格式，表名.列名
select products.prod_name
from products;
使用带有完全限定的表明：
# 使用格式为：数据库名.表名
select products.prod_name
from databases1.products;
3.排序检索数据
默认检索返回的数据顺序是以数据在底层表中的顺序显示的，为了对select语句返回的数据进行排序，使用order by字句。
# 排序,按照单个列
select prod_name
from products
order by prod_name;
为了实现按照多个列排序，需要指定列名，并以逗号分隔。
select prod_id, prod_price, prod_name
from products
order by prod_price, prod_name;
上面的例子，当且仅当多个行具有相同的prod_price的时候，才会按照prod_name进行排序，若prod_price各取值唯一，则不会按照prod_name排序。
指定排序方向：默认升序，即asc，为了实现降序，使用desc关键字。
# 实现降序排序
select prod_id, prod_price, prod_name
from products
order by prod_price desc;
desc关键字仅仅作用于直接位于其前面的列。若想实现在多个列上进行降序排序，必须对每个列指定desc关键字。
# 找出价格最贵的物品价格
select prod_price
from products
order by prod_price desc
limit 1;
# 使用函数
selece max(prod_price) as max_price
from products;
4.过滤数据
有时候仅仅需要检索出满足特定条件的数据，此时需要指定检索条件或搜索条件，搜索条件也称为过滤条件。其通过使用where字句实现。
# 检索价格为2.5的产品名，产品价格
select prod_name, prod_price
from products
where prod_price = 2.50;
# 检索单个值
select prod_name, prod_price
from products
where prod_name = 'fuses';
# mysql在执行的时候默认不区分大小写
# 检索出价格小于或大于或不等于10的产品信息
select prod_name, prod_price
from products
where prod_price>10;
select prod_name, prod_price
from products
where prod_price<10;
select prod_name, prod_price
from products
where prod_price!=10;
select prod_name, prod_price
from products
where prod_price<>10;
# 单引号用于限定字符串，如果将值与字符串类型的列进行比较，则需要限定引号。数值类型则不用
为了检索出特定范围内的值，使用between关键字。
# 检索出价格在5和10之间的产品
select prod_name, prod_price
from products
where prod_price between 5 and 10;
# 使用between关键字需要指定起始值和结束值，并使用and进行分隔。
空值检查，空值即没有值，其不等同于0， 空字符串以及空格。关键字为null。
sql提供了专门用于控制检查的where字句，即is null。
# 检索出价格为空的产品名称
select prod_name
from products
where prod_price is null;
5.数据过滤
使用where字句实现功能更强的搜索条件。MySQL允许给出多个where子句，并可以通过and和or进行组合。
# 检索出供应商为1003且价格不超过10的所有产品名称和价格
select prod_name, prod_price, prod_id
from products
where prod_price<=10 and vend_id = 1003;
# 使用or关键字检索出满足任一条件的产品信息
select prod_name, prod_price
from products
where vend_id = 1002 or vend_id = 1003;
# and在计算次序中优先级更高，因此通常使用小括号进行明确分组。示例如下三个查询
select prod_name, prod_price
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price>=10;
select prod_name, prod_price
from products
where vend_id = 1002 or vend_id = 1003 and prod_price>=10;
select prod_name, prod_price
from products
where vend_id = 1002 or (vend_id = 1003 and prod_price>=10);
# 因此，任何时候使用具有and和or操作符的where字句，都应该使用圆括号进行明确分组。
in操作符用于指定条件范围，范围中的每个条件都可以进行匹配。
# 检索供应厂为1002， 1003的所有产品
select prod_name, prod_price
from products
where vend_id in (1002, 1003)
order by prod_name;
# 删除检索等价于
select prod_name, prod_price
from products
where vend_id = 1002 or vend_id = 1003
order by prod_name;
# in操作符一般比or操作符速度更快
# 而且in操作符可以用于子查询，即包含其他select语句。
not操作符，用于否定其之后的所有条件。
# 检索出供应商为1002和1003以外的所有产品
select prod_name, prod_price
from products
where vend_id not in (1002, 1003)
order by prod_name;
select prod_name, prod_price
from products
where vend_id !=1002 and vend_id !=1003
order by prod_name;
6.使用通配符
通配符：用来匹配值的一部分的特殊字符，使用like操作符实现。
**百分号%**通配符：表示任意字符出现任意次数。
# 检索出所有以jet开头的产品
select prod_id, prod_name
from products
where prod_name like 'jet%';
# 检索出产品名中包含anvil的产品信息
select prod_id, prod_name
from products
where prod_name like '%anvil%';
# 检索出产品名以s开头，以e结尾的产品信息
select prod_id, prod_name
from products
where prod_name like 's%e';
尾部空格可能会干扰通配符进行匹配操作。例如，保存词anvil的时候，若其后面有一个或多个空格，则字句where prod_name like '%anvil’将不会对其进行匹配，解决方案如下：
# 方案1,直接在搜索模式尾部增加%
select prod_id, prod_name
from products
where prod_name like '%anvil%';
# 方案2，使用函数去掉右端空格
select prod_id, prod_name
from products
where prod_name like '%rtrim(anvil)';
注意null：%貌似可以匹配任何东西，但null除外，
# 一下语句依然无法匹配到null的行
select prod_id, prod_name
from products
where prod_name like '%';
# 办法，使用is null
select prod_id, prod_name
from products
where prod_name is null;
下划线_通配符：与%不同的是，下划线通配符仅匹配单个字符
# 示例如下
select prod_id, prod_name
from products
where prod_name like '_ton anvil';
# 该单个字符可以是字母，数字，或其他
通配符搜索的时间一般会较长，将通配符置于搜索模式的开头，搜索起来最慢。
7.使用正则表达式进行搜索
MySQL使用where字句对正则表达式提供了初步的支持，关键字regexp。
# 检索产品名中包含有文本1000的所有行
select prod_name
from products
where prod_name regexp '1000'
order by prod_name desc;
# 使用.进行特殊字符匹配
select prod_name
from products
where prod_name regexp '.1000'
order by prod_name desc;
# 此处的正则表达式.1000匹配结果有1000， 2000。这里的.表示匹配任意一个字符。
# 类似的，使用like表示如下
select prod_name
from products
where prod_name like '_1000'
order by prod_name desc;
like和regexp的区别：
# 使用like
select prod_name
from products
where prod_name like '1000'
order by prod_name;
# 使用regexp
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
# 结果，第一条语句不返回数据，第二条语句有返回
# 原因：like匹配整个列，若被匹配的文本在列中出现，like将不会找到他，相应的行也不会被返回，除非使用通配符；而regexp在列值内进行匹配，找到后将做返回。
匹配不区分大小写：MySQL中的正则表达式不区分大小写，为了进行区分，可以使用binary关键字。
# 例如
where prod_name regexp binary 'jetPack .000'；
进行or匹配：为了搜索两个串之一，使用"|"。
# 示例
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;
# |为正则表达式的or操作符，匹配其中之一
select prod_name
from products
where prod_name like ('%1000%') or ('%2000%')
order by prod_name;
# 在功能上，|类似于select语句中的or语句，多个or条件可以并入单个正则表达式
# 例如
'1000|2000|3000' 可以用于匹配1000 or 2000 or 3000
匹配任何单一字符：使用中括号[]
# 匹配任意产品名以1或2或3开头，并以ton结尾的产品信息
select prod_id, prod_name
from products
where prod_name regexp '[123] ton'
order by prod_name;
# 同等表示如下
select prod_id, prod_name
from products
where prod_name regexp '[1|2|3] ton'
order by prod_name;
匹配范围:
# 匹配数字0到9
[0123456789]
# 或者
[0-9]
# 匹配任意字母字符
[a-z]
[A-Z]
# 示例
select prod_name
from products
where prod_name regexp '[1-5] ton'
order by prod_name;
匹配特殊字符：为实现对特殊字符的匹配，使用转义字符。双下线后接目标字符
# 示例,供应商名字中带有.的信息
select vend_name
from vendors
where vend_name regexp '\\.'
order by vend_name;
# 为了匹配反斜杠\本身，使用形式为\\\
匹配字符类：
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字，[a-zA-Z0-9] |
| [:alpha:] | 任意字符，[a-zA-Z] |
| [:blank:] | 空格和制表 |
| [:cntrl:] | ASCII控制字符，ASCII0到31和127 |
| [:digit:] | 任意数字,[0-9] |
| [:graph:] | 与[:print:]相同，但不含空格 |
| [:lower:] | 任意小写字母，[a-z] |
| [:print:] | 可以打印任意字符 |
| [:punct:] | 不在[:alnum:]和[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符 |
| [: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]表示匹配数字0到9中的任一个，s之后的?使得s可选，因为?匹配它前面的字符出现0次或者1次。
# 使用字符类和元字符
select prod_name
from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;
# [:digit:]表示匹配任意数字，{4}表示数字出现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;
^的双重用途：在集合中(使用[]定义),表示否定该集合，同时其也可以用于表示字符串的开始。
在MySQL命令行中进行正则表达式的测试：可以使用带有文子串的regexp来测试表达式，
# 示例
select 'xiaoyao' regexp '[a-z]';
# 结果将返回1
# 示例
select 'xiaoyao' regexp '[0-9]';
# 结果将返回0
8.创建计算字段
计算字段：并未真实存在于数据库表中，而是在运行时于select语句内创建的。
拼接：通过concat()函数实现将值联结到一起形成单个值。
# 示例
select concat(vend_name, vend_country) as huizong
from vendors
order by vend_name;
# concat()拼接串，也就是将多个串联结起来形成一个较长的串
# concat()需要指定一个或者多个指定的串，各个串之间使用逗号进行分隔
# 示例，同时通过rtrim()去除串右端的空格
select concat(rtrim(vend_name), rtrim(vend_country))
from vendors
order by vend_name;
# 对应的可以使用ltrim()去除串左端的空格
使用别名：上述示例中联结后的列名字很长，可疑通过as关键字进行别名。
# 示例
select avg(prod_price) as avg_price
from products
别名有时候也称为导出列。
执行算术运算：这是计算字段的另一个常见用途，orders表包含收到的所有订单，orderitems表包含每个订单中的各项物品。
# 检索出订单号为20005中的所有物品
select prod_id, quantity, item_price
from orderitems
where order_num = 20005;
# item_price列包含的是订单中每项商品的单价
# 示例，汇总商品的总价
select prod_id, quantity, item_peice, quantity*item_price as all_peice
from orderitems
where order_num = 20005;
MySQL提供了测试和试验函数与计算的一个好办法，即省略from字句，以便简单的访问和处理表达式。
# 示例，计算加法
select 1+1;
# 示例，查看当前时间
select now();
# 示例，计算两个日期之间的相差天数
select datediff(now(), '1996-10-16') as dayoflife;
# 我已经活了8946天了
# 示例，测试trim()
select trim('abc');
# 结果将返回abc
9.使用数据处理函数
多数SQL实现支持的函数类型：文本串处理函数(删除或者填充值，转换为大小写等)；对数值数据进行算术运算(返回绝对值，代数运算等)；日期时间处理函数(返回当前日期时间，计算两个日期只差等)；DBMS的特殊信息等(返回用户登录信息，检查版本信息等)等。
文本处理函数：
# 示例，大写转换
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() | 将串转换为大写 |
# 示例
select left('xiaoyao', 4);
# 返回xiao
# 示例
select length('xiaoyao');
# 返回7
# 示例
select locate('yao','xiaoyao');
# 返回5，即第一个串在第二个串中的起始位置，从1开始
# 示例
select lower('XIAOYAO');
# 返回xiaoyao
# 示例
select right('xiaoyao', 3);
# 返回yao
# 示例
select soundex('xiaoyao');
# 本机返回X000
# 示例
select substring('xiaoyao', 5);
# 返回yao,即返回从起始位置为5开始的字串
# 示例
select upper('xiaoyao');
# 返回XIAOYAO
soundex()：是一个将任何文本串转换为其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得此函数可以对串进行发音比较，而不是字母比较。
# 示例，soundex()的一个用途
# 假设customers表中有一个顾客Coyote Inc.,其联系名为Y.Lee。但由于输入错误而实际应该为Y.Lie
# 这里，按照正确的联系名Y.Lie进行检索显然无法得到想要的数据，但操作如下
select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex('Y.Lie');
# 因为Y.Lie与Y.Lee发音相似，以此检索返回数据
日期和时间处理函数：日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
| 函数 | 说明 |
|---|---|
| adddate() | 增加一个日期；天，周等 |
| addtime() | 增加一个时间；时，份等 |
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| date() | 返回日期时间的日期部分 |
| datediff() | 计算两个日期相差天数 |
| date_format() | 返回一个格式化的日期或时间串 |
| day() | 返回一个日期是第几天 |
| dayofweek() | 返回一个日期对应的是本周第几天 |
| hour() | 返回一个时间的小时部分 |
| minute() | 返回一个时间的分钟部分 |
| month() | 返回一个日期的月份部分 |
| second() | 返回一个时间的秒部分 |
| time() | 返回一个日期的时间部分 |
| year() | 返回一个日期的年份部分 |
| now() | 返回当前日期和时间 |
# 示例
select curdate();
# 返回2021-04-15
# 示例
select curtime();
# 返回07:30-27
# 示例
select date('2021-04-15 07:30:55');
# 返回2021-04-15
# 示例
select datediff(now(), '1996-10-16');
# 返回8947
# 示例
select day(now());
# 返回15
# 示例
select dayofweek(now());
# 返回5
# 示例
select hour(now());
# 返回7
# 示例
select now();
# 返回2021-04-15 07:45:26
无论是插入或更新表值，还是用where字句进行过滤，日期必须为格式yyyy-mm-dd。
# 示例
select cust_id, order_num
from orders
where order_date = '2005-09-01';
# 如果表中实际存储的是当时的now()
# 上述检索更改为
select cust_id, order_num
from orders
where date(order_date) = '2005-09-01';
# 如果要的仅仅是日期，则使用date();如果要的仅仅是时间，则使用time();
# 示例，检索出2005年9月下的所有订单
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() | 计算正切 |
# 示例
select mod(3,2);
# 返回1
# 示例
select abs(2-3);
# 返回1
# 示例
select exp(3);
# 返回20.0855...,自然指数e的3次方
# 示例
select sqrt(4);
# 返回2
# 示例
select sqrt(-4);
# 返回null
10.汇总数据
经常需要汇总数据而不用将它们实际检索出来，常见的像：确定表中的行数，找出表列最大值、最小值、平均值等。这些操作是通过MySQL提供的聚集函数实现的。
avg()函数：对表中行数计数并计算特定列之和，求取该列的平均值。
# 检索所有产品平均价格，并使用别名avg_price
select avg(prod_price) as avg_price
from products;
# avg()也可以用来确定特定列或者行的平均值
select avg(prod_price) as avg_price
from products
where vend_id = 1003;
# avg()只能用于确定特定数值列的平均值，且列名必须作为函数参数给出。
# 为了获得多个列的均值，必须使用多个avg()函数。
# avg()函数忽略值为null的行。
count()函数：使用count()确定表中行的数目或者符合特定条件的行的数目。count(*)或者count(column)，前者不忽略null值，后者忽略null值。
# 检索customers表中客户的总数
select count(*) as num_cust
from customers
# 统计具有电子邮件的客户数量,自动忽略null
select count(cust_email) as num_cust
from customers
max()函数：返回指定列中的最大值，max()函数忽略null值的行。
# 检索产品的最高价格
select max(prod_price) as max_price
from products
# 检索具有最高产品价格的产品记录
select prod_id, prod_name, prod_price
from products
order by prod_price desc
limit 1;
# 检索具有最高产品价格的产品记录
select prod_id, prod_name, prod_price
from products
where prod_peice = max(prod_price);
min()函数：min()的功能正好与max()功能相反，min()也忽略值为null的行。
# 检索产品的最低价格
select min(prod_price) as min_price
from products
# 检索具有最高产品价格的产品记录
select prod_id, prod_name, prod_price
from products
order by prod_price asc
limit 1;
# 检索具有最高产品价格的产品记录
select prod_id, prod_name, prod_price
from products
where prod_peice = min(prod_price);
sum()函数：返回指定列的和，sum()函数忽略值为null的行。
# 检索所有订单数之和
select sum(quantity) as items_ordered
from orderitems
where order_num = 20005;
# 检索订单总金额
select sum(item_price*quantity) as total_peice
from orderitems
where order_num = 20005;
聚集不同的值：利用关键字distinct，默认all，即统计所有值，包括重复值。
# 检索不同价格的产品的均价
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;
组合聚集函数：在一个select语句中包含多个聚集函数
# 示例
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;
11.分组数据
主要是通过group by 和 having字句对数据进行分组。group by字句出现在where字句后，order by字句前。
# 返回特定供应商提供的产品数量
select count(*) as num_prods
from products
where vend_id = 1003;
现在要求，返回每个供应商提供的产品数量，并分组显示。这样就需要使用到分组
# 分别统计每个供应商提供的产品数量
select vend_id, count(*) as num_prods
from products
group by vend_id;
# 结果显示两列，并按照vend_id进行分组数据。
with rollup关键字：可以得到每个分组，以及每个分组的汇总级别
# 示例
select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;
过滤分组：where字句过滤的是指定的行而不是分组，或者说where没有分组的概念。此时应该使用having字句，having过滤的是分组。
要求：列出至少具有两个订单的所有客户。
# 检索订单数量大于2的客户以及相对应的订单数
select cust_id, count(*) as orders
from orders
group by cust_id
having count(*) >=2;
where在数据分组之前进行过滤，having在数据分组之后进行过滤。
# 检索具有2个以上，且价格大于10的产品的供应商。
select vend_id, count(*) as num_prods
from products
where prod_price >= 10
group by vend_id
having count(*) >= 2;
分组和排序：
# 检索总计订单价格不低于50的订单号和总订单价格,结果未排序
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50;
# 检索总计订单价格不低于50的订单号和总订单价格,并依据总订单价格对结果进行降序排序
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50;
order by ordertotal desc;
select字句顺序：
| 字句 | 说明 | 是否必须使用 |
|---|---|---|
| select | 要返回的列或表达式 | 是 |
| from | 从何表进行检索 | 仅在选择数据时使用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在按组计算聚集时使用 |
| having | 分组过滤 | 否 |
| order by | 输出排序顺序 | 否 |
| limit | 要检索出的行数 | 否 |
# 检索总计订单价格不低于50的订单号和总订单价格,并依据总订单价格对结果进行降序排序，并返回6-10行
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50;
order by ordertotal desc;
limit 5, 5;
12.使用子查询
sql支持子查询，即嵌套在其它查询中的查询。
说明：orders表包含订单号，客户ID，订单日期等；orderitems表包含各订单的物品等；customers表包含实际的客户信息。
# 检索出所有订购了物品TNT2的客户id
select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id = 'TNT2');
# 检索出所有订购了物品TNT2的客户的详细信息
select cust_name, cust_contact
from customers
where cust_id in (select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id = 'TNT2'));
SQL对于嵌套的子查询的数目没有限制，但出于性能的考虑，不能嵌套太多的子查询。
使用计算字段作为子查询：
# 检索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;
13.连接表
外键：外键为表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
# 创建联结
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;
完全限定列名：在引用的列可能出现二义性的时候，需要使用完全限定列名，即table_name.column_name。
这里的where字句不可少，若缺乏where字句，则第一个表中的每个行将与第二个表中的每个行进行配对，即没有联结条件的表关系返回的结果为笛卡尔积。总行数为两个表行数乘积。
# 结果返回笛卡尔积
select vend_name, prod_name, prod_price
from vendors, products
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;
# 此处传递给on的实际条件与传递给where的条件相同
联结多个表：SQL对一条select语句中可以联结的标的数目没有限制。
# 示例,检索编号为20005的订单中的物品价格，名称，供应商名称，以及订购数量
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 = 20005;
一般不要联结不必要的表，联结的表越多，则有更多的性能损失。
# 示例，检索订购了产品TNT2的客户列表--使用子查询
select cust_name, cust_contact
from customers
where cust_id in (select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id = 'TNT2'));
# 示例，检索订购了产品TNT2的客户列表--使用表连接
select cust_name, cust_contact
from custmers, orders, orderitems
where custmers.cust_id = orders.cust_id
and orders.order_num = orderitems.order_num
and prod_id = 'TNT2';
14.创建高级联结
之前给列名，计算字段使用了别名，这里也可以对表进行别名。好处是可以缩短SQL语句，同时可以实现在单一select语句中多次使用相同的表。
# 示例，检索订购了产品TNT2的客户列表--使用表连接
select cust_name, cust_contact
from custmers as C, orders as O, orderitems as OI
where C.cust_id = O.cust_id
and O.order_num = OI.order_num
and prod_id = 'TNT2';
表的别名与列的别名不同，其别名不返回到客户机。
使用自联结：
# 检索生产了产品id为DTNTR的供应商生产的其他产品--使用子查询
select prod_id, prod_name
from products
where vend_id = (select vend_id
from products
where prod_id = 'DTNTR');
# 检索生产了产品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';
使用自然联结：自然联结排除多次出现，使得每个列仅返回一次。
# 示例
select C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
from custmers as C, orders as O, orderitems as OI
where C.cust_id = O.cust_id
and O.order_num = OI.order_num
and prod_id = 'FB';
外部连接：联结包含了那些在相关表中没有关联行的行。
# 对每个客户下了多少订单进行计数， --包括至今尚未下订单的客户--
# 列出所有产品以及订单数量，--包括没有人订购的产品--
# 计算平均销售规模，--包括至今尚未下订单的客户--
# 使用内部连接，检索所有客户以及订单数
select customers.cust_id, orders.order_num
from customers inner join orders
on vustomers.cust_id = orders.cust_id;
# 使用外部连接，检索所有客户，包括没有订单的客户
select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;
# 这里没有下单的客户也被检索出来，对应的order_num值为null
left outer joni包含左边表中的所有行，right outer join包含右边表中的所有行，两者都包含在相应表中没有对应的行，其值为null。
使用具有聚集函数的联结：
# 检索所有客户，以及每个客户所下订单数
select customers.cust_name,
customers.cust_id,
count(order_num) as num_ord
from customers inner join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
# 检索所有客户，以及每个客户所下订单数，--包括没有下单的客户--
select customers.cust_name,
customers.cust_id
count(order_num) as num_ord
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
15.组合查询
MySQL允许执行多个查询，并将结果作为单个查询结果进行返回。这些组合查询称为并(union)或者复合查询(compound query)。
-
在单个查询中从不同的表返回类似结构的数据；
-
对单个表执行多个查询，按照单个查询返回数据；
创建组合查询：使用union操作符来组合数条SQL查询，在各条语句之间放上关键字union。
# 检索价格不超过5的所有物品，此外还需检索供应商1001和1002生产的所有物品--使用union-- # 单一语句 select vend_id, prod_id, prod_price from products where prod_price <=5; # 单一语句 select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002); # 组合上述两条语句 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); # 实际上，上述组合语句也可以通过多条where字句实现 select vend_id, prod_id, prod_price from products where prod_price <=5 or vend_id in (1001, 1002);union注意：每个查询必须包含相同的列、表达式或者聚集函数，但是各自出现的顺序可以不一致。
其次，各个列的数据类型必须兼容，即类型不必完全相同。例如：不同的数值类型或不同的日期类型。
包含或者取消重复的行：–union从查询结果集中自动去除了重复的行–
如果需要返回所有的行，需要使用关键字union all。
# 示例，重复的行也被返回 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);对组合查询的结果进行排序：在使用union组合查询时，仅可以使用一条order by 字句，且其出现位置必须出现在最后一条select语句之后。
理解：对于结果集，不允许各部分的排序方式不同，
# 示例 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 vend_id, prod_price; # 这里的一条order by 实现了对所有的结果进行排序union组合查询可以用于不同的表。
16.全文本搜索
注意，对于数据库引擎myisam和innodb，前者支持全文本搜索，后者不支持。
like关键字：利用通配操作符匹配文本，可以查找到包含特殊值或部分值的行。不论这些值位于什么位置。
基于文本的正则表达式regexp：可以编写查找所需行的非常复杂的匹配模式。
上述方式存在的限制：
- 通配符和正则表达式匹配通常要求MySQL尝试匹配表中的所有行，耗时。
- 通配符和正则表达式很难明确地控制匹配内容。
- 无法区分包含单个匹配和行和包含多个匹配的行，同时无法实现：对一个特殊词的搜索任务，找出不包含该词，但包含其他词的行。
- …
全文本搜索：在使用全文本搜索的时候，MySQL无需分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各个词的一个索引，搜索工作可以针对这些词进行。快速，高效，智能。
为了进行全文本搜索，必须索引被搜索的列，且随着数据的改变不断地重新索引。
在索引之后，select可与match()和against()一起使用，从而执行搜索任务。
启用全文本搜索：create table语句接受fulltext字句，
# 示例，演示fulltext字句的作用
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;
# 为了进行全文本搜索，MySQL根据fulltext(note_text)的指示，对其进行索引。
# 这里的fulltext索引单个列，但也可以指定多个列
定义之后，MySQL自动维护该索引。在增加、更新或删除行的时候，索引随之更新。
不要在导入数据的时候使用fulltext，应该首先导入所有的数据，然后修改表，定义fulltext。
进行全文本搜索：使用match()和against()两个函数执行全文本搜索。前者指定被搜索的列，后者指定使用的搜索表达式。
# 示例终于来了
select note_text
from productnotes
where match(note_text) against('rabbit');
# 由于有两行包含'rabbit',所以返回两行
# 使用like通配符实现上述示例
select note_text
from productnotes
where note_text like '%rabbit%';
# 这个查询也返回两行，但返回顺序与上述的查询顺序不同
上述两条select语句都不包含order by 字句，但前者返回以文本匹配的良好程度排序的数据。
全文本搜索的一个重要部分就是实现对结果的排序。具有较高等级的行，将先返回，
# 示例
select note_text , match(note_text) against('rabbit') as RanK
from productnotes;
# 这里没有写出where过滤条件，则所有的行都被返回，无论是否包含'rabbit'
# 但是，结果中多了一列，即全文本搜索的等级值。
该等级值的计算原理：等级由MySQL根据行中词的总数据、唯一词汇的数目、整个索引中词的总数以及包含该词的行数计算出来。
- 不包含‘rabbit’的行，等级为0
- 包含‘rabbit’的每个行都有一个等级值，但是目标词靠前的行，具有更高的等级值。
使用查询扩展：查询扩展用来放宽所返回的全文本搜索结果的范围。关键词：with query expansion
# 示例，不含查询扩展
select note_text
from productnotes
where match(note_text) against('anvils');
# 只有一行包含anvils，因此仅返回一行
# 使用查询扩展，返回包含关键词的行，同时返回“可能重要的行”
select note_text
from productnotes
where match(note_text) against('anvils' with query expansion);
# 返回若干行，第一行包含关键词‘anvils’等级最高，第二行包含第一行中的两个词，所以也被检索出来。
# 第三行也包含这两个相同的词，但它们在文本中的位置更靠后，因此等级更低一级。
# and so on
布尔文本搜索：按照布尔方式，可以提供一下细节内容：
- 要匹配的词。
- 要排斥的词。某行包含这个词，则不返回改行。
- 排列提示，更重要的词具有更高的等级。
- 表达式分组。
- 其他。
即便没有使用fulltext，也可以使用布尔索引。–非常缓慢–
# 示例,匹配包含heavy但不包含任意以rope开始的词的行
select note_text
from productnotes
where match(note_text) against('heavy -rope*' in boolean mode);
# -rope*指示MySQL排除包含rope*的行。
# -排除一个词，而*是阶段操作符。
| 全文布尔操作符 | 说明 |
|---|---|
| + | 包含，词必须存在 |
| - | 排除，词必须不存在 |
| > | 包含，且增加等级值 |
| < | 包含，且降低等级值 |
| () | 把词组成子表达式 |
| ~ | 取消一个词的排序值 |
| “” | 定义一个短语，此时匹配整个短语 |
| * | 词尾的通配符 |
# 示例,检索包含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 bait"' in boolean mode);
# 示例，检索包含rabbit和bait的行，增加前者等级，降低后者等级
select note_text
from productnotes
where match(note_text) against('>rabbit <bait' in boolean mode);
# 示例，检索包含词汇sage和combination的行，并降低后者等级
select note_text
from productnotes
where match(note_text) against('+safe +(<combination)' in boolean mode);
排列但不排序：在布尔方式中，返回的行不按照等级值降序排。
17.插入数据
使用insert插入数据：可以插入完整的行、插入多行、行的一部分、插入某些查询的结果。
# 插入完整的数据行,指定表明，值
insert into customers
values(null, 'Pep E.Lapew',
'100 main street','los angeles',
'ca','90046','usa',null,null);
# insert 语句一般不会产生输出
# 这里的第一列是指cust_id,插入的值为null. 因为每次插入一个新的行，该列由MySQL自动增长 auto_increment
# 更可靠的写法，标明列名
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语句可能很耗时，可以通过在insert和into之间添加关键字low_priority降低insert语句的优先级。
# 一次性插入多行数据，语句之间使用分号间隔，一次性提交
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,
cust_contact,
cust_email)
values('Pep E.Lapew',
'100 main street','los angeles',
'ca','90046','usa',null,null);
# 或者，若每条insert语句中的列名和次序相同，可以如下组合
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),
values('Pep E.Lapew',
'100 main street','los angeles',
'ca','90046','usa',null,null),
values('Pep E.Lapew',
'100 main street','los angeles',
'ca','90046','usa',null,null);
# 即每一组值使用圆括号括起来，并使用逗号分隔
# 第二种方法，是一个单条insert语句，可以有效提高数据库性能
插入检索的数据：即把一条select语句的结果作为values插入到数据表中，insert select
# 从表custnew中检索出数据，并将其插入到数据表customers中去
insert into customers(
cust_id,
cust_contact,
suct_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
select cust_id,
cust_contact,
suct_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
from custnew;
上述代码中的insert 和select语句中使用了相同的列名，但是不一定要求列名匹配。事实上，MySQL不关心select返回的列名，它使用的是列的位置。
insert select 中的select语句可以包含where字句，从而过滤数据，有选择性的插入。
18.更新和删除数据
注意区分，更新，删除表结构的操作。这里是更新和删除数据！
更新或者修改表中的数据，使用update语句，更新方式：更新表中特定行或者更新表中所有行。
注意：在使用update的时候，一般要考虑好where字句，否则就会更新表中所有数据。
# 示例，更新id为10005客户的电子邮件
update customers
set cust_email = 'aihubei@163.com'
where cust_id = 10005;
set命令用来将新值赋予被更新的列。
# 示例，更新多个列
update customers
set cust_name = 'aihubei',
cust_email = 'zuel@163.com'
where cust_id = 10005;
更新多个列，只需要使用单个set命令。
若update语句更新多行，且在更新这些行中的一行或者多行发生错误的时候，则整个update操作被取消。
为实现即便发生部分错误，也继续进行更新操作，可以使用ignore关键字。
# 示例
update ignore customers...
删除某个列的值：若该列允许null值，像删除该列的某个值，可以将其设置为null
# 示例，删除id为10005客户的邮箱
update customers
set cust_email = null
where cust_id = 10005;
删除数据：从表中删除数据，使用delete语句，可以删除特定行或者删除所有行。
# 示例，删除id为10006的客户信息--删除整行--
delete from customers
where cust_id = 10006;
# 删除表中所有行--不建议--
delete from customers;
注意：delete删除的是表中的数据，某行或者所有行。但是delete不删除表结构本身。
注意：如果想要删除表中的所有行，不要使用delete语句。而是使用truncate table语句。truncate完成相同的工作，但速度更快。
注意：truncate删除表中所有的数据实际上是：先删除原来的表并重新建立该表，而不是逐条删除表中的数据记录。
19.创建和操纵表
创建表的一般方式：交互式创建或者管理工具，比如navicate，access，可视化操作界面等；或者使用MySQL语句进行操纵。使用程序建表，即create table语句。
# 示例，创建customers表
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 if not exists 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;
使用null：null就是没有值或者缺失值。允许null值的列也支持在插入行的时候不给出values。
# 创建orders表
create table orders
(
order_num int not null auto_incrementmt,
order_date datetime not null,
cust_id int not null,
primary key(order_num)
)engine = innodb;
# 指定了not null的列，在插入值的时候，必须要有合规的值，且不允许为空
# 创建vendors表
create table if not exists vendors
(
vend_id int not null auto_increment,
vend_name char(50) not null,
vend_address char(50) null,
vend_city char(50) null,
vend_state char(5) null,
vend_zip char(10) null,
vend_country char(50) null,
primary key(vend_id)
)engine = innodb;
null为默认设置，若不进行not null的指定，则默认为null。
null代表没有值，值缺失，不等同于空串，不等同于空格。’'代表两个单引号，之间没有值，这可以用于not null的列。而空串是一个有效值，它不是无值。
主键：若主键使用的是单个列，则该列的值不允许重复，且不允许为空。若主键由多个列组成，则这些列的组合值必须唯一。
使用联合主键：
# 创建orderitems表
create table orderitems
(
order_num int not null,
order_item int not null,
prod_id char(10) not null,
quantity int not null,
item_price decimal(8, 2) not null,
primary key(order_num, order_item)
)engine=innodb;
使用auto_increment：比如customers表中的cust_id，orders表中的order_num，
auto_increment的作用：实现列值自动增长，当前列每增加一行就自动增长。
每个表只允许一个auto_increment列，且它必须被索引，比如通过使其成为主键。MySQL主键默认索引
可以覆盖auto_increment：若一个列被指定为了auto_increment自增长，我们还是可以通过insert指定该列的一个值，且该值满足唯一即可。这样一来，该值就被用来替代自动生成的值，后续增量便以该值为增长起点。
last_insert_id：设置了auto_increment自增长的列，我们无法时刻知道该值是多少，怎样获取？
# 方法,使用last_insert_id()函数实现获取
select last_insert_id();
# 上述语句返回最后一个auto_increment值
指定默认值：默认值的指定通过default关键字实现，MySQL不允许使用函数作为默认值，仅支持常量。
# 示例,指定quantity的默认值为1
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;
引擎类型：上述创建表的时候，指定引擎。改引擎具体创建表，且在进行select语句或者其他数据库操作的时候，该引擎在内部进行请求处理。
innodb:不支持全fulltesxt，支持事务，但提供了行级锁和外键约束，用于处理大数据容量的数据库系统；
memory：数据存储在内存，速度很快，适合于临时表；
myisam：支持全文本搜索，速度极高，不支持事务处理；
# 查看MySQL的引擎
show engines;
更新表：属于表级操作，使用alter table语句。alter table之后给出表名，所做的更改；
# 示例，添加表列
alter table vendors
add vend_phone char(20);
# 示例，删除表列
alter table vendors
drop column vend_phone;
alter table的一种常见用途是定义外键：
# 示例，使用alter table定义数据表中的外键
alter table orderitems
add constraint fk_orderitems_orders
foreign key(order_num) references orders(order_num);
# 示例，使用alter table定义数据表中的外键
alter table orderitems
add constraint fk_orderitems_products
foreign key(prod_id) references products(prod_id);
# 示例，使用alter table定义数据表中的外键
alter table orders
add constraint fk_order_customers
forign key(cust_id) references customers(cust_id);
# 示例，使用alter table定义数据表中的外键
alter table products
add constraint fk_products_vendors
forign key(vend_id) references vendors(vend_id);
删除表：删除表结构而非其内容，直接使用drop table进行删除。
使用alter table应该小心，最好在改动之前进行数据表的备份。
# 示例，删除表customers2
drop table customers2;
# 删除表customers2，删除表没有确认，亦无法撤销，该表被永久删除
重命名表：使用rename table进行表的重命名
# 示例，将customers2重命名为customers
rename table customers2 to customers;
# 示例，重命名多个表
rename table backup_customers to customers,
bacuup_vendors to vendors,
backup_products to products;
20.使用视图
视图是虚拟的表，与包含数据的表不同，视图仅包含使用时动态检索数据的查询。
# 示例,检索出订购了某个产品的客户
select cust_name, cust_contact
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and prod_id = 'TNT2';
# 示例，将上述整个查询包装成为一个名为productcustomers的虚拟表
select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';
# 这就是视图的作用，productcustomers是一个视图，作为视图，它不含表中任何列或数据，包含的仅仅是一个# SQL查询
使用视图的好处：
- 实现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。
利用视图简化复杂的联结：
# 示例,检索订购了任意产品的客户列表，并以其建立视图productcustomers
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;
# 利用上述的视图，检索出订购了产品TNT2的客户
select cust_name, cust_contact
from productcostomers
where prod_id = 'TNT2';
# 利用视图可以一次性编写基础的SQL，然后根据实际需要进行多次使用
使用视图重新格式化检索出的数据：
# 示例，在单个检索列中返回供应商名和位置
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;
# 从视图中进行检索
select *
from vendorlocations;
使用视图过滤不想要的数据：
# 示例，建立视图customeremaillist，实现对没有电子邮件客户的过滤
create view customeremaillist as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;
# 使用该视图，进行数据检索
select *
from customeremaillist;
如果从视图中检索数据的时候，使用了一条where子句，则原始检索语句中的where子句将与该子句自动组合。
使用视图与计算字段：视图对于简化计算字段的使用特别有用。
# 示例，检索某个订单中的物品，并计算每种物品的总价格
select prod_id,
quantity,
item_price,
quantity*item_peice as expand_peice
from orderitems
where order_num = 20005;
# 将上述检索转换为一个视图
create view orderitemsexpanded as
celect order_num,
prod_id,
quantity,
item_price,
quantity*item_price as expand_price
from orderitems;
# 利用视图，检索出订单20005的详细内容
select *
from orderitemsexpanded
where order_num = 20005;
更新视图：更新视图，将更新其基础表，若对视图增加或者删除行，实际上是对其基本表增加或者删除行。若视图中有一下操作，则该视图不允许更新：
- 分组，使用group by 和having
- 联结，
- 子查询
- 并
- 聚集函数，min(),count(),sum()等
- distinct
- 导出(计算)列
视图主要用于数据检索。
21.使用存储过程
存储过程是一组预编译的SQL语句的集合，可以将其视为批文件。
# 示例，按要求编写sql语句。描述如下：
- 为了处理订单，需要核对以确保库存中有相应的物品
- 如果有物品，这些物品需要预定以便不将其再卖给别的人，且需要减少可用的物品数量
- 库存中若没有物品，则需要从供应商处进行订购
- 关于哪些物品入库和哪些物品退订，需要通知相应的客户
# 完成上述任务，传统的单挑SQL语句无法一次性实现
为什么要是用存储过程：简化复杂的操作；无需反复建立一系列的处理步骤，保证了数据的完整性；提高了性能，因为使用存储过程比使用单独的SQL语句要快；
执行存储过程：MySQL将存储过程的执行称为调用。MySQL调用存储过程使用call。
call接受存储过程的名字，同时还有需要传递给他的任意参数，调用示例如下：
# 调用存储过程
call productpricing(
@pricelow,
@pricehigh,
@priceaverage
);
# 上述代码执行名为productpricing的存储过程，计算并返回产品的最低，最高和均价。
创建存储过程：
# 创建存储过程，返回产品均价
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
# 上述示例无法在MySQL命令行中直接正确执行，因为分号为一条SQL语句的结束标志
# 解决办法如下：临时修改语句分隔符
delimiter //
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end //
delimiter ;
# 调用上述存储过程,存储过程实际是一种函数
call productpricing();
删除存储过程：
# 示例，删除存储过程
drop procedure productpricing;
# 和调用存储过程不同，这里不需要括号。
# 或者，更加稳健的写法
drop procedure if exists productpricing;
# 删除存储过程，当且仅当其存在
使用参数：基于参数的使用，存储过程将结果返回给指定的变量。变量实际上是一个特定的位置，用来临时存储数据。
# 示例，创建productpricing的修改版本
delimiter //
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 //
delimiter ;
# 说明：次存储过程接受三个参数，分别为最低，最高，平均价格；
# 关键字out指出相应的参数用来从存储过程传出一个值，返回给调用者
# MySQL支持in(传递给存储过程)，out(从存储过程传出)，inout(对存储过程传入和传出)类型的参数。
# begin和end之间的部分代表存储过程体，这里是一系列的select语句，用来检索值，然后通过into保存到相应# 的变量
不能通过一个参数返回多个行和列，因此上述存储过程使用了三条select语句。
# 调用上述存储过程,调用时必须指定3个变量名
call productpricing(
@pricelow,
@pricehig,
@priceavg
);
# 所有的mysql变量都需要以@开头
# 调用存储过程之后，并没有显示任何数据
# 利用上述定义好的存储过程和调用程序，检索产品的均价
select @priceavg;
# 检索出最低，最高，均价
select @pricelow, @pricehig, @priceavg;
# 示例，给存储过程传入参数，这次同时使用in和out参数
delimiter //
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 //
delimiter ;
# 这里的onumber定义为in，订单号将被传递给存储过程。
# ototal定义为out，实现从存储过程返回给调用者。
# 调用上述存储过程ordertotal
call ordertotal(20005, @ototal);
# 显示统计结果
select @ototal;
# 为了得到另外一个订单的合计显示，需要再次调用
call ordertotal(20008, @ototal);
建立智能存储过程：
# 建立更加复杂的存储过程
# procedure name: ordertotal
# parameters: onnumber = order number
# taxable = 0 if not taxable, 1 if taxable
# ototal = order total vatiable
delimiter //
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)comment 'obtain order total ,optionally adding tax'
begin
# declear vatiable for total
declear total decimal(8,2);
# declear tax percentage
declear taxrate int default 6;
# get the order total
select sum(item_price*quantity)
from orderitems
where order_num = onumber
into total
# Is this taxtable?
if taxable then
# yes, so add taxable to the total
select total+(total/100*taxrate) into total;
end if;
# and finally, save to out variable
select total
into ototal;
end //
delimiter;
# 这里开头做了注释，说明了参数，存储过程的名字
# taxable是一个布尔变量，表明是否需要缴税
# 在存储过程体中，使用declear声明了两个局部变量，declear 变量名 数据类型；
# comment是MySQL的说明语句，不是必须的。这里写出来了，它可以通过show procedure status进行显示
# 调用上述存储过程
call ordertotal(20005, 1, @ototal);
# 结果显示
select @ototal;
这里的if语句，if语句还支持elseif和else字句。
检查存储过程：使用show create procedure语句
# 示例,显示用来创建一个存储过程的create语句
show create procedure ordertotal;
# 为了获得何时，创建者等详细信息
show procedure status;
# 限制过程状态结果的输出内容
show procedure status like 'ordertotal';
22.使用游标
游标：cursor是一个存储在MySQL服务器上的数据库查询，即检索出来的结果集。存储了游标之后，应用程序可以根据需要滚动或者浏览其中的数据。
有时候需要在检索出来的行中前进或者后退一行或者多行。
游标主要用于交互式应用。MySQL游标只能用于存储过程(和函数)。
步骤：
- 使用游标之前，先声明或定义。
- 声明游标之后，必须打开游标以供使用。
- 对填有数据的游标，根据需要取出各行。
- 结束游标使用时，必须关闭游标。
创建游标：使用declear语句进行创建
# 示例，定义名为ordernumbers的游标，使用了可以检索所有订单的select语句
delimiter //
create procedure processorders()
begin
declear ordernumbers cursor
for
select order_num from orders;
end //
delimiter;
# declear语句用来定义和命名游标，存储过程完成之后，游标就消失。因为MySQL游标局限于存储过程。
# 定义游标之后，打开游标;在处理open语句时，执行查询，存储检索出来的数据供浏览或滚动
open ordernumbers;
# 游标处理完成之后，使用close关闭游标
close ordernumbers;
close释放游标使用的所有内部内存和资源，因此每个游标在不再需要时都应该关闭。游标关闭之后，需要重新打开，但是无需再次声明定义。
隐含关闭：如果没有显式关闭游标，则MySQL会在到达end语句时自动将其关闭。
# 上述例子的修改版本
delimiter //
create procdure processorders()
begin
# declear the cursor
declear ordernumbers cursor
for
select order_num from orders;
# open the cursor
open ordernumbers;
# close the cursor
close ordernumbers;
end //
delimiter;
使用游标数据：在一个游标打开之后，可以使用fetch语句分别访问它的每一行。
fetch：指定检索什么数据，检索出来的数据存储在什么地方，它可以向前移动游标中的内部行指针，使得下一条fetch语句检索下一行。
# 从游标中检索单个行,fetch用来检索当前行的order_num列，写入到名为o的局部变量中。
delimiter //
create procedure processorders()
begin
# declear the local varialbes
declear o int;
# declear the cursor
declear ordernumbers cursor
for
select order_num from orders;
# open the cursor
open ordernumbers;
# get the order number
# 读取的数据写入局部变量o
fetch ordernumbers into o;
# close the cursor
close ordernumbers;
end //
delimiter;
循环检索数据，从第一行到最后一行。
# 循环检索数据，直至结束标志02000
delimiter //
create procedure processorders()
begin
# declear the local variables
declear done boolean default 0;
declear o int;
# declear the cursor
declear ordernumbers cursor
for
select order_num from orders;
# declear continue handler
declear continue handler for sqlstate '02000' set done = 1;
# open the cursor
open ordernumbers;
# loop through all rows
repeat
# get order number
fetch ordernumbers into o;
# end of loop
until done end repeat;
# close the cursor
close ordernumbers;
end //
delimiter;
# 这里的repeat在repeat内部，反复执行直到done为真停止
# declear continue handler for sqlstate '02000' set done=1;
# 上述语句定义了continue handler，当且仅当sqlstate为02000时，set sone=1
sqlstate为02000的时候，表示一个未找到条件。
declear语句定义的局部变量必须定义在任意游标或句柄之前，而句柄handler必须定义在游标之后。
# 定义游标，使用游标，并将数据存储到新的数据表中
delimiter //
create peocedure processorders()
begin
# declear local variables
declear done boolean default 0;
declear o int;
declear t decimal(8, 2);
# declear the cursor
declear ordernumbers cursor
for
select order_num from orders;
# declear the continue handler
declear continue handler for sqlstate '02000' set done=1;
# create a table to store the results
create table if not exists ordertotals(
order_num int ,
total decimal(8, 2)
);
# open the cursor
open ordernumbers;
# loop through all rows
repeat
# get order number
fetch ordernumbers into o;
# get the total for this order
# 调用之前定义的存储过程ordertotal
call ordertotal(o, 1, t);
# insert order and total into ordertotals
insert into ordertotals(order_num, total)
values(o, t);
# end of loop
until done end repeat
# close the cursor
close ordernumbers;
end //
delimiter;
# 查看数据
select *
from ordertotals;
23.使用触发器
触发器是MySQL为响应delete,update,insert语句而自动执行的一条MySQL语句。
创建触发器：最好是在数据库范围内使用唯一的触发器名字。
唯一的触发器名字；触发器关联的表；触发器应该响应的活动(delete，insert，update)；触发器执行的时机，处理之前或者之后。
# 示例，创建触发器
create trigger aihubei after insert on products
for each row select 'product added';
# 此触发器aihubei将在insert语句执行成功后执行，且针对每个插入行执行
# 文本product added将对每个插入的行显示一次
只有表才支持触发器，视图不支持，临时表也不支持。触发器按照每个表，每个事件每次的定义，每个表每个事件每次只允许一个触发器。因此每个表最多支持6个触发器。(每一条insert，update，delete)前后。单一触发器不能与多个事件或者多个表相关联。
若before触发器失败，则MySQL不执行请求的操作；若before触发器或语句本身失败，MySQL将不执行alter触发器(如果有)。
删除触发器：直接使用drop trigger
# 示例，删除触发器
drop trigger aihubei;
触发器不能更新或者覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。
insert触发器：在insert语句执行前后执行。insert触发器代码内，可以引用一个名为new的虚拟表，访问被插入的行；在before insert触发器中，new中的值也可以被更新；
# 示例，after insert触发器
create trigger neworder after insert on orders
for each row
select new.order_num;
# 在插入一个新的订单到orders表中后，触发器从new.order_num取得插入的order_num值并返回
delete触发器：delete触发器在delete语句执行之前或者之后执行；delete触发器代码内部可以引用一个名为old的虚拟表，访问被删除的行。且old中的值全部只读，不能更新。
# 示例，before delete触发器
delimiter //
create trigger deleteorder before delete on orders
for each row
begin
insert into archive_orders(order_num, ordera_date,cust_id)
values(old.order_num, old.order_date, old.cust_id);
end //
delimiter;
# 在任意订单被删除前都执行次触发器，它将要被删除的订单插入到表archive_orders。
# 表archive_orders需要使用与orders相同的列进行创建
多语句触发体：使用begin和end的好处是，触发器可以容纳多条sql语句。
update触发器：在update触发器代码中，可以引用一个名为old的虚拟表访问以前的值，引用一个名为new的表访问新更新的值；在before update触发器中，new中的值可能被更新，而old中的值全部只读，不能更新。
# 示例，创建触发器，保证州名总是大写
create trigger updatevendor before update on vendors
for each row set new.vend_state = upper(new.vend_state);
触发器将自动执行，一个重要意义是创建审计跟踪，把更改记录到另一个表。
MySQL触发器中不支持call语句，即触发器不能调用存储过程。所需的存储过程代码需要复制到触发器内部。
24.事务处理
事务：事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位。其执行的结果必须使得数据库从一种状态到另一种状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
myisam引擎不支持事务处理，innodb提供了对事务的支持。
事务的四大特性：
A-原子性：事务是最小的执行单位，不允许分割。原子性确保动作要么全部完成，要么全部不起作用。
C-一致性：执行事务前后，数据保持一致，多个事务对同一数据读取结果相同。
I-隔离性：并发访问数据库的时候，个并发事务之间数据库独立，一个用户的事务不干扰其它事务。
D-持久性：事务被提交之后，对数据库中的数据的改变是持久的，即便数据库发生故障也不会影响。
相关术语：
回退：rollback，代表撤销指定sql语句的过程
提交：commit，代表将尚未存储的SQL语句结果写入数据库
保留点：savepoint，代表事务处理中设置的临时占位符，可以对其发布回退
控制事务处理：
# 示例，标识事务的开始
start transaction;
使用rollback：
# 示例，使用rollback进行回退
select * from orderitems;
start transaction;
delete from orderitems;
select * from orderitems;
rollback;
select * from orderitems;
# 首先执行查询操作，然后开始一个事务处理，即start transaction
# 使用delete删除表orderitems中的所有行，另一条select语句证实删除后，表为空
# 使用rollback回滚，回滚的是start transaction之后的所有语句
# 这样一来，最后一条select语句显示该表不为空
事务处理用来管理insert，update，delete语句，，无法回退select语句，无法回退create或者drop操作。
使用commit：一般的MySQL语句都是直接针对数据库表执行和编写的，即隐含提交。
在事务处理块中，提交不会隐含地进行，为了进行明确提交，需要使用commit语句。
# 示例，使用commit进行显式提交
start transaction
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;
# 若第一条delete语句生效，但第二条失败，则delete不会被提交，即自动撤销
隐含事务关闭：当commit或者rollback语句执行后，事务会自动关闭。
使用保留点：简单的rollback或者commit语句可以实现对整个事务的回滚或提交。复杂的事务可能需要部分提交或者回退。可以使用savepoin语句
# 示例
savepoint delete1;
rollback to delete1;
# 示例
start transaction
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
savepoint delete1;
delete from orderitems where order_num = 20020;
delete from orders where order_num = 20020;
savepoint delete2;
# 使用rollback进行回滚
rollback to delete1;
commit;
# 则第二条个删除20020被撤销，
释放保留点：可以使用release savepoin明确释放保留点。
更改默认的提交行为：默认的MySQL行为是自动进行提交，且立即生效。为实现不自动提交，操作如下：尤其是在导入数据的时候，
# 示例，更改默认提交行为,针对每个连接，而非服务器
set autocommit = 0;
25.其他操作和本文数据表
# 查看所有的字符集完整列表
show character set;
# 显示所有可用的字符集以及每个字符集的描述和默认校对
# 查看所以支持的完整列表
show collation;
# 显示所以的可用校对，以及适用的字符集
# 显示所有数据类型
? data types;
# 给表指定字符集和校对
create table mytable
(
column1 int,
column2 varchar(20),
column2 text
)default character set hebrew
collate hebrew_general_ci;
# 创建用户账号
create user xiao identified by 'xiao@w012';
# identified by 指定的口令为纯文本，为了作为散列值指定口令
create user xiao identified by password 'xiao@012';
# 更改指定用户口令
set password for xiao = password('xiao12@013');
# 更改当前用户口令
set password = password('admin12@X')
# 对用户进行重命名
rename user xiao to xiaoyao;
# 删除用户账号
drop user xiaoyao;
# 查看用户权限，usage表示没有权限，
show grants for xiao1;
# 给指定用户授权
grant select on table_name或者database_name.* to xiao1;
# grant的反操作为revoke，用来撤销特定的权限
revoke select on database_name.* from xiao1;
# 通过命令查看数据库版本，
status;
# 或者
show variables like '%version%';
# 或者，命令行登录界面直接查看
# 若遭遇显著的数据库性能不良，可以使用
show processlist
# 显示所以的活动进程，以及他们的线程ID和执行时间
# 也可以使用kill中介某个特定的进程
本文数据表：
vendors表：
| 列 | 说明 |
|---|---|
| vend_id | 唯一的供应商ID，主键，自增长 |
| vend_name | 供应商名 |
| vend_address | 供应商的地址 |
| vend_city | 供应商的城市 |
| vend_state | 供应商的州 |
| vend_zip | 供应商的邮编 |
| vend_country | 供应商的国家 |
products表：
| 列 | 说明 |
|---|---|
| prod_id | 唯一的产品ID，主键 |
| vend_id | 供应商ID，外键，references vendors(vend_id) 外键 |
| prod_name | 产品名 |
| prod_price | 产品价格 |
| prod_desc | 产品描述 |
customers表：
| 列 | 说明 |
|---|---|
| cust_id | 唯一的客户ID，主键，自增长 |
| cust_name | 顾客名 |
| cust_address | 顾客地址 |
| cust_city | 顾客城市 |
| cust_state | 顾客州 |
| cust_zip | 顾客所在地邮编 |
| cust_country | 顾客的国家 |
| cust_contact | 顾客的联系名 |
| cust_email | 顾客的email地址 |
orders表：
| 列 | 说明 |
|---|---|
| order_num | 唯一订单号，主键，自增长 |
| order_date | 订单创建日期 |
| cust_id | 订单顾客ID，references customers(cust_id)，外键 |
orderitems表：
| 列 | 说明 |
|---|---|
| order_num | 订单号，references orders(order_num)，外键 |
| order_item | 订单物品号，与order_num同为主键 |
| prod_id | 产品ID，reverence products(prod_id)，外键 |
| quantity | 物品数量 |
| item_price | 物品价格 |
productnotes表：engine为myisan
| 列 | 说明 |
|---|---|
| note_id | 唯一注释ID，主键 |
| prod_id | 产品ID，references products(prod_id) |
| note_date | 增加注释的日期 |
| note_text | 注释文本，fulltext，自动索引 |
创建本文数据表：
# sql语句
########################
# 创建 customers 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;
#########################
# 创建 orderitems table
#########################
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# 创建 orders table
#####################
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
#######################
# 创建 products table
#######################
CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
######################
# 创建 vendors table
######################
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
###########################
# 创建 productnotes table
###########################
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;
#####################
# 定义数据表的外键 foreign keys
#####################
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);
为本文数据表插入数据：
# sql语句
##########################
# customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
########################
# vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
#########################
# products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
#######################
# orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);
###########################
# orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);
#############################
# productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);
26.参考文献
[1] Forta B . MySQL Crash Course[M]. Pearson Schweiz Ag, 2006.
扫一扫
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
