摘记_SQL常用语法
CREATE TABLE STUDENT
(
STUDENT_ID CHAR(5) NOT NULL PRIMARY KEY,
NAME VARCHAR(20) UNIQUE,
GENDER CHAR(10) DEFAULT('MALE'),
BIRTHDAY INT,
SDEPT VARCHAR(15)
);
--创建索引
create index student_name on student(name)
create table course
(
cno char(4) not null primary key,
cname char(40),
cpno char(4),
CCREADIT int,
FOREIGN key(cpno) REFERENCES course(cno)
)
CREATE TABLE SC
(
STUDENT_ID CHAR(5) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(STUDENT_ID,CNO),
FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(STUDENT_ID),
FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
);
--选择某些列、计算表达式、常量、函数、别名
select student_id,name,gender,2012-birthday 年份,'中国人' 国籍,upper(sdept) 系别 from student;
--消除重复的行
select distinct student_id from sc;
--查询练习
select * from student where birthday>21;
select * from student where birthday between 21 and 22;
select * from student where sdept not in('cs','is')
select * from student where name like '张%';
select * from student where name like '张__';
insert into student values('00004','王五','female',24,'ma');
insert into student values('00005','张家明','female',25,'ma');
insert into student(student_id,name) values('00006','张大明');
--空值查询
select * from student where gender is null;
--排序(order by)
select * from student order by birthday asc
select * from student order by birthday desc
select * from student order by name desc;
--聚合函数
select count(*) from student;
select sum(grade) from sc where student_id='00001'
select avg(grade) from sc where student_id = '00001';
select max(grade) from sc where student_id = '00001';
select min(grade) from sc where student_id = '00001';
select * from sc;
select student_id, sum(grade) from sc group by student_id;
select student_id, sum(grade) from sc group by student_id having sum(grade)>140;
select sdept,count(*) from student group by sdept having count(*)>1;
select sdept ,count(*) from student group by sdept;
select gender,count(*) from student where sdept= 'cs' group by gender
select sdept,gender,count(*) from student group by sdept,gender
select sdept,count(*) from student where gender='male' group by sdept having count(*)>1;
--等值连接
select * from student,sc where student.student_id = sc.student_id;
--自身连接
select * from course a,course b where a.cpno = b.cno;
select * from course;
select * from student a,student b
select * from student a,student b where b.name='张三' and a.sdept = b.sdept and a.name!='张三';
select a.name,a.gender from student a,student b where b.name='张三' and a.birthday>b.birthday
select * from sc;
select * from student b;
select * from student a,sc b where a.student_id = b.student_id;
--左连接
select * from student a left outer join sc b on a.student_id = b.student_id;
select * from sc a right join student b on a.student_id = b.student_id;
select * from sc;
--多张表的等值连接
select a.student_id,a.name,b.cname,c.grade from student a,course b,sc c
where a.student_id = c.student_id and c.cno = b.cno
select a.student_id,a.cno,a.grade from sc a,student b where a.student_id = b.student_id and b.name='张三' ;
select b.grade from student a,sc b,course c
where a.student_id = b.student_id and b.cno = c.cno and a.name='张三' and c.cname='高等数学上'
select b.student_id,b.name from sc a,student b,course c
where a.student_id= b.student_id and c.cno = a.cno and c.cname='高等数学上'
select c.cno,c.cname from student a,sc b,course c
where a.student_id = b.student_id and b.cno = c.cno and a.name='张三'
select a.* from student a,sc b where a.student_id = b.student_id and b.cno='c01'
select * from student where student_id in (select student_id from sc where cno='c01');
select * from student where sdept in (select sdept from student where name='张三');
--不相关嵌套查询
select * from student where student_id in
(select student_id from sc where cno in
(select cno from course where cname='高等数学上'));
select * from sc;
--相关嵌套查询
select * from sc x where grade>
(
select avg(grade) from sc y where y.student_id=x.student_id
)
--查询比张三年龄大的学生学号、姓名
select student_id,name from student where birthday>(select birthday from student where name='张三')
--查询选修每门课程中成绩小于其平均成绩的学生学号
select * from sc x
where x.grade<
(
select avg(grade) from sc y where y.cno = x.cno
)
--查询张三选修高等数学1的课程的成绩
select grade from sc where student_id=(select student_id from student where name='张三')
and cno=(select cno from course where cname='高等数学上')
--查询张三选修课程的平均成绩
select avg(grade) from sc where student_id = (select student_id from student where name='张三')
--查询选修课程的平均成绩小于张三平均成绩的学生学号
select student_id, avg(grade) from sc group by student_id having avg(grade)<
(
select avg(grade) from sc where student_id = (select student_id from student where name='张三')
)
--查询课程的平均成绩低于张三平均成绩的课程号
select cno,avg(grade) from sc group by cno having avg(grade)<
(
select avg(grade) from sc where student_id = (select student_id from student where name='张三')
)
--查询选修课程成绩大于等于该课程平均成绩的学生学号
select * from sc x
where x.grade>=
(
select avg(grade) from sc y where y.cno = x.cno
)
select * from
(
select * from student where student_id in
(
select student_id from sc where cno in
(
select cno from sc where student_id=
(
select student_id from student where name='张三'
)
)
)
) a where a.gender='male'
--union
create table stu
(
id char(5),
name char(20),
age int
)
insert into stu values('90001','陈三',21);
insert into stu values('90002','陈四',22);
insert into stu values('90003','陈五',23);
insert into stu(id) values('90004')
--union
--在线视图
select * from
view_a a
where a.age>22;
select * from
view_a a
where a.age>22;
select * from
view_a a
where a.age>22;
select * from
view_a a
where a.age>22;
create view view_a as
select * from stu
union
select student_id,name,birthday from student
select * from student;
insert into stu values('00001','张三',21);
--except
select * from stu
except
select student_id,name,birthday from student
--INTERSECT
select * from stu
INTERSECT
select student_id,name,birthday from student
--获取记录的前3条
select top 3 * from student order by birthday desc;
--复制表
select * into b from student;
select * from b;
--仅仅复制表结构
select * into c from student where 2>10
select * from c;
--批量插入数据
insert into stu select student_id,name,birthday from student
select name from sysobjects where type='U'
--批量初始化表
TRUNCATE TABLE stu
select * from stu
--随机取出某表中2条记录
select top 2 * from student order by NewID()
create table deptage
(
dept char(15),
avgage int
)
insert into deptage select sdept,avg(birthday) from student group by sdept
select * from deptage;
select * from student;
update student set gender='male' ,birthday=23,sdept='ma' where student_id= '00006'
update student set birthday=20;
update student set birthday = birthday+1;
select * from sc;
update sc set grade=grade+10 where
'cs'=(select sdept from student y where y.student_id=sc.student_id);
select * from student;
delete from student where student_id='00006'
delete from sc where student_id= (select student_id from student where name='张三');
create view stu1 as
select student_id,name,gender from student
select * from stu1;
CREATE VIEW IS_STUDENT
AS
SELECT STUDENT_ID,NAME,BIRTHDAY FROM STUDENT WHERE
SDEPT = 'IS';
select * from IS_STUDENT
select * from student;
SELECT STUDENT_ID,NAME,2010-BIRTHDAY
FROM STUDENT;
-------------------------------------------存储过程-游标-触发器-----------------------------------------------
create procedure CheckStudent
as
insert into stu values('80001','zs',23)
select * from stu;
alter procedure CheckStudent @id char(5),@name char(20),@age int
as
insert into stu values(@id,@name,@age)
select * from stu;
------存储过程的定义开始---第一次创建用CREATE，修改用ALTER--------
---多表操作 try catch ;output; set;
alter procedure AddStuInfo @student_id char(5),@name char(20),@cno char(5),
@cname char(20),@grade int,@myerror char(50) output
as
begin try
insert into student(student_id,name) values(@student_id,@name)
insert into course(cno,cname) values(@cno,@cname)
insert into sc values(@student_id,@cno,@grade)
set @myerror='成功！'
return 0
end try
begin catch
set @myerror='主键冲突，新增失败'
return -1
end catch
------存储过程的定义结束-----------
----------------存储过程的执行开始-------------
--定义变量
declare @myerror char(50)
--使用包含output参数
execute AddStuInfo '20002','陈七','c06','工商管理2',90,@myerror output
--显示值
select @myerror
----------------存储过程的执行结束-------------
alter procedure AddStuInfo2
@student_id char(5),--学号
@name char(20), --姓名
@cno char(5), --课程号
@cname char(20), --课程名
@grade int, --成绩
@myerror char(50) output --输出错误信息
as
begin try
insert into student(student_id,name) values(@student_id,@name) --新增学生表
insert into course(cno,cname) values(@cno,@cname) --新增课程表
insert into sc values(@student_id,@cno,@grade) --新增选课表
set @myerror = '执行成功'
return 0
end try
begin catch
set @myerror = '参数错误，请检查'
return -1
end catch
declare @myerror char(50)
execute AddStuInfo2 '91003','陈9','c83','机械工程',60,@myerror output
select @myerror
select * from student;
select * from course;
select * from sc;
execute CheckStudent '10002','ls',21;
-----游标运用的准备，准备一个分数调整表，用于修正学生考试分数
create table AddGrade
(
student_id char(5),
cno char(5),
ModiGrade int
)
insert into AddGrade values('00001','c01',10);
insert into AddGrade values('00001','c02',-10);
insert into AddGrade values('00002','c01',20);
insert into AddGrade values('00002','c02',10);
select * from AddGrade;
select * from sc;
-----创建包含游标的存储过程，逐条读取和更新分数
create procedure ModiGrade
as
--申明变量
declare @student_id char(5),
@cno char(5),
@ModiGrade int
--申明游标
declare mycursor cursor for select student_id,cno,ModiGrade from AddGrade
--打开游标
open mycursor
--将游标里的值放置到变量里
fetch next from mycursor into @student_id,@cno,@ModiGrade
while(@@fetch_status=0)
begin
update sc set grade=grade+@ModiGrade where student_id=@student_id and cno=@cno
fetch next from mycursor into @student_id,@cno,@ModiGrade
end
close mycursor --关闭游标
deallocate mycursor --释放游标
---执行此存储过程
exec ModiGrade
create table SC_HIS
(
student_id char(5),
cno char(5),
grade int
)
create trigger SCDelete on sc for delete
as
insert into sc_his select * from deleted;
alter trigger SCInsert on sc instead of insert
as
insert into student(student_id) select student_id from inserted
insert into course(cno) select cno from inserted
insert into sc select * from inserted
insert into sc values('70002','c88',90)
select * from student;
select * from course;
select * from sc;
select * from sc_his;
delete from sc where student_id='91002'
