第1章 了解SQL

数据库（database）保存有组织的数据的容器
表（table）某种特定类型数据的结构化清单
模式（schema）关于数据库和表的布局及特性的信息
列（column）表中的一个字段，多有表都是有一个或多个列组成
数据类型（datatype）所容许的数据类型。每个表列都用相应的数据类型，它限制（或容许）该列中存储的数据
行（row）表中的一个记录
主键（primary key）一列（或一组列），其值能够唯一区分表中的每个行
表中的任何列都可以作为主键，只要满足以下条件
每个行都必须具有一个主键值（不允许NULL值）且任意两行都不具有相同的主键值
SQL（Structured Query Language）结构化查询语言。

第2章 MySQL简介

MySQL是一种DBMS（数据库管理系统），即它是一种数据库软件

第3章 使用MySQL

选择数据库：use 数据库名;
查看数据库列表：show databases;
查看数据库内的表的列表：show tables;
查看表中的每个字段，包括字段名、数据类型、是否允许NULL、键信息、默认值等
show columns from 表名;
自动增量是表列需要的胃一直值。例如，订单编号、雇员ID或顾客ID等。
show status；用于显示管饭的服务器状态信息
show create database; 和 show create table;分别用来显示穿件特定数据库或表的MySQL语句
show grants;用来显示授予用户的安全权限
show errors; 和 show warnings; ，用来显示服务器错误或警告信息

第4章 检索数据;

1、检索单个列
select 列名 from 表名;

2、检索多个列
select 列名, 列名, 列名 from 表名;

3、检索所有列
select * from 表名;

4、检索不同的行,即找出一列中的不同值
select distinct 列名 from 表名;

第5章 排序检索数据

1、排序数据
select 列名 from 表名;
通过order by子句，默认升序排序
select 列名 from 表名 order by 列名;

2、按多个列排序
select 列名, 列名, 列名 from 表名 order by 列名, 列名;

3、指定排序方向，按照降序排序
select 列名, 列名, 列名 from 表名 order by 列名 desc;

4、找出一列中的最高或最低值
select 列名 from 表名 order by 列名 desc limit 1;

第6章 过滤数据

1、使用where子句。只检索所需数据需要指定搜索条件，利用where子句，如下所示
select 列名, 列名 from 表名 where 列名 = xxx;（where跟某一条件）
order by要在where子句之后

2、where子句操作符
（1）、检测单个值，同上

（2）、或者等于10美元的所有产品
select prod_name, prod_price from products where prod_price <= 10;

（3）、不匹配检查
找出不是由供应商1003制造的所有产品
select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;

（4）、范围值检查
检索价格在5美元和10美元之间所有的产品
select prod_name， prod_price from products where prod_price between 5 and 10;

（5）、空值检查
检查顾客的电子邮件没有地址
select cust_id from customers where cust_email is null;

第7章 数据过滤

1、组合where子句
（1）and操作符
检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格
select prod_id, prod_price, prod_name form products where vend_id = 1003 and prod_price <= 10;
用and语句可以添加多个过滤条件，每添加一个条件就使用一个and

（2）or操作符
检索由1002和1003中任一个指定供应商制造的所有产品的产品和价格
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;

（3）计算次序
where可包含任意数目的and和or操作符，允许两者结合以进行复杂和高级的过滤，但是谁的优先级比较高
需要列出价格为10美元（含）以上且由1002和1003制造的所有产品
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10
这样表达室友问题的，因为and的优先级比or高，所以正确的表达如下
select prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10

2、IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，其实等同于OR
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;

3、NOT操作符
列出除1002和1003之外的所有供应商制造的产品
select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;
在复杂的子句中，not非常有用，在与IN操作符联合使用时，not找出不匹配信息非常简单

第8章 用通配符进行过滤

1、LIKE操作符
通配符是用来匹配值的一部分的特殊字符
搜索模式由字面值、通配符或两者组合构成的搜索条件
（1）百分号（%）通配符
在搜索中，%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下select语句

select prod_id, prod_name from products where prod_name like 'jet%';
%是接受jet之后的任意字符，不管它有多少字符

通配符可以在搜索模式中任意位置使用，可以使用多个通配符
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';
如果要搜索的数据，最后有一个尾空格，会影响通配符的使用，解决办法就是在末尾加一个%
%通配符可以匹配任何东西，但是不匹配NULL

（2）下划线（）通配符
与%用途一样，但是下划线匹配单个字符而不是多个字符
显示“ ton anvil”中只存在一个字符的字段
select prod_id, prod_name from products where prod_name like ' ton anvil';

2、使用通配符的技巧
（1）不要过度使用通配符，能不用就不用
（2）把通配符置于搜索模式开始处，搜索起来最慢
（3）要注意通配符位置，放错地方，可能不会返回预期效果

第9章 用正则表达式进行搜索

1、使用MySQL正则表达式
（1）基本字符匹配
检索列prod_name包含文本 “王” 的所有行
select prod_name from products where prod_name regexp '王' order by prod_name;

关键字regexp后所跟的东西作为正则表达式，与文字正文1000匹配的一个正则表达式处理

使用正则表达式.000 . 是正则表达式语言中搞一个特殊的字符。表示匹配任意一个字符，因此1000和2000都返回

select prod_name from products where prod_name regexp '.000' order by prod_name;

LIKE和REGEXP之间有一个重要差别
select prod_name from products where prod_name like '1000' order by prod_name;

此处不返回数据，like匹配整个列。如果匹配的文本仅是在列中出现（就是不完全相同），like不会找到它
select prod_name from products where prod_name regexp '1000' order by prod_name;

而regexp实在列值内进行匹配，如果被匹配的文本在列值中出现，regexp会找到它，相应行会被返回。

用regexp也可以用来匹配整个列（从而和like相同），使用^和$定位符即可

在MySQL中的正则表达式匹配不区分大小写，为区分大小写，可使用binary关键字，如下
select prod_name from products where prod_name regexp binary 'JetPack .000';

（2）进行OR匹配
为搜索两个串之一，使用|，如下所示
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;
两个以上or条件，例如‘1000|2000|3000’，将匹配1000或2000或3000

（3）匹配几个字符之一
如何匹配特定字符，用 [ 和 ] 括起来的字符完成，如下所示
select prod_name from products where prod_name regexp ‘[123] Ton’ order by prod_name;
使用正则表达式[123]，定义了一组字符，意思是匹配1或2或3

（4）匹配范围
集合可以用定义要匹配的一个或多个字符，如[0123456789]会匹配数字0到9，为了简化，可以写成[0-9]
select prod_name from products where prod_name regexp ‘[1-5] Ton’ order by prod_name;

（5）匹配特殊字符
为了匹配特殊字符，必须用 \ 为前导，\- 表示 -，\. 表示.
select prod_name from vendors where vend_name regexp ‘\.’ order by vend_name;

（6）匹配字符类

image

（7）匹配多个实例

image

select prod_name from products where prod_name regexp ‘\([0-9) sticks?\)’ order by prod_name;

正则表达式\([0-9) sticks?\)，[0-9]匹配任意字符，sticks？匹配stick和sticks（s后的？使s可选）
select prod_name from products where prod_name regexp ‘[[:digit:]]{4}’ order by prod_name;
如前所述， [:digit:]匹配任意数字， 因而它为数字的 个集合。 {4}确切地要求它前面的字符（任意数字） 出现4次， 所以[[:digit:]]

{4}匹配连在 起的任意4位数字。

（8）定位符

image

select prod_name from products where prod_name regexp ‘A[0-9\.]’ order by prod_name;

＾匹配串的开始。因此，"[0-9\.]只在．或任意数字为串中第一个字符时才匹配它们。没有＾，则还要多检索出4个别的行（那些中间有数字的行）。

第10章 创建计算字段

1、计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子。

如果想在一个字段中既显示公司名， 又显示公司的地址， 但这两个信息一般包含在不同的表列中。

城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。

列数据是大小写混合的， 但报表程序需要把所有数据按大写表示出来。

物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）为打印发票，需要物品的总价格。

需要根据表数据进行总数、平均数计算或其他计算。

2、拼接字段
语法格式：CONCAT(char c1, char c2, ..., char cn) ，其中char代表字符串，定长与不定长均可以
拼接（CONCAT）将值联结到一起构成单个值
select conact（vend_name, '(', vend_country, ')'） from vendors order by vend_name;

Trim函数可以删去冗余的空格，RTrim()删去右边所有空格，LTrim()删去左边冗余空格
select conact（RTrim(vend_name), '(', vend_country, ')'） from vendors order by vend_name;

3、使用别名
别名是一个字段或替换名，别名用as关键字赋予
select conact（RTrim(vend_name), '(', vend_country, ')'）as vend_title from vendors order by vend_name;

4、执行算术计算
select prod_id, quantity, item_price, quantity*item_price as expanded price from orderitems where order_num = 20005;

第11章 使用数据处理函数

1、函数
函数一般是在数据上执行的，它给数据的转换和处理提供了方便

2、使用函数
（1）文本处理函数
Upper()将文本转换为大写，因此本例子中每个供应商都应出现两次，一次未大写前，一次是大写很后
select vend_name, Upper(vend_name) as vend_name_upcase from vendors order by vend_name;

还有其它常用的文本处理函数

Soundex()函数将任何文本转换为描述其语音表示的字母数字模式的算法，如下

select cust_name, cust_contact from customers where Soudex(cust_contact) = Soudex( 'Y.Lie');

2）日期和时间处理函数

日期必须为yyyy-mm-dd格式
检索订单记录的order_date是2005-09-01的记录
select cust_id, order_num from orders where order_date = '2005-09-01';

如果存储的数据类型为datetime，其中也包含了时间值，存储的order_date值为2005-09-01 11:30:05，上述检索就会失败

为此，必须使用Date()函数

select cust_id, order_num from orders where Date(order_date) = '2005-09-01';

如果要的是日期，请使用Date()
检索出2005-09-01和2005-09-30的日期范围
select cust_id, order_num from orders where Date(order_date) between '2005-09-01' and '2005-09-30';

还有另一种办法（无需操心每个月有多少天或不需要操心闰年2月的方法）

检索出年份为2005和月份为9的日期
select cust_id, order_num from orders where Year(order_date) = 2005 and Month(order_date) = 9;

（3）数值处理函数

第12章 汇总数据

1、聚集函数

1）AVG()函数

返回products表中所有产品的平均价格
select AVG(prod_price) as avg_price from products;

如果用来返回特定的行或列的平均值，如下

select AVG(prod_proce) as avg_price from products where vend_id = 1003;

AVG()函数或略列值为NULL的行

（2）COUNT()函数

返回customers表中客户的总数

select COUNT(*) as num_cust from customers;

image

（3）MAX() 函数

返回products表中最贵的物品的价格

select MAX(prod_price) as max_price from products;

image

（4） MIN()函数

与上面相反，返回最便宜的价格

select MIN(prod_price) as max_price from products;

image

（5） SUM函数

检索所订购物品的总数（所有quantity值之和）

select SUM(quantity) as items_ordered from orderitems where order_num = 20005;

image

2、聚集不同值

利用DISTINCT聚集不同值，找出其中不同的值，再计算平均价格

select AVG(DISTINCT prod_price) as avg_price from products where vend_id = 1003;

image

不允许使用COUNT(DISTINCT)，否则会产生错误，类似，DISTINCT必须使用列名，不能用于计算或表达式

3、组合聚集函数

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;

image

第13章 分组数据

1、数据分组
分组允许吧数据分为多个逻辑组，以便能对每个组进行聚集计算

2、创建分组
select vend_id, COUNT(*) as num_prods from products group by vend_id;

group by子句可以包含任意数目的列
如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总
group by子句列出每个列都必须是检索列或有效表达式（不能是聚集函数）
如果有null，null作为一个分组返回
group by子句必须出现在where子句之后，order by子句之前

第14章 使用子查询

1、子查询
即嵌套在其他查询中的查询

2、利用子查询进行过滤
列出订购物品TNT2的所有客户；
检索包含物品的TNT2的所有订单的编号
检索具有前一步骤列出的订单编号的所有客户的ID
检索前一步骤返回的所有客户ID 的客户信息

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'));

image

3、作为计算字段使用子查询
假如需要显示customers表中的每个客户的订单总数。订单与相应的客户ID存储在orders表中
从customers表中检索客户列表
对于检索出的每个客户，统计其中orders表中的订单数目

select cust_name, cust_state, (select count(*) from orders where orders.id = customers.cust_id) as orders from customers order by cust_name;

第15章 联结表

1、联结
联结表就是能在数据检索查询的执行中联结表

（1）关系表
外键：外键为某个表中的一列，它包含了另一个表的主键值，定义了两个表之间的关系
products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又焦作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息

2、创建联结
select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

笛卡尔积：由没有联结条件的表关系返回为结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
如 select vend_name, prod_name, prod_price from vendors,products order by vend_name,prod_name;

（2）内部联结
返回和前面帘子完全相同的数据，用inner join指定
sele

（3）联结多个表
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;

image

第16章 创建高级联结

1、使用表别名，缩短SQL语句，允许在单挑select语句中多次使用相同的表
应当注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机

2、使用不同类型的联结
（1）自联结
查询要求首先找到生产ID为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' ;

image

（2）外部联结
用来包含没有关联行的那些行
select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

image

3、使用带聚集函数的联结
select customers.cust_name, custome.cust_id, count(orders.order_num) as num_ord
from customers
inner join orders on customers.cust_id = orders.cust_id;
group by customers.cust_id;

image

4、使用联结和联结条件

第17章 组合查询

1、组合查询
允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回，通常称作并或符合查询

2、创建组合查询
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);

image

UNION会自动包含或取消重复的行，使用UNION ALL就是不取消重复的行
对组合查询结果排序，用UNION组合查询时，只能使用一条order by子句

第18章 全文本搜索

1、使用全文本搜索
Match()和Against()一起使用以实际执行搜索
（1）启用全文本搜索支持
一般在创建表时启用全文本搜索

（2）进行全文本搜索
select note_text from productnotes where Match(note_text) Against('rabbit');

image

传递给Match()的值必须与FULLTEXT()定义中的相同，而且数量和次序正确，且搜索不区分大小写，除非BINARY

与LIKE相比，这种方式有良好程度的排序

select note_text Match(note_text) Against('rabbit') as rank from productnotes;

image image

每行都一个等级值，文本词靠前的行的等级比靠后的搞

（3）使用查询扩展

select note_text from productnotes where Match(note_text) Against('anvils' with query expansion);

image

按照等级从高到低依次排序

（4）布尔文本搜索

select note_text from productnotes where Match(note_text) Against('heavy' in boolenan mode);

image

为匹配包含heavy但不包含任意以rope开始词的行

select note_text from productnotes where Match(note_text) Against('heavy -rope*' in boolenan mode);

[图片上传中...(image-df073d-16011

第19章 插入数据

1、插入完整的行
插入一个新客户到customers表中
insert into customers values(null, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);
完整的insert语句，输入对应的列名】

insert into customers(cust_name, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);
如果列中允许null或者给出默认值，可以省略列

2、插入多个行
insert into customers(cust_name, 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),
('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);

3、插入检索出的数据
insert的另一种形式，利用它将一条select语句的结果插入表中
从custnew中将所有数据导入customers中去
insert into customers(cust_contact, cust_email, cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_country)
select cust_id, cust_email, cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_country from custnew;

image.png

第20章 更新和删除数据

1、更新数据
更新表中特定行
更新表中所有行
客户10005现在有了电子邮件地址，需要更新他的记录
update customers set cust_emailn = 'elmer@fudd.com' where cust_id = 10005;

更新多个列
update customers set cust_emailn = 'elmer@fudd.com', cust_name = 'The Fudds'
where cust_id = 10005;
如果要删除某一列的值，可设置它成为null（表定义允许为null）

2、删除数据
从表中删除特定的行
从表中删除所有行
只删除客户10006
delete from customers where cust_id = 10006;
delete语句从表中删除行，甚至所有行，而不是表本身

更快的删除可以使用truncate table语句，实际是删除原来的表并重建创建一个表，而不是逐行删除表中数据

3、更新和删除的指导原则

image

第21章 创建和操纵表

1、创建表
表创建基础
create table customers
(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_adress char(50) not null,
cust_city char(50) not null,
cust_state char(5) not null,
cust_zip char(10) not null,
cust_country char(50) not null,
cust_contact char(50) not null,
cust_email char(255) not null,
primary key(cust_id)
)Enging = InnoDB;
使用null值的合理性
使用auto_increment自增长
InnoDB是事物引擎

2、更新表
更改表结构，添加一个列
alter table vendors add vend_phone char(20);
还可以用来定义外键

image

3、删除表
drop table customers2;

4、重命名表
rename table customers2 to customers;

第22章 使用视图

1、视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询

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'

举个例子，查询用来检索订购了某个特定产品的客户，任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的where子句。

（1）为什么使用视图

（2）视图的规则和限制

image

2、使用视图

image

（1）利用视图简化复杂的联结
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;

创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。
检索订购了产品TNT2的客户
select cust_name, cust_contact from productcustomers where prod_id = 'TNT2';

（2）用视图重新格式化检索出的数据
视图的另一种常见用途是重新格式化检索出的数据
select contact(RTrim(vend_name), '(', RTrim(vend_country), ')') as vend_title from vendors order by vend_name;
假如经常需要这个格式的结构，不必每次需要时执行联结，创建一个视图，每次需要时使用它即可。

create view vendorlocations as
select contact(RTrim(vend_name), '(', RTrim(vend_country), ')') as vend_title from vendors order by vend_name;

select * from vendorlocations;

image

（3）用视图过滤不想要的数据
过滤没有电子邮件地址的客户
create view customeremaillist as
select cust_id, cust_name, cust_email from customers where cust_email is not null;

select * from customeremaillist;

（4）使用视图与计算字段
select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;

image

将其转换为一个视图，如下进行：
create view orderitemsexpanded as
select order_num prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems;

select * from orderitemsexpanded where order_num = 20005;

image

（5）更新视图
视图是可以更新的，可以对它们使用insert、update和delete
但是不是所有视图都可以更新的，如果视图定义中有以下操作，则不能进行视图的更新

image image

第23章 使用存储过程

1、存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为批文件，虽然它们的作用不仅限于批处理

2、为什么要使用存储过程

image

3、使用存储过程
（1）执行存储过程
call productpricing(@pricelow, @pricehigh, @priceaverage);
执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

（2）创建存储过程
一个返回平均价格的存储过程
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
使用这个存储过程
call productpricing();

image

（3）删除存储过程
drop procedure productpricing;

（4）使用参数
create procedure productpricing(
out pl declimal(8,2),
out ph declimal(8,2),
out pa declimal(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;
in传递给存储过程、out从存储过程传出和inout对存储过程传入和传出

（5）检查存储过程
show create procedure ordertotal;