====查看所有表、表结构、库等数据====
exec sp_help //查询所有表
exec sp_help t_student //查看具体表结构
select name from sysobjects where type = 'U' //查询用户表
Create database student_info; --创建库
use student_info; --使用库
sp_helpdb student_info --查看库
alter table t_student modify ssex char(3) //修改表字段类型
exec sp_rename 't_score.sorce','score' //修改表列名
exec sp_help t_student //查看表结构
drop table t_teaher //删除表
=======================================
数据源
Create database student_info; --创建库
use student_info; --使用库
1.建表语句
--学生表
create table t_student(
sid varchar(10),
sname varchar(10),
sage date,
ssex char(3)
)
--教师表
create table t_teacher(
tid varchar(10),
tname varchar(10)
)
--课程表
create table t_course(
cid varchar(10),
cname varchar(10),
tid varchar(10)
)
--分数表
create table t_score(
sid varchar(10),
cid varchar(10),
score float
)
exec sp_help t_teacher --查看表数据
2.测试源数据及例题
================工具Interactive SQL==============================
--学生表
insert into t_student values('01' , '赵雷' , '1990-01-01' , '男')go
insert into t_student values('02' , '钱电' , '1990-12-21' , '男')go
insert into t_student values('03' , '孙风' , '1990-12-20' , '男')go
insert into t_student values('04' , '李云' , '1990-12-06' , '男')go
insert into t_student values('05' , '周梅' , '1991-12-01' , '女')go
insert into t_student values('06' , '吴兰' , '1992-01-01' , '女')go
insert into t_student values('07' , '郑竹' , '1989-01-01' , '女')go
insert into t_student values('09' , '张三' , '2017-12-20' , '女')go
insert into t_student values('10' , '李四' , '2017-12-25' , '女')go
insert into t_student values('11' , '李四' , '2012-06-06' , '女')go
insert into t_student values('12' , '赵六' , '2013-06-13' , '女')go
insert into t_student values('13' , '孙七' , '2014-06-01' , '女')go
select * from t_student go
-- 教师表
insert into t_teacher values('01' , '张三') go
insert into t_teacher values('02' , '李四') go
insert into t_teacher values('03' , '王五') go
select * from t_teacher go
-- 课程表
insert into t_course values('01' , '语文' , '02') go
insert into t_course values('02' , '数学' , '01') go
insert into t_course values('03' , '英语' , '03') go
select * from t_course go
-- 成绩表
insert into t_score values('01' , '01' , 80) go
insert into t_score values('01' , '02' , 90) go
insert into t_score values('01' , '03' , 99) go
insert into t_score values('02' , '01' , 70) go
insert into t_score values('02' , '02' , 60) go
insert into t_score values('02' , '03' , 80) go
insert into t_score values('03' , '01' , 80) go
insert into t_score values('03' , '02' , 80) go
insert into t_score values('03' , '03' , 80) go
insert into t_score values('04' , '01' , 50) go
insert into t_score values('04' , '02' , 30) go
insert into t_score values('04' , '03' , 20) go
insert into t_score values('05' , '01' , 76) go
insert into t_score values('05' , '02' , 87) go
insert into t_score values('06' , '01' , 31) go
insert into t_score values('06' , '03' , 34) go
insert into t_score values('07' , '02' , 89) go
insert into t_score values('07' , '03' , 98) go
select * from t_score go
==========================================
3.例题
01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数（即查询某人语文成绩比数学成绩高）
02）查询同时存在" 01 "课程和" 02 "课程的情况（即选课同时存在语文和数学）
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况,02不存在时显示为 null (即查询存在语文成绩但可能不存在数学成绩的信息)
04）查询不存在" 01 "课程但存在" 02 "课程的情况（不存在语文但存在数学课程）
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
06）查询在t_score表存在成绩的学生信息，没有成绩的不显示/显示 null
======================解析（附有多种解法）==========================
01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数（即查询某人语文成绩比数学成绩高）
select stu.*,s.score 语文成绩,s2.score 数学成绩
from t_student stu
inner join t_score s
on s.sid=stu.sid
inner join t_score s2
on s2.sid=stu.sid
where s.cid='01' and s2.cid='02' and s.score>s2.score
go
02）查询同时存在" 01 "课程和" 02 "课程的情况（即选课同时存在语文和数学）
select stu.*,s.score 语文,s2.score 数学
from t_student stu inner join t_score s
on s.sid=stu.sid
inner join t_score s2
on s2.sid=stu.sid
where s.cid='01' and s2.cid='02'
go
select s1.sid,s1.cid,s2.cid ,s1.score 语文分数,s2.score 数学分数
from t_score s1
inner join t_score s2
on s1.sid=s2.sid
where s1.cid='01' and s2.cid='02'
go
select s1.sid,s1.score 语文分数,s2.score 数学分数,s1.cid,s2.cid
from t_score s1,t_score s2
where s1.sid=s2.sid and s1.cid='01' and s2.cid='02'
go
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况,02不存在时显示为 null (即查询存在语文成绩但可能不存在数学成绩的信息)
select s1.sid,s1.cid 课程ID1,s1.score 语文成绩,s2.cid 课程ID2,s2.score 数学成绩
from t_score s1
left join t_score s2
on s2.sid =s1.sid and s2.cid='02'
where s1.cid='01'
go
04）查询不存在" 01 "课程但存在" 02 "课程的情况（不存在语文但存在数学课程）
--反向查询：先查询存在01课程的学生id，再排除存在01课程的学生id（not in）
select *
from t_score
where sid not in
(select sid from t_score where cid='01')
and cid='02'
go
select *
from t_score s1
where s1.cid='02'
and sid not in
(select sid from t_score where cid in ('01'))
go
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select stu.sid,stu.sname,avg(s.score) avg_score
from t_student stu
inner join t_score s
on stu.sid=s.sid
group by stu.sid
having avg(s.score)>=60
order by avg_score desc
go
select stu.sid,stu.sname,s.avg_score
from t_student stu,
(select avg(score) avg_score,sid
from t_score
group by sid
having avg(score) >=60)s
where s.sid=stu.sid
order by avg_score desc
go
06）查询在t_score表存在成绩的学生信息，没有成绩的不显示/显示 null
--不显示没有成绩的学生（不包含没有成绩的）
select stu.*,s.sum_score 总成绩,s.avg_score 平均成绩,ct 课程数 from t_student stu
inner join
(
select sid,sum(score) sum_score,avg(score) avg_score,count(cid) ct
from t_score
where score >=0 --此条件可省略
group by sid
)s
on s.sid=stu.sid
go
--显示所有学生的成绩（包含没有成绩的）
select stu.*,s.sum_score 总成绩,s.avg_score 平均成绩,ct 课程数 from t_student stu
left join
(
select sid,sum(score) sum_score,avg(score) avg_score,count(cid) ct
from t_score
group by sid
)s
on s.sid=stu.sid
go
================篇幅过长，待续=====================
================附：常见问题处理====================
/*
1.sybase数据库解决中文乱码问题（中文显示问号问题-?）
解决办法：
修改locales.dat
找到[NT]
最后一行的后缀编码修改为 utf8，然后重启sybase数据库
2.表字段无法修改
解决办法：
use master go
sp_dboption 'student_info','select into/bulkcopy/pllsort',true go
*/
=================================================