oracle查询姓王的学⽣,Oracle之SQL和PLSQL编程指南1.定义表的结构：
sql>
create table stu (
student_id number(5)
constrant student_pk primary key, --定义主键约束
moitor_id number(5),
name varchar2(10) not null,
sex varchar2(6),
constraint sex_chk check(sex in (‘男’,’⼥’)),
dob Date,
specialty varchar2(10)
)
;
插⼊数据：
insert into stu values('12','5','e’,’男’,’07-11⽉-2009,’mm’);
删除数据：
Delete from stu;
2.简单查询
1. 检索所有教师信息，⽇期格式按“YYYY-MM-DD”输出：
Select name,title,wage,TO_CHAR(hire_date,’YYYY-MM-DD’) from teachers;
2. 检索学⽣表，显⽰学⽣专业列(带distinct关键字，消除重复⾏)
Select distinct specialty from students;
3. 使⽤别名：
Select name as “姓名”, dob as “⽣⽇” from stu;
4. 字符串连接符“||”
Select name || ‘⽣⽇是：’ || dob as “学⽣清单” from stu;
结果为：
学⽣清单：
⼩⼩⽣⽇是：08-3⽉-89
5．使⽤算术表达式：
Select name AS “姓名” , bonus+wage AS ”⽉总收⼊”
电脑编程工资多少from teachers；
注意：bonus+wage指“奖⾦+⼯资”
3.空值处理：
1. 利⽤NVL()函数处理bonus出现空值情况：
Select NVL(bonus,0) as “奖⾦” from teachers;
注意：NVL(bonus,0)表⽰当bonus=null时，返回0，不为null时，返回bonus 2．利⽤NVL2()函数处理bonus出现空值情况：
Select NVL2(A,B,C) from D;
注：当A=null时，返回C；
A！=null时，返回B
2. 利⽤COALESCE()函数处理bonus出现空值情况：
Select COALESCE (A,B) from D;
A!=null 时，返回A
A=null,B!=null，返回B
A=B=null 返回null
3.条件查询
1.使⽤算术⽐较符()
检索⼯资多余或等于2000元的教师信息：
Select name from teacher where wage>=2000;
检索计算机专业学⽣信息：
Select * from students where specialty=’计算机’;
检索1990年1⽉1号以前出⽣学⽣信息：
Select * from students where dob
2.使⽤包含测试：
检索teachers表中获得奖⾦为500或600元的⽼师信息：
Select * from teachers where bonus in (500,600);
检索students表中在1989年12⽉26⽇及1990年8⽉10⽇出⽣的学⽣信息：Select * from where dob in(’08-10⽉-1990’,’26-12⽉-1989’);
时间格式：
select to_char(sysdate,'yy-mm-dd') from dual;
select to_char(sysdate,'dd-mon-yy') from dual;
检索teachers表中获得奖⾦为500-- 600元之间的⽼师信息：
Select * from teachers where bonus between 500 and 600;
3.使⽤匹配测试(Like)
检索students表中所有姓袁的学⽣信息：
Select * from students where name Like ‘袁%’;
4.空值测试：
表students中bonus为空：
Select * from students where bonus is null;
4.复合条件查询：
1.使⽤and，or，not
检索计算机专业男⽣信息：
Select * from students where specialty=’ 计算机’ and sex=’男’;
检索计算机专业和⾃动化专业学⽣信息：
Select * from students where specialty=’ 计算机’ or specialty=’⾃动化’;
检索不是计算机专业男⽣信息：
Select * from students where not specialty=’ 计算机’ ;
检索students表中不姓袁的学⽣信息：
Select * from students where name not like ‘袁%’;
除姓名为：A和B以外的的学⽣信息:
Select * from students where name not in(‘A’,’B’);
检索students表中在1989年12⽉26⽇和1990年8⽉10⽇之间出⽣的学⽣信息：
Select * from where dob between ’08-10⽉-1990’ and ’26-12⽉-1989’;
2.组合使⽤逻辑条件：
检索计算机专业⼥⽣和机电专业男⽣的学⽣信息：
Select * from students where specialty=’计算机’ and sex=’⼥’
or specialty=’机电’ and sex=’男’;
4. 检索teachers表中不是⼯程师，并且2002年1⽉1⽇前参加⼯作，⼯资低于3000元的教师信息：Select * from teachers where not title=’⼯程师’
and hire_date
5.记录排序：
单⼀排序：
1.升序：
Select * from teachers order by id ASC;
2.降序：
Select id as”ID号” from teachers order by “ID号” DESC;
按多列排序：
--按姓名降序，id升序排序：
Select id ,name from teachers order by id DESC,name asc;
6.分组查询：
1.函数AVG(),COUNT(),MAX(),MIN(),SUM()的使⽤：
Select avg(wage) from teachers; --教师的平均⼯资
Select sum(wage) from teachers; --教师的总⼯资
Select count(*) from teachers ; --教师总数
Select max(dob), min(dob) from teachers; --最⼤及最⼩的出⽣⽇期
Select stddev(wage) from teachers; --教师的⼯资标准偏差
2.HAVING⼦句(必须与GROUP BY⼦句⼀起使⽤)：
Select department_id,AVG(wage) from teachers
group by department_id HAVING(WAGE)>2200;
3. ⼯资⾼于2000，低于3000的教师，并按⼯资排序：
Select id, wage from teachers where wage<3000 GROUP BY id HAVING wage>=2000 order by 2;
7.⼦查询：
1.单⾏⼦查询(where)
查询⼯资低于平均⼯资：
Select * from teachers where wage
from teachers);
2.查询与⼩笨同专业的所有学⽣
Select * from students where specialty = (select specialty from students where name=’⼩笨’)
4. Having ⼦句使⽤⼦查询：
在teachers表中查询部门平均⼯资⾼于最低部门平均⼯资的部门和平均⼯资
Select department_id, avg(wage) as 平均⼯资from teachers group by department_id having avg(wage) > (select min( avg(wage) ) from teachers group by department_id );
5. 在from⼦句中使⽤⼦查询：
在students表中的男同学中，查询计算机专业的学⽣：
Select * from (select * from students where sex=’男’) where
Specialty=’计算机’;
6.多⾏⼦查询：
利⽤IN或not in操作符，查询students表中姓王同学信息：
Select * from students where student_id in
(select student_id from students where name like ‘王%’);
查询未被学⽣选修的课程：
Select * from courses
Where courses_id not in (select course_id from students_grade);
使⽤any操作符：
查询⼯资低于如何⼀个部门平均⼯资的教师信息：
Select * from teachers where wage
使⽤all操作符：
查询⼯资多余各部门平均⼯资的教师信息：
Select * from teachers where wage > all(select avg(wage) from teachers group by department_id);
7.多列⼦查询：
1.和⼩笨同专业，同⽣⽇的学⽣信息：
Select * from students where (specialty,dob)=(select specialty , dob from students where name=’⼩笨’);
2.在teachers表中查询在各部门⼯资最低的教师：
Select * from teachers where (department_id,wage) in
(select department_id,min(wage) from teachers
group by department_id);
8.相关⼦查询：
1. 使⽤exists
在courses表查询已经被选修的课程：
Select course_id,course_name from courses c
Where exists (select 2 from students_grade sg
Where sg.courses_id = c.courses_id);
2. 使⽤ not exists
在courses表查未被选修的课程：
Select course_id,course_name from courses c
Where not exists (select 2 from students_grade sg
Where sg.courses_id = c.courses_id);
3. 使⽤ in
在departments表中查已经安排的教师的系部：
Select department_id,department_name from departments
Where department_id IN
(select department_id from teachers);
4. 使⽤ not in
在departments表中查没有安排的教师的系部：
Select department_id,department_name from departments
Where department_id NOT IN
(select department_id from teachers);
9.嵌套⼦查询：
Students表中查询与⼩笨同专业的学⽣的姓名，性别：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论