SQL语言

SQL语言按功能分为四大类
1.数据查询语言DQL ： 查询数据
2.数据定义语言DDL ：建立、删除和修改数据对象
3.数据操纵语言DML ：完成数据操作的命令，包括查询
4.数据控制语言DCL ：控制对数据库的访问，服务器的关闭、启动等

显示数据库结构
desc 用户名.数据表

单表查询
查询
select * from 用户名.数据表

查询某些字段不同的记录
select distinct job from 用户名.数据表

单条件查询
select empno,ename,job from scott.emp where job=''manager';
select empno,ename,job,sal from scott.emp where sal<=25;

组合条件查询
select empno,ename,job from scott.emp where job>='CLERK' and sal<=2000;
select empno,ename,job from scott.emp where job>='CLERK' or sal<=2000;
select empno,ename,job from scott.emp where not job='CLERK' ;

排序查询
select empno,ename,job from scott.emp where job<='CLERK' order by job asc,sal desc;

分组查询
select empno,ename,job,sal from scott.emp group by job,empno,ename,sal having sal<=2000;
select empno,ename,job,sal from scott.emp where sal<=2000 group by job,empno,ename,sal ;
having是检查分组后的各组是否满足条件，只能搭配group by 使用

字段运算查询
select empno,ename,job,sal,mgr,sal+mgr from scott.emp；

变换查询显示
select empno 编号,ename 姓名,job 工作,sal 薪水 from scott.emp；

多表查询
无条件多表查询
select emp.empno,emp.ename,emp.deptno,dept.name,dept.loc from scott.emp, scott.dept；

等值多表查询
select emp.empno,emp.ename,emp.deptno,dept.name,dept.loc from scott.emp, scott.dept where scott.emp.deptno=scott.dept.deptno；

非等值多表查询
select emp.empno,emp.ename,emp.deptno,dept.name,dept.loc from scott.emp, scott.dept where scott.emp.deptno！=scott.dept.deptno and scott.emp.deptno=10；

嵌套查询
select emp.empno,emp.ename,emp.obj,emp.sal from scott.emp where sal>=(select sal from scott.emp where ename='WARD');
select emp.empno,emp.ename,emp.obj,emp.sal from scott.emp where sal>any(select sal from scott.emp where job='MANAGER');
select emp.empno,emp.ename,emp.obj,emp.sal from scott.emp where sal=some(select sal from scott.emp where job='MANAFER');
select emp.empno,emp.ename,emp.obj,emp.sal from scott.emp where sal>all(select sal from scott.emp where job='MANAFER');
select emp.empno,emp.ename,emp.obj,emp.sal from scott.emp,scott.dept where exists (select * from scott.emp where scott.emp.deptno=scott.dept.deptno);
(select deptno from scott.emp)
union
(select deptno from scott.dept);
(select deptno from scott.emp)
intersect
(select deptno from scott.dept);
(select deptno from scott.dept)
minus
(select deptno from scott.emp);


函数查询
【ceil】
select mgr,mgr/100,ceil(mgr/100) from scott.emp;
[floor]
select mgr,mgr/100,floor(mgr/100) from scott.emp;
[mod]
select mgr,mod(mgr,1000),mod(mgr,100),mod(mgr.10) from scott.emp;
[power]
select mgr,power(mgr,2),power(mgr,3) from scott.emp;
[round]
select mgr,round(mgr/100,2),round(mgr/1000,2) from scott.emp;
[sign]
select mgr,mgr7800,sign(mgr-7800) from scott.emp;
[avg]
select avg(mgr) 平均薪水 from scott.emp;
[count]
select count(*) 记录总数 from scott.emp;
[min]
select min(sal) 最少薪水 from scott.emp;
[max]
select max(sal) 最高薪水 from scott.emp;
[sum]
select sum(sal) 薪水总和 from scott.emp;


整表数据删除
truncate table scott.test;(删除表数据，保留结构)

直接赋值更新
update 数据表 set 字段名 1=新的赋值，字段名2=新的赋值,....where 条件