1.SQL语句概述

1.1.SQL语句介绍

• SQL（Structured Query Language，结构化查询语句）是一种特定目的编程语言，用于管理关系型数据库管理系统。
• SQL基于关系代数和元组相关演算，包括一个数据定义语言和数据操作语言。SQL的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

1.2.SQL语句分类

1. DDL（Data Definition Language）数据定义语言
   用于定义或修改数据库中的对象，如：表、索引、视图、数据库、存储过程、触发器、自定义函数等。

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

3. DCL（Data Control Language）数据控制语言
   用来设置或更改数据库事务、保存点操作、授权操作（用户或角色授权，权限回收，创建角色，删除角色等）、锁表、锁实例等。

4. DQL（Data Query Language）数据查询语言
   用来查询数据库内的数据，如查询数据、合并多个select语句的结果集、子查询。

2.数据类型

2.1.数据类型概念

• 数据类型是数据的一个基本属性，主要用于建表时指定字段的数据类型，包括：常用数据类型和非常用数据类型。
• 常用数据类型包括：数值类型、字符类型、日期类型等。
• 非常用数据类型包括：布尔类型、空间数据类型、JSON数据类型等。

2.2.常用数据类型

2.2.1.数值类型

1. 整数类型

整数类型	范围（signed）	范围（unsigned）	占用空间（bytes）
tinyint	[-128,127]	[0,255]	1
smallint	[-32768,32768]	[0,65535]	2
mediumint	[-8388608,8388607]	[0,16777215]	3
int(integer)	[-2147483648,2147483647]	[0,4294967295]	4
bigint	[-2^63,2^63-1]	[0,2^64-1]	8

2. 浮点类型

   • float
     • 单精度浮点数，8位精度
     • 占用4字节
   • double
     • 双精度浮点数，16位精度
     • 占用8字节

3. 定点类型

   • decimal/numeric：
     • 语法格式包括numeric/decimal，numeric/decimal§
     • 和numeric/decimal(p,s) 要求s≤p，p表示可存储的最大精度，s表示小数点后有效数字个数

2.2.2.字符类型

1. GaussDB(for MySQL)支持的字符类型有char，varchar，binary，varbinary，blob，text，enum，set。默认编码集utf8mb64下，汉字占3个字节，数字和英文字符占1个字节，其他字符最多可占4个字节。
2. char与varchar
   • char(n)
     • 存储定长字符串，n取值0-255
     • 若输入长度小于n，则用空格在右端补齐
   • varchar(n)
     • 存储变长字符串，n取值0-65535
     • 若输入长度小于n，不需要利用空格补齐
3. binary和varbinary
   • binary(n)
     • 存储二进制定长字符串
     • 少于n个字节的会自动在尾部加0x00字节
   • varbinary(n)
     • 存储二进制变长字符串
     • 少于n个字节额不会去填补0x00字节
4. text
   • 存储大对象变长字符串
   • 关键词：tinytext(1 byte),text(2 bytes),mediumtext(3 bytes),longtext(4 bytes)
5. blob
   • 存储二进制大对象变长字符串
   • 关键词：tinyblob(1 byte),blob(2 bytes),mediumblob(3 bytes),longblob(4 bytes)
6. enum
   • 单选枚举，最多可包含65535个不同的元素
7. set
   • 多选枚举，最多可包含64个元素

2.2.3.日期类型

日期类型	范围	格式	占用空间(bytes)
year	1901/2155	YYYY/YY	1
date	1000-01-01/9999-12-31	YYYY-MM-DD	3
time	-838:59:59/838:59:59	HH:MM:SS	3
timestamp	1970-01-01 00:00:00/2037-12-31 23:59:59	YYYY-MM-DD HH-MM-SS	4
datetime	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH-MM-SS	8

2.3.非常用数据类型

2.3.1.布尔类型

数据类型	说明	对应关键字	取值范围	占用空间
boolean	存储布尔类型数据，转化为tinyint(1)存储	bool、boolean	true、false	1字节

2.3.2.空间数据类型

• geometry
• point
• linestring
• polygon

2.3.3.JSON数据类型

• 支持原生JSON数据类型，可以更有效存储和管理JSON文档

2.4.数据类型案例

创建表

DROP TABLE IF EXISTS `T_TEST_CASE`;
CREATE TABLE `T_TEST_CASE`(
	`section_id` NUMERIC(10) PRIMARY KEY,
	`section_grade` INT,
	`section_name` VARCHAR(100),
	`section_is_excellent` Boolean,
	`section_date` date
);

然后修改列的数据类型

ALTER TABLE `T_TEST_CASE` MODIFY `section_grade` DOUBLE;

2.5.思考题

1. bigint占用4个字节？答：false，占用8个字节。
2. blob用于存储变长大对象二进制数据？答：true。
3. create table aaa(name char(5)); insert into aaa values('TEST '); select name=‘test’ from aaa;结果为：1。

3.系统函数

3.1.系统函数概述

• 系统函数是对一些业务逻辑的封装，以完成特定的功能。系统函数可以有参数，也可以没有参数。系统函数执行完成后会返回执行的结果。

3.2.系统函数分类

• 数值计算函数
• 字符处理函数
• 时间日期函数
• 类型转换函数
• 系统信息函数

3.3.数值计算函数

• abs(x)，cos(x)，acos(x)，asin(x)：返回表达式的绝对值，余弦值，正弦值，反余弦值和反正弦值。
  • abs(x)的返回值类型与参数x数据类型相同。
  • asin和acos函数说明：入参x是可转成数值型的表达式，取值范围为[-1,1]。

• round(x,d)：将数值x按照d指定的向小数点前后截断，d取值范围为[-30,30]间的整数。

• pow(x,y)：等同于power(x,y)，计算x的y次方

语法	功能	示例
ceil(x)	返回大于或者等于指定表达式x的最小整数	ceil(15.3)->16
sign(x)	取x结果的符号，大于0返回1，小于0返回-1，等于0返回0。	sign(2*3)->1
sqrt(x)	返回非负实数x的平方根。入参是可转成非负数值型表达式。	sqrt(49)->7;sqrt(-49)->null
truncate(x,d)	按指定的格式截取输入的数值数据。x是待截取的数据，d是截取精度。	truncate(15.79,1)->15.7;truncate(15.79,-1)->10
floor(x)	求小于或等于表达式x值得最近的整数	floor(12.8)->12
pi()	返回结果为pi的值，默认有效数字7位。	pi()->3.141593
mod(x,y)	求模运算	mod(29,3)->2

3.4.字符处理函数

• concat(str[,…]),concat_ws(separator,str1,str2,…)：拼接一个或多个字符串。第一个函数无分隔符，第二个函数可以指定分隔符连接。

select concat('11','null','22'),concat_ws('-','11',null,'22') from dual;
输出结果：11null22,11-22

• hex(str)：返回十六进制的字符串表示形式。

select hex('ABC');
输出结果：414243

• insert(str,pos,len,newstr),replace(str,src,dst)：字符串插入和字符串替换函数。

select insert('quadratic',5,2,'what'),replace('123456','45','abds') from dual;
输出结果：quadwhattic,123abds6

• instr(str1,str2)：字符串查找函数，返回要查找的字符串首次在源字符串中出现的位置。

select instr('gauss数据库', '据');
输出结果：7

语法	功能	示例
left(str,length)	返回指定字符串的左边几个字符。	left(‘abcdef’,3)->abc;left(‘abcdef’,0)或left(‘abcdef’,-1)->空串
length(str)	获取字符串字节数的函数	length(‘1234大’)->7
lower(str)	将字符串转换成对应字符的小写	lower(‘ABCD’)->abcd;lower(‘1234’)->1234
upper(str)	将字符串转换成对应的字符的大写	upper(‘abcd’)->ABCD;upper(‘1234’)->1234
space(n)	生成n个空格	concat(‘123’,space(3),‘abc’)->123 abc
right(str,len)	返回指定字符串的右边几个字符	right(‘abcdef’,3)->def;right(‘abcdef’,0)或right(‘abcdef’,-1)->空串
reverse(str)	返回字符串的倒序，仅支持string类型	reverse(‘abcd’)->dcba
substr(str,start[,len])	字符串截取函数	substr(‘abcdefg’,3,4)->cdef;表示从’abcdefg’字符串的第3个字符开始截取长度为4的字符

3.5.时间日期函数

• date_format(date,format)：格式化日期函数，根据format转化为需要的格式。

select date_format(sysdate(),'%W'),date_format(sysdate(),'%w'),date_format(sysdate(),'%Y-%m-%d')
输出结果：Monday,1,2022-08-01

• extract(field from datetime)：从指定的日期（datetime）中提取指定的时间字段（field），按指定的格式截取输入的日期数据。

select extract(month from date '2022-08-01') from dual;
输出结果：8

语法	功能	示例
current_timestamp(fractional_second_precision)	获取当前系统时间戳	select current_timestamp(4);->2022-08-01 21:44:03.0502，4表示微秒精度
current_date()	获取当前日期	current_date()->2022-08-01
current_time()	获取当前时间	current_time()->21:46:33
from_unixtime(unix_timestamp)	转换Unix时间戳为日期	from_unixtime(1234567890)->2009-02-14 07:31:30
now(fraction_second_precision)	获取当前系统时间	now()->2022-08-01 22:12:28
sleep(n_second)	设置休眠时间。单位是秒
unix_timestamp(),unit_timestamp(datetime)	获取Unix时间戳的方法，即当前时间到1970-01-01 00:00:00 UTC所经过的秒数。	unix_stamp()->1659363260
date_add(date2,INTERVAL d_value d_type)	在date2中加上日期和时间，d_type可取值second，minute，hour，day，week，month，year	date_add(sysdate(),interval 3 hour)表示当前时间加上3小时->2022-08-02 09:25:00
date_sub(date2,interval d_value d_type)	在date2中减去日期和时间，d_type可取值second，minute，hour，day，week，month，year	date_sub(sysdate(),interval 3 hours)表示当前时间减去3小时->2022-08-02 03:41:25
add_time(date2,time_interval)	在date2中加上时间间隔	addtime(‘2022-08-02 23:59:59.999999’,‘1:1:1:1.000002’)->2022-08-03 01:01:00.999999
sub_time(date2,time_interval)	在date2中减去时间间隔	subtime(‘2022-08-02 23:59:59.999999’,‘1:1:1:1.000002’)->2022-08-02 22:58:58.999999
datediff(date1,date2)	date1与date2的日期差	datediff(sysdate(),‘2020-01-01’),datediff(‘2020-01-01’,sysdate())->944,-944
timediff(time1,time2)	time1与time2的时间差	timediff(sysdate(),‘2022-08-02 00:00:00’),timediff(‘2022-08-02 00:00:00’,sysdate())->06:50:39,-6:50:39

3.6.类型转换函数

• if(ocnd,exp1,exp2),ifnull(exp1,exp2),nullif(exp1,exp2)：条件判断函数。
• nullif函数中expr1和expr2应该为相同数据类型，或能隐式转换成相同数据类型，否则校验报错。

select if(10>13,10,14),ifnull(10,12),nullif(10,12)
输出结果：14,10,10

• cast(value as type)/convert(value,type)：类型转换函数
• Type取值：binary，char()，date，time，datetime，decimal，signed，unsigned

cast('123e342.82' as signed),convert((1/3)*100,unsigned) as percent
输出结果：123,33

3.7.系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

• version():返回数据库的版本号。
• connection_id()：返回服务器的连接数。
• database()：返回当前数据库名称。
• schema()：返回当前schema名称。
• user(),system_user(),session_user(),current_user()：返回当前用户名称。
• last_insert_id：返回最后生成的auto_increment的值。
• charset(str)：返回字符串str的字符集。
• collation(str)：返回字符串str的字符排列方式。

4.操作符

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

4.1.逻辑操作符

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

-- 从staffs表中查询2000年后入职，且薪资>5000的职员信息
select * from staffs where hire_date>'2000-01-01 00:00:00' and salary>5000;
-- 从staffs表中查询2000年后入职，或薪资>5000的职员信息
select * from staffs where hire_date>'2000-01-01 00:00:00' or salary>5000;
-- 从staffs表中查询不在2000年后入职，且薪资不大于5000的职员信息。
select * from staffs where not hire_date>'2000-01-01 00:00:00' and not salary>5000;

4.2.比较操作符

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

--- 从staffs表中查询薪资>5000的职员信息
select * from staffs where salary>5000;
-- 从staffs表中查询薪资不等于5000的职员信息
select * from staffs where salary!=5000;

4.3.算术运算符

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

select 2+3,2*3,3<<1 from dual;
输出结果：5,6,6

4.4.测试运算符

运算符	描述
in	元素在指定的集合中。
not in	元素不在指定的集合中。
exists	存在符合条件的元素。
not exists	不存在符合条件的元素。
between…and…	在两者之间，例如a between x and y等效于a >= x and a <= y。
not between…and…	不在两者之间，例如a not between and y等效于a < x or a > y。
is null	等于null
is not null	不等于null
like … [escape char]	与…相匹配。仅支持字符类型。
not like … [escape char]	与…不匹配。
regexp	字符串与正则表达式相匹配，仅支持string类型。
regexp_like	字符串与正则表达式相匹配，支持string类型和numeric类型。表达式返回值是bool类型。

-- IN运算符
select * from T_TEST_OPERATOR where ID in(1,2);
-- EXISTS运算符
select count(1) from dual where EXISTS(select ID from T_TEST_OPERATOR where name='zhangsan');
-- BETWEEN...AND...运算符
select * from T_TEST_OPERATOR where ID between 1 and 2;
-- IS NULL运算符
select * from T_TEST_OPERATOR where name is null;
-- LIKE...[ESCAPE char]运算符
select * from T_TEST_OPERATOR where name like '%an%';

4.5.通配符

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

4.6.其他操作符

操作符	描述
单引号(’)	表示字符串类型。如果在字符串文本里含有单引号，那么必须运用两个单引号示意。
双引号(")，反引号(`)	表示表、字段、索引等Object Name或者别名。