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