【数据库】SQL语法入门

前言：

知道什么是数据库了，然后来看看我们是怎么操作数据库吧

一、SQL语句介绍

1. 维基百科定义：
   SQL（Structured Query Language，结构化查询语言）是一种特定目的编程语言，用于管理关系数据库管理系统，或在关系流数据管理系统中进行流处理。

2. SQL语句分类

   • DDL（Data Definition Language）数据定义语言
     用于定义或修改数据库中的对象，如：表、索引、视图、序列、用户、角色、表空间等

     ○ 定义数据库：创建数据库（create database），修改数据库属性（alter database）
     ○ 定义表空间：创建表空间（create tablespace），修改表空间属性（alter tablespace），删除表空间（drop tablespace）
     ○ 定义表：创建表（create table），修改表属性（alter table），删除表（drop table），删除表中所有数据（truncate table）
     ○ 定义索引：创建索引（create index），修改索引属性（alter index），删除索引（drop index）
     ○ 定义角色：创建角色（create role），删除角色（drop role）
     ○ 定义用户：创建用户（create user），修改用户属性（alter user），删除用户（drop user）
     ○ 定义视图：创建视图（create view），删除视图（drop view）
     ○ 定义路由规则：创建路由规则（create distribute rule），删除路由规则（drop distribute rule）
     ○ 定义节点：创建节点（create node），删除节点（drop node），修改节点（alter node）
     ○ 修改系统参数：修改系统参数（alter system），结束会话（alter system kill session）
     ○ 操作会话：修改会话（alter session），结束会话（alter system kill session）

   2. DML （Data Manipulation Language）数据操纵语言
      用于对数据库表中的数据进行操作，如插入，更新和删除

      ○ 数据操作：插入数据（insert），更新数据（update）和删除数据（delete）
      ○ 导入导出：导入（load），导出（dump）
      ○ 其他：查看执行计划（explain plan）

   3. DCL （Data Control Language）数据控制语言
      用来设置或更改数据库事务、权限操作（用户或角色授权，权限回收）、锁表（支持共享锁和排他锁两种锁表模式）、停机等。

      ○ 事务管理：提交事务（commit），回滚事务（rollback）
      ○ 授权操作：授予权限（grant）,回收权限（revoke）
      ○ 锁表：lock table
      ○ 停机：shutdown

   4. DQL （Data Query Language）数据查询语言
      用来查询数据库内的数据，如单表查询、多表查询

      ○ 查询数据（select）
      ○ 合并多个select语句的结果集

二、数据类型

数据类型：是数据的一个基本属性，主要用于建表时指定字段的数据类型

注意：不同的数据类型所占的存储空间不同，能够进行的操作也不相同。数据库中的数据存储在数据表中。数据表中的每一列都定义了数据类型，用户存储数据时，须遵从这些数据类型的属性，否则可能会出错。

常用数据类型

1. 数值类型

   类型	占用字节	取值范围	关键字
   teger（32位有符号整数）	4	-2^31 ~ 2 ^31-1	int，integer，short，smallint，tinyint，binary_integer，int signed
   teger unsigned（32位无符号整数）	4	0~2^32-1	uint，binary_uint32，integer unsigned
   gint（64位有符号整数）	8	-2^63~2 ^63-1	bigint、binary_bigint和bigint signed
   浮点类型
   float	8	[-1.79E+308, +1.79E+308]	real，double，float和binary_double
   高精度数值类型
   decimal/number	4~24	(-1.0E128, 1.0E128）	decimal，number和numeric

   USE_NATIVE_DATATYPE（数据类型控制参数）
   数据类型：bigint、double、float、int 、integer、real、smallint、tinyint
   TRUE：映射为binary_double类型；
   FALSE：映射为number类型。

2. 字符类型

   类型	描述
   T8编码	汉字和全角字符占2~8个字节，数字和英文字符等都是1个字节
   K编码	汉字和全角字符占2个字节，数字等字符占用1个字节

   字符串类型	描述	关键字
   har(size [byte char])	存储定长字节或者字符串，默认为byte类型，关键字为char	size byte：最大能容纳的字节数，size char：最大能容纳的字符数
   har(size)	存储定长字符串，等同于char(size char)	关键字：nchar

注：1~8000字节，若输入长度小于size，则利用空格在右端补齐

变长字符串类型	描述	关键字
varchar(size [byte /char])	存储变长字节或字符串。size byte：最大能容纳的字节数，size char：最大能容纳的字符数，1~8000字节	varchar
nvarchar(size)	用于存储变长字符串。1~8000字节	nvarchar
clob	存储大对象变长字符串。占用字节：0~4G	clob，text，longtext，long

注：若输入长度小于size，不会利用空格补齐

建议：

• 对于存储内容知道指定长度的，如存储性别时，只有可能是男或者女，这时候采用nchar(1)，可以节省存储空间。
• 对于存储内容长度不定的，可采用变长字符串类型varchar等，可以节省存储空间，因为不需要利用空格补齐。
• clob常用于存储长的数据内容，如对某产品的详细描述等。

3. 日期类型

不带时区的时间戳（8字节）	可以保存的时间	关键字	例子
datetime/date	保存年、月、日、时、分、秒	date、datetime	2020-03-16 20:10:10
timestamp[(n)]	保存年、月、日、时、分、秒、微秒，n取值为0~6，默认值为6	timestamp	2020-03-16 17:29:13.263183 （n=6）

带时区的时间戳	可以保存的时间	关键字	例子
timestamp(n) with time zone	保存年、月、日、时、分、秒、微秒和时区，占12字节	timestamp(n) with time zone	2020-03-16 18:41:30.135428 +08:00
timestamp(n) with local time zone	不保存时区，存储时转换为数据库时区的timestamp，占8字节	timestamp(n) with local time zone	存储时为2020-03-16 18:41:30.135428 查看时为2020-03-16 18:41:30.135428 +08:00

非常用数据类型

二进制类型

数据类型	说明	关键字	占用空间
binary(size)	存储定长的二进制数据	binary	1~8000字节
raw(size)	存储变长的二进制数据	raw	1~8000字节
varbinary(size)	存储变长的二进制数据	varbianry	1~8000字节
image	varbinary的大对象类型，用于存储大对象数据	image	0~4G
blob	raw类型的大对象类型，存储变长大对象二进制数据	blob、bytea	0~4G

布尔类型

数据类型	说明	关键字	取值范围	占用空间
boolean	存储布尔类型数据	bool、boolean	true,false	4字节

时间间隔类型

数据类型	说明	取值范围	占用空间
interval year[(n)] to month	存储一个若干年若干月的时间间隔	[-9999-11,+9999-11]	4字节
interval day[(n1)] to second [(n2)]	存储一个若干天（包括天、时、分、秒、微秒）的时间间隔	[-9999999 23:59:59.999999, +9999999 23:59:59.999999]	8字节

注：

• 布尔类型可与int和bigint类型转换，因为布尔类型可以看成是数字0与1，因此它可以转换为整数0和1整数类型可以转换为布尔类型，转换规则为整数0对应布尔false，其他非0整数为布尔true。
• n的取值范围是[0,4]，表示年的精度，默认值为2。
• n1取值范围是[0,7]，表示天的精度，默认值为2。
• n2取值范围是[0,6]，表示秒后面的精度，不指定时默认为6。

三、系统函数

1. 数值计算函数

语法	功能	示例
abs（exp）	取绝对值。入参：数值类型或可以隐式转换为数值类型的非数值类型；返回值：同入参数据类型	abs（-10）–>10
sin和cos函数	入参：可转成数值型的表达式；返回值：NUMBER类型	cos(0)–>1 / sin（0）–>0
asin和acos函数	入参：可转成数值型的表达式，取值范围为[-1,1]；返回值：NUMBER类型	acos（1）—> 0 / asin（0）–> 0
bitand(exp1,exp2)，bitor(exp1,exp2)，bitxor(exp1,exp2)	按位与，按位或，按位异或运算	bitand(29，15)–>13 / bitor(29，15)–>18 / bitor（29，15）–>31
round(number[,decimals])	将number类数值按照decimals指定的向小数点前后截断	ROUND(1234.5678,-2)–> 1200 / ROUND(1234.5678,2)–>1234.57
trunc(number, scale)	按指定的格式截取输入的数值数据	trunc(15.79,1) --> 15.7 / number：待截取的数据，scale：截取精度 / trunc(15.79,-1) -->10 / 返回值：number
ceil(exp)	计算大于或者等于指定表达式n的最小整数	ceil(15.3) --> 16
floor(exp)	计算小于或等于表达式值的最近的整数。	floor(12.8) -->12
sign(exp)	取数值类型的符号，大于0返回1，小于0返回-1，等于0返回0	sign(2*3) --> 1/返回值：数值类型
sqrt(n)	计算非负实数的平方根	sqrt(49) --> 7
mod(exp1, exp2)	求模运算	mod(29,3) --> 2

注：exp必须是可转换为数值类型的表达式

2. 字符处理函数

语法	功能	示例
concat(str[,…])，concat_ws(separator,str1,str2,…)	拼接一个或多个字符串。第一个函数无分隔 符，第二个函数可以指定分隔符连接	CONCAT(‘11’,‘NULL’,‘22’) -->11null22 /CONCAT_WS(’-’,‘11’,NULL,‘22’) -->11-22
hex(str)，hex2bin(str)，hextoraw(str)	第一个函数返回十六进制值的字符串表示形式，其他函数返回十六进制字符串所表示的字节串。不同点：hex2bin返回binary型，hextoraw返回raw型	HEX(‘ABC’) -->414243
insert(str,pos,len,newstr)，replace(str,src,dst)	字符串插入和字符串替换函数	INSERT(‘QUADRATIC’,5,2,‘WHAT’) --> Quadwhattic / REPLACE(‘123456’,‘45’,‘ABDS’)–>123abds6
instr(str1,str2[,pos[,n]])，instrb(str1,str2[,pos[,n]])	字符串查找函数。返回要查找的字符串在源字符串中的位置。不同点：instr按字符计算位置，instrb按字节计算位置
left(str, length)	返回指定字符串的左边几位字符。	left(‘abcdef’,3) --> abc / left(‘abcdef’,0) 或left(‘abcdef’,-1) -->空串
right(str,len)	返回指定字符串的右边几位字符。	right(‘abcdef’,3) --> def / right(‘abcdef’,0) 或right(‘abcdef’,-1) --> 空串
lower(str)	将字符串转换成对应字符的小写。	lower(‘ABCD’) --> abcd / lower(‘1234’) --> 1234
upper(str)	将字符串转换成对应字符的大写。	upper(‘abcd’) --> ABCD / upper(‘1234’) --> 1234
length(str)	获取字符串长度的函数。	length(‘1234大’) --> 5
reverse(str)	返回字符串的倒序。仅支持string类型。	reverse(‘abcd’) --> dcba
substr(str, start[, len])	字符串截取函数。	substr(‘abcdefg’,3,4) --> cdef
space(n)	生成n个空格。n取值范围[0,4000]。	concat(‘123’,space(3),‘abc’) --> 123 abc

3. 时间日期函数

语法	功能	示例
add_months(date,n)，months_between(date1, date2)	返回date加或减n个月后的值，计算两个日期之间的月份差	ADD_MONTHS(TO_DATE(‘2016-02-10’,‘YYYY-MM-DD’),1) -->8 MONTHS_BETWEEN(TO_DATE(‘2016-02-10’–>-24
extract(field from datetime)，trunc(date[,fmt])	从指定的日期（datetime）中提取指定的时间字段（field），按指定的格式截取输入的日期数据	EXTRACT(MONTH FROM DATE ‘2019-08-23’) -->8 TRUNC(SYSDATE,‘YY’)–>2019-01-01 00:00:00
unix_timestamp() / unix_timestamp(datetime)	获取Unix时间戳的方法，即当前时间到1970-01-01 00:00:00 UTC所经过的秒数。	unix_timestamp() --> 1566548122 / unix_timestamp(2005-03-27 09:00:00.000000) --> 1111885200
from_unixtime(unix_timestamp)	根据Unix时间戳返回日期datetime。	from_unixtime(1111885200) --> 2005-03-27 09:00:00.000000
current_timestamp(fractional_second_precision)	获取当前系统时间及时区。fractional_second_precision表示秒后面小数位数的精度，取值范围为[0~6]，默认为6。	current_timestamp(4) --> 2019-08-23 16:10:45.5461 +08:00
now(fractional_second_precision)	获取当前系统时间及时区。	now() --> 2019-08-23 16:15:22.321674 +08:00
systimestamp	返回当前的时间戳。	systimestamp --> 2019-08-23 16:10:45.546126 +08:00
getutcdate()	返回当前UTC时间戳，同时带有UTC时区信息（即0时区）。	getutcdate() --> 2019-08-23 08:10:45.546148 +00:00
sleep(n_second)	设置休眠时间。单位是秒。

4. 间隔函数

语法	功能
numtodsinterval(num, ‘interval_unit’)	输入一个数值和interval域描述字段，输出interval day to second类型。	sysdate --> 2019-08-23 12:52:43 返回当前时间1小时后的时间：sysdate+numtodsinterval(1,‘hour’) --> 2019-08-23 13:52:43
numtoyminterval(num, ‘interval_unit’)	输入一个数值和interval域描述字段，输出interval day to second类型。	sysdate --> 2019-08-23 12:55:54 返回当前时间1年后的时间sysdate+numtoyminterval(1,‘year’) -->2020-08-23 12:55:54
to_dsinterval(str_exp)	输入interval字符串，输出interval day to second的值。用于表示间隔中的天(day)和时间(包括时、分、秒、微秒)。适用于表示更精确的时间。	sysdate --> 2019-08-23 15:03:01 返回当前时间180天后的时间：sysdate+to_dsinterval(‘180 00:00:00’) --> 2020-02-19 15:03:01
to_yminterval(str_exp)	输入interval字符串，输出interval day to month的值。用于表示间隔中的年(year)和月(month)。适用于只关注时间差的年和月。	sysdate --> 2019-08-23 14:59:33 返回当前时间2年1个月后的时间：sysdate+to_yminterval(‘02-01’) --> 2021-09-23 14:59:33

5. 类型转换函数

语法	功能	示例
if(cond,exp1,exp2)，ifnull(exp1,exp2)，nullif(exp1,exp2)，nvl(exp1,exp2)，nvl2(exp1,exp2,exp3)	条件判断函数	IF(10>13,10,14) -->14 IFNULL(10,12) --> 10 NULLIF(10,10) -->10 NVL(10,12) -->10 NVL2(0,10,12)–>10
to_char(exp[,fmt])，to_clob(str)，to_date(exp[,fmt])，to_number(n[,fmt])	将指定入参转换为char，clob，date，number类型
ascii(str)	返回字符串str首个字符对应的ASCII码。	ascii(‘hello’) --> 104
char(n)、chr(n)	返回ascii码为n的字符。n支持范围为[0,127]。入参是可转成数值型表达式。	char(67) --> C / chr(67) --> C
cast(expr as datatype)	将列名/值转换为指定的数据类型datatype。表达式可以转换为与自身相同的类型。	cast(‘10’ as int) --> 10
convert(expr, data_type)	将expr转换成data_type类型。data_type取值范围是除了clob，blob，image以外的所有数据类型。	convert(‘2018-06-28 13:14:15’, timestamp) --> 2018-06-28 13:14:15.000000
unhex(expr1)	expr1为十六进制字符串，将十六进制字符串转化为byte字节表示的字符。	unhex(‘426232’) --> Bb2

四、操作符

操作符可对一个或多个操作数进行处理，位置上可能处于操作数之前、之后，或两个操作数之间。

1. 逻辑操作符

操作符	功能
and	支持在查询条件where/on/having语句中，用于条件之间的逻辑与操作。
or	支持在查询条件where/on/having语句中，用于条件之间的逻辑或操作。
not	支持在where/having子句后的条件表达式前加NOT关键字，对条件结果取反，常与关系运算合用，例如not in、not exists。

2. 比较操作符

操作符	描述
<	小于
>	大于
<=	小于或等于
>=	大于或等于
=	等于
<> 或 !=	不等于

注：返回类型为布尔类型，可以是t、f或NULL。

3. 算术操作符

运算符	描述	运算符	描述
+	加		字符串拼接
-	减		按位或
*	乘	&	按位与
/	除（除法操作符不会取整）	^	按位异或
%	模运算	<<	左移位
		>>	右移位

4. 测试操作符

运算符	描述
in	元素在指定的集合中。
not in	元素不在指定的集合中。
exists	存在符合条件的元素。
not exists	不存在符合条件的元素。
between … and …	在两者之间。
not between … and …	不在两者之间。
is null	等于NULL。
is not null	不等于NULL。
any	子查询中有一个值满足条件即可。
like … [escape char]	与…相匹配。仅支持字符类型。
not like … [escape char]	与…不匹配。
regexp	字符串与正则表达式相匹配，仅支持string类型
regexp_like	字符串与正则表达式相匹配，支持string类型和number类型。表达式返回值是bool类型。

5. 其他操作符

通配符

通配符	描述
%	表示任意数量的字符，包括无字符，用于like和not like语句中。
_	下划线，表示确切的一个未知字符，用于like和not like语句中。

其他操作符

操作符	描述
单引号（’）	表示字符串类型。如果在字符串文本里含有单引号
双引号（"）/反引号（`）	表示表、字段、索引等Object Name或者是别名