1.表管理
1.查询表结构
sql > desc tb ;
select column_name,data_tpye,nullable,data_default
from all_tab_columns
where table_name = 'tb_da' ;
2.计算一个表的大小
select segment_name,sum(bytes)/1024/1024 || 'm'
from dba_extents
where segment_name = 's_employee'
group by segment_name;
2.删除表
1.放进去回收站,约束也一起删除
drop table tb cascade constraints;
2.查看回收站
sql > show recyclebin ;
select * from recyclebin ;
3.清空回收站
purge recyclebin ;
4.直接删除,不放回收站
drop table tb purge ;
3.表数据管理
1.插入数据
insert into table_name (col1,col2,col3)
values (val1,val2,val3);
2.merge = update + insert
merge into t_bonus t
using (select employee_id,salary from s_employee ) s
on (t.employee_id = s.employee_id)
when matched then
update set t.bonus = s.salary + 40 where t.bonus > 1000
delete where (t.bonus >1300)
when not matched then
insert (t.employee,t.bonus )
values (s.employee_id,s.salary + 20)
where (s.salary >3000)
3.插入日期
insert into tb values (sysdate);
insert into tb
values (to_date('1971-01-01 15:24:56','yyyy-mm-dd hh24:mi:ss')) ;
4.插入null
insert into tb values (null) ;
5.更新表数据
update table_owner.table.name
set col = val,col=val,col = val
where ....;
4.查询表
1.计算记录数,不含重复值
select count(distinct age) from student;
2.查询前面几条记录
select * from student where rownum <=3 ;
3.数据排序
select name,age from student order by age asc;
4.查询最大,最小值,平均值,总数
select max(age) from student ;
select min(age) from student ;
select avg(age) from student ;
select sum(age) from student ;
5.decode 函数
decode (条件,值1,返回值1,值2,返回值2,值3,返回值3.....)
6.case 函数
case 条件 when 值1 then 返回值1 when 值2 then 返回值2 else 返回值3 end
7.连接字符串:|| 或者 concat 函数
8.换行
select 'hello' || chr(10) || 'my puppy' from dual;
9.随机数
select dbms_random.value(1,50) from dual;
select trunc(dbms_random.value(1,50)) from dual; ---随机数取整
10.随机字符串
select dbms_random.string('a',20) from dual;
11.找出重复记录
select * from tb where id in
(select id from tb groub by id having(count(id)>=2));
12.分组统计
select dept_name,count(*) from group_employees group by dept_name;
字符处理函数:replace (string1,string2,string3), translate(string1,string2,string3),length(string),substr(string)
1.去除字符串中的数字
select translate('m8y 2d5e6a9l','#1234567890','#')
2.统计某个字符在字符串中出现的次数
select length(translate('bbcyyddkbbc','b'||'bbcyyddk','b')) from dual;
3.删除前面几个字符
select substr('455dtrtef',3) from dual;
4.大小写转换
select lower('MY GOD') from dual;
select upper('my dear') from dual;
5.首字母大写
select initcap('my doG') from dual;
日期时间函数 sysdate
1.显示sysdate
select sysdate from dual;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
---仅仅是本次会话会改变时间的显示格式;
---如果想要修改数据库显示的时间格式,需要修改初始化文件中的nls_date_format参数
2.求某个月有多少天
select sysdate,to_number(to_char(last_day(sysdate),'dd')) from dual;
3.求某个月还剩下多少天
select sysdate,last_day(sysdate)-sysdate "day left" from dual;
4.今天是星期几
select to_char(sysdate,'day') from dual;
5.今天是几号
select extract(day from sysdate) from dual;
6.今天是一年的第几天
select to_char(sysdate,'ddd') from dual;