sql语句分类
DML:数据操纵语言 (增删改查
DDL:数据定义语言 (VREATE、ALTER、DROP、RENAME 、TRUNCATE 表
DCL:数据控制语言 权限控制
简单查询
-- 查询表中所有数据
select * from emp;
--条件查询,查询编号是10的员工
select empno, ename, job from emp where deptno = 10;
--distinct 查询结果去重
select distinct deptno from emp;
--在查询的过程中可以给列添加别名,同时也可以给表添加别名
select e.empno 雇员编号,e.ename 雇员名称,e.job 雇员工作 from emp e where e.deptno=10;
条件比较
- =,!=,<>,<,>,<=,>=,any,some,all
- is null,is not null
- between …… and ……
- in(list),not in(list)
- exists(sub-query)
- 模糊查询 like _ ,%,escape ‘\‘ _%
- 转义 escape ‘\’
-- =
select * from emp where deptno = 20;
-- !=
select * from emp where deptno != 20;
-- <> 不等于
select * from emp where deptno <> 20;
-- <
select * from emp where deptno < 20;
-- >
select * from emp where deptno > 20;
-- <=
select * from emp where deptno <= 20;
-- >=
select * from emp where deptno >= 20;
-- any 取其中的任意一个
select sal from emp where sal > any(1000,1500);
-- some和any一样
select sal from emp where sal > some(1000,1500,3000);
-- all,大于所有的值
select sal from emp where sal > all(1000,1500,3000);
-- is null,is not null . null是一个特殊的含义,与谁比较都是false
select * from emp where comm is null;
select * from emp where comm is not null;
--between x and y,在x到y之间,等价于(sal >= 1500 and sal <= 3000)
select * from emp where sal between 1500 and 3000;
--in(list),not in(list),对某些值进行等值运算时使用
select * from emp where deptno in(10,20);
select * from emp where deptno not in(10,20);
--and和or可能出现在同一个sql语句中,此时需要注意and和or的优先级
--and 的优先级要高于or,所以一定要将or的相关操作用()括起来,提高优先级
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno != 10 and deptno !=20;
/*exists(sub-query),当exists中的子查询语句能查到对应结果的时候,意味着条件满足
相当于双层for循环*/
--现在要查询部门编号为10和20的员工,要求使用exists实现
select * from emp e where exists (
select deptno from dept d where (d.deptno = 10 or d.deptno = 20) and e.deptno = d.deptno);
/*
模糊查询:
在like的语句中,需要使用占位符或者通配符
_:某个字符或者数字仅出现一次
%:任意字符出现任意次数
escape:使用转义字符,可以自己规定转义字符
使用like的时候要慎重,因为like的效率比较低,使用like可以参考使用索引,但是要求不能以%开头
涉及到大文本的检索的时候,可以使用某些框架 luence,solr,elastic search
*/
--查询名字以S开头的用户
select * from emp where ename like('S%');
--查询名字以S开头且倒数第三个字符是o的用户
select * from emp where ename like('S%O__');
--查询名字中带%的用户
select * from emp where ename like('%\%%') escape('\');
排序
order by asc/desc
排序是按照自然顺序进行排序的,如果是数值,那么按照从大到小,如果是字符串,那么按照字典序排序,在进行排序的时候可以指定多个字段,而且多个字段可以使用不同的排序方式。
每次在执行order by的时候相当于是做了全排序,思考全排序的效率,会比较耗费系统的资源,因此选择在业务不太繁忙的时候进行。
/*
order by进行排序操作,默认情况下完成的是升序的操作,
asc:是默认的排序方式,表示升序;desc:降序的排序方式
*/
select * from emp order by sal;
select * from emp order by sal desc;
select * from emp order by ename;
select * from emp order by ename,sal;
关联查询
在进行连接的时候,可以使用等值连接,可以使用非等值连接。
等值连接:两个表中包含相同的列名
非等值连接:两个表中没有相同的列名,但是某一个列在另一张表的列的范围之中
外连接:没有关联到的数据不会显示,此时需要外连接。左外连接(把左表的全部数据显示)和右外连接(把右表的全部数据显示)
笛卡尔积:当关联多张表,但是不指定连接条件的时候,会进行笛卡尔积
92语法
在92语法中,多张表的连接条件会方法where子句中,同时where需要对表进行条件过滤
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
select * from emp e,dept d where e.deptno = d.deptno;--等值连接
select * from emp e,dept d where e.deptno = d.deptno(+);--左外连接
select * from emp e,dept d where e.deptno(+) = d.deptno;--右外连接
99语法
CROSS JOIN | 类似于笛卡尔积 |
---|---|
NATURAL JOIN | 自然连接,自动找列等值连接 |
USING子句 | 作为连接条件,连接条件的列不归属与任何一张表 |
LEFT OUTER JOIN | 左外连接 |
RIGHT OUTER JOIN | 右外连接 |
FULL OUTER JOIN | 左外连接与右外连接的合集 |
Inner join | 两张表的连接查询,只会查询出有匹配记录的数据 |
ON子句,用来添加连接条件
--cross join 等同于92语法中的笛卡尔积
select * from emp cross join dept;
--natural join 相当于是等值连接,不需要写连接条件,会从两张表中找相同的列做连接
--当两张表中不具有相同的列名的时候,会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系
select * from emp e natural join dept d;
--on子句,可以添加任意连接条件
--添加连接条件相当于92语法中的等值连接
select * from emp e join dept d on e.deptno=d.deptno;
--相当于92语法中的非等值连接
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
--left outer join,会把左表中的全部数据正常显示,右表没有对应的数据直接显示空即可
select * from emp e left outer join dept d on e.deptno=d.deptno;
--right outer join ,会把右表中的全部数据正常显示,左表中没有对应的记录的话显示空即可
select * from emp e right outer join dept d on e.deptno=d.deptno;
select * from emp e ,dept d where e.deptno(+) = d.deptno;
--full outer join ,相当于左外连接和右外连接的合集
select * from emp e full outer join dept d on e.deptno = d.deptno;
--inner join,两张表的连接查询,只会查询出有匹配记录的数据
select * from emp e inner join dept d on e.deptno=d.deptno;
select * from emp e join dept d on e.deptno=d.deptno;
--using,除了可以使用on表示连接条件之外,也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表
select deptno from emp e join dept d using(deptno);
select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;
子查询
嵌套再其他sql语句中的完整sql语句,可以称之为子查询。分为单行子查询、 多行子查询
--单行子查询
--有哪些人的薪水是在整个雇员的平均薪水之上的
select * from emp e where e.sal > (select avg(e.sal) from emp e);
--多行子查询
使用 in,not in
删除
--删除满足条件的数据
delete from emp where empno = 5;
--把整张表的数据全部清空
delete from emp2;
--truncate ,跟delete有所不同,delete在进行删除的时候经过事务,而truncate不经过事务,一旦删除就是永久删除,不具备回滚的操作
--效率比较高,但是容易发生误操作,所以不建议使用
truncate table emp2
修改
--更新单列
update emp set ename = 'heihei' where ename = 'hehe';
--更新多个列的值
update emp set job='teacher',mgr=7902 where empno = 15;
插入
--最基本的插入方式
insert into tablename values(val1,val2,....)-- 如果表名之后没有列,那么只能将所有的列都插入
insert into tablename(col1,col2,...) values(val1,val2,...) --可以指定向哪些列中插入数据
--复制表同时复制表数据,不会复制约束
create table emp2 as select * from emp;