SQL语句简单代码

来源：互联网发布：三维家软件多少钱编辑：程序博客网时间：2024/06/05 07:06

--创建的表名为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 1SQL> --将系统时间显示为2016-7-21SQL> select to_date(sysdate,'yyyy-mm-dd') from dual;TO_DATE(SYSDATE,'YYYY-MM-DD')-----------------------------0021/7/16SQL> --求人员表中年龄最大的人员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.37374799747474E17SQL> --求人员表中年龄最小的人员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.47147147777789E17SQL> --按城市分组求每个城市分组的人数（我的表中没有这个列，我以性别组为例进行分组）SQL> select count(*) as peoplenum , sex from person_lf group by sex; PEOPLENUM SEX---------- ---         2 1         2 0SQL> --求人员表中年龄的合计SQL> select sum(age) from person_lf;  SUM(AGE)----------        76SQL> --查询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西瓜                                      16SQL> --查询（三），between...and...用法SQL> select name, age from person_lf where age between 15 and 18;NAME                                     AGE---- ---------------------------------------冬瓜                                      15西瓜                                      16SQL> --查询所有集宁人（无表，假设表名为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 <=3SQL>--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)) tSQL>--接下来是查询其信息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)

0 0