SQL基础 之一 增删改查

--判断表stuInfo2是否存在，如果存在删除表 stuMarks和stuInfo2

if exists(select name from sysobjects where name='stuInfo2' and type='u')

begin

drop table stuMarks

drop table stuInfo2

end 

 

/*创建表stuInfo2*/

create table stuInfo2

(stuNamevarchar(20) not null,/*学生姓名*/

stuNo char(6)not null,/*学号*/

stuAge int not null,/*年龄*/

stuID NUMERIC(18,0),/*身份证号，NUMERIC (18,0)代表18位数字，小数位数为0 */

stuSeat smallint identity(1,1)/*自动编号（标识列），从1开始递增*/

)

GO

/*新增列stuAddress和stuPhoto*/

alter table stuInfo2add stuAddresstext

alter table stuInfo2add stuPhotoimage

 

/*为表stuInfo2添加主键约束，stuNo作为主键*/

alter table stuInfo2add constraint pk_stuNoprimary key(stuNo)

 

/*为表stuInfo2添加唯一约束unique--，身份证号码唯一。。。。*/

alter table stuInfo2add constraint UQ_stuIDUNIQUE(stuID)

 

/*为表stuInfo2添加默认约束default--，若地址不填，则默认为不详。。。*/

alter table stuInfo2add constraint DF_stuAdressDEFAULT('地址不详') for stuAdress

 

/*检查 check---*/

alter table stuInfo2add constraint CK_stuAgeCHECK(stuAgebetween 15and 40)

 

/*解除约束  alter tabel 有约束的表名 dorp 约束名*/

 

/*创建表stuMarks*/

create table stuMarks

(ExamNochar(7) not null,/*考号*/

stuNo char(6)not null,/*学号*/

WrittenExam int not null,/*笔试成绩*/

spokenExam int not null/*口语成绩*/

)

GO

 

/*为表stuMarks添加主键约束，ExamNo作为主键*/

alter table stuMarksadd constraint PK_ExamNoprimary key(ExamNo)

 

/*添加外键约束，主表stuInfo和从表stuMarks建立关系，关联字段为stuNo*/

alter table stuMarksadd constraint FK_stuNoforeign key(stuNo) references stuInfo2(stuNo)

go

 

/*为表stuInfo2添加数据*/

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('杨晓明',001,23,1234567,'浙江')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('杨刚',002,24,1234543,'江苏')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('曾花',003,20,1234509,'河南')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('王将',005,25,6734543,'广东')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('黎明',008,19,1258567,'广西')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('何明',007,24,1263980,'哈尔滨')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('蒋文',033,23,1236793,'河南')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('黎亚',043,27,1230703,'浙江')

insert into stuInfo2(stuName,stuNo,stuAge,stuID,stuAddress) values('黎名',765,27,1230703,'浙江'')

/*更新修改数据行*/

update stuInfo2 set stuPhoto='不详' where stuPhotois null

update stuInfo2 set stuPhoto=null where stuAge<26


/*删除指定的数据行*/

delete from stuMarkswhere ExamNo=310

delete from stuMarkswhere ExamNo=123

 

/*为表stuMarks添加数据*/

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(123,043,90,90)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(310,005,69,78)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(270,033,100,98)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(210,005,97,100)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(110,001,80,90)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(140,043,100,100)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(111,008,99,90)

insert into stuMarks(ExamNo,stuNo,writtenExam,spokenExam) values(114,002,70,98)

 

/*删除所有记录truncate--，删除后表的结构列，约束，索引都不改变，不能用于有外键约束的表*/

truncate table stuMarks2

truncate table stuinfo2 /*该表由外键约束，无法删除*/

 

/*查询语句*/

/*查询表的所有信息*/

select * from stuInfo2

select * from stuMarks

select * from stuMarks2


/*指定查询*/

select WrittenExamfrom stuMarksorder by  WrittenExam desc/*降序排序*/

select spokenExamfrom stuMarksorder by  spokenExamasc/*升（默认）*/

select WrittenExamfrom stuMarks where WrittenExam>80order by  WrittenExam desc/*降序排序*/

select spokenExam  from  stuMarks where spokenExam>80and spokenExam<100

 

/*按多列排序,口语成绩小于98的，先按口语成绩排序，在按学号排序*/

select stuNoas 学号,spokenExamas 口语成绩from stuMarks where spokenExam<98order by spokenExam,stuNo

 

/*用AS显示查询结果的列名*/

select ExamNoas 学生考号,stuNoas 学生学号,WrittenExamas 笔试成绩, spokenExamas  口语成绩from stuMarks

select ExamNoas 学生考号,stuNoas 学生学号,WrittenExamas 笔试成绩, spokenExamas  口语成绩from stuMarkswhere spokenExam>90

 

/*合并两列数据，并以规定格式查询结果*/

select stuNo,WrittenExam+spokenExamas 各学生总成绩from stuMarks

 

select stuNo,(WrittenExam*0.6+spokenExam*0.4) as 各学生总成绩from stuMarkswhere(WrittenExam*0.6+spokenExam*0.4)>90

select stuNo,WrittenExam*0.6+spokenExam*0.4as 各学生总成绩from stuMarksorder by(WrittenExam*0.6+spokenExam*0.4) desc

 

/*使用=来命名列*/

select 考号=ExamNo,学号=stuNo,笔试成绩=WrittenExam,口语成绩=spokenExam from stuMarks where stuNo>40/*查询学号大于40的学生的成绩并以规定格式显示*/

select 学生总分=WrittenExam+spokenExam from stuMarks where stuNo=1/*查询学号为1的学生的总成绩*/

 

/*查询某几列为空的行*/

select stuNo  from stuInfo2 where stuPhotois null

 

/*限制查询结果输出的行数*/

select top 6 stuNo from stuInfo2

 

/*返回百分之几的行*/

select top 40percent stuNo from stuMarks

select top 40percent stuNo from stuMarks  where spokenExam<90

 

/*模糊查询*/

/*  like--*/

select stuNameas 姓名from stuInfo2 where stuName like '杨%'/*查姓杨的人*/

select stuNameas 姓名from stuInfo2 where stuName like '杨_'/*查姓杨且单名的人*/

select stuNameas 姓名from stuInfo2 where stuName like '%明%'/*姓名中含有 明 的人*/

 

/* is null--*/

select stuID from stuInfo2 where stuPhotois null/*查询stuPhoto栏为空的学生身份证号*/

 

/* between--*/

select stuNo,spokenExam from stuMarks where spokenExambetween 80and 99/*查询口语成绩在80-99之间的学生学号和对应的口语成绩*/

 

/* in--*/

select stuNo,stuName from stuInfo2 where stuAddress  in ('浙江','河南')/*数据类型 text 和 varchar 在 equal to 运算符中不兼容。用下面的方法转换数据类型*/

select stuNo,stuName from stuInfo2 where cast(stuAddressas varchar(max))  in ('浙江','河南')/*查询地点在浙江和河南两个地方的学生学号和姓名*/

select stuNo,stuName from stuInfo2 where convert( varchar(max),stuAddress)  in ('浙江','河南')


/*聚合函数，即统计*/

select sum(spokenExam) from stuMarks where stuNo>20/*求和sum,学号大于20的口语成绩的和*/

select avg(spokenExam) from stuMarks where stuNo>=20/*求平均值AVG，学号大于等于20的口语成绩均值*/

select max(spokenExam) as 最高分,min(spokenExam) as 最低分from stuMarks where spokenExam>80/*求最大max，最小值min*/

select count(*) as 优秀from stuMarks where WrittenExam>=90/*求数量，计算分数为90以上为优秀的人数*/

 

/*数据类型转换--*/ 

/*select cast(字段 as varchar) from 表名

--或者

select convert(varchar(50),字段) from 表名*/

select stuNo,stuNamefrom stuInfo2where cast(stuAddressas varchar(max))  in ('浙江','河南')

 

/*分组查询*/

select stuNo,sum(spokenExam) as 总分from stuMarksgroup by stuNo/*单列分组查询*/

 

/*exists查询视图存在性*/

if exists(select * from sysobjects where name='view_stuInfo2_stuMarks')

   drop view view_stuInfo2_stuMarks

go

 

create view view_stuInfo2_stu_Marks/*创建视图stuInfo2_stuMarks*/

 as

    select  姓名=stuName,学号=stuInfo2.stuNo,笔试成绩=WrittenExam,口语成绩=spokenExam,平均成绩=(WrittenExam+spokenExam)/2       

    from stuInfo2full join stuMarkson stuInfo2.stuNo=stuMarks.stuNo/*多表连接，左连接left join，右连接right join，全连接full join*/

  go

 

  /*查询视图*/

  select * from view_stuInfo2_stu_Marks

/*删除视图--*/

    drop view view_stuInfo2_stu_Marks