Oracle—SQL基础语句

数据是描述事物的符号，用于文本，图形，音频，视频
数据库是存放数据的仓库，存放在计算机中，按照一定格式存放，可为用户共享
数据库管理系统（Datebase Management System,DBMS）
数据库应用程序（DBAS）
常用软件：Oracle MySQL SQL Srever DB2 FoxPro
数据库分为网状数据库、层次数据库、关系数据库和NOSQL数据库
黑框运行步骤：
cmd–>sqlplus–>sys as sysdba
解锁scott用户：
scott tiger–>
alter user scott account unlock;（解锁）
alter user scott account lock;（上锁）
修改用户权限：
conn scott
以管理员权限修改用户密码：alter user 用户名 identified by 新密码

结构化查询语言（Structured Query Language）
CRUD增删改查
数据操作语言：（DML）select insert update
权限控制语言：（DCL）grant revoke
事物控制语言：（TCL）save point rellback set transaction commit
对象操作语言：（DDL）create alter drop

关系：代表整个二维表
关系名：代表表格名称
元组：代表行数据
属性：代表列数据
属性名：代表列名称
主键：代表唯一确定元组的属性组
域：代表属性的取值范围

字段别名的优点：
1、重命名查询结果中的字段，以增强可读性
2、别名如果含有空格或其他特殊字符或大小写敏感，需用双引号引起来。
3、AS可以省略

去除重复行：distinct
作用范围：是后面所有字段的组合
例：select distinct deptno job from emp;
使用order by 排序：升序：asc 和降序：desc
例：select empno, ename, sal from emp order by sal;（默认升序排序）
select empno, ename, sal from emp order by sal desc ;

查询条件使用where子句：
例：select * from emp where deptno=10;（int类型的条件）
select * from emp where ename = 'SMITH';（char类型的条件）
select * from emp where hiredate = '02-4月-81';（date类型的条件）
注意：
1、字符串和日期值要用单引号扩起来
2、字符串大小写敏感
3、日期值格式敏感，缺省的日期格式是’DD-MON-YY

查询条件可以使用比较运算符
例：select * from emp where sal > 2900;（大于）
select * from emp where deptno <> 30;（不包涵）

使用between…and…（什么之间）查询
例：select * from emp where sal between 1600 and 2900;（之间）
使用in(set)（出现在集合中）查询
例：select * from emp where ename in('SMITH','CLARK','KING');（集合中的姓名）
使用like模糊查询（%代表0或多个字符，_代表一个字符，特殊字符用ESCAPE标示符查询）
例：select * from emp where ename like 'S%';
select * from emp where ename like '_A%';
select * from emp where ename like '%\_%' escape '\';
使用is null （为空值）查询
例：select * from emp where comm is null;（是空值）
select * from emp where comm is not null;（不是空值）

SQL优化问题：
AND: 把检索结果较少的条件放到后面
OR: 把检索结果较多的条件放到后面

SQL可用的四种运算符（运算优先级）：算术>连接>比较>逻辑

Oracle函数分为单行函数和多行函数两大类
单行函数分为字符函数、数值函数、日期函数、转换函数、通用函数
多行函数分为sum()、avg()、count()、max()、min()
首字母大写：initcap
全转小写：lowek
全转大写：upper
左移除：ltrim
例：ltrim('abcdef','ab');
右移除：rtrim
例：rtrim('abcdef','ef');
翻译：translate
例：translate('jackd','abcdef','123456')结果：j13k4
替换：replace
例： 替换字符：replace('jack and jue','j','bl')结果：black and blue
查找字符串的位置：instr
例：instr('worldwide','d') 结果：5
截取字符串：substr
例：substr('abcdefg',3,2) 结果：cd
连接字符串：concat
例：concat('Hello','world') 结果：Helloworld

数值函数：
取绝对值：ABC()
向上取整：CEIL()
正弦：SIN()
余弦：COS()
取符号：SIGN()
向下取整：FLOOR
m的n次幂：POWER(m,n)
取余数：MOD(m,n)
四舍五入：ROUND(m,n)
截断：TRUNK(m,n)
平方根：SQRT()

日期函数（sysdate：当前日期时间）
MONTHS_BETWEEN （返回两个日期间的月份）
ADD_MONTHS（返回把月份数加到日期上的新日期）
NEXT_DAY（返回指定日期后的星期对应的新日期）
LAST_DAY（返回指定日期所在的月的最后一天）
ROUND（按指定格式对日期进行四舍五入）
TRUNC（对日期按指定方式进行截断）

Oracle的类型转换分为自动类型转换和强制类型转换。
select '12.5'+30 from dual;(自动类型转换)
select '12.5'||30 from dual;(强制类型转换)
字符和数字的相互转换 &字符和日期的相互转换
VARCHAR2 or char－－number
VARCHAR2 or char －－date
number－－varchar2
date－－varchar2
数据类型之间可以进行隐式转换，仍建议使用显示转换函数，以保持良好的设计风格
有类型的转换，转换分为两种，隐式类型转换和显示类型转换
常用类型转换函数有to_char(), to_date(), to_number()
yyyy、yy代表四位、两位数字的年份
MM用数字表示月份
MON月份的缩写、对中文月份来说是全称
DD数字表示的日
DY星期的缩写，对中文的星期来说是全称
HH24、HH12 12小时或者24小时进制下的时间
MI分钟数
SS秒数
9代表一位数字
0代表一位数字如果空则显示0
$ 显示美元符号
L显示本地货币符号
.显示小数点
,显示千分位符号

to_char() 例：SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;
说明：日期格式可缺省为（DD-MON-YY），可使用SYSDATE函数获取当前系统日期和时间
to_date() 例：insert into test2 values('Tom', to_date('2008-02-28 ', 'yyyy-mm-dd '));
NVL(EXP1, EXP2) 用于将空值null替换为指定的缺省值，适用于字符、数字、日期等类型数据
例：select empno, ename, hiredate, nvl(hiredate, sysdate) from emp;
NVL2(EXP1, EXP2, EXP3)用于实现条件表达式功能
例：select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;
DECODE(VALUE,IF1,THEN1,IF2,THEN2,……,ELSE) 如果value的值为if1，则返回then1的值，如果value的值为if2，则返回then2的值，……，否则返回else值
多行函数对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数
例：select avg(sal), max(sal), min(sal), sum(sal) from emp;
使用NVL()函数强制多行函数处理空值
例：select count(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0)) from emp;
count(*)返回组中总记录数目；
count(exp)返回表达式exp值非空的记录数目；
count(distinct(exp))返回表达式exp值不重复的、非空的记录数目
GROUP BY 子句将表中数据分组
例：select deptno, job, count(*),avg(sal) from emp group by deptno, job;
注意：
1、出现在SELECT列表中的字段，如果不是包含在多行函数中，那么该字段必须同时在GROUP BY子句中出现
2、包含在GROUP BY子句中的字段则不必须出现在SELECT列表中，如果没有GROUP BY子句
3、SELECT列表中不允许出现字段（单行函数）与多行函数混用的情况
4、不允许在WHERE 子句中使用多行函数

having从句过滤分组后的结果，它只能出现在group by从句之后
where过滤行，having过滤分组。having支持所有where操作符。
例：1、在emp表中，列出工资最小值小于2000的职位。

    Select job,min(sal) 
    from emp 
    group by job
    having min(sal)<2000;

2、列出平均工资大于1200元的部门和工作搭配组合

    select deptno, job, avg(sal)   
    from emp 
    group by deptno,
    job  having avg(sal) > 1200  
    order by deptno,job;

3、统计人数小于4的部门的平均工资。

    select deptno, count(*),avg(sal)
    from emp
    group by deptno
    having  count(*)<4;

4、统计各部门的最高工资，排除最高工资小于3000的部门

    select deptno,max(sal)
    from emp
    group by deptno
    having not max(sal)<3000;

DML—insert插入语句
INSERT INTO table [(column [, column…])] VALUES (value [, value…]);
添加部分列但要求省略的列必须满足下面的条件：
1、该列定义为允许Null值。
2、在表定义中给出默认值，这表示如果不给出值，将使用默认值。

创建一个临时表

    create table temp
    As
    select * from emp
    where 1 = 2

DML—update语句
UPDATE table
SET column = value [, column = value] …
[WHERE condition];
DML—delete语句
DELETE [FROM] table
[WHERE condition];
delete from table 表示从表中删除一切元组
truncate table删除所有行，速度快，不可还原
为表添加注释
comment on table emp is ‘雇员表';

为列添加注释
comment on column emp. Empno is '雇员工号';
查看所有表：select table_name from user_tables;
查看表结构：describe dept;(或者desc dept;)