1.SQL语句之单表的增删改查和数据备份

增删改查中查是最重要的，也是内容最多的，所以单独讲

单表查询

简单SQL语句

查询表中所有数据  select * from 表名;  *表示所有

 select * from emp;

 查询表中指定字段  select 字段名1,字段名2... from 表名;

select ename,empno from emp;

 查询结果中的字段使用别名  select 字段名 别名 from 表名;          select 字段名 "别名" from 表名;
                                              select 字段名 as 别名 from 表名;     select 字段名 as "别名" from 表名;
                                           （四种均可）注意as关键字可以省略，别名中没有特殊字符双引号也可省略

select empno 员工编号,ename as "姓名" from emp;

连接符    select 字段名||'字符'||字段名||... from 表名
             ||为SQL中的字符连接符，使用在select和from之间
             字符连接符格式为 字段名||'字符'||字段名
             注意：一个拼接好的连接在结果集中作为一个新的字段显示，可以使用别名优化字段显示

select empno||'的姓名是'||ename from emp;
select empno||'的姓名是'||ename as 信息 from emp;

去除重复  select distinct 字段名1,字段名2... from 表名
               注意:去除重复的规则是按行去除的，多行数据完全相同取其一

select distinct job from emp;
select distinct job,mgr from emp;

排序

注意：可以在排序中使用别名
  单字段排序   select 字段名1,字段名2.... from 表名 order by 字段名 asc(升序排序) 不写默认升序排序
                       select 字段名1,字段名2.... from 表名 order by 字段名 desc(降序排序)

select distinct ename,empno from emp order by ename;
select distinct ename,empno from emp order by empno desc;

多字段排序   select 字段名1,字段名2.... from 表名 order by 字段名1,字段名2...
                     先按照字段名1排序，字段名1的值相同按照字段名2排序

字段的逻辑运算  select关键字和from之间的关键字可以直接进行四则运算
                           注意：1.字段与字段之间也可以进行运算
                          2.字段值需要是数值类型，才能进行运算

select empno,ename,sal*2+100,sal+comm from emp;

---

 where子句查询  

select 字段名1,字段名2... from 表名 where 筛选条件
单条件筛选  使用=,>,>=,<,<=,<> 单个条件中
                    注意：如果条件中的值为字符，必须使用单引号括起来

select * from emp where ename='SMITH';
select * from emp where hiredate >='01-1月-1980' order by hiredate;
--注意：oracle默认日期格式为 日-月-年 '03-1月-1980'

多条件筛选  使用and,or,like,escape,is null等关键字

--查询工资在2000-3000之间的员工信息
select * from emp where sal>=2000 and sal<3000;
select * from emp where sal between 2000 and 3000;

--查询工作为SALESMAN,ANALYST的员工信息
select * from emp where job='SALESMAN' or job='ANALYST';
select * from emp where job in('SALESMAN','ANALYST');

--查询姓名中包含S的，以S开头的，以S结尾的，第二个字符为A的用户信息
select * from emp where ename like '%S%';  --like表示姓名中包含S的,%表示任意个任意字符
select * from emp where ename not like '%S%';  --like表示姓名中不包含S的,%表示任意个任意字符
select * from emp where ename like 'S%';  --以S开头的
select * from emp where ename like '%S';  --以S结尾的
select * from emp where ename like '_A%';  --_表示任意一个字符

--查询名字中包含下划线'_'的用户信息
select * from emp where ename like '%A_%' escape 'A' 
--使用escape关键字将普通字符设置为转义字符，又通过转义字符将_转为普通字符

--查询有津贴的员工信息
select * from emp where comm is not null;
--查询没有津贴的员工信息
select * from emp where comm is null;

---

Oracle函数

主要包括单行函数,多行函数,转换函数以及其他函数

单行函数

格式：select 函数名(字段名) from 表名
注意：可以和字段直接混用，实际就是对每行数据进行修饰
字符函数 对字符串进行操作

--查询员工姓名并小写显示
select lower(ename) from emp;

其余函数见下表： 

 数值函数： 对数值类型的数据进行运算 

--查询员工工资并向下取整
select floor(sal) from emp;
--伪表：不存储数据的表，为了方便进行数据的验证而临时存在的表，表名为dual
select abs(-1) from dual;

其余函数见下表： 

日期函数： 对日期进行查找运算的函数

--获取系统当前时间
select sysdate from dual;
select months_between('18-9月-2011','21-12月-2015') from dual;

其余函数见下表：

多行函数 

max(),min(),avg(),sum(),count()
注意：多行函数不能和字段直接混用，除非分组，实际是对查询的数据进行统计

select max(sal) from emp;
select ename,max(sal) from emp;  --错误，很明显前面是14行后面是1行
select count(*) from emp;  --查询表的记录数
select count(comm) from emp;  --查询字段值的数量，null会自动过滤不计
select count(distinct *) from emp;  --查询公司有多少工作种类

转换函数

to_number(数值类型字符)：将字符转换为数值
to_char(数值或日期)：将数值或日期转换为字符
to_date(日期格式字符)：将字符转换为日期
字符转数字  char-->number

select to_number('123') from dual;

数字转字符  number-->char
    指定显示格式
        9表示位置占位，例如999,999,999会将数字按照三个一组使用逗号隔开
        0也可以进行占位分组，但是如果真是数据位数不足，会使用0进行补位
        $表示美元符号  ￥表示人民币符号

select to_char(123) from dual;
select to_char(123456789,'$999,999,999');

字符转换为日期 char-->date
    使用to_date('要转换的字符'，日期格式)函数将字符转换为日期
    注意：1.字符必须符合日期格式
               2.oracle默认的转换格式为日月年，例如(01-1月-2015)
               3.常用日期格式：  yyyy-mm-dd      yyyy/mm/dd

select * from emp where hiredate>'01-1月-1980';  --默认格式
select * from emp where hiredate>to_date('1980-01-01','yyyy-mm-dd');
select * from emp where hiredate>to_date('1980-01-01','yyyy/mm/dd');

日期转字符  date-->char
    使用to_char('要转换的日期')

select to_chat(hiredate) from emp  --使用默认格式将日期转换为字符
select to_chat(hiredate,'yyyy-mm-dd') from emp  --使用指定格式将日期转换为字符
select to_chat(hiredate,'yyyy/mm/dd') from emp  --使用指定格式将日期转换为字符
select to_chat(hiredate,'yyyy"年"mm"月"dd"日"') from emp  --使用指定格式将日期转换为字符

其他函数

nvl() : 如果字段值不为null,返回该字段的值。如果为null则返回新的值
        格式：nvl(字段名,新的值)
nvl2() 如果字段不为null,执行处理1，否则执行处理2
        格式：nvl2(字段名，处理1，处理2) 
decode()：如果字段中的值和decode中的条件值相同执行对象的处理。如果都没有执行公共处理
        格式：decode(字段名,值1,处理1,值2,处理2,,...公共处理)

--查询员工工资信息
select ename,job,sal from emp
--查询员工薪水(工资加奖金)
select ename,job,sal+nvl(comm,0) from emp;
select ename,job,nvl2(comm,sal+com,sal) from emp;
--显示员工职称
select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','普通员工') from emp;

---

分组查询和分组筛选

关键字：group by 分组字段名
      注意1.使用了分组后，在select语句中只允许出现分组字段和多行函数
              2.如果是多字段分组，则先按照第一字段分组，后按照第二字段分组，一次类推
              3.在where子句中不允许出现多行函数
分组筛选
    关键字：having
        作用：针对分组进行分组后的数据筛选，允许使用多行函数
        注意：having关键字必须和group by一块使用，不能单独使用
        where和having的比较：
            where子句不允许出现多行函数，having子句允许出现多行函数
            where子句和having子句都可以使用普通字段进行筛选，但是where效率高于having
            where执行顺序：from-->where-->group by-->select-->order by
            having执行顺序：from-->group by-->select-->having-->order by
        结论：在分组语句中，使用where进行字段筛选，使用having进行多行函数筛选

--查询不同部门的最高工资
select deptno,max(sal) group by deptno;
--查询不同工作岗位员工数
select job,count(*)from emp group by job;
--查询不同工作部门不同岗位工作人数
select deptno,job,count(*) from emp group by deptno,job order by deptno;

--查询不同部门的不同工作岗位的并且人数大于1的信息
select deptno,job,count(*)from emp group by deptno,job having count(*)>1 order by deptno;
--查询部门号大于10的不同部门的不同工作岗位的人数
select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno;
select deptno,job,count(*)from emp group by deptno,job having deptno>1 order by deptno;

---

单表增删改

数据库增删改&数据备份 
    注意：增删改的数据SQL语句执行完毕后，不会立马进行数据的写入。
          还需要手动对数据进行提交，如果数据有问题还可以回滚
    主键：在一张表中，某个字段的值是非空唯一的，将此字段设置为主键
         作用：唯一的标识一条数据

增加数据

 insert into 表名(字段名1,字段名2,...) values(值1,值2,...)
    注意1：主键必须赋值，允许为空的字段可以不赋值
            2.插入语句表名后跟的字段名为要赋值的字段，值和字段必须一一对应
           3.如果是全字段插入，可以省略字段名

--在部门中新增一个新的部门信息 编号：50 名称：哈哈 地址：北京
insert into dept(deptno,dname,loc) values (50,'哈哈','北京');
insert into dept values (60,'哈哈','北京');

删除数据

delete from 表名 (删除表中所有数据)
truncate table 表名 删除表中所有数据，效率高于delete
delete from 表名 where 条件  (删除符合条件数据)

--删除刚才添加的一条信息
delete from dept where deptno=60;

更新数据

update 表名 set 字段名1=新值1,字段名2=新值2...  (表中所有数据全部更改)
update 表名 set 字段名1=新值1,字段名2=新值2...where 条件 (符合条件的数据更改)

--将添加的数据名称改为嘿嘿 地址改为上海
update dept set where dname='嘿嘿',loc='上海' where deptno=50;

---

数据的备份

注意：只会备份表结构和表数据，约束不会备份

表级别备份

全部备份：create table 新表名 as select * from 要备份的表名;
部分备份：create table 新表名 as select 字段名1,字段名2... from 要备份的表名;

create table deptBak as select * from dept;

数据整体插入

insert into 插入表名 select * from 表名;
注意：查询语句结果的字段数据必须和插入表名的字段数量一致，类型一致