金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Mysql必知必会,mysql基础整理

Mysql必知必会,mysql基础整理

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-04 21:26

1.  背景及原因

关系型数据库的一个基本原则是将不同细分数据放在单独的表中存储。这样做的好处是:

1).避免重复数据的出现

2).方便数据更新

3).避免创建重复数据时出错

例子:

有供应商信息和产品信息两部分。如果将他们放在一个表中。弊端有:

1). 同一供应商的所有数据所在行的供应商信息相同,即产生重复数据。

2). 在供应商信息变更时,如果更换联系方式或地址,需要在所有包含此供应商信息的行更新。

3). 在录入同一供应商的多个产品时,有可能会供应商信息不同,导致调取数据时无法使用。

通过将供应商和产品信息分别放在不同的表中,可以避免以上弊端。

1). 一个供应商只需一条记录,省时省空间。

2). 变更供应商信息时,只需变更一次。方便快捷不易出错。

3). 录入信息时,不会出错。

DB中存在如下几张表:

orders:

order_num, order_date, cust_id

venders:

vend_id, vend_name, vend_address, vend_contact

products:

prod_id, prod_name, vend_id, prod_price

customers:

cust_id, cust_name, cust_address, cust_city, cust_state

orderitems:

order_num, order_item, prod_id, quantity, item_price

一、联结

(一)几个数据库相关的概念

1.表中的任何列都可以作为主键, 只要它满足以下条件:
任意两行都不具有相同的主键值;
每一行都必须具有一个主键值( 主键列不允许NULL值) ;
主键列中的值不允许修改或更新;
主键值不能重用( 如果某行从表中删除, 它的主键不能赋给以后的新行)
2.MySQL的要求:任意两行都不具有相同的主键值;
每个行都必须具有一个主键值(主键列不允许NULL值)。
3.tips:除MySQL强制实施的规则外,应该坚持的
几个普遍认可的最好习惯为:
 不更新主键列中的值;
 不重用主键列的值;
 不在主键列中使用可能会更改的值。(例如,如果使用一个
名字作为主键以标识某个供应商,当该供应商合并和更改其
名字时,必须更改这个主键。)

2. 表的联结

 由于以上原因,在调出多个表中信息时,就需要将多个表通过主键和外键联结。

 联结的基本方法:1. 指出需要联结的表;2. 指出通过哪个字段联结。

 例子:

需要供应商和所提供产品信息:

select vend_name, prod_name, prod_price #select fields
from vendors, products  #from tables
where vendors.vend_id=products.vend_id #how to join tables;

  联结是利用SQL的select能执行的最重要的操作。

1.数据库

3. 两种联结方法

除了上面的在where字句中创建联结,还可以使用关键字join ... on...

例如上面的语句还可写成:

select vend_name, prod_name, prod_price #select fields
from vendors inner join products  #from tables
on vendors.vend_id=products.vend_id #how to join tables;

此处注意,在指定联结字段时,需要使用完全限定列名,既table.column的格式。

笛卡儿积:当没有制定联结字段时会出现笛卡儿积。既,被联结的两个表中任意一行都和另一个表中所有行联结。

  1、关系表:假如有一个包含产品目录的数据库表,其中每个类别的物品占一行。对于每种物品要求存储的信息包括产品描述和价格,以及生产该产品的供应商信息。

数据库: 保存有组织数据的容器。

第二章

1.MySQL选项和参数 如果仅输入mysql, 可能会出现一个错误
消息。因为可能需要安全证书,或者是因为MySQL没有运行
在本地或默认端口上。 mysql接受你可以(和可能需要)使用
的一组命令行参数。例如,为了指定用户登录名ben,应该使
用mysql -u ben。为了给出用户名、主机名、端口和口令,
应该使用mysql -u ben -p -h myserver -P 9999。
完整的命令行选项和参数列表可用mysql --help获得。
2.命令输入在mysql>之后;
 命令用;或g结束,换句话说,仅按Enter不执行命令;
 输入help或h获得帮助,也可以输入更多的文本获得特定命令的
帮助(如,输入help select获得使用SELECT语句的帮助);
 输入quit或exit退出命令行实用程序。

4. 联结多个表

在一条SQL语句中可以联结任意多张表。但是要注意:联结表非常消耗数据库系统资源,所以一定要注意控制联结的使用。

一个例子:

select prod_name, vend_name, prod_price, quantity
from products, vendors, orderitems
where products.prod_id = orderitems.prod_id
and vendors.vend_id=products.vend_id
and orderitems = 20005;

    现在假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如供应商名、地址、联系方式等)。

数据的所有存储、检索、管理和处理实际上是有数据库软件DBMS完成的。

第三章

1.必须先使用USE打开数据库,才能读取其中的数据。
2.SHOW COLUMNS 要 求 给 出 一 个 表 名 ( 这 个 例 子 中 的 FROM
customers),它对每个字段返回一行,行中包含字段名、数据
类型、是否允许NULL、键信息、默认值以及其他信息(如字段cust_id
的auto_increment)。
什么是自动增量? 某些表列需要唯一值。例如,订单编号、
雇员ID或(如上面例子中所示的)顾客ID。在每个行添加到表
中时, MySQL可以自动地为每个行分配下一个可用编号,不
用在添加一行时手动分配唯一值(这样做必须记住最后一次使
用的值)。这个功能就是所谓的自动增量。
3.SHOW STATUS,用于显示广泛的服务器状态信息;
 SHOW CREATE DATABASE和SHOW CREATE TABLE,分别用来显示创
建特定数据库或表的MySQL语句;
 SHOW GRANTS,用来显示授予用户(所有用户或特定用户)的安
全权限;
 SHOW ERRORS和SHOW WARNINGS, 用来显示服务器错误或警告消息。

5. 联结的类型

1). 内联结和外联结

内联结:查找出的行是通过两个表的相等测试的数据。inner join on

外联结:在联结是指定一个表,并反回其中所有行,无论是否通过相等测试。外联结包括左联结右联结。left/right outer join on

一个例子:检索系统中所有下了订单的客户id和订单数量

内联结:

select customers.cust_id, orders.order_num
from customers inner join orders
  on customers.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;

left outer join左边的customers表中所有的行都会被检索到。不论客户是否下单。

左联结和右联结的区别:

左联结关键字的左边的表会被检索出所有行,右联结关键字右边的表会被检索出所有行。所以左联结和右联结可以轻易转换,在实现功能上没有区别。

2).自联结

在一些特定情况下,需要让一张表自己和自己做联结,就需要用到自联结。例如需要查出生产了产品ID为DTNTR的供应商的所有产品。

使用子查询:

select prod_id, prod_name
from products
where vend_id in (select vend_id
                          from products
                          where prod_id="DTNTR");

使用自联结:

select p1.prod_id, p2.prod_name
from products as p1 inner join products as p2
on p1.vend_id=p2.vend_id
and p2.prod_id="DTNTR";

3).自然联结

所有查找出的列都是唯一的,不会有一个行被输出两次。自然联结需要通过人工手动实现,没有公式或关键字能制定自然联结。

    由于相同数据出现多次违背了范式设计原则,因此在这个例子中,可以建立两个表,一个存储供应商信息vendors,另一个存储产品信息products。产品信息表中除了存储供应商ID外不存储其他供应商信息。因此vendors表的主键又叫做products的外键。他将vendors与products关联。

我们通过数据库软件DBMS来创建和操纵容器。

第四章 检索数据

1.要想从一个表中检索多个列,使用相同的SELECT语句。唯一的不同
是必须在SELECT关键字后给出多个列名,列名之间必须以逗号分隔。
2.mysql命令行用;
3.在选择多个列时,一定要在列名之间加上逗号,但
最后一个列名后不加。如果在最后一个列名后加了逗号,将出
现错误。
4.如果给定一个通配符( *),则返回表中所有列
5.SELECT DISTINCT vend_id告诉MySQL只返回不同(唯一)的
vend_id行,因此只返回4行.不能部分使用DISTINCT DISTINCT关键字应用于所有列而
不仅是前置它的列。
6.此语句使用SELECT语句检索单个列。 LIMIT 5指示MySQL返回
不多于5行.LIMIT 5, 5指示MySQL返回从行5开始的5行。
7.SELECT products.prod_name FROM products;

6. 有聚合函数的联结

联结可以和聚合函数一起使用。例如,需要检索出所有客户的订单数:

内连接:

select customers.cust_id, customers.cust_name,
count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id inner join orders.cust_id
group by customers.cust_name;

检索出所有已下单客户的订单数。

外联结:

select customers.cust_id, customers.cust_name,
count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id inner join orders.cust_id
group by customers.cust_name;

检索出所有客户的订单数,包括没有订单数为0的客户。

 

2.表

第五章,排序

1.SELECT prod_name
金沙棋牌app手机下载,FROM products
ORDER BY prod_name;
2.SELECT prod_id,prod_price,prod_name FROM products
ORDER BY prod_price,prod_name DESC;
DESC表示降序,ASC是升序
3.如何区分大小写和排序顺序是数据库管理员设定规则
4.在给出ORDER BY子句时,应该保证它
位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY
之后。使用子句的次序不对将产生错误消息。

  2、外键(foreign key):外键为某个表中的一列,它包含另一个表中的主键值,定义了两个表之间的关系。

某种特定类型数据的结构化清单。表名是唯一的,用来标识自己。

第六章 过滤数据

1.SELECT prod_name,prod_price
FROM products
WHERE prod_price=2.50
2.在同时使用ORDER BY和WHERE子句时,应
该让ORDER BY位于WHERE之后, 否则将会产生错误
3.SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
4.SELECT prod_name
FROM products
WHERE prod_price IS NULL;

  

表具有一些特性,定义了数据在表中如何的存储,存储什么样的数据,数据如何分解,各部分信息如何命名等。描述这组信息叫做模式(schema),它是关于数据库和表的布局及特性信息。

第七章 数据过滤

1.AND在计算次序中优先级更高。
2.SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
3.MySQL 支 持 使 用 NOT 对 IN 、 BETWEEN 和
EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件
取反有很大的差别。

二、创建联结

3.列和数据类型

第八章 用通配符进行过滤

1.通配符,搜索模式,在搜索子句中用通配符必须使用LIKE操作符
2.SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';
%表示任何字符出现的任意次数。
3.tips:尾空格可能会干扰通配符匹配。例如,在保存词
anvil 时 , 如 果 它 后 面 有 一 个 或 多 个 空 格 , 则 子 句 WHERE
prod_name LIKE '%anvil'将不会匹配它们,因为在最后的l
后有多余的字符。解决这个问题的一个简单的办法是在搜索模
式最后附加一个%。
虽然似乎%通配符可以匹配任何东西,但有一个例
外,即NULL。即使是WHERE prod_name LIKE '%'也不能匹配
用值NULL作为产品名的行
4.与%能匹配0个字符不一样, _总是匹配一个字符,不能多也不能少。
5.tips:不要过度使用通配符。如果其他操作符能达到相同的目的,应该
使用其他操作符。
 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用
在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起
来是最慢的。
 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数

  联结是一种机制,用来在一条select语句中关联表,因此称为联结。使用特殊的语法,可以联结多个表返回一组输出。

列:表中的一个字段

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

1.SELECT prod_name
FROM products
WHERE prod_name REGEXP'1000'
ORDER BY prod_name;
2.LIKE是匹配整个列,REGEXP匹配列值,为在搜索子句中使用通配符,必须使用LIKE操作符。 LIKE指示MySQL,
后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
3.MySQL正则表达式匹配后不区分大小写。区分大小写可以用BINARY关键字,如WHERE prod_name REGEXP BINARY 'JetPack.000'
4.匹配特定的字符用[].
5.否定字符集合用^.
6.正则表达式匹配列值,只要列值中含有就可以,LIKE则是匹配整个列形式。
7..是匹配任意字符,匹配特殊字符用为前导。、|[].
8.f换页,n换行,r回车,t制表,v纵向制表。
9.找出你自己经常使用的数字,所有字母字符或所有数字字母字符的匹配。预定义的字符类。
[:alnum:]任意字母和数字
[:alpha:]任意字符
[:blank:]空格和制表[t]
[:cntrl:]ASCII控制字符 0到31和127
[:digit:]任意数字
[:graph:]与[:print:]相同,但不包括空格
[:lower:]任意小写
[:print:]任意可打印字符
[:punct:]既不是alnum也不是cntrl的字符
[:space:]包括空格在内的空白字符,同[fnrtv]
[:upper:]
[:xdigit:]任意十六进制数字
10.匹配多个实例
*0个或多个匹配
+1个或多个
?0个或1个
{n}指定数目的匹配
{n,}不少于指定数目的匹配
{n,m}匹配数目的范围
11.^文本的开始 $文本的结尾 [[:<:]]词的开始 [[:>:]]词的结尾
SELECT 'hello' REGEXP '[0-9]'返回0

  select vend_name,prod_name,prod_price from vendors,products where vendos.vend_id=products.vend_id order by vend_name,prod_name;

数据类型:每个列都有相应的数据类型

第十章 创建计算字段

1.存储在表中的数据都不是应用程序所需要的。
我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是
检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
这就是计算字段发挥作用的所在了。与前面各章介绍过的列不同,
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句
内创建的。数据库知道SELECT语句中哪些列是实际的
表列,哪些列是计算字段。从客户机(如应用程序)的角度来看,计算
字段的数据是以与其他列的数据相同的方式返回的。

2.tips:多数DBMS使用+或||来实现拼接,
MySQL则使用Concat()函数来实现。当把SQL语句转换成
MySQL语句时一定要把这个区别铭记在心
3.SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
4.SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
FROM vendors
ORDER BY vend_name;
Rtrim去掉右边所有空格,Ltrim去掉左边所有空格
5.使用别名
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')AS
vent_title
FROM vendors
ORDER BY vend_name;
6.计算字段
SELECT prod_id,
quatity,
item_price,
quatity*item_price AS expanded_price
FROM orderotems
WHERE order_num=2005;

  为了匹配两个列以vender.vend_id和products.vend_id指定,需要使用完全限定名,防止引起歧义。

4.行

第十一章 用数据处理函数

1.常用文本处理函数
Left()返回左边字符,Length(),Locate()找出串的一个子串,Lower(),LTrim(),Right(),Rtrim(),Soundex()返回串的soundex值,SubString()返回子串的字符,Upper()
2.SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact)=Soundex('Y Lie');
3.日期和时间处理函数
AddDate()天,周,AddTime()时,分,CurDate(),CurTime(),Date()日期时间的日期部分,DateDiff()日期之差,Date_Add()高度灵活的日期运算函数,Date_Format()返回格式化日期或者时间串,Day(),DayOfWeek(),Hour(),Minute(),Month(),Now(),Second(),Time(),Year()。
4.数值处理函数
abs(),cos(),exp(),mod(),pi(),rand()随机数,sin(),sqrt(),tan()
第十二章 汇总数据
1.聚集函数 运行在行组上,计算和返回单个值的函数。
2.AVG()平均值COUNT()行数 MAX()MIN()SUM()
3.SELECT AVG(prod_price)AS avg_price
FROM products;
4.确定特定列的平均值
AVG()忽略NULL的行
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id =1003;
5.COUNT()
COUNT()包含null,COUNT(column)忽略null
SELECT COUNT(
) AS num_cust
FROM customers;
6.MAX()在用于文本数据时,如果数
据按相应的列排序,则MAX()返回最后一行。???
7.SELECT SUM(item_pricequantity)AS total_price
FROM orderitems
WHERE order_num=2005;
8.distinct只考虑不同的
SELECT AVG(DISTICT prod_price)AS avg_price
FROM products
WHERE vend_id =1003;
9tips:
如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT
不能用于COUNT(
),因此不允许使用COUNT( DISTINCT),
否则会产生错误。类似地, DISTINCT必须使用列名,不能用
于计算或表达式。

  笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行数目将是第一个表中的行数乘以第二个表中的行数。

表中的一条记录

第十三章 分组数据

1.SELECT vend_id, COUNT() AS num_prods
FROM products
GROUP BY vend_id;
GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套,
为数据分组提供更细致的控制。
 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上
进行汇总。换句话说,在建立分组时,指定的所有列都一起计算
(所以不能从个别的列取回数据)。
 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
 除聚集计算语句外, SELECT语句中的每个列都必须在GROUP BY子
句中给出。
 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列
中有多行NULL值,它们将分为一组。
 GROUP BY子句必须出现在WHERE子句之后, ORDER BY子句之前。
使用有 WITH ROLLUP 子句的 GROUP BY 语句时,不能再使用 ORDER语句对结果集进行排序,如果对返回的结果顺序不满意,需要应用程序获得结果后在程序中进行排序
2.WHERE 过滤行,HAVING过滤分组
SELECT cust_id,COUNT(
)AS orders
FROM orders
GROUP BY cust_id
HAVING count()>=2;
这里有另一种理解方法,WHERE在数据
分组前进行过滤, HAVING在数据分组后进行过滤。这是一个重
要的区别, WHERE排除的行不包括在分组中。这可能会改变计
算值,从而影响HAVING子句中基于这些值过滤掉的分组。
3.SELECT vend_id,COUNT(
)AS num_prods
FROM products
WHERE prod_price>=10
GROUP BY vend_id
HAVING COUNT()>=2;
4.如果用了having 就要用group by。
SELECT order_num,SUM(quatity
item_price)AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quatity*item_price)>=50
ORDER BY ordertotal;
5.SELECT 是
WHERE 否
GROUP BY 按组计算聚集使用
HAVING 否 ORDER BY
LIMIT 否

  select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

5.主键

第14章 使用子查询

1.SQL
SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
FROM orderitems
WHERE prod_id='TNT2');
2.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'));
3.列必须匹配:
在WHERE子句中使用子查询(如这里所示),应
该保证SELECT语句具有与WHERE子句中相同数目的列。通常,
子查询将返回单个列并且与单个列匹配,但如果需要也可以
使用多个列。
4.SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE order.cust_id=customers.cust_id)AS orders
FROM customers
ORDER BY cust_name;

 

一列或者一组列,能够唯一区分表中的每个行。

第十五章 联结表

1.外键为某个表中的一列,它包含另一个表
的主键值,定义了两个表之间的关系。
2.在一条SELECT语句中联结几个表时,相应的关系是
在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进
行联结的东西。你必须自己做这件事情。在联结两个表时,你实际上做
的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE子句作为
过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。
3.由没有联结条件的表关系返回
的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘
以第二个表中的行数。
4.SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id;
5.ANSI SQL规范首选INNER JOIN语法。此外,
尽管使用WHERE子句定义联结的确比较简单,但是使用明确的
联结语法能够确保不会忘记联结条件,有时候这样做也能影响
性能
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'));
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=order.order_num
ADN prod_id='TNT2';
第十六章 创建高级联结


1.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';
自联结通常作为外部语句用来替代
从相同表中检索数据时使用的子查询语句。虽然最终的结果是
相同的,但有时候处理联结远比处理子查询快得多。应该试一
下两种方法,以确定哪一种的性能更好。
2.自然联结
SELECT c.*o.order_num,o.order_date,
oi.prood_id,oi.quatity,OI.item_price
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='FB';
通配符只对第一个表使用。所有其他列明确列
出,所以没有重复的列被检索出来。
3.SELECT customer.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id;
4.SELECT customer.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id;
这条SELECT语句使用了关
键字OUTER JOIN来指定联结的类型(而不是在WHERE子句中指
定)。但是,与内部联结关联两个表中的行不同的是,外部联结还包括没
有关联行的行。在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字
指定包括其所有行的表( RIGHT指出的是OUTER JOIN右边的表,而LEFT
指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTER JOIN从FROM
子句的左边表( customers表)中选择所有行。???
5.SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num)AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id
GROUP BY custmers.cust_id;
6.SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num)AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id
GROUP BY customers.cust_id:

三、内部联结

习惯上:不更新主键列中的值,不重用主键列的值,不在主键列中使用可能更改的值。

第十七章 组合查询

1.使用UNION
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);
TIPS:UNION可能比使用WHERE子句更为复杂。
但对于更复杂的过滤条件,或者从多个表(而不是单个表)中检索数据
的情形,使用UNION可能会使处理更简单。

  1. UNION规则
    必须由两条或两条以上的SELECT语句组成,语句之间用关
    键字UNION分隔(因此,如果组合4条SELECT语句,将要使用3个
    UNION关键字)。
     UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过
    分析各个列不需要以相同的次序列出)。
     列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以
    隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。
    如果遵守了这些基本规则或限制,则可以将并用于任何数据检索任务。
    UNION可以去除重复的行,想返回所有匹配行,使用UNION ALL 而不是UNION,在使用UNION组合查询的时候,只能使用一条ORDER BY,必须出现在最后一条SELECT语句之后 不能用多条

  等值联结:它基于两个表之间的相等测试。可以使用:

6.外键

第十八章,全文本搜索

1.MySQL
支持几种基本的数据库引擎。并非所有的引擎都支持本书所描
述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB,
前者支持全文本搜索,而后者不支持。这就是为什么虽然本书
中 创 建 的 多 数 样 例 表 使 用 InnoDB , 而 有 一 个 样 例 表
( productnotes表)却使用MyISAM的原因。

  inner join on:

外键为某个表的一列,它包含另一个表的主键值。

  select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

(二)mysql使用方法

  以上语句以inner join指定,在使用这种语法时,联结条件用特定的on子句而不是where子句给出。传递给on实际条件与传递给where相同。

1.mysql是什么?

  inner join on性能比where好。

mysql是一种DBMS,即是一种数据库软件。它是一种基于客户机-服务器的数据库。

 

2.mysql的优点:

四、联结多个表

(1)因为开源,成本低

  SQL对一条select语句中可以联结的数目没有限制。创建联结基本规则也相同。首先列出所有表,然后定义表之间的关系。

(2)执行速度快,性能好

  select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id=vendor.vend_id and orderitems.prod_id = products.prod_id and order_num=20005;

(3)可信赖

  联结的表越多,性能下降的越厉害。

(4)易于安装和使用

  我们在之前使用的子查询其实也可以使用联结语句。如下:

3.连接好数据库后,就可以访问数据库并做操作,其中use是用来选择数据库的,show是用来查看mysql数据库、表、每部信息的。

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

(1)use + database name;

而子查询中可以这样写:select cust_name,cust_contact form customers where cust_id in (select cust_id from orders where order_num in(select order_num from orderitems where prod_id='TNT2'))

      表示切换使用哪个数据库。

 

      用use打开数据库,才能读取其中的数据。

五、使用表别名

(2)show database;

  不仅仅可以对列起别名,也可以对表起别名。

      返回数据库的列表

  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.cust_id and oi.order_num=o.order_num and prod_id ='TNT2';

(3)show tables;

 

      获取一个数据库内的表的列表

六、使用不同类型的联结:

(4)show columns from customers;

  我们在前面使用了内部联结和等值联结,其实还有更复杂的联结

      显示每列的信息,后面用的比较多的是 desc customers;

  1、自联结:假如你发现某物品(ID为DTNTR)存在问题,因此想知道该物品的供应商生产的其他物品是否也存在这些问题,因此,你需要先查到生产ID为DTNTR的物品供应商,然后找出这个供应商生产的其他物品。自连接的速度非常好:

(三)用select检索数据

  select prod_id,prod_name from products where vend_id=(select vend_id form products where prod_id ='DTNTR');

1.select:

  也可以使用别名:  

用途:从表中检索一个或者多个数据列。

  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语句中需要体现两种信息:选什么,从什么地方选。

  

2.检索单列

  2、外部连接

例如:select prod_name from products;

  许多连接将一个表中的行与另一个表中的行向关联,但有时候会需要包含没有关联行的那些行。

解释:从products表中检索出来prod_name列

  外链接不包含在相关表中没有关联行的行,这种类型的联结称为外部联结,例如:

注意:返回结果是未排序的。

  1、对每个客户下了多少订单进行计数,包括那些至今尚未下订单的客户。

3.检索多列

  2、列出所有产品以及订购数目,包括没有人订购的产品

例如:select prod_id,prod_name,prod_price from products;

  3、计算平均销售规模,包括那些至今尚未下订单的客户。

4.检索所有列

  select customers.cust_id,orders.order_num from customers left outer join orders on(customers.cust_id=orders.cust_id);

select * from product;

  以上语句我们使用了左外连接,目的是也关联左表并没有被查询关联数据。当然我们也可以使用右连接。

优点:不明确列名的时候使用。

  select customers.cust_id,orders.order_num form customers right outer join orders on (orders.cust_id=customers.cust_id);

5.distinct关键字

 

作用:指示mysql只返回不同的值的行

  七、使用带聚集函数的联结

例如:select distinct vend_id from products;

  聚集函数也可以与联结一起使用。

使用:它必须放在列名前面

  如果要检索所有客户及每个客户所下的订单数:

6.limit

  select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord from customers inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

作用:返回结果的前几行

  聚集函数也可以分方便的与其他联结一起使用。

例如:select prod_name from products limit 5;

  select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord from customers left outer orders on customers.cust_id = orders.cust_id group by customers.cust_id ;

如果是 select prod_name from products limit 5,5;则表示从行5开始,检索5行.

  外部联结,把没下订单的客户也查出来了。

注意:检索出来第一行是行0。如果行数不够,能返回多少就返回多少。

7.完全限定的表名

select products.prod_name from product;

(四)用where进行数据过滤

1、where子句

作用:指定搜索条件,因为一般数据库表都包含大量的数据,很少我们需要所有的行,通常会根据特定需要来提取数据的子集。where语句就是来指定搜索条件(过滤条件)

位置:放在from之后,order by之前

例子:select prod_name,prod_price from products where prod_price = 2.50

解释:这里采用了相等测试,只返回prod_price为2.5的行,还可以有等于、不等于、小于、小于等、大于、大于等、between操作符。

扩展:根据这些操作符,可以做单个值匹配(=)、不匹配检查(!=或者<>)、范围值检查(between)、空值检查(is null)

例子1:between用法,它需要两个值。

select prod_name,prod_price from products where prod_price between 5 and 10;

例子2:空值检查

select cust_id from customers where cust_email is null;

2.组合where子句

目的:为了进行更强的过滤控制,mysql允许给出多个where自居,以逻辑操作符and或者or的方式使用。

and例子:

select prod_id,prod_price,prod_name

from products

where vend_id =1003 and prod_price <=10;

解释:必须同时满足两个条件

or例子:

select prod_id,prod_price,prod_name

from products

where vend_id =1003 or vend_id =1002;

解释:满足任意一个条件即可

注意:计算次序用圆括号界定,要不容易混淆。

3、in操作

作用:指定条件范围

例子:select prod_name,prod_price

from products

where vend_id in (1002,1003);

解释:检索供应商1002和1003制造的所有产品。in操作符后面跟着的是合法值得清单。

另一种写法:

select prod_name,prod_price

from products

where vend_id = 1002 or vend_id = 1003;

那么为什么使用in操作符呢,优点是什么呢?

(1)清楚只管

(2)计算次序容易理解

(3)in执行比or执行的快

(4)在in中可以包含其他的select语句

4、not操作符:

作用:where子句中用来否定后跟条件的关键字。

例如:select prod_name,prod_price

from products

where vend_id not in (1002,1003);

解释:检索除了1002和1003之外的所有。

(五)通配符过滤

1、应用场景

  之前说的数据过滤都是对已知值进行过滤的,比如说匹配一个值或者多个值,大于某个值或者是小于某个值,或者是检查某个范围的值。

  但是如果我要搜索产品名中包含anvil的所有产品呢,这时候通配符就可以大显身手了,我们可以利用通配符搜索模式,找出产品名中任何位置出现anvil的产品。

2、什么是通配符

概念:用来匹配值得一部分的特殊字符

如何使用:为了在搜索子句中使用通配符,必须使用like操作符。

3、有哪些通配符以及如何使用呢?

(一)百分号通配符%

表示:任何字符出现任意次数,也可以是0次

例子:

(1)找到以jet开头的产品,接受jet后面为任意多个字符

select prod_id,prod_name

from products

where prod_name like 'jet%';

(2)匹配任何位置包含anvil,不论在之前还是之后出现什么字符。

select prod_id,prod_name

from products

where prod_name like '%anvil%';

(3)找到以s起头、以e结尾的所有产品:

select prod_name

from products

where prod_name like 's%e';

注意:

(1)可以用‘**%’的形式进行尾空格处理,也可以用trim函数进行处理

(2)%通配符不能匹配null

(二)下划线通配符_

表示:下划线只匹配单个字符而不是多个字符

这也是与%的区别,这里就不举例赘述了。

4、小结:

通配符是一种非常有用的搜索工具,但是不能过度使用,否则搜索时间会很长。

(六)正则表达式匹配

1、啥是正则表达式

正则表达式的作用是匹配文本,将一个正则表达式与一个文本串进行比较,mysql用where子句对正则表达式提供了初步的支持,允许指定正则表达式,过滤select检索出的数据。

2、like与regexp的区别

举个例子来看两者的差别:

(一)like统配符

select prod_name

from products

where prod_name like '1000'

order by prod_name;

结果:不返回数据

(二)正则表达式

select prod_name

from products

where prod_name regexp '1000'

order by prod_name;

结果:返回一行

原因:like匹配的是整个列,只有使用通配符的时候才会返回。而regexp是在列值中匹配,如果被匹配的文本在列值中出现regexp将会找到他,相应的行将被返回。

3、有哪几种匹配呢?

(一)基本字符匹配

例1:检索列prod_name包含文本1000的所有行

select prod_name

from products

where prod_name regexp '1000'

order by  prod_name;

注意:regexp后所跟的东西作为正则表达式处理。

例2:检索列prod_name包含000的所有行

select prod_name

from products

where prod_name regexp '.000'

order by  prod_name;

注意:.是正则表达式语言中的一个特殊的字符,它表示的匹配任意一个字符,所以1000和2000都符合条件。

(二)or匹配

使用:当我想搜索两个串之一时,使用|

例子:匹配prod_name为1000或者2000的情况

select prod_name

from products

where prod_name regexp '1000|2000'

order by prod_name;

(三)匹配几个字符之一

表示:匹配任何一个单一字符,当想匹配特定字符的时候,可通过制定一组用[]括起来的字符来完成。

例1:

select prod_name

from products

where prod_name regexp '[123] Ton'

order by prod_name;

解释:正则表达式是[123] Ton,[123]定义了一组字符,即匹配1或者2或者3,这么看,其实[ ]是另一种形式or语句,也可以看做是[1|2|3]的缩写。

例2:

select prod_name

from products

where prod_name regexp '[^123] Ton'

order by prod_name;

解释:匹配的是除这些字符意外的任何东西。

(三)匹配范围

集合可以用来定义要匹配的一个或者多个字符,如果想匹配0到9,可以用[0123456789],也可以用[0-9],并且范围不一定是数值,也可以匹配字符,[a-z]匹配任意的字母字符。

例1:

select prod_name

from products

where prod_name regexp '[1-5] Ton'

order by prod_name;

解释:这个表达式的意思是匹配1到5,例如.5 Ton也会返回。

(四)匹配特殊字符

为了匹配特殊字符,必须使用\为前导

例如:

(1) \-匹配-,\.匹配.

(2) 匹配用\

(五)匹配字符类

(1)[:alnum:]  --  任意字符和数字

(2)[:alpha:]  --  任意字符

(3)[:blank:]  --  空格和制表

(4)[:cntrl:]  --  ascii控制字符

(5)[:digit:]  --  任意数字

(6)[:graph:]  --  与[:print:]相同,但是不包含空格

(7)[:print:]  --  任意可打印字符

(8)[:lower:]  --  任意小写字母

(9)[:punct:]  --  既不在[:alpha:]也不在[:cntrl:]中的任意字符

(10)[:space:]  --  包括空格在内的任意空白字符

(11)[:upper:]  --  任意大写字母

(12)[:xdigit:]  --  任意十六进制数字

(六)匹配多个实例

意义:之前的正则表达式师徒匹配单词出现。但是有的时候需要对匹配书目进行更强的控制

例1:

select prod_name

from products

where prod_name regexp '\([0-9] sticks?\)'

order by prod_name;

解释:其中\是用来匹配括号的,[0-9]用来匹配任意数字,sticks?匹配stick和sticks,因为?匹配他前面任何字符的0次或者1次出现。

例2:

select prod_name

from products

where prod_name regexp '[[:digit:]]{4}'

order by prod_name;

解释:[:digit:]匹配任意数字,{4}确切的要求它前面的数字出现4次,所有正则表达式匹配连在一起的任意4位数字。

同理,可以写成:

select prod_name

from products

where prod_name regexp '[0-9][0-9][0-9][0-9]’

order by prod_name;

扩展:

*表示0个或者过个匹配

+表示1个或者多个匹配

?表示0个或者1个匹配

{n}表示指定书目的匹配

{n,}表示不少于指定数目的匹配

{n,m}表示匹配数目的范围

(七)定位符

目的:为了匹配特定位置的文本。

例子:找出以一个数或者小数点开始的所有产品,这里需要定位符^,表示文本的开始。

select prod_name

from products

where prod_name regexp '^[0-9\.]'

order by prod_name;

注意:在集合^[123]中表示否定该集合,在此处表示的是文本的开始。

扩展:

(1)^ 文本的开始

(2)$ 文本的结尾

(七)计算字段

--为什么需要计算字段?

因为存储在数据库表中的数据一般不是应用程序所需要的格式,有的时候我们需要对原始数据做一些变换等需求。这就是计算字段发挥作用的时候了。

注意:

(1)我们需要直接从数据库中检索出转换、计算或者格式化过的数据,而不是检索出数据,然后再在客户机应用程序或者报告程序中重新格式化。

(2)计算字段并不实际存在于数据库表中,计算字段是运行时在select语句内创建的。

本文主要介绍两个知识点,一个是拼接字段,一个是执行算数计算。

1、拼接字段

拼接:将值联结到一起构成一个单个值。在select语句中,使用concat()函数来拼接两个列,待拼接的各个串用逗号分隔。

例子:

select concat(vend_name,' (',vend_country,') ')

from vendors

order by vend_name;

2、执行算数计算

例子:

select prod_id,

          quantity,

          item_price,

          quantity*item_price as expanded_price

from orderitems

where order_name = 20005;

解释:其中expanded_price是一个计算字段,计算为quantity*item_price。在执行算数计算的时候,加减乘除都可以甲酸,并且用圆括号来确定计算顺序。

扩展:删除右侧的所有空格,可以用rtrim()函数,如果是删除左侧的所有空格,可用ltrim(),去掉两边的空格是trim(),在拼接字段有时候会用到。例如:

select concat(rtrim(vend_name),' (',rtrim(vend_country),')') as

vend_title

from vendors

order by vend_name;

(八)数据处理函数

本文主要介绍mysql支持什么样的函数,以及如何使用这些函数。

1、使用函数,那么主要有哪些种类的函数呢?

(1)用于处理文本串的文本函数。比如说:删除或者填充值,转化大小写

(2)用于在数值数据上进行算数计算,比如说:返回绝对值以及代数运算

(3)用于处理日期和时间值,并且从这些值中提取特定的成分的日期和时间函数,比如说返回这两个值的日期之差,检查日期的有效性。

(4)返回DBMS正使用的特殊信息的系统函数。比如返回用户登录信息、检查版本细节。

2、文本处理函数

(1)rtrim():去除列值右边的空格

(2)upper():将文本转换大写

(3)left():返回串左边的字符

(4)length():返回串的长度

(5)locate():找出串的一个子串

(6)lower():转换为小写

(7)soundex():返回串的soundex值,发音相似

(8)substring():返回子串的字符

例子:

select cust_name,cust_contact

from customers

where soundex(cust_contact) = soundex('Y Lie');

3、日期和时间处理函数

(1)adddate() :增加一个日期(天、周)

(2)addtime() :增加一个时间(时、分)

(3)curdate():返回当前日期

(4)date():返回日期时间的日期部分

(5)datediff():计算两个日期之差

(6)day():返回一个日期的天数部分

(7)dayofweek():对应一个日期返回对应星期几

(8)hour():返回一个日期的小时部分

(9)minute():返回一个时间的分钟部分

(10)month():返回一个时间的月份部分

(11)now():返回当前日期的时间

(12)second():返回一个时间的秒部分

(13)time():返回一个日期时间的时间部分

(14)year():返回一个日期的年份部分

注意:不管是插入表还是更新表还是用where子句进行过滤,日期必须为yyyy-mm-dd的格式,虽然其他的日期格式可能可行,但是这是首选的日期格式,它排除了多意义性。

例如:

select cust_id,order_num

from orders

where order_date = '2015-09-01';

如果日期的形式是‘2015-09-01 00:00:00’,那么就检索不出来,所以更加可靠的形式为:

select cust_id,order_num

from orders

where date(order_date) = '2015-09-01';

如果我想检索出2005年9月下的所有订单:

方法一:

select cust_id,order_num

from orders

where date(order_date) between '2005-09-01' and '2015-09-30';

方法二:

select cust_id,order_num

from orders

where year(order_date) =2005 and month(order_date)=9;

4、数值处理函数:

它一般用于代数、三角或几何运算。

abs、cos、exp、mod、pi、rand、sin、sqrt、tan

(九)汇总数据

这部分主要介绍聚集函数以及如何利用它们来汇总表的数据。

1、聚集函数的概念:运行在行组上,计算和返回单个值的函数。

2、mysql主要的聚集函数有哪些?

(1)avg:返回某个列的平均值

(2)count:返回某列的行数

(3)max:返回某列的最大值

(4)min:返回某列的最小值

(5)sum:返回某列值之和

3、avg

概念:

(1)通过对表中的行数计数病计算特定列值之和。求得该列的平均值。

(2)avg可以用来返回所有列的平均值,也可以用来范数特定列或行的平均值。

例子:

selelct avg(prod_price) as avg_price

from product;

解释:返回products表中所有产品的平均价格

select avg(prod_price) as avg_price

from products

where vend_id = 1003;

解释:指定特定行或者特定列求平均值,它返回的是特定供应商1003所提供产品的平均价格。

注意:

(1)avg只能确定特定数值列的平均值,而且列名必须作为函数的参数给出。如果说我们想获得更多列的平均值,要使用多个avg()函数。

(2)avg()函数忽略列值为NULL的行

4、count

概念:

(1)count()主要是用来计数

(2)利用count()确定表中行的数目,以及符合特定条件的行的数目

注意:

(1)使用count(*)对表中行的数目进行统计,不管是表列中包含的是null和非null

(2)使用count(column)对特定的具有值的行进行计数,忽略null

例子:

select count(*) as num_cust

from customers;

解释:返回customers表中客户的总数,这里利用count(*)对所有的行进行计数,不管各行中有什么值,计数值将在num_cust中返回。

select count(cust_emial) as num_cust

from customers;

解释:只对有点子邮件的客户进行计数。

5、max()

概念:max()返回指定列的最大值,但是max()需要制定列名

例子:

select max(prod_price) as max_price

from products;

解释:返回products表中最贵的物品。

注意:

(1)在用于文本数据的时候,如果数据是按相应的列排序,则max()返回的是最后一行。

(2)max()函数忽略列值为null的行

6、min()

概念:max()返回指定列的最小值,但是max()需要制定列名,使用方法与max同理

7、sum

概念:用来返回指定列值的和(总计)

例子:

select sum(quantity) as items_ordered

from orderitems

where order_num = 20005;

解释:sum(quantity)用来返回订单中所有物品的数量之和,where子句值统计某个物品订单中的物品。

同理,sum也可以用来合计计算值。例如下面的例子,可以得到总的订单金额,where子句同样保证只统计某个物品订单中的物品。

select sum(item_price*quantity) as total_price

from orderitems

where order_num = 20005;

注意:sum()会忽略列值为null的行。

8、在聚合函数中用distinct

例子:加了distinct参数之后,平均值只考虑各个不同的价格

select avg(distinct prod_price) as avg_price

from products

where vend_id = 1003;

注意:

(1)distinct只能用于count()

(2)不能用count(distinct)

(3)不能用distinct count(*)

9、组合聚集函数,看到这个例子就会秒懂~

例如:

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;

(十)排序检索数据

目的:为了明确的排序用select语句检索出来的数据,可以用order by子句,order by往往取一个或者多个列的名字,根据此对输出进行排序。

1、如何用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排序。

2、指定排序方向

升序:asc,默认的

降序:desc

例子:按照价格降序,最贵的在前面。

select prod_id,prod_price,prod_name

from products

order by prod_price desc;

如果按照多个列进行排序呢?例如:

select prod_id,prod_price,prod_name

from products

order by prod_price desc,prod_name;

解释:以降序排序产品,然后再对产品名排序。

注意:这里只对prod_price降序了,如果想在多个列上进行姜旭排序,必须对每个列指定desc关键字。

扩展:使用order by和limit的组合,能够找出一个列的最高和最低的值。

例如:如何找出最昂贵物品的值

select prod_price

from products

order by prod_price desc

limit 1;

(十一)分组数据

1、什么是分组?

分组就是汇总表内容的子集,分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算。

2、涉及的子句

group by子句和having子句

3、下面从两个部分介绍分组:

(1)创建分组 --group by

(2)过滤分组 --having

(一)创建分组

举例:

select vend_id, count(※) as num_prods

from products

group by vend_id;

解释:

对vend_id分组,并且统计个数。

group by指定要被分组的目标(vend_id),并做统计。

注意:

(1)group by子句可以包含任意数目的列

(2)如果在group by子句中嵌套了分组,数据将会在最后规定的分组上进行汇总。

(3)在group by中列出的列不能是聚集函数

(4)如果在select中使用表达式,则必须在group by子句中指定相同的表达式,不能使用别名。

(5)除了聚集计算语句之外,select语句中的没个列都必须在group by子句中给出。

(6)如果分组列中具有null,则它们将会作为一个分组返回。

(7)group by必须在where语句之后,order语句之前。

(二)过滤分组

意义:规定了包括哪些分组,排除哪些分组

比如:我想列出至少有两个订单的所有顾客,此时,必须基于完整的分组进行过滤,而不是根据个别的行进行过滤。

注意:where过滤指定的是行而不是分组。where没有分组的概念。

引入:having进行过滤分组,事实上,所有类型的where子句都可以用having来代替。

where和having的区别:where过滤行,having过滤分组

举例1:

select cust_id, count(※)  as orders

from orders

group by cust_id

having count(※)>2;

解释:订单数大于2的用户

举例2:

select vend_id, count(※) as num_prods

from products

where prod_price >10

group by vend_id

having count(※)>2;

解释:同时用了where和having,表示具有2个以上,价格10以上的产品的供应商。

最后注意:

一般在使用group by子句的时候,应该给出order by子句,这是保证数据正确排序的唯一方法。千万不要依赖group by的排序数据。

总结:

select子句的顺序:

select

from

where

group by

having

order by

limit

(十二)子查询

子查询经常出现的场景:

(1)where子句的in操作符中

(2)用来填充计算列

1、在这里举一个例子,就知道子查询是什么,以及子查询如何使用了。

举例:列出订购物品TNT2的所有客户。

参考:mysql必知必会样例表

涉及的表:orderitems、orders、customers

检索的步骤:

(1)检索包含物品TNT2的所有订单号

select order_num

from orderitems

where prod_id = 'TNT2';

得到的结果是:order_num:20005和20007

(2)查询具有订单20005和20007的客户

select cust_id

from orders

where order_num in (20005,20007);

得到的结果是:cust_id:10001和10004

(3)检索客户id为10001和10004的客户信息

select cust_name,cust_contact

from customers

where cust_id in (10001,10004);

即得到了最后的结果

将这三个查询合并为一个:

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

2、再举一个例子:现在需要显示customers表中每个客户的订单总数。

涉及的表:customers,orders(存储订单与相应的客户id)

(1)先过滤某个特定用户的订单数量,再推广到每个用户。

select count(※) as orders

from orders

where cust_id = 10001;

(2)对每个用户进行count计算

select cust_name,

          cust_state,

          (select count(※)

            from orders

            where orders.cust_id = customers.cust_id) as orders

from customers

order by cust_name;

备注:该子查询对检索出的每个客户执行一次

子查询的优缺点:

优点:

在where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句

缺点:

(1)包含子查询的select语句难以阅读和调试。

(2)虽然对嵌套的子查询的数目没有限制,不过在实际使用中由于性能的限制,不能嵌套太多的子查询。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Mysql必知必会,mysql基础整理

关键词:

上一篇:没有了

下一篇:没有了