10.简单SQL
1.查看用户下有哪些表：
scott@ORCL10G 2023-02-18 20:48:42> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
Elapsed: 00:00:00.022.查看表结构
scott@ORCL10G 2023-02-18 21:53:59> desc emp;
Name Null? Type
----------------------------- -------- --------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)3.查询指定的列
scott@ORCL10G 2023-02-18 21:54:02> select EMPNO,ENAME from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
Elapsed: 00:00:00.00sql语句书写标准：
--1.不区分大小写
--2.可以换行写
--3.关键词不能简写或分割跨行写
--4.通常一个子句一行
--5.排版可增加可读行
--6.开发工具结束语 ; 是可选的，执行多个语句用 ; 分割他们，sqlplus里面必须用 ; 分割4.默认标题如何显示
--SQL Developer：
--默认标题排列：左对齐
--默认标题显示：大写
--SQL*PLUS：
--字符和日期型左对齐
--数字型右对齐
--默认标题显示：大写
scott@ORCL10G 2023-02-18 22:02:02> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.005.算术表达式（对数字，日期类型的使用算术操作符）
hr@ORCL10G 2023-02-18 22:04:17> select last_name,salary,salary+300
2 from employees;
LAST_NAME SALARY SALARY+300
------------------------- ---------- ----------
OConnell 2600 2900
Grant 2600 2900
Whalen 4400 4700
Hartstein 13000 13300
Fay 6000 6300
Mavris 6500 6800
Baer 10000 10300
Higgins 12000 12300
Gietz 8300 8600
King 24000 24300
Kochhar 17000 17300
De Haan 17000 17300
Hunold 9000 9300
Ernst 6000 6300算术操作符优先级
hr@ORCL10G 2023-02-18 22:05:50> select last_name,salary,12*salary+300 from employees;
LAST_NAME SALARY 12*SALARY+300
------------------------- ---------- -------------
OConnell 2600 31500
Grant 2600 31500
Whalen 4400 53100
Hartstein 13000 156300
Fay 6000 72300
Mavris 6500 78300
Baer 10000 120300
Higgins 12000 144300
Gietz 8300 99900
King 24000 288300
Kochhar 17000 204300
De Haan 17000 204300
Hunold 9000 108300
Ernst 6000 72300
hr@ORCL10G 2023-02-18 22:06:10> select last_name,salary,12*(salary+300) from employees;
LAST_NAME SALARY 12*(SALARY+300)
------------------------- ---------- ---------------
OConnell 2600 34800
Grant 2600 34800
Whalen 4400 56400
Hartstein 13000 159600
Fay 6000 75600
Mavris 6500 81600
Baer 10000 123600
Higgins 12000 147600
Gietz 8300 103200
King 24000 291600
Kochhar 17000 207600
De Haan 17000 207600
Hunold 9000 111600
Ernst 6000 75600空值：null是一个不确定，未赋值，未知的值，null不等于零或空白，空值算术操作为空
hr@ORCL10G 2023-02-18 22:09:15> select last_name,job_id,12*salary*commission_pct from employees;
LAST_NAME JOB_ID 12*SALARY*COMMISSION_PCT
------------------------- ---------- ------------------------
OConnell SH_CLERK
Grant SH_CLERK
Whalen AD_ASST
Hartstein MK_MAN
Fay MK_REP
Mavris HR_REP
Baer PR_REP
Higgins AC_MGR
Gietz AC_ACCOUNT
King AD_PRES
Kochhar AD_VP
De Haan AD_VP
Hunold IT_PROG
Ernst IT_PROG
Austin IT_PROG
Pataballa IT_PROG
Lorentz IT_PROG
Greenberg FI_MGR
Faviet FI_ACCOUNT
Chen FI_ACCOUNT
Sciarra FI_ACCOUNT
Urman FI_ACCOUNT
Popp FI_ACCOUNT
Raphaely PU_MAN
Khoo PU_CLERK
Baida PU_CLERK
Tobias PU_CLERK
Himuro PU_CLERK
Colmenares PU_CLERK
Weiss ST_MAN
Fripp ST_MAN
Kaufling ST_MAN
Vollman ST_MAN
Mourgos ST_MAN
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
Marlow ST_CLERK
Olson ST_CLERK
Mallin ST_CLERK
Rogers ST_CLERK
Gee ST_CLERK
Philtanker ST_CLERK
Ladwig ST_CLERK
Stiles ST_CLERK
Seo ST_CLERK
Patel ST_CLERK
Rajs ST_CLERK
Davies ST_CLERK
Matos ST_CLERK
Vargas ST_CLERK
Russell SA_MAN 67200
Partners SA_MAN 48600
Errazuriz SA_MAN 43200
Cambrault SA_MAN 39600
Zlotkey SA_MAN 25200
Tucker SA_REP 36000
Bernstein SA_REP 28500
Hall SA_REP 27000
Olsen SA_REP 19200
Cambrault SA_REP 18000
Tuvault SA_REP 12600
King SA_REP 42000
Sully SA_REP 39900
McEwen SA_REP 37800
Smith SA_REP 28800
Doran SA_REP 27000
Sewall SA_REP 21000
Vishney SA_REP 31500
Greene SA_REP 17100
Marvins SA_REP 8640
Lee SA_REP 8160
Ande SA_REP 7680
Banda SA_REP 7440
Ozer SA_REP 34500
Bloom SA_REP 24000
Fox SA_REP 23040
Smith SA_REP 13320
Bates SA_REP 13140
Kumar SA_REP 7320
Abel SA_REP 39600
Hutton SA_REP 26400
Taylor SA_REP 20640
Livingston SA_REP 20160
Grant SA_REP 12600
Johnson SA_REP 7440
Taylor SH_CLERK
Fleaur SH_CLERK
Sullivan SH_CLERK
Geoni SH_CLERK
Sarchand SH_CLERK
Bull SH_CLERK
Dellinger SH_CLERK
Cabrio SH_CLERK
Chung SH_CLERK
Dilly SH_CLERK
Gates SH_CLERK
Perkins SH_CLERK
Bell SH_CLERK
Everett SH_CLERK
McCain SH_CLERK
Jones SH_CLERK
Walsh SH_CLERK
Feeney SH_CLERK
107 rows selected.
Elapsed: 00:00:00.006.列别名
--1.重命名列标题
--2.有益于运算
--3.直接跟在列后，可在列和别名之间用as增强可读性
--4.包括空格，特殊字符，区分大小写必须用双引号引起来
hr@ORCL10G 2023-02-18 22:09:42> select last_name as name, commission_pct comm from employees;
NAME COMM
-------------------------------------------------------------------------------- ----------
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
hr@ORCL10G 2023-02-18 22:15:44> select last_name as "Name", salary*12 "Annual Salary" from employees;
Name Annual Salary
-------------------------------------------------------------------------------- -------------
OConnell 31200
Grant 31200
Whalen 52800
Hartstein 156000
Fay 72000
Mavris 780007.连接运算符，字面字符串，单引号转义符，去除重复行
--1.连接运算符
--1.1.连接列和字符串，列和列，字符串和字符串
--1.2.使用||作连接标志
--1.3.连接操作之后的列是字符型
hr@ORCL10G 2023-02-18 22:19:12> select last_name||job_id as "Employees" from employees;
Employees
-----------------------------------
AbelSA_REP
AndeSA_REP
AtkinsonST_CLERK
AustinIT_PROG
BaerPR_REP
--2.字面字符串
--2.1.是包含在selct中的字符，数字，日期
--2.2.字符，日期类型的必须使用单引号
--2.3.每行每次返回一行字符串
hr@ORCL10G 2023-02-18 22:21:49> select last_name||' is a ' || job_id as "Employees" from employees;
Employees
-----------------------------------------
Abel is a SA_REP
Ande is a SA_REP
Atkinson is a ST_CLERK
Austin is a IT_PROG
Baer is a PR_REP
--3.单引号转义符
--3.1.指定引号界定符
--3.2.选择任何界定符
--3.3.增加可读性和可用性
hr@ORCL10G 2023-02-18 22:25:40> select department_name || q'[ Department's Manager ID: ]' || manager_id as "Department and Manager" from departments;
Department and Manager
------------------------------------------------------------------------------------------------
Administration Department's Manager ID: 200
Marketing Department's Manager ID: 201
Purchasing Department's Manager ID: 114
Human Resources Department's Manager ID: 203
Shipping Department's Manager ID: 121
--4.重复行
--4.1.去除重复行
hr@ORCL10G 2023-02-18 22:26:12> select distinct department_id from employees;
DEPARTMENT_ID
-------------
100
30
20
70
90
110
50
40
80
10
60
12 rows selected.
Elapsed: 00:00:00.01
