–scott用户不能够运用,–修改用户scott账户解锁

–SELECT{*, column [alias],…}FROM table;
SELECT empno,ename,sal 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;

–SELECT语句中的算术表明式
SELECT empno,ename,sal,sal * 12 FROM scott.emp;

–scott用户不只怕使用,使用system登录
–修改用户scott账户解锁
ALTER USER SCOTT ACCOUNT UNLOCK;
–重新设置密码 (identified 被识其余)
alter user scott identified by tiger;

SELECT ename,job,sal
FROM scott.emp
WHERE job=’SALESMAN’
OR (job=’PERSIDENT’
AND sal>1500);

–nvl函数(Java的措施,传参数进去重返结果)
–nvl(第四个参数,第三个参数),即使第三个参数为null,则取第三个参数
SELECT empno,ename,sal,comm,NVL(sal + comm,sal) * 12 FROM scott.emp;

–scott用户不可以动用,使用system登录
–修改用户scott账户解锁
ALTER USER SCOTT ACCOUNT UNLOCK;
–重新设置密码 (identified 被识其余)
alter user scott identified by tiger;

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;

–相比较运算符
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%’;

–字符串的接连操作符 \\(相当于java的++)
–把八个字段的数码利用_连接起来
SELECT empno ||’_’|| ename FROM scott.emp;

–where子句 限制筛选数据,必须跟在from之后
SELECT * FROM scott.emp WHERE deptno = 20 OR deptno = 30;
SELECT * FROM scott.emp WHERE deptno = 30 AND mgr = 7698;

–定义字段的别名,注意,别名不接济使用单引号
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就是别名

/**
重点: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;

–优先级的平整 先and 再or
SELECT ename,job,sal
FROM scott.emp
WHERE job=’PERSIDENT’
OR job=’SALESMAN’
AND sal>1500;

–定义字段的别名,注意,别名不帮助使用单引号
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就是别名

–拔取 所有字段 scott用户单位表
SELECT * FROM scott.dept;
–员工表
SELECT * FROM scott.emp;

–SELECT语句中的算术表明式
SELECT empno,ename,sal,sal * 12 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;

–优先级的平整 先and 再or
SELECT ename,job,sal
FROM scott.emp
WHERE job=’PERSIDENT’
OR job=’SALESMAN’
AND sal>1500;

–SELECT{*, column [alias],…}FROM table;
SELECT empno,ename,sal FROM scott.emp;

–选拔 所有字段 scott用户单位表
SELECT * FROM scott.dept;
–员工表
SELECT * FROM scott.emp;

–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,所以不能够出示完整的数量

–运算符的先期级
SELECT empno,ename,sal,sal * 12 + 100 FROM scott.emp;
SELECT empno,ename,sal,sal * (12 + 100) FROM scott.emp;

–相比运算符
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%’;

–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;

–where子句 限制筛选数据,必须跟在from之后
SELECT * FROM scott.emp WHERE deptno = 20 OR deptno = 30;
SELECT * FROM scott.emp WHERE deptno = 30 AND mgr = 7698;

–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,所以无法突显完整的多少

–nvl函数(Java的措施,传参数进去再次回到结果)
–nvl(第二个参数,第四个参数),若是第一个参数为null,则取第一个参数
SELECT empno,ename,sal,comm,NVL(sal + comm,sal) * 12 FROM scott.emp;

相关文章