oracle基础语法和使用

知兮丶青
阅读(217) 2018-01-03
oracle基础语法和使用
oracle基础语法和使用

自从工作了几乎没用过oracle了,接触的企业都是用mysql 和 sqlserver。今天整理文件时翻了翻读书时候的笔记,看了下感触颇多,虽然和mysql语法差不多,但也生疏了,因此分享下oracle入门基础语法和使用,以后可能用的着。


登录/授权

已管理员的身份登录

sqlplus "/as sysdba"


创建用户hello,identified by hello表示密码为hello

create user hello identified by hello;


给用户hello授权--连接

grant connect to hello;


给用户hello授权--资源

grant resource to hello;


已普通用户登录 用户名:hello 密码:hello

sqlplus hello/hello



DDL语句

create table 表名 -- 创建表

drop table 表名 -- 删除表

alter table 表名 -- 修改表结构

delete table 表名 -- 删除表记录

truncate table 表名 -- 快速删除表记录



DML语句

查看表结构

desc user;


修改表名

alter table user rename to member;


增加表字段

alter table user add gender varchar2(6);


修改表列名

alter table user rename column name to username;


删除一列

alter table user drop column username;


修改列属性

alter table user modify id varchar2(12);



SQL语句

创建表

create table user(id number(3), name varchar2(12));


过滤相同的行

select distinct * from user;


给查询出来的id起个别名userid

select id "userid" from adam;

select id as userid from adam;

select id userid from adam;


连接符||

select '用户:'||name ,'的年薪是:'||salary*12 from user;

select id||name from user;


空值置换函数nvl,如:查询出来的id如果是空值则用-1来表示

select nvl(id,-1) from user;

select nvl(start_date, '01-JAN-95') from user;

select nvl(title, 'No Title Yet') from user;

select nvl(salary, 1000) from user;


相对于java中的if{}else if{}else{},如果id==1取2;否则如果id==3取4,否则取5。

select decode(id, 1,2, 3,4, 5) from user;


排序

select * from user order by id asc; -- id升序排列

select * from user order by id desc; -- id降序排列

select * from user order by id desc,name asc; -- 先id降序排列,再name降序排列


条件

select * from user where gender is null order by id asc;

select * from user where gender is not null order by id asc;

select * from user where id between 1 and 2;

select * from user where id>=1 and id<=2;

select * from user where id in(1,3); -- 查询id在1和3里的内容

select * from user where id not in(1,3);

select * from user where id not in(2);

select * from user where name like 'a%';

select * from user where name like 'a_'; -- 下划线为通配符

select * from user where name like 'a\_%' escape '\'; -- 这里在_前面加\,转义为'a_' escape说明'\'这个是个转义字符


插入

insert into user values(1, 'MrChen', 'male');

insert into user(id) values(1);

insert into user(name) values('c');

insert into user(id, name)values(3, 'aa');


更新

update user set name = 'haha';

update user set name='xixi' where id = 1;


删除

delete from user;

delete from user where id is null;

delete from user where id = 1;



函数

字符函数

select lower('A') from dual;  -- a转换为小写字母

select upper('he') from dual;  -- he转换为大写字母

select initcap('abc') from dual; -- abc首字母大写

select concat('abc','def') from dual; -- abcdef合并字母

select substr('abc',2) from dual; -- bc从2开始截取所有

 select substr('abc',1,2) from dual; -- ab从1开始截取2位

select length('abc') from dual; -- 3获得字符串长度

select nvl(null,1) from dual; -- 1空值置换


数字函数

select round(15.923) from dual;

select trunc(15.923) from dual;

ROUND (15.923, 2)          15.92

ROUND (15.923, 0)          16

ROUND (15.923, -1)         20

TRUNC (15.923, 2)           15.92

TRUNC (15.923)               15

TRUNC (15.923, -1)          10


日期函数

查系统日期

select sysdate from dual;


修改日期显示格式(临时的)

alter session set nls_date_format = 'yyyy-mm-dd-hh24-mi-ss';


求月差

select months_between('2018-1-1', '2018-2-1') from dual;


加月数

select add_months('2017-10-1',3) from dual;


查下一个星期四的日期

select next_day(sysdate,'星期四') from dual;


查月的最后一天

select last_day(sysdate) from dual;


日期进位

select round(sysdate+1/24,'hh24') from dual;


日期截取

select trunc(sysdate,'mm') from dual;


转换函数

转换为日期格式

select to_date('2018') from dual;


把sysdate转换成字符

select to_char(sysdate) from dual;

select to_char(sysdate,'yyyy-mm') from dual;

select to_char(add_months(sysdate,2),'yyyy-mm') from dual;


把数值型字的字符串转换为数字

select to_number('123') from dual;


如果括号里面是:正整数显示1,负整数显示-1,里面是0显示:0

select sign(23) from dual;


when的使用

select case when 1 = 2 then 'haha' when 2=2 then 'xixi' end when from dual;


组函数

avg(求平均值)

max(最大值)

min(最小值)

sum(和)

count(总数)



约束

查询

desc user_constraints;

select constraint_name,table_name from user_constraints;

select constraint_name,table_name from user_constraints where lower(table_name)='user';


非空约束

create table user(id number(3) not null);


唯一约束(三种添加约束)

create table user(id number(3) unique); -- 唯一约束

create table user(id number(3),username varchar2(12),constraint abc unique(id)); -- abc为约束的别名

alter table user add constraint user_unique_id unique(id);

alter table user drop constraint abc; -- 删除约束abc为别名


主键约束

create table user(id number(3),constraint pk_id primary key(id));

alter table user add constraint pk_id primary key(id);


自定义约束条件

create table user(id number(3),username varchar2(12),gender varchar2(12),constraint ck_gender check(gender='male')); -- 在添加记录的时候核对约束来限定

alter table user add constraint ck_age check(age>18 and age<25);


外键约束

alter table user_emp add constraint fk_deptid foreign key(deptid) references user_dept(id);


在删除user_dept表的同时也将外键约束删除

drop table user_dept cascade constraints;


级联删除(on delete cascade)、级联置空(on delete set null)

alter table user_emp add constraint user_emp_fk_deptid foreign key(deptid) references user_dept(id) on delete set null; -- 级联删除置换置空,删除了部门表的如何一列,员工表以此记录对应的所有关联的部门被置空


右外连接

select * from emp e,dept d where e.deptid(+)=d.id;

select * from emp e right join dept d on e.deptid=d.id;


左外连接

select * from emp e,dept d where e.deptid=d.id(+);

select * from emp e left join dept d on e.deptid=d.id;


全外连接

select * from emp e full join dept d on e.deptid = d.id;


自连接

select * from emp e,emp d where e.id = d.id;


找出自己想要的数据作为一张表

select * from emp e,(select * from emp where id=1) d;


伪列rowunm(不唯一)

select rownum,id,username from user;


伪列rowid(唯一)

select rowid,id,empname from user_emp;


对组进行判断having

select avg(salary) from user group by deptid having avg(salary)>=1000;




原创文章,转载请注明出处:https://www.weizhixi.com/article/48.html