--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';
