1.默认约束,3个核心元素分别是

作者: www.9159.com  发布:2019-11-09

先创建Students表

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBField)) from TableName;

1.默认约束
2.主键约束,(唯一约束+非空约束),
设置了主键的字段在赋值的时候不能重复,stuID为主键则1,下一个就不能为1
3.唯一约束(允许一行为空),
4.check约束
5.主外建约束 (主表 子表)一对多

/*
FROM CSDN
说明:以下五十个语句都按照测试数据进行过测试,最好每次只单独运行一个语句。
问题及描述:
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)
go

CREATE TABLE [dbo].[Students](
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL,
[name] [nvarchar](50) NULL,
[addTime] [datetime]  NULL
) ON [PRIMARY]

 

添加完整性约束
外键约束:
学生基本信息表(主表)的学生编号是主键
考试成绩表的学生(子表)的编号是外键
一个表在不同的关系中既可以是主表又可以是子表
主外建是一对多

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score > c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where b.score > isnull(c.score,0)

 

图片 1图片 2出处:

”数据字典“

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where isnull(b.score,0) < c.score

插入几条测试数据

oracle分析函数 

哪一个是外键表,就给他添加外建约束
3个一致:1 外键表外键与主键数据类型一致(语法一只)
2 长度:外键>=主键表;长了也没有意义
3 名称一致。

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.S#

INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (22, N'李四', '2015-04-08 01:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (8, N'李四', '2017-05-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (98, N'李四', '2017-10-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (34, N'张三', '2016-09-08 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (45, N'张三','2011-05-08 00:00:00.000')
INSERT [dbo].[Students] ( [age], [name], [addTime]) VALUES (5, N'张三', '2014-04-01 00:00:00.000')

=========================================================== 
作者: zhouwf0726()
发表于:2006.07.25 12:51
分类: oracle开发 
出处:
--------------------------------------------------------------- 

如果sql语句写的错误很多,只需要看第一行和最后一行错误。
如果不多,就仔细看下。

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.S#
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score
from Student a left join sc b
on a.S# = b.S#
group by a.S# , a.Sname
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.S#

第一种写法:

oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句 

通配符----

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a , SC b
where a.S# = b.S#
group by a.S#,a.Sname
order by a.S#
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a left join SC b
on a.S# = b.S#
group by a.S#,a.Sname
order by a.S#

  这种写法用到了窗口函数,窗口函数的行为描述出现在函数的OVER子句中,并涉及多个元素,3个核心元素分别是:分区,排序和框架

--CREATE TEST TABLE AND INSERT TEST DATA.
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));

select * from employee
-- 插入一条忘记加入的字段 直接在表的后面添上add
--alter table employee add emName varchar(20) not null
alter table employee
add constraint PK_emId primary key (emId)
alter table employee
add constraint CK_age CHECK(age between 20 and 55)
alter table employee
add constraint DF_address default('地址不详') for address
alter table employee
add constraint UQ_emName unique (emName)
--外键表
alter table employee
add constraint FK_emtypeId
foreign key(emtypeId) references mangertype(mantypeId)
标示列可以不是主键,种子和自增量必须是int,标示列不能编辑
先自增,再验证,最后插入
在数据关系图里 带钥匙的一方是主键表

--6、查询"李"姓老师的数量

select distinct name,
maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name) maxAddTime ,
minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name) minAddTime
from (
select name,addtime,
max(age) over(partition by name) maxAge,
min(age) over(partition by name) minAge,
RANK() over(partition by name order by age desc) maxAgeNum ,
RANK() over(partition by name order by age ) minAgeNum from students
) s

insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;


--方法1
select count(Tname) ["李"姓老师的数量] from Teacher where Tname like N'李%'
--方法2
select count(Tname) ["李"姓老师的数量] from Teacher where left(Tname,1) = N'李'
/*

第二种写法:

col score format 999999999999.99

插入(insert)
1.匹配,
2.多行数据
备份表 select * into student1 from student
就生成一个新的student1

"李"姓老师的数量  

1
*/

--7、查询学过"张三"老师授课的同学的信息
select distinct Student.* from Student , SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三'
order by Student.S#

--8、查询没学过"张三"老师授课的同学的信息
select m.* from Student m where S# not in (select distinct SC.S# from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三') order by m.S#

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#
--方法2
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '02' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '01') order by Student.S#
--方法3
select m.* from Student m where S# in
(
  select S# from
  (
    select distinct S# from SC where C# = '01'
    union all
    select distinct S# from SC where C# = '02'
  ) t group by S# having count(1) = 2
)
order by m.S#

--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and not exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#
--方法2
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and Student.S# not in (Select SC_2.S# from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#

--11、查询没有学全所有课程的同学的信息
--11.1、
select Student.*
from Student , SC
where Student.S# = SC.S#
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course)
--11.2
select Student.*
from Student left join SC
on Student.S# = SC.S#
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course)

--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct Student.* from Student , SC where Student.S# = SC.S# and SC.C# in (select C# from SC where S# = '01') and Student.S# <> '01'

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select Student.* from Student where S# in
(select distinct SC.S# from SC where S# <> '01' and SC.C# in (select distinct C# from SC where S# = '01')
group by SC.S# having count(1) = (select count(1) from SC where S#='01'))

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select student.* from student where student.S# not in
(select distinct sc.S# from sc , course , teacher where sc.C# = course.C# and course.T# = teacher.T# and teacher.tname = N'张三')
order by student.S#

--15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

select student.S# , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc
where student.S# = SC.S# and student.S# in (select S# from SC where score < 60 group by S# having count(1) >= 2)
group by student.S# , student.sname

--16、检索"01"课程分数小于60,按分数降序排列的学生信息
select student.* , sc.C# , sc.score from student , sc
where student.S# = SC.S# and sc.score < 60 and sc.C# = '01'
order by sc.score desc 

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--17.1 SQL 2000 静态
select a.S# 学生编号 , a.Sname 学生姓名 ,
       max(case c.Cname when N'语文' then b.score else null end) [语文],
       max(case c.Cname when N'数学' then b.score else null end) [数学],
       max(case c.Cname when N'英语' then b.score else null end) [英语],
       cast(avg(b.score) as decimal(18,2)) 平均分
from Student a
left join SC b on a.S# = b.S#
left join Course c on b.C# = c.C#
group by a.S# , a.Sname
order by 平均分 desc
--17.2 SQL 2000 动态
declare @sql nvarchar(4000)
set @sql = 'select a.S# ' + N'学生编号' + ' , a.Sname ' + N'学生姓名'
select @sql = @sql + ',max(case c.Cname when N'''+Cname+''' then b.score else null end) ['+Cname+']'
from (select distinct Cname from Course) as t
set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) '

  • N'平均分' + ' from Student a left join SC b on a.S# = b.S# left join Course c on b.C# = c.C#
    group by a.S# , a.Sname order by '
  • N'平均分' + ' desc'
    exec(@sql)
    --17.3 有关sql 2005的动静态写法参见我的文章《普通行列转换(version 2.0)》或《普通行列转换(version 3.0)》。

--18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
--及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
--方法1
select m.C# [课程编号], m.Cname [课程名称],
  max(n.score) [最高分],
  min(n.score) [最低分],
  cast(avg(n.score) as decimal(18,2)) [平均分],
  cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [中等率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优良率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]
from Course m , SC n
where m.C# = n.C#
group by m.C# , m.Cname
order by m.C#
--方法2
select m.C# [课程编号], m.Cname [课程名称],
  (select max(score) from SC where C# = m.C#) [最高分],
  (select min(score) from SC where C# = m.C#) [最低分],
  (select cast(avg(score) as decimal(18,2)) from SC where C# = m.C#) [平均分],
  cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [中等率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优良率(%)],
  cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]
from Course m
order by m.C#

--19、按各科成绩进行排序,并显示排名
--19.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc t order by t.c# , px
--Score重复时合并名次
select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t order by t.c# , px
--19.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select t.* , px = rank() over(partition by c# order by score desc) from sc t order by t.C# , px
--Score重复时合并名次(DENSE_RANK完成)
select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t order by t.C# , px

--20、查询学生的总成绩并进行排名
--20.1 查询学生的总成绩
select m.S# [学生编号] ,
       m.Sname [学生姓名] ,
       isnull(sum(score),0) [总成绩]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
order by [总成绩] desc
--20.2 查询学生的总成绩并进行排名,sql 2000用子查询完成,分总分重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t2 where 总成绩 > t1.总成绩) + 1 from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t1
order by px

select t1.* , px = (select count(distinct 总成绩) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t2 where 总成绩 >= t1.总成绩) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t1
order by px
--20.3 查询学生的总成绩并进行排名,sql 2005用rank,DENSE_RANK完成,分总分重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by [总成绩] desc) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t
order by px

select t.* , px = DENSE_RANK() over(order by [总成绩] desc) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(sum(score),0) [总成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t
order by px

--21、查询不同老师所教不同课程平均分从高到低显示

select m.T# , m.Tname , cast(avg(o.score) as decimal(18,2)) avg_score
from Teacher m , Course n , SC o
where m.T# = n.T# and n.C# = o.C#
group by m.T# , m.Tname
order by avg_score desc

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--22.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select * from (select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc t) m where px between 2 and 3 order by m.c# , m.px
--Score重复时合并名次
select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t) m where px between 2 and 3 order by m.c# , m.px
--22.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select * from (select t.* , px = rank() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# , m.px
--Score重复时合并名次(DENSE_RANK完成)
select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# , m.px

--23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

--23.1 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
--横向显示
select Course.C# [课程编号] , Cname as [课程名称] ,
  sum(case when score >= 85 then 1 else 0 end) [85-100],
  sum(case when score >= 70 and score < 85 then 1 else 0 end) [70-85],
  sum(case when score >= 60 and score < 70 then 1 else 0 end) [60-70],
  sum(case when score < 60 then 1 else 0 end) [0-60]
from sc , Course
where SC.C# = Course.C#
group by Course.C# , Course.Cname
order by Course.C#
--纵向显示1(显示存在的分数段)
select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end) ,
  count(1) 数量
from Course m , sc n
where m.C# = n.C#
group by m.C# , m.Cname , (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end)
order by m.C# , m.Cname , 分数段
--纵向显示2(显示存在的分数段,不存在的分数段用0显示)
select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end) ,
  count(1) 数量
from Course m , sc n
where m.C# = n.C#
group by all m.C# , m.Cname , (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end)
order by m.C# , m.Cname , 分数段

--23.2 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[<60]及所占百分比

--横向显示
select m.C# 课程编号, m.Cname 课程名称,
  (select count(1) from SC where C# = m.C# and score < 60) [0-60],
  cast((select count(1) from SC where C# = m.C# and score < 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)],
  (select count(1) from SC where C# = m.C# and score >= 60 and score < 70) [60-70],
  cast((select count(1) from SC where C# = m.C# and score >= 60 and score < 70)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)],
  (select count(1) from SC where C# = m.C# and score >= 70 and score < 85) [70-85],
  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 85)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)],
  (select count(1) from SC where C# = m.C# and score >= 85) [85-100],
  cast((select count(1) from SC where C# = m.C# and score >= 85)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)]
from Course m
order by m.C#
--纵向显示1(显示存在的分数段)
select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end) ,
  count(1) 数量 , 
  cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)]
from Course m , sc n
where m.C# = n.C#
group by m.C# , m.Cname , (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end)
order by m.C# , m.Cname , 分数段
--纵向显示2(显示存在的分数段,不存在的分数段用0显示)
select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end) ,
  count(1) 数量 , 
  cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)]
from Course m , sc n
where m.C# = n.C#
group by all m.C# , m.Cname , (
  case when n.score >= 85 then '85-100'
       when n.score >= 70 and n.score < 85 then '70-85'
       when n.score >= 60 and n.score < 70 then '60-70'
       else '0-60'
  end)
order by m.C# , m.Cname , 分数段

--24、查询学生平均成绩及其名次

--24.1 查询学生的平均成绩并进行排名,sql 2000用子查询完成,分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (select count(1) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t2 where 平均成绩 > t1.平均成绩) + 1 from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t1
order by px

select t1.* , px = (select count(distinct 平均成绩) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t2 where 平均成绩 >= t1.平均成绩) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t1
order by px
--24.2 查询学生的平均成绩并进行排名,sql 2005用rank,DENSE_RANK完成,分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(order by [平均成绩] desc) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t
order by px

select t.* , px = DENSE_RANK() over(order by [平均成绩] desc) from
(
  select m.S# [学生编号] ,
         m.Sname [学生姓名] ,
         isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩]
  from Student m left join SC n on m.S# = n.S#
  group by m.S# , m.Sname
) t
order by px
 
--25、查询各科成绩前三名的记录
--25.1 分数重复时保留名次空缺
select m.* , n.C# , n.score from Student m, SC n where m.S# = n.S# and n.score in
(select top 3 score from sc where C# = n.C# order by score desc) order by n.C# , n.score desc
--25.2 分数重复时不保留名次空缺,合并名次
--sql 2000用子查询实现
select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t) m where px between 1 and 3 order by m.c# , m.px
--sql 2005用DENSE_RANK实现
select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 1 and 3 order by m.C# , m.px

--26、查询每门课程被选修的学生数

select c# , count(S#)[学生数] from sc group by C#

--27、查询出只有两门课程的全部学生的学号和姓名

select Student.S# , Student.Sname
from Student , SC
where Student.S# = SC.S#
group by Student.S# , Student.Sname
having count(SC.C#) = 2
order by Student.S#

--28、查询男生、女生人数

select count(Ssex) as 男生人数 from Student where Ssex = N'男'
select count(Ssex) as 女生人数 from Student where Ssex = N'女'
select sum(case when Ssex = N'男' then 1 else 0 end) [男生人数],sum(case when Ssex = N'女' then 1 else 0 end) [女生人数] from student
select case when Ssex = N'男' then N'男生人数' else N'女生人数' end [男女情况] , count(1) [人数] from student group by case when Ssex = N'男' then N'男生人数' else N'女生人数' end

--29、查询名字中含有"风"字的学生信息
select * from student where sname like N'%风%'
select * from student where charindex(N'风' , sname) > 0

--30、查询同名同性学生名单,并统计同名人数
select Sname [学生姓名], count(*) [人数] from Student group by Sname having count(*) > 1

--31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)

select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0
select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'

--32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列

select m.C# , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score
from Course m, SC n
where m.C# = n.C#   
group by m.C# , m.Cname
order by avg_score desc, m.C# asc

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85
order by a.S#

--34、查询课程名称为"数学",且分数低于60的学生姓名和分数

select sname , score
from Student , SC , Course
where SC.S# = Student.S# and SC.C# = Course.C# and Course.Cname = N'数学' and score < 60

--35、查询所有学生的课程及分数情况;
select Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C#
order by Student.S# , SC.C#

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

select Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score >= 70
order by Student.S# , SC.C#

--37、查询不及格的课程
select Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score < 60
order by Student.S# , SC.C#

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;

select Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.C# = '01' and SC.score >= 80
order by Student.S# , SC.C#

--39、求每门课程的学生人数

select Course.C# , Course.Cname , count(*) [学生人数]
from Course , SC
where Course.C# = SC.C#
group by  Course.C# , Course.Cname
order by Course.C# , Course.Cname

--40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
--40.1 当最高分只有一个时
select top 1 Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三'
order by SC.score desc
--40.2 当最高分出现多个时
select Student.* , Course.Cname , SC.C# , SC.score 
from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三')

--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

--方法1
select m.* from SC m ,(select C# , score from SC group by C# , score having count(1) > 1) n
where m.C#= n.C# and m.score = n.score order by m.C# , m.score , m.S#
--方法2
select m.* from SC m where exists (select 1 from (select C# , score from SC group by C# , score having count(1) > 1) n
where m.C#= n.C# and m.score = n.score) order by m.C# , m.score , m.S#

--42、查询每门功成绩最好的前两名

select t.* from sc t where score in (select top 2 score from sc where C# = T.C# order by score desc) order by t.C# , t.score desc

--43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 

select Course.C# , Course.Cname , count(*) [学生人数]
from Course , SC
where Course.C# = SC.C#
group by  Course.C# , Course.Cname
having count(*) >= 5
order by [学生人数] desc , Course.C#

--44、检索至少选修两门课程的学生学号
select student.S# , student.Sname
from student , SC
where student.S# = SC.S#
group by student.S# , student.Sname
having count(1) >= 2
order by student.S#

--45、查询选修了全部课程的学生信息

--方法1 根据数量来完成
select student.* from student where S# in
(select S# from sc group by S# having count(1) = (select count(1) from course))
--方法2 使用双重否定来完成
select t.* from student t where t.S# not in
(
  select distinct m.S# from
  (
    select S# , C# from student , course
  ) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
)
--方法3 使用双重否定来完成
select t.* from student t where not exists(select 1 from
(
  select distinct m.S# from
  (
    select S# , C# from student , course
  ) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
) k where k.S# = t.S#
)

--46、查询各学生的年龄
--46.1 只按照年份来算
select * , datediff(yy , sage , getdate()) [年龄] from student
--46.2 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [年龄] from student

--47、查询本周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0

--48、查询下周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1

--49、查询本月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0

--50、查询下月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1

drop table  Student,Course,Teacher,SC

with s as
(
select name,max(age) maxAge,min(age) minAge from students
group by name
)
select name,max(maxAge) maxAge,max(maxAgeTime) maxAgeTime,max(minAge) minAge,max(minAgeTime) minAgeTime from (
select ss.name,s.maxAge,ss.addTime maxAgeTime,0 minAge, '' minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.maxAge
union all
select ss.name,0 maxAge , '' maxAgeTime,s.minAge minAge,ss.addTime minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.minAge
) a group by name

--A、GROUPING SETS

select * into student1 from student
select * from student1
--将字段名备份,但是所建的约束消失
select stuNoid,stuName,sex,age,address into student2 from student
select * from student2
--将字段名更改,加as即可
select stuNoid as haha,stuName as gaga,sex as hello,age,address into student3 from student
select * from student3
select * from student1
use wangwang
go
create table employee
(
empId int identity primary key,
empName varchar(20) not null,
sex bit not null,
age int ,
address varchar(20)

 

select id,area,stu_type,sum(score) score 
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;

)
go
select * from employee
---接下来将student1表中的数值插入到employee中
/*insert into employee
(empId,empName,sex,age,address)
select
(stuNoId,stuName,sex,age,address)
from stdent1
where age>20
*/
----插入多行数据union方法
insert student1
(stuNoId,stuName,sex,age,address)
select--不支持default关键字要用‘’括起来,当两行数据一致时,默认为一行
'1','liuzhao','1','31','default' union
select
'1','liu','1','28','default' union
select
'1','zhao','1','31','default'
select * from student1

结果如下图:

/*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )

select * from student1 order by age desc(asc)

模糊查询
alter table student add groupId int
select * from student
insert student
(stuNoId,stuName,sex,age,address)
select
'5','zhaoliu','1','30','南昌路' union
select
'6','zhao','1','31','南路' union
select
'7','liu','1','32','昌路'

---模糊查询
select * from student where address like '%南%'
select * from student where stuName like 'wangwu[2-4]'
select * from student where stuName like 'wangwu[^2-4]%'--取反
--加上% 可以将wangwua查出来

 图片 3

等效于

select * from student where stuName like 'wangwu[^2-4]%'

use wangwang
go
create table person
(
perId int identity primary key,
personId int not null--身份证
)
go
--给身份证添加约束,假如身份证五位数字
/*select * from person
alter table person
add constraint CK_personId CHECK(personId like [0-4],[0-4],[0-4],[0-4],[0-4])
go*/
--更改
update student set age=age+1
select * from student where groupId=1
update student set age=age+2,stuName=stuName+'mm'
where groupId=1
--删除
delete from student where groupId=1
select * from student

delete from student-- 删除表

 

select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b 
union all
select null, null, c, sum( d ) from t group by c 
)
*/

truncate table student--删除表(效率快 )

SQL 结构化查询语言

图片 4

--B、ROLLUP

T-Sql是SQL的加强版

查询:
1.基础查询2.聚合函数3.链接查询4.子查询
--更改
update student set age=age+1
select * from student where groupId=1
update student set age=age+2,stuName=stuName+'mm'
where groupId=1
--删除
delete from student where groupId=1
select * from student

delete from student-- 删除表
truncate table student--删除表(效率快 )
-------------------------------查询
use wangwang
go
select * from student where address='南昌路'
/*select
colname1 as 别名,
colname2 as 别名,
from
tableName
where
条件
group by
分组
having
分组后的筛选
order by
排序
*/
--起别名

select *,'姓名'=stuName from student
select *,stuName as 'as用法' from student
--给表起名
select *,stuName as 'as用法' from student as Stu
alter table student add haha varchar(20)
select * from student
update student set haha='mm' where age>31
select * from student where haha is null
--string a=null与a=''是不一样的 null与空
select * from student where haha is null
select * from student where haha=''
select * from student order by stuNoId asc
-------------------分页处理,关键字--top--percent
select top 5 * from student--每页出现5行
select top 50 percent * from student--每页出现总体的百分之n,进1法
--排序
select * from student order by age desc--降序号
select * from student order by stuNoId asc--升序
-----函数。
select 'aaa' as '列明'
--查询an在后面的英语中出现的序列
select CHARINDEX('an','my name is wangwang')
--查询an在后面的英语中出现的序列,前面14个不算,从第15开始查找
select CHARINDEX('an','my name is wangwang',14)
-----查询长度
select LEN('123456')
select address,LEN(address) as ' cahngdu' from student
-----------ltrim()可取空格
select * from student where ltrim(address)=' 天津路'--左空格
select * from student where ltrim(ltrim(address))=' 天津路'--俩边空各
---截取字符串right
select RIGHT('sgdsggueui',4) as '截取后显示'
select left('sgdsggueui',4) as '截取后显示'
----------替换
select replace('sfsfhufhsh','sh','AA')--将sh换成AA
select replace('sfsfhufhsh','s','9')--将sh换成AA
------------日期函数
select GETDATE()--得到当前日期
select DATEADD(DD,10000,'93-07-22')--活一万天
select DATENAME(DW,GETDATE())--今天星期几
select DATENAME(WW,GETDATE())--今天是今年的第几周
select * from student
select convert(varchar(2),age)+address as '新的列明' from student
select age + convert( int,address) as '新的列明' from student--有错误


案例1
create table card
(
cId int identity primary key,
CaId varchar(20) not null
)
go
insert card values ('fifuejf789io0i658')

select * from card
update card set CaId = replace(CaId,'i','1')

--update replace(CaId,'i','1') from card

select REPLACE(replace(CaId,'i','1'),'o','0') from card
select * into card2 from card
select * from card2

根据上面又延伸出来一个新需求。

select id,area,stu_type,sum(score) score 
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;

update card2 set card=REPLACE(REPLACE(CaId,'i','1'),'o','0')

SQL编程
变量分为:1.局部变量,以@符号前缀,先声明再赋值
赋值:set @变量名=值 select @变量名=值
set @name=zhangsan
select studentNo from student where studentName=@name
李文才:
declare @name varchar(8)
set @name='李文才'
select @No=studentNo from student where studentName=@name

declare @No int//定义
print'学号'+@No
查询出学号+-1的相邻的同学。
select * from student where student.studentNo=@No+1 or student.studentNo=@No-1


insert into Class(GradeId,ClassID) values (4,'201')

求一天之内的最大值最小值   最大值时间最小值时间

/*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);

@@IDENTITY

DECLARE @TAG VARCHAR(1)
SET @TAG='*'
PRINT @TAG+@TAG
PRINT @TAG+@TAG+@TAG
PRINT @TAG+@TAG+@TAG+@TAG
------DECLARE @xuehao int
set @xuehao =20011
select * from student where studentNo=@xuehao//学生记录查出来

declare @year int
set @year=DateDiff(DY,'1996-8-7',getdate())/365;年份
select * from student where student.brondate=@year+1 or student.brondate=@year-1;


create database MySchool
on primary
(
name = 'MySchool_data',
filename = 'D:projectMySchool_data',
size = 10mb,
maxsize = 100mb,
filegrowth=15%
)
log on
(
name = 'MySchool_log',
filename = 'D:projectMySchool_log',
size = 3mb,
maxsize = 20mb,
filegrowth=1mb
)

if exists(select * from sysobjects where name = 'Student')
drop table student
create table dbo.Student(
StudentNo int NOT NULL,
LoginPwd nvarchar(50) NOT NULL,
StudentName nvarchar(50) NOT NULL,
sex bit NOT NULL,
GradeId int NOT NULL,
Phone nvarchar(50) NULL,
Address nvarchar(255) NULL,
BornDate datetime NOT NULL,
Email nvarchar(50) NULL,
IdentityCard varchar(18) NOT NULL
)

if exists(select * from sysobjects where name = 'Subject')
drop table subject
create table dbo.Subject(
SubjectNO int IDENTITY(1,1) NOT NULL,
SubjectName nchar(50) NOT NULL,
ClassHour int NOT NULL,
GradeId int NOT NULL
)

if exists(select * from sysobjects WHERE name = 'Result')
drop table Result
create table dbo.Result(
StudentNo int NOT NULL,
SubjectNo int NOT NULL,
StudentResult int NOT NULL,
ExamDate datetime NOT NULL
)

if exists(select * from sysobjects where name = 'Grade')
drop table Grade
create table Grade(
GradeId int IDENTITY(1,1) NOT NULL,
GradeName nvarchar(50) NOT NULL
)

--向Grade表插入数据
INSERT INTO Grade VALUES('S1')
--向Subject表插入数据
INSERT INTO Subject VALUES('Winforms',20,1)

--向Student表插入数据
INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)

--向Result表插入数据
INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')


USE master --设置当前数据库为master,以便访问sysdatabases表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='MySchool')
DROP DATABASE MySchool
GO

CREATE DATABASE MySchool
ON (
name='MySchool_data',
FILENAME='F:projectMySchool_data.mdf',
SIZE=10mb, --主数据文件的初始大小
MAXSIZE=100mb, --主数据文件增长的最大值
FILEGROWTH=15% --主数据文件的增长率
)
LOG ON
(
/*--日志文件的具体描述,各参数含义同上--*/
NAME='MySchool_log',
FILENAME='F:projectMySchool_log.ldf',
SIZE=3mb,
MAXSIZE=20mb, --日志文件增长的最大值
FILEGROWTH=1mb
)
GO

 

/*--查询得到Student表的信息--*/
USE MySchool
GO

SELECT * FROM sysobjects

/*--案例:使用SQL语句删除表Student学生--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
/*--学时2--*/
/*--案例:使用SQL语句创建Student表--*/
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] bit NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [nvarchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL
)
GO

/*--案例:使用SQL语句创建Subject科目表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Subject')
DROP TABLE Subject
CREATE TABLE [dbo].[Subject](
[SubjectNo] [int] IDENTITY(1,1) NOT NULL,
[SubjectName] [nchar](50) NOT NULL,
[ClassHour] [int] NOT NULL,
[GradeId] [int] NOT NULL
)
GO
/*--案例:使用SQL语句创建Result结果表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Result')
DROP TABLE ResultResult
CREATE TABLE [dbo].[Result](
[StudentNo] [int] NOT NULL,
[SubjectNo] [int] NOT NULL,
[StudentResult] [int] NOT NULL,
[ExamDate] [datetime] NOT NULL
)
GO
/*--案例:使用SQL脚本创建Student学生表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] [bit] NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [varchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [varchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL,
)
GO
/*--案例:使用SQL脚本创建Grade年级表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade')
DROP TABLE Grade
CREATE TABLE [dbo].[Grade](
[GradeId] [int] IDENTITY(1,1) NOT NULL,
[GradeName] [nvarchar](50) NOT NULL
)
GO
alter table Student
add Constraint pk_StudentNo primary key(StudentNo)
--Student表中的唯一约束
alter table Student
add Constraint uq_IdentityCard unique(IdentityCard)
--student表中的默认约束
alter table Student
add Constraint df_Address default('地址不详') for Address
--student表中的检查约束
alter table Student
add Constraint ck_BornDate check(BornDate>'1980-01-01')
--外键约束
alter table Grade
add Constraint pk_GradeId PRIMARY key(GradeId)
alter table Student
add Constraint fk_Grade foreign key(GradeId)
references Grade(GradeId)
go
--向Subjiect添加约束
alter table Subject
add Constraint pk_SubjectNo primary key(SubjectNo)
alter table Subject
add Constraint ck_ClassHour check(ClassHour>0)
ALTER TABLE Subject --非空约束(课程名称)
ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)
alter table subject
add Constraint fk_GradeId foreign key(GradeId)
references Grade(GradeId)
go
--使用SQL语句创建Result表的约束
ALTER TABLE Result --主键约束(学号、科目号、日期)
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)

ALTER TABLE Result --默认约束(日期为系统当前日期)
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate

ALTER TABLE Result --检查约束(分数不能大于100,小于0)
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)

ALTER TABLE Result --外键约束(主表Student和从表Result建立关系)
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)

ALTER TABLE Result --外键约束(主表Subject和从表Result建立关系)
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO


DATEDIFF(DY, BornDate, GETDATE())/365
DatePart()函数用于返回日期或者时间的单独部分,比如年、月、日、小时、分
例如 DatePart(YY,BornDate)
返回出生年月中的年份

可以通过DatePart()函数得到学号20011的学生大一岁和小一岁的学生

declare @year datetime
select @year= BornDate from Student where StudentNo=20011
select StudentName,sex,datepart(YY,BornDate) as BornDate from Student where StudentNo=20011 or datepart(YY,BornDate)= @year+1 or datepart(YY,BornDate)=@year-1

--select * from Student where ABS(DATEDIFF(DAY,@year,BornDate))/365<=1

--select * from Student where DATEDIFF(DAY,@year,BornDate)/365<=1 and DATEDIFF(DAY,BornDate,@year)/365<=1

 

等效于

--select * from Student

--子查询 总是用小括号括起来,先执行螺号里面的子查询,然后才执行外围的父查询
--注意:将子查询和比较运算符联合使用,必须保证子查询的返回结果为一个。
--查看比李思文小的学生
--declarre @date datetime
--select BornDate as '李斯文的出生日期' from Student where StudentName='李斯文'
--select * from Student
--where BornDate > (select BornDate as '李斯文的出生日期' from

图片 5

select * from (
select a, b, c, sum( d ) from t group by a, b, c 
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/

Student where StudentName='李斯文')

高级查询
简单子查询的用法
declare @Birthday datetime
select @Birthday = BornDate from student where studentName='李斯文';
select studentName,sex from student where BornDate>@Birthday


select StudentResult from Result
where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP') and
ExamDate=(select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP'))
--select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP')
--1.从课程名称查询出课程编号,2.从课程编号查询出最近(大)日期,从1和2一起查询出考试成绩。
--查询Java课程考试成绩为60分的学生名单--in 关键字:查询出来的结果可能是多个
select StudentName from Student where StudentNo in
(
select StudentNo from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='Java Logic')
and
StudentResult=60
)


--in 用法 查询结果有多个
select StudentNo,studentName from Student where GradeId in (select

 

--C、CUBE

GradeId from Grade where GradeName = 'S1')

聚合函数:sum avg min max count(null列不计算在rpu内)
分组:select groupId,avg(age) from student
group by groupId
having avg(age)<=30 刷选
where 和 having的区别:where 对表中的原始数据进行帅选,
having对分组后使用聚合函数计算后的数据进行帅选

 

select id,area,stu_type,sum(score) score 
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;

having一般与group by结合使用

视图
视图名称
creatview view_name(view_colum_name)
As query_expression(查询表达式)

select name,d '日期',maxAge,maxAddTime,minage,minAddTime from
(
          select distinct name,d,
          maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name,d) maxAddTime ,
          minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name,d) minAddTime
          from
         (
              select name,addtime,DAY(addtime) d,
              max(age) over(partition by name,DAY(addtime)) maxAge,
              min(age) over(partition by name,DAY(addtime)) minAge,
              RANK() over(partition by name,DAY(addtime) order by age desc) maxAgeNum ,
              RANK() over(partition by name,DAY(addtime) order by age ) minAgeNum from students where addtime>='2017-5-1' and addtime<'2017-6-1'
         ) s
) a order by name,d

/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)

[with check option](添加约束条件)

--分组查询 Group by 在select 语句查询的最后。

select studentResult ,sum(subjectNo) from Result group by studentResult
--出现Group by 一般就会出现聚合函数
--查询学生每一门课程的平均成绩
--要求显示学生姓名,课程名称,
--科目ji的平均分--如何将studentNo与studentName联系在一起?
select SubjectNo , AVG(studentResult)From Result group by subjectNo --left on student.
select SubjectName ,AVG(studentResult)from Result
left join subject on
subject.SubjectNO=result.subjectNo ///////left join 外表 on 俩表相等的字段
group by SubjectName --学生的平均分

--select studentNo ,AVG(studentResult) from Result where StudentNo=1000 group by studentNo
--查询课程的成绩 显示课程名称以及所对应的成绩
--left join 左链接!!!! 主从表的关系右边的是主表 on
select SubjectNo , AVG(studentResult)From Result group by subjectNo
select Result.StudentResult,SubjectName from Result
Left join Subject on --left join +主表 on后面加俩个表相同的字段

图片 6

等效于

subject.SubjectNO=result.subjectNo

事务 视图 索引
begin transaction
declare @error int
set @error=0
update bank set currentmoney=currentmoney-1000 where customername='张三'
set @error=@error+@@error 错误信息 全局变量
update bank set currentmoney=currentmoney+1000 where customername='李四'
set @error=@error+@@error
给客户一个可视化的结果,
if(@error<>0)
begin
print '转账失败,重重新操作'
rollback transaction 回滚
end

esle
begin
print '转账成功张三给李四转了1000'
commit transaction 提交给数据库
end
go

1.业务逻辑需要多个T_SQL语句来执行

create table bank
(
customername varchar(5)
currentmoney int
)

事务属性:原子性(各个步骤不可以再分,要么都执行,要么都不执行)
一致性 当事务完成时,数据必须处于一致状态
隔数离性 并发实物之间彼此隔离,独立,不应依赖其他
永久性
开始事务: begin transaction
提交事务:commit transaction
回滚事务:roollback transaction

 

select a, b, c, sum( d ) from t
group by grouping sets( 
( a, b, c ), 
( a, b ), ( a ), ( b, c ), 
( b ), ( a, c ), ( c ), 
() )
*/

事务是解决可能存在的问题

begin transaction
declare @error int
set @error=0
insert into Result values(113,2,150,GETDATE())
set @error=@error+@@error

if(@error<>0)
begin
print '插入失败'
rollback transaction
end

else
begin
print '插入成功'
commit transaction
end

--D、GROUPING

select * from Result

索引:根据索引键查找定数据行
汉语字典中的汉字按“页”存放,一般都有汉语拼音的目录(索引),偏旁部首目录等
sql中的数据也是按页存放
索引;shi sql编排数据的方法

/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/

唯一索引,主键索引,聚集索引,

存储过程
系统存储过用程的名字一般以sp_开头
扩展存储一般xp

调用存储
execute 过程名 [参数] 或者 exec 过程名 [参数]

create proc[edure] 存储过程名
@参数1 数据类型=默认值 output
。。。。。。。
@参数n 数据类型=默认值 output
as
sql语句
go

 

select decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type; 

--2、OVER()函数的使用
--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()

break on id skip 1
select id,area,score from students order by id,area,score desc;

select id,rank() over(partition by id order by score desc) rk,score from students;

--允许并列名次、名次不间断
select id,dense_rank() over(partition by id order by score desc) rk,score from students;

--即使SCORE相同,ROW_NUMBER()结果也是不同
select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;

select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number 
row_number() over (order by id) rn,id,area,score from students;

select id,max(score) over(partition by id order by score desc) as mx,score from students;

select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别

--按照ID求AVG
select id,avg(score) over(partition by id order by score desc rows between unbounded preceding 
and unbounded following ) as ag,score from students;

--2、SUM()

select id,area,score from students order by id,area,score desc;

select id,area,score,
sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;

select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;

--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据

select id,lag(score,1,0) over(order by id) lg,score from students;

select id,lead(score,1,0) over(order by id) lg,score from students;

--5、FIRST_VALUE()、LAST_VALUE()

select id,first_value(score) over(order by id) fv,score from students;

select id,last_value(score) over(order by id) fv,score from students; 

/*而对于last_value() over(order by id),结果是有问题的,因为我们没有按照id分区,所以应该出来的效果应该全部是90(最后一条)。

再看个例子就明白了:*/
select id,last_value(score) over(order by rownum),score from students;

/*ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 80 80.00
1 80 80.00
1 89 89.00
1 68 68.00
2 80 80.00
2 70 70.00
2 60 60.00
2 65 65.00
3 75 75.00
3 58 58.00
3 58 58.00
3 90 90.00
4 89 89.00
4 90 90.00
4 90 90.00
4 89 89.00

16 rows selected
当使用last_value分析函数的时候,缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,在进行比较的时候从当前行向前进行比较,所以会出现上边的结果。加上如下的参数,结果就正常了。呵呵。默认窗口范围为所有处理结果。*/

select id,last_value(score) over(order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students;

/*
ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 89 80.00
1 89 80.00
1 89 89.00
1 89 68.00
2 89 80.00
2 89 70.00
2 89 60.00
2 89 65.00
3 89 75.00
3 89 58.00
3 89 58.00
3 89 90.00
4 89 89.00
4 89 90.00
4 89 90.00
4 89 89.00

16 rows selected 

*/

--给出一个例子再次理解分析函数

/*********************************************************************************************

问题提出:

一个高级SQL语句问题 
假设有一张表,A和B字段都是NUMBER,
A B
1 2
2 3
3 4

有这样一些数据
现在想用一条SQL语句,查询出这样的数据
1-》2-》3—》4
就是说,A和B的数据表示一种连接的关系,现在想通过A的一个值,去查询A所对应的B值,直到B为NULL为止,
不知道这个SQL语句怎么写?请教高手!谢谢

*********************************************************************************************/

--以下是利用分析函数的一个简单解答:
--start with connect by可以参考

CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));

INSERT INTO TEST VALUES(1,2);
INSERT INTO TEST VALUES(2,3);
INSERT INTO TEST VALUES(3,4);
INSERT INTO TEST VALUES(4,NULL);

INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);

INSERT INTO TEST VALUES(9,10);
INSERT INTO TEST VALUES(10,NULL);

INSERT INTO TEST VALUES(11,12);
INSERT INTO TEST VALUES(12,13);
INSERT INTO TEST VALUES(13,14);
INSERT INTO TEST VALUES(14,NULL);

select max(col) from(
select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN INSTR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from(
select ltrim(sys_connect_by_path(col1,'->'),'->') col from (
select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG 
from test
)
start with flag=1 connect by col1=prior col2
)
)
group by flag
;

--再次给出一个例子:

--查找重复记录的方法,除了用count(*),还可以用row_number()等函数实现

create table test(xm varchar2(20),sfzhm varchar2(20));

insert into test values('1','11111');
insert into test values('1','11111');
insert into test values('2','22222');
insert into test values('2','22222');
insert into test values('2','22222');
insert into test values('3','33333');
insert into test values('3','33333');
insert into test values('3','33333');

commit;

select * from test a,(
select xm,sfzhm from test 
group by xm,sfzhm
having count(*)>2
) b
where a.xm=b.xm and a.sfzhm=b.sfzhm

select * from (select xm,sfzhm,count(*) over(partition by xm,sfzhm) sl from test) where sl>2;

看到很多人对于keep不理解,这里解释一下! 

Returns the row ranked first using DENSE_RANK
2种取值:
DENSE_RANK FIRST 
DENSE_RANK LAST 

在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。

SQL> select * from test;

ID MC SL
-------------------- -------------------- -------------------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2

9 rows selected

SQL> 
SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
4 from test
5 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555

9 rows selected

SQL>

不要混淆keep内(first、last)外(min、max或者其他):
min是可以对应last的
max是可以对应first的

SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

9 rows selected

SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id):id等于1的数量最小的(DENSE_RANK first )为
1 111 1 
1 222 1 
在这个结果中取min(mc) 就是111
max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)
取max(mc) 就是222;
min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id):id等于1的数量最大的(DENSE_RANK first )为
1 555 3 
1 666 3 
在这个结果中取min(mc) 就是222,取max(mc)就是666

详细讲述看这些地址:

更多讨论看以下地址:

/*****************分析函数的计算顺序问题*************/

有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。

over 中的partition为分组, order by是视窗内排序, 先执行 partition 然后order by 如 partition by col_a order by col_b 的执行排序效果类似于order by col_a, col_b 这样的排序效果,如果再在最后加order by,是在前边分组排序的结果基础上进行排序。

SQL> create table test(id varchar2(20));

Table created

SQL> insert into test values('1');

1 row inserted

SQL> insert into test values('1');

1 row inserted

SQL> insert into test values('8');

1 row inserted

SQL> insert into test values('5');

1 row inserted

SQL> insert into test values('5');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

ID

1
1
8
5
5

1.按照id排序:

SQL> select row_number() over(order by id),id,rownum from test;

ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
3 5 5
4 5 4
5 8 3

2.组内(没有分组就是所有数据1组)按照id排序,最后order by在组内排序基础上按照rownum排序:

SQL> select row_number() over(order by id),id,rownum from test order by rownum;

ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
5 8 3
4 5 4
3 5 5

3.按照rownum排序:

SQL> select row_number() over(order by rownum),id,rownum from test;

ROW_NUMBER()OVER(ORDERBYROWNUM ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
3 8 3
4 5 4
5 5 5

4.按照id分组,组内按照id排序

SQL> select row_number() over(partition by id order by id),id,rownum from test;

ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 5
2 5 4
1 8 3

5.按照id分组,组内按照rownum(这个是早已经出来的结构)排序:

SQL> select row_number() over(partition by id order by rownum),id,rownum from test;

ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 4
2 5 5
1 8 3 

oracle在提取数据库的时候是按over(partition by ... order by ...)这个里边的order by后边的字段的一个个distinct值取出数据的。

SQL> select * from t;

A B C D
---------- ---------- ---------- ----------
1 111 G 87
1 111 G 87
1 222 G 85
1 222 G 86
2 111 G 80
2 111 G 80
2 222 G 81
2 222 G 80

8 rows selected

只有partition by a,distinct a有2个值1和2:分2次提取数据
为1的提取一次,4条a值相同,4条平均86.25
为2的提取一次,4条a值相同,4条平均80.25

SQL> select a,b,c,avg(d) over(partition by a ),d from t;

A B C AVG(D)OVER(PARTITIONBYA) D
---------- ---------- ---------- ------------------------ ----------
1 111 G 86.25 87
1 111 G 86.25 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80.25 80
2 111 G 80.25 80
2 222 G 80.25 81
2 222 G 80.25 80

8 rows selected

partition by a,order by b,distinct a,b有4个值:
1---111
1---222
2---111
2---222
分四次提取数据:
1---111:取出2条,a=1的2条取平均87
1---222:取出2条,a=1的4条取平均86.25
2---111:取出2条,a=2的2条取平均80
2---222:取出2条,a=2的4条取平均80.25

SQL> select a,b,c,avg(d) over(partition by a order by b ),d from t;

A B C AVG(D)OVER(PARTITIONBYAORDERBY D
---------- ---------- ---------- ------------------------------ ----------
1 111 G 87 87
1 111 G 87 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80 80
2 111 G 80 80
2 222 G 80.25 81
2 222 G 80.25 80

8 rows selected

SQL>

/****************一个综合实例*************/

行列拆分问题 

表A数据
起始id 终止ID 面额
890001 890009 20
891001 891007 30
.......

插入B表
ID 面额
890001 20
890002 20
890003 20
890004 20
890005 20
890006 20
890007 20
890008 20
890009 20
891001 30
891002 30
891003 30
891004 30
891005 30
891006 30
891007 30
........

我现在是通过pl/sql过程实现,有没有简便的办法,一条sql语句解决?

/*********************************************************/

SQL> create table test(s_id varchar2(20),e_id varchar2(20),je number(18));

Table created

SQL> insert into test values('890001','890009',20);

1 row inserted

SQL> insert into test values('891001','891007',30);

1 row inserted

SQL> insert into test values('892001','892022',50);

1 row inserted

SQL> insert into test values('893001','893008',60);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

S_ID E_ID JE
-------------------- -------------------- -------------------
890001 890009 20
891001 891007 30
892001 892022 50
893001 893008 60

SQL> 
SQL> SELECT S_ID+ROWNUM-weight,JE FROM (
2 select S_ID,RN,E_RN,JE,lag(E_RN,1,0) over(order by rownum)+1 weight from(
3 SELECT S_ID,rownum rn,sum(E_ID-S_ID+1) over(order by rownum) E_RN,JE FROM TEST
4 )
5 )
6 start with rn=1 CONNECT BY ROWNUM<=e_rn;

S_ID+ROWNUM-WEIGHT JE
------------------ -------------------
890001 20
890002 20
890003 20
890004 20
890005 20
890006 20
890007 20
890008 20
890009 20
891001 30
891002 30
891003 30
891004 30
891005 30
891006 30
891007 30
892001 50
892002 50
892003 50
892004 50

S_ID+ROWNUM-WEIGHT JE
------------------ -------------------
892005 50
892006 50
892007 50
892008 50
892009 50
892010 50
892011 50
892012 50
892013 50
892014 50
892015 50
892016 50
892017 50
892018 50
892019 50
892020 50
892021 50
892022 50
893001 60
893002 60
893003 60

S_ID+ROWNUM-WEIGHT JE
------------------ -------------------
893004 60
893005 60
893006 60
893007 60
893008 60

46 rows selected

SQL> 

 

 

图片 7图片 8扩展GROUP BY研究

0.概述
本文主要介绍查询中扩展group by子句的基本用法,同时简要解析工作的原理。
主要包括如下内容:
(1). 准备
(2). GROUP BY
(3). ROLLUP
(4). CUBE
(5). GROUPING SETS
(6). GROUPING()函数
(7). grouping_id()函数
(8). group_id()函数

1. 准备
创建一个查询样例表,下面的例子都基于此表。
CREATE TABLE egb_tab(
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10),
c4 VARCHAR2(10));

INSERT INTO egb_tab VALUES('x','a','1','xa1');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','5','xb4');
INSERT INTO egb_tab VALUES('x','c','6','xc6');

INSERT INTO egb_tab VALUES('x','c','6','xc6');

INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','3','xa1');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','5','xb2');
INSERT INTO egb_tab VALUES('y','c','6','xa1');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','d','8','xb2');

INSERT INTO egb_tab VALUES('y','d','9','xa1');

INSERT INTO egb_tab VALUES('z','a','1','xa5');
INSERT INTO egb_tab VALUES('z','a','2','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa3');
INSERT INTO egb_tab VALUES('z','f','7','xa4');
COMMIT;

SELECT * FROM egb_tab;

C1 C2 C3 C4

x  a 1  xa1
x  a 2  xa2
x  a 2  xa2
x  a 3  xa3
x  a 3  xa3
x  a 3  xa3
x  b 4  xb4
x  b 4  xb4
x  b 5  xb4
x  c 6  xc6
x  c 6  xc6
y  a 1  xa1
y  a 1  xa1
y  a 2  xa1
y  a 2  xa1
y  a 3  xa1
y  b 4  xb2
y  b 4  xb2
y  b 5  xb2
y  c 6  xa1
y  c 7  xb2
y  c 7  xb2
y  d 8  xb2
y  d 9  xa1
z  a 1  xa5
z  a 2  xa5
z  f 6  xa5
z  f 6  xa3
z  f 7  xa4

2. GROUP BY
单独使用group by统计方式只有一种,即按group by后的所有列进行一次统计。

注意: GROUP BY,以及 ROLLUP,CUBE,GROUPING SETS 后面的参数,
不必都出现在查询列中,只要保证查询列中的参数是其子集便可。
比如下面的例子中,SELECT 中不必选择c1,c2,只选择c3是可以的。

SELECT c1, c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  b 4  2
x  b 5  1
x  c 6  2
y  a 1  2
y  a 2  2
y  a 3  1
y  b 4  2
y  b 5  1
y  c 6  1
y  c 7  2
y  d 8  1
y  d 9  1
z  a 1  1
z  a 2  1
z  f 6  2
z  f 7  1

3. ROLLUP
若rollup的参数个数为n,则统计的方式有n+1种。
假设 ROLLUP(p1,p2,...,p(n-1),p(n)),则分别按
p1,p2,..,p(n-1),p(n)
p1,p2,...p(n-1)
... ...
p1
NULL
分组统计,其中 NULL 表示不分组。

例子:
SELECT c1, c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY ROLLUP(c1, c2, c3)
ORDER BY c1, c2, c3;

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  a     6
x  b 4  2
x  b 5  1
x  b     3
x  c 6  2
x  c     2
x       11 
y  a 1  2
y  a 2  2
y  a 3  1
y  a     5
y  b 4  2
y  b 5  1
y  b     3
y  c 6  1
y  c 7  2
y  c     3
y  d 8  1
y  d 9  1
y  d     2
y       13 
z  a 1  1
z  a 2  1
z  a     2
z  f 6   2
z  f 7   1
z  f      3
z         5
         29 
<=> 以下查询union all(需要剔除中间的order by)
(1)按c1,c2,c3分组统计
SELECT c1, c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  b 4  2
x  b 5  1
x  c 6  2
y  a 1  2
y  a 2  2
y  a 3  1
y  b 4  2
y  b 5  1
y  c 6  1
y  c 7  2
y  d 8  1
y  d 9  1
z  a 1  1
z  a 2  1
z  f  6  2
z  f  7  1
(2)按c1,c2分组统计
SELECT c1, c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, c2, NULL
ORDER BY c1, c2, NULL;

C1 C2 C3 CNT

x  a   6
x  b   3
x  c   2
y  a   5
y  b   3
y  c   3
y  d   2
z   a   2
z   f   3 
(3)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL;

C1 C2 C3 CNT

x             11
y             13
z               5
(4)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL;

C1 C2 C3 CNT

                29 

4. CUBE
若cube参数的个数为n,则统计的方式有2^n种(表示2的n次方)。
假设 CUBE(p1,p2,...,p(n-1),p(n)),则分别按:
从p1,p2,...,p(n-1),p(n)中:
选1个分组,组合为C(n,1),
选2个分组,组合为C(n,2),
...
选n-1个分组,组合为C(n,n-1),
选n个分组,组合为C(n,n)
不分组,相当于C(n,0)
进行统计,故整个统计种数为:C(n,0) + C(n,1) + C(n,2) + ... + C(n,n-1) + C(n,n) = 2^n。

例子:
SELECT c1,c2,c3,COUNT(c4) cnt
FROM egb_tab
GROUP BY CUBE(c1,c2,c3)
ORDER BY c1,c2,c3;

<=>以下查询union all(需要剔除中间的order by)
(1)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4)
  FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL

C1 C2 C3 CNT

                29 
(2)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL

C1 C2 C3 CNT

x              11
y              13
z                5
(3)按c2分组统计
SELECT NULL c1, c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY NULL, c2, NULL
ORDER BY NULL, c2, NULL

C1 C2 C3 CNT

     a         13
     b          6
     c          5
     d          2
     f           3
(4)按c3分组统计
SELECT NULL c1, NULL c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY NULL, NULL, c3
ORDER BY NULL, NULL, c3

C1 C2 C3 CNT

 

 

本文由9159.com发布于www.9159.com,转载请注明出处:1.默认约束,3个核心元素分别是

关键词:

上一篇:已有的新表&gt
下一篇:没有了