[华为云在线课程][SQL语法入门][学习笔记]

举报
John2021 发表于 2022/08/03 05:54:25 2022/08/03
【摘要】 1.SQL语句概述 1.1.SQL语句介绍SQL(Structured Query Language,结构化查询语句)是一种特定目的编程语言,用于管理关系型数据库管理系统。SQL基于关系代数和元组相关演算,包括一个数据定义语言和数据操作语言。SQL的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。 1.2.SQL语句分类DDL(Data Definition L...

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
  1. 浮点类型

    • float
      • 单精度浮点数,8位精度
      • 占用4字节
    • double
      • 双精度浮点数,16位精度
      • 占用8字节
  2. 定点类型

    • 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或者别名。
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200