–scott用户不能使用，使用system登录
–修改用户scott账户解锁
alter user scott account unlock;
–重新设置密码 (identified 被识别的)
alter user scott identified by tiger;
–选择 所有字段 scott用户部门表
select * from scott.dept;
–员工表
select * from scott.emp;
–select{*, column [alias],…}from table;
select empno,ename,sal from scott.emp;
–select语句中的算术表达式
select empno,ename,sal,sal * 12 from scott.emp;
–运算符的优先级
select empno,ename,sal,sal * 12 + 100 from scott.emp;
select empno,ename,sal,sal * (12 + 100) from scott.emp;
–字符串的连接操作符 \\(相当于java的++)
–把两个字段的数据使用_连接起来
select empno ||’_’|| ename from scott.emp;
/**
重点：null 空值
空值是指不可用，未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
*/
–查询scott用户的emp表的所有列
select * from scott.emp;
–查询scott用户的emp表的所有列，条件为comm等于0
select * from scott.emp where comm = 0;
–查询scott用户的emp表的所有列，条件为comm等于空字符串
select * from scott.emp where comm = ”;
–查询scott用户的emp表的所有列，条件为comm等于空
select * from scott.emp where comm = null;
select * from scott.emp where comm is null;
select * from scott.emp where comm is not null;
select empno,ename,sal,comm,(sal + comm,sal) * 12 from scott.emp;
–nvl函数(java的方法，传参数进去返回结果)
–nvl(第一个参数，第二个参数)，如果第一个参数为null，则取第二个参数
select empno,ename,sal,comm,nvl(sal + comm,sal) * 12 from scott.emp;
–定义字段的别名，注意，别名不支持使用单引号
select empno,ename,sal,sal * 12 as yearsal from scott.emp;
select empno,ename,sal,sal * 12 yearsal from scott.emp;
select empno,ename,sal,sal * 12 as “yearsal” from scott.emp;
select empno,ename,sal,sal * 12 “yearsal” from scott.emp;
–查询规定：empno叫做columnname列名,eid叫做columnlable列标签(自己定义的别名)
select empno as eid,ename,sal,sal * 12 as yearsal from scott.emp;
–jdbc中 getint(string columnlable) 如果有别名则是别名，如果没有别名则columnlable就是别名
–distinct关键字明显的有区别的
–缺省情况下查询显示所有行，包括重复行
select deptno from scott.emp;
–distinct关键字去除重复数据
select distinct deptno from scott.emp;
–distinct的作用范围是后面所有字段的组合
select distinct deptno,ename from scott.emp;
select * from scott.emp;
select distinct deptno,job from scott.emp;
–为什么distinct的作用范围是后面所有字段的组合
select distinct deptno,ename from scott.emp where deptno = 30;
–distinct deptno之后比如30只有一条记录，而30有6个ename，所以无法显示完整的数据
–where子句 限制筛选数据，必须跟在from之后
select * from scott.emp where deptno = 20 or deptno = 30;
select * from scott.emp where deptno = 30 and mgr = 7698;
–比较运算符
select * from scott.emp where sal >= 800 and sal <= 1600;
–between and
select * from scott.emp where sal between 800 and 1600;
–in 包含，in执行的时候会拆分成一堆的or
select * from scott.emp where deptno = 20 or deptno = 30;
select * from scott.emp where deptno in(20,30);
–like模糊查询 区分大小写
–%匹配所有 _匹配一个字符
–查询所有以 “s” 开头的员工
select * from scott.emp where ename like ‘s%’;
–查询所有以”s”结尾的员工
select * from scott.emp where ename like ‘%s’;
–查询名字中包含”s”的员工
select * from scott.emp where ename like ‘%s%’;
–查询名字中第二个字符是a的员工
select * from scott.emp where ename like ‘_a%’;
–优先级的规则 先and 再or
select ename,job,sal
from scott.emp
where job=’persident’
or job=’salesman’
and sal>1500;
select ename,job,sal
from scott.emp
where job=’salesman’
or (job=’persident’
and sal>1500);
–order by 以…排序
–desc descend 降序
–asc ascend 升序
–对结果集排序 order by asc(升序 默认) desc(降序)
–注意：order by只能出现在sql语句的最后一行
–按照薪水从低到高排序
select * from scott.emp order by sal;
select * from scott.emp order by sal asc;
select * from scott.emp order by sal desc;