sql server 2000自己整理的笔记

SQlServer数据库：

--创建数据库 school
create database school

--删除数据库
drop database school

go--可以派出先后执行顺序

--更改当前操纵的数据库
use school

go

--创建教师表-->主表
create table teacher
(
 class_name varchar(20) primary key,
 tea_name varchar(20) not null,
 t_time  datetime default(getdate())
)

go

--创建学生表-->从表
create table student
(
 stu_id  int  identity(1,1)  primary key,
 stu_name varchar(20) not null,
 sex  char(2)  check(sex='男' or sex='女'),
 score  int  default(0),
 class_name varchar(20) foreign key references teacher(class_name)--class_name字段必须是teacher表的主键
)

go

--添加语句insert
insert into teacher(class_name,tea_name)values('s123','张老师')
insert into teacher values('s456','郭老师','2008-1-2 15:51:30')
insert into teacher(class_name,tea_name)values('t123','李老师')
insert into teacher(class_name,tea_name)values('t456','赵老师')
go
insert into student(stu_name,sex,score,class_name)values('李刚','男',68,'s123')
insert into student(stu_name,sex,score,class_name)values('李小明','男',80,'t123')
insert into student(stu_name,sex,score,class_name)values('李大刚','男',97,'t456')
insert into student(stu_name,sex,score,class_name)values('王菲','女',100,'t123')
insert into student(stu_name,sex,score,class_name)values('赵刚','男',90,'s123')
insert into student(stu_name,sex,score,class_name)values('李宁','男',85,'t123')
insert into student(stu_name,sex,score,class_name)values('赵姗','女',56,'s456')
insert into student(stu_name,sex,score,class_name)values('郭蕊','女',78,'t456')
insert into student(stu_name,sex,score,class_name)values('孙小旭','男',60,'s123')
insert into student(stu_name,sex,score,class_name)values('孙津','女',72,'t123')
insert into student(stu_name,sex,score,class_name)values('丁磊','男',23,'t456')

--更新语句
update student set score=80 where stu_name='孙津'
update student set score=81,class_name='t456' where stu_name='孙津'

--删除语句
delete from student--清空表
truncate table student--清空表   不走日志  快
delete from student where stu_name='丁磊'
delete from student where stu_name='丁磊' and stu_name='孙津'

--查询语句
select * from teacher
select * from student

select * from student where stu_name='李刚'
select stu_name,score from student where stu_name='李刚'
select * from student where score!=60
select * from student where score<>60
select * from student where score>60 and sex='女'

--排序 order by
select * from student order by score  --升序
select * from student order by score desc  --降序
select * from student order by stu_name
select * from student order by stu_name,score

--聚合函数 sum、avg、max、min、count
--所有学生成绩总和
select sum(score) as 总和 from student
--所有学生成绩平均分
select avg(score) as 平均分 from student
--s123班级所有学生成绩的平均分
select avg(score) as 平均分 from student where class_name='s123'
--最高分
select max(score) as 最高分 from student
--最低分
select min(score) as 最低分 from student
--参加考试学生人数
select count(*) as 总数 from student
select count(*) as 总数 from student where score<>0

--分组函数 group by   配合着聚合函数一起使用
--注意：查询结果中出现的列，要么出现在聚合函数中，要么出现在group by中
--所有男生和女生的平均分
select avg(score)as 平均分,sex from student group by sex
--查询所有学生的成绩总和，但是去掉不及格的学生成绩
select sum(score) from student where score>=60
--查询每个班级所有学生的平均分
select avg(score) from student group by class_name
--查询班级的个数，平均分不小于60的班级的个数
select count(*) as 学生人数,class_name from student group by class_name having avg(score)>=60
--注意：where、having后边都可以跟条件
--where: 普通的条件查询
--having: 一般都是配合着聚合函数、分组函数

 

--模糊查询 like
--  %  代表任意长度的字符
--  _ 代表任意一个字符
--  [] 代表任意一个位置，可以是多个字符
--查询所有姓李的学生
select * from student where stu_name like '李%'
--查找所有姓李的学生，名字有两个字组成
select * from student where stu_name like '李_'
--查找所有姓张和姓李的学生
select * from student where stu_name like '[李郭]%'
--名字中代“小”
select * from student where stu_name like '%小%'

--多表查询
--内联接 inner join...on (将多张表的字段合为一张表)
--查询张老师班级所有的学生
select * from student inner join teacher
on
student.class_name=teacher.class_name and teacher.tea_name='张老师'

--外联接 outer join...on
  --左外连接 left outer join...on (以左表为主)
--查询张老师班级所有的学生
select * from student s left outer join teacher t
on
s.class_name=t.class_name and t.tea_name='张老师'

  --右外连接 right outer join...on (右表为主)
select * from student s right outer join teacher t
on
s.class_name=t.class_name and t.tea_name='张老师'

  --全外联接 full outer  join...on 
select * from student s full outer join teacher t
on
s.class_name=t.class_name and t.tea_name='张老师'

--子查询 (在查询条件中还包含另外一个sql语句)
select * from student where class_name=(select class_name from teacher where tea_name='张老师')

select * from student where class_name in
(select class_name from teacher where tea_name='张老师' or tea_name='李老师')

select * from student where class_name !=
(select class_name from teacher where tea_name='张老师')

select * from student where class_name not in
(select class_name from teacher where tea_name='张老师' or tea_name='李老师')

 

--联合 union (把多张表的记录合为一张表)

--去掉重复记录
select class_name from student
union
select class_name from teacher

--所有记录全部显示
select class_name from student
union all
select class_name from teacher

 

--多表查询 (相同的字段相等)
select * from student,teacher where
 teacher.tea_name='张老师' and student.class_name=teacher.class_name

--pubs案例数据库  图书作者
sp_tables  --显示该数据库所有的表名
select * from authors   --作者
select * from titles  --图书
select * from titleauthor  --图书作者

--查询Johnson作者的所有的图书
select a.au_id,b.title from authors a,titles b,titleauthor c
 where a.au_id=c.au_id and b.title_id=c.title_id and a.au_fname='Marjorie'

--northwind   订单查询
sp_tables
select * from customers  --客户表
select * from orders  --订单表
select * from products  --产品表
select * from [order details]  --订单明细表

--查询Alfreds Futterkiste订购的所有商品
select a.CustomerID,c.ProductName from Customers a,Orders b,Products c,[Order Details] d
 where a.CustomerID=b.CustomerID and b.OrderID=d.OrderID and
 c.ProductID=d.ProductID and a.CompanyName='Alfreds Futterkiste'

--视图 view  视图一旦创建，始终存在   ---表
--创建视图
create view myview
as
select a.CustomerID,c.ProductName from Customers a,Orders b,Products c,[Order Details] d
 where a.CustomerID=b.CustomerID and b.OrderID=d.OrderID and
 c.ProductID=d.ProductID and a.CompanyName='Alfreds Futterkiste'
go

--修改视图
alter view myview
as
select * from orders
go

--删除视图
drop view myview

select * from myview where productName='Spegesild'

--触发器 trigger
--创建
create trigger mytri
on student
for delete
as
select * from student
go

--修改
alter trigger mytri
on student
for insert,delete,update
as
select * from student
go

--删除
drop trigger mytri

delete from student where stu_id=1
update student set score=100 where stu_name='李小明'

 

--存储过程 proc
--创建
create proc myproc
as
select * from student
go

alter proc myproc @stuId int
as
select * from student where stu_id > @stuId
go

exec sp_tables  --系统存储过程
exec myproc
myproc 6