select * from STU_INFO t;
insert into stu_info values ('034', 'ford2', 22, '4110000000', 45);
insert into stu_info values ('035', 'ford2', 23, '4110000001', 45);
insert into stu_info values ('036', 'ford3', 24, '4110000002', 45);
insert into stu_info values ('037', 'ford3', 25, '4110000003', 45);
insert into stu_info values ('038', 'ford3', 25, '4110000004', 45);
--创建备份表
--create table stu_info_bak as select * from stu_info;
--向表1中插入数据,数据来源于备份表:
--insert into 表1 select 字段 from 备份表;
--删除表的记录from省略了
delete STU_INFO;
--筛选重复的字段stu_name,distinct一般单独使用
select distinct stu_name from STU_INFO;
select distinct stu_name, stu_id from STU_INFO;
--排序,先按年龄升序,年龄相同再按号码降序,注意先后顺序:
select * from STU_INFO order by stu_age asc, stu_no desc;
--给字段别名,as可以省略,如果没有空格可以省略双引号
select stu_name as "姓 名", stu_id 学号 from STU_INFO;
select st.stu_name as "姓 名", st.stu_id 学号 from STU_INFO st;
--查询Scott用户的emp表
select * from scott.emp;
--函数
select stu_name, sysdate, trunc(88.88) from STU_INFO;
select stu_name, (select sysdate from dual) from STU_INFO;
--转换函数,12
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"hh12":"mi":"ss') from dual;
select to_char(sysdate, 'yyyy-mm-dd hh12-mi-ss') from dual;
select to_char(sysdate, 'yyyy年mm月dd日hh12:mi:ss') from dual;
--to_number,下划线勿忘,后面的数字可以是"9"和"0",不能是其他数字
--还要注意的是:前面要转化的数字的位数不能大于后面的转化格式的位数
select to_number('123456','009999.99999') from dual;
select to_number('123456') from dual;
select to_number('123456','9999999.99999') from dual;
select to_number('123456','009999.0000') from dual;
--下面这样转化不行,必须转化数字
select to_number('100a') from dual;
--聚合函数使用
select * from STU_INFO t;
select sum(stu_age) from STU_INFO;
select max(stu_age) from STU_INFO;
select min(stu_age) from STU_INFO;
select avg(stu_age) from STU_INFO;
--count使用,里面的参数有下面几种
select count(stu_age) from STU_INFO;
select count(*) from STU_INFO;
select count(1) from STU_INFO;
select count(*) from STU_INFO where stu_age = 25;
--group by having
select * from scott.emp;
select * from scott.emp e order by e.deptno asc;
--select 后面的字段必须是group by 后面的字段,如 deptno
--having后面的条件不能放在where后面
select deptno, avg(sal), max(sal), count(sal)
from scott.emp e
group by deptno
order by deptno desc;
--分析函数row_num(),rank(),dense_rank()
select ename,
job,
deptno,
sal,
row_number() over(order by sal desc) as sal_rank
from scott.emp;
select ename, job, deptno, sal, rank() over(order by sal desc) as sal_rank
from scott.emp;
select ename,
job,
deptno,
sal,
dense_rank() over(order by sal desc) as sal_rank
from scott.emp;
--partition by和分析函数
select ename,
job,
deptno,
sal,
row_number() over(partition by e.deptno order by sal desc) as sal_rank
from scott.emp e;
--伪列rownum/rowid(给一行唯一的一个标识),rowid用的比较少,不能把e.给去掉,因为*后面不能跟字段了,要加上"e."
select e.*, rownum, rowid from scott.emp e;