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

一、联结

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

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

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

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完成的。

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来创建和操纵容器。

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.表

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

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

  

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

二、创建联结

3.列和数据类型

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

列:表中的一个字段

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

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

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

4.行

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

表中的一条记录

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

5.主键

 

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

三、内部联结

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

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

6.外键

  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:

发表评论

电子邮件地址不会被公开。 必填项已用*标注