SQL基础语法

来源：互联网发布：python 协同过滤算法编辑：程序博客网时间：2024/05/17 06:38

第一章、简单查询

1、1简单查询语句

1、创建dept表、 emp表、 bonus表、 salgrade表

CREATE TABLE dept(

deptno NUMBER(2) CONSTRAINT PK_dept PRIMARY KEY,

dname VARCHAR2(14),

loc VARCHAR2(13)

);

SQL> CREATE TABLE emp(

2 empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

3 ename VARCHAR2(10),

4 job VARCHAR2(9),

5 mgr NUMBER(4),

6 hiredate DATE,

7 sal NUMBER(7,2),

8 comm NUMBER(7,2),

9 deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

10 );

SQL> CREATE TABLE bonus(

2 enamE VARCHAR2(10),

3 job VARCHAR2(9),

4 sal NUMBER(7,2),

5 comm NUMBER(7,2));

SQL> CREATE TABLE salgrade(

2 grade NUMBER,

3 losal NUMBER,

4 hisal NUMBER);

插入数据

INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK');

INSERT INTO dept VALUES(20,'RESENARCH','DALLAS');

INSERT INTO dept VALUES(30,'SALES','CHICAGO');

INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');

INSERT INTO emp VALUES(7369,'SMITH','CLERK',1902,to_date('12-12-1980','dd-mm-yyyy'),80,NULL,20);

INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-2-1981','dd-mm-yyyy'),1250,1400,30);

INSERT INTO emp VALUES(7698,'BLANK','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

INSERT INTO emp VALUES(7782,'CLARK','MANAGER',1839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);

INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);

INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

INSERT INTO salgrade VALUES(1,700，1200);

INSERT INTO salgrade VALUES(2,1201，1400);

INSERT INTO salgrade VALUES(3,1401,2000);

INSERT INTO salgrade VALUES(4,2001,3000);

INSERT INTO salgrade VALUES(5,3001,9999);

语法1 简单查询语句语法

SELECT [DESTINCT] *|列名称 [AS] [列别名] ，列名称 [AS] [列别名]，.....FROM 表名称 [表别名];

(SELECT之后可以使用*将所有字段（列）的内容全部查询出来；如果数据列上面存在重复，则可以使用DISTINCT关键字取消重复元素)

范例：查询emp表中的数据

SELECT *FROM EMP;

在简单查询语句中，也可以使用各种四则运算“+、-、*、/”进行数学计算的操作

select empno,ename,sal*12,sal/30 from emp;

为查询结果设置别名

select empno 雇员编号,ename 雇员姓名,(sal+200)*12+500 as 年薪 from emp;

第二章、限定查询与排序显示

限定查询指的是在数据查询时设置一系列的过滤条件，只有满足制定条件后才可以进行显示。

查看emp表中的数据量

select count(*) from emp;

语法：限定查询

select [distinct] *| 列名称 [as] [列名称]，列名称 [as] [列名称]，....

from 表名称 [表别名]

[where 条件(s)];

范例：统计基本工资高于1500元的全部雇员的信息

select * from emp where sal>1500;

1、常用限定运算符

NO运算符符号描述1关系运算符>,<,>=,<=,=,!=,<>进行大小或相等的比较，其中不等于有两种，!=或<>2判断nullIS NULL,IS NOT NULL判断某一列的内容是否为null3逻辑运算符AND,OR,NOTAND表示多个条件必须同时满足，OR表示只需要有一个条件满足，NOT表示条件去反，即：真变假，假变真4范围查询BETWEEN最小值AND最大值，IN,NOT IN在一定范围内进行，查找结果为最小值=<内容<=最大值;IN为在指定数据范围内查找5模糊查询LIKE，NOT LIKE可以对指定的字段进行模糊查询。在LIKE语句中有两个通配符：百分号%、下划线_。百分号可以匹配任意类型和长度的字符，下划线匹配单个任意字符

2、对结果排序：ORDER BY

语法：对结果排序

SELECT [DISTINCT] *|列名称 [AS] 列别名 [AS] 列名称

FROM 表名称表别名

[WHERE 条件（s）]

[ORDER BY 排序的字段 | 列索引序号 ASC|DES,排序的字段2 | 列索引序号 ASC|DES .....]....;

(ASC 升序排序，默认排序；DES 降序排序)

第三章、单行函数

语法：单行函数语法：

funcation_name(列 | 表达式[参数1，参数2])

单行函数主要分为以下几种：

字符函数：接收数据返回具体字符信息
数值函数：对数字进行处理，例如四舍五入
日期函数：直接对日期进行相关的操作
转换函数：日期、字符、数字之间可以完成互相转换功能
通用函数：oracle自己提供的特色函数

3.1、字符函数

RPAD(列 | 字符串，长度，填充字符)

LPAD(列 | 字符串，长度，填充字符)

在右或左填充指定长度字符串10LTRIM(字符串)，RTRIM(字符串)去掉左、右空格11TRIM(列 | 字符串)去掉左右空格12INSTR(列 | 字符串，要查找的字符串，开始位置，出现位置)查找一个子字符串是否在指定位置上出现

3.2、数值函数

NO函数名称描述1ROUND(数字，[，保留位数])对小数进行四舍五入，可以指定保留位数，如果不指定，则表示对小数之后数字全部进行四舍五入2TRUNC(数字，[，截取位数])保留指定位数的小数，如果不指定，则表示不保留小数3MOD(数字，数字)取模

3.3、日期函数

范例：取得当前的系统时间（取得的数据只能是年、月、日、分、秒等数据）

SELECT SYSDATE FROM dual；

如果要精确到毫秒，则用select systimestamp from dual;

修改日期显示格式：ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

范例：

SQL> ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> SELECT SYSDATE FROM dual;

SYSDATE

-------------------

2016-11-07 15:39:26

除取得系统时间外，在oracle中也有日下三个日期操作公式：

日期 - 日期 = 日期

日期 + 数字 = 日期

日期 - 日期 = 数字（天数）

日期操作函数：

NO日期函数描述1ADD_MONTHS(日期，数字)指定的日期上加入指定的月数，求出新的日期（使用日期函数主要是为了考虑闰年的问题，如果用时期加减数字的方式则无法进行准确的日期操作）2MONTHS_BETWEEN(日期1，日期2)求出两个日期间的雇佣天数3NEXT_DAY(日期，星期数)求出下一个星期X的具体天数4LAST_DAY(日期)求出指定日期的最后一天日期5EXTRACT(格式 FROM 数据)日期时间分割，或计算给定两个日期的间隔

范例：验证ADD_MONTHS()函数

SQL> SELECT SYSDATE,

2 ADD_MONTHS(SYSDATE,3) 三个月后的日期,

3 ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,

4 ADD_MONTHS(SYSDATE,60) 六十个月之后的日期

5 FROM dual;

SYSDATE 三个月后的日期三个月之前的日期六十个月之后的日期

------------------- ------------------- ------------------- -------------------------------------- --------

2016-11-07 15:52:34 2017-02-07 15:52:34 2016-08-07 15:52:34 2021-11-07 15:52:34

范例：验证NEXT_DAY()函数

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期一') 下一个星期一,NEXT_DAY(SYSDATE,'星期日')下一个星期日 FROM dual;

SYSDATE 下一个星期一下一个星期日

------------------- ------------------- -------------------

2016-11-07 16:13:43 2016-11-14 16:13:43 2016-11-13 16:13:43

范例：验证LAST_DAY()函数。使用LAST_DAY()函数可以求得指定日期所在月的最后一天日期，如果今天的日期是“2013年01月19日”，则使用LAST_DAY()函数求得的日期是“2013年01月31日”

SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;

SYSDATE LAST_DAY(SYSDATE)

------------------- -------------------

2016-11-07 16:18:03 2016-11-30 16:18:03

范例：验证EXTRACT()函数

此函数的主要功是可以从一个日期时间（DATE）或者是时间间隔（INTERVAL）中截取除特定的部分，此函数使用语法如下：

语法：EXTRACT（）函数语法

EXTRACT([YEAR |　MONTH | DAY |　HOUR | MINUTE |　SECOND] 
        | [TIMEZONE_HOUR |　TIMEZONE_MINUTE]
        | [TIMEZONE_REGION | TIMEZONE_ABBR]
 FROM [ 日期 （date_value） | 时间间隔（interval_value） ])

范例：从日期时间中取出年、月、日数据

SQL> SELECT EXTRACT (YEAR FROM DATE'2001-09-19')years,
  2 EXTRACT(MONTH FROM DATE'2001-09-19')months,
  3 EXTRACT(DAY FROM DATE'2001-09-19')days
  4 FROM dual;

  YEARS      MONTHS     DAYS
---------- ---------- ----------
  2001       9          19

范例：从时间戳中取得年、月、日、时、分、秒

SQL> SELECT EXTRACT(YEAR FROM SYSTIMESTAMP)years,
  2 EXTRACT(MONTH FROM SYSTIMESTAMP)months,
  3 EXTRACT(DAY FROM SYSTIMESTAMP)days,
  4 EXTRACT(HOUR FROM SYSTIMESTAMP)hours,
  5 EXTRACT(MINUTE FROM SYSTIMESTAMP)minutes,
  6 EXTRACT(SECOND FROM SYSTIMESTAMP)seconds
  7 FROM dual;

  YEARS      MONTHS     DAYS       HOURS      MINUTES     SECONDS
---------- ---------- ---------- ---------- ---------- ----------
  2016      11          7           8          56          18.17

3.4转换函数

转换函数主要功能是将一个指定的数据类型变为另一种数据类型，常见的转换函数如下表：

NO函数名称描述1TO_CHAR(日期　|　数字　｜列，转换格式)将指定的数据按照指定的格式变为字符串型２TO_DATE(字符串 |　列，转换格式）将指定的数据按照指定的格式变为DATE型3TO_NUMBER(字符串 | 列)将指定的数据类型变为数字

3.4.1、TO_CHAR（）函数

熟悉格式化日期的替代标记

NO转换格式描述1YYYY完整的年份数字表示，年有四位，所以用四个Y2Y,YYY带逗号的年3YYY年的后三位4YY年的后两位5Y年的最后一位6YEAR年份的文字表示，直接表示四位的年7MONTH月份的文字表示，直接表示两位的月8MM用两位数字表示月份，月有两位，所以用两个M表示9DAY 天数的文字表示10DDD表示一年里的天数（01--366）11DD表示一月里的天数（01--31）12D表示一周里的天数13DY用文字表示的星期几14WW表示一年里的周数15W表示一月里的周数16HH表示12小时制，小时是两位数字，使用两个H17HH24表示24小时制18MI表示分钟19SS表示秒，秒是两位数字，所以使用两个S 20SSSSS午夜之后的秒数字表示（0---86399）21AM|PM(A.M.|P.M.)表示上午下午22FM去掉查询后的前导0，改标记用于时间模板的后缀

范例:格式化当前的日期时间

3.5、通用函数

通用函数指的是oracle中具有一些基本特色的函数，

NO.函数名称描述1NVL(数字 | 列，默认值）如果显示的数字是null的话，则使用默认值表示2NVL2（数字 | 列，返结果如果1（不为空现实），返回结果2（为空现实））判断指定的列是否为null，如果不为null则返回结果为1，如果为空则返回结果为23NULLIF(表达式1，表达式2）比较表达式1和表达式2的结果是否相等，如果相等返回NULL，如果不等返回14DECODE(列 | 值，判断值1，显示结果1，判断值2，显示值2，... ，默认值）多指判断，如果某一个列（或某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值5CASE 列 | 数值 WHEN 表达式1 THEN 显示结果1 ..... ELSE 表达式n .....END用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE之后的表达式处理6COALESCE(表达式1，表达式2，..... ，表达式n)将表达式逐个判断，如果表达式1的内容为null，则显示表达式2，如果表达式2的内容为null，则显示表达式3的内容，以此类推，如果表达式n的结果还是为null，则返回null

范例：验证NULLIF()函数

SELECT NULLIF(1,1),NULLIF(1,4)FROM dual;

0 0