一、实验目的

1.掌握SELECT语句的基本用法；

2.掌握子查询表示；

3.掌握直接查询表示；

4.掌握SELECT语句中的GROUP BY子句的作用和使用方法；

5.掌握SELECT语句中的ORDER BY子句的作用和使用方法；

6.掌握SELECT语句中的LIMIT子句的作用和使用方法；

二、实验内容与实验结果

1.SELECT语句的基本使用

(1)use YGGL;

select * from Employees;

(2)select Name,Address,PhoneNumber

from Employees;

a.用SELECT语句查询Departments 和Salary表的一行或者若干列；

select DepartmentID,DepartmentName

from Departments;

select EmployeesID,InCome,OutCome

from Salary

where EmployeesID=’010008’ ;

(3) select Address,PhoneNumber

from Employees

where EmployeesID= ‘000001’;

(4) select Address as 地址,PhoneNumber as 电话

from Employees

where sex =’0’;

(5)select Name as 姓名,

case

when Sex=’1’ then ‘男’

when Sex=’0’ then ‘女’

end as 性别

from Employees;

(6)select EmployeesID,InCome-OutCome as 实际收入

from Salary;

(7)select COUNT(*)

from Employees;

(8)select DepartmentID

from Employees

where name like ‘王%’;

(9)select EmployeesID

from Salary

where InCome between 2000 and 3000;

2.子查询的使用

(1)select * from Employees

where DepartmentID=

( select DepartmentID

from Departments

where DepartmentName=’广告部’);

);

(2) select Name

from Employees

where DepartmentID in

( select DepartmentID

from Departments

where DepartmentName=’研发部’

)

and

Birthday<= ALL

( select Birthday

from Employees

where DepartmentID in

( select DepartmentID

from Departments

where DepartmentName=’市场部’

)

);

(3) select Name

from Employees

where EmployeesID in

( select EmployeesID

from Salary

where InCome >

all ( select InCome

from Salary

where EmployeesID in

( select EmployeesID

from Employees

where DepartmentID=

( select DepartmentID

from Departments

where DepartmentName=’广告部’

)

)

)

);

3.连接查询的使用

(1)select Employees.* ,Salary.*

from Employees,Salary

where Employees.EmployeesID=Salary.EmployeesID;

(2)select DepartmentName

from Departments join Employees

on Departments.DepartmentID=Employees.DepartmentID

where Employees.Name=’王林’;

(3)

select Name,InCome,OutCome

from Employees,Salary,Departments

where Employees.EmployeesID=Salary.EmployeesID

and

Employees.EmployeesID=Departments.DepartmentID

and

DepartmentName=’广告部’

and

InCome>2000;

4.GROUP BY、ORDER BY和LIMIT字句的使用

(1)select Sex,COUNT(SEX)

from Employees

group by Sex;

(2)select DepartmentName,COUNT(*)AS 人数

from Employees,Departments

where Employees.DepartmentID=Departments.DepartmentID

group by Employees.DepartmentID

having COUNT(*)>2;

(3)select EmployeesID

from Employees

order by EmployeesID DESC;

(4)select *

from Employees

limit 5;

三、实验心得

查询分析器是在数据库应用系统时使用的工具。查询分析器的主要作用是编辑SQL将其发送到服务器，并将执行结果及分析显示出来。查询分析功能主要通过测试查询成本，判断查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。

在查询分析器中的左边窗口是对象浏览器，其中按树结构列出了数据库对象；右上方是SQL代码区域，用于输入SQL的查询语句；右下方为结果区，用于显示查询结果和分析结果。对于SQL语句的执行结果，在结果区中可以有4中不同的输出形式；标准执行将结果直接显示在结果区；网格执行将结果以表格形式显示在结果区；计划执行显示执行计划；索引分析为在结果区中显示查询的索引情况。

四、代码

CREATE DATABASE YGGL CHARACTER SET UTF8;

use YGGL;

create table Employees ( EmployeesID char(6) not null, Name char(10) not null, Education char(4) not null, Birthday date not null, Sex char(2) not null default '1', WorkYear tinyint(1), Address varchar(20), PhoneNumber char(12), DepartmentID char(3) not null, primary key(EmployeesID) )engine=innodb;

create table Employees0 like Employees;

create table Departments ( DepartmentID char(3) not null, DepartmentName char(20) not null, Note text(16) )engine=innodb;

create table Salary ( EmployeesID char(6) not null, InCome float(8) not null, OutCome float(8) not null )engine=innodb;

insert into Employees values('000001','王林','大专','1966-01-23','1',8,'中山路32-1-508','83355668','2');

insert into Employees values('010008','伍荣华','本科','1976-03-28','1',3,'北京东路100-2','83321321','1');

insert into Employees values('020010','王向荣','硕士','1982-12-09','1',2,'四牌楼10-0-108','83792316','1');

insert into Employees values('020018','李丽','大专','1960-07-30','0',6,'中山东路102-2','83413301','1');

insert into Employees values('102201','刘明','本科','1972-10-18','1',3,'虎距路100-2','83606608','5');

insert into Employees values('102208','朱俊','硕士','1965-09-28','1',2,'牌楼巷5-3-106','84708817','5');

insert into Employees values('108991','钟敏','硕士','1979-08-10','0',4,'中山路10-3-105','83346722','3');

insert into Employees values('111006','张石兵','本科','1974-10-01','1',1,'解放路34-1-203','84563418','5');

insert into Employees values('210678','林涛','大专','1977-04-02','1',2,'中山北路24-35','83467336','3');

insert into Employees values('302566','李玉珉','本科','1968-09-20','1',3,'热和路209-3','58765991','4');

insert into Employees values('308759','叶凡','本科','1978-11-18','1',2,'北京西路3-7-52','83308901','4');

insert into Employees values('504209','陈林琳','大专','1969-09-03','0',5,'汉中路120-4-12','84468158','4');

select *from Employees;

insert into Departments values('1','财务部','');

insert into Departments values('2','人力资源部','');

insert into Departments values('3','经理办公室','');

insert into Departments values('4','研发部','');

insert into Departments values('5','市场部','');

select * from Departments;

insert into Salary values('000001',2100.8,123.09);

insert into Salary values('010008',1582.62,88.03);

insert into Salary values('102201',2569.88,185.65);

insert into Salary values('111006',1987.01,79.85);

insert into Salary values('504209',2066.15,108.0);

insert into Salary values('302566',1980.7,210.2);

insert into Salary values('108991',3259.98,281.52);

insert into Salary values('020010',1860.0,198.0);

insert into Salary values('020018',2347.68,180.0);

insert into Salary values('308759',2351.98,199.08);

insert into Salary values('210678',2240.0,121.0);

insert into Salary values('102208',1980.0,100.0);

select * from Salary;

/***********三个表格数据*******************/

replace into Departments values('1','广告部','负责推广产品');

/***********第三次作业，课本实验4**********************/

use YGGL;

select * from Employees;

select Name,Address,PhoneNumber from Employees;

select DepartmentID,DepartmentName from Departments;

select Address,PhoneNumber from Employees where EmployeesID= '000001';

select Address as 地址,PhoneNumber as 电话 from Employees where sex ='0';

select Name as 姓名, case when Sex='1' then '男' when Sex='0' then '女' end as 性别 from Employees;

select EmployeesID,InCome-OutCome as 实际收入 from Salary;

select COUNT(*) from Employees;

select DepartmentID from Employees where name like '王%';

select EmployeesID from Salary where InCome between 2000 and 3000;

select * from Employees where DepartmentID= ( select DepartmentID from Departments where DepartmentName='广告部');

)

select Name from Employees where DepartmentID in ( select DepartmentID from Departments where DepartmentName='研发部' ) and Birthday<= ALL ( select Birthday from Employees where DepartmentID in ( select DepartmentID from Departments where DepartmentName='市场部' ) );

select Name from Employees where EmployeesID in ( select EmployeesID from Salary where InCome > all ( select InCome from Salary where EmployeesID in ( select EmployeesID from Employees where DepartmentID= ( select DepartmentID from Departments where DepartmentName='广告部' ) ) ) );

select Employees.* ,Salary.* from Employees,Salary where Employees.EmployeesID=Salary.EmployeesID;

select DepartmentName from Departments join Employees on Departments.DepartmentID=Employees.DepartmentID where Employees.Name='王林';

select Name,InCome,OutCome from Employees,Salary,Departments where Employees.EmployeesID=Salary.EmployeesID and Employees.EmployeesID=Departments.DepartmentID and DepartmentName='广告部' and InCome>2000;

select Sex,COUNT(SEX) from Employees group by Sex;

select DepartmentName,COUNT(*)AS 人数 from Employees,Departments where Employees.DepartmentID=Departments.DepartmentID group by Employees.DepartmentID having COUNT(*)>2;

select EmployeesID from Employees order by EmployeesID DESC;

select * from Employees limit 5;