SQL 的总结

一．查询部分（s）

1.1 select关键字

作用：检索“列”

注意：1.select后面的列可以起别名（查询的显示结果）

1. 列名后面一个空格后添加别名（别名中不许有“空格”）

2. 列名后面一个空格后使用双引号添加别名

3. 列名后面一个空格后使用as关键字，在as后面添加别名

2.distinct用于对显示结果的去重

1. distinct必须放在select后面

2. 如果查询有多列，必须满足多列值都相同时，方可去重。

from关键字

作用：检索“表”

注意：检索的表后可以添加别名（别名不需要被双引号引起）

1.2 where关键字

作用：过滤“行”记录（record）

用法：

1.＝，！＝,<>，<,>,<=,>=,any,some,all

**Domo: *selectfrom emp where sal>1500;

2. is null,is not null

Domo:：select *from emp where ename is not null;

3. between x and y

例子：select ename from salgrade where sal between losal and hisal;

**Domo:** -- between x and y

--查询员工薪水在2000-3000的员工信息

select * from emp where sal between 2000 and 3000

4.and 、 or 、 not

Domo:

--and、or 、not

select * from emp where sal >= 2000 and sal <=3000

5.in（list），not in（list）

Domo:

-- in（list），not in（list）

--查询职务为MANAGER和ANALYST的员工信息

select * from emp where job in ('MANAGER','ANALYST')

--查询工资不为3000和5000的员工信息

select * from emp where sal not in (3000,5000)

-- exists（sub－query）、not exists（sub-query）

select * from emp where exists(select * from dept where deptno != 50)

-- like _ ,%,escape ‘\‘ _% escape ‘\’

1.2.1 like关键字

定义：模糊查询,有两个特殊的符号"%" ,"_"

用法：

--“%”表示匹配零个或若干字符

--“_”表示匹配一个字符

Domo:

--查询：员工姓名中含有“M”的员工信息

select * from emp where ename like '%M%'

--查询：员工姓名中第二个字母是“M”的员工信息

select * from emp where ename like '_M%'

--查询：员工姓名中第三个字母是“O”的员工信息

select * from emp where ename like '__O%'

--查询:员工姓名中倒数第二个字母为“E”的员工信息

select * from emp where ename like '%E_'

--查询：员工姓名中含有“%”的员工信息

select * from emp where ename like '%%%' escape ''

--插入一条信息：insert into emp(empno,ename) values(9527,'huan%an');

1.3 order by关键字

作用：用于对查询结果进行排序

用法：

1.利用asc 、desc对排序列进行升序或降序

2.order by后可以添加多个列（逗号分隔），当一个列的值相同时，在按第二列进行排序，依次类推

Domo:

select * from emp where deptno = 20 order by sal

--1.如何决定升序还是降序？

select * from emp where deptno = 20 order by sal asc

select * from emp where deptno = 20 order by sal desc

--2.如果排序的列值相同时，如何处理？

select * from emp where deptno = 20 order by sal desc,ename desc

1.4集合

1.union 并集

2.union all 全集

3.intersect 交集

4.minus 差集

注意：

1.保证两个sql查询的列数是个数一致的

2.保证两个sql查询的列的数据类型是一致的

3.保证两个sql查询的列是相同的，否则查询的结果是无意义的

Domo:

--计算字段（列）：不在于表中，通过 + 、-、* 、/操作和列进行计算得到的列

-- 获取员工的年薪

select (ename ||'的年薪为：'|| sal*12) info from emp;

--集合：每次查询结果可以看作一个集合

select * from emp where deptno = 20;

select * from emp where sal > 2000;

-- union 并集

select * from emp where deptno = 20

union

select * from emp where sal > 2000;

-- union all 全集

select * from emp where deptno = 20

union all

select * from emp where sal > 2000;

--union和union all的区别在于：union all会重复显示两个集合相同的部分

--intersect 交集

select * from emp where deptno = 20

intersect

select * from emp where sal > 2000;

--minus 差集

--注意两条sql语句的顺序

select * from emp where deptno = 20

minus

select * from emp where sal > 2000;

--other

select * from emp where sal > 2000

minus

select * from emp where deptno = 20;

--使用集合操作注意：两条sql语句必须保证查询的列是一致的

错误1：列数不匹配

select * from emp where sal > 2000

minus

select empno from emp where deptno = 20;

错误2：数据类型不匹配

select ename from emp where sal > 2000

minus

select empno from emp where deptno = 20;

错误3：该条sql无意义

select sal from emp where sal > 2000

minus

select empno from emp where deptno = 20;

1.5函数

1.5.1****单行函数

单行函数：对单个数值进行操作，并返回一个值。

分类：

1.字符函数

1)concat(a,b) 拼接a,b两个字符串数据

2)initcap(x) 将每个单词x首字母大写

3)lower() / upper() 将字符串小写/将字符串大写

4)length() 获取字符串的长度

5)lpad(a,b,c) /rpad() 将a字符串左边填充至b长度，用c字符填充,如果c字符不填写，默认用空格填充

6)ltrim(a,b) / rtrim() 去除a字符串左边的b字符，如果b不传参，默认去除空格

7)replace(a,b,c) 将a中的b字符串替换为c

8)substr(a,b,c) 将a的字符串，从b位置开始截取，截c个长度

9)trim( a from b) 将b左右两边的a字符去除掉

2.数字函数

abs() 求取绝对值

ceil() 向上取整

floor() 向下取整

round() 四舍五入

power(x,y) x的y次幂

3.日期函数

sysdate 返回系统当前日期，注意没有括号

add_months(d1,d2) 在d1日期上，增加d2个月份

months_between(d1,d2) 返回d1和d2之间的相隔月份

last_day(d) 返回d日期所在月份最后一天的日期

next_day(d,X) 返回下一个“星期X”的日期

4.转换函数

to_char() 将数字、或日期转化为字符串

to_date() 将字符串转化为日期

to_number() 将字符串转化为数字

5.其他函数

nvl(x,y) 如果x为null，则显示为y，x和y的类型保持一致

sys_guid() 生成一个的32位随机字符串

decode() 条件取值，类同java的switch

case when then else end 条件取值，类同java的if-else if-else

Domo:

dual是一个虚表，为了满足sql句式而设置这么一个表

单行函数

--1.字符函数

--concat 拼接两个字符串

select concat(concat(ename,'的职位是'),job) from emp;

--initcap 将每个单词的首字母大写

select initcap('wang yi kun') from dual;

--lower 将字符串中的字符小写

--upper 将字符串中的字符大写

select lower('LAOWANG') from dual;

select lower(ename) from emp;

select upper('laowang') from dual;

--length 获取字符串的长度

select ename,length(ename) from emp;

--lpad

--rpad

select lpad(ename,10) from emp;

select rpad(ename,10,'*') from emp;

--注意：第二个参数要设定合理的值，否则数据就不完整！

select lpad(ename,6) from emp;

--ltrim 去除字符串左边指定字符，如果不设定第二个参数，则默认去除空格

--rtrim 去除字符串右边指定字符，如果不设定第二个参数，则默认去除空格

select ltrim('a abccba ','a') from dual;

select rtrim(' abccba aaaa','a') from dual;

--replace 替换字符串

select replace('he love you','he','i') test from dual;

--substr 截取子字符串

select substr('130888881234',3,8) test from dual;

--trim 去除字符串

select trim('a' from 'a ba a') from dual;

--2.数字函数

--abs() 求取绝对值

select abs(-5) from dual;

--ceil() 向上取整

select ceil(3.1) from dual;

--floor() 向下取整

select floor(3.9) from dual;

--round() 四舍五入

select round(4.5),round(4.4) from dual;

--power(x,y) x的y次幂

select power(2,10) from dual;

--3.日期函数

--sysdate 返回系统当前日期，没有括号

select sysdate from dual;

--add_months(d1,d2) 返回d1的基础上，添加d2个月后的日期

select hiredate,add_months(hiredate,12) from emp;

select add_months(sysdate,6) from dual;

--months_between(d1,d2) 返回d1,d2日期相隔的月份，返回的不是一个整数

select months_between(sysdate,hiredate)/12 from emp;

--last_day() 返回当前日期的月份的最后一天

select hiredate,last_day(hiredate) from emp;

--next_day() 返回下一个星期X的日期

select hiredate,next_day(hiredate,'星期一') from emp;

select sysdate,next_day(sysdate,'星期日') from dual;

--4.转化函数

--to_date()

select to_date('1999-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss') from dual;

--to_char()

select to_char(sal, '$9,999.00') from emp;

--to_number()

select to_number('876') from dual;

--5.其他函数

--nvl如果例的值为null，则转为另外结果显示

select ename,nvl(comm,0) from emp;

--sys_guid() --UUID

select sys_guid() from dual;

--decode

--类似于java中switch

select ename,sal,decode(sal,800,'屌丝',2000,'白领',3000,'小资',5000,'高富帅','一般人') from emp;

--case when then else end

--类似于java中的if - else if - else

select ename,sal,case when sal<1000 then '屌丝'

            when sal<2000 then '白领'

            when sal<3000 then '小资'

            when sal<4000 then '高富帅'

              else '王宝强' end from emp;

1.5.2组函数

组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果

avg()求平均值，只能对数字类型进行处理，不处理空字段

sum()求和，只能对数字类型进行处理

count()计数，对任何类型生效，不处理空字段

max() 求最大值，对任何类型生效

min() 求最小值，对任何类型生效

Domo:

--组函数

avg

--求20部门的平均薪水为多少？

select avg(sal) avgsal from emp where deptno = 20

sum

--求20部门的员工的总薪水

select sum(sal) sumsal from emp where deptno = 20;

count

--求20部门的员工有几个

select count(1) from emp where deptno = 20;

max

--求20部门员工工资最高的是多少

select max(sal) from emp where deptno = 20;

select max(hiredate) from emp;

min

--求20部门员工工资最低的是多少

select min(sal) from emp where deptno = 20;

--组函数：可以用在分组中的函数

select min(comm) from emp;

1.6 group by关键字

作用：对查询结果进行分组处理

用法：

1.分组之后，不能将除分组字段之外的字段放在select后面

2.group by 后面可以跟多个字段，则这多个字段值都相同时，才分为一组

3.分组之后，可以使用组函数对每个组进行数据处理

having 关键字

作用：用于对分组数据进行过滤

用法：

类似于where的用法

小知识点：

sql顺序分为两类：

1.sql的书写顺序

select from where group by having order by [asc/desc]

2.sql的执行顺序

from where group by having select order by [asc/desc]

Domo:

select * from emp;

--分组可以按多个列分组

select e.job,e.sal from emp e group by e.job,e.sal;

--group by分组的列可以不出现在select里，检索字段(select后面跟着的字段)必须出现在分组列表里。

--错误的：select e.job,e.detpno from emp e group by e.job;

--正确的：select e.deptno from emp e group by e.deptno,e.sal,e.job;

--如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。

select comm from emp e group by e.comm;

--group by 子句必须出现在where子句之后，order by 子句之前。

select e.deptno from emp e where e.sal>800 group by e.deptno having e.deptno=20 order by e.deptno desc

--where过滤行记录 having 过滤组记录

--select 后面只能跟列( group by 出现的列)或组函数

--不能在 WHERE 子句中使用组函数

--错误的：select e.deptno from emp e where avg(e.sal) group by e.deptno;

--求部门下雇员的平均工资>2000 人数

select e.deptno ,count(1),avg(e.sal) from emp e group by e.deptno having avg(e.sal) > 2000;

Sql语句执行过程：

1.读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。

2.选取满足where子句中给出的条件表达式的元组

3.按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组

4.按select子句中给出的列名或列表达式求值输出

5.Order by子句对输出的目标表进行排序。

Sql语句执行顺序：

from - where - group by - having - select - order by

1.7 联表查询

Sql****1992

sql分类

1.笛卡尔积 （表乘表）

2.等值连接 表的连接条件使用“=”

3.非等值连接 表的连接条件使用“>、>=、 <、<=、!=、any等”

4.自连接 自己连接自己

5.外连接

1.左外连接，“(+)”在等号右边

2.右外连接，“(+)”在等号左边

3.“(+)”在哪一边的列，该表就补充null

Sql****1999

sql分类

1.cross join 交叉连接 （笛卡尔积） ，不需要on关键字

2.natural join 自然连接 （找两个表中相同的列，进行等值匹配），不需要on关键字

3.inner join 内连接

1)必须有on关键字，on表示连接条件

2)inner关键字可以省略

4.outer join 外连接，outer关键字可以省略

1. left outer join

2. right outer join

3. full outer join

二．修改的部分

2.1 DML

insert关键字

作用：往表中插入一条（多条）数据

语法1：元祖值式的插入

语法1：insert into tablename(column1,column2,...,columnN) values(value1,value2,...,valueN);

语法2：查询结果式的插入

语法2：insert into tablename sub-query

delete关键字

作用：从表中删除数据

语法：delete [from] tablename [where condition]

update关键字

作用：更新表中的数据

语法：update tablename set column1=value1,column2=value2,...,columnN=valueN [where condition]

2.2 事务

事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。

目的：保证数据库的完整性

特点：事务不能嵌套

如何开启事务：

一个DML语句（insert、delete、update）的执行

如何关闭事务：

1.显示的调用commit或rollback

2.当执行DDL(Create、Alter、Drop)语句事务自动提交

3.用户正常断开连接时，事务自动提交。

4.系统崩溃或断电时事务自动回滚

2.3 序列

一、概念：

序列(sequence)：oracle专有的专有对象

二、作用：

产生一个自动递增的数列

三、创建一个序列：

create sequence seq_name

increment by 1

start with 1

四、使用序列：

序列名.nextval

序列名.currval

2.4 视图

一、定义：

视图（view）：一种不占用物理空间的虚表。

二、作用：

将一些查询复杂的SQL语句变为视图，便于查询

三、语法：

create [or replace] view v$_name

as sub-query

[with read only]

四、需要注意的点：

1.视图也可以从视图中产生

2.我们把用于产生视图的表称之为基表

3.我们对视图进行数据修改就是对基表进行数据修改，反之亦然

4.不能对多张表的数据，通过视图进行修改。

五、使用视图需要注意

1.一般来讲，只有重复出现非常多次的SQL语句，才会创建视图

2.数据库迁移，视图也得随之迁移，否则在新数据中是不能用的

3.创建视图时，尽量不要带or replace

2.5数据类型

数据类型分类：

1.number(x,y) 数字类型，x表示最大长度，y表示精度

2.varchar2(x) 可变字符串，x表示最大长度

3.char(x) 定长字符串，x表示最大长度

4.long 长字符串，最大2G

5.Date，日期（年月日时分秒）

6.TIMESTAMP 时间戳，精确到微秒

要掌握oracle数据类型，在java中的对应数据类型

<u>https://www.cnblogs.com/softidea/p/7101091.html</u>

三．创建表格部分

3.1 DDL

1.create关键字

作用：用于创建数据库对象（表、视图、序列等）

语法： create table tablename(column1 dataType, column2 dataType,...,columnN dataType)

语法2：create table tablename as subquery

2.alert关键字

作用：用于修改数据库对象（表、视图、序列等）

语法：

1)alter table tablename add（columnname dataType）

2)alter table tablename modify( columnname dataType)

3)alter table tablename drop [column] columnname

3.drop关键字

作用：用于删除数据库对象（表、视图、序列等）

语法：

drop table tablename

3.2约束

一、定义

约束（constraint）：在建表时，为某些列添加一些特定的规则，保证数据库的数据满足某种用户的要求。添加约束之后，在往表中（插入、更新）数据时，如果数据不满足约束，则该条语句不能执行

二、约束的分类

①非空约束 not null

②唯一约束 unique

③自定义检查约束 check

④主键约束 primary key

⑤外键约束 foreign key

三、如何添加约束

1)在建表的同时，可以为某一列添加约束

①在列后面直接追加约束

②在填写完所有列之后，添加约束

2. 在建表之后，通过修改表结构来添加约束

3.3三范式

第一范式

列不可分

第二范式

不能部分依赖

第三范式

不能存在传递依赖

3.4索引

索引：类似于“书”的目录，索引可以加快对表的查询速度。

作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

使用：

CREATE INDEX index ON table (column[, column]...);

条件：

1.当数据量非常大的时候

2.当该列的值不经常重复的情况下

3.当该列的值不容易发生变化的情况下

重点：数据库会为我们的表自动创建索引，为表中的唯一键列自动的添加索引