ORACLE SQL语句笔记

ORACLE SQL语句笔记

– 数据类型 tinyint,smallint,int,bi
gint,decimal(小数),float,real(实数),datetime
– smalldatetime,char,bit(逻辑),money(货币),smallmoney
– binary(二进制),varbinary,image
– 定义变量 declare @aaa int set @aaa=555 全局变量 @@变量名，批处理以go隔离
– sum() 求和
– avg() 平均值
– min() 最小值
– max() 最大值
– count() 返回满足select的记录值
– count(*) 返回找到的行数 ,count(distinct 字段名) 可查找出字段的类别
– 内联接 inner join … on
– 左外联接 left outer join
– 右外联接 right outer join
– 交叉联接 cross join
– 排序 order by
– 分类 group by
– 实现实体完整性
– int PRIMARY KEY 主键
– 添加主键 Alter TABLE 表 ADD CONSTRAINT 约束名称 PRIMARY KEY 字段)
– 删除主键 Alter TABLE 表 Drop CONSTRAINT 约束名称
– 唯一约束 int UNIQUE
– 添加唯一约束 Alter TABLE 表 ADD CONSTRAINT 约束名称 UNIQUE(字段)
– 删除唯一约束 Alter TABLE 表 Drop CONSTRAINT 约束名称
– 自动编号 int IDENTITY(1,1)
– 实现域完整性
– CHECK约束 int CHECK(字段的范围)
– 添加CHECK约束 Alter TABLE 表 ADD CONSTRAINT 约束名称 CHECK(字段范围)
– 删除CHECK约束 Alter TABLE 表 Drop CONSTRAINT 约束名称
– DEFAULT约束 int DEFAULT 数值
– 添加DEFAULT约束 Alter TABLE 表 ADD CONSTRAINT 约束名称 数值 for 字段
– 删除DEFAULT约束 Alter TABLE 表 Drop CONSTRAINT 约束名称
– NOT NULL约束 int NOT NULL
– FOREIGN KEY约束
– 添加FOREIGN KEY约束 Alter TABLE 表 ADD CONSTRAINT 约束名称 FOREIGN KEY(字段) REFERENCES Customers(字段)
– 删除FOREIGN KEY约束 Alter TABLE 表 Drop CONSTRAINT 约束名称
create database 学生管理系统–创建数据库
go

use 学生管理系统
create table 学生表(编号 int primary key not null IDENTITY(1,1),学号 int,名字 varchar(10),班级 varchar(10),教师号 int)–创建表
insert into 学生表 values(1,‘王二’,‘一班’,1)–添加数据
insert into 学生表 values(2,’%张三’,‘一班’,1)
insert into 学生表 values(3,‘李四’,‘二班’,2)
insert into 学生表 values(4,‘王六’,‘二班’,2)
insert into 学生表 values(5,‘赵七’,‘五班’,4)

create table 教师表(编号 int primary key not null IDENTITY(1,1),教师号 int,名字 varchar(10))
insert into 教师表 values(1,‘李老师’)
insert into 教师表 values(2,‘黄老师’)
insert into 教师表 values(3,‘朱老师’)

create table 成绩表(编号 int primary key not null IDENTITY(1,1),学号 int,课程 varchar(10),成绩 float)
insert into 成绩表 values(1,‘语文’,50)
insert into 成绩表 values(1,‘数学’,80)
insert into 成绩表 values(2,‘语文’,40)
insert into 成绩表 values(3,‘英语’,50)
insert into 成绩表 values(1,‘地理’,40)

select * from 成绩表 where 成绩 in (select max(成绩) from 成绩表 where 课程 = ‘语文’) and 课程 = ‘语文’
union
select * from 成绩表 where 成绩 in (select max(成绩) from 成绩表 where 课程 = ‘数学’) and 课程 = ‘数学’
union
select * from 成绩表 where 成绩 in (select max(成绩) from 成绩表 where 课程 = ‘英语’) and 课程 = ‘英语’
select 课程,max(成绩) 最高成绩 from 成绩表 group by 课程–按照课程列出最高成绩
select 成绩表.学号,成绩表.课程,成绩表.成绩 from 成绩表 inner join (select 课程,max(成绩) 最高成绩 from 成绩表 group by 课程)as aaa
on 成绩表.成绩=aaa.最高成绩 and 成绩表.课程=aaa.课程–按照课程列出最高程绩
select 学号,成绩, case 课程 when ‘语文’ then ‘语文课’ when ‘数学’ then ‘数学课’ else ‘其它’ end from 成绩表

select DISTINCT 班级 from 学生表–使用distinct要可过滤掉重复的班级
select 名字 from 学生表 where 班级 in(‘一班’,‘五班’)
select * from 教师表 where 教师号 in (select 教师号 from 学生表 where 学号 in (select 学号 from 成绩表 where
成绩<60 group by 学号))-----查找不及格学生的老师
--------------找出课程为语文的课程的最高成绩，显示该最高成绩的学生姓名-------------
select 名字 from 学生表 inner join
(select top 1 成绩,学号 from 成绩表 where
课程=‘语文’ order by 成绩 desc) as tt on 学生表.学号=tt.学号
select top 1 名字,成绩 from 学生表,成绩表
where 学生表.学号 = 成绩表.学号 and 课程 = ‘语文’
order by 成绩 desc
select top 1 成绩,名字 from 学生表,成绩表
where 学生表.学号 = 成绩表.学号 and 课程 = ‘语文’
order by 成绩 desc
-------------------------------------列出各班中成绩不低于50的平均成绩
select 学生表.名字,avg(成绩表.成绩) as 平均成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.成绩>=50 group by 学生表.名字
--------------------------------------将所有成绩低于50的增加50%
update 成绩表 set 成绩=成绩2 where 成绩<50
--------------------------------------按班级列出各班学生的平均成绩
select 班级,avg(成绩) as 平均成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号 group by 班级–第一种情况,总成绩/记录数
select 班级,avg(tt.学生总成绩) from (select 学号,sum(成绩) as 学生总成绩,count() as 记录数 from 成绩表 group by 学号) as tt,学生表
where 学生表.学号=tt.学号 group by 班级–第二种情况,总成绩/学生数
select 学号,班级 from 学生表
--------------------------------------列出同时选修了语文和数学的学生的学号
select aa.学号 from (select * from 成绩表 where 课程=‘语文’) as aa inner join(select * from 成绩表 where 课程=‘数学’) as bb on aa.学号=bb.学号

select 名字 from 教师表 where 教师号 in
(select 教师号 from 学生表 inner join
(select 学号 from 成绩表 where 成绩<60 group by 学号) as tt
on tt.学号=学生表.学号 group by 教师号)–查找不及格学生的老师

select 学号,avg(成绩) 平均分 from 成绩表 where 成绩>50 group by 学号 having avg(成绩)>60 --用having语句查询成绩<>50,且平均分>60的学生
select top 2 sum(成绩) as 总分,学号 from 成绩表 group by 学号 order by 总分 desc --查找总分前2位的学生，如果用百分比，则 2 percent,desc 为降序
select 学号,sum(成绩) as 总分 from 成绩表 group by 学号 having sum(成绩) between 120 and 200 --查找总分在120-200之间的学生
select * from 学生表 where 名字 like ‘王%’–查找姓为王的人,通配符
– %: 0-n个任意字符,
– _: 单个任意字符
– []: 括号内列出的任意字符
– [^]: 任意一个不在括号内的字符
select 学生表.名字 into 临时学生表 from 学生表 where 学生表.教师号=1–将老师为1的学生名字插入到新建的表中
select * from 学生表 where 名字 like ‘t%%’ escape ‘t’–t为占位符,该字符与escape后跟的字符要一致，查找以%开头的学生名字