04-Oracle入门之简单select语句
发布时间:2020-12-12 15:28:44 所属栏目:百科 来源:网络整理
导读:补充知识 设置语言环境 LANG = "zh_CN.UTF-8" [root @localhost ~] # echo $LANG zh_CN. UTF - 8 启动和关闭监听服务 [oracle @localhost ~] $ lsnrctl start status stop oracle启动 [oracle @localhost ~] $ sqlplus /nolog SQL connect /as sysdba Connec
补充知识
LANG="zh_CN.UTF-8"
[root@localhost ~]# echo $LANG
zh_CN.UTF-8
[oracle@localhost ~]$ lsnrctl start status stop
[oracle@localhost ~]$ sqlplus /nolog
SQL> connect /as sysdba
Connected.
SQL> startup
set linesize 140
set pagesize 140
select * from emp;
host clear -- 清屏
col deptno for 9999999; --修改数字列宽
col ename for a20; --修改字符列宽
sqlplus scott/你自己设置的密码 如果安装的时候没有解锁scott用户,用下述命令进行解锁: sqlplus /nolog
conn /as sysdba;
alter user scott account unlock;
alter user scott identified by 新密码;
查询表结构的定义
desc后面跟上表名。 关于SQL语言的注意事项
select基本语法SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
选择所有列 SELECT * FROM departments;
选择特定列 SELECT department_id,location_id FROM departments;
SQL语句中的运算符
SELECT ename,sal,sal + 300 FROM emp;
SELECT ename,12*sal+100 FROM emp;
SELECT ename,12*(sal+100) FROM emp;
关于空值
滤空函数进行修正 SQL> select ename,sal*12+nvl(comm,0) "年收入" from emp;
ENAME SAL 年收入
---------- ---------- ----------
tom_abc 8000 96000
SMITH 800 9600
ALLEN 1600 19500
WARD 1250 15500
JONES 2975 35700
MARTIN 1250 16400
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
15 rows selected.
空值不是空 null != null SQL> ed
Wrote file afiedt.buf
1 select * from emp
2* where comm is null
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 tom_abc 8000 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
11 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select * from emp
2* where comm is not null
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
列的别名
SELECT last_name AS name,commission_pct comm FROM employees;
或者 SELECT last_name "Name",salary*12 "Annual Salary" FROM employees;
连接符
SQL> select ename||deptno as "SB" from emp;
SB --------------------------------------------------
tom_abc10 SMITH20 ALLEN30 WARD30 JONES20 MARTIN30 BLAKE30 CLARK10 SCOTT20 KING10 TURNER30 ADAMS20 JAMES30 FORD20 MILLER10 15 rows selected.
字符串
SQL> select ename || 'is a ' || job as "Emp details" from emp;
Emp details
------------------------
tom_abcis a
SMITHis a CLERK
ALLENis a SALESMAN
WARDis a SALESMAN
JONESis a MANAGER
MARTINis a SALESMAN
BLAKEis a MANAGER
CLARKis a MANAGER
SCOTTis a ANALYST
KINGis a PRESIDENT
TURNERis a SALESMAN
ADAMSis a CLERK
JAMESis a CLERK
FORDis a ANALYST
MILLERis a CLERK
15 rows selected.
重复行默认情况下,查询会返回全部行,包括重复行. SQL> select deptno from emp;
DEPTNO ----------
10
20
30
30
20
30
30
10
20
10
30
20
30
20
10
15 rows selected.
删除重复行 在 SELECT 子句中使用关键字 SQL> select distinct deptno from emp;
DEPTNO ----------
30
20
10
SQL 和 SQL*PlusSQL 语句与 SQL*Plus 命令 的区别 SQL |
SQL*Plus |
|