字符集的不同使日期型的显示有问题解决
alter session set NLS_DATE_LANGUAGE=’AMERICAN’;
解锁scott账户
alter user scott identified by tiger account unlock;
DISTINCT去掉重复的行
基本查询语句的格式
SELECT *|{[DISTINCT] 列表，…}
FROM 表名；
其中 列表： 列名|表达式 [别名]
SELECT * FROM tab;
SELECT * FROM emp;
SELECT empno,ename,sal
FROM emp
WHERE sal >= 1500;
SELECT empno,sal
FROM emp
WHERE sal BETWEEN 1500 AND 2900;
SELECT hiredate FROM emp;
SELECT empno,sal,hiredate
FROM emp
WHERE hiredate BETWEEN ’01-1月-81′ AND ’31-5月-82′;
SELECT empno,hiredate
FROM emp
WHERE hiredate NOT BETWEEN ’01-1月-81′ AND ’31-5月-82′;
SELECT empno,job,sal
FROM emp
WHERE job =’SALESMAN’;
SELECT empno,job
FROM emp
WHERE job IN (‘SALESMAN’,’CLERK’,’MANAGER’);
SELECT empno,job
FROM emp
WHERE job NOT IN (‘ANALYST’,’PRESIDENT’);
LIKE 运算符可以使用以下两个通配符 “%”和”_”，其中：
“%”代表零个或多个字符
“_”代表一个且只能是一个字符
SELECT empno,job
FROM emp
WHERE job LIKE ‘SAL%’;
SELECT empno,job
FROM emp
WHERE job LIKE ‘S_L_S%’;
SELECT empno,hiredate
FROM emp
WHERE hiredate LIKE ‘%81’;
SELECT * FROM tab;
SELECT * FROM dept_temp;
CREATE TABLE dept_temp AS SELECT * FROM dept;
INSERT INTO dept_temp VALUES (88,’IT_PRESEARCH’,’BEIJING’);
SELECT * FROM dept_temp WHERE dname LIKE ‘IT\_%’ escape ‘\’;
SELECT * FROM dept_temp WHERE dname LIKE ‘IT~_%’ escape ‘~’;
ORDER BY 子句一定是sql语句的最后一个子句
SELECT empno,sal FROM emp WHERE sal >= 1500 ORDER BY sal; [ASC]升序默认
SELECT empno,sal FROM emp WHERE sal >= 1500 ORDER BY sal DESC;
SELECT empno AS “Employee Number”,ename name,(500+sal)*12 “Annual Salary”
FROM emp ORDER BY “Annual Salary” DESC;
SELECT empno AS “Employee Number”,(500+sal)*12 “Annual Salary”
FROM emp ORDER BY (500+sal)*12 DESC;
SELECT empno AS “Employee Number”,(500+sal)*12 “Annual Salary”
FROM emp ORDER BY 3 DESC;
SELECT ename,sal FROM emp ORDER BY job,sal DESC;
SELECT ename,sal FROM emp ORDER BY empno;
修改默认显示宽度
SET LINE[SIZE] {80|n}
SET line 100
L 显示刚刚输入的缓冲区中的sql语句
n text 修改出错的部分
‘/’ (run)命令
SAVE
GET
@或START 命令时把指定脚本文件内容装入sql缓冲区中运行
SPOOL命令
SELECT * FROM emp;SELECT * FROM tab;DESC emp;DESC dept;
总结
如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/227331.html原文链接：https://javaforall.net
