SQL语句简单代码

--创建的表名为person-lf,表中原有四行数据。

<pre class="sql" name="code">SQL> desc person_lf;
Name       Type          Nullable Default Comments 
---------- ------------- -------- ------- -------- 
IDENTITY   NUMBER(1)                      ID编号   
NAME       VARCHAR2(4)                    姓名     
AGE        INTEGER       Y                年龄     
SEX        CHAR(1)                0       性别     
PHONENUM   NUMBER(11)    Y                手机号码 
ADDRESS    NVARCHAR2(15) Y                地址     
INSERTTIME DATE          Y                插入时间 
BIRTHDAY   DATE          Y                         
IDCARDNUM  NUMBER(18)    Y    

SQL> --将系统时间显示为2016-7-21 时：分：秒
SQL> select sysdate from dual;
SYSDATE
-----------
2016/7/21 1

SQL> --将系统时间显示为2016-7-21
SQL> select to_date(sysdate,'yyyy-mm-dd') from dual;
TO_DATE(SYSDATE,'YYYY-MM-DD')
-----------------------------
0021/7/16

SQL> --求人员表中年龄最大的人员
SQL> select * from person_lf where age = (select max(age) from person_lf);
IDENTITY NAME                                     AGE SEX     PHONENUM ADDRESS                         INSERTTIME  BIRTHDAY              IDCARDNUM
-------- ---- --------------------------------------- --- ------------ ------------------------------- ----------- ----------- -------------------
       3 南瓜                                      23 0    13299999999 南山省南山市南山县南山乡        2016/7/19 1 1993/12/3   1.37374799747474E17

SQL> --求人员表中年龄最小的人员
SQL> select * from person_lf where age = (select min(age) from person_lf);
IDENTITY NAME                                     AGE SEX     PHONENUM ADDRESS                         INSERTTIME  BIRTHDAY              IDCARDNUM
-------- ---- --------------------------------------- --- ------------ ------------------------------- ----------- ----------- -------------------
       2 冬瓜                                      15 0    15847417896 西山省西山市西山县西山乡        2016/7/19 1 2001/5/6    1.47147147777789E17

SQL> --按城市分组求每个城市分组的人数（我的表中没有这个列，我以性别组为例进行分组）
SQL> select count(*) as peoplenum , sex from person_lf group by sex;
 PEOPLENUM SEX
---------- ---
         2 1
         2 0

SQL> --求人员表中年龄的合计
SQL> select sum(age) from person_lf;
  SUM(AGE)
----------
        76

SQL> --查询2-5条数据
SQL> --查询（一），like用法一
SQL> select name from person_lf where name like '南_';
NAME
----
南瓜

SQL> --查询（一），like用法二
SQL> select name from person_lf where address like '%南%';
NAME
----
南瓜

SQL> --查询（二），in用法
SQL> select name, age from person_lf where age in (15,16,17,18);
NAME                                     AGE
---- ---------------------------------------
冬瓜                                      15
西瓜                                      16

SQL> --查询（三），between...and...用法
SQL> select name, age from person_lf where age between 15 and 18;
NAME                                     AGE
---- ---------------------------------------
冬瓜                                      15
西瓜                                      16

SQL> --查询所有集宁人（无表，假设表名为population,有一个字段名为city）
SQL> --select * from population where city = '%集宁%';
SQL> --查询人数大于5人的城市（无表，假设同上表，另有一列名为name）
SQL> --select count(*) as "Number_of_People", city from population group by city having count(*)>5 order by count(*) desc;

成绩表s_score，包含字段name 和 score。这里涉及到oracle函数。

SQL> --1.查询成绩最高的学生信息
SQL> select * from s_score where score = (select max(score) from s_score);

SQL> --2.查询成绩最低的学生信息
SQL> select * from (select * from s_score order by score asc) where rownum = 1;

SQL> --3.删除一个表有重复id的行，只保留其中一条
SQL> delete from t1 where rowid not in(select min(rowid) from t1 group by id1);

SQL> --4.查询成绩前三的学生，包括并列
SQL>select name,score from (select s.*,dense_rank() over(order by s.score desc) dr from s_score s) t where t.dr <=3

SQL>--5.查询成绩表中有相同成绩的人员信息及成绩
SQL>--下面这句是查询有相同成绩的人员姓名
SQL>select t.name from (select s.* from s_score s where score in (select score from s_score group by score having count(score)>1)) t
SQL>--接下来是查询其信息
SQL>select * from person_wh where name in (select t.name from (select s.* from s_score s where score in (select score from s_score group by score having count(score)>1)) t)