第三章 SQL语法入门

第三章 SQL语法入门

一、SQL语句概述

1、SQL语句介绍

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

2、SQL语句分类

• DDL（Definition Language）数据定义语言
  • 用于定义或修改数据库中的对象，如：表、索引、视图、数据库、存储过程、触发器、自定义函数等
• DML（Data Manipulation Language）数据操作语言
  • 用于对数据库表中的数据进行操作，如插入，更新和删除
• DCL（Data Control Language）数据控制语言
  • 用来设置或更改数据库事务、保存点操作、授权操作（用户或角色授权，权限回收，创建角色，删除角色）、锁表、锁实例等
• DQL（Data Query Language）数据查询语言
  • 用来查询数据内数据，如查询数据，合并多个select语句的结果集、子查询

二、数据类型

1、数据类型

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

2、常用数据类型

（1）数值类型：

• 整数类型

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

• 浮点类型

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

• 定点类型

  • decimal/numeric:

（2）字符类型：

• char 与 varchar

  • char(n):
    • 存储定长字符串，n取值0~255
    • 若输入长度小于n，则用空格在右端补齐
  • varchar(n):
    • 存储变长字符串，n取值0~65535
    • 若输入长度小于n，不需要利用空格补齐

• binary与var binary

  • binary(n):
    • 存储二进制定长字符串
    • 少于n个字节的会自动在尾部加 0x00 字节
  • varbinary(n):
    • 存储二进制变长字符串
    • 少于n个字节的不会去填补 0x00 字节

• text

  • 存储大对象变长字符串
  • 关键词：tinytext(1 byte), text(2 bytes), mediumtext(3 bytes), longtext(4 bytes)

• blob

  • 存储二进制大对象变长字符串
  • 关键词：tinyblob(1 byte), blob(2 bytes), mediumenblob(3 bytes), longblob(4 bytes)

• enum

  • 单选枚举，最多可包含65535个不同的元素

• set

  • 多选枚举，最多可包含64个元素

（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

3、非常用数据类型

• 布尔类型

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

• 空间数据类型
  • geometry
  • point
  • linestring
  • polygon
• JSON数据类型
  • 支持原生JSON数据类型，可以更有效地存储和管理 JSON 文档

4、数据类型案例介绍

三、系统函数

1、系统函数

• 系统函数是对一些业务逻辑的封装，以完成特定的功能。系统函数可以有参数，也可以没有参数。系统函数执行完成后会返回执行结果。
• 系统函数的分类如下：
  • 数值计算函数
  • 字符处理函数
  • 时间日期函数
  • 类型转换函数
  • 系统信息函数

2、数值计算函数

• abs(X), cos(X), sin(X), asin(X); 返回表达式的绝对值，余弦值，正弦值，反余弦值和反正弦值。

  • abs(X)的返回值类型与参数 X 数据类型相同
  • abs(X)和acos函数说明：入参X是可转成数值类型的表达式，取值范围为[-1，1]。

  语法	功能	示例
  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
  truncate(X, D)	按指定的格式截取输入的数值数据。X是待截取的数据，D截取精度	truncate(15.79,1) ->15.7;truncate(15.79,-1) -> 10
  floor(X)	求小于或等于表达式X的值的最近的整数	floor(12.8) -> 12
  pi()	返回结果为Π的值，默认有效数字为7位	pi() ->3.141593
  mod(X, Y)	求模运算	mod(29.3) -> 2

3、字符处理函数

• concat(str[,…]),concat_ws(separator, str1, str2, …)：拼接一个或多个字符串。第一个函数无分割符，第二个函数可以指定分隔符连接
• hex(str)：返回十六进制值的字符串表示形式
• insert(str, pos, len, newstr), replace(str, src, dest)：字符串插入和字符串替换函数
• instr(str1, str2)：字符串查找函数，返回要查找的字符串首次在源字符串中出现的位置

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

4、时间日期函数

• date_format(date, format)：格式化日期函数，根据format转化为需要的格式
• extract(field from datetime)：从指定的日期(datetime)中提取指定的时间字段(filed)，按指定的格式截取输入的日期数据

5、类型转换函数

• if(cond, exp1,exp2)，ifnull(exp1, exp2)，nullif(exp1, exp2)：条件判断函数。
• nullifh函数中，expr1和expr2应该为相同数据类型，或能隐式转换成相同数据类型，否则校验报错。
• cast(value as type)/conver(value, type)：类型转换函数
• Type取值：binary, char(), date, time, datetime,decimal, signed,unsigned

6、系统信息函数

• 系统信息函数用来查询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的字符排列方式

四、操作符

1、操作符

• 操作符可对一个或多个操作数进行处理，位置上可能处于操作数之前，之后，或者两个操作数之间
• 常见操作符类型（从使用场景划分）
  • 逻辑操作符
  • 比较操作符
  • 算数操作符
  • 测试操作符
  • 通配符
  • 其他操作符

2、逻辑操作符

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

3、比较操作符

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

4、算术操作符

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

5、测试操作符

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

6、通配符

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

7、其他操作符

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