--查询当前系统时间
select sysdate from dual;
select systimestamp from dual;
--查询伪列
select rowid,emp.* from emp;
select rownum,emp.* from emp;
--创建表
create table score(
cid int primary key,
html number,
js number,
ajax number
)
drop table java1227
create table java1227(
jid int primary key,--主键
birth date,
jname varchar2(20) not null,--非空约束
phone varchar(12) unique,--唯一约束
address varchar(30) default ‘山东淄博‘,--缺省约束
sex char(5) check(sex in (‘男‘,‘女‘)),--检查约束
cid int,
foreign key(cid) references score(cid)
)
insert into score values(1,0,60,100);
select *from score where cid=1;
insert into java1227 values(1,to_date(‘2020-06-06‘,‘yyyy-MM-dd‘),‘刘向昌‘,‘110‘,‘山东济南‘,‘男‘,‘1‘);
select *from java1227 where jid=1;
select *from java1227 join score on java1227.cid=score.cid where jid=1;
--创建用户
create user laoliu identified by 123456--创建用户
--赋予用户连接数据库获取资源的权限
grant connect,resource to laoliu
--赋予用户查询,修改，删除表的权限
grant select,update,delete on scott.emp
to laoliu with grant option
--撤销权限
revoke select ,update on scott.emp
from laoliu
select *from scott.emp;
--复制表
create table emp01 as
select * from emp order by sal
select *from emp01;
--复制表结构
create table emp02 as
select * from emp where 1=2
select *from emp02;
--插入别的表的数据
insert into emp02(empno,ename,job)
select empno,ename,job from emp
--内连接
select * from emp join dept on emp.deptno=dept.deptno
select *from emp e,dept d where e.deptno=d.deptno
--左外连接: 内连接的结果+左表中不满足条件的数据，对应右表字段自动补齐
select *from dept left join emp on emp.deptno=dept.deptno
--右外连接：内连接的结果+右表中不满足条件的数据，对应左表字段自动补齐
select *from emp right join dept on emp.deptno=dept.deptno
--全外连接
select *from emp full join dept on emp.deptno=dept.deptno
--分组查询
select *from java1227;
insert into java1227 values(2,to_date(‘1995-08-08‘,‘yyyy-mm-dd‘),‘高德环‘,‘120‘,‘山东淄博‘,‘男‘,‘1‘);
insert into java1227 values(3,to_date(‘1995-08-08‘,‘yyyy-mm-dd‘),‘吴丹丹‘,‘122‘,‘山东淄博‘,‘女‘,‘1‘);
--查询java1227男女各有多少人
select sex,count(*)from java1227 group by sex;
--分组后查询
--查询java1227中人数大于1的性别
select sex from java1227 group by sex having count(*)>1
--将另一个查询的结果当做此查询的表
select *from(select empno,ename,job from emp)
--分页
--第一页
select * from(select rownum as r,emp.* from emp) where r>=1 and r<=4
--第二页
select * from(select rownum as r,emp.* from emp) where r>=5 and r<=8
--当前页
select * from(select rownum as r,emp.* from emp)
where r>=（当前页-1）*每页显示的条数+1 and r<=当前页*每页显示的条数
--连接操作符
select ‘姓名为‘||ename||‘工作为‘||job||‘工资为‘||sal as INFO from emp
--将字符串转小写
select lower(ename) as name from emp
--逐值替换
select decode(deptno,‘10‘,‘开发部‘,‘20‘,‘产品部‘,‘30‘,‘维护部‘)as dname from emp