SQL>desc dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOTNULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
SQL>select*from dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL>select ename,sal from emp;
ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100
ENAME SAL ---------- ---------- JAMES 950 FORD 3000 MILLER 1300
14rows selected.
SQL>select ename,(sal+100)*12from emp;
ENAME (SAL+100)*12 ---------- ------------ SMITH 10800 ALLEN 20400 WARD 16200 JONES 36900 MARTIN 16200 BLAKE 35400 CLARK 30600 SCOTT 37200 KING 61200 TURNER 19200 ADAMS 14400
ENAME (SAL+100)*12 ---------- ------------ JAMES 12600 FORD 37200 MILLER 16800
14rows selected.
-- (SAL+100)*12列在磁盘上并没有保存，我们称其为计算表达式所生成的伪列 -- 空值不能参与运算 SQL>select ename,sal,comm,sal+comm from emp;
ENAME SAL COMM SAL+COMM ---------- ---------- ---------- ---------- SMITH 800 ALLEN 16003001900 WARD 12505001750 JONES 2975 MARTIN 125014002650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 150001500 ADAMS 1100
ENAME SAL COMM SAL+COMM ---------- ---------- ---------- ---------- JAMES 950 FORD 3000 MILLER 1300
14rows selected.
-- 将工资和奖金求和，结果发现奖金comm列为null的员工不用发工资了！这是错误的
SQL>select ename as first_name , sal*12 "Annual Salary" from emp;
FIRST_NAME Annual Salary ---------- ------------- SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200
FIRST_NAME Annual Salary ---------- ------------- JAMES 11400 FORD 36000 MILLER 15600
14rows selected.
-- as可以省略，如果别名加了引号，则显示指定的字符，而不会使用缺省的大写
SQL>select ename,job,ename ||' is a '|| job detail from emp;
ENAME JOB DETAIL ---------- --------- ------------------------- SMITH CLERK SMITH is a CLERK ALLEN SALESMAN ALLEN is a SALESMAN WARD SALESMAN WARD is a SALESMAN JONES MANAGER JONES is a MANAGER MARTIN SALESMAN MARTIN is a SALESMAN BLAKE MANAGER BLAKE is a MANAGER CLARK MANAGER CLARK is a MANAGER SCOTT ANALYST SCOTT is a ANALYST KING PRESIDENT KING is a PRESIDENT TURNER SALESMAN TURNER is a SALESMAN ADAMS CLERK ADAMS is a CLERK
ENAME JOB DETAIL ---------- --------- ------------------------- JAMES CLERK JAMES is a CLERK FORD ANALYST FORD is a ANALYST MILLER CLERK MILLER is a CLERK
INSTR('ABCA','A') ----------------- 1 SQL> select instr('abca','a',2) from dual;
INSTR('ABCA','A',2) ------------------- 4 SQL> select substr('abca',1,4-1) from dual;
SUB --- abc SQL> select instr('superman batman wonderwoman','batman') from dual;
INSTR('SUPERMANBATMANWONDERWOMAN','BATMAN') ------------------------------------------- 10 -- 截取字符串'superman batman wonderwoman'中从batman开始到最后 SQL> select substr('superman batman wonderwoman',instr('superman batman wonderwoman','batman')) from dual;
SUBSTR('SUPERMANBA ------------------ batman wonderwoman
填充，打印固定字符，实现左右对齐
SQL> select lpad('abc',6,'*') from dual;
LPAD(' ------ ***abc SQL> select rpad('abc',6,'*') from dual;
RPAD(' ------ abc***
删除字符串ab前后空白
SQL> select trim(' abc ') from dual;
TRI --- abc SQL> select ' abc ' from dual;
'ABC' ------- abc
一般空格在左边容易发现，空格在右边不容易发现，默认取出的半角的，所以最好再来一次全角的空格。
例如，从emp中查找雇员名为scott的详细信息
SQL> insert into emp values (1111,'SCoTT ','ANALYST',7566,'19-APR-87',3000,20,NULL);
1 row created. SQL> select * from emp where lower(ename)='scott';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 SQL> select * from emp where trim(lower(ename))='scott';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1111 SCoTT ANALYST 7566 19-APR-87 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 SQL> select * from emp where trim(' ' from trim(lower(ename)))='scott';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1111 SCoTT ANALYST 7566 19-APR-87 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
trim只能带一个字符
替换字符串
SQL> select replace('superman batman wonderwoman','batman','booboo') from dual;
SQL> select ename from emp where substr(lower(ename),instr(lower(ename),'n',-1)) = 'n' ;
ENAME ---------- ALLEN MARTIN SQL> select ename from emp where lower(ename) like '%n';
ENAME ---------- ALLEN MARTIN
执行以下sql并解释的含义
select concat('Hello','World') from dual; select concat(ename,job) from emp; select substr('Helloworld',1,2) from dual; select substr('Helloworld',5) from dual; select substr('Helloworld',-5,2) from dual; select length('Helloworld') from dual; select instr('Helloworld','l') from dual; select instr('Helloworld','l',1,2) from dual; select instr('Helloworld','l',-1,2) from dual; select instr('Helloworld','l',-1) from dual; select lpad('Hello',10,'*') from dual; select rpad('Hello',10,'*') from dual; select trim('H' from 'HelloHhHH') from dual; select * from emp where trim(' ' from UPPER(ename))='SCOTT'; select replace('Helloworld','owo','xxoo') from dual;
数值函数
ROUND :将值舍入到指定的小数位
TRUNC :将值截断到指定的小数位
MOD :返回除法运算的余数
DUAL 是可用于查看函数和计算结果的公用表。
执行以下sql并解释的含义
select round(45.926,2) from dual; select round(45.926,0) from dual; select round(45.926) from dual; select round(45.926,-1) from dual;
select trunc(45.926,2) from dual; select trunc(45.926,0) from dual; select trunc(45.926) from dual; select trunc(45.926,-1) from dual;
select mod(15,4) from dual; select power(3,3) from dual; select ceil(1.00001) from dual; select abs(-190) from dual;
日期函数
日期是以数字保存的，可以进行加减运算
函数
结果
MONTHS_BETWEEN
两个日期之间的月数
ADD_MONTHS
将指定的月数添加到日期
NEXT_DAY
指定日期之后的下一个日期
LAST_DAY
当月最后一天
ROUND
舍入日期
TRUNC
截断日期
MONTHS_BETWEEN(‘01-SEP-95’,’11-JAN-94’)
19.6774194
ADD_MONTHS (‘31-JAN-96’,1)
‘29-FEB-96’
NEXT_DAY (‘01-SEP-95’,’FRIDAY’)
‘08-SEP-95’
LAST_DAY (‘01-FEB-95’)
‘28-FEB-95’
计算每一个雇员工作的时间（天）
sysdate返回当前时间
hiredate雇员入职时间
SQL> select sysdate from dual;
SYSDATE --------- 29-JUN-17 SQL> select sysdate - hiredate from emp;
SQL> select ename,months_between(sysdate,hiredate) from emp;
ENAME MONTHS_BETWEEN(SYSDATE,HIREDATE) ---------- -------------------------------- SCoTT 362.348474 SMITH 438.41299 ALLEN 436.316216 WARD 436.2517 JONES 434.896861 MARTIN 429.058152 BLAKE 433.929119 CLARK 432.671055 SCOTT 362.348474 KING 427.41299 TURNER 429.703313 ADAMS 361.219442 JAMES 426.864603 FORD 426.864603 MILLER 425.219442
15 rows selected.
假设员工使用期都是三个月，查看员工专正日期
add_months() 在制定时间点上加多少个月份
SQL> select ename,hiredate,add_months(hiredate,3) from emp;
ENAME HIREDATE ADD_MONTH ---------- --------- --------- SCoTT 19-APR-87 19-JUL-87 SMITH 17-DEC-80 17-MAR-81 ALLEN 20-FEB-81 20-MAY-81 WARD 22-FEB-81 22-MAY-81 JONES 02-APR-81 02-JUL-81 MARTIN 28-SEP-81 28-DEC-81 BLAKE 01-MAY-81 01-AUG-81 CLARK 09-JUN-81 09-SEP-81 SCOTT 19-APR-87 19-JUL-87 KING 17-NOV-81 17-FEB-82 TURNER 08-SEP-81 08-DEC-81 ADAMS 23-MAY-87 23-AUG-87 JAMES 03-DEC-81 03-MAR-82 FORD 03-DEC-81 03-MAR-82 MILLER 23-JAN-82 23-APR-82
15 rows selected.
这周六是哪一天？本月最后一天是那一天？
next_day() 用周几的方式来表示将来的某一天
last_day() 指定时间点所在月份的最后一天，返回自然月的最后一天
Last_day()快速定位自然月最后一天，还有什么用？可以计算期末量。
例如：每个月月底库存还剩下多少？月底销售员的销售额是多少？
同比：今年二月份比去年二月份有什么变化？
环比：今年二月份和今年一月份有什么变化？
分析出淡季旺季
同期比：
SQL> select sysdate from dual;
SYSDATE --------- 29-JUN-17 SQL> select next_day(sysdate,'sun') as SUN ,last_day(sysdate) Last from dual;
SUN LAST --------- --------- 02-JUL-17 30-JUN-17
生日所在那个月的最后一天
SQL> select last_day(to_date('1990-04-15','yyyy-mm-dd')) from dual;
LAST_DAY( --------- 30-APR-90 SQL> select last_day(to_date('1900-02-15','yyyy-mm-dd')) from dual;
SYSDATE --------- 29-JUN-17 SQL> select round(sysdate,'year') from dual;
ROUND(SYS --------- 01-JAN-17 SQL> select round(sysdate+30,'year') from dual;
ROUND(SYS --------- 01-JAN-18 SQL> select round(sysdate,'month') from dual;
ROUND(SYS --------- 01-JUL-17 SQL> select round(sysdate-15,'month') from dual;
ROUND(SYS --------- 01-JUN-17 SQL> select round(sysdate-15,'month') from dual;
ROUND(SYS --------- 01-JUN-17 SQL> select to_date('2017-07-16','yyyy-mm-dd') from dual;
TO_DATE( --------- 16-JUL-17 SQL> select round(to_date('2017-07-16','yyyy-mm-dd'),'year') from dual;
ROUND(TO_ --------- 01-JAN-18 SQL> select trunc(to_date('2017-07-16','yyyy-mm-dd'),'year') from dual;
TRUNC(TO_ --------- 16-JAN-17 SQL> select round(to_date('2017-07-16','yyyy-mm-dd'),'month') from dual;
ROUND(TO_ --------- 01-AUG-17 SQL> select trunc(to_date('2017-07-16','yyyy-mm-dd'),'month') from dual;
TRUNC(TO_ --------- 01-JUL-17
执行以下sql并解释的含义
select sysdate from dual; select sysdate,sysdate+1/1440 from dual; select months_between(sysdate,hiredate),ename from emp; select sysdate,add_months(sysdate,6) from dual; select sysdate,next_day(sysdate,'wed') from dual; select sysdate,last_day(to_date('01-feb-1900','dd-mon-yyyy')) from dual; select round(sysdate,'month') from dual; select round(sysdate,'year') from dual; select trunc(sysdate,'month') from dual; select trunc(sysdate,'year') from dual;
-- 将数字以指定格式打印出来 SQL> select ename,to_char(sal,'L99,999.99') as sal from emp;
ENAME SAL ---------- -------------------- SCoTT $3,000.00 SMITH $800.00 ALLEN $1,600.00 WARD $1,250.00 JONES $2,975.00 MARTIN $1,250.00 BLAKE $2,850.00 CLARK $2,450.00 SCOTT $3,000.00 KING $5,000.00 TURNER $1,500.00 ADAMS $1,100.00 JAMES $950.00 FORD $3,000.00 MILLER $1,300.00
15 rows selected.
-- 00和99的区别在整数会前置0补满指定位数 SQL> select ename,to_char(sal,'L00,000.00') as sal from emp;
ENAME SAL ---------- -------------------- SCoTT $03,000.00 SMITH $00,800.00 ALLEN $01,600.00 WARD $01,250.00 JONES $02,975.00 MARTIN $01,250.00 BLAKE $02,850.00 CLARK $02,450.00 SCOTT $03,000.00 KING $05,000.00 TURNER $01,500.00 ADAMS $01,100.00 JAMES $00,950.00 FORD $03,000.00 MILLER $01,300.00
15 rows selected.
char to_number
TO_NUMBER(char[, ‘format_model’])
SQL> select to_number(' $00,800.00','L99999.00') from dual;
TO_NUMBER('$00,800.00','L99999.00') ----------------------------------- 800 SQL> select to_number(' $00,800.00','$99999.00') from dual;
TO_NUMBER('$00,800.00','$99999.00') ----------------------------------- 800 SQL> select to_number(' $00,800.00','$9999.00') from dual; select to_number(' $00,800.00','$9999.00') from dual * ERROR at line 1: ORA-01722: invalid number
第二参数指定的数字位数不能小于第一个参数的位数，否则会报错。
char to_date
TO_DATE(char[, ‘format_model’])
SQL> select to_date('1997-10-1','yyyy-mm-dd') from dual;
TO_DATE(' --------- 01-OCT-97 SQL> select to_char(to_date('1997-10-1','yyyy-mm-dd'),'yyyy-mm-dd') from dual;
-- 将字符串97转换为日期时，一定注意rr和yy，建议不要用两位表示年份。 SQL> select to_char(to_date('97-2-1','rr-mm-dd'),'yyyy-mm-dd') from dual;
TO_CHAR(TO ---------- 1997-02-01 SQL> select to_char(to_date('97-2-1','yy-mm-dd'),'yyyy-mm-dd') from dual;
TO_CHAR(TO ---------- 2097-02-01
练习以下sql：
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select ename,sal,to_char(sal,'L999,999.99') from emp; select to_number('$800.00','L999.99') from dual; select to_date('2016-09-15','yyyy-mm-dd') from dual; select to_char(sysdate,'yyyy') curr_year, to_char(to_date('05','yy'),'yyyy') yy05, to_char(to_date('99','yy'),'yyyy') yy99, to_char(to_date('05','rr'),'yyyy') rr05, to_char(to_date('99','rr'),'yyyy') rr99 from dual;
嵌套函数
单行函数可以嵌套到任意层。
嵌套函数的计算顺序是从最内层到最外层。
-- 打印出大写的雇员名字从第一位开始的8个字符，并加上'_china' SQL> select ename,upper(concat(substr(ename,1,8),'_china')) from emp;
ENAME UPPER(CONCAT(S ---------- -------------- SCoTT SCOTT _CHINA SMITH SMITH_CHINA ALLEN ALLEN_CHINA WARD WARD_CHINA JONES JONES_CHINA MARTIN MARTIN_CHINA BLAKE BLAKE_CHINA CLARK CLARK_CHINA SCOTT SCOTT_CHINA KING KING_CHINA TURNER TURNER_CHINA ADAMS ADAMS_CHINA JAMES JAMES_CHINA FORD FORD_CHINA MILLER MILLER_CHINA
15 rows selected.
-- 打印大写雇员的姓名，去除头尾空白并加上'_china' SQL> select ename,upper(concat(trim(' ' from ename),'_china')) from emp;
ENAME UPPER(CONCAT(TRI ---------- ---------------- SCoTT SCOTT_CHINA SMITH SMITH_CHINA ALLEN ALLEN_CHINA WARD WARD_CHINA JONES JONES_CHINA MARTIN MARTIN_CHINA BLAKE BLAKE_CHINA CLARK CLARK_CHINA SCOTT SCOTT_CHINA KING KING_CHINA TURNER TURNER_CHINA ADAMS ADAMS_CHINA JAMES JAMES_CHINA FORD FORD_CHINA MILLER MILLER_CHINA
15 rows selected. SQL> select to_char(round((sal/7),2),'9G99D99') from emp;
-- 计算员工工资和奖金的和 SQL> select ename,sal,comm,sal+comm from emp;
ENAME SAL COMM SAL+COMM ---------- ---------- ---------- ---------- SCoTT 3000 20 3020 SMITH 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 MARTIN 1250 1400 2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300
15 rows selected. SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0) ---------- ---------- ---------- --------------- SCoTT 3000 20 3020 SMITH 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300
15 rows selected. SQL> select nvl2(null,1,2) from dual;
NVL2(NULL,1,2) -------------- 2 SQL> select nvl2('a',1,2) from dual;
NVL2('A',1,2) ------------- 1 SQL> select nullif(1,1) from dual;
NULLIF(1,1) ----------- SQL> select nullif(1,2) from dual;
NULLIF(1,2) ----------- 1 SQL> select nullif(3,2) from dual;
-- 职员工资上涨百分之10，销售工资上涨百分之15，其他人不变 SQL> select ename,job,sal, 2 case job when 'CLERK' then sal*1.1 3 when 'SALESMAN' then sal*1.15 4 else sal end rev_sal 5 from emp;
ENAME JOB SAL REV_SAL ---------- --------- ---------- ---------- SCoTT ANALYST 3000 3000 SMITH CLERK 800 880 ALLEN SALESMAN 1600 1840 WARD SALESMAN 1250 1437.5 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1437.5 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1725 ADAMS CLERK 1100 1210 JAMES CLERK 950 1045 FORD ANALYST 3000 3000 MILLER CLERK 1300 1430
15 rows selected. SQL> select ename,job,sal,decode(sal,'CLERK',sal*1.1,'SALESMAN',sal*1.1,sal) rev_sal from emp;
ENAME JOB SAL REV_SAL ---------- --------- ---------- ---------- SCoTT ANALYST 3000 3000 SMITH CLERK 800 800 ALLEN SALESMAN 1600 1600 WARD SALESMAN 1250 1250 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1250 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1500 ADAMS CLERK 1100 1100 JAMES CLERK 950 950 FORD ANALYST 3000 3000 MILLER CLERK 1300 1300
15 rows selected.
-- 工资低于1000并且job为雇员的员工薪资涨百分之15，其他人不涨 -- 非标准的case when，不能转化为decode() -- 任何条件满足则break SQL> select ename,job,sal,case when sal>1000 then sal when job='CLERK'then sal*1.15 else sal end as rev_sal from emp;
ENAME JOB SAL REV_SAL ---------- --------- ---------- ---------- SCoTT ANALYST 3000 3000 SMITH CLERK 800 920 ALLEN SALESMAN 1600 1600 WARD SALESMAN 1250 1250 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1250 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1500 ADAMS CLERK 1100 1100 JAMES CLERK 950 1092.5 FORD ANALYST 3000 3000 MILLER CLERK 1300 1300
15 rows selected.
练习以下语句
select ename,sal,comm,sal+nvl(comm,0) from emp; select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp; select ename,sal,comm,coalesce(comm,sal,0) from emp;
select ename, job, sal, case job when 'CLERK' then sal*1.1 when 'ANALYST' then sal*1.20 else sal end raise_sal from emp order by job;
select ename, job, sal, decode(job, 'CLERK',sal*1.1, 'ANALYST',sal*1.2, sal) raise_sal from emp order by job;
与MySQL的区别
sql
mysql
oracle
查看用户的表
use dbname;show tables;
conn user/password;select * from tab;
限制行数
select * from emp limit 5;
select * from emp where rownum < 6;
MySQL数据库名、表名、列名、别名大小写规则
lower_case_table_names = 0 其中
0：区分大小写
1：不区分大小写
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
数据库名与表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有的情况下均是忽略大小写的；
变量名也是严格区分大小写的；
root@SH_MySQL-01 17:02: [(none)]> select @@lower_case_table_names; +--------------------------+ | @@lower_case_table_names | +--------------------------+ | 0 | +--------------------------+ 1 row in set (0.00 sec)
root@SH_MySQL-01 17:03: [(none)]> use test; Database changed root@SH_MySQL-01 17:03: [test]> select * from T1; ERROR 1146 (42S02): Table 'test.T1' doesn't exist root@SH_MySQL-01 17:03: [test]> select * from t1; +----+------+ | id | num | +----+------+ | 1 | 100 | | 2 | 200 | +----+------+ 2 rows in set (0.00 sec)