| 快来分享啊,我才知道 的啊,~!
create database STU_CJ
on primary
(
name=STU_CJ_DATA,
filename=';C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\桌面\SQL代码\STU_CJ_DATA.mdf';,
size=2mb,
maxsize=400mb,
filegrowth=10%
)
log on
(
name=STU_CJ_LOG,
filename=';C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\桌面\SQL代码\STU_CJ_LOG.ldf';,
size=1mb,
maxsize=200mb,
filegrowth=10%
)
go
use STU_CJ
go
create table Stu --学生表
(
ID char(12) primary key not null, --学号
Name char(10),--姓名
Sex char(2) default ';男'; check(Sex=';男'; or Sex=';女';),--性别
ClassID char(9) ,--班级编号
BirthDay smalldatetime,--出生日期
zcj numeric(5,1) default 0.0 check(zcj>=0 and zcj<=500),--总成绩
mc int default 0 --名次
)
go
create table Class -- 班级表
(
ClassID char(9) primary key not null,-- 班级编号
ClassName varchar(40),--班级名称
ClassCount int default 0 --班级人数
)
go
--给班级增加记录
insert into class (classid,classname,classcount)
values (';051022031';,';05网络1班';,49)
insert into class (classid,classname,classcount)
values (';051022032';,';05网络2班';,48)
insert into class (classid,classname,classcount)
values (';051022033';,';05网络3班';,48)
insert into class (classid,classname,classcount)
values (';051022051';,';05WEB班';,35)
insert into class (classid,classname,classcount)
values (';051022071';,';05软件1班';,31)
insert into class (classid,classname,classcount)
values (';051022072';,';05软件2班';,30)
----- 给学生表增加记录
insert into Stu (ID,Name,Sex,BirthDay)
values (';051022031001';,';Abc';,';男';,';1987-12-10';)
insert into Stu (ID,Name,Sex,BirthDay)
values (';051022031002';,';GGGG';,';女';,';1987-02-20';)
insert into Stu (ID,Name,Sex,BirthDay)
values (';051022032001';,';BBBB';,';男';,';1986-12-10';)
--查询班级
select * from class
--*表示所有字段
-- 对classid,和classname查询
select classID,className from class
--对学号为051022051的学生进行查询
select * from class where classid=';051022051';
--将54号插入
insert into class (classid,classname,classcount)
values(';04102204 ';,';04软件1班';,54)
--查询04级的班
select *from class
where left(classid,2)=';04';
--left(classid,2)代表取classid的左边的前二位
--查询所有为网络专业的班级classid的，从第6位取3位203
select * from class
where substring (classid,6,3)=';203';
--将英文代号改为汉字
select classid as ';班级编号';, classname as ';班级名称';,
classcount as ';人数'; from class
|
