#窗体设计
【事件】各控件记得绑定对应事件
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】
- 建表
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