Orcale
orcale数据类型
1. 字符串
* varchar ：不可变字符串
* varchar2： 可变字符串
2. number
*number（n）表示一个整数，长度是n
* number（m，n）表示一个小数，总长度是m，小数点后有n位，整数有m-n位
3. data
* 表示日期类型，七个字节,如果是英文环境 DD-MOR-RR “11-JUN-15” 如果是中文环境 15-7月-15
4. clob
* 大对象，表示大文本数据类型，可存4G
5. bolb
* 表示二进制数据类型，可存4G
基本语法
1.建表
create table 表名(
字段名(列名) 字段类型 [约束 默认值],
.....
字段名(列名) 字段类型 [约束 默认值]
);
create table t_user(
name varchar2(30),
gender varchar2(3),
age number
);
2.删除表
drop table 表名
3.修改表结构
--添加新的字段
语法:alter table 表名 add (新的字段 字段类型,...);
--将t_emp表中追加deptno的字段
alter table t_emp add(deptno number(11));
--删除字段
语法:alter table 表名 drop column 字段名;
--删除t_emp表中的gender字段
alter table t_emp drop column gender;
--修改列名
语法:alter table 表名 rename column 旧列名 to 新列名;
--将t_emp表中的deptno修改为dept_no
alter table t_emp rename column deptno to dept_no;
--修改字段类型
语法:alter table 表名 modify (列名 新类型,...);
--将t_emp表的salary的类型修改为number(5,2)
alter table t_emp modify (salary number(5,2));
4.删除表中数据
truncate table 表名; -- 删除整张表，然后再创建表结构，比delete更高效
5.DML语句
5.1.插入数据
|-语法 INSERT INTO 表名 [(字段名,...)] VALUES(值,...)
--注意:如果是向表中的所有字段添加数据时,可以省略字段名
--向t_emp表中插入数据
--开发中推荐使用明确字段名
insert into t_emp(id,name,salary,birth,job,dept_no)
values(1001,'yves',123.23,sysdate,'开发',10);
5.2.删除数据
|-语法:delete from 表名 [where 过滤条件];
delete from t_emp; --注意：将表中的数据全部删除
/*删除数据通常使用where条件*/
//删除id=1001的用户
delete from t_emp where id=1001;/*where是过滤条件*/
5.3.修改记录
|-语法：update 表名 set 字段名=值[,....] [where 过滤条件];
update t_emp set name='jerry';
//将id=1001的用户名改为 jerry,工资改为 888
update t_emp set name='jerry',salary=888 where id=1001;
5.4.查询数据
|-语法：select 查询的字段 from 表名;
select id,name from t_emp;
//查询所有字段
select * from t_emp;
6.函数
6.1.coalesce(参数列表):返回参数列表中第一个非空参数,最后一个参数通常为常量
--案例:
年终提成:
|-1.如果员工的comm不为空,发comm
|-2.如果员工的comm为空,发工资的一半
|-3.如果sal和comm为空,发100安慰
select ename,sal,comm,coalesce(comm,sal*0.5,100) comms
from emp;
实例
创建如下四个表并添加数据
学生信息表
-- 创建学生信息表的结构
CREATE TABLE CENTER_TEST.HAND_STUDENT
(
STUDENT_NO VARCHAR2(10) NOT NULL ENABLE,
STUDENT_NAME VARCHAR2(20),
STUDENT_AGE NUMBER(2,0),
STUDENT_GENDER VARCHAR2(5)
);
-- 添加注释
COMMENT ON TABLE CENTER_TEST.HAND_STUDENT IS '学生信息表';
COMMENT ON COLUMN CENTER_TEST.HAND_STUDENT.STUDENT_NO IS '学号';
COMMENT ON COLUMN CENTER_TEST.HAND_STUDENT.STUDENT_NAME IS '姓名';
COMMENT ON COLUMN CENTER_TEST.HAND_STUDENT.STUDENT_AGE IS '年龄';
COMMENT ON COLUMN CENTER_TEST.HAND_STUDENT.STUDENT_GENDER IS '性别';
-- 创建主键、唯一建和外键约束
alter table HAND_STUDENT add primary key(STUDENT_NO);
-- 添加数据
insert into HAND_STUDENT values ('s001','张三',23,'男');
insert into HAND_STUDENT values ('s002','李四',23,'男');
insert into HAND_STUDENT values ('s003','吴鹏',25,'男');
insert into HAND_STUDENT values ('s004','琴沁',20,'女');
insert into HAND_STUDENT values ('s005','王丽',20,'女');
insert into HAND_STUDENT values ('s006','李波',21,'男');
insert into HAND_STUDENT values ('s007','刘玉',21,'男');
insert into HAND_STUDENT values ('s008','萧蓉',21,'女');
insert into HAND_STUDENT values ('s009','陈萧晓',23,'女');
insert into HAND_STUDENT values ('s010','陈美',22,'女');
commit;
老师信息表
-- 创建老师信息表结构
CREATE TABLE CENTER_TEST.HAND_TEACHER
(
TEACHER_NO VARCHAR2(10) NOT NULL ENABLE,
TEACHER_NAME VARCHAR2(20),
MANAGER_NO VARCHAR2(10),
);
-- 添加注释
COMMENT ON TABLE CENTER_TEST.HAND_TEACHER IS '教师信息表';
COMMENT ON COLUMN CENTER_TEST.HAND_TEACHER.TEACHER_NO IS '教师编号';
COMMENT ON COLUMN CENTER_TEST.HAND_TEACHER.TEACHER_NAME IS '教师名称';
COMMENT ON COLUMN CENTER_TEST.HAND_TEACHER.MANAGER_NO IS '上课编号';
-- 创建主键、唯一建和外键约束
alter table HAND_TEACHER add primary key (TEACHER_NO);
-- 添加数据
insert into HAND_TEACHER values ('t001', '刘阳','');
insert into HAND_TEACHER values ('t002', '谌燕','t001');
insert into HAND_TEACHER values ('t003', '胡明星','t002');
commit;
课程表
-- 创建课程表结构
CREATE TABLE HAND_COURSE(
COURSE_NO varchar2(10) NOT NULL,
COURSE_NAME varchar2(20),
TEACHER_NO varchar(20) NOT null);
-- 添加注释
COMMENT ON TABLE CENTER_TEST.HAND_COURSE IS '课程信息表';
COMMENT ON COLUMN CENTER_TEST.HAND_COURSE.COURSE_NO IS '课程号';
COMMENT ON COLUMN CENTER_TEST.HAND_COURSE.COURSE_NAME IS '课程名称';
COMMENT ON COLUMN CENTER_TEST.HAND_COURSE.TEACHER_NO IS '教师编号';
-- 创建主键、唯一建和外键约束
alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);
-- 添加数据
insert into HAND_COURSE values ('c001','J2SE','t002');
insert into HAND_COURSE values ('c002','Java Web','t002');
insert into HAND_COURSE values ('c003','SSH','t001');
insert into HAND_COURSE values ('c004','Oracle','t001');
insert into HAND_COURSE values ('c005','SQL SERVER 2005','t003');
insert into HAND_COURSE values ('c006','C#','t003');
insert into HAND_COURSE values ('c007','JavaScript','t002');
insert into HAND_COURSE values ('c008','DIV+CSS','t001');
insert into HAND_COURSE values ('c009','PHP','t003');
insert into HAND_COURSE values ('c010','EJB3.0','t002');
commit;
成绩表
-- 创建成绩表结构
create table HAND_STUDENT_CORE
(
STUDENT_NO VARCHAR2(10) not null,
COURSE_NO VARCHAR2(10) not null,
CORE NUMBER(4,2)
);
-- 添加注释
comment on table HAND_STUDENT_CORE is '学生成绩表';
comment on column HAND_STUDENT_CORE.STUDENT_NO is '学号';
comment on column HAND_STUDENT_CORE.COURSE_NO is '课程号';
comment on column HAND_STUDENT_CORE.CORE is '分数';
-- 创建主键、唯一建和外键约束
lter table HAND_STUDENT_CORE add constraint PK_SC primary key (STUDENT_NO, COURSE_NO);
-- 添加数据
insert into HAND_STUDENT_CORE values ('s001','c001',58.9);
insert into HAND_STUDENT_CORE values ('s002','c001',80.9);
insert into HAND_STUDENT_CORE values ('s003','c001',81.9);
insert into HAND_STUDENT_CORE values ('s004','c001',60.9);
insert into HAND_STUDENT_CORE values ('s001','c002',82.9);
insert into HAND_STUDENT_CORE values ('s002','c002',72.9);
insert into HAND_STUDENT_CORE values ('s003','c002',81.9);
insert into HAND_STUDENT_CORE values ('s001','c003',59);
commit;
问：
1、查询没学过“谌燕”老师课的同学，显示（学号、姓名）
SELECT out_stu.STUDENT_NO ,out_stu.STUDENT_NAME
FROM HAND_STUDENT out_stu
WHERE out_stu.STUDENT_NO NOT IN
(SELECT DISTINCT hs.STUDENT_NO
FROM HAND_COURSE hc,
HAND_TEACHER ht,
HAND_STUDENT hs ,
HAND_STUDENT_CORE hsc
WHERE hc.TEACHER_NO = ht.TEACHER_NO
AND hsc.COURSE_NO = hc.COURSE_NO
AND hs.STUDENT_NO = hsc.STUDENT_NO
AND ht.TEACHER_NAME = '谌燕');
2、查询没有学全所有课的同学，显示（学号、姓名）
select hs.student_no, hs.student_name
from hand_student hs,hand_student_core hsc
where hs.student_no = hsc.student_no(+) -- 左连接
GROUP by hs.student_no, hs.student_name -- 分组
having count(hsc.course_no) <(select count(*) from hand_course c);
3、查询 c001 课程比 c002 课程成绩高的所有学生，显示：学号，姓名
select stu.student_no,stu.student_name
from hand_student stu,(select t1.student_no
from (select * from hand_student_core sc where sc.course_no = 'c001') t1,
(select * from hand_student_core sc where sc.course_no = 'c002') t2
where t1.student_no = t2.student_no
and t1.core>t2.core) tt
where stu.student_no=tt.student_no;
4、按各科平均成绩和及格率的百分数，按及格率高到低的顺序排序，显示（课程号、平均分、及格率）
select course_no,avg(core), sum(case when core>60 then 1 else 0 end)/count(*)*100 || '%'
from hand_student_core
group by course_no
order by sum(case when core>60 then 1 else 0 end) desc
5、1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）
select hs.student_no,hs.student_name,hs.student_age
-- MAX(hs.student_age) max_age, MIN(hs.student_age) min_age 该列的最大值和最小值
from HAND_STUDENT hs,(select MAX(hs.student_age) max_age, MIN(hs.student_age) min_age
from hand_student hs
-- select TO_CHAR(sysdate,'yyyy') from dual 获取当前年份
where hs.student_age<((select TO_CHAR(sysdate,'yyyy') from dual)-1992)) s
where hs.student_age = s.max_age
OR hs.student_age = s.min_age
6、列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（提示使用case when句式）
select hc.course_no,hc.course_name,
sum(case when sc.core between 85 and 100 then 1 else 0 end) "[100-85]",
sum(case when sc.core between 70 and 85 then 1 else 0 end) "[85-70]",
sum(case when sc.core between 60 and 70 then 1 else 0 end) "[70-60]",
sum(case when sc.core <60 then 1 else 0 end) "[<60]"
from hand_course hc,hand_student_core hsc
where hc.course_no=hsc.course_no
group by hc.course_no, hc.course_name
7、查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）
select student_no,course_no,core
from (select hsc.student_no,hsc.course_no,hsc.core,dense_rank() over(partition by hsc.course_no order by hsc.core desc) ranks
from hand_student_core hsc)
where ranks<4
row_number(): 顺序排序(如:1,2,3,4,5,6),用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
rank():跳跃排序(如:1,1,3,3,5,6)，如果有两个第一级别时，接下来是第三级别**,函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。
**dense_rank(): 连续排序，如果有两个第一级别时，接下来是第二级别 (如:1,1,2,2,3,4)**函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。
-- 固定搭配
select 字段1，字段2，···,rank() over(partition by 分组字段 order by 排序字段 desc) 序号别名
8、查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）
-- 选修“谌燕”老师所授课程的学生
select distinct hs.student_no,hs.student_name,hc.course_name,hsc.core
from hand_student hs,hand_course hc,hand_student_core hsc,hand_teacher ht
where ht.teacher_name = '谌燕'
and ht.teacher_no = hc.teacher_no
and hc.course_no = hsc.course_no
and hsc.student_no = hs.student_no
-- 上面的学生中每个科目的最高分
select hhh.student_no,hhh.student_name,hhh.course_name,hhh.core
from (select hh.student_no,hh.student_name,hh.course_name,hh.core,rank() over(partition by course_name order by core desc) rank
from(select distinct hs.student_no,hs.student_name,hc.course_name,hsc.core
from hand_student hs,hand_course hc,hand_student_core hsc,hand_teacher ht
where ht.teacher_name = '谌燕'
and ht.teacher_no = hc.teacher_no
and hc.course_no = hsc.course_no
and hsc.student_no = hs.student_no) hh) hhh
where rank=1;
SELECT hs.student_no,hs.student_name,hc.course_name,hsc.core
FROM hand_student hs,hand_student_core hsc,hand_course hc,hand_teacher ht
WHERE hs.student_no = hsc.student_no
AND hsc.course_no = hc.course_no
AND hc.teacher_no = ht.teacher_no
AND ht.teacher_name = '谌燕'
AND hsc.core = (SELECT MAX(sc.core)
FROM hand_student_core sc
WHERE sc.course_no = hc.course_no);
9、查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））
SELECT hsc.student_no,hs.student_name,ROUND(AVG(hsc.core),2) avg_core
-- round(m,n)对m四舍五入保留n位小数
FROM hand_student_core hsc,hand_student hs
-- EXISTS():主要测试在一个子查询中行的存在如果子查询有数据返回（至少有一行，不管什么数据）就表示条件满足，那么就可以显示出数据，否则不显示
WHERE EXISTS (SELECT sc.student_no
FROM hand_student_core sc
WHERE sc.core < 60
AND sc.student_no = hsc.student_no
-- GROUP BY：分组字段1，分组字段2，···
GROUP BY sc.student_no
-- HAVING：对GROUP BY分组的数据进行筛选，跟随GROUP BY而出现
HAVING COUNT(sc.student_no) > 1)
AND hsc.student_no = hs.student_no
GROUP BY hsc.student_no,hs.student_name;
10、查询姓氏数量最多的学生名单，显示（学号、姓名、人数）
select hs.student_no,hs.student_name,ht.cnt
from (select substr(hs.student_name,1,1) surname,count(1) cnt,dense_rank() over(order by count(1) desc) ranks
from hand_student hs
group by substr(hs.student_name,1,1)) ht,hand_student hs
where substr(hs.student_name,1,1) = ht.surname
and ht.ranks = 1;
11、查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级）
12、这是一个树结构，查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）
13、查询分数高于课程“J2SE”中所有学生成绩的学生课程信息，显示（学号，姓名，课程名称、分数）
14、分别根据教师、课程、教师和课程三个条件统计选课的学生数量：（使用rollup),显示（教师名称、课程名称、选课数量）
15、查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认，显示（学号、成绩）
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
