Oracle数据库操作练习

 

--Oracle创建表：

create table user_info(

id VARCHAR2(11),

NAME VARCHAR2(50),

AGE NUMBER(2),

SEX CHAR(2),

ADDRESS VARCHAR2(100),

DESCRIPTION VARCHAR2(500)

)

insert into user_info VALUES ('10000000001','陈奕迅',31,'1', '湖南省','这是一位靓仔');

insert into user_info VALUES ('10000000002','王菲',23,'0', '湖南省','这是一名女歌手');

insert into user_info VALUES ('10000000003','胡歌',55,'1', '四川省','这是一位中年人');

insert into user_info VALUES ('10000000004','黄晓明',68,'1', '浙江省','这是一位老年人');

insert into user_info VALUES ('10000000005','黄晓明',48,'1', '广东省','这是一位年轻人');

insert into user_info VALUES ('10000000006','张三',18,'1', '云南省','这是一位靓仔');

insert into user_info VALUES ('10000000007','李四',18,'1', '湖南省','这是一位靓仔');

insert into user_info VALUES ('10000000008','刘诗诗',31,'0', '湖南省','这是一位明星');

insert into user_info VALUES ('10000000009','刘亦菲',32,'0', '湖南省','这是一位明星');

insert into user_info VALUES ('10000000010','胡歌',31,'1', '湖南省','这是一位靓仔');

insert into user_info VALUES ('10000000011','林泉',23,'0', '湖南省','这是一名仙女');

insert into user_info VALUES ('10000000012','黄晓明',55,'1', '四川省','这是一位中年人');

insert into user_info VALUES ('10000000013','黄晓明',68,'1', '浙江省','这是一位老年人');

insert into user_info VALUES ('10000000014','吴彦祖',48,'1', '广东省','这是一位年轻人');

insert into user_info VALUES ('10000000015','张三',18,'1', '云南省','这是一位靓仔');

insert into user_info VALUES ('10000000016','李四',18,'1', '湖南省','这是一位靓仔');

insert into user_info VALUES ('10000000017','刘诗诗',31,'0', '湖南省','这是一位明星');

insert into user_info VALUES ('10000000018','刘亦菲',32,'0', '湖南省','这是一位明星');

select * from user_info ui ORDER BY AGE;

select NAME ,COUNT(*) from user_info ui GROUP BY NAME;

select ADDRESS ,COUNT(*) from user_info ui GROUP BY ADDRESS HAVING SUM(AGE) >30;

SELECT NAME ,COUNT(*) FROM USER_INFO ui GROUP BY NAME HAVING AVG(age) >40

select LENGTH (age) from user_info;

SELECT SUBSTR('abcdefghi',2,3) FROM dual;

SELECT SUBSTR('abcdefghi',-5,3) FROM dual;

SELECT UPPER('abcdefghi') FROM dual;

SELECT LOWER('abcdefghi') FROM dual;

SELECT REPLACE('abcdefghi', 'a','A') FROM dual;

SELECT INSTR('abcdefghi', 'd') FROM dual;

SELECT TRIM(' abcdefgh i ') FROM dual;

SELECT CONCAT('abc','def') FROM dual;

SELECT SYSDATE+1 FROM dual;

SELECT SYSTIMESTAMP FROM dual;

SELECT ADD_MONTHS(SYSDATE,5) FROM dual;

--从时间提取日期部分

SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;

SELECT EXTRACT(MONTH FROM DATE '2021-07-24') month FROM dual;

SELECT FLOOR(SYSDATE-TO_DATE('2021-07-22','yyyy-mm-dd')) FROM DUAL ;

--查询两个日期相差月份

SELECT MONTHS_BETWEEN(TO_DATE('2021-07-24','yyyy-mm-dd'),TO_DATE('2021-06-24','yyyy-mm-dd')) AS month FROM dual;

SELECT USER FROM dual;

SELECT count(*) FROM dual;

SELECT 7+9*8-10 FROM dual;

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;

--查询本周是第几周

SELECT TO_CHAR(SYSDATE,'iw') FROM dual;

SELECT TO_NUMBER('1000') FROM DUAL;

--查询下个星期四日期

SELECT NEXT_DAY(SYSDATE, '星期一') FROM DUAL;

--查询当前这个月最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;

--获取主机名（未取到）

SELECT SYS_CONTEXT('userenv','terminal') FROM dual;

--获取当前locale

SELECT SYS_CONTEXT('userenv', 'language') FROM dual;

--获取一个随机数

SELECT dbms_random.random FROM dual;

--nvl(exp1,exp2),当exp1不为空时用exp1，为空时，为exp2

SELECT NVL('' ,'1') FROM dual;

--nvl(exp1,exp2,exp3),当exp1不为空时用exp2，为空时，为exp3

SELECT NVL2('' ,'1','2') FROM dual;

--DISTINCT去重复数据

SELECT DISTINCT name FROM USER_INFO ui ;

--分页查询(pageIndex=0(0为第一页),pageSize=5(5为每页5条))

select * from (select u.*, rownum rn from user_info u where rownum<=(pageIndex+1)*pageSize ORDER BY id) t

where t.rn>=pageIndex*pageSize+1;

--分页查询(pageIndex=1(1为第一页),pageSize=5(5为每页5条))

select * from (select u.*, rownum rn from user_info u where rownum<=pageIndex*pageSize ORDER BY id) t

where t.rn>=(pageIndex-1)*pageSize+1;

--查询第一页数据，每页显示5条

select * from (select u.*, rownum rn from user_info u where rownum<=5 ORDER BY id) t where t.rn>=1;

--查询第二页数据，每页显示5条

select * from (select u.*, rownum rn from user_info u where rownum<=10 ORDER BY id) t where t.rn>=6;

--查询第三页数据，每页显示5条

select * from (select u.*, rownum rn from user_info u where rownum<=15 ORDER BY id) t where t.rn>=11;

--查询第四页数据，每页显示5条

select * from (select u.*, rownum rn from user_info u where rownum<=20 ORDER BY id) t where t.rn>=16;

SELECT * FROM USER_INFO;

DROP TABLE USER_INFO ;

--创建视图

CREATE VIEW USER_INFO_VIEW AS SELECT * FROM user_info ;

--修改视图

REPLACE VIEW USER_INFO_VIEW AS SELECT * FROM USER_INFO ui ;

--查询视图

SELECT * FROM USER_INFO_VIEW;

--删除视图

DROP VIEW USER_INFO_VIEW;

--创建索引

CREATE INDEX index_user_id ON user_info (id);

--删除索引

DROP INDEX index_user_id;

SELECT * FROM USER_INFO WHERE ID ='10000000016';