华南理工软件复试数据库【细节】

#窗体设计

【事件】各控件记得绑定对应事件

Form——Form_Load

TabControl——tabControl_SelectedIndexChanged

Button——button_Click

【属性】

1.Form

     StartPosition运行位置-------CenterScreen

2.comboBox：

    DropDownStyle设置为DropDown，以便自行输入文本和选择空白文本；

     DropDownStyle设置为DropDownList，则不可以自定义内容。                  

3.dataGridView:

      AllowUserToAddRows设置为False，即去掉表中最后一行空白行；

     ReadOnly只读——False；

     RowHeaderVisible设置为False——行的首列置为不可见；

     SelectionMode设置为FullRowSelect——整行一起被选中;

     AutoSizeColumnMode设置为Fill——表格内容自适应datagridview大小

#数据库设计

1.弄清楚建表顺序

2.约束：

#主键primary key;    不为空not null;         值的范围check() ;   

#外键要加上如下限制：on update cascade on delete cascade

#唯一约束unique

3.varchar与nvarchar：中文较多时用nvarchar

4.关键字：遇到group、month之类的关键字务必加上方括号[ ]，关键字颜色为蓝色。

#数据库操作

【Attention】增删改查（SCUT2016）注意约束条件：

【2015】

1.   建表

create table Employee(
	EmpNo varchar(5) primary key,
	EmpName nvarchar(4) not null,
	EmpSex varchar(2) not null check(EmpSex='男' or EmpSex='女'),
	EmpAge int not null checked(EmpAge>0)
);

create table Company(
	CmpNo varchar(10) primary key,
	CmpName nvarchar(20) not null
);

create table Works(
	EmpNo varchar(5),
	CmpNo varchar(10),
	Salary int check(Salary>0),
	primary key(EmpNo, CmpNo),
	foreign key(EmpNo) references Employee(EmpNo) on update cascade on delete cascade,
	foreign key(CmpNo) references Company(CmpNo) on update cascade on delete cascade,
);

2.查询与统计

（1）根据员工号或员工名查找员工所在的公司名和工资，员工号或员工名不能文本输入，要求使用下拉菜单实现，并与数据库中现有信息一致（10分）

select [employee].EmpNo, [employee].EmpName, [company].CmpName, [works].Salary
from employee, company, works
where [employee].EmpNo = [works].EmpNo and [company].CmpNo = [works].CmpNo;

（2）统计年龄至少为40岁员工的总工资，工资按从大到小顺序排列；与数据库中现有信息一致（10分）

select [employee].EmpNo, [employee].EmpName, [employee].EmpAge, sum([works].Salary) as salary
from [employee], [works]
where employee.EmpNo = works.EmpNo and employee.EmpAge >= 40
group by employee.EmpNo, employee.EmpName, employee.EmpAge
order by salary desc;

（3）查询至少具有两份工作员工的姓名和其公司名。（10分）

两种实现：

select employee.empNo, employee.empName, company.cmpName
from employee, company, (
	select works.empNo, works.cmpNo
	from works
		where works.empNo in(
		select works.empNo
		from works
		group by works.empNo
		having count(works.empNo)>1
		)
	) as t1
where employee.empNo = t1.empNo and company.cmpNo = t1.cmpNo

select employee.empname, company.cmpname
from employee, company, works, (
	select empno from works 
	group by empno having count(empno)>1
) as w1 
where employee.empno=works.empno and company.cmpno=works.cmpno and works.empno=w1.empno

【2016】

1.建表

create table Department(
	deptNo varchar(5) primary key,
	deptName nvarchar(10) not null
);

create table CLASS(
	classNo varchar(5) primary key,
	specName nvarchar(20) not null,
	deptNo varchar(5) references Department(deptNo) on update cascade on delete cascade,
    [year] varchar(4) not null,
    num int not null check(num > 0 and num <= 30)
);


create table Student(
	SNo varchar(5) primary key,
	SName nvarchar(4) not null,    
    [Date] datetime,
	classNo varchar(5) references CLASS(classNo) on update cascade on delete cascade,
);

2.查询与统计

--根据系名称、班级名称、学生姓名在一个界面实现单条件、多条件查询，学生名称可提供模糊查询，
--查询价格包括系名称、专业名称、学生名字，得到的结果按照系（降序）、班（升序）、学生（降序）顺序排列
select * from student, class, department
where student.classno=class.classno and class.deptno=department.deptno
order by department.deptno desc, class.classno asc, student.sno desc

--统计（1）每个系学生人数
select department.deptname as 系名, sum(class.num) as 总人数
from department, class
where department.deptno = class.deptno
group by department.deptno,  department.deptname

--统计（2）按入学年份统计学生人数
select class.[year], sum(class.num) as 学生总人数
from class
group by class.[year]

 【2017】

1.建表

create table Department(
	DeptNo varchar(5) primary key,
	DeptName nvarchar(10) not null,
);

create table [Group](
	GroupNo varchar(5) primary key,	
	GroupName nvarchar(10) not null,
	DeptNo varchar(5) references Department(DeptNo) on update cascade on delete cascade,
	[Month] int not null check([Month]>0 and [Month]<13),
	Number int not null check(Number>0 and Number<=80),
);


create table Patient(
	PID varchar(6) primary key,
	PName nvarchar(4) not null,
	[Date] datetime not null,
	GroupNo varchar(5) references [Group](GroupNo) on update cascade on delete cascade,
);


--alter table [Group] add constraint CK_Number check(Number <= 80)

2.触发器

--增加新病人时，触发增加小科室病人数
create Trigger SCUT2017_addPatient 
On Patient                         --在Student表中创建触发器 
for insert                         --为什么事件触发 
As                                 --事件触发后所要做的事情 
--IF update(studentID)
BEGIN
declare @thegroup varchar(6) --从临时表Inserted记录新的的学号ID 
select @thegroup=groupno from Inserted
update [Group] set Number=Number+1 where groupno=@thegroup
end 


--修改病人时，触发修改小科室病人数
create Trigger SCUT2017_updatePatient 
On Patient                         --在Student表中创建触发器 
for update                         --为什么事件触发 
As                                 --事件触发后所要做的事情 
IF update(groupNo)
BEGIN
declare @oldgroup varchar(6)
declare @newgroup varchar(6) --从临时表Inserted记录新的的学号ID 
select @oldgroup=groupno from deleted
select @newgroup=groupno from Inserted
update [Group] set Number=Number-1 where groupno=@oldgroup
update [Group] set Number=Number+1 where groupno=@newgroup
end 

--删除病人时，触发减少小科室病人数
create Trigger SCUT2017_deletePatient 
On Patient                         --在Student表中创建触发器 
for delete                         --为什么事件触发 
As                                 --事件触发后所要做的事情 
BEGIN
declare @oldgroup varchar(6)
select @oldgroup=groupno from deleted
update [Group] set Number=Number-1 where groupno=@oldgroup
end 

3.查询与统计

--(1)统计每个大科室的病人人数
select Department.deptname as 大科室名, sum([Group].number) as 总人数
from department, [group]
where department.deptno=[group].deptno
group by department.deptname

--(2)按小科室的月份统计人数
select [Group].[month] as 月份, sum([Group].number) as 总人数
from [group]
group by [Group].[month]

--显示最多和最少病人数的科室
select * from [group] 
where [group].number in (select max(number) from [group]) 
or [group].number in (select min(number) from [group])

【2018】

1.建表

create table Student(
	StuNo varchar(10) primary key,
	StuName nvarchar(5) not null,
	StuSex varchar(2) not null check(StuSex='男' or StuSex='女'),
);

create table Course(
	CrsNo varchar(10) primary key,
	CrsName nvarchar(20) not null
);

create table Grade(
	StuNo varchar(10),
	CrsNo varchar(10),
	Score int check(Score>0),
	primary key(StuNo, CrsNo),
	foreign key(StuNo) references Student(StuNo) on update cascade on delete cascade,
	foreign key(CrsNo) references Course(CrsNo) on update cascade on delete cascade,
);

2.查询与统计

-- 根据经销商号、经销商名、商品名称进行查询：
select businessman.busname as 经销商名, [order].good as 商品名, [order].[date] as 订购日期,[order].[money] as 金额
from businessMan, [order]
where businessman.busno = [order].busno
order by businessman.busname desc, [order].good asc, [order].[money] desc

--1）按照地方统计订单金额：
select businessMan.city as 城市, sum([order].money) as 总金额
from businessMan, [order]
where businessMan.busno=[order].busno
group by businessMan.city

--2)按照经销商统计订单金额：
select businessMan.busname as 经销商名, sum([order].money) as 总金额
from businessMan, [order]
where businessMan.busno=[order].busno
group by businessMan.busname