SQL语言的基本操作数据库系统原理实验

一、实验目的

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