|
背景:
虽然阿里还有其它大厂都在提倡用NoSQL数据库，但是绝大部分公司还是抵挡不住SQL关系型数据库便宜到不要钱的诱惑，加上用Redis作为缓冲层抵消热点压力，ElasticSearch作为搜索引擎数据库，能够很好的抵消掉关系型数据库查询慢的弊端，所以SQL语言在未来很长的一段时间内都是我们要钻研的对象。
?? ?分类：
?? ??? ? DQL: ?select ... from ... where ... group by ...order by ...?
?? ??? ? DML: ?insert , delete , update ,select ... for update ...?
?? ??? ? DDL: ?create , alter , drop , ?truncate table ,
?? ??? ? TCL: ?savepoint, commit, rollback
?? ??? ? DCL: ?grant , revoke?
DDL数据定义语言
?? ?创建表 ?create?
?? ? ? ?????????CREATE table [schema.]tbname
?? ??? ? ? ?(column datatype [DEFAULT expr][, ...]);
?? ??? ?CREATE table tbname
?? ??? ? ??? ? ?[(column, column...)]
?? ??? ?AS subquery;
?? ?修改表?? ?alter?
?? ??? ?ALTER table tbname
?? ??? ?ADD?? ??? ? ? (column datatype [DEFAULT expr]
?? ??? ??? ??? ? ? [, column datatype]...);
?? ??? ?ALTER table tbname
?? ??? ?MODIFY?? ? ? (column datatype [DEFAULT expr]
?? ??? ??? ??? ? ? [, column datatype]...);
?? ??? ?ALTER table tbname
?? ??? ?DROP COLUMN ?column_name;
?? ??? ?ALTER table tbname_name RENAME COLUMM old_column_name?
?? ??? ?TO new_column_name
?? ?删除表 ?drop ??
?? ??? ?DROP table tbname_name [purge];
?? ??? ?TRUNCATE TABBLE tbname_name; --清空表中的数据 truncate tbname?
?? ?约束 constraint?
?? ??? ??? ?非空约束 ?not null
?? ??? ??? ?主键约束 ?primary key?
?? ??? ??? ?唯一约束 ?unique
?? ??? ??? ?检查约束 ?check
?? ??? ??? ?外键约束 ?foreign key ... references ...?
? ? ? ? 1）建表的时候添加索引,即在后面加约束类型，自定义的约束名，约束的字段
? ? ? ? ? ? ? ? 如以下例子，由于语言简单，所以不做过多解释了
? ? ? ? ? ? ? ? drop table
? ? ? ? ? ? ? ? if
? ? ? ? ? ? ? ? ? ? exists 'department'
? ? ? ? ? ? ? ? creatable 'department' (
????????????????id int(10) not null auto_increment comment '主键',
? ? ? ? ? ? ? ? 'code' varchar2(200) default null comment '编号'
? ? ? ? ? ? ? ? 'name' varchar2(200) default null comment '名称',
? ? ? ? ? ? ? ? primary key ('id'),
? ? ? ? ? ? ? ? unique key 'index_code' ('code'),
? ? ? ? ? ? ? ? index 'index_name' ('name')?)
? ? ? ? ? ? ? ? engine = innodb default charset=utf8 comment '部门表'
? ? ? ? ?2)建表之后添加约束
?????????????????alter table 表名 add constraint 约束名 约束内容（约束关键字 字段名）;
????????
DML数据操作语言
?? ?插入数据
?? ?INSERT INTO?? ?tbname [(column [, column...])]
?? ?VALUES?? ??? ?(value [, value...]);
?? ?修改数据
?? ?UPDATE?? ??? ?tbname
?? ?SET?? ??? ?column = value [, column = value, ...]
?? ?[WHERE ?? ??? ?condition];
?? ?删除数据
?? ?DELETE FROM?? ? ?tbname
?? ?[WHERE?? ? ?condition];
DQL数据查询语言
?? ?1、查询语句
?? ??? ?SELECT?? ?*|{[DISTINCT] column|expression [alias],...} FROM?? ?tbname;
?? ? ? ?select * from emp;
?? ??? ?select empno,ename,sal from emp; ?
?? ??? ?* ?? ??? ??? ??? ??? ?所有列
?? ??? ?column?? ?列名?? ??? ??? ?指定列,多列用,分隔
?? ??? ?expression 表达式 ? ?? ?常量 变量 函数 ?， 支持算术运算
?? ??? ?alias ?别名?? ??? ??? ?给列设定的其他名字
?? ?2、基础查询与筛选过滤 ? ?where
?? ??? ??? ?对结果进行过滤 ?where 过滤条件
?? ??? ??? ?表达式： ?
?? ??? ??? ??? ?算术运算: + - * /
?? ??? ??? ??? ?关系运算: ?> ?< ?= ?!= ?>= <= <> ?^= ?
?? ??? ??? ??? ?逻辑运算: ?and ?or ?not ??? ??? ??? ??? ??
?? ?3、排序查询 order by ?字段
?? ??? ??? ??? ?升序 asc ? 默认
?? ??? ??? ??? ?降序 desc?
?? ?4.高级查询
?? ??? ??? ??? ??? ?空值判断 ??? ??? ??? ?is null?
?? ??? ??? ??? ??? ?别名 ? ? ?? ??? ??? ?as "别名"
?? ??? ??? ??? ??? ?字符串连接操作符 ??? ?... || ...
?? ??? ??? ??? ??? ?去重 ? ? ?? ??? ??? ?distinct?
?? ??? ??? ??? ??? ?取值区间： ? ??? ??? ?between...and... ?
?? ??? ? ? ? ? ? ? ?是否在集合内： ??? ??? ?in ?(set)
?? ??? ? ? ? ? ? ? ?模糊查询： ??? ??? ??? ?like ? ?通配符 _ %?? ?
?? ?5、分组查询 group by ?
?? ??? ??? ?group by 分组字段?
?? ??? ??? ?通常与聚合函数连用?
?? ??? ??? ??? ?求和 sum() 求平均 avg() 求最大最小 max(), min() ?数量统计 count()
?? ? ? ? select deptno, avg(sal) from emp group by deptno having avg(sal)>2000;
?? ? ? ??? ?在分组的基础上过滤 ?having ?
?? ? ? ??? ?在分组前过滤还用 ? ?where ? -- where后不能使用聚合函数做为条件，只可放在having后
?? ?6、连接查询（多表查询）?
?? ??? ?1）交叉连接 cross join ? ?--笛卡尔集?
?? ??? ??? ?select ename, job, sal ,dname from emp, dept;
?? ??? ??? ?select ename, job, sal ,dname from emp cross join dept ;?
?? ??? ?2）内连接 ? [inner] join ... on ... ? ?--inner 可省略
?? ??? ??? ??? ?仅提取多表中满足条件的记录并连接起来。?
?? ??? ??? ??? ?或：多表连接中只返回满足连接条件的数据行。
?? ??? ??? ?select ename, job, sal, dname from emp, dept where emp.deptno = dept.deptno; --传统多表查询写法
?? ??? ??? ?select ename, job, sal, dname from emp inner join dept on emp.deptno=dept.deptno; --标准连接查询写法
?? ??? ??? ?
?? ??? ??? ?优化之后：
?? ??? ??? ?select ename, job, sal , dname from emp e join dept d on e.deptno=d.deptno;
?? ??? ??? ?最标准的写法：
?? ??? ??? ??? ?等值连接
?? ??? ??? ?select e.ename, e.job, e.sal , d.dname, e.deptno ?from emp e join dept d on e.deptno=d.deptno;
?? ??? ??? ??? ?非等值连接
?? ??? ??? ?select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
?? ??? ?3）外连接 ?outer join ... on ... ? --outer 可省略
?? ??? ??? ??? ?不仅提取多表中满足条件的记录连接起来，某表不满足的条件的记录也会提取出来。
?? ??? ??? ??? ?或： 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。
?? ??? ??? ?左连接 ?left [outer] join ?
?? ??? ??? ??? ?以左表为基准，提取左表所有记录，连接右表满足条件的记录。
?? ??? ??? ?右连接 ?right [outer] join?
?? ??? ??? ??? ?以右表为基准，提取左表所有记录，连接右表满足条件的记录。
?? ??? ??? ?右连接
?? ??? ??? ?select dname, ename, job, sal from emp right outer join dept on emp.deptno=dept.deptno;?
?? ??? ??? ?select d.dname, e.ename, e.job, e.sal from emp e right join dept d on e.deptno=d.deptno; ?--标准写法
?? ??? ??? ?select d.dname, e.ename, e.job, e.sal from emp e , dept d where e.deptno(+)=d.deptno; ?--oracle 私有的写法 多表
?? ??? ??? ?左连接
?? ??? ??? ?select d.dname, e.ename, e.job, e.sal from emp e left join dept d on e.deptno=d.deptno;
?? ??? ??? ?select d.dname, e.ename, e.job, e.sal from emp e , dept d where e.deptno=d.deptno(+); ?--oracle 私有的写法 多表
?? ??? ??? ??? ?-- 表 [as] 别名
?? ??? ?4） 自连接
?? ??? ??? ??? ?表跟自身表的连接
?? ??? ??? ??? ?检索所有的员工的姓名，以及他经理的姓名？
?? ??? ??? ??? ?select e1.ename, e2.ename ?from emp e1, emp e2 where e1.mgr=e2.empno; ? --传统多表查询写法
?? ??? ??? ??? ?select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno; ? --标准连接查询写法
?? ??? ??? ??? ?select e.ename , m.ename from emp e left join emp m on e.mgr=m.empno;
?? ??? ?5）完全连接（满外连接）
?? ??? ??? ?两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。
?? ??? ??? ??? ?select e.*, d.* from emp e [outer] full join dept d ?on e.deptno=d.deptno; ?
?? ??? ??? ?
?? ??? ? 三个表的例子：
?? ??? ??? ??? ?select * from ?t1 join t2 on t1.sn=t2.sn [ left | right ] join t3 on t2.cn=t3.cn ?;
?? ??? ??? ??? ?select * from t1, t2, t3 where t1.sn=t2.sn and t2.cn=t3.cn ;
?? ??? ??? ?select ename, job, sal, grade, dname from emp e left join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal
?? ?7. 集合查询
?? ??? ??? ?select 运行结果，一定是一个结果集。 ?集合操作的前提是，两个集合的元素是同一性质的。
?? ??? ?intersect ? ?交集 ? 集合共有的记录
?? ??? ?union [all] ?并集 ? 集合所有的记录 union all 包含重复的记录， union 不包含重复的记录
?? ??? ?minus ? ? ? ?补集 ? 集合相差的记录
?? ??? ??? ?select * from emp where sal<1300
?? ??? ??? ?union [all] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?--并集
?? ??? ??? ?select * from emp where job='SALESMAN';
?? ??? ??? ?select * from emp where sal<1300
?? ??? ??? ?intersect?? ??? ??? ??? ??? ??? ??? ? ? ? ? --交集
?? ??? ??? ?select * from emp where job='SALESMAN';
?? ??? ??? ?select * from emp where sal<1300
?? ??? ??? ?minus?? ??? ??? ??? ??? ??? ??? ??? ??? ? ? --补集
?? ??? ??? ?select * from emp where job='SALESMAN';
?? ?8.子查询：?
?? ? ? ? ? 在 select , update ,delete, insert ?等语句内部嵌套的 select 的语句， 子查询在主查询之前执行完成
?? ? ? ? ??
?? ? ? ? ? 位置分两种：
?? ? ? ? ? ?? ??? ?where 后： ?子查询做为外部查询条件从句的一部分
?? ? ? ? ? ?? ??? ?from ?后： ?子查询左外外部查询的临时表?
?? ? ? ?1)单行子查询 ?子查询的结果，最多返回一条记录(0 ,1) ??
?? ? ? ??? ??? ?关系运算 ?> < = !=?
?? ??? ? ? ?select * from emp where sal != (select max(sal) from emp ) and ?sal!= (select min(sal) from emp) ;
?? ??? ? ? ?select * from emp where deptno != (select deptno from dept where dname='SALES');
?? ? ? ?2)多行子查询 ?子查询的结果，有可能有多个（0， 1， n)
?? ??? ??? ??? ??? ? ? in , not in , any ,all , some?
?? ??? ??? ?select * from emp where deptno in (select deptno from dept where dname!='SALES');
?? ??? ??? ?select * from emp where ?sal > any (select sal from emp where deptno=10 )?? ?and ?deptno!=10;
?? ??? ??? ?select * from emp where ?sal > all (select sal from emp where deptno=10 )?? ?and ?deptno!=10;
?? ??? ?3)相关子查询 ?内部的查询条件跟外部查询的条件相关
?? ??? ??? ?主查询循环查询的每一行都执行一次子查询
?? ??? ??? ?select ename, job, sal from emp o where sal > (select avg(sal) from emp where job = o.job ) ;
?? ??? ??? ?--查询薪水大于部门平均薪水的员工信息
?? ??? ??? ? ? ?select * from emp outer where sal > (select avg(sal) from emp where deptno=outer.deptno ) ? ?--相关子查询?
?? ??? ?4)存在查询 ? exists ， ?not exists ?
?? ??? ??? ??? ??? ?exists ?子查询返回的是布尔值?
?? ??? ??? ??? ??? ??? ??? ?子查询有记录返回，结果为 true?
?? ??? ??? ??? ??? ??? ??? ?子查询无记录返回，结果为 false?
?? ??? ??? ?select dname from dept d ?where exists (select * from emp where sal>3000 and deptno=d.deptno );
?? ??? ??? ?
?? ??? ??? ?EXISTS 存在子查询?
?? ??? ??? ?exists对外表用loop循环逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句
?? ??? ??? ?能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop循环到的这条记
?? ??? ??? ?录；反之，如果exists里的条件语句不能返回记录行，则当前loop循环到的这条记录被丢弃，exists的
?? ??? ??? ?条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false
?? ??? ??? ?"主查询循环每条记录，每次查询都会去判断exists子查询条件,如果为true就返回主查询的这条记录
?? ??? ??? ?, 如果为false就丢弃主查询对应的这条记录。"
?? ??? ??? ?
?? ??? ??? ?--查询部门名称中包含字符S的员工信息？
?? ??? ??? ??? ?select * from emp e where exists (select * from dept where dname like '%S%' and deptno = e.deptno ?); ? -- 存在子查询 相关子查询
?? ??? ??? ?
?? ??? ??? ?--查询各部门最高薪水的员工信息?
?? ??? ??? ??? ?--多列匹配条件?
?? ??? ??? ??? ?select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO); ? --多列子查询
?? ??? ?五、分页子查询 ? select子查询的结果做为外部查询的临时表 --伪列
?? ??? ??? ?select * from (select rownum r, t.ename, t.job, t.sal from (select ename, job, sal from emp order by sal desc) t) o
?? ??? ??? ??? ?where o.r>=5 and o.r<=10;
?? ??? ??? ?select * from (select rownum r, ename, job, sal from emp) t where ?t.r>=5 and t.r<=10;
?? ?9.内置函数
?? ??? ??? ?1)单行函数
?? ??? ??? ??? ?常用的如：round() power() mod() length() ASCII() lower() upper() to_char() to_date() concat() substr() replace()?
?? ??? ??? ??? ?months_between() next_day() ?last_day() ...
?? ??? ??? ??? ?
?? ??? ??? ?2)聚合函数
?? ??? ??? ??? ?avg() sum() count() min() max()
?? ??? ??? ?3)通用函数
?? ??? ??? ??? ? ?nvl(expr1, expr2) ? ? ? ? ? ? -- 如果expr1为空，返回expr2,否则返回expr1
?? ??? ??? ? ? ? ?nvl2(expr1, expr2, expr3) ? ? ?-- 如果expr1非空，返回expr2,否则返回expr3?
?? ? ? ? ??? ?4)条件表达式函数：
?? ? ? ? ??? ? ? ?case [expr] when ... then ...
?? ? ? ? ? ? ? ? ??? ??? ??? ?when ... then ...
?? ? ? ? ? ? ? ? ??? ??? ?else ...?
?? ? ? ??? ??? ?end?
?? ??? ??? ??? ?select ename , ?sal, case deptno when 10 then 'dept10'
?? ??? ??? ??? ? ? ? ? when 20 then 'dept20'
?? ??? ??? ??? ? ? ? ? when 30 then 'dept30'
?? ??? ??? ??? ? ? ? ? ?else 'other' end department
?? ??? ??? ??? ??? ??? ??? ??? ?from emp?
?? ??? ??? ??? ?select ename,sal,case when deptno=10 then 'dept10'
?? ????????????????????????? ??? ??? ??? ?when deptno=20 then 'dept20'
?? ????????????????????????? ??? ??? ??? ?when deptno=30 then 'dept30'
?? ???????????????????????????????else 'other' end department
?? ??? ??? ??? ?from emp;?
TCL事务控制语句
????????commit提交
????????rollback回滚
????????savepoint保存点
? DCL权限控制语句
grant授予权限，如连接的权限，查询的权限，写数据的权限
revoke是去除相应权限的作用
?
|