一、实验目的
1、熟悉MySQL和Navicat的使用。
2、理解SELECT语句的操作和基本使用方法。
3、理解JOIN语句的操作和基本使用方法,掌握内连接、外连接、自身连接的概念和使用
4、掌握视图的定义与工作原理。
5、掌握使用SQL插入、删除和修改数据表数据的方法
二、实验内容
1) 查找出职称为职员的女员工的姓名、职称、性别。
select emp_name,title,Sex from employee where title='职员'and Sex='F'
2) 查找出employee表中与‘周小梅’住址相同的员工的姓名、性别、职称、薪水、住址。
select emp_name,Sex,title,salary,Addr from employee where addr=(select Addr from employee where emp_name='周小梅')
3) 由employee表中查找出薪水最高的员工信息。
select * from employee where salary=(select max(salary)from employee)
4) 查询出employee表中所有女职工的平均工资和住址在"上海市
市"的所有女职工的平均工资
1. select avg(salary) from employee where Sex='F';
2.select avg(salary) from employee where Sex='F' and Addr='上海市';
5)计算出sale_item表中每一笔销售数据的销售金额,并按照销售金额的大小排序。
select Prod_id,Qty,Unit_price,(Qty*Unit_price) as tot_amt from sale_item
order by tot_amt desc
6)检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。
select order_no,Prod_id,Qty,Unit_price from sale_item where Qty>2;
7) 查找所有经理的姓名、职称、薪水。
select emp_name,title,salary from employee where title='经理'
8)查找出姓“王”并且名字的最后一个字为“功”的员工
select *from employee where emp_name like '王_功'
9)查找住在上海或北京的女员工,并显示其姓名、所属部门、职称、住址。
select emp_name,Dept,title,Addr from employee
where Addr like '北京%'or Addr like '上海%' and Sex like 'F'
10)在表sales中挑出销售金额大于等于10000元订单。
select *from sales where tot_amt>=10000;
10)在表sales中挑出销售金额大于等于10000元订单。
select *from sales where tot_amt>=10000;
11)选取订单金额最高的十个订单数据。
select * from sale_item order by Unit_price desc limit 0,10
12)查找出职称为经理或职称为职员的女员工的信息。
select *from employee where (select title='经理' or title='职员')and Sex='F'
13)计算出一共销售了几种产品。
select count(Prod_name)from product
14)显示sale_item表中每种个别产品的订购金额总和,并且依据总额由大到小排列来显示出每一种产品的排行榜。
select Prod_id,sum(Qty*Unit_price)'金额'from sale_item group by Prod_id order by '金额'desc
15)计算每一产品每月的销售金额总和,并将结果按销售(月份,产品编号)排序。(提示:Year(属性名) 将属性名对应的日期型属性值提取年份,Month((属性名) 将属性名对应的日期型属性值提取月份)
select prod_id,SUM(qty*unit_price) as tot_price,month(Ship_date) as month
from sale_item
group by MONTH(Ship_date),prod_id
order by month desc,prod_id desc
16)查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。(提示:使用自我连接将两个地址相同的员工显示在同一行)
select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr
from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)
17)查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。
select a.emp_name,a.Sex,a.title,a.salary,a.Addr
,b.emp_name,b.Sex,b.title,b.salary,b.Addr
from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.Sex='F')and (b.Sex='F')
and (a.Addr=b.Addr)and (a.Dept=b.Dept)
18)检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。
SELECT a.prod_id, b.prod_name, a.qty, a.unit_price
from sale_item a, product b
where a.prod_id=b.prod_id
19) 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。
SELECT product.prod_id,product.prod_name,qty,unit_price
from product,sale_item
where unit_price>2400 and
product.prod_id=sale_item.prod_id
order by sale_item.prod_id
20)由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。
select *
from sales
where tot_amt>all
(select tot_amt
from sales
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt
21)由sales表中查找出销售金额最高的订单。
SELECT *
from sales
where tot_amt=(select max(tot_amt)
from sales)
22)由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单,并显示承接这些订单的业务员和该条订单的金额。
select *
from sales
where tot_amt>all
(select tot_amt
from sales
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt
23)找出公司女业务员所接的订单。
SELECT sale_id,tot_amt
from sales
where sale_id in
(select sale_id
from employee
where sex='女')
24)找出公司中姓名相同的员工,并且依据员工编号排序输出这些员工信息。
select * from employee a
where
(select emp_name from employee b where a.emp_name=b.emp_name and a.emp_no<>b.emp_no)
order by emp_name
可知无姓名相同的员工
25)找出目前业绩未超过200000元的员工。
SELECT *
from employee
where emp_no in
(select sale_id
from sales
group by sale_id
having sum(tot_amt)<=200000)
26)选取销售数量最多的前5条订单订单号、数量。
Select top 5 with ties order_no,sup_id,qty
From sale_item
Order by qty desc
27)由employee表中查找出薪水最高的员工信息。
Select *from employee
Where salary=
(select max(salary)from employee)
28)计算公司内各个部门的工资支出总和。
select title,sum(salary) 工资支出
from employee
group by title
29)计算每一产品销售数量总和与平均销售单价。
select Prod_id,sum(Qty)销售数量总和,
avg(Unit_price)平均销售单价 from sale_item
group by Prod_id
s
30)查询员工的姓名、籍贯和收入水平,60000以上显示为高收入,30000~60000为中等收入,30000以下为低收入。
select emp_name,Addr,salary,
case
when salary<30000 then '低收入'
when salary>30000 and salary<=60000 then'中等收入'
When salary > 60000 then '高收入'
END AS 收入水平
from employee
31)分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。
外部连接:
select product.Prod_id,Prod_name,Qty,Unit_price
from product,sale_item
where product.Prod_id=sale_item.Prod_id
and Unit_price>'2400'
左外连接:
select product.Prod_id,Prod_name,Qty,Unit_price
from product left out join on sale_item si on product.Prod_id = si.Prod_id outer join sales_item on(product.Prod_id=sale_item.Prod_id)
where Unit_price>'2400'
右外连接:
select product.Prod_id,Prod_name,Qty,Unit_price
from product right out join sale_item on product.Prod_id = si.Prod_id outer join sales_item on(product.Prod_id=sale_item.Prod_id)
where Unit_price>'2400'
32) 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。(用join ..on..实现数据表连接)
select product.Prod_id,Prod_name,Qty,Unit_price
from product,sale_item
where product.Prod_id=sale_item..Prod_id
and Qty>2
33)将姓“王”的客户地址改为“上海市”
update employee set Addr = '上海市'
where emp_name='王%';
select *from employee;
34)删除销售表sales中不存在于销售明细表中的定单号。
delete
from sales
where order_no not in (
select order_no
from sale_item
)
35)删除sales表中作废的订单(其发票号码为I000000004),其订货明细数据也一并删除。
Delete from sales where invoice_no='I000000004'
36)将数据(p0015,21寸显示器)插入产品表中
Insert into product (prod_id, prod_name)
values(
'p0015',
'21寸显示器'
)
37)请往销货明细表插入两条记录(内容自拟)
记录1:
insert into sale_item(order_no, Prod_id, Qty, Unit_price, Ship_date)
values ('10006',
'p0009',
'8',
'2400.00',
'2022-11-06 15:58:54'
)
记录2:
insert into sale_item(order_no, Prod_id, Qty, Unit_price, Ship_date)
values ('10009',
'p0019',
9,
'8000.00',
'2022-11-06 16:58:54'
)
38)建立销售信息视图,能显示每个定单的详细信息
create view 销售信息
as
select *from sale_item
39)创建一个视图,该视图只含上海客户信息,即客户号、客户姓名、住址。
create view 上海客户信息
as
select emp_no,emp_name,Addr
from employee
where Addr='上海市'
40)对视图添加一条记录数据。(注意:分别查看customer表和该视图的结
insert
into 上海客户信息(emp_no,emp_name, addr)
values('C1111',
'上海市')
41)删除视图中所有姓“王”的客户数据。
delete from 上海客户信息
where emp_name='王%'
42) 通过视图修改表内某一名字中包含‘王’字的客户的姓名为‘张三’。
update 上海客户信息
set emp_name='张三'
where emp_name='%王%'
43)有两个基本表employee和sales,创建一个视图,该视图包含sales中各业务员的编号、姓名、订单号、销售总金额。
create view 销售详情
as
select emp_no,emp_name,Sale_id,tot_amt
from sales,employee
where sales.Sale_id=employee.emp_no
44) 将上述视图中订单号为10001的记录的销售总金额改为60000。
update sales
set tot_amt='60000'
where Sale_id='1000'
45)给上述视图添加一条记录数据。
insert
into 销售详情
values('12303','p',54321,300000)
46) 删除上述视图。
drop view 销售详情
1