Oracle读书笔记(一)

前言

   sqlplus sys/所设定的密码 as sysdba(用管理员登陆)
   alter user scott account unlock;(解锁scott账户)

1. table structure

   描述某一张表：desc 表名
   select * from 表名

2. select 语句

     计算数据可以用空表：比如：select 2*3 from dual  

     select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。

     select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。(||代表连接符)

3. distinct

      select deptno from emp;
      select distinct deptno from emp;
  ​
      select distinct deptno from emp;
      select distinct deptno ,job from emp
      去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。

4. where

      select * from emp where deptno =10;
      select * from emp where deptno <>10;不等于10        
      select * from emp where ename ='bike';
      select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)空值处理:
      select ename,sal,comm from emp where comm is (not) null;
      select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
      select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
      可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';

5. orderby

       select * from dept; 
       select * from dept order by dept desc;(默认:asc)
       select ename,sal,deptno from emp order by deptno asc,ename desc;

6. sql function1

      select ename,sal*12 annual_sal from emp
      where ename not like '_A%' and sal>800
      order by sal desc;
  ​
      select lower(ename) from emp;
  ​
      select ename from emp 
      where lower(ename) like '_a%';等同于
      select ename from emp where ename like '_a%' or ename like '_A%';
  ​
      select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
      select chr(65) from dual 结果为:A
      select ascii('a') from dual 结果为:65
      select round(23.652,1) from dual; 结果为: 23.7
      select round(23.652,-1) from dual; 20
    
      select to_char(sal,'$99_999_999') from emp;
      select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
  ​
      这个需要掌握牢:
      select birthdate from emp;
      显示为:
      BIRTHDATE
      ----------------
      17-12月-80
      ----------------
  ​
      改为:
      select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
      
      显示:
       
      BIRTHDATE
      -------------------
      1980-12-17 12:00:00
      -------------------
      
      select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
      TO_CHAR(SYSDATE,'YY
      -------------------
      2017-07-15 14:46:14  

      to_date函数:
      select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
      如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY. 

      select sal from emp where sal>888.88 无错.但
      select sal from emp where sal>$1,250,00;
      会出现无效字符错误. 
      改为:
      select sal from emp where sal>to_number('$1,250.00','$9,999.99');s
      
      把空值改为0
      select ename,sal*12+nvl(comm,0) from emp;
      这样可以防止comm为空时,sal*12相加也为空的情况.

7. Group function 组函数

      max,min,avg ,count,sum函数 

      select to_char(avg(sal),'99999999,99') from emp;       

      select round(avg(sal),2) from emp;
      结果:2073.21
        
      select count(*) from emp where deptno=10;
      select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
      select count(distinct deptno) from emp;
      select sum(sal) from emp;

前言

   sqlplus sys/所设定的密码 as sysdba(用管理员登陆)
   alter user scott account unlock;(解锁scott账户)

1. table structure

   描述某一张表：desc 表名
   select * from 表名

2. select 语句

     计算数据可以用空表：比如：select 2*3 from dual  

     select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。 

     select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。(||代表连接符)

3. distinct

      select deptno from emp;
      select distinct deptno from emp;
  ​
      select distinct deptno from emp;
      select distinct deptno ,job from emp
      去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。

4. where

      select * from emp where deptno =10;
      select * from emp where deptno <>10;不等于10        
      select * from emp where ename ='bike';
      select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)空值处理:
      select ename,sal,comm from emp where comm is (not) null;
      select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
      select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
      可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';

5. orderby

       select * from dept; 
       select * from dept order by dept desc;(默认:asc)
       select ename,sal,deptno from emp order by deptno asc,ename desc;

6. sql function1

      select ename,sal*12 annual_sal from emp
      where ename not like '_A%' and sal>800
      order by sal desc;
  ​
      select lower(ename) from emp;
  ​
      select ename from emp 
      where lower(ename) like '_a%';等同于
      select ename from emp where ename like '_a%' or ename like '_A%';
  ​
      select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
      select chr(65) from dual 结果为:A
      select ascii('a') from dual 结果为:65
      select round(23.652,1) from dual; 结果为: 23.7
      select round(23.652,-1) from dual; 20
    
      select to_char(sal,'$99_999_999') from emp;
      select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
  ​
      这个需要掌握牢:
      select birthdate from emp;
      显示为:
      BIRTHDATE
      ----------------
      17-12月-80
      ----------------
  ​
      改为:
      select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
      
      显示:
       
      BIRTHDATE
      -------------------
      1980-12-17 12:00:00
      -------------------
      
      select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
      TO_CHAR(SYSDATE,'YY
      -------------------
      2017-07-15 14:46:14  

      to_date函数:
      select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
      如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.  

      select sal from emp where sal>888.88 无错.但
      select sal from emp where sal>$1,250,00;
      会出现无效字符错误. 
      改为:
      select sal from emp where sal>to_number('$1,250.00','$9,999.99');s
      
      把空值改为0
      select ename,sal*12+nvl(comm,0) from emp;
      这样可以防止comm为空时,sal*12相加也为空的情况.

7. Group function 组函数

      max,min,avg ,count,sum函数  

      select to_char(avg(sal),'99999999,99') from emp;      

      select round(avg(sal),2) from emp;
      结果:2073.21
        
      select count(*) from emp where deptno=10;
      select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
      select count(distinct deptno) from emp;
      select sum(sal) from emp;