典型单行函数
select round(45.926,2) trunc(45.926,2) from dual
round小数点后两位四舍五入;trunc保留小数点后两位select sysdate from dual;//返回系统日期
select ename,hidedate (sysdate-hidedate)/7 as weeks hidedate-1 hidedate-24/24
select ename,hidedate,to_char(hidedate,'yyyy-mm-dd') from user;
select ename,sal to_char(sal,'$99,999.00' from emp where sal=3000;
select to_number('000011111') from user
select to_date() from user
典型分组函数
select avg(sal) from user where deptno=30;
select deptno,avg(sal) from user group by deptno;
先分组再取平均select deptno,max(sal) from user group by deptno having max(sal)>10000 order by deptno;
having只能接在groupby后面,对分组的进行筛选多表查询
a表(上)与b表(下)
id | name |
---|---|
1 | 张3 |
2 | 李四 |
3 | 王武 |
id | job | parent_id |
---|---|---|
1 | 23 | 1 |
2 | 34 | 2 |
3 | 34 | 4 |
1) 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2)左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3) 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4) 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null
典型子查询
select ename,sal from emp where sal>(select sal from emp where ename='rachel');
薪水比rachel高的信息
select ename,sal from emp where sal=(select min(sal) from emp);
薪水最低的员工
select *from emp where empno in(select mgr from emp)
查询所有经理信息
select empno,ename,job,sal from emp where sal<any(select sal from emp where job='salesman') and job<>'salesman';
薪水低于销售部任意员工的非销售部员工
表结构操作语句
not null(非空)
unique(唯一)
primary key(主键)
foreign key(外键)
check(条件检查)
列约束包含上面五个,表约束包含后面四个
creat table emp (id varchar2(6) primary key,
name varchar2(20) not null,
hiredate date default sysdate not null,
salary number(7,2),
constraint emp_sal_min check(salary>1000)
);
alter table emp add address varchar(20);
alter table emp drop column address;
alter table emp rename to emps;
alter table emp rename column name to ename;
drop table emp cascade constraints
desc emp
典型数据操作语句DML
insert into emp (empno,ename) values (7902,'richal')
update emp set depto=60,job='trainer' where ename='ena'
delete from emp where ename='ena'
典型事物操作语句 commit rollback
sql语句简单优化
*
select e.ename,e.sal,d.deptno from emp e,dept d where d.deptno=e.deptno;
select * from emp e,dept d where d.deptno>10 and e.deptno=30;
查询限制
where ename like‘s%’
//%是通配符,表示匹配所有s开头的
where ename in (700,800,900)
//选中700,800,900的