项目四公司管理数据库系统的数据查询

项目四公司管理数据库系统的数据查询 终极目标：能根据需要灵活、快速地查询公司管理系统数据库的数据

一、教学目标： • 1、会在一个数据表中实现数据的简单查询 • 2、会同时在多个数据表中实现数据的复合查询 • 3、会实现分组查询 • 4、会对查询结果集排序 • 5、会利用库函数进行数据统计 • 二、工作任务 • 根据需要灵活快速地查询公司管理数据库系统的数据

模块1 数据库系统的简单查询 • 一、教学目标： • 1、准确理解TRANSACT-SQL查询语句的基本结构 • 2、能根据需要灵活使用查询语句的各子句 • 二、工作任务 • 公司对所有招收的雇员的材料都存储在公司管理数据库系统COMPANYINFO的employee表中，该表中包含了雇员的姓名、性别、出生年月、雇佣日期、特长和薪水等信息。现编写查询语句，按要求得到相应的结果。

SELECT语句 • 使用数据库和数据表的主要目的是存储数据，以便在需要时进行检索、统计或组织输出，通过T-SQL的查询可以从表或视图中迅速、方便地检索数据。在众多的T-SQL语句中，SELECT语句是使用频率最高的一个。 • 查询的最基本方式是使用SELECT语句，按照用户给定的条件从SQL SERVER数据库中取出数据，并将数据通过一个或多个结果集返回给用户。

一、SELECT格式 • 从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。 • SELECT [ALL| DISTINCT] <目标表达式>[,…<目标表达式>] • [ INTO <新表名>] • FROM <表名或视图名>[,… <表名或视图名> • [ WHERE <条件表达式>] • [ GROUP BY <列名1> [HAVING <表达式>] ] • [ ORDER BY <列名2> [ ASC | DESC ] ]

1、子句的功能 • Select子句：指定由查询返回的列。 • Into子句：将检索结果存储到新表或视图中。 • From子句：用于指定引用的列所在的表和视图。 • Where子句：指定用于限制返回的行的搜索条件。 • Group by子句：指定用来放置输出行的组，并且如果 SELECT 子句 <select list> 中包含聚合函数，则计算每组的汇总值。 • having子句：指定组或聚合的搜索条件。 HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。 • Order by子句：指定结果集的排序。

2、SELECT语句的执行方式 • 1）通过查询设计器辅助生成和执行select语句 • 2）查询分析器执行select语句

二、SELECT 子句 • 功能: 指定由查询返回的列。 • 格式： • SELECT [ALL|DISTINCT][TOP N [ PERCENT ] ] • 列名1[,列名2,…列名N] • FROM 表名或视图名 • 参数的含义： • ALL：指定在结果集中可以显示重复行。ALL 是默认设置。 • DISTINCT：指定在结果集中只能显示唯一行。即表示输出无重复的所有记录。 • TOP N[PERCENT]：指定只从查询结果集中输出前 N行。如果还指定了 PERCENT，则只从结果集中输出前百分之 N 行。

1、查询所有的列 • SELECT 子句中，在选择列表处使用通配符“*”,表示选择指定的表或视图中所有的列。服务器会按用户创建表格时声明列的顺序来显示所有的列. • 语法： • SELECT * • FROM 表名

【例1】从公司管理数据库COMPANYINFO中的客户表(customer)中检索所有的客户的公司名称、联系人姓名、联系方式、地址和邮编。【例1】从公司管理数据库COMPANYINFO中的客户表(customer)中检索所有的客户的公司名称、联系人姓名、联系方式、地址和邮编。 • USE COMPANYINFO • GO • SELECT * • FROM customer • GO

2、查询特定的列 • 【例2】从公司管理数据库(COMPANYINFO)中的客户表(customer)中检索所有的客户的公司名称、联系人姓名、地址。 • USE COMPANYINFO • GO • SELECT 公司名称，联系人姓名，地址 • FROM customer • GO

【例3】从公司管理数据库(COMPANYINFO)中的雇员表（employee）中检索所有的雇员的姓名和特长。【例3】从公司管理数据库(COMPANYINFO)中的雇员表（employee）中检索所有的雇员的姓名和特长。 • USE COMPANYINFO • GO • SELECT 姓名，特长 • FROM employee • GO

3、指定特定列的列名 • （1）采用符合ANSI规则的标准方法，在列表达式后面给出列名。 • 【例4】查询每个人的薪水降低30%信息。 • USE COMPANYINFO • GO • SELECT 姓名, 薪水‘原薪水’, 薪水-薪水*0.3 • '现薪水' • FROM employee • GO

（2）用“=”来连接列表达式 • 【例5】查询每个人的薪水降低30%信息。 • USE COMPANYINFO • GO • SELECT 姓名, '原薪水'=薪水, '现薪水'=薪水-薪水*0.3 • FROM employee • GO

（3）用AS关键字来连接列表达式和指定的列名 • 【例6】查询每个人的薪水降低30%信息。 • USE COMPANYINFO • GO • SELECT 姓名, 薪水as '原薪水' , • 薪水-薪水*0.3 as '现薪水' • FROM employee • GO

4．使用TOP关键字 • SQL Server 2000提供了TOP关键字，让用户指定返回前面一定数量的数据。 • 语法格式： • SELECT [TOP n | TOP n PERCENT] 列名1[,列名2,…列名n] • FROM 表名 • 其中: • TOP n:表示返回最前面的n行，n表示返回的行数。 • TOP n PERCENT:表示返回的前面的n%行。

【例7】从northwind数据库的customers表中返回前面10行数据。【例7】从northwind数据库的customers表中返回前面10行数据。 • use northwind • go • select top 10 * • from customers • go

【例8】从northwind数据库中的customers表中返回前10%的数据。【例8】从northwind数据库中的customers表中返回前10%的数据。 • use northwind • go • select top 10 percent * • from customers • go

三、 WHERE 字句 • 使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行。 • 语法格式： • SELECT 列名1 [,列名2, … 列名n] • FROM 表名 • WHERE 条件

常用查询条件

1．使用算术表达式 • 使用算术表达式作为搜索条件的一般表达形式是： • 表达式算术操作符表达式 • 表达式为：常量、变量和列表达式的任意有效组合。

【例9】查询COMPANYINFO数据库的雇员表（employee）中，特长是计算机的雇员的信息。【例9】查询COMPANYINFO数据库的雇员表（employee）中，特长是计算机的雇员的信息。 • USE COMPANYINFO • GO • SELECT * • FROM employee • WHERE 特长='计算机'

【例10】查询COMPANYINFO数据库的雇员表（employee）中，薪水超过3000元的雇员的姓名和薪水。【例10】查询COMPANYINFO数据库的雇员表（employee）中，薪水超过3000元的雇员的姓名和薪水。 • USE COMPANYINFO • GO • SELECT 姓名，薪水 • FROM employee • WHERE 薪水>=3000 • GO

2．使用逻辑表达式 • 在T-SQL里的逻辑表达式共有3个。分别是： • NOT：非，对表达式的否定。 • AND：与，连接多个条件，所有的条件都成立时为真。 • OR：或，连接多个条件，只要有一个条件成立就为真。

【例11】查询所有在美国加利福尼亚州的出版社。【例11】查询所有在美国加利福尼亚州的出版社。 • use pubs • go • select pub_id as 出版社代号, pub_name as 出版社名称,city as 城市, state as 州, country as 国家 • from publishers • where country='usa‘ and state ='ca' • go

查询结果如下： • 出版社代号出版社名称城市州国家 • ----- ----------- -------------------- ---- ------------------------------ • 1389 Algodata Infosystems Berkeley CA USA • （所影响的行数为1 行）

3．使用BETWEEN关键字 • 使用BETWEEN关键字可以更方便地限制查询数据的范围。 • 语法格式为： • 表达式 [NOT] BETWEEN 表达式1 AND 表达式2

【例12】查询COMPANYINFO数据库的雇员表（employee）中，薪水在3000元至4000元的雇员的姓名和薪水。【例12】查询COMPANYINFO数据库的雇员表（employee）中，薪水在3000元至4000元的雇员的姓名和薪水。 • SELECT 姓名，薪水 • FROM employee • WHERE 薪水 between 3000 and 4000

【例13】查询库存量大于200和库存量小于100的产品名、库存量和单价。【例13】查询库存量大于200和库存量小于100的产品名、库存量和单价。 • SELECT 产品名，库存量，单价 • FROM product • WHERE 库存量not between 200 and 100

【例14】 查询价格在15和20美元之间的书的书号、种类和价格。 • use pubs • go • select title_id as 书号,type as 种类,price as 原价 • from titles • where price between $15 and $20 • go

【例15】查询书价大于20和书价小于15的书的代号、种类和价格。【例15】查询书价大于20和书价小于15的书的代号、种类和价格。 • use pubs • go • select title_id as 书号,type as 种类, price as 原价 • from titles • where price < $15 or price > $20 • go

查询结果如下： • 书号种类原价 • ------ ------------ --------------------- • BU1111 business 11.9500 • BU2075 business 2.9900 • MC3021 mod_cook 2.9900 • PC1035 popular_comp 22.9500 • …… • （所影响的行数为11 行）

4．使用IN关键字 • 同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。 • 语法格式为： • 表达式[NOT] IN (表达式1 , 表达式2 [,…表达式n])

【例16】查询所有居住在KS、CA、MI或IN州的作家。【例16】查询所有居住在KS、CA、MI或IN州的作家。 • use pubs • go • select au_id,au_lname,au_fname • from authors • where state IN ('CA','KS','MI','IN') • go • 如果不使用IN关键字，这些语句可以使用下面的语句代替： • use pubs • go • select au_id,au_lname,au_fname • from authors • where state='CA ‘ or state='KS‘ or state='MI' or state='IN' • go

【例17】查询所有不在上述4个州居住的作家。 • use pubs • go • select au_id,au_lname,au_fname • from authors • where state not in ('CA', 'KS', 'MI', 'IN') • go • 与下面的语句等价： • use pubs • go • select au_id,au_lname,au_fname • from authors • where state<>'CA ' and state<>'KS' and state<>'MI' • and state<>'IN' • go

5、通配符的使用 • 注意：所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。

【例18】列出所有姓“章”的雇员的信息。 • 查询语句如下： • SELECT * • FROM employee • WHERE 姓名LIKE '章%'

【例19】查询雇员表（employee）中所有姓名中含有“利”字的雇员。【例19】查询雇员表（employee）中所有姓名中含有“利”字的雇员。 • SELECT 雇员ID，姓名 • FROM employee • WHERE 姓名 like '%利%' • GO

【例20】查询雇员表（employee）中所有雇员ID满足前2个字符为“01”，第4个字符为“-”的雇员的姓名和出生年月。【例20】查询雇员表（employee）中所有雇员ID满足前2个字符为“01”，第4个字符为“-”的雇员的姓名和出生年月。 • SELECT 雇员ID，姓名，出生年月 • FROM employee • WHERE 雇员id like '01_-%' • GO

【例21】查询所有au_id满足前2个字符为“72”，第4个字符为“-”的作家的姓名和电话号码。【例21】查询所有au_id满足前2个字符为“72”，第4个字符为“-”的作家的姓名和电话号码。 • use pubs • go • select au_lname, au_fname, phone, au_id • from authors • where au_id like '72_-%' • go

查询结果如下： • au_lname au_fname phone au_id • -------------------------- -------------------- ------------ ----------- • DeFrance Michel 219 547-9982 722-51-5454 • Stringer Dirk 415 843-2991 724-08-9931 • MacFeather Stearns 415 354-7128 724-80-9391 • （所影响的行数为3 行）

【例22】查询所有au_id的第一个字符为5-9、第二个字符为1-4的作家的姓名和电话号码。【例22】查询所有au_id的第一个字符为5-9、第二个字符为1-4的作家的姓名和电话号码。 • use pubs • go • select au_lname,au_fname, phone, au_id • from authors • where au_id like '[5-9][1-4]%' • go

五、 from 子句 • 在每一条要从表或视图中检索数据的 SELCET 语句中，都需要使用 FROM 子句。用 FROM 子句可以： • 列出选择列表和 WHERE 子句中所引用的列所在的表和视图。可用 AS 子句为表和视图的名称指定别名。

FROM 子句可以指定： • 一个或多个表或视图。 • 例： SELECT * FROM Shippers • 两个表或视图之间的联接： • SELECT Cst.CustomerID, Cst.CompanyName, Cst.ContactName, Ord.ShippedDate, Ord.Freight • FROM Northwind.dbo.Orders AS Ord • JOIN Northwind.dbo.Customers AS Cst ON (Cst.CustomerID = Ord.CustomerID)

六、 ORDER BY子句 • SELECT语句获得的数据一般是没有排序的。为了方便阅读和使用，最好对查询的结果进行一次排序。SQL语言中，用于排序的是ORDER BY子句。 • 语法格式为： • ORDER BY 表达式1 [ ASC | DESC] • [,表达式2[ ASC | DESC][,…n]]

【例24】在公司管理数据库（COMPANYINFO）中的订单表(p_order)中，查询产品ID，数量和订货日期，并按的订货日期降序给产品排序。【例24】在公司管理数据库（COMPANYINFO）中的订单表(p_order)中，查询产品ID，数量和订货日期，并按的订货日期降序给产品排序。 • USE COMPANYINFO • GO • SELECT 产品ID，数量，订货日期 • FROM p_order • ORDER BY 订货日期 desc • GO

【例25】查询pubs数据库中的表stores中商店的名字，并按商店名的降序给商店排序。【例25】查询pubs数据库中的表stores中商店的名字，并按商店名的降序给商店排序。 • use pubs • go • select stor_name • from stores • order by stor_name desc • go

【例26】查询titles中各类书的销售利润和书号，并按照各种书的销售利润降序排列。【例26】查询titles中各类书的销售利润和书号，并按照各种书的销售利润降序排列。 • use pubs • go • select title_id,'profit'=price*ytd_sales • from titles • order by profit desc • go

查询结果如下： • title_id profit • -------- --------------------- • PC1035 201501.0000 • TC4203 180397.2000 • PC8888 81900.0000 • BU1032 81859.0500 • BU7832 81859.0500 • PS3333 81399.2800 • …… • （所影响的行数为18 行）

项目四 公司管理数据库系统的 数据查询