常用的 SQL 语句

结构化查询语言(Structured Query Language)简称SQL，是对数据库进行增、删、查、改等操作的语言。

SQL 语言分类

DML

数据库中，称增删改查，为DML语句。(Data Manipulation Language 数据操纵语言)，如：

1. 增 insert
2. 删 delete
3. 改 update
4. 查 select

DDL

数据定义语言(Data Definition Language)。如：

1. create table(创建表)
2. alter table(修改表)
3. truncate table(清空表)
4. drop table(删除表)
5. create view(视图)
6. create index(索引)
7. create sequence(序列)
8. create synonym(同义词)

DCL

数据控制语言（Data Control Language）。如：

1. commit(提交)
2. rollback(回滚)

基本 select 语句

其语法格式为：

SELECT    *|{[DISTINCT] column |expression [alias],...}

FROM    table  [TableAlias1 ] [,table2 [TableAlias2 ],…]

       [WHERE expression]

       [GROUP BY column1[,column2,…]]

       [HAVING expression]

       [ORDER BY column1[,column2,…]]

语法描述说明：

花括号 { } 括起来的部分为必填部分；

中括号 [ ] 括起来的部分为可选部分；

多种形式的语法用竖线 | 来表示并列单选，或者的意思

别名 as

 SQL> select empno as “员工号”, ename “姓名”, sal 月薪, sal * 12 年薪
			 	   from  emp;  

 关键字as写与不写没有区别； “ ” 有与没有取决于别名中是否有空格或者关键字

“DISTINCT” 关键字。去除结果集重复的行

select distinct job from emp;

DISTINCT的作用范围：    distinct作用于后面所有列

算数运算 + - * /

• 乘除的优先级高于加减
• 优先级相同时，按照从左至右运算
• 可以使用括号改变优先级

查询: 员工号、姓名、月薪、年薪、奖金、年收入。

select deptno, ename, sal, sal * 12, comm, comm+sal*12 from emp;

NULL值问题

1. 包含NULL值的表达式都为空。

2. NULL != NULL

任何算数运算碰到NULL值都变成NULL，任何逻辑运算碰到NULL值都变成假

控制不是空格或者 0.

where 条件过滤

查询10号部门的员工信息：

select * from emp where deptno=10

比运算较

普通比较运算符：                                                                                                        

=    等于(不是==)              >    大于

>= 大于等于                     <    小于

<= 小于等于                     <>  不等于(也可以是!=)

BETWEEN…AND:         介于两值之间

例如：查询工资在1000-2000之间的员工

SQL> select * from emp where sal >=1000 and sal<2000;

SQL> select * from emp where sal between 1000 and 2000;

IN：在集合中。(not in 不在集合中)

SQL> select * from emp where deptno in (10, 20);

like：模糊查询

1. ‘%’匹配任意多个字符。
2. ‘_’匹配一个字符。

查询名字是4个字的员工

select * from emp where ename like ‘____’;

逻辑运算

AND      逻辑与   

OR         逻辑或   

NOT      逻辑非   

如果…..where 表达式1 and 表达式2;

       …..where 表达式2 and 表达式1;

这两句SQL语句效果是不一样的，因为SQL在解析where的时候，是从右至左解析的。

所以： and时应该将易假的值放在右侧

order by 排序

1. 使用 ORDER BY 子句排序
   • ASC（ascend）: 升序。默认采用升序方式。
   • DESC（descend）: 降序
2. ORDER BY 子句在SELECT语句的结尾。

查询员工信息，按月薪排序

select  ename, sal, sal*12, from emp order by 2 desc;

上述代码中 order by 后面的序号为：默认：ename→1， sal→2，sal*12→3

注：order by后有多列时，列名之间用逗号隔分，order by会同时作用于多列。

排序的规则

• 可以按照select语句中的列名排序
• 可以按照别名列名排序
• 可以按照select语句中的列名的顺序值排序
• 如果要按照多列进行排序，则规则是先按照第一列排序，如果相同，则按照第二列排序；以此类推

单行函数

单行函数:只对一行数据进行计算，产生一个结果。函数可以没有参数，但必须要有返回值。

• 操作数据对象 hello world
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值

字符函数

操作对象是字符串。大致可分为两大类：一类是大小写控制函数，主要有lower、upper、initcap：

select lower('HeLlo, WORld') 转小写, upper('HellO, woRld') 转大写, initcap('hello, world') 首字母大写	from dual

另一类是字符控制函数：有CONCAT、SUBSTR、LENGTH/LENGTHB、INSTR、LPAD | RPAD、TRIM、REPLACE

concat(a,b)：连接字符串a和b，只能连接两个

select concat('hello', ' world') as "你好" from dual;

select concat('aa', concat('bb', 'cc')) from dual;

注意：SQL双引号 “ ” 表示别名，使用 ‘ ’ 来表示字符串

另一种连接字符串的方法，使用||，可以做到多个字符串连接

select 'hello' || ' world' || ' Oracle' as "你好" from dual

substr(a, b)：从a中，第b位开始取(计数从1开始)，取到结尾

substr(a, b, c)：从a中，第b位开始，向右取c位

length：字符数， lengthb：字节数

instr：在母串中查找子串， 找到返回下标，计数从1开始。没有返回0

lpad：左填充，参1：待填充的字符串，参2：填充后字符串的总长度(字节)， 参3：填充什么  rpad：右填充。

trim：去掉前后指定的字符

replace：替换

数值函数

1. ROUND: 四舍五入

ROUND(45.926, 2)               45.93

1. TRUNC:    截断

TRUNC(45.926, 2)                45.92

1. MOD: 求余

MOD(1600, 300)                  100

转换函数

在不同的数据类型之间完成转换。将“303” 转换为 303。有隐式转换和显式转换之分。

隐式转换：Oracle 数据库有对特定类型之间自动完成转化的功能

select * from emp where hiredate = '17-NOV-81' 

显示转换：借助to_char(数据，格式)、to_number、to_date函数来完成转换

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

条件表达式

CASE expr  WHEN comparison_expr1 THEN return_expr1

            [WHEN comparison_expr2 THEN return_expr2

            WHEN comparison_exprn THEN return_exprn

            ELSE else_expr]

END

select ename, job, sal 涨前薪水, case job when 'PRESIDENT' then sal+1000 
											    when 'MANAGER' then sal+800
                                                else sal + 400
										        end 涨后薪水 
		  from emp  
 

分组函数（多行函数）GROUP BY 、HAVING

分组函数作用于一组数据，并对一组数据返回一个值

求员工的工资总额：

select sum(sal) from emp;  

sum() 对指定列的各行求和。

select count(*) from emp;

 count() 统计指定列的非空行数

count函数：求个数，如果要求不重复的个数，使用distinct

SQL> select count(distinct job) from emp;

分组数据 GROUP BY

按照group by 后给定的表达式，将from后面的table进行分组。针对每一组，使用组函数。

查询“部门”的平均工资：

select  deptno,  avg(sal)  from emp  group by deptno;

注意: 在SELECT 列表中所有没有包含在组函数中的列，都必须在group by的后面出现。

Having 过滤分组

使用 HAVING 过滤分组:

1.    行已经被分组。

2.    使用了组函数。

3.    满足HAVING 子句中条件的分组将被显示。

查询平均薪水大于2000的部门 ：

select deptno, avg(sal)  from emp  group by deptno  having avg(sal)>2000;

注意：having后面不能使用别名

 where 和 having 的比较

1. 不能在 WHERE 子句中使用组函数（注意）。
2. 可以在 HAVING 子句中使用组函数。

从功能上讲，where和having都是将满足条件的结果进行过滤，但是应该尽量采用 where，如果有分组的话，where 是先过滤再分组，而 having 是先分组再过滤。、

子查询

子查询语法很简单，就是select 语句的嵌套使用。

子查询语法格式：

SELECT select_list
    FROM    table
    WHERE expr operator
                    (SELECT      select_list
                       FROM         table);

查询工资比 sct 高的员工信息

select * 
	  from emp  
	  where sal  >  (select sal  
					 from emp  
					 where ename='sct') 

定义子查询 需要注意的问题

•  小括号( )
•  主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可
• 可以在主查询的 where、select、having、from 后都可以放置子查询
• 不可以在主查询的 group by 后面放置子查询 (SQL语句的语法规范)
• 强调：在from后面放置的子查询(***)   from后面放置是一个集合(表、查询结果)
• 一般先执行子查询(内查询)，再执行主查询(外查询)；但是相关子查询除外
• 一般不在子查询中使用order by, 但在Top-N分析问题中，必须使用order by 
• 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
• 子查询中的null值：判断一个值等于、不等于空，不能使用=和!=号，而应该使用is 和 not。

插入数据 insert

使用 INSERT 语句向表中插入数据。其语法为：

INSERT INTO    table [(column [, column...])]

VALUES               (value [, value...]);

如果：values后面的值，涵盖了表中的所有列，那么table的列名可以省略不写。

如果：插入的时候没有插入所有的列，就必须显式的写出这些列的名字。

没有写出的列自动填NULL， 这种方式称之为“隐式插入空值”。

insert into emp values (1005, 'Bone', 'Raphealy', 7829, to_date('17-12月-82', 'DD-MON-RR'), 					NULL, 300, 20); 

批量插入

子查询可以出现在DML的任何语句中，不只是查询套查询，因此我们可以使用子查询来批量将一个表中的数据插入到另一个表中。

一次性将emp表中所有10号部门的员工，放到新表emp10中来

insert into emp10 select * from emp where deptno=10

注意没有values关键字了。且列名必须一一对应

更新数据update

SQL>  update emp10 set sal=4000, comm=300 where ename = 'CLARK';

对于更新操作来说，一般会有一个“where”条件，如果没有这限制条件，更新的就是整张表。

删除数据delete

delete from emp10 where empno=7782;（或ename= 'KING'）

注：“from”关键字在Oracle中可以省略不写，但MySQL中不可以

表操作

创建表 

创建一张表必须具备：1. Create Table的权限 2. 存储空间。

create table test1 (tid number, tname varchar2(20), hiredate date default sysdate) 

default的作用是，当向表中插入数据的时候，没有指定时间的时候，使用默认值sysdate

修改表

ALTER TABLE

追加一列：

alter table test1 add image blob 

修改一列：

alter table test1 modify tname varchar2(40)

 删除一列 和 重命名一列

alter table test1 drop column image

alter table test1 rename column tname to username

清空表 

使用 truncate table 表名 的方式清空一张表

delete 和 truncate的区别：

delete from 表名 （没有where 条件） 也可以清空一张表

1. delete 逐条删除表“内容”，truncate 先摧毁表再重建。
2. delete 是DML语句，truncate 是DDL语句。DML语句可以闪回(flashback)和回滚rollback，DDL语句不可以闪回和回滚。
3. 由于delete是逐条操作数据，所以delete会产生碎片，truncate不会产生碎片。
4. delete不会释放空间，truncate 会释放空间。用delete删除一张10M的表，空间不会释放。而truncate会。所以当确定表不再使用，应使用truncate。