SQL操作系统上机报告.docx
- 文档编号:18580410
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:48
- 大小:194.05KB
SQL操作系统上机报告.docx
《SQL操作系统上机报告.docx》由会员分享,可在线阅读,更多相关《SQL操作系统上机报告.docx(48页珍藏版)》请在冰点文库上搜索。
SQL操作系统上机报告
数据库系统概论
上机实验报告
姓名:
陆高昇
学号:
1007130426
指导老师:
安海忠
一、SQLserver实例
1、在命令行方式下使用CREATEDATABASE语句创建一个数据库。
名字为超市管理数据库,数据文件名为超市管理数据库.Mdf,存储在E:
\下,初始大小为20MB,最大为50MB,文件增量以1MB增长。
事务文件为超市管理数据库_Log.Ldf,存储在E盘下,初始大小为10MB,最大为25MB,文件增量以1MB增长。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
2、使用命令行方式在超市经营管理数据库中创建Goods表。
Goods表的结构如表所示。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
3、使用命令行方式在超市经营管理数据库中创建employees表。
employees表的结构如表所示。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
4、使用SQL语句为Goods表的“商品编号”列创建主键约束,以保证不会出现编号相同的商品。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
5、使用命令行方式给超市经营管理数据库的employees表增加一列,列名为“联系方式”,数据类型为VarChar(40)。
在查询窗口输入以下SQL语句并运行:
6、使用命令行方式在超市经营管理数据库中创建mygoods表。
mygoods表的结构如表所示。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
7、使用命令行方式删除Sales数据库中Goods表,
在查询窗口输入以下SQL语句并运行:
8、在命令行方式下使用selectinto语句生成一张新表,新表的名称为“男员工表”,数据来自于employees表中性别=1的编号,姓名,性别等字段。
在查询窗口输入以下SQL语句并运行:
9、使用命令行方式在超市经营管理数据库中创建Sell表。
Sell表的结构如表所示。
(1)打开SQLServerManagementStudio,连接到数据库服务器。
(2)单击“新建查询”按钮,进入到命令行方式。
(3)输入以下SQL语句:
10、在命令行方式下使用DELETE语句删除表Sell中售出时间为1995年1月1日以前的记录。
在查询窗口输入以下SQL语句并运行:
11、查询进货表中所有的生产厂商,去掉重复值,程序为:
12、在employees表中查询姓名为聂浩的员工的联系电话,程序为:
13、查询进货员工的基本信息,程序为:
14、创建一个视图v_sales1,要求基表选择goods,sell,employees;来源字段为sell表中的销售编号、商品编号和数量,goods表中的商品名称,employees表中员工编号和员工姓名;要求查询采购部的聂浩所采购商品的销售情况,程序为:
15、创建一个新视图v_sales2,要求基表选择goods,sell,employees;来源字段为sell表中的销售编号、商品编号和数量;goods表中的商品名称;employees表中员工编号和员工姓名;要求查询销售部的聂浩所销售商品的情况,并对视图的定义进行加密,程序为:
二、Groupby
GROUPBY是分组查询,一般GROUPBY是和聚合函数配合使用,用了GROUPBY按ITEM.ITEMNUM这个字段分组,那其他字段内容不同,变成一对多又改如何显示呢,比如下面所示
AB
1abc
1bcd
1asdfg
selectA,BfromtablegroupbyA
这样查出来是什么结果,
AB
abc
1bcd
asdfg
右边3条如何变成一条,所以需要用到聚合函数,比如
selectA,count(B)数量fromtablegroupbyA
这样的结果就是
A数量
13
groupby有一个原则,就是select后面的所有列中,没有使用聚合函数的列,必须出现在groupby后面
问题应该这么解决,具体的没有上机操作,DISTINCT有可能放到ID前面,具体操作下:
SELECTID,DISTINCTNAME
FROMTAB;
对于SELECT语句完整的句法如下:
SELECT[DISTINCT]目标表的列名或列表达式序列--------必写项(其中DISTINCT是去掉重复的数据项)
FROM基本表名和(或)试图序列----------------------必写项
[WHERE行条件表达式]
[GROUPBY列名序列[HAVING组条件表达式]]
[ODERBY列名[ASC/DESC]]------------------ASC升序排列,DESC降序排列
---------------------------------------------------------------由于你在问题题目中提到了GROUPBY的用法,下面就介绍一部分。
在数据库中,我们可以使用GROUPBY函数把数据组合在一起,从而获得总计信息。
可以把此功能看成是一种当数据从数据库中返回时把相同类型的信息集中到一起的能力。
下面给出了完整列表。
avg([distinct]column_name)
求所有雇员薪水的平均值。
selectAVG(emp_salary)
fromemployee;
求取column_name中的所有值的平均值。
如果使用DISTINCT选项,则只使用不同的非空的数值。
count([distinct]value)
统计雇员的数目。
selectCOUNT(emp_name)
fromemployee;
统计选择行的数目,并忽略VALUE中的空值。
如果使用了DISTINCT选项
则只统计不同的非空数值。
VALUE可以是列名,也可以是表达式。
max(value)
返回薪水的最大值
selectMIN
emp_salary)
fromemployee;
从选定的VALUE中选出数值/字符的最大值,忽略空值。
VALUE要求同上。
min(value)
返回薪水的最小值
selectMIN(emp_salsry)
fromemployee;
从选定的VALUE中选出数值/字符的最小值,忽略空值。
VALUE要求同上。
stddev(value)返回雇员薪水的标准偏差
selectSTDDEV(emp_salary)
fromemployee;
从选择的VALUE的标准中返回标准偏差。
variance([distinct]value)
返回雇员薪水的方差
selectVARIANCE(emp_salary)
fromemployee;
返回所选行的所有数值的方差,忽略VALUE的空值。
DISTINCT和VALUE要求同前。
1.没有groupby子句的groupby函数。
注意到上面的所有示例都没有使用GROUPBY命令了吧?
当在查询中没有使用GROUPBY子句时,数据库就把数据表中的所有行为作为一个组来处理。
通常情况下,我们并不希望是这样的。
多数用户都能理解组的概念,但在使用的时候却很快就陷入麻烦中。
有一个技巧是:
如果有一列在GROUPBY子句中没有提到,就必须对它进行累计。
换句话说,就是必须对那些不包括在GROUPBY子句中的所有列使用上面提到过的一个函数。
2.having子句。
就象使用where子句检查从查询返回的个别行一样,我们也可以使用having子句为一组行指定搜索条件。
例如,如果想要察看新雇佣人数超过两个的州的平均工资,就可以使用having子句。
查询语句如下所示:
selectstate_cd,avg(salsry)
fromnewhire
groupbystate_cd
havingcount(state_cd)>2;
3.可以使用groupby查找重复数据。
4.可以使用groupby删除重复数据。
三、模糊查询
在进行数据库查询时,有完整查询和模糊查询之分。
一般模糊查询语句如下:
SELECT字段FROM表WHERE某字段Like条件
其中关于条件,SQL提供了四种匹配模式:
1,%:
表示任意0个或多个字符。
可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
比如
SELECT*FROM[user]WHEREu_nameLIKE'%三%'
将会把u_name为“张三”,“张猫三”、“三脚猫”,“唐三藏”等等有“三”的记录全找出来。
另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件
SELECT*FROM[user]WHEREu_nameLIKE'%三%'ANDu_nameLIKE'%猫%'
若使用
SELECT*FROM[user]WHEREu_nameLIKE'%三%猫%'
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。
2,_:
表示任意单个字符。
匹配单个任意字符,它常用来限制表达式的字符长度语句,比如
SELECT*FROM[user]WHEREu_nameLIKE'_三_'
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
再比如
SELECT*FROM[user]WHEREu_nameLIKE'三__';
只找出“三脚猫”这样name为三个字且第一个字是“三”的;
3,[]:
表示括号内所列字符中的一个(类似正则表达式)。
指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
比如
SELECT*FROM[user]WHEREu_nameLIKE'[张李王]三'
将找出“张三”、“李三”、“王三”(而不是“张李王三”);
如[]内有一系列字符(01234、abcde之类的)则可略写为“0-4”、“a-e”
SELECT*FROM[user]WHEREu_nameLIKE'老[1-9]'
将找出“老1”、“老2”、……、“老9”;
4,[^]:
表示不在括号所列之内的单个字符。
其取值和[]相同,但它要求所匹配对象为指定字符以外的任一个字符。
比如
SELECT*FROM[user]WHEREu_nameLIKE'[^张李王]三'
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等;
SELECT*FROM[user]WHEREu_nameLIKE'老[^1-4]';
将排除“老1”到“老4”,寻找“老5”、“老6”、……
5,查询内容包含通配符时
由于通配符的缘故,导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现,而把特殊字符用“[]”括起便可正常查询。
据此我们写出以下函数:
functionsqlencode(str)
str=replace(str,"[","[[]")'此句一定要在最前
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
endfunction
在查询前将待查字符串先经该函数处理即可,并且在网页上连接数据库用到这类的查询语句时侯要注意:
如Select*FROMuserWherenameLIKE'老[^1-4]';上面《'》老[^1-4]《'》是要有单引号的。
SQL模糊查询,使用like比较关键字,加上SQL里的通配符,请参考以下:
1、LIKE'Mc%'将搜索以字母Mc开头的所有字符串(如McBadden)。
2、LIKE'%inger'将搜索以字母inger结尾的所有字符串(如Ringer、Stringer)。
3、LIKE'%en%'将搜索在任何位置包含字母en的所有字符串(如Bennet、Green、McBadden)。
4、LIKE'_heryl'将搜索以字母heryl结尾的所有六个字母的名称(如Cheryl、Sheryl)。
5、LIKE'[CK]ars[eo]n'将搜索下列字符串:
Carsen、Karsen、Carson和Karson(如Carson)。
6、LIKE'[M-Z]inger'将搜索以字符串inger结尾、以从M到Z的任何单个字母开头的所有名称(如Ringer)。
7、LIKE'M[^c]%'将搜索以字母M开头,并且第二个字母不是c的所有名称(如MacFeather)。
四、联合查询
使用联合查询合并多个选择查询的结果
对于多个相似的选择查询,当您希望将它们返回的所有数据一起作为一个合并的集合查看时,便可以使用联合查询。
本文将介绍如何根据两个或多个现有的选择查询创建联合查询,同时说明如何使用结构化查询语言(SQL)编写联合查询。
1、联合查询基础知识
联合查询有哪些功能?
联合查询可合并多个相似的选择查询的结果集。
例如,假设您有两个表,一个用于存储有关客户的信息,另一个用于存储有关供应商的信息,并且这两个表之间不存在任何关系。
又假设这两个表都有一些存储联系人信息的字段,而您希望同时查看这两个表中的所有联系人信息。
您可以为每个表都创建一个选择查询(选择查询:
就表中存储的数据提出问题,然后在不更改数据的情况下以数据表的形式返回一个结果集。
),以便只检索包含联系人信息的那些字段,但返回的信息仍将位于两个单独的位置。
要将两个或多个选择查询的结果合并到一个结果集中,可以使用联合查询。
联合查询的要求
联合查询中合并的选择查询必须具有相同的输出字段数、采用相同的顺序并包含相同或兼容的数据类型。
在运行联合查询时,来自每组相应字段中的数据将合并到一个输出字段中,这样查询输出所包含的字段数将与每个Select语句相同。
注释 根据联合查询的目的,“数字”和“文本”数据类型兼容。
联合查询是特定于SQL的。
特定于SQL的查询不能在“设计”视图中显示,因此必须直接用SQL编写。
在MicrosoftOfficeAccess2007中,您可以使用“SQL视图”对象选项卡编写特定于SQL的查询,包括联合查询。
提示 每个查询都可以用SQL语句来表达。
此外,大多数查询也可以在查询设计网格(设计网格:
在查询设计视图或“高级筛选/排序”窗口中设计查询或筛选时所用的网格。
对于查询,该网格以前称为“QBE网格”。
)中表达,如果此环境可用,构建查询将更加轻松。
在使用设计网格创建查询时,您始终都可以切换到SQL视图,以查看运行查询时所处理的SQL语句。
在SQL视图中查看查询是一种很好的做法,因为您可以通过此途径熟悉SQL并加深对查询工作方式的理解。
在某些情况下,您还可以使用SQL视图对未返回预期结果的查询进行故障排除。
联合查询的SQL语法
在联合查询中,每个选择查询(又称为Select语句)都有一个SELECT子句和FROM子句,还可能有WHERE子句。
SELECT子句列出包含要检索的数据的字段;FROM子句列出包含这些字段的表;WHERE子句则列出这些字段的条件。
联合查询中的Select语句用UNION关键字组合在一起。
对于合并了两个选择查询的联合查询,其基本SQL语法如下:
SELECTfield_1[,field_2,…]
FROMtable_1[,table_2,…]
UNION[ALL]
SELECTfield_a[,field_b,...]
FROMtable_a[,table_b,…];
例如,假设您有两个表,分别名为Products和Services。
这两个表都具有包含下列内容的字段:
产品或服务的名称、价格、保修或担保条款以及是否以独占方式提供产品或服务。
虽然Products表存储了保修信息,而Services表存储了担保信息,但基本信息是相同的(即特定的产品或服务是否符合其质量承诺)。
您可以使用联合查询将两个表中的这四个字段合并在一起,例如:
SELECTname,price,warranty_available,exclusive_offer
FROMProducts
UNIONALL
SELECTname,price,guarantee_available,exclusive_offer
FROMServices;
让我们逐行检查上面的语法示例。
SELECTname,price,warranty_available,exclusive_offer 这是一个SELECT子句,用于引入选择查询。
SELECT后面跟有一个标识符列表,用于指示要从中检索数据的字段。
SELECT子句必须始终至少列出一个字段。
此SELECT子句列出了字段标识符name、price、warranty_available和exclusive_offer。
FROMProducts 这是一个FROM子句。
FROM子句跟在SELECT子句之后,二者共同构成了一个基本的Select语句。
FROM后也跟有一个标识符列表,用于指示哪些表包含SELECT子句中列出的字段。
FROM子句必须始终至少列出一个表。
此FROM子句列出了表标识符Products。
UNIONALL 这是一个UNION关键字以及一个可选的ALL关键字。
UNION指示将UNION前后的SELECT语句的结果合并在一起。
在使用ALL关键字时,Union生成的合并集中并不删除重复行。
这样,Access便无需检查重复行的结果,从而可以显著提高查询的性能。
如果满足下列任一条件,则应使用ALL关键字:
您确定选择查询不会生成任何重复行。
结果中是否存在重复行无关紧要。
您希望查看重复行。
在本示例中,我们使用ALL关键字是因为我们既不期望会返回重复行,也没有略去它们的必要。
SELECTname,price,guarantee_available,exclusive_offer 这是第二个SELECT子句,用于引入联合查询中的第二个SELECT语句。
在编写联合查询时,各个SELECT语句中的字段必须相互对应,这意味着各个SELECT语句必须具有相同的字段数,并且共享通用数据的字段必须以相同的顺序出现在子句中,同时这些字段必须具有相同或兼容的数据类型,如示例中所示。
只有这些字段相互对应,才能在查询输出中将它们合并在一起。
注释 联合查询输出中的字段名称从第一个SELECT子句中提取。
因此,在本示例的查询输出中,来自字段“warranty_available”和“guarantee_available”的数据将被命名为“warranty_available”。
FROMServices 这是第二个FROM子句,用于完成联合查询中的第二个SELECT语句。
与SELECT子句中的字段不同,联合查询对FROM子句没有表限制。
您既可以创建在每个FROM子句中都使用相同的表的联合查询,也可以在FROM子句中使用不同数目的表。
在我们的示例中,每个FROM子句都只有一个表。
2、创建联合查询
创建联合查询有两种基本方法:
先在查询“设计”视图(设计视图:
显示数据库对象(包括表、查询、窗体、报表和宏)的设计的视图。
在设计视图中,可以创建新的数据库对象以及修改现有对象的设计。
)中创建各个组件选择查询,然后将这些查询合并为一个联合查询。
直接在SQL视图(SQL视图:
用于显示当前查询的SQL语句或用于创建SQL特定查询(联合查询、传递查询或数据定义查询)的对象标签。
在“设计”视图中创建查询时,会在SQL视图中构建SQL的等价查询。
)中创建整个联合查询。
在大多数情况下,您都应该先创建选择查询,然后再将它们合并为一个联合查询。
在OfficeAccess2007中,“设计”视图提供了一种易用的创建选择查询的图形用户界面,您可以复制这些查询的SQL语句并将它们粘贴到联合查询中。
但是,如果您认为编写SQL语句得心应手,或者希望获得更多的SQL编写体验,则可能更倾向于直接在SQL视图中创建联合查询。
您要做什么?
先在“设计”视图中创建选择查询,然后合并它们
直接在SQL视图中创建联合查询
先在“设计”视图中创建选择查询,然后合并它们
按照此方法,将先使用“设计”视图创建每个选择查询,然后使用SQL视图合并选择查询。
在“设计”视图中创建每个选择查询
在“创建”选项卡上的“其他”组中,单击“查询设计”。
在“显示表”对话框中,双击要包括的字段所在的表。
这会将该表添加到查询设计窗口。
注释 虽然您可以在选择查询中包括多个表或查询,但此过程假定每个选择查询只包括一个表中的数据。
关闭“显示表”对话框。
在查询设计窗口中,双击要包括的每个字段。
选择字段时,请确保您在其他选择查询中以相同顺序添加了相同数目的字段。
另外,请注意各个字段的数据类型,确保在要合并的其他查询中,处于相应位置的字段具有兼容的数据类型。
例如,如果第一个选择查询具有五个字段,且第一个字段包含“日期/时间”数据,请确保要合并的其他每个选择查询也具有五个字段,并且第一个字段同样包含“日期/时间”数据,依此类推。
另外,您还可以在字段网格的“条件”行中键入适当的表达式(表达式:
算术或逻辑运算符、常数、函数和字段名称、控件和属性的任意组合,计算结果为单个值。
表达式可执行计算、操作字符或测试数据。
),以此向字段中添加条件。
在添加完字段和字段条件后,应运行选择查询并查看其输出。
在“设计”选项卡上的“结果”组中,单击“运行”。
将查询切换到“设计”视图。
保存该选择查询,但不要将其关闭。
对于要合并的每个选择查询,请重复此过程。
在SQL视图中合并选择查询
在“创建”选项卡上的“其他”组中,单击“查询设计”。
在“设计”视图中打开一个新查询。
关闭“显示表”对话框。
在“设计”选项卡上的“查询”组中,单击“联合”。
将隐藏查询设计窗口,并显示SQL视图对象选项卡。
此时,SQL视图对
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 操作系统 上机 报告