SQL数据库的常用操作

以下为学习时整理的一些常用SQL语句，便于个人查阅

USE [my_db]  /*使用表my_table*/
 GO

/*模式的操作

 *create schema <name> authorization <user>
 */
 /*create schema my_db*/

 create schema my_db create table mytable(
 col1 smallint,
 col2 int,
 col3 char(10),
 col4 numeric(10,3),
 col5 decimal(5, 2),
 );

 /*模式的删除

  *drop schema <name> <cascade|restrict>
  */
  drop schema my_db
 

/*创建表

 *create table <name> (<col> <type> [约束],..., [<表级约束>]);
 *create table <"schema">.<name>(...);
 */
  create table student(
  sno char(9) primary key,
  sname char(20) unique,
  ssex char(2),
  sage smallint,
  sdept char(20) /*增加外键*/
  );

  create table course(
  cno char(4) primary key,/*课程号*/
  cname char(40), /*课程名*/
  cpno char(4),  /*先修课*/
  ccredit smallint,/*学分*/
  foreign key(cpno) references course(cno)
  );

  create table sc(
  scno char(9),  /*学号*/
  cno char(4), /*课程号*/
  scgrade smallint, /*成绩*/

  primary key(scno,cno),
  foreign key(scno) references student(sno),
  foreign key(cno) references course(cno)

  );

  /*修改基本表

   *alter table <name>
   *[add <colname> <type> [完整性约束]]
   *[drop <完整性约束>]
   *[alter column <colname> <type>];
   */
  alter table course
  alter column cs int
  alter table course add unique(cname)

  alter table sc
  add constraint sa foreign key(scno) references student(sno)
  on update cascade on delete cascade

 /*删除表

  *drop table <tablename>
  */
  alter table sc drop sa /*从表sc中删除约束规则sa*/
  drop sc /*删除表sc*/
  drop table student restrict; /*执行时出现问题*/

  /*视图的操作

   *create view <name> as select * from <tablename> where *
   *删除基本表的同时就会删除视图
   */
   create view as
   select *
   from course
   where cno = '11';

  /*
   alter table sc
   drop constraint student 有错误！！
 
   将Delete Rule 和 Update Rule 改为cascade即可。
   或者直接写SQL语句也可以，在外键关系声明的后面加上
   ON UPDATE CASCADE ON DELETE CASCADE即可*/
   alter table SZ_Picture
   add constraint FK_SZ_PICTU_RELATIONS_SZ_PICTU foreign key (pictureTypeId)
   references SZ_PictureType (pictureTypeId)
   on update cascade on delete cascade

/*索引的建立与删除

 *create [unique] [cluster] index <name>
 * on <tablename> (<colname>[order],...);
 *drop index <name>
 */
 drop index course.courseindex
 create unique index courseindex
 on course(cno asc, cname desc)

/*query database

 *select [top] [all|distinct] <col/exp>,...
 *from <tablename/viewname>,...
 *[where <exp>]
 *[group by <col> [having <exp>] ]
 *[order <col> [asc | desc] ]
 */

/*basic query*/

select sno, sname
from student

/*as: allia name, expr*/
select sname,'year of birth', 2014-sage as birth, UPPER(sdept)
from student

/*distinct the same row*/
select distinct sno
from sc

/*insert usage*/
insert 
into sc(sno, cno, scgrade)
values('200215122','4',99) //外键码值要跟主键值域匹配

/*范围内查找*/

select distinct sno,sname
from student
where ssex not between '男' and '男'

/*in: the value in domain*/

select distinct sno,sname
from student
where ssex in ('男','女')

/* %_: match string*/

select distinct sno,sname,sdept
from student
where sdept  like '%c\_' escape '\'

/*is: query value is null*/

select distinct cno
from course
where cpno is NULL

/*or:  equal in*/

select *
from student
where sdept = 'computer' or sdept = 'chinese'

select *
from student
where sdept in ('computer', 'chinese')

/*aggregate functions
 *count: num of row
 *sum: add values
 *where字句中不能使用聚集函数
 */
/*查询学生总人数*/
select COUNT(sno)
from student

/*查询选修了课程的学生人数*/
select COUNT(distinct sno)
from sc

/*计算1号课程的学生平均成绩*/
select avg(scgrade)
from sc
where cno = '1'

/*查询学生200215122选修课程的总学分数*/
select SUM(ccredit)
from sc,course
where sc.cno = course.cno and sno='200215122'

/*having

 *查询选修了3门以上课程的学生学号
 */
select sno
from sc
group by sno
having count(*) >= 3 /*作用于组不能使用where*/

/*连接查询

 *查询每个学生以及选修课程的情况
 */
select student.*, sc.*
from student,sc
where student.sno = sc.sno

/*查询每一门课的先行课*/
select fir.cno, sec.cpno
from course fir, course sec
where fir.cpno = sec.cno

/*查询选修了2号课程且成绩在90分以上的所有学生*/
select student.*
from student,sc
where sc.cno = '2' and sc.scgrade >= 80 and student.sno = sc.sno

/*查询每个学生的学号、姓名、选修的课程名以及成绩*/
select student.*,sc.*,course.*
from student,sc,course
where  student.sno = sc.sno and sc.cno = course.cno

/*查询与“刘晨”在同一个系学习的学生*/
select student.*
from student
where sdept in (
select sdept
from student
where sname = '刘晨')

select s1.*
from student s1, student s2
where s1.sname = '刘晨' and s1.sdept = s2.sdept

/*查询选修了课程名为“信息系统”的学生学号和姓名*/
select student.sno,sname
from studet
where student.sno in
(
select sc.sno
from sc, course
where sc.cno = course.cno and cname = '信息系统'
)

select sc.sno,sname
from sc, course,student
where sc.cno = course.cno and cname = '信息系统' and student.sno = sc.sno

/*找出每个学生超出他选修课程平均成绩的课程号*/
select sno,cno
from sc sc1
where sc1.scgrade > (
    select AVG(sc2.scgrade)
    from sc sc2
    where sc1.sno = sc2.sno
    )

/*插入数据

 *insert
 *into <tablename> [(<col1>, <col2>,...)]
 *values(<val1>, <val2>,...)
 */

/*将一个学生元组（学号：200215128，姓名：陈东，性别：男，
 *所在系：IS， 年龄：18）插入到student表中*/
insert
into student(sno, sanme, ssex, sdept,sage)
values('200215128', '陈东', '男', 'IS', 18)

/*插入一条选课记录（200215128,1）*/
insert
into sc
values('200215128', '1')

/*对每一个系，求学生的平均年龄，并把结果存入数据库*/
create table dept_sage
(sdept char(3),
 avg_age smallint
)

/*对student表分组求平均年龄，再把系名和平均年龄存入新表*/
insert
into dept_sage(sdept, avg_age)
select sdept, AVG(scgrade)
from sc,student
where sc.sno = student.sno
group by sdept

/*修改数据

 *update <tablename>
 *set <col1>=<val1>,[<col1>=<val2>,...]
 *where <expr>
 */

/*修改学生200215121的年龄为22岁*/
update student
set sage = 22
where sno = '200215121'

/*将所有学生的年龄+1*/
update student
set sage = sage + 1

/*将所有学生的年龄修改为课程分 + 课程学分*/
update sc /*错误*/
set scgrade = scgrade+ccredit
where course.cno = sc.cno and sc.sno = student.sno

update student /*错误*/
set sage = sage + course.ccredit
where sno =
( select sno
  from sc,course
  where course.cno = sc.cno
)

update sc /*正确*/
set scgrade = scgrade+ccredit
from sc,course
where sc.cno=course.cno

/*删除数据

 *delete
 *from <tablename>
 *[where <expr>]
 */
/*删除学号为200215128的学生记录*/
delete
from student
where sno = '200215128'

/*视图

 *create view <viewname> [<col1>, <col2>, ...]
 *as <expr>
 *[with check option]
 */
/*建立信息系学生的视图*/
create view info_view
as
select sno,sname,sage,sdept
from student
where sdept = 'computer'

/*以上，并要求进行修改和插入操作时仍需保证视图只有信息系的学生*/
create view info_view
as
select sno,sname,sage,sdept
from student
where sdept = 'computer'
with check option;
/*建立信息系选修了1号课程的学生视图*/
create view no1_view
as
select distinct student.sno,sname,scgrade
from student,sc
where student.sdept = 'computer' and sc.cno = '1'

/*建立信息系选修了1号课程且成绩在90分以上的学生视图*/
create view above_view
as
select student.sno,sname,sdept
from student,sc
where sc.cno = '1' and scgrade > 90 and sc.sno = student.sno

create view above_view
as
select student.sno,sname,scgrade
from no1_view
where scgrade > 90

/*定义一个反映学生出生年月的视图*/
create view birth_view(sno, sname, birthday)
as
select sno,sname,2013-sage
from student

/*将学生的学号以及他的平均成绩定义为一个视图*/
create view avg_view(sno, avg_grade)
as
select sno, avg(scgrade)
from sc
group by sno

/*将student表中所有女生记录定义为一个视图*/
create view girl_view
as
select *
from sc
where sc.ssex='女'

/*删除视图

 *drop view <viewname> [cascade]
 */
/*删除view1*/
drop view view1

/*查询视图*/
/*在计算机系视图中找出年龄小于20岁的学生*/
select *
from info_view
where sage<20

/*查询选修了1号课程的计算机系学生*/
select sc.sno,sc.cno,sc.scgrade
from info_view,sc
where sc.cno = '1' and info_view.sno = sc.sno

/*查询平均成绩在90分以上的学生学号和平均成绩*/
select *
from avg_view
where avg_grade>90

/*查询超过自己平均成绩的学生*/
select *
from sc x
where scgrade >
(select avg(scgrade)
 from sc y
 /*group by y.sno 使用不对*/
 where x.sno = y.sno
)

/*更新视图

 *insert/delete/update
 */
/*更新200215123学号学生的姓名为刘莉*/
update birth_view
set sname = '刘莉'
where sno='200215123'

/*向生日视图插入一条记录（200215129,赵信,20）*/
insert
into birth_view
values('200215129', '赵信', 20)
/*不能插入，因其包含派生域或常量域。*/

/*删除生日视图中学号为200215128的记录*/
delete
from birth_view
where sno = '200215128'

/*实体完整性

 *create table 用primary key指定主码
 *单属性构成的码：列约束/表级约束
 */
/*将student表中的sno属性定义为主码*/
create table stu1(
 sno char(20) primary key,
 sname char(10) not null,
 ssex char(2),
 sage smallint,
 sdept char(50)
)

/*将sc表中的sno, cno属性定义为主码*/
create table sc1(
 sno char(20),
 cno int,
 scgrade int,
 primary key(sno, cno)
)

/*参照完整性

 *create table 使用foreign key定义外码，使用references定义参照那些主码
 */
/*定义sc表中的参照完整性*/
create table sc2(
sno char(9),
cno char(4),
scgrade int,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
)

/*显式说明参照完整性的违约处理示例*/
create table sc3(
sno char(9),
cno char(4),
scgrade int,
primary key(sno, cno),
foreign key(sno) references student(sno)
on update cascade on delete cascade,
foreign key(cno) references course(cno)
on update cascade on delete cascade
)

/*用户定义完整性

 *列值非空（not null ）
 *列值唯一（unique ）
 *检查列值满足一个布尔表达式（check）
 */
/*在定义sc时，说明sno、cno、scgrade属性不允许取空值*/
create table sc4(
sno char(9) not null,
cno char(4) not null,
scgrade int not null
)

/*建立部门表dept，要求部门名称dname列值唯一，部门编号deptno列为主码*/
create table dept(
deptno char(20) primary key,
dname char(50) unique,
location char(100),
)

/*student表中的ssex只允许取‘男’或‘女’*/
create table stu2(
sno char(9),
snmae char(20),
ssex char(4) not null check(ssex in('男', '女','保密')),
sage smallint,
sept char(20)
)

/*sc表中的scgrade的值应该在0~100之间*/
create table sc5(
sno char(9),
cno char(4),
scgrade int check(scgrade between 0 and 100)
)

/*当学生的性别是男时，其名字不能以Ms.开头*/
create table stu3(
sno char(9),
sname char(20),
ssex char(4),
sage smallint,
sdept char(50),
check(ssex  = '女' or sname not like 'Ms.%' )
)

/*完整性约束命名子句

 *create table constraint完整性约束定义命名
 *constraint <constrname> [primary key expr] [foreign key expr] [check expr]
 */
/*建立学生登记表stu4，要求学号在9000~9999，姓名不能取控制，年龄小于30岁，性别只能是男或女*/
create table stu4(
sno char(9)
constraint a1 primary key check(sno between '9000' and '9999'),
sname char(20),
ssex char(4)
constraint a2 check(ssex in('男','女')),
sage smallint
constraint a3 check(sage<30),
sdept char(20)
)

/*建立教师表teacher，要求每个教师的应发工资不低于3000元*/
create table teacher(
eno numeric(4) primary key,
ename char(10),
job char(8),
sal numeric(7,2),
deduct numeric(7,2),
deptno numeric(7,2),
/*constraint empkey foreign key(deptno) references dept(deptno),*/
constraint c1 check(sal+deuct >= 3000)
)

/*修改表中完整性限制

 *alter table <tablename>
 *drop constraint <constname>
 */
/*去掉stu4表中对性别的限制*/
alter table stu4
drop constraint a2

/*修改表stu4中约束条件要求学号改为在90000~99999之间，年龄由小于30改为小于40*/
alter table stu4
drop constraint a1

alter table stu4
add constraint a1 primary key(sno) check(sno between '90000' and '99999')

alter table stu4
drop constraint a3

alter table stu4
add constraint a3 check(sage < 40)

/*域中完整性（sql 2012不支持域）

 * create domain <domainname> <type>
 * check(expr)
 */
/*建立一个性别域，并声明性别域的取值范围*/
create domain dom1 char(2)
check(values in('男','女'))

/*触发器

 *create trigger <triggername>
 *{before | after} <event>//insert/update/delete
 *on <tablename>
 *for each {row | statement}
 *[when <expr>] //条件为真
 *<action> //PL/SQL过程块，或者是已创建的存储过程
 */
/*定义一个before行级触发器，为教师表teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，则自动调为4000元”*/
create trigger teachsale
before update or insert on teacher
for each row
as begin
 if(new.job = '教授') and (new.sale < 4000)
    new.sale = 4000;
 end if
end