SQL基础-1

sql基础

SQL入门

• 索引列加函数，加减乘除之类的，便不会再走索引

• truncate(DDL) 不可以回滚彻底清空，delete(DML)可以回滚

• 加锁： for update nowait

• 命名规范：项目简称_模块_业务名称

• 比较：
  SELECT department_id,
  AVG(salary) avgs
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) > 8000;

    select * from (SELECT department_id,
           AVG(salary) avgs
      FROM employees
     GROUP BY department_id)
    where avgs > 8000;
  

Select

字符串拼接 ：
Select name ||‘is a’|| job From …==> wang is a man

去除重复项 ：
Select DISTINCT job_id from…
尽量不用，性能消耗大，涉及排序

条件限制和排序

1. between…and：
   Select … From …
   where salary between 4000 and 8000

2. in：
   Select … From …
   where salary in(6000,8000);

3. like：
   Select … From …
   where name like ‘%风%’（前后模糊）
   where name like ‘_林%’（第二位为林字的姓名）escape转义通配符

4. isNull:
   Select … From …where job_id is null;

5. order by:
   asc 升序 默认方式
   desc 降序

单行函数

1. 大小写转换：
   LOWER UPPER INTERCAP

2. 字符串操作函数：
   CONCAT(‘hello’,‘world) ==>helloword
   SUBSTR(‘helloworld’,1,5) ==>hello
   LENGTH(‘helloworld’) ==>10
   INSTR(‘helloworld’,W) ==>6
   LPAD(salary,10,’’) ==>*****24000
   RPAD(salary,10,’’) ==>24000*****
   TRIM( helloworld ) ==>helloworld 中间空格去不掉

3. 数字操作函数：
   ROUND(46.0769,2) ==>46.08 四舍五入
   ROUND(46.0769,-1) ==>50 个位数四舍五入
   TRUNC(46.0769,2) ==>46.07
   MOD(1600,300) ==>1600/300 余100

4. 日期操作函数：
   SELECT TO_DATE(‘2016/07/01’,‘yyyy/mm/dd HH24:MI:SS’) from dual;
   select to_char(sysdate,‘YYYY/MM/DD HH:MI:SS’) FROM DUAL;
   select months_between(sysdate + 31, sysdate) from dual;
   lect NEXT_DAY (‘01-SEP-95’,1) from dual;
   select ROUND(‘25-JUL-95’,‘MONTH’) from DUAL;
   select ROUND(SYSDATE ,‘YYYY’) from DUAL;

5. 数据类型转换 隐式转换规则：

    对于赋值操作可以:
    varchar or char --->number
    number --->varchar
    date --->varchar
    varchar--->date
   
   
    对于表达式比较操作仅可以：
    varchar --->number
    number --->varchar
   

6. 数据类型转换 显式转换规则：

    varchar--(to_number)-->number--(to_char)-->varchar
    varchar--(to_date)-->date--(to_char)-->varchar
   

7. 其他常用单行函数：

   NVL(expr1,expr2) 如果expr1为空，则返回expr2
   NVL2(expr1,expr2,expr3)如果expr1为空返回expr3,否则返回expr2
   NULLIF(expr1,expr2) 如果expr1=expr2，则返回空，否则返回expr1
   COALESCE(expr1…exprn) 一直找到不为空的返回，全部为空返回null

多表连接查询

等于连接
不等连接
外连接(left join, right join，full outer join)
自连接 两张表名相同的字段进行连接

分组计算函数和group by子句

求和(SUM)
求平均值(AVG)
计数(COUNT)
求标准差(STDDEV)
求方差(VARIANCE)
求最大值(MAX)
求最小值(MIN)

使用group by子句进行分组:

1. 可以是按照某一个字段分组，也可以按照多个字段的组合进行分组
   例：group by dep_id,job_id

2. select语句中同时选择分组计算函数表达式和其他独立字段时，其他字段都必须出现在group by子句中，否则不合法
   例：select department_id,count(last_name) from employee
   group by department_id

3. 不能再where条件中使用分组计算哈数表达式，应该使用having
   例：select department_id,avg(salary)from employee
   group by department_id
   having avg(salary)>8000

子查询

注意：
1. 单行比较必须对用单行子查询(但会一个结果值的查询); 比如= ，>

2. 多行比较必须对应多行子查询(返回一个数据集合的查询)：
	比如 in，>any,>all

DML语句

简单的来说，就是增删改语句

*** INSERT 语句：

1. 写出表名+列名 对于不允许为null的必须写出，允许为null的，可不写

2. 仅写出表名 在value中必须对应每列的值,可为null的字段也必须显式给出null

3. 从另一个表中copy一行
   例： INSERT INTO sales_reps(id,NAME,salary,commission_pct)
   SELECT employee_id,last_name,salary,commission_pct
   FROM employees
   WHERE job_id LIKE ‘%REP%’;

4. 子查询作为插入的目标
   例：INSERT INTO
   (SELECT employee_id,last_name,email,hire_date,job_id,salary
   FROM employees
   WHERE department_id = 50 WITH CHECK OPTION)
   VALUES
   (99998,‘Smith’,‘JSMITH’,to_date(‘1999-06-07’, ‘YYYY-MM-DD’)
   ,‘ST_CLERK’,50);

   ***UPDATE 语句：

   1. 更新符合条件的行中某些列为具体的值
   2. 使用子查询的结果作为更新后的值（不能是个集合，一定是一个值）
   3. 存在某些约束时可能会更新失败

   ***DELETE 语句：

   1. 删除某些符合条件的记录
   2. 删除某张表的所有记录
   3. TRUNCATE语句慎用，不可回滚
   4. 存在某些约束时可能会删除失败

事务控制

Commit，Rollback是显式的事务；

隐式的事务提交或回滚：

隐式的执行Commit:

1.数据定义语句被执行的时候，比如新建一张表
2.数据控制语句被执行的时候，比如赋权grant
3.正常退出PLSQL DEVELOPER

隐式的执行Rollback:
	1.非正常退出PLSQL DEVELOPER,或者发生系统错误

Commit或者Rollback前后数据的状态

1. 数据被更改，但是没有commit，被更改记录处于锁定状态，其他用户无法进行更改
   2. 数据被更改，但是没有commit，只能被当前session用户看到变更
   3. 数据被更改，commit后，被更改记录自动解锁，其他用户可以进行更改
   4. 数据被更改，commit后。其他session用户访问数据，看到的是已经变化的数据

   同理可知Rollback前后数据的状态以及锁的变化

   读一致性：
   1.任何时候，确保提供数据的一致性视图
   2.一个用户对数据的更改不会影响到另一个用户对数据的更改
   3.可确保在同一时刻，读不等写，写不等读

锁

概念：防止并发事务对相同资源进行更改的时候，互相破坏。

可以查询锁的信息：
		SELECT a.*,c.type,c.lmode
	  	FROM v$locked_object a
	      ,all_objects     b
	      ,v$lock          c
	 	WHERE a.object_id = b.object_id
	   AND a.session_id = c.sid
	   AND b.object_name = '表名';	

某用户对数据进行更改未提交之前，Oracle会隐式的进行加锁
用户也可以显式的加锁，如：
select..from tableA where...For UPDATE NoWait