实验1

[TOC]

1-6

一、安装MYSQL

在安装MYSQL的同时，也安装了DataGrip可视化界面，本次实验使用DataGrip与MYSQL命令行界面交叉使用来练习SQL语言。对于DataGrip来说，其显示的代码更完整，以及展示的更规整。

二、建立并使用BANK

我的学号是20069100175，因此选取后三位175作为bank的后缀。

三、建表并输入数据

# 建表：BRANCHxxx, CUSTOMERxxx, LOANxxx, BORROWERxxx, ACCOUNTxxx, DEPOSITORxxx；
CREATE TABLE account_175 (
    account_number char(10),
    branch_name char(30),
    balance  numeric(12,2),
    PRIMARY KEY (account_number),
    FOREIGN KEY (branch_name)
	REFERENCES branch_175(branch_name),
    CONSTRAINT chk_balance CHECK (balance >= 0 )
    );

create table  branch_175(
    branch_name char(30),
    branch_city char(10),
    assets int,
    primary key(branch_name)
    );

create table customer_175(
    customer_name char(10),
    customer_street char(10),
    customer_city char(20)
);

create table depositor_175(
    customer_name char(10),
    customer_number char(10)
);

create table loan_175(
  loan_number char(10),
  branch_name char(30),
  amount int,
  FOREIGN KEY (branch_name) REFERENCES branch_175(branch_name)
);

create table borrower_175(
  customer_name char(10),
  loan_number char(10)
);
# 查询建表情况
desc account_175;

# 插入数据
insert into account_175 values
('A_101','Downtown',500),
('A_102','Perryridge',400),
('A_201','Brighton',900),
('A_215','Mianus',700),
('A_217','Brighton',750),
('A_222','Redwood',700),
('A_305','Round Hill',350);

insert into branch_175 values
('Brighton','Brooklyn',7100000),
('Downtown','Brooklyn',9000000),
('Mianus','Horseneck',400000),
('North Town','Rye',3700000),
('Perryridge','Horseneck',1700000),
('Pownal','Bennington',300000),
('Redwood','Palo Alto',2100000),
('Round Hill','Horseneck',8000000);

insert into customer_175 values
('Adams','Spring','Pittsfield'),
('Brooks','Senator','Brooklyn'),
('Curry','North','Rye'),
('Glenn','Sand Hill','Woodside'),
('Green','Walnut','Stamford'),
('Hayes','Main','Harrison'),
('Johnson','Alma','Palo Alto'),
('Jones','Main','Harrison'),
('Lindsay','Park','Pittsfield'),
('Smith','North','Rye'),
('Turner','Putnam','Stamford'),
('Williams','Nassau','Princeton');

insert into depositor_175 values
('Hayes','A-102'),
('Johnson','A-101'),
('Johnson','A-201'),
('Jones','A-217'),
('Lindsay','A-222'),
('Smith','A-215'),
('Turner','A-305');

insert into loan_175 values
('L-11','Round Hill',900),
('L-14','Downtown',1500),
('L-15','Perryridge',1500),
('L-16','Perryridge',1300),
('L-17','Downtown',1000),
('L-23','Redwood',2000),
('L-93','Mianus',500);

insert into borrower_175 values
('Admas','L-16'),
('Curry','L-93'),
('Hayes','L-15'),
('Jackson','L-14'),
('Jones','L-17'),
('Smith','L-11'),
('Smith','L-23'),
('Williams','L-17');

批量建表如下:

批量录入数据如下：

7

创建图3.12的四张表，输入一些数据，查询书上第三章3.2, 3.9的作业题。

一、创建表并输入数据

在给定的schema中，employee_name是主键，说明要求每一位员工的姓名必须是不同的。company_name是主键，说明每一家公式的名字也是不同的。因此建立schema如下：

# set schema
create table employee(
    employee_name char(10) comment '职员姓名',
    street char(10),
    city char(10),
    primary key (employee_name)
);

create table works(
    employee_name char(10),
    company_name char(10),
    salary int,
    primary key (employee_name),
    foreign key (employee_name) references employee(employee_name)
);

create table company(
    company_name char(10),
    city char(10),
    primary key (company_name)
);

create table managers(
    employee_name char(10),
    manager_name char(10),
    primary key (employee_name),
    foreign key (employee_name) references employee
);

对于每个员工的上司，最高级的员工，他的上司是他自己；上司的姓名也在员工表内。

插入数据时没有写入E5。

# insert data
# City一共是C1-8，Street一共是S1-6，共有32名员工
insert into employee values
('E1','S1','C1'),('E2','S4','C1'),('E3','S1','C1'),('E4','S1','C1'),('E6','S2','C2'),
('E7','S6','C2'),('E8','S1','C2'),('E9','S1','C2'),('E10','S1','C3'),('E11','S2','C3'),
('E12','S3','C3'),('E13','S4','C3'),('E14','S1','C4'),('E15','S2','C4'),('E16','S1','C4'),
('E17','S3','C5'),('E18','S4','C4'),('E19','S5','C5'),('E20','S6','C1'),('E21','S2','C8'),('E22','S5','C8'),('E23','S6','C8'),('E24','S4','C8'),('E25','S3','C8'),('E26','S5','C1'),('E27','S2','C9'),('E28','S1','C9'),('E29','S4','C9'),('E30','S2','C9'),('E31','S1','C7'),
('E32','S6','C6');
#这些员工在哪家公司工作？薪水是多少
insert into works (employee_name, company_name, salary) values
('E1','COM1',5100),('E2','COM2',6100),('E3','COM1',7100),('E4','COM3',9100),
('E6','COM6',15600),('E7','COM4',1600),('E8','COM5',8600),('E9','COM1',6600),
('E10','COM2',5000),('E11','COM3',2300),('E12','COM4',10200),('E13','COM5',8000),
('E14','COM6',7000),('E15','COM1',8900),('E16','COM1',2300),('E17','COM1',1200),
('E18','COM1',4500),('E19','COM2',6300),('E20','COM2',7400),('E21','COM2',13000),
('E22','COM3',5000),('E23','COM3',6000),('E24','COM3',7000),('E25','COM4',8000),
('E26','COM4',9000),('E27','COM4',1000),('E28','COM5',3000),('E29','COM5',800),
('E30','COM6',4000),('E31','COM6',2000),('E32','COM6',1000);

insert into company values
('COM1','C5'),('COM2','C2'),('COM3','C1'),
('COM4','C1'),('COM5','C3'),('COM6','C4');

insert into managers (employee_name, manager_name)values
('E1','E1'),('E2','E10'),('E3','E1'),('E4','E11'),('E6','E14'),('E7','E12'),
('E8','E8'),('E9','E1'),('E10','E10'),('E11','E11'),('E12','E12'),('E13','E8'),
('E14','E14'),('E15','E3'),('E16','E9'),('E17','E3'),('E18','E9'),('E19','E10'),
('E20','E2'),('E21','E2'),('E22','E11'),('E23','E22'),('E24','E4'),('E25','E7'),
('E26','E27'),('E27','E12'),('E28','E8'),('E29','E8'),('E30','E14'),('E31','E30'),('E32','E14');

二、查询3.2

1. 找出fisrt bank公司所有员工的姓名以及居住城市

设first bank为COM1:

1	select works.employee_name as name,city from employee,works where works.employee_name=employee.employee_name and company_name='COM1';

结果：

2.找出fisrt bank公司所有收入超1w美元的员工的姓名、街道地址、居住城市

1	select works.employee_name as name,street,city,salary from works,employee where salary>10000 and works.employee_name=employee.employee_name;

结果：

3.找出DB范围内所有不为fisrt bank工作的员工

1	select works.employee_name as name,company_name from employee,works where works.employee_name=employee.employee_name and company_name!='COM1';

结果：

4.找出DB范围内所有收入高于small bank公司每一个员工的员工

假设small bank为COM4。在COM4中，工资最高的是E12，其工资为10200。

1
2
3

select * from works;# 首先查看全部员工的工资情况
select employee_name,salary from works where company_name='COM4';# 再查看COM4的员工中的最高工资
select * from works where salary>( select max(salary) from works where company_name='COM4');#以此为筛选条件

结果：

5.假设公司位于多个城市。找到位于每个城市所有公司，在这些城市，small Bank也在

公司位于多个城市，可能是类似总公司与子公司的情况。但是由于题干中将company_name设置为了主键，因此是唯一的、不可重复的。由于其对应的city可以是多个，但是对于唯一的公司只能有一条数据，因此没有办法设置为多个城市？

相当于暂时忽略掉主键的设定。

# (1)首先先取消掉设置的主键。并添加一些子公司的地址数据。
insert into company values ('COM4','C2'),('COM1','C2'),('COM1','C1');
# (2)找到city where COM4 land
create temporary table land_city(select city from company where company_name='COM4');# C1,C2
select company_name,city from company natural join land_city where company_name!='COM4';

结果：

然后要删除添加的数据：

# 把company修改为原来样子
delete from company where company_name='COM4' and city='C2';
delete from company where city='C2' and company_name='COM1';
delete from company where company_name='COM1' and city='C1';

6.找到拥有最多的员工的公司

count解决问题！使用order对人数排序，排在第一的是人数最多的公司。

1	select company_name,count(employee_name)as cnt_employee from works group by company_name order by cnt_employee desc ;

结果：

7.找出一些公司，这些公司员工的平均工资高于fisrt bank的平均工资

having后的条件是进行分组后的数据再次进行筛选。

1
2

select company_name,avg(salary) as avg_salary from works group by company_name order by avg_salary desc;
select company_name,avg(salary) as avg_salary from works group by company_name having avg_salary>(select avg(salary) from works where company_name='COM1');

结果：

查询代码

# 查询操作
#1. 找出`fisrt bank`公司所有员工的姓名以及居住城市
select works.employee_name as name,city from employee,works where works.employee_name=employee.employee_name and company_name='COM1';
#2.找出`fisrt bank`公司所有收入超1w美元的员工的姓名、街道地址、居住城市
select works.employee_name as name,street,city,salary from works,employee where salary>10000 and works.employee_name=employee.employee_name;
#3.找出DB范围内所有不为`fisrt bank`工作的员工
select works.employee_name as name,company_name from employee,works where works.employee_name=employee.employee_name and company_name!='COM1';
#4.找出DB范围内所有收入高于`small bank`公司每一个员工的员工
select * from works;# 首先查看全部员工的工资情况
select employee_name,salary from works where company_name='COM4';# 再查看COM4的员工中的最高工资
select * from works where salary>( select max(salary) from works where company_name='COM4');#以此为筛选条件
#5.假设公司位于多个城市。找到位于每个城市所有公司，在这些城市，`small Bank`也在
# (1)首先先取消掉设置的主键。并添加一些子公司的地址数据。
insert into company values ('COM4','C2'),('COM1','C2'),('COM1','C1');
# (2)找到city where COM4 land
create temporary table land_city(select city from company where company_name='COM4');# C1,C2
select company_name,city from company natural join land_city where company_name!='COM4';
#6.找到拥有最多的员工的公司
select company_name,count(employee_name)as cnt_employee from works group by company_name order by cnt_employee desc ;
#7.找出一些公司，这些公司员工的平均工资高于`fisrt bank`的平均工资
select company_name,avg(salary) as avg_salary from works group by company_name order by avg_salary desc;
select company_name,avg(salary) as avg_salary from works group by company_name having avg_salary>(select avg(salary) from works where company_name='COM1');

三、查询3.9

1.找出为first bank工作的所有员工的姓名

1	select employee_name from employee natural join works where company_name='COM1';

结果：

2.在DB范围中找出，居住地与所在公司的city相同的员工

这里给出了两种代码，所得到的结果是相同的；但是自然连接的代码会比较简洁。

1
2

select * from employee,company,works where works.employee_name=employee.employee_name and works.company_name=company.company_name and company.city=employee.city;
select * from employee natural join works natural join company;

3.在DB范围中找出，居住地与其经理的city相同、street相同的员工

由于我设计了多级的经理，最高级的经理的manager是他自己。因此这里的思路是：先从managers中找到所有的经理，由于所有的经理也在employee中，该表中也有他们的住址信息。因此对两表进行如下操作：

create temporary table mana(select distinct manager_name from managers natural join employee);
#找到一些经理。
create temporary table mana_info(select manager_name as mana_name,street,city from employee,mana where employee_name=manager_name);
create temporary table emp(select * from managers natural join employee);
select * from mana_info,emp where emp.street=mana_info.street and emp.city=mana_info.city and emp.manager_name=mana_name;

结果：

4.找出每家公司中，所有收入高于该公司平均工资的员工

这里也是使用了建立临时表。

1
2
3

create temporary table sub(select company_name,avg(salary) as avg_salary from works group by company_name);
select * from sub;
select * from works natural join sub where salary>avg_salary;

5.找到拥有最少的payroll的公司

payroll指的是公司发放的工资总额。该处使用升序排序，因此排在第一个的公司即为目标公司。

1	select company_name,sum(salary)as payroll from works group by company_name order by payroll asc ;

结果：

查询代码

# 3.9
#1.找出为`first bank`工作的所有员工的姓名
select employee_name from employee natural join works where company_name='COM1';
#2.在DB范围中找出，居住地与所在公司的`city`相同的员工
select * from employee,company,works where works.employee_name=employee.employee_name and works.company_name=company.company_name and company.city=employee.city;
select * from employee natural join works natural join company;
#3.在DB范围中找出，居住地与其经理的`city`相同、`street`相同的员工
create temporary table mana(select distinct manager_name from managers natural join employee);#找到一些经理。
create temporary table mana_info(select manager_name as mana_name,street,city from employee,mana where employee_name=manager_name);
create temporary table emp(select * from managers natural join employee);
select * from mana_info,emp where emp.street=mana_info.street and emp.city=mana_info.city and emp.manager_name=mana_name;
#4.找出每家公司中，所有收入高于该公司平均工资的员工
create temporary table sub(select company_name,avg(salary) as avg_salary from works group by company_name);
select * from sub;
select * from works natural join sub where salary>avg_salary;
#5.找到拥有最少的`payroll`的公司
select company_name,sum(salary)as payroll from works group by company_name order by payroll asc ;

数据库上机实验1

实验1

1-6

一、 安装MYSQL

二、 建立并使用BANK

三、建表并输入数据

7

一、创建表并输入数据

二、查询3.2

查询代码

三、查询3.9

查询代码

一、安装MYSQL

二、建立并使用BANK