数据库实验

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

create table sc
(
sno varchar(10),
cno varchar(7),
grade float,
primary key(sno,cno),
foreign key(sno) references stu(sno),
foreign key(cno) references course(cno)
)
 
 
1、首先在MySQL中创建一个数据库
create database pcshop;
2、使用数据库
use pcshop;
3、导入文件
source d:\pcshop.sql
 
 
 
1、pcshop运行到MySQL中
 
--单表查询，最简单的查询
2、进行单表查询
1)查询所有speed大于2.8的PC信息
select * from pcs where speed>2.8
 
2)查询购买model为1007的购买记录信息
 
select * from sales where model='1007'
3)统计2013-12-20购买记录的数量（count）
聚集函数
select count(*) from sales where day='2013-12-20'
4)统计2013-12-20购买的总数量(sum)
select model,count(*),max(quantity),sum(quantity) from sales
group by model
 
 
 
 
 
a) What PC models have a speed of at least 3.00?
select * from pcs where speed>=3.0
b) Which makers make laptops with a hard disk of 
at least 100 GB
--1、做等值连接
select maker
from products,laptops
where products.model=laptops.model and hd>=100
--2、自然连接
select maker
from products natural join laptops
where hd>=100
--3、内连接
select maker
from products  join laptops
on products.model=laptops.model
where hd>=100
--4、自然连接
select maker
from products  join laptops
using (model)
where hd>=100
--5、子查询实现
--不相关子查询（子查询的SQL语句可以单独执行）
select maker
from products
where model in (
select model from laptops where hd>=100
)
--相关子查询（子查询的SQL语句不能够单独执行，
必须嵌在父查询中，作为父查询的条件执行）
select maker
from products
where exists (
select * from laptops where model=products.model and  hd>=100
)
--所有子查询都可以写成相关子查询
--有些相关子查询可以用不相关子查询来代替
--所有不相关子查询都可以用相关子查询来代替
 
 
c) Find the model number and price of all products 
(of any type) made by maker B
select model,price
from 
(
select model,price
from pcs
union
select model,price
from laptops
union
select model,price
from printers
) allType 
 
natural join products
where maker='B'
 
 
 
d) Find the model numbers of all color laser printers
 
select model from printers where color='TRUE' and type='laser'
e) Find those makers that sell Laptops but not PCs
select distinct maker
from products
where type='laptop' and maker not in (
select maker from products where type='pc'
)
 
f) Find those hard-disk sizes that occur in two or more PCs
select distinct hd
from pcs
group by hd
having count(hd)>=2
 
 
a)Find those pairs of PC models that 
have both the same speed and ram. 
A pair should be listed only once; 
e.g., list (i, j) but not (j, i).
--典型的表的自身连接查询
select a.model,b.model
from pcs a,pcs b
where a.speed=b.speed and a.ram=b.ram
and a.model<b.model
 
 
 
 
b)Find the makers who sell exactly three 
different models of PC
 
select distinct maker
from products
where type='pc'
group by maker
having count(*)>=3
 
 
c)Find the makers of PCs with at least 
three different speeds
select distinct maker
from products natural join pcs
group by maker
having count(speed)>=3
 
 
d)Find those makers of at least two 
different computers (PCs or laptops) 
with speeds of at least 2.80
--想让大家用视图解决问题
--from子句中应用别名方式给子查询命名名称也可以
select distinct maker
from products natural join 
(
select model,speed from pcs where speed>=2.8
union select model,speed from laptops where speed>=2.8
) atleastSpeed
group by maker
having count(*)>=2
 
 
 
e)Find the maker(s) of the computer 
(PC or laptop) with the highest available speed
 
--想让大家用视图解决问题
 
create view v_pcandlaptop(model,speed)
as
select model,speed from pcs
union select model,speed from laptops
 
select distinct maker
from products
where model in (
select model from
(
select model,speed from pcs
union select model,speed from laptops
) atleastSpeed where atleastSpeed.speed>=all(
select speed from pcs
union select speed from laptops
)
)
 
 
 
select distinct maker
from products
where model in (
select model from
v_pcandlaptop where speed>=(select max(speed) from v_pcandlaptop)
)
 
 

Reply to "数据库实验"

Here you can reply to the paste above

Author What's your name?

Title Give your paste a title.

Language What language is your paste written in?

Your paste Paste your paste here

create table sc
(
sno varchar(10),
cno varchar(7),
grade float,
primary key(sno,cno),
foreign key(sno) references stu(sno),
foreign key(cno) references course(cno)
)

1、首先在MySQL中创建一个数据库
create database pcshop;
2、使用数据库
use pcshop;
3、导入文件
source d:\pcshop.sql

1、pcshop运行到MySQL中

--单表查询，最简单的查询
2、进行单表查询
1)查询所有speed大于2.8的PC信息
select * from pcs where speed>2.8

2)查询购买model为1007的购买记录信息

select * from sales where model='1007'
3)统计2013-12-20购买记录的数量（count）
聚集函数
select count(*) from sales where day='2013-12-20'
4)统计2013-12-20购买的总数量(sum)
select model,count(*),max(quantity),sum(quantity) from sales
group by model

a) What PC models have a speed of at least 3.00?
select * from pcs where speed>=3.0
b) Which makers make laptops with a hard disk of 
at least 100 GB
--1、做等值连接
select maker
from products,laptops
where products.model=laptops.model and hd>=100
--2、自然连接
select maker
from products natural join laptops
where hd>=100
--3、内连接
select maker
from products  join laptops
on products.model=laptops.model
where hd>=100
--4、自然连接
select maker
from products  join laptops
using (model)
where hd>=100
--5、子查询实现
--不相关子查询（子查询的SQL语句可以单独执行）
select maker
from products
where model in (
select model from laptops where hd>=100
)
--相关子查询（子查询的SQL语句不能够单独执行，
必须嵌在父查询中，作为父查询的条件执行）
select maker
from products
where exists (
select * from laptops where model=products.model and  hd>=100
)
--所有子查询都可以写成相关子查询
--有些相关子查询可以用不相关子查询来代替
--所有不相关子查询都可以用相关子查询来代替

c) Find the model number and price of all products 
(of any type) made by maker B
select model,price
from 
(
select model,price
from pcs
union
select model,price
from laptops
union
select model,price
from printers
) allType

natural join products
where maker='B'

d) Find the model numbers of all color laser printers

select model from printers where color='TRUE' and type='laser'
e) Find those makers that sell Laptops but not PCs
select distinct maker
from products
where type='laptop' and maker not in (
select maker from products where type='pc'
)

f) Find those hard-disk sizes that occur in two or more PCs
select distinct hd
from pcs
group by hd
having count(hd)>=2

a)Find those pairs of PC models that 
have both the same speed and ram. 
A pair should be listed only once; 
e.g., list (i, j) but not (j, i).
--典型的表的自身连接查询
select a.model,b.model
from pcs a,pcs b
where a.speed=b.speed and a.ram=b.ram
and a.model<b.model

b)Find the makers who sell exactly three 
different models of PC

select distinct maker
from products
where type='pc'
group by maker
having count(*)>=3

c)Find the makers of PCs with at least 
three different speeds
select distinct maker
from products natural join pcs
group by maker
having count(speed)>=3

d)Find those makers of at least two 
different computers (PCs or laptops) 
with speeds of at least 2.80
--想让大家用视图解决问题
--from子句中应用别名方式给子查询命名名称也可以
select distinct maker
from products natural join 
(
select model,speed from pcs where speed>=2.8
union select model,speed from laptops where speed>=2.8
) atleastSpeed
group by maker
having count(*)>=2

e)Find the maker(s) of the computer 
(PC or laptop) with the highest available speed

--想让大家用视图解决问题

create view v_pcandlaptop(model,speed)
as
select model,speed from pcs
union select model,speed from laptops

select distinct maker
from products
where model in (
select model from
(
select model,speed from pcs
union select model,speed from laptops
) atleastSpeed where atleastSpeed.speed>=all(
select speed from pcs
union select speed from laptops
)
)

select distinct maker
from products
where model in (
select model from
v_pcandlaptop where speed>=(select max(speed) from v_pcandlaptop)
)

Create Shorturl Create a shorter url that redirects to your paste?

Private Private paste aren't shown in recent listings.

Delete After When should we delete your paste?

Spam protection Type in the letters

Stikked

数据库实验

Reply to "数据库实验"