SQL中的SELECT 简单查询语句总结

–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 以…排序
–descend 降序
–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;

SQL中的SELECT 简单查询语句总结

相关推荐