SQL语法基础

SQL 简介

通过 SQL可以实现与 Oracle 服务器的通信
SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词
SQL 是数据库语言，Oracle 使用该语言存储和检索信息
表是主要的数据库对象，用于存储数据

SQL 组成

SQL由下列类别的命令组成：

数据定义语言（DDL操作表结构(Data Definition Language)）:
CREATE、ALTER、DROP

数据操纵语言（DML操作表数据(data manipulation language)）:
INSERT、DELETE、SELECT、UPDATE

数据定义语言(DDL)

数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象
用于操纵表结构的数据定义语言命令有：
CREATE TABLE －创建表
ALTER TABLE －修改表
DROP TABLE －删除表

数据操纵语言（DML）

数据操纵语言用于检索、插入和修改数据
数据操纵语言是最常见的SQL命令
数据操纵语言命令包括：
SELECT 查询
INSERT 插入
UPDATE 更新
DELETE 删除
TRUNCATE TABLE －清空表（不存储日志）

DML – SELECT 命令

利用现有的表创建表
语法：

利用子查询获取到的表数据放到新建的表中（不包含主键索引）
CREATE TABLE <new_table_name> AS
SELECT column_names FROM <old_table_name>;

列别名可以直接在列后边写

select  sno 学号 from student;

DML – INSERT 命令

插入日期类型的值
日期数据类型的默认格式为“DD-MON-YY”
使用日期的默认格式
使用TO_DATE函数转换

--插入数据 字符串用单引号  
--系统函数to_date('2001-01-01','yyyy-MM-dd')，第一个参数日期，第二个参数转换格式（格式要求一样）。
--系统函数sysdate获取系统当前时间
--commit将数据提交 RollBack回滚。数据提交后将不能回滚
--不能拼量插入(myslq可以)
--Selection Comment将选中的全体注释
insert into student
values(9,'你好',123456,1,20,20,to_date('2001-01-01','yyyy-MM-dd'),sysdate);

//括号的参数要一致
insert into student(id,name,username,password,sex,age,birthday,create_time)
values(1,'张三',123456,1,20,20,to_date('2001-01-01','yyyy-MM-dd'),sysdate);

插入来自其它表中的记录

语法：

 INSERT INTO <table_name> [(cloumn_list)] 
       SELECT column_names FROM <other_table_name>;

注意：插入的类和查找的类一一对应

DML – UPDATE 命令

--可视化修改
--点击Edit date 解锁
--修改数据后点击Post changes（勾勾）提交
--Commit将数据提交
--点击Edit date 上锁
select * from student for update;

命令修改

update student set sex = 0 where id in (2,4);

Oracle 数据类型

创建表时，必须为各个列指定数据类型
以下是 Oracle 数据类型的类别：

字符串类型

VARCHAR2()是Oracle独有的，与VARCHAR()是一样。VARCHAR2()方便与Oracle不同版本的数据库兼容。
LONG是字符串类型

数字类型

日期类型

Oracle的伪列

rownum常用于分页

Oracle的伪表

伪表(dual)不是真实的表（有一行一列），无法存放数据，一般用于单行函数查询使用

select * from dual;

select 1+1,2+1 from dual;

SQL 操作符

算术操作符

算术操作符用于执行数值计算
可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
算术操作符包括加(+)、减(-)、乘(*)、除(/)
例如使得表中的数字全部加上5

select age 原来的,age + 5 加5后 from student;

表中数据每条数据之间的字段运算

select age*sex 年龄乘以性别 from student;

比较操作符 （逻辑操作符）

比较操作符用于比较两个表达式的值
比较操作符包括 =、!=、、<>、<、>、<=、>=、BETWEEN…AND、IN、NOT IN、LIKE 和 IS NULL、IS NOT NULL等

-- =
select * from student where sex=0;
-- != <>
select * from student where sex<>3;
select * from student where sex!=3;
--between ... and (包括边界)
select * from student where id between 3 and 5;
--in 在集合中的数据
select * from student where id in (3,4,5);
--not in 不在集合中的数据
select * from student where id not in (3,4,5);
--like  %代表占位符(0~n个字符) _占位符(只占一个字符)
select * from student where name like '张%';--查找张开头的
select * from student where name like '%张%';--查找有张的
select * from student where name not like '___'--查找三个字的
--is null
select * from student where birthday is null;
--is not null
select * from student where birthday is not null;

逻辑操作符(与或非)

--and
select * from student where age < 33 and age > 10;
--or
select * from student where age = 33 or age = 14;
--not 将得到的结果取反(注意 is not null 的写法)
select * from student where birthday is not null;
select * from student where not 1=2;

集合操作符

集合操作符将两个查询的结果组合成一个结果

union：合并两个查询的结果，如果有相同的记录则去重(两个集合交集去重)
union all：合并两个查询的结果,不去重（两个集合合并）
intersect：两个查询结果集的交集(两个集合的交集)
minus：从第一个结果集中去掉两个结果集的交集(第一个集合去掉两个集合的交集)
实例：

--集合操作符
--union 合并两个查询的结果，如果有相同的记录则去重
select * from student where sex = 0
union
select * from student where age <=50;
--union all 合并两个查询的结果,不去重
select * from student where sex = 0
union all
select * from student where age <=50;
--intersect 两个查询结果集的交集
select * from student where sex = 0
intersect
select * from student where age <=50;
--minus 从第一个结果集中去掉两个结果集的交集
select * from student
minus
select * from student where age <50;

连接操作符

连接操作符用于将多个字符串或数据值合并成一个字符串

-- 连接操作符
--原样输出select要打印的内容  列明为别名‘学生信息表’
select '学生姓名：' || s.name || '学生帐号：' || s.username || '  学生密码：' ||
       s.password 学生信息表
  from student s

操作符的优先级

SQL 操作符的优先级从高到低的顺序是：
算术操作符 --------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR 逻辑操作符 --------最低优先级

SQL 函数

Oracle 提供一系列用于执行特定操作的函数
SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类：

单行函数分类

单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT 子句中和 WHERE 子句中
单行函数可以大致划分为：

1. 日期函数
2. 数字函数
3. 字符函数
4. 转换函数
5. 其他函数

日期函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
日期函数包括：

1. ADD_MONTHS(d,n) --时间点d再加上n个月
2. MONTHS_BETWEEN(x,y) --用于计算x和y之间有几个月
3. LAST_DAY (d) --返回某个时间值的当月最后一天
4. ROUND(d) --返回某个时间值年月日(四舍五入)
5. NEXT_DAY(x,y) --用于计算x时间后第一个星期y的时间
6. TRUNC(d) --返回某个时间值年月日(直接截取)
7. EXTRACT(year from date) --用于从一个d或者interval类型中截取到特定的部分 ,如： EXTRACT(YEAR from sysdate)

特殊日期函数

–Y或YY或YYY 年的最后一位，两位，三位
select to_char(sysdate,‘Y’) from dual;
–Q 季度,1-3月为第一季度
select to_char(sysdate,‘Q’) from dual;
select to_char(sysdate,‘MM’) from dual; --MM 月份数
–RM 月份的罗马表示(I,II,III…)
select to_char(sysdate,‘RM’) from dual;
–ww 当年第几周
select to_char(sysdate,‘ww’) from dual;
–w 本月第几周
select to_char(sysdate,‘w’) from dual; 　
–DDD 当年第几天,一月一日为001 ,二月一日032
select to_char(sysdate,‘DDD’) from dual;
select to_char(sysdate,‘DD’) from dual; --DD 当月第几天
select to_char(sysdate,‘D’) from dual; --D 周内第几天 　　　　　　
select to_char(sysdate,‘DY’) from dual; --DY周内星期几　
–hh12 12小时制小时数,hh24 24小时制小时数　　　　　　
select to_char(sysdate,‘hh12’) from dual;
select to_char(sysdate,‘Mi’) from dual; --Mi 分钟数
select to_char(sysdate,‘ss’) from dual; --ss 秒数
–把日期转换为字符串
select to_char(sysdate,‘YYYY-MM-DD HH24:mi:ss’) from dual;

实例

--当前时间
select sysdate from dual;
--五个月后的时间
select add_months(sysdate,5) from dual; 
--计算两个日期之间相差的月份  左边减去右边的日期有余数
select months_between(sysdate,add_months(sysdate,5)) from dual;
--计算某个月份最后一天
select last_day(add_months(sysdate,1000)) from dual;
--返回某个时间值年月日(四舍五入)(超过12小时则五入)， 数字也可以四舍五入
select round(to_date('2001-01-01 11:59:59','yyyy-MM-dd hh24:mi:ss')) from dual;
select round(1.5) from dual;
--用于计算x时间后第一个星期y的时间
select next_day(add_months(sysdate,-1),2) from dual;
--返回某个时间值年月日(直接截取)，或着返回某个数字的正数。
select trunc(to_date('2001-01-01 11:59:59','yyyy-MM-dd hh24:mi:ss')) from dual;
select TRUNC(1.5) from dual;
--按字段截取（要截取的字段,被截取的字段）
select EXTRACT(day from sysdate) from dual;
--截取年份。Y或YY或YYY 年的最后一位，两位，三位(循环截取)
select to_char(sysdate,'YYYYYYyyyyyyyyyyyyYYY') from dual;
--截取季度,第一季度(1~3)、第二季度(4,7)...
select to_char(sysdate,'Q') from dual;
--截取月份to_char(date,field),按照字段从日期截取相应的数据
select to_char(to_date('2001-12-01 11:59:59','yyyy-MM-dd hh24:mi:ss'),'MM') from dual;--MM 月份数  
select to_char(to_date('2001-08-01 11:59:59','yyyy-MM-dd hh24:mi:ss'),'RM') from dual;--RM 月份的罗马表示(I,II,III...) 
--截取当年第几个星期
select to_char(to_date('2001-02-01 11:59:59','yyyy-MM-dd hh24:mi:ss'),'ww') from dual;--WW 当年第几周
--截取当月第几个星期
select to_char(to_date('2001-02-09 11:59:59','yyyy-MM-dd hh24:mi:ss'),'w') from dual;--W 当月第几周
--截取本年第几天
select to_char(to_date('2001-12-31 11:59:59','yyyy-MM-dd hh24:mi:ss'),'DDD') from dual;--DDD 当年第几天 例如000~365
--截取当月第几天
select to_char(to_date('2001-12-5 11:59:59','yyyy-MM-dd hh24:mi:ss'),'DD') from dual;--DD 当月第几天 例如01~31
--截取当周第几天
select to_char(to_date('2001-12-5 11:59:59','yyyy-MM-dd hh24:mi:ss'),'D') from dual;--D 当周第几天 例如1~7
--截取当天是星期几
select to_char(to_date('2001-12-5 11:59:59','yyyy-MM-dd hh24:mi:ss'),'DY') from dual;--DY 当天是星期几
--截取第几个小时
select to_char(to_date('2001-12-5 20:59:59','yyyy-MM-dd hh24:mi:ss'),'hh') from dual;--hh、hh12 是12小时
select to_char(to_date('2001-12-5 20:59:59','yyyy-MM-dd hh24:mi:ss'),'hh24') from dual;--hh24 是24小时
--截取多少分钟
select to_char(to_date('2001-12-5 20:18:59','yyyy-MM-dd hh24:mi:ss'),'mi') from dual;--mi 是分钟
--截取毫秒
select to_char(to_date('2001-12-5 20:18:55','yyyy-MM-dd hh24:mi:ss'),'ss') from dual;--ss 是毫秒
--总的输出（原样输出，仅支持年月日和时分秒之间的少部分符号）
select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss w') from dual;

字符函数

字符函数接受字符输入并返回字符或数值

其它字符函数

–转换成ASCII码值
CHR(n)–CHR()和ASCII () chr(9)制表符, chr(10)换行符, chr(13)回车符,chr(97)a
LPAD (string,n,[pad_string])和RPAD (string,n,[pad_string])将左右边的字符串填充一些特定的字符数
LENGTH(str) ‘获取字符串长度’和 null 的长度为null
DECODE(t,a,b,c,d….) --类似ifelse 当t=a,则返回b,当t=c,则返回d以些类推
实例

--字符函数
--首字母大写
Select initcap('hello') from dual;
--字母变小写
Select lower('HELLO') from dual;
--字母变大写
select upper('hello') from dual;
--左删除 (被删除字符串，删除字符串)一直循环删除到字符不相等
Select ltrim('helloword','wor') from dual;
select ltrim('aaaaaabaaaaaa','a') from dual;
--右删除 (被删除字符串，删除字符串)一直循环删除到字符不相等(数字一样)
Select rtrim('helloword','word') from dual;
select rtrim(1112111,1) from dual;
--删除两头空格
Select trim('999 ' ) from dual;
--翻译（待翻译字符串，翻译字符串，译文）
--将待翻译字符串与翻译字符串的字符一一循环比较（循环待翻译字符串字符个数次)
--若是相同则将译文的第相同次数个字符替换待翻译字符
Select translate('abcdef','cao','ABCDEFG') from dual; 
--替换(被替换字符串,比较字符串,替换字符串)被替换字符串与比较字符串比较(比较到被替换字符串结束)
--相同则将替换字符串进行替换
Select replace('jack and jue' ,'ja','bl') from dual;
--查找字符位置，下标从1开始
Select instr ('helloword','d') from dual;
--截取字符串(被截取字符串，从第几个字符开始截取，截取几个字符)
Select substr('helloworld',3,2) from dual; 
--字符串拼接
Select concat ('hello',' word') from dual;

--其他字符函数
select chr(97) from dual;
--字符串左填充LPAD（字符串，自定义长度，填充字符串）
--字符串小于自定义长度时，填充字符串循环填充到其两长度相等
SELECT LPAD('function',15,'=a') FROM dual;
SELECT RPAD('function',15,'=a') FROM dual;--字符串右填充
--获取字符串长度
SELECT LENGTH('frances') FROM dual;
SELECT LENGTH(null) FROM dual;--null的长度为空(不是0)
--去重（重复字符串（数字），字符串（数字））
SELECT trim('9' from '9999876789999') FROM dual;
SELECT trim(8 from 888888888887778888) FROM dual;
--DECODE(t,a,b,c,d….)  --类似ifelse 当t==a,则返回b,当t==c,则返回d以此类推（）
select * from student;
select decode('a','aa','b','ac','e','未知')from dual;
select decode(20,s.sex,s.name,s.age,s.name) 性别20或年龄为20的人 from student s;

数字函数

数字函数接受数字输入并返回数值结果

实例

--数字函数
--绝对值
Select abs(-80) from dual;
--取大于它的最小整数
select ceil(5.1)from dual;
--取它的整数部分
Select floor(80) from dual; 
--Power(m,n)  计算m的n次方
select power(2,2)from dual;
--Mod(m,n)    m%n 求余(取模)
select  mod(5,2)from dual;
--Round(m,n)   四舍五入，保留n位小数
select round(5.8888888899999,5) from dual;
--Trunc(m,n)     直接截取，保留n位小数
select trunc(5.8888888888888888,2)from dual;
--Sqrt(n)            开方根
select sqrt(16)from dual;
--日期转换TO_DATE 字符串转成日期
select to_date('20200101','yyyyMMdd')from dual;
--日期转换 日期格式化输出(可以输出多个付哈珀 不支持数字中文)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss -/*%-+')from dual;

转换函数

转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
TO_CHAR (9-代表整数,0-代表小数,C-国际货币符号, L(本地货币符号）
TO_DATE
TO_NUMBER
实例

--字符串转换成数字
select floor(to_number('10000.3'))from dual;
--格式化数据
--会四舍五入
--9代表整数，不够补空格
--0代表小数，不够补0
SELECT TO_CHAR(1234567.9,'99999999.000000') FROM dual; 

其它函数

NVL(expr1,expr2)->expr1为NULL返expr2；非NULL返expr1;
NVL2(expr1, expr2, expr3) ->expr1不为NULL返expr2；为NULL返expr3
NULLIF (expr1, expr2) ->相等返NULL(空),不等返回expr1; expr1和 expr2类型必须一致

分组函数(聚集函数)

分组函数基于一组行来返回结果
为每一组行返回一个值

AVG(number)：求平均值
MIN(number)：求最小值
MAX(number)：求最大值
COUNT(field)：求单元的所有数量,(不支持别名.*)
DISTINCT：去重

GROUP BY和HAVING子句

GROUP BY子句
用于将信息划分为更小的组
每一组行返回针对该组的单个结果

HAVING子句
用于指定 GROUP BY 子句检索行的条件(只能是分组的字段或者聚集函数)

SELECT子句
只能查询聚集函数和分组字段

实例

--查询各个年龄段，性别为1的人
select age 年龄, count(s.id) 数量
  from student s
 group by s.age
having count(sex) = 1;

注意：having 后边的条件判断只能写聚集函数

ORDER BY子句

ASC 按升序排序
DESC按降序排序

-- asc desc 排序
select * from student order by age desc;--asc升序(默认，可省略)，desc降序
-- 多条件排序用逗号隔开。（按照年龄降序排序，年龄相等按照编号降序排序）
select * from student order by age desc,id desc;--asc升序(默认，可省略)，desc降序

总结

SQL 是通用的数据库语言
SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言
Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等
SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符
SQL 函数可大致分为单行函数、聚合函数和分析函数