常用sql语句

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;