数据库实验

From Stained Owl, 3 Years ago, written in Plain Text, viewed 296 times.
URL http://axuhongbo.top/paste/view/8c67a0ce Embed
Download Paste or View Raw
  1. create table sc
  2. (
  3. sno varchar(10),
  4. cno varchar(7),
  5. grade float,
  6. primary key(sno,cno),
  7. foreign key(sno) references stu(sno),
  8. foreign key(cno) references course(cno)
  9. )
  10.  
  11.  
  12. 1、首先在MySQL中创建一个数据库
  13. create database pcshop;
  14. 2、使用数据库
  15. use pcshop;
  16. 3、导入文件
  17. source d:\pcshop.sql
  18.  
  19.  
  20.  
  21. 1、pcshop运行到MySQL中
  22.  
  23. --单表查询,最简单的查询
  24. 2、进行单表查询
  25. 1)查询所有speed大于2.8的PC信息
  26. select * from pcs where speed>2.8
  27.  
  28. 2)查询购买model为1007的购买记录信息
  29.  
  30. select * from sales where model='1007'
  31. 3)统计2013-12-20购买记录的数量(count)
  32. 聚集函数
  33. select count(*) from sales where day='2013-12-20'
  34. 4)统计2013-12-20购买的总数量(sum)
  35. select model,count(*),max(quantity),sum(quantity) from sales
  36. group by model
  37.  
  38.  
  39.  
  40.  
  41.  
  42. a) What PC models have a speed of at least 3.00?
  43. select * from pcs where speed>=3.0
  44. b) Which makers make laptops with a hard disk of
  45. at least 100 GB
  46. --1、做等值连接
  47. select maker
  48. from products,laptops
  49. where products.model=laptops.model and hd>=100
  50. --2、自然连接
  51. select maker
  52. from products natural join laptops
  53. where hd>=100
  54. --3、内连接
  55. select maker
  56. from products  join laptops
  57. on products.model=laptops.model
  58. where hd>=100
  59. --4、自然连接
  60. select maker
  61. from products  join laptops
  62. using (model)
  63. where hd>=100
  64. --5、子查询实现
  65. --不相关子查询(子查询的SQL语句可以单独执行)
  66. select maker
  67. from products
  68. where model in (
  69. select model from laptops where hd>=100
  70. )
  71. --相关子查询(子查询的SQL语句不能够单独执行,
  72. 必须嵌在父查询中,作为父查询的条件执行)
  73. select maker
  74. from products
  75. where exists (
  76. select * from laptops where model=products.model and  hd>=100
  77. )
  78. --所有子查询都可以写成相关子查询
  79. --有些相关子查询可以用不相关子查询来代替
  80. --所有不相关子查询都可以用相关子查询来代替
  81.  
  82.  
  83. c) Find the model number and price of all products
  84. (of any type) made by maker B
  85. select model,price
  86. from
  87. (
  88. select model,price
  89. from pcs
  90. union
  91. select model,price
  92. from laptops
  93. union
  94. select model,price
  95. from printers
  96. ) allType
  97.  
  98. natural join products
  99. where maker='B'
  100.  
  101.  
  102.  
  103. d) Find the model numbers of all color laser printers
  104.  
  105. select model from printers where color='TRUE' and type='laser'
  106. e) Find those makers that sell Laptops but not PCs
  107. select distinct maker
  108. from products
  109. where type='laptop' and maker not in (
  110. select maker from products where type='pc'
  111. )
  112.  
  113. f) Find those hard-disk sizes that occur in two or more PCs
  114. select distinct hd
  115. from pcs
  116. group by hd
  117. having count(hd)>=2
  118.  
  119.  
  120. a)Find those pairs of PC models that
  121. have both the same speed and ram.
  122. A pair should be listed only once;
  123. e.g., list (i, j) but not (j, i).
  124. --典型的表的自身连接查询
  125. select a.model,b.model
  126. from pcs a,pcs b
  127. where a.speed=b.speed and a.ram=b.ram
  128. and a.model<b.model
  129.  
  130.  
  131.  
  132.  
  133. b)Find the makers who sell exactly three
  134. different models of PC
  135.  
  136. select distinct maker
  137. from products
  138. where type='pc'
  139. group by maker
  140. having count(*)>=3
  141.  
  142.  
  143. c)Find the makers of PCs with at least
  144. three different speeds
  145. select distinct maker
  146. from products natural join pcs
  147. group by maker
  148. having count(speed)>=3
  149.  
  150.  
  151. d)Find those makers of at least two
  152. different computers (PCs or laptops)
  153. with speeds of at least 2.80
  154. --想让大家用视图解决问题
  155. --from子句中应用别名方式给子查询命名名称也可以
  156. select distinct maker
  157. from products natural join
  158. (
  159. select model,speed from pcs where speed>=2.8
  160. union select model,speed from laptops where speed>=2.8
  161. ) atleastSpeed
  162. group by maker
  163. having count(*)>=2
  164.  
  165.  
  166.  
  167. e)Find the maker(s) of the computer
  168. (PC or laptop) with the highest available speed
  169.  
  170. --想让大家用视图解决问题
  171.  
  172. create view v_pcandlaptop(model,speed)
  173. as
  174. select model,speed from pcs
  175. union select model,speed from laptops
  176.  
  177. select distinct maker
  178. from products
  179. where model in (
  180. select model from
  181. (
  182. select model,speed from pcs
  183. union select model,speed from laptops
  184. ) atleastSpeed where atleastSpeed.speed>=all(
  185. select speed from pcs
  186. union select speed from laptops
  187. )
  188. )
  189.  
  190.  
  191.  
  192. select distinct maker
  193. from products
  194. where model in (
  195. select model from
  196. v_pcandlaptop where speed>=(select max(speed) from v_pcandlaptop)
  197. )
  198.  
  199.  

Reply to "数据库实验"

Here you can reply to the paste above

captcha