Oracle学习笔记 -- day03 修改密码、基本查询、条件查询、模糊查询、排序查询

一、新用户登录

1、解锁用户语句

--解锁用户的语句alter user scott account unlock

2、使用system用户重置新用户密码

alter user scott identified by tiger1;

二、基本查询

1、别名

别名  --别名使用双引号select empno as "员工编号",ename "员工姓名" ,job as 职位 ,hiredate 入职时间 from emp e;select empno as "员工编号",ename "员工姓名" from emp e;

2、四则运算

-- 四则运算  如果null参与运算 结果恒为null-- 使用函数：nvlselect empno,ename,sal,comm, sal*12+nvl(comm,0) from emp

3、去重

--去重 distinctselect distinct job from emp

4、字符串连接

-- 字符串的链接  concat    ||-- 要求：把员工信息显示一句话：员工编号是XXXX，姓名是XXX，职位是XXXXselect '员工编号是'||empno||'，姓名是'||ename||'，职位是'||job info from emp

三、条件查询

1、比较运算符

2、逻辑运算符

3、查询语句

select * | 列名  from 表名  where查询职位是CLERK的员工select * from emp where job='CLERK'查询职位是CLERK的并且部门是30的员工select * from emp where job='CLERK' and deptno=30查询职位是CLERK的或者部门是30的员工select * from emp where job='CLERK' or deptno=30查询工资大于1500并且小于3000的员工select * from emp where sal>=1500 and sal<=3000;select * from emp where sal between 1500 and 3000;  --包括临界值select * from emp where sal between 3000 and 1500; --无结果查询编号是7369   7566  7788 的员工select * from emp where empno in(7369,7566,7788);查询姓名是SMITH, JONES ,SCOTT的员工select * from emp where ename in('SMITH', 'JONES' ,'SCOTT');notselect * from emp where sal not between 1500 and 3000;  --包括临界值select * from emp where ename not in('SMITH', 'JONES' ,'SCOTT');查询奖金不为空的员工select * from emp where comm is not null and comm!=0;select * from emp where comm is not null and comm<>0;

四、模糊查询

1、使用通配符查询

关键字  like通配符  _   %查询员工姓名中带M的select * from emp where ename like '%M%'查询员工姓名中第二个字母是M的select * from emp where ename like '_M%'查询员工姓名中带_的,使用escape进行转义，即escape后面的字符是查询的条件，而不是一个通配符select * from emp where ename like '%q_%' escape 'q';select * from emp where ename like '%5_%' escape '5';select * from emp where ename like '%@_%' escape '@';select * from emp where ename like '%__%' escape '_';select * from emp where ename like '%%_%' escape '%'; -- %号不能作为转义符select * from emp where ename like '%&_%' escape '&'; -- &号不能作为转义符

五、排序查询

1、要求

order关键字一定是出现在一个sql的最后面

2、排序

-- 一定是出现在一个sql的最后面order byascdesc奖金从高到低排序select * from emp order by comm  desc  nulls last奖金从低到高排序select * from emp order by comm  asc   nulls first