数据库各种增删查询语句例子

数据库各种增删查询语句例子

一、建数据库和插入 *** 作
1、 用代码创建了一个text1数据库和运行结果如下

CREATE DATAbase text1
ON

(

NAME = text1,

FILENAME ='D:sqltext1_data.mdf',

SIZE = 5MB,

MAXSIZE = 20,

FILEGROWTH = 20

)

LOG ON

(   NAME = text1_log,

FILENAME ='D:sqltext1_log.ldf',//存储的位置

SIZE = 2MB,

MAXSIZE = 10MB,

FILEGROWTH = 1MB

//主数据文件名和主日志要不起相同名字，否则报错的

);

刷新一下数据库，点开database，点开表这时可以看到新建的四个表、
2、altert able语句将student表的sex属性添加check约束：代码与运行结果如下

ALTER TABLE student
ADD ConSTRAINT CK_score_sex CHECK(sex IN('男','女'))

3、每一个表用insert into语句插入5条以上记录，要求满足约束条件，代码如下

insert into student(sno,sname)
values('182','李伟'),('184','木棉花花'),('185','李花'),('187','陆花'),('186','马华')

insert into score(sno,cno)
values('182','1'),('184','2'),('185','3'),('187','4'),('186','5')

在SQL Server Management Studio中生成4个表的关系图。选择数据库名->数据库关系鼠标右键->新建数据库关系图，分别添加四个表，然后把student作为主键其他为外键，右击一个表选择表示图-标准，可以清楚地看到表的属性列名等等。

二、数据更新、视图及用户
1、创建一张history_student表（结构同student），将学生表中数据全部插入到history_student表中

select*into history_student
 from student

2、新插入一门课程，并默认所有学生都选修该门课程插入到成绩表中（成绩为NULL）

insert into course(cname,cno)
values('java','08181069')
insert into score
select sno,'08181069',NULL from student

3、将所有成绩为空的成绩全部修改为60。

update score
set grade='60'
where grade=null

4、将所有平均成绩低于60分的学生成绩记录删除

Update score
set grade=null
where sno in(select sno from score y
where y.sno=sno
group by sno
having avg(grade)<60)

5、创建一个视图VIEW1：查询d01系学生的信息，只投影学生学号，姓名，年龄，系。要求在进行更新 *** 作时满足约束

create view v_student_3
as
select sno,sname,age,Depart
from student
where specialty='001’

6、由VIEW1导出一个视图VIEW2：查询年龄在18到20岁的学生

create view view2
as 
select*
from v_student_1
where age between 18 and 20

7、将查询语句select sno,sname from view2 where ssex=’女’等价转换为对基本表的查询语句并运行结果

Select sno,sname 
from student
 where gender='女' and depart='001' 
and(age between 10 and 20)

8、将VIEW1,VIEW2删除。

Deop view v_student_3
Deop view v_student_4

9、创建登录用户：USER1,USER2,USER3,DBA给USER1授予select,update(sage)权限，并允许其传递授权；USER1授予select,update(sage)权限给USER2，其中select允许传递；USER2授予select权限给USER3,不允许传递；DBA给USER3授予update(sage)权限，不允许其传递授权
10、将第9题中的所有权限逐级回 --9

grant select,update(sage)
on student 
to user1
with grant option
grant select
on student
to user2
with grant option
grant select
on student
to user3
with grant option
--
Exec sp_addlogin 'user1','123','stu'
exec sp_adduser 'user1','user1_a'
Exec sp_addlogin 'user2','123','stu'
exec sp_adduser 'user2','user2_a'
Exec sp_addlogin 'user3','123','stu'

三、数据查询语句
1、查询选修了课程号为"01",“02”,"03"的学号、课程号和成绩记录

select sno,cno,grade 
from score 
where cno IN('08181060','08181170','08181192')

2、查询课程号"01",“02”,"03"除外的，成绩大于60分的学号、课程号和成绩记录

select sno,cno,grade
from score
where cno NOT
IN(’01’，’02’，’03’)
group  by sno,cno,grade
AND  grade>=60

3、查询选修了课程号为"01",“02”,“03”，成绩在70-80分的学号、课程号和成绩记录

select sno,cno,grade
from score
where cno IN(’01’，’02’，’03’）
and grade between 70 and 80

4、查询选修了课程号为01 的最好成绩、最坏成绩、平均成绩

select max(grade),min(grade),avg(grade)
from score
where cno=’01’

5、查询20140101班的男生人数（学号前8位作为班级号）

select count(sno)
from student
where sno like '20158741__'and gender=’男’

6、查询2015班张姓同学的人数

select count(sno)
from student
where sno like '20158741__' and sname like '张%'

7、查询’20140101’班张姓同学的学号、姓名

select sno,snme
from student
where sno like '20158741__'and sname like’张%’

8、查询1980后出生的副教授记录

select *
from teacher
where Age 
9、查询编号为‘0001’教授的授课门数
 
select count(cno)
from coure
where tno='0128'
 
10、查询没有安排授课老师的课程信息
 
select *
from course
where Tno is NULL
 
11、在表score中插入数据，要求每个同学选修3门课以上，
 –每门课至少3个同学选修
 
insert into score
select cno
from score
group by cno
having count(*)>=3 
#Values()
#Insert into score
#Values()
 
12、查询至少选修了3门课的同学的学号和选修课程门数
 
select sno,count(cno)
from score 
group by sno
having count(cno)>=3


 
13、查询学号为101，,102,103三位同学不及格课程门数，查询结果按照学号降序排列
 
select count(distinct cno),sno
from score
where sno IN('2015',2013')
and grade<60
group by All sno
order by sno desc

 
14、查询每个同学的学号、姓名、选修的课程名称、成绩、上课老师姓名，
 –按照学号升序排列结果
 
select student.sno,sname,cname,grade,tname
from  student,course,score,teacher
where student.sno=score.sno AND teacher.tno=course.tno AND course.cno=score.cno
 
15、查询《数据库课程设计》的间接先修课，要求出输出课程编号，课程名称，间接先修课的课程编号和名称
 
select a.cno,a.cname,a.sno,a.cname
from course a,course b,course c
where c.pcno=b.cno and b.pcno=c.p.cno
cname='数据库课程设计'
 
16、查询所有学生的选课信息（包括没有选课的学生）
 
select Student.sno,cno
From student left outer join score on 
student.sno=score.sno
 
17、查询学习课程号为C06的学生与姓名
 
select score.sno,sname
from score,student
where score.sno=student.sno and cno='C06'
 
18、查询至少选修了课程号为C02,C06课程的学号与姓名
 
select  sno 
from score
where cno='C02'
intersect
select sno
from score
where cno='C06' 

 
19、查询学习全部课程的学号
 
select sno
from score
group by sno
having count(sno)=(select count(cno)
from course0

 
20、查询每个学生超过自己选修课程平均成绩的课程号
 
select x.cno,x.sno
from score x where grade>=(select AVG(grade)
from score
where sno=x.sno

 
21、查询平均成绩大于80的课程编号和平均成绩
 
select cno,AVG(grade)
from score
group by cno
HAVINg AVG(grade)>=80
 
22、查询专业specialty中第3个字符为字母“f“”的学号、姓名、专业
 
select sno,sanme,specialty
from student
where specialty like’__f%’
 
23、查询每个同学的学号、最好成绩、最差成绩、平均成绩
 
select sno,max(grade),min(grade),avg(grade)
from score
group by sno
 
24、查询最低分大于70，最高分小于90的同学学号
 
select sno
from score
where min(grade)>70 and max(grade)<90
 
25、查询所有同学的学号、姓名、最好成绩、最差成绩、平均成绩
 
select score.sno,student.sname,max(grade),min(grade),avg(grade)
from student,score
where student.sno=score.sno
group by score.sno,sname
 
26、查询最低分大于70，最高分小于90的学生学号姓名
 
select student.sno,sname
from student
where sno in(select sno
from score
group by sno
having MIN(grade)70 and max(grade)<90)
 
27、查询选修‘0818’课程的成绩高于‘0158’号同学成绩是所有学生学号
 
select sno
from score
where grade>(select grade)
where cno='0818' and cno='0158'
 
28、查询选修‘203‘课程的成绩高于‘03‘号同学成绩的使用学生学号、姓名
 
select  sno, sname
from  student
where sno in(select sno
from score
where grade>(select grade from score 
where cno='203`’ and sno='02')
and  cno='203'
 
29、查询‘何金凤‘同学同岁的使用学生的信息
 select *
 from student
 where age=(select age
 from student
 where sname='何金凤’)
 ’’
 30、查询与张三同龄同班的学生学号
 
select sname
from student
where specialty=(
select specialty
from student
where sanme='张三
and age=(select age
from student
where sname='何金’

 
31、查询成绩比该课程平均成绩低的学号、成绩
 
select x.sno,x.grade
from score x
where x.grade<(select avg(grade) from score
where cno=x.cno)
```、
三、sql高级编程
1、创建存储过程，给定老师姓名，删除学习老师的选课记录。存储过程的参数为老师姓名，返回删除记录的个数。
```c
create proc pp
@xm char(20),@del_num int output
as
begin
declare @tno char(4)
select @tno=tno from techer where tname=@Xm
delete from score
where cno in(select cno from course where @cno=tno)
set @del_num=@@rowcount
END
 
2、创建存储过程，给定老师姓名，把score表中该老师授课创建低于60分的改为0分，返回修改记录的个数
 
create proc pp
@xm char(10),@del_num int output
as 
begin
declare @tno char(10)
select @tno=tno from teacher where tname=@xm
update score
set grade=0
where grade<60 and cno in(select cno from course
where tno=@tno)
set @del_num==@@rowcount
END
 
3、创建存储过程，给定学号、课程号和成绩，如果在score表中有该选课记录，更新该选课的成绩，如果没有，则在score表中插入一条新记录。数据更新成功，返回1，数据更新失败，返回-1
 
create proc pp2
@sno char(10),@cno char(8),@grade int
as
begin
if exists(select*from score where sno=@sno and cno=@cno)
update score
set grade=@grade
where sno=@sno and cno=@cno
else
insert into score
values(@sno,@cno,@grade)
if(@@rowcount=1)
return 1
return -1
end
 
4、创建一个点delete触发器，删除course表中的课程信息时，同时删除score表中相应的选课信息
 
create trigger trtt
on course
 instead of delete
 as 
 begin
 declare @cno char(8)
 select @cno=cno from deleted
 delete from score
 where cno=@cno
 delete from course
 where cno=@cno
 end
 
5、创建一个update触发器，当更新course表中的课程标号cno时，修改score表中相应课程标号cno的信息
 
create trigger trr
on course
 instead of update
 as 
 begin
 declare @cno char(8),@cname char(20),@pcno char(8),@tno char(4)
 select @cno=cno from deleted
 select @cname=cname,@pcno=pcno,@tno=tno
 from course
 where cno=@cno
 declare @new cno char(8)
 select @new cno=cno from inserted
 INsert into course
 values(@new cno,@cname,@pcno,@tno)
 update score
 set cno=@new cno
 where cno=@cno


 delete from course
 where cno=@cno
 end
 
四、查询、删除
 
1、写出Deparment增加一条记录和更新一条记录的sql语句，增加值（‘12’,’研发部’,’张三’）；
 更新dept_id=’12’的记录（‘12’,’研发部’,’张丽’）
 
Insert deparment(dept_id,dept_name,dept_leader)
Values（‘12’,’研发部’,’张三’）
//更新记录
Update deparment set dept_leader=’张丽’ 
Where dept_id=’’12’;
 
2、需要给表department增加一列字段notes。长度为10的字符串，默认值为0
 
Alter table deparment notes var char(10) default0;
 
3、查找工资大于2000的员工记录，并按id升序排列
 
Select name
From person 
Where laryinteger>2000
Order by id;
 
4、查找工资大于2000的员工所在部门、部门编号、部门经理、员工名称
 
Select dept_name,dept_id,dept_leader
From person
Where  laryinteger>2000
 
5、查找张三和李四所在部门，所有人员的姓名
 
Select name
From person
Where name=’’张三’ and name=李四’

 
6、查看每个部门的部门经理和部门人数，按部门人数排列
 
Select  dept_leader
(selectcount(*)
From person
Where dept_id=p.dept_id)
From department,personn
Group by dept_id 
Order by co
 
数据分析
 6、删除表所有记录
 
Delete from department
删除表
Drop table department
 
7、没有选“小易”老师课程的所有学生姓名所需要的程序语句如下：
 
select SNAME from S 
where SNO not in (
 select SNO from SC where CNO in (
 select CNO from C where CTEACHER = "小易“));
 
8、有三门（包括三门）以上课程分数>90的学生姓名及其平均成绩程序语句如下:
 
SELECt S.SNAME, AVG(SC.SCORE) 
FROM S,C,SC 
WHERe S.SNO=SC.SNO AND SC.CNO=C.CNO
GROUP BY S.NAME 
 HAVINg COUNT(SC.SCORE>90)>=3;
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5608305.html