摘要：条件查询，别名，排序，聚合函数，where，having，联接查询，where in/exists/><=子查询，，rownum伪列，模糊查询。

• 查询所有信息
  select *from table1；- - 查询table1表中的所有信息
• 条件查询（多列，多条件查询）
  从学生表中检索出2班性别为女性的学生的姓名和电话
  Select Sname,Stel from t_student Where Sclass = 2 and Ssex =‘f’;
• 选择无重复行 distinct关键字。
  • 去除查询结果中多有的重复行。
    select distinct Sclass from t_student;- - 从t_表中查询Sclass并去重
    select distinct name, id from t_student；查询多个去重时，必须每一行所有字段全重复时才去。
  • 链接: distinct不同情况下的详细用法.
• 给列指定别名
  • Select CID AS 课程号 , Cname AS 课程名, Ctype AS 必修/选修 Chours AS 课时 from t_course; - - 多个同时改别名
  • SELECT ID AS (学号) FROM STUDENT- - 一个改别名
  • 其中as 可以省略，（）也可省略 as后面的为as前面的字段的别名。详见链接: 别名详解.
• 对查询结果进行排序
  • order by 排序字段 排序方式
  • 降序：select * from t_score where sid = ‘10002’ order by score desc;- - score字段降序排序
  • 升序：selectfrom 表名 order by 列名 asc；- - 升序排列，asc可省略
    selectfrom 表名 order by 列名；为升序排列
    • desc降序排列 ，asc 升序。
• 在列1升序的基础上 排列 列2
  select*from 表名 order by 列名1 asc，列名2 desc；
• 模糊查询 ：字段 like 匹配表达式，关键字单引号包围
  • 语法：xxx where 字段 like‘’；
  • % :0个或多个字符，每个字符可以任意
  • _ :有且只有一个字符，这个字符可以任意
    查询张某： select * from t_student where sname like ‘张%’;
  • 字段 like ‘%关键字%’字段包含”关键字”的记录
    字段 like ‘关键字%’字段以”关键字”开始的记录
    字段 like ‘%关键字’字段以”关键字”结束的记录
  • 字段1 like‘’ and 字段2 like ‘’ --like可以使用and并列使用
• 利用现有的表创建表
  create table table_new as select * from table_old;复制结构和数据
  create table table_new as select * from table_old where 1=0;只复制结构
• 修改用户密码：
  alter user 用户名 identified by 新密码；

多表查询

• 内联接查询(等值联接)，查询结果形成一个新的表
• SQL语句是先执行WHERE子句然后才执行SELECT子句的！
• 同时查询t_student，t_score，t_course三张表，表前接jion

SELECT Sname, Cname, Score FROM t_student join t_score On t_student.SID = t_score.SID Join t_course On t_course.CID = t_score.CID;

也可以用“，”隔开。其等同于

SELECT Sname, Cname, Score FROM t_student ,t_course ,t_score 
WHERE t_student.SID=t_score.SID and t_course.CID= t_score.CID;

• 查找的字段名不可以是表中的重名 ，若是则用 表名.字段名 区别，否则无法查找。
  即 若查询列出现共有字段则必须指定该字段取自哪个表格式是:表名.列名。
• 查询时可以给表指定别名

• 外联查询 语法同内联
• 左外联接： left join
  左外联接就是将左表作为主表,结果集中除了满足联接条件的记录外,还有主表中不满足条件的记录
• 右外联接： right join
  右外联接就是将右表作为主表,结果集中除了满足联接条件的记录外,还有主表中不满足条件的记录（和左外联接相对）
• 全联接：full join
  全联接就是结果集中除了满足联接条件的记录外,还有左、右表中不满足条件的记录
• 交叉连接 cross jion
  一个交叉联接(CROSS JOIN)接收两个分别有N行和M行的表T1和T2，然后返回一个包含交叉乘积N×M 条记录的联接表；
  我们想知道学生和课程有多少可能的组合
  select * from t_student cross join t_course;
  或者
  select from t_student , t_course;
  cross jion可以省略
• 自联接
  当我们需要的信息是来自一个表中的，并且需要将这个表跟它自己进行连接时用。
   注意：连接时要给表起别名
• 例如：要查询scott.emp 表中的员工编号、姓名、和上级领导名称

select p1.empno,p1.ename,p1.mgr,p2.ename from emp p1 
left join emp p2 on p1.mgr=p2.empno;

分组聚合

• SQL中提供的聚合函数：
  Min ：最小值 Max ：最大值 Sum ：求和 Avg ：求平均值 Count：计数
  函数后要加()
  查询课程表中最多（少）课时数

select max(chours) as max_chours from t_course;

查询当前的学生数（教师数）

Select count(*) as stutotal_amount from t_student;

• 分组聚合
  对记录的分组是通过关键字GROUP BY实现的
  
• Having子句
  having子句主要用于聚集查询中，在group之后过滤掉那些不符合条件的组
  如 统计平均成绩超过75分的学生的学号和平均成绩

select sid,avg(score) as avgscore from t_score
group by sid
having avg(score)>=75;

• having 与where的区别
  “Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
  “Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用“聚合函数”。

子查询

• 查询比张老师年龄大的教师信息
   分析查询字段：教师表所有信息Select * from t_teacher;
   分析查询条件：年龄大于大山的年龄 Where tage > (张老师的年龄)
   解决问题：查询出张老师的年龄
   Select tage from t_teacher Where tname = ‘张老师’;
   结论：张老师的年龄查询作为中间结果提供给上一层查询而不输出，该类查询称为子查询
• 1.带有比较运算符的子查询：
  子查询只能为外层查询提供一个单一值作为外层查询的比较对象(>,<,=,!=)

SELECT 列名
FROM 表名
WHERE 表达式 比较运算符 （子查询）

• 查询参加过课程编号为1的考试的学员

selecdt * from t_student where sid = (select sid  from t_score where cid=1);

• 2.带有ANY(some)或ALL谓词的子查询 [ANY | ALL | SOME]
• 3.带有谓词IN的子查询 [NOT] IN
  这种情况适用于子查询中返回多个值时的情况

SELECT 列名
FROM 表名
WHERE 列名 [NOT] IN（子查询）

如查询所有已经安排教师上课的课程信息

select * from t_course
where cid in (select cid from t_teachcourse);

• 4.带有EXISTS谓词的子查询[NOT] EXISTS
  存在查询，子查询不反回任何结果，只产生逻辑真TRUE或逻辑假FALSE
  
  该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
  要查出 article 表中的数据，但要求 uid 必须在 user 表中存在。SQL 语句如下：

SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)	

• 在from里面的子查询：
  当子查询返回一个结果集时，那么它就相当于一个普通的表，因此，在FROM子句我们同样可以使用子查询
  如：在成绩表中查询出所有学生中平均分最高的是多少

select max(avgscore) from
(select avg(score) as avgscore from t_score
group by sid);

分页查询rownum伪列

• 对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
  Oracle中最rownum的说明

永远按照默认生成；
永远只能用< or <= 不能使用 > or >=;

• rownum 对于等于（=）某值的查询条件
  如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
• rownum对于大于（>）某值的查询条件
  如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。
  查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
  • 即给rownum起别名，即可进行>查询

select * from(select rownum no ,id,name from student) where no>2;

---

     NO ID     NAME3 200003 李三4 200004 赵四

• rownum对于小于某值的查询条件
  rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

  select rownum,id,name from student where rownum <3;

---

    ROWNUM ID     NAME1 200001 张一2 200002 王二   

• rownum对于区间内的数据
  查询rownum在某区间的数据，必须使用子查询
  例如要查询rownum在第二行到第三行之间的数据：

select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;

---

    NO ID     NAME2 200002 王二3 200003 李三

• 找了一个20W左右的数据表，以每页显示20条数据，查找第251页数据

SELECT UU.* FROM (SELECT ROWNUM rno, U.* FROM ITEM U ORDER BY ITEM_CODE DESC
) UU WHERE rno > 500 AND rno <= 520;
或
SELECT UU.* FROM (
SELECT ROWNUM rno, U.* FROM ITEM U WHERE ROWNUM <= 5020 ORDER BY ITEM_CODE DESC) UU 
WHERE rno > 5000;

• rownum和排序

select rownum ,id,name from student order by name;

使用该语句是会发现rownum并不是有序的，是因为系统是按照记录插入时的顺序给记录排的号。

---

    ROWNUM   ID  NAME3 200003 李三2 200002 王二1 200001 张一4 200004 赵四

为了解决这个问题，必须使用子查询

select rownum ,id,name from (select * from student order by name);

---------- ------ ---------------------------------------------------ROWNUM ID     NAME1 200003 李三2 200002 王二3 200001 张一4 200004 赵四

• 每页显示20条
  select * from (select * from tabname order by name) where rownum<20 或 select * from tabname where rownum<20 order by name
• 假如要返回前5条记录：

select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6) 
select * from tablename where rownum != 10;返回的是前９条记录。 

• 取出第11-20条记录!(先选再排序再选)

select * from ( 
select rownum r,a from yourtable 
where rownum <= 20 
order by name ) 
where r > 10 

• 排序伪列时
  要先排序再选则须用select嵌套：内层排序外层选。
  rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
  rownum 是在查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:
  • 1： 假如判定条件是常量，则：
    只能 rownum = 1, <= 大于1 的自然数， = 大于1 的数是没有结果的；大于一个数也是没有结果的
    即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stop key（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，this is stop key）；
  • 2： 假如判定值不是常量，则：若条件是 = var , 则只有当 var 为1 的时候才满足条件，这个时候不存在 stop key ,必须进行full scan ,对每个满足其他where条件的数据进行判定，选出一行后才能去选rownum=2的行……

若嫌麻烦则记下面两种格式
查询20-40数据，20-40为每页范围，查询效率一要比二高
1.

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21  

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 21 AND 40  

下一步：
链接: Oracle二.