数据库实验报告一SQL查询.doc

(1) 查询员工的姓名、职务和薪水。select employeename,employeeno,salaryfrom Employee(2) 查询名字中含有“有限”的客户名称和所在地。select customerno,addressfrom customerwhere customername like %有限%(3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。select *from employeewhere employeename like 张_梅(4) 查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职称、住址、出生日期和性别，其中如果出生日期为空，显示“不详”，否则按格式“yyyy-mm-dd”显示，性别用“男”和“女”显示。select employeename,department,headship,address,birthday=case birthday when null then不详 end,sex=case sex when Mthen 男when Fthen 女 endfrom employeewhere address like %上海% or address like %南昌%(5) 查询出职务为“职员”或职务为“科长”的女员工的信息。select *from employeewhere headship like 职员 or headship like 科长(6) 选取编号不在C20050001C20050004之间的客户编号、客户名称、客户地址。select customerno,customername,addressfrom customerwhere customerno not between C20050001 and C20050004(7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。先统计订单主表中的订单金额，使用命令：update OrderMaster set orderSum=sum2from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) bwhere a.orderNo=b.orderNoselect a.orderno,b.sum2from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) bwhere a.orderNo=b.orderNo and b.sum2=5000(8) 选取订单金额最高的前10%的订单数据。select top 10 percent a.orderno,b.sum2from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) bwhere a.orderNo=b.orderNo(9) 计算出一共销售了几种商品。select count(*)商品总数from(select ordernofrom orderdetail group by orderno)a(10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额，并且依据销售金额由大到小排序输出。select productno,sum(quantity)销售数量,avg(price)平均价格,sum(quantity*price)销售金额from orderdetailgroup by productnoorder by sum(quantity*price) desc(11) 按客户编号统计每个客户2008年2月的订单总金额。select customerno,sum(ordersum)from ordermasterwhere year(orderdate)=2008 and month(orderdate)=2group by customerno(12) 统计至少销售了10件以上的商品编号和销售数量。select productno,sum(quantity)sumquantityfrom orderdetailgroup by productnohaving sum(quantity)10(13) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。select year(birthday),count(*),avg(salary)from employeewhere year(birthday)=1973 or year(birthday)=1967group by year(birthday)(14) 实验问题： 给出SQL语句实现分组聚集操作的执行过程。1、 先执行form 语句中的表目连接（笛卡尔积）2、 Where 语句中的选择保留满足条件的分组3、 Group by 将满足条件的语句进行聚合4、 Having 语句在聚合后的分组中进行条件筛选5、 Order by 对满足条件的分组进行排列6、 Select 语句最后对表目中的属性选择行输出 WHERE和HAVING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。Where是对分组进行初步的筛选找出符合条件的分组（不能包含聚合函数）Having 对最终满足条件的分组进行选择一般会用到聚合函数（sum count avg 等） 在分组聚集操作中，为什么在查询列中，除了集聚函数运算，其它表达式必须包含在GROUP BY子句中。其实我也纳闷为什么有时候在group by 中只要一个属性就能唯一标识一个聚合后的分组，而在select中可能要查询多属性时group by 中要把select中出聚合函数外所有函数运算都包含进去，如果不这样就会报错这可能是硬性规定或当初设计就是这样 分析条件BETWEEN . AND、AND、OR等关键字的使用方法。这些语句都在where 中使用BETWEEN AND 是区间选择一个分组的某个属性值在这个区间就满足AND 一个分组必须同时满足这些选择条件OR 一个分组只要满足其中的一个条件就可以 请总结SQL语句中的单表查询语句的使用方法。先执行form 语句中的表目连接（笛卡尔积）Where 语句中的选择保留满足条件的分组 （不能包含聚合函数）Group by 将满足条件的语句进行聚合（必须包含select语句中出聚合函数外的所有函数运算）Having 语句用聚合函数在分组中进行条件筛选（如：sum avg count 等）Order by 对满足条件的分组进行排列（desc 由大到小）Select 语句最后对表目中的属性选择行输出(1) 找出同一天进入公司服务的员工。select a.employeeno,a.employeename,a.sex,a.birthday,address,a.telephone,a.hiredate,a.department,a.headship,a.salaryfrom employee a,(select hiredate from employeegroup by hiredatehaving count(*)=2)bwhere a.hiredate=b.hiredate(2) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。select b.employeename,b.sex,b.department,b.headshipfrom employee a,employee bwhere a.employeename like 陈诗杰and a.department=b.department(3) 在Employee表中查询薪水超过员工平均薪水的员工信息。select * from employee a,(select avg(salary)avgsalary from employee)bwhere a.salaryb.avgsalary(4) 查找有销售记录的客户编号、名称和订单总额。select a.customerno,a.customername,sum(b.ordersum)from customer a,ordermaster bwhere a.customerno=b.customernogroup by a.customerno,a.customernamehaving sum(b.ordersum)0(5) 查询没有订购商品的客户编号和客户名称。select a.customerno,a.customernamefrom customer awhere a.customerno not in(select customerno from ordermaster group by customerno )(6) 使用子查询查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。select a.employeename,sex=case a.sex when Fthen 女 when M then 男 end,b.orderdate,c.quantity,c.price*c.quantity 金额from employee a,ordermaster b,orderdetail c,product dwhere a.employeeno=b.salerno and b.orderno=c.orderno and ductno=ductno and ductname like 32M DRAM(7) 查询OrderMaster表中订单金额最高的订单号及订单金额。select a.orderno,a.ordersumfrom ordermaster a, (select max(ordersum)maxordersum from ordermaster)bwhere a.ordersum=b.maxordersum(8) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。select a.orderno,a.customerno,a.salerno,a.orderdate,a.ordersum,a.invoicenofrom ordermaster a,(select min(b.ordersum)minorder from ordermaster bwhere b.salerno like E2005002 and year(b.orderdate)=2008 and month(b.orderdate)=1 and day(b.orderdate)=9)cwhere a.ordersumc.minorder(9) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。select ductno,ductname,sum(b.quantity)订单数量,b.pricefrom product a,orderdetail bwhere ductprice400 and ductno=ductnogroup by ductno,ductname,b.price(10) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。左外连接select ductno,ductname,sum(b.quantity)订单数量,b.pricefrom product a left outer join orderdetail b on ductprice400 and ductno=ductnogroup by ductno,ductname,b.price右外连接select ductno,ductname,sum(b.quantity)订单数量,b.pricefrom product a right outer join orderdetail b on ductprice400 and ductno=ductnogroup by ductno,ductname,b.price完整外部连接select ductno,ductname,sum(b.quantity)订单数量,b.pricefrom product a full outer join orderdetail b on ductprice400 and ductno=ductnogroup by ductno,ductname,b.price(11) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。Selecta.customerno,customername,orderdate=isnull(convert(char(10),orderdate,120),NULL),ordersumfrom customer a left outer join ordermaster b on a.customerno=b.customernoorder by a.customerno,ordersum desc函数isnull: 第一个参数是convert(char(10),birthday,120),第二个参数是NULL 作用: 若第一个参数为null,则返回结果为第二个参数,不为null则返回第一个参数.再看函数 convert ,他又三个参数.作用是将日期型转换成字符串型.第一个参数: char(10) 表示转换结果的类型及长度.第二个参数: birthday 表示要需要被转换的日期型变量或字段第三个参数: 120 表示转换结果的表示格式.其中第三个参数120也可以是其他数值:style数字在转换时间时的含义如下:-Style(2位表示年份) | Style(4位表示年份) | 输入输出格式 -0 | 100 | mon dd yyyy hh:miAM(或PM) -1 | 101 美国 | mm/dd/yy -2 | 102 ANSI | yy-mm-dd -3 | 103 英法 | dd/mm/yy -4 | 104 德国 | dd.mm.yy -5 | 105 意大利 | dd-mm-yy -6 | 106 | dd mon yy -7 | 107 | mon dd,yy -8 | 108 | hh:mm:ss -9 | 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)-10 | 110 美国 | mm-dd-yy -11 | 111 日本 | yy/mm/dd -12 | 112 ISO | yymmdd -13 | 113 欧洲默认值 | dd mon yyyy hh:mi:ss:mmm(24小时制) -14 | 114 | hh:mi:ss:mmm(24小时制) -20 | 120 ODBC 规范 | yyyy-mm-dd hh:mi:ss(24小时制) -21 | 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制) -(12) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。select a.employeeno,a.employeename,sex=case a.sex when Mthen 男 when F then 女end,ductname,d.quantity,d.price,d.quantity*d.price 金额,orderdate=isnull(convert(char(10),c.orderdate,120), )from employee a,product b,ordermaster c,orderdetail dwhere a.employeeno=c.salerno and ductno=ductno and c.orderno=d.orderno(13) 查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。(题中的“16M DRAM”在Product 表没有该商品 只有“32M DRAM”)select a.employeename,sex=case a.sex when F then 女 when M then 男 end,c.orderdate,d.quantity,d.quantity*d.pricefrom employee a,product b,ordermaster c,orderdetail dwhere a.employeeno=c.salerno and ductno=ductno and c.orderno=d.orderno and ductname like 32M DRAM(14) 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。 select b.orderno,b.ordersumfrom employee a,ordermaster bwhere a.employeeno=b.salerno and a.sex like M and b.ordersum2000(15) 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。select a.customerno,sum(b.ordersum)订购金额from customer a left outer join ordermaster b on a.customerno=b.customernogroup by a.customernoorder by sum(b.ordersum)desc(16) 查询每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。select ductno 商品编号,ductname 商品名称,sum(b.quantity) 总数量,sum(b.quantity*price) 总金额from product a,orderdetail bwhere ductno=ductnogroup by ductno,ductnameorder by ductno (17) 实验问题： 连接操作类型有哪些？分析外连接在现实应用中的意义。1、 自表连接2、 外表连接1） 左外连接2） 右外连接3） 完整外部连接外表连接的意义：当两表中一个表的属性在另一个表中没有属性是也能显示出来 例：使用左外连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价左select ductno,ductname,sum(b.quantity)订单数量,b.pricefrom product a left outer join orderdetail b on ductprice400 and ductno=ductnogroup by ductno,ductname,b.price可查询出没有销售但是存在一表product中的商品实际生活中可以更为直观的了解信息 查询表可以用在什么地方？使用查询表要注意哪些地方？用在各种信息的管理中（如学校、商场、公司等）查询条件的完整和完备性 分析SQL语句中的IN和OR关键字有何异同点？它们可以互换吗？给出实例说明。1异同：相同：1、 IN 和OR 都是范围选择某个元组的某个属性是否满足期中范围值之一2、 IN 和OR 都在where 中使用差异：IN 是用于子查询即：IN 包含完整的查询（select from ）其结果是一个表含有多个元组OR 直接用于where中只有某个属性的一个值2 互换：很明显两个不可能互换IN 使用格式：where 属性 IN（子查询）OR使用格式：where 条件（属性运算符号属性）or条件（属性运算符号属性） 分析哪几种情况需要使用自表连接。1、同一个表中同一属性的一个值存在多个元组查询多个元组是否满足条件如：学生成绩表中查询既选修了某门课程又选修了某门课程的学生属性值为学生学号，同一学号存在多个元组2、查询表中与表中某个元组的的某个属性相同时如：在员工信息emplpyee表中查询与某个员工部门相同的员工时(1) 在订单明细表中查询订单金额最高的订单。select *from orderdetail a,(select max(quantity*price) maxmoney from orderdetail )bwhere a.quantity*a.price=b.maxmoney(2) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。select productno,orderno,quantity,quantity*price 金额from orderdetailwhere productno in(select productnofrom orderdetailgroup by productnohaving count(*)=3)order by quantity desc(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。select a.employeeno,a.employeename,sum(b.ordersum)from employee a,ordermaster bwhere a.employeeno=b.salernogroup by employeeno,employeenamehaving sum(ordersum)5000(4) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。select a.employeeno,a.employeename,sum(b.ordersum)业绩from employee a,ordermaster bwhere a.employeeno=b.salernogroup by employeeno,employeenamehaving sum(ordersum)5000order by sum(ordersum)desc(5) 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。select customerno,customernamefrom customerwhere customerno in(select a.customernofrom customer a,ordermaster b,orderdetail cwhere a.customerno=b.customerno and b.orderno=c.ordernogroup by a.customernohaving sum(c.quantity)=3)(8) 找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。select salerno,sum(ordersum)业绩from ordermastergroup by salernohaving sum(ordersum)4000order by sum(ordersum)desc(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select a.customerno,ductno,sum(b.quantity)sumquantity,sum(b.quantity*b.price)/sum(b.quantity)单价from ordermaster a,orderdetail bwhere a.orderno=b.ordernogroup by a.customerno,ductnoorder by a.customerno,ductno(10) 查询业绩最好的的业务员号、业务员名及其总销售金额。select a.employeeno,a.employeename,sum(ordersum)sumorderfrom employee a,ordermaster bwhere a.employeeno=b.salernogroup by a.employeeno,a.employeenamehaving sum(ordersum)=(select max(c.sumorder) from (select salerno,sum(ordersum)sumorder from ordermaster group by salerno )c)(11) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。select *from ordermaster xwhere not exists(select ductno from ordermaster a,orderdetail b where a.orderno=b.orderno and a.orderno like 200803010001 and not exists(select * from ordermaster d,orderdetail e where d.orderno=e.orderno and x.orderno=d.orderno and ductno=ductno)(12) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。select a.customerno,a.customername,a.address,sum(b.ordersum)ordersumfrom customer a,ordermaster b,(select x.customerno customerno,sum(y.ordersum) ordersum from customer x,ordermaster y where x.customerno=y.customernogroup by x.customerno )cwhere a.customerno=b.customerno and c.customerno=C20070002 group by a.customerno,a.customername,a.address,c.ordersumhaving sum(b.ordersum)c.ordersum(13) 查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。select a.customerno,a.customername,a.address,sum(b.ordersum)ordersumfrom customer a,ordermaster bwhere a.customerno=b.customernogroup by a.customerno,a.customername,a.addresshaving sum(b.ordersum)=(select max(ordersum) from(select x.customerno customerno,sum(y.ordersum) ordersum from customer x,ordermaster y where x.customerno=y.customernogroup by x.customerno )c)(14) 用存在量词查找没有订货记录的客户名称。select customernamefrom customer awhere not exists(select x.customerno customerno from customer x,ordermaster y where x.customerno=y.customerno and a.customerno=x.cust