SQLPLUS命令

执行sql语句的主要命令

格式化命令

set pagesize 30;设置每页的长度

set linesize 30;设置每行的长度

调用本机命令

host copy 源地址 目标地址：

echo hello world

     ORACLE，甲骨文，源于IBM的一篇关系型数据库的论文

     云时代？

   ORACLE8/ORACLE 8i:"i"表示internet，表示oracle进军网络

    Oracle9i(3cd):8i的稳定版本，依然大范围使用

DML 数据操作语言，开发中使用的部分

DDL 数据定义语言，开发前的设计

DCL 数据库控制语言，系统人员工作

SCOTT 数据表结构，重点要背下来。

--查询用户

1、show user 

--连接到scott 用户

2、conn scott /tiger

--查看用户表，有四张表

3、select * from tab;

表名：BONUS，DEPT，EMP，SALGRADE

--查看表结构，DESC 表名称。

查看Dept表结构。

4、DESC dept;

--查询 部门信息

5、select * from dept;

select  、from 、where、group by 、having、order by、

insert、update、delete、create、drop、alter、grant、revoke.

一、多表查询的实现形式

    1.1 基本语法：SELECT [DISTINCT] * | 列名 [别名], 列名 [别名], ... FROM 表名[别名], 表名[别名] [WHERE 过滤条件(s)] [ORDER BY 列名 [ASC | DESC], 列名 [ASC | DESC]...];

二、笛卡尔积的处理

三、数据表的连接操作

四、数据的集合操作

1、简介：完成某一具体功能的操作函数。

2、基本语法：返回值 函数名称(参数)

    2.1 通用函数：Oracle的特色函数

        2.1.1 数字 NVL(列 | NULL, 默认值)：如果传入的内容是空，则使用默认数字处理，如果不是空则原样处理

        2.1.2 数据类型 DECODE(列 | 字符串 | 数值, 比较内容1, 显示内容1, 比较内容2, 显示内容2,...[, 默认显示内容])：传入的内容会与每一个比较内容进行比较，输出相同的比较内容对应的显示内容，如果都不相同，则使用默认内容输出。

1、简介：完成某一具体功能的操作函数。

2、基本语法：返回值 函数名称(参数)

    2.1 转换函数

        2.1.1 字符串 TO_CHAR(列 | 日期 | 数字, 转换格式)：将日期或数字转化为字符串

        2.1.2 日期 TO_DATE(列 | 字符串, 转换格式) 将按照日期格式书写的字符串转化为日期

        2.1.3 数字 TO_NUMBER(列 | 字符串)：将字符串变为数字

    2.2 使用方法及注意事项

        转换格式：

• 日期：yyyy(年)、mm(月)、dd(日)
• 时间：hh/hh24(时)、mi(分)、ss(秒)
• 数字：9(任意数字)、L(本地货币符号)

        SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM dual;

        SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh:mi:ss') FROM dual;

        SELECT TO_CHAR(SYSDATE, 'mm') FROM dual;

        SELECT TO_CHAR(2556323552, '999,999,999,999,999') FROM dual;

        SELECT TO_DATE('2019-08-29', 'yyyy-mm-dd') FROM dual;

1、简介：完成某一具体功能的操作函数。

2、基本语法：返回值 函数名称(参数)

    2.1 日期函数

        2.1.1 日期 ADD_MONTH(列 | 日期, 月数)：计算在指定的日期上增加 <月数>个月后的日期

        2.1.2 数字 MONTHS_BETWEEN(列 | 日期, 列 | 日期)：返回两个日期之间的月数

        2.1.3 日期 LASDT_DAY(列 | 日期)：取得指定日期所在月的最后一天

        2.1.4 日期 NEXT_DAY(列 | 日期, 星期X)：返回下一个指定的星期X对应的日期

    2.2 使用方法及注意事项

        Oracle提供了伪列(SYSDATE, SYSTIMESTAMP)以方便获取当前时间。

        SELECT SYSDATE FROM dual;

        SELECT SYSTIMESTAMP FROM dual;

        伪列可直接作为任意表的列进行查询。

        SELECT ename, job, sal, SYSDATE FROM emp;

        日期有如下操作公式：

• 日期 + 数字  = <数字>天后的日期
• 日期 - 数字  = <数字>天前的日期
• 日期 - 日期 = 两个日期间隔的天数

1、简介：完成某一具体功能的操作函数。

2、基本语法：返回值 函数名称(参数)

    2.1 数值函数

        2.1.1 数字 ROUND(列 | 数字[, 小数位])：对数字进行四舍五入操作

        2.1.2 数字 TRUNC(列 | 数字[, 小数位])：截取数字，不进位

        2.1.3 数字 MOD(列 | 数字, 列 | 数字)：求模

    2.2 使用方法及注意事项

        如果不设置小数位，会直接取整。

1、简介：完成某一具体功能的操作函数。

2、基本语法：返回值 函数名称(参数)

    2.1 字符串函数

        2.1.1 字符串 UPPER(列 | 字符串)：将传入的字符串全部变为大写

        2.1.2 字符串 LOWER(列 | 字符串)：将传入的字符串全部变为小写

        2.1.3 字符串 INITCAP(列 | 字符串)：首字母大写，其余字母小写

        2.1.4 数字 LENGTH(列 | 字符串)：取得指定字符串的长度

        2.1.5 字符串 SUBSTR(列 | 字符串, 开始索引, [长度])：从开始索引起，取得[长度]长度的子串

        2.1.6 字符串 REPLACE(列 | 字符串, 旧内容, 新内容)：将指定字符串里的旧内容替换为新内容

    2.2 使用方法及注意事项

        Oracle中，函数要运行必须编写SQL语句。为方便测试操作，有一个虚拟表：“dual”。

        SELECL UPPER("heLlo") FROM dual;

        SELECL UPPER(ename) FROM emp;

        Oracle可完成接收用户输入的操作，使用"&"操作符标记变量即可。如果需要单引号，直接写上就行。

        SELECL * FROM emp WHERE UPPER(ename = '&name');

        SELECL * FROM emp WHERE LENGTH(ename) = 5;

        Oracle中字符串起始索引为1，如果指定为0，会自动替换为1。

        SELECL SUBSTR('hello', 0, 3) FROM dual;

        SELECL SUBSTR('hello', 1, 3) FROM dual;

        Oracle中，截取字符串后三位字符可直接指定负数长度。

        SELECL SUBSTR('hello', -3) FROM dual;

1、选择部门30中的全部员工；

        SELECT * FROM emp WHERE depno = '30';

        SELECT * FROM emp WHERE deptno = 30;

2、列出所有办事员的姓名、编号和部门编号；

        SELECT ename, eno, depno FROM emp WHERE ejob = 'CLERK';

        SELECT ename, empno. deptno FROM  emp WHERE job = 'CLERK';

3、找出佣金高于薪金60%的员工；

        SELECT * FROM emp WHERE yj > (sal * 0.6);

        SELECT * FROM emp WHERE comm > (sal * 0.6);

4、找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)；

        SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER', deptno = 20 AND job = 'CLERK';

        SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK');

5、找出部门10中所有经理(MANAGER)，部门20中所有办事员(CLERK)，既不是经理又不是办事员但其薪金大于等于2000的所有员工；

        SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') OR (job NOT IN ('MANAGER', 'CLERK') AND sal >=2000);

        SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') OR (job NOT IN ('MANAGER', 'CLERK') AND sal >=2000);

6、找出收取佣金的员工的不同工作；

        SELECT DISTINCT job FROM emp WHERE comm NOT NULL;

        SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

7、找出不收佣金或收取佣金低于100的员工；

        SELECT * FROM emp WHERE (comm IS NULL) OR (comm IS NOT NULL AND comm < 100);

        SELECT * FROM emp WHERE (comm IS NULL) OR (comm < 100);

8、显示名字不带'R'的员工的姓名；

        SELECT * FROM emp WHERE NOT ename LIKE '%R%';

        SELECT * FROM emp WHERE ename NOT LIKE '%R%';

9、显示姓名包含'A'的员工的姓名，显示的结果基于基本工资按照从高到低排序，如果基本工资相同则按照雇佣年限由早到晚排序，如果雇佣日期相同，则按职位排序；

        SELECT * FROM emp WHERE ename LIKE '%A%', ORDER BY sal DESC, hiredate, job;

        SELECT * FROM emp WHERE ename LIKE '%A%' ORDER BY sal DESC, hiredate, job;

多条件判断使用逻辑连接，使用"()"分隔每个条件。

1、简介：查询时，会按照数据的插入顺序显示结果。如果要针对指定列进行排序显示，则需要ORDER BY子句。

2、基本语法：SELECT [DISTINCT] * | 列名 [别名], 列名 [别名], ... FROM 表名[别名] [WHERE 过滤条件(s)] [ORDER BY 列名 [ASC | DESC], 列名 [ASC | DESC]...];

    2.1 以上结构中，首先执行FROM子句，确定数据来源；使用WHERE找到显示满足条件的数据行；执行SELECT子句控制要显示的数据列；执行ORDER BY针对查询结果进行排序。

    ORDER BY在SELECT之后执行，所以可以使用SELECT定义的别名。

    2.2 排序方式：

        2.2.1 ASC(默认)：升序排列

        2.2.2 DESC：降序排列

    3.3 查询table的全部数据，以col从高到低排序

            SELECT * FROM table ORDER BY col DESC;

1、简介：在简单查询的基础上，可控制结果中只保留需要的数据行。

2、基本语法：SELECT [DISTINCT] * | 列名称 [别名], 列名称  [别名], ... FROM 表名[别名] [WHERE 过滤条件(s)];

    2.1 以上结构中，首先执行FROM子句，确定数据来源；然后使用WHERE找到显示满足条件的数据行；最后执行SELECT子句控制要显示的数据列。

    行数据的过滤，采用的是条件的形式。

    WHERE一般写在FROM之后，但它紧接着FROM执行；SELECT写在最前，但由于它在WHERE之后执行，所以SELECT中定义的别名WHERE子句中无法使用。

    WHERE控制显示数据行，WHERE控制显示数据列。

    2.2 以上结构中，需用到限定查询符：

        2.2.1 关系运算符：>, <, >=, <=, !=(<>), =

• 作用：比大小， boolean
• 查询table表中col2大于A的col1列信息

            SELECT col1 FROM table WHERE col2 > A;

• 查询table中col列大于A的全部信息

            SELECT * FROM table WHERE col > A;

• 查询table中，col是B的信息

            SELECT * FROM table WHERE col = B;

            Oracle中，字符串字母区分大小写

            Oracle中，不需要针对不同数据类型使用相对应的运算符。运算符适配全部数据类型

        2.2.2 逻辑运算符：AND, OR, NOT

• 作用：连接多个条件
  • AND：所有参与连接的条件，都要满足
  • OR：所有参与连接的条件里，有一个满足即可
  • NOT：对条件结果取反
    • 查询table中同时满足条件A和B的信息

                                SELECT * FROM table WHERE A AND B;​​​​​​​​​​​​​​​​​​​​​

        2.2.3 范围运算符：BETWEEN...AND

• 作用：对数据进行范围判断
  • 查询table中，col在A和B之间的信息

                    SELECT * FROM table WHERE col BETWEEN A AND B; 

        2.2.4 谓词范围：IN, NOT IN

• 作用：判断数据是否在给定的范围内，与BETWEER...AND的区别在于IN的范围更具体，不连续
  • 查询table中，col为A、B、C的信息

                    SELECT * FROM table WHERE col IN (A, B, C);

                    SELECT * FROM table WHERE col IS A OR col IS B OR col IS C;

• NOT IN操作中如果掺杂了NULL，不会有任何返回结果。因为有些数据永远不可能为空，所以NOT IN NULL就等于针对全部数据进行操作，数据库对此进行了限制，不会返回结果。
• IN操作中使用NULL，不会对结果有任何影响。

        2.2.5 空判断：IS NULL, IS NOT NULL

• “空”：不确定的内容。数值类型中的“NULL”不是0。针对“空”的判断不能用关系运算符，只能用空判断符。
• 作用：判断某数据是否不为空。
  • 查询table中，所有有col的信息。

                    SELECT * FROM table WHERE col IS NOT NULL;

                    SELECT * FROM table WHERE NOT col IS NULL;

        2.2.6 模糊查询：LIKE

• 作用：在数据库中实现模糊查询
• 可使用两个通配符：
  • "_"：匹配任意一位字符
  • "%":匹配任意零位、一位、多位字符
• 查询table中，col的第二位是A的全部信息

            SELECT * FROM table WHERE col LIKE '_A%';

• LIKE可用在全部数据类型上
• 使用LIKE时，不指定关键字，则查询全部数据。

1、简介：查询一张表中全部数据行记录。简单查询可以控制显示列，但不能控制行。

2、基本语法：SELECT [DISTINCT] * | 列名称 [别名], 列名称  [别名], ... FROM 表名[别名];。

    2.1 以上结构中，首先执行FROM子句，确定数据来源；然后执行SELECT子句，筛选要显示的数据列。

3、简单范例：

    3.1 查询表中全部记录

        SELECT * FROM table;

    3.2 查询表中特定数据列

        SELECT col1, col2, col3 FROM table;

    3.3 查询特定数据列，去除结果中的重复信息

        SELECT DISTINCT col1 FROM table;

        如果有多列，DISTINCT只会消除结果中全部都相同的行

    3.4 查询特定数据，对其中指定数据进行运算

        SELECT col1, col2 * X [别名] FROM table;

        别名不建议使用中文

        SELECT col1,

                     col2 * X + (a + b + c) * Y + M * N [别名]

        FROM table;

    3.5 SELECT子句中，可使用常量。常量会原样输出

• 如果常量是字符串，用'括起来。'字符串'
• 如果常量是数字，直接写上就可以。100
• 如果常量是日期，按日期分隔形式，以字符串方式填写。'12-08-19'

     3.6 SELECT子句中，可以用||将不同列连接

        SELECT '第一列：' || col1 || '，第二列：' || col2 DATA FROM table;

        DATA

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

        第一列：123，第二列：asd

        别名不需要声明，字符串一定要用单引号括起来。

1、SQL简介：SQL是结构化查询语言。为使用方便，由IBM在70年代末开发。

2、常用命令：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、GRANT、REVOKE。

3、SQL的分类：

    1.DML(数据操作语言)：数据库的查询与更新操作，开发中使用。

    2.DDL(数据定义语言)：数据对象(表、用户)的创建；开发前的设计。

    3.DCL(数据库控制语言)：权限管理操作，系统人员的常用语言。

4、用户表结构相关：

    1.SELCET * FROM tab;：查询用户下的所有数据表；

    2.DESC [表名];：查询数据表的结构。

    3.NUMBER(7,2)：小数类型，小数位占两位，整数位占5位。

5、显示格式控制：

    1.列显示宽度：COL [列名] FOR A10;；

强烈要求使用命令行。

1、sqlplus命令由Oracle提供，可直接在运行中调用。需要输入用户名和密码，但密码不会回显。

2、可在cmd调用sqlplus，输入格式为：sqlplus scott/tiger。密码会直接明文显示。

3、查询表中所有数据：SELECT * FROM emp;。

4、页面格式化指令：

    1.设置每行显示数据长度：SET LINESIZE 300;。需注意，这个显示会受到命令行窗口的影响，所以需要调整命令行窗口的大小。

    2.设置每页显示数据的行数：SET PAGESIZE 30;。

5、启动本地记事本程序：ed [FileName];(默认后缀sql)。如果文件名称不存在，会提示创建，存在则会打开。本命令为阻塞式命令，需关闭后才可继续操作。

6、执行文件中的命令：@[FileName](默认后缀sql)。

7、切换当前用户：CONN UserName/Pass [AS SYSDBA];；使用sys用户登陆时，需AS SYSDBA。

8、访问其他用户的数据表：SELECT * FROM 模式名称/用户名.表名;(模式名称已经基本等同于用户名)；

9、调用本机操作系统的命令：HOST [Command];；

1、Oracle下载链接：

https://www.oracle.com/cn/downloads/#category-database

2、安装注意事项：

Oracle个人学习使用时不收费，商用时会收数据保险费用。

安装之前，建议：

    1.关闭安全软件；

    2.断开网络连接。

安装步骤：

    1.执行setup.exe；

    2.询问是否填写邮箱以接收Oracle的更新信息和安全通知，但填写意义不大；

    3.安装选项中，选择安装和配置数据库，因为是要直接使用的；

    4.选择系统类型，对于个人学习选择服务器类即可；

    5.数据库安装类型，个人的学习中不会涉及到RAC管理，所以选择单实例安装；

    6.为方便配置，选择高级安装配置；

    7.产品语言：英语、中文；

    8.数据库版本，选最全的企业版；

    9.安装位置，根据自己实际情况选择即可。

    10.配置类型，默认即可。

    11.数据库标识符(名称)，自己定义即可，但需要记录好；

    12.配置选项

        12.1字符编码，设置为UTF-8，否则可能中文乱码；

        12.2示例方案，为学习方便，选择创建具有示例方案的数据库；

    13.一直默认直到方案口令；

    14.方案口令，选择“对所有账户使用相同的口令”，输入自己可以记住的就行，可忽略密码安全安全警告；

    15.先决条件检查，如果出现错误提示可直接忽略，开始安装进程；

    16.安装完成，使用“口令管理”。

        16.1超级管理员：sys/change_on_install

        16.2普通管理员：system/manager

    以下两个需要解锁(取消选择复选框即可)：

        16.3普通用户：scott/tiger

        16.4大数据用户(样本数据库)：sh/sh

        16.5忽略口令的安全警告。

    17.安装完成之后会自动注册这些服务，其中有些会设置为“自动”。建议除以下全部修改为“手动”。

        17.1***TNSListener：监听服务，如果要通过程序或不同客户端连接服务器时，此服务必须启动。

        17.2OracleServiceSID：数据库实例服务，每当为系统增加数据库时，都会出现一个类似服务。

3、Oracle卸载注意事项：

    1.Oracle正常安装完成
        a.使用Oracle提供的卸载程序

        b.重新启动电脑，进入安全模式

        c.找到Oracle安装路径，删除

        d.进入注册表，删除所有“oracle”相关内容

        e.重新启动电脑即可开始下次安装

    2.Oracle安装失败

        a.重新启动电脑，进入安全模式

        b.找到Oracle安装路径，删除

        c.进入注册表，删除所有“oracle”相关内容

        d.重新启动电脑即可开始下次安装

1、Oracle中文含义：“甲骨文”。

2、Oracle主要版本：

1CD = 630M。

Oracle8i，1CD，开始进军网络。

Oracle9i，3CD，8i的稳定版。

Oracle10g，1CD，使用网格计算的方式提升数据库分布式访问的性能。

Oracle11g，1.7G，Oracle实际的稳定版本。

Oracle12C，初学者不推荐使用，提供云服务支持。

推荐使用11G。

命令行形式连接数据库：

sqlplus scott/tiger

一、分组统计查询：

1、基础统计函数的使用。

2、分组统计操作的实现，结合多表查询使用分组统计。

二、统计函数。

count(*| 【DISTINC】 )、MAX( )、MIN( )、SUM( )、AVG( )

三、

数据集合操作：

UNION | UNION ALL | INTSECT  |  MINUS

UNION：遇见有相同的数据，不重复显示。

UNION ALL ：所有的重复数据都会进行显示。

INTSECT:返回了相同的数据部分，属于交集的操作。

MINUS：第一个集合减去第二个集合

总结：

1、多表查询存在笛卡尔积，所有优秀的系统设计的时候绝对不可能去考虑多表查询。

2、多表连接查询时，必须会存在有关联字段或者是关联条件，否则无法进行多表查询

3、大部分情况下使用都是内连接操作，外连接Oracle提供的控制是“（+）”；

4、在Oracle之外的数据库（SQL Server、MySQL）那么就必须利用SQL：1999语法实现外连接操作；但是，一般情况下，不建议大家这么使用。

5、可以使用多个查询结果合并到一起显示，但是要求，多个查询结果返回的结构必须相同。