数据库技术基础实践 3

第2章关系数据库基础知识2.1关系代数2.2数据类型2.3运算符2.4系统函数本章内容2.1关系代数2.1关系代数关系代数是一种抽象的查询语言。关系代数分为传统的集合运算和专门的关系运算。集合运算包含并、差、交、广义笛卡尔积运算。集合运算的运算符包括：∪（并）、-（差）、∩(交)、×（笛卡尔积）。关系运算包括投影、选择、连接和除运算。关系运算包括：∏（投影），σ（选择）、⋈（连接）、÷（除）。2.1.1集合运算并运算差运算交运算笛卡尔积运算1.并运算有两个关系R和S，将两个关系中的元组置于一个关系中，消除重复元组组成的新关系，就是R和S的并，新关系中包含R、S中所有不同元组，记做：R∪S。2.差运算有两个关系R和S，将在R中出现且在S中不出现的元组组成一个新关系，就是R和S的差，新关系中包含所有属于R但不属于S的元组，记作：R-S。3.交运算有两个关系R和S，将在R中出现且在S中出现的元组组成一个新关系，就是R和S的交，新关系由属于R也属于S的元组组成，记作：R∩S。4.笛卡尔积运算有含m个元组的关系R和含有n个元组的S两个关系，R中的每个元组与S中的每个元组连接组成一个新关系，就是R和S的笛卡尔积，记作：R×S。2.1.2关系运算选择运算投影运算连接运算除运算1.选择运算选择是根据给定的条件，选择关系R中若干元组组成新关系，是对关系的元组进行筛选，记作：其中F是选择条件，是一个逻辑表达式，它由逻辑运算符（∧、∨、¬）和比较运算符（>、>=、<、<=、=和<>）组成。【例2-1】查询关系User中degree是本科的所有记录。2.投影运算投影是选择关系R中的若干属性组成的新关系，并去掉重复的元组，是对关系的属性进行筛选，记作其中，A为关系R的属性列表，各属性间用逗号分隔。【例2-2】求User关系上name和gender两个属性上的投影。3.连接运算连接即θ连接，是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。记作：其中，A和B分别为R和S上列数相等且可比的属性组，θ是比较运算符。连接运算有：条件连接：从两个关系的笛卡尔积中选取属性间满足一定条件的元组。相等连接：从关系R与S的笛卡尔积中选取满足等值条件的元组。自然连接：也称等值连接，从两个关系的笛卡尔积中，选取公共属性满足等值条件的元组，但新关系不包含重复的属性。外连接：两个关系R和S在做自然连接时，选择两个关系在公共属性上值相等的元组构成新的关系。如果，关系R中某些元组在S中不存在公共属性上值相等的元组，从而使得R中的这些元组在连接操作时被舍弃，同样S中某些元组也可能被舍弃，这些被舍弃的元组称为悬浮元组。把悬浮元组保留在结果关系中，并将其他属性填上空值（NULL），就是外连接。3.连接运算4.除运算R与S的除运算得到一个新关系P(X)，P是R中满足下列条件的元组在X属性列上的投影：元组在X上分量值x的象集Yx包含S在Y上的投影的集合。记作：其中Yx为x在R中的象集，2.2数据类型2.2数据类型2.2.1数值类型2.2.2文本类型2.2.3时间日期类型2.2.4其他数据类型2.2.1数值类型整数型数据浮点类型数据定点类型数据1.整数型数据类型名称范围（signed）范围（unsigned）存储空间说明INT(INTEGER)【-2147483648，2147483647】【0，4294967295】4字节普通大小的整数SMALLINT【-32768，32767】【0，65535】2字节小的整数TINYINT【-128，127】【0，255】1字节很小的整数MEDIUMINT【-8388608，8388607】【0，16777215】3字节中等大小的整数BIGINT【-263，263-1】【0，264-1】8字节大整数2.浮点类型数据类型名称负数的取值范围非负数取值范围存储空间FLOAT【-4.402823466E+38，-1.175494351E-38】【0和1.175494351E-38，4.402823466E+38】4字节DOUBLE【-1.7976931348623157E+308，-2.2250738585072014E-308】【0和2.2250738585072014E-308，1.7976931348623157E+308】8字节3.定点类型数据定点类型占用4-24个字节，实际长度由其精度决定，取值范围-1.0E+128到1.0E+128，可表示为：DECIMAL（p，s）。其中p是精度，表示总共的位数，p的取值范围是1-65，若不指定p值，p默认值为10，表示不对小数点后面的数值做限制；s表示小数的位数，s的取值范围是0-30，若不指定s的值或者s=0，表示定点类型没有小数部分，s≤p。2.2.2文本类型定长字符串数据变长字符串数据文本类型数据枚举类型数据SET类型数据二进制形式文本数据1.定长字符串数据CHAR(n)用于存储固定长度字符串，n表示字符串的长度，n的取值范围是0-255。例：CHAR(9)定义了一个固定长度的字符串，数据的字符个数最大为9。当检索到CHAR值时，尾部的空格将被删除。2.变长字符串数据VARCHAR(n)类型用于存储变长字符串n表示变长字符串长度，n取值范围是0-65535，若输入字符串的长度小于n，不需要利用空格进行补齐。例：VARCHAR(40)定义了一个最大长度为40的字符串，如果输入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束符，将占用11个字节。VARCHAR在值保存和检索时，尾部空格仍保留。3.文本类型数据类型名称存储空间最大字符数说明TINYTEXT1字节28-1（255）可以存储的最大字符是255，长度不一致，不需要排序，如：博文摘录，文章摘要等TEXT2字节216-1（65,535）最多可容纳64KB文本数据MEDIUMTEXT3字节224-1（16,777,215）最多可容纳16MB的文本数据,如书籍文本，白皮书等LONGTEXT4字节232-1（4,294,967,295）可以存储高达4GB的文本数据4.枚举类型数据ENUM类型表示单选枚举，是一个字符串对象，其值是在创建表定义列时枚举确定的一列值。。ENUM值在内部用整数表示，每个枚举值均有一个索引值，列表值所允许的成员值从1开设编号，最多可以枚举65535个元素。5.SET类型数据SET表示多选枚举，是一个字符串对象，可以有零个或者多个值，最多可包含64个元素。SET列值为表创建时规定的一列值，可以从定义的列值中选择多个字符的联合。6.二进制形式文本数据类型名称数据长度说明BIT(M)M字节位字段类型BINARY(M)M字节固定长度二进制字符串VARBINARY(M)M+1字节可变长度二进制字符串TINYBLOB(M)最大长度位255（28-1）字节非常小的BLOBBLOB(M)最大长度位65535（28-1）字节小BLOBMEDIUMBLOB(M)最大长度位16777215（224-1）字节中等大小的BLOBLONGBLOB(M)最大长度位4294967295（232-1）字节非常大的BLOB2.2.3时间日期类型类型名称占用空间取值范围格式YEAR1字节1901-2155YYYY/YYTIME3字节-838：59：59∽838：59：59HH:MM:SSDATE3字节1000-01-01∽9999-12-31YYYY-MM-DDDATETIME8字节1000-01-0100:00:00∽9999-12-3123:59:59YYYY-MM-DDHH-MM-SSTIMESTAMP4字节1970-01-0100:00:00∽2037-12-3123:59:59YYYY-MM-DDHH-MM-SS2.2.4其他数据类型布尔类型（BOOLEAN）空间数据类型，包括几何体（GEOMETRY）、点(PIONT)、线(LINESTRING)、多边形(POLYGON)等。JSON（JavascriptObjectNotation）数据类型支持原生JSON数据类型，可以更有效存储和管理JSON文档。2.3运算符2.3运算符2.3.1算术运算符2.3.2比较运算符2.3.3逻辑运算符2.3.4测试运算符2.3.5其他运算符2.3.1算术运算符运算符说明示例结果+加法运算SELECT2+46-减法运算SELECT6-51×乘法运算SELECT2×36/除法运算，返回商SELECT9/33%，MOD取余运算，返回余数SELECT9%412.3.2比较运算符运算符说明示例结果=等于SELECT4=50<=>安全的等于SELECT8<=>10<>或者！=不等于SELECT3<>11<=小于等于SELECT3<=61<小于SELECT8<20>大于SELECT6>51>=大于或等于SELECT9>=612.3.3逻辑运算符运算符说明示例结果NOT或者！逻辑非SELECTNOT10AND或者&&逻辑与SELECT4>2AND2<10OR或者逻辑或SELECT1OR01XOR逻辑异或SELECT1XOR3>102.3.4测试运算符运算符说明IN元素在指定的集合中判断表达式的值是否在给出的列表中，如果是，返回值为TRUE，否则返回值为FALSENOTIN元素不在指定的集合中判断表达式值是否不存在于给出的列表中，如果不存在，返回值为TRUE，否则返回值为FALSEEXISTS存在符合条件的元素判断是否存在结果，既判断查询子句是否有记录，如果有一条或多条记录存在返回值TRUE，否则返回值为FALSENOTEXISTS不存在符合条件的元素判断查询子句是否有返回记录，如果子查询没有记录时返回值为TRUE，否则返回值为FALSEBETWEENAND判断一个值是否在两个值之间aBETWEENxANDy等效于a>=xANDa<=yNOTBETWEENAND判断一个值是否在两个值之间aNOTBETWEENxANDy等效于a<xORa>yISNULL判断一个值是否为空字段等于NULL时返回值为TRUE，否则返回值为FLASEISNOTNULL判断一个值是否为空字段不等于NULL时返回值为TRUE，否则返回值为FLASELIKE与表达式匹配，仅支持字符型类型与表达式匹配时返回值为TRUE,否则返回值为FALSENOTLIKE与表达式不匹配，仅支持字符型类型与表达式不匹配时返回值为TRUE,否则返回值为FALSEREGEXP字符串与正则表达式相匹配，仅支持字符串类型字符串与正则表达式相匹配时返回值为TRUE，否则返回值为FLASEREGEXP_LIKE字符串与正则表达式相匹配，支持字符型类型和NUMBER类型字符串与正则表达式相匹配时返回值为TRUE，否则返回值为FLASEANY将值与子查询返回一组进行比较，ANY运算符必须结合比较运算符使用子查询中有一个值满足条件时返回值既为TURE，否则返回值为FLASE2.3.5其他运算符通配符符说明%表示任意数量的字符，包括无字符，用于LIKE和NOTLIKE语句中_下划线，表示确切的一个未知字符，用于LIKE和NOTLIKE语句中运算符说明单引号（‘）表示字符串类型，如果在字符串文本中含有单引号，则单引号需成对出现双引号（“）表示表、字段、索引等对象名或别名，对大小写敏感，支持关键字作为名字或别名2.4系统函数2.4系统函数2.4.1数学函数2.4.2字符型串函数2.4.3时间日期函数2.4.4类型转换函数2.4.5系统信息函数2.4.6条件判断函数2.4.7聚合函数2.4.8加密函数2.4.9格式化函数2.4.10锁函数2.4.1数学函数函数名称说明ABS功能：求绝对值示例：SELECTABS(-20);返回值：20SQRT功能：求二次方根示例：SELECTSQRT(400);返回值：20MOD功能：求余数示例：SELECTMOD(20,3);返回值：22.4.2字符串函数函数名称说明LENGTH格式：LENGTH(string)功能：计算字符串长度函数示例：SELECTLENGTH(‘MySQL’);返回值：5CONCAT格式：CONCAT(string1，string2)功能：合并字符串函数示例：SELECTCONCAT(‘My’,‘SQL’);返回值：MySQLTRIM格式：TRIM(string)功能：去掉字符串重点空格示例：SELECTTRIM(‘MySQL’);返回值：MySQLLOWER格式：LOWER(string)功能：将字符串中的字母转换为小写示例：SELECTLOWER(MySQL);返回值：mysql2.4.3时间日期函数函数名称说明CURDATE和CURRENT_DATE功能：两个函数功能相同，返回当前系统日期示例：SELECTCURDATE();返回值：2023-10-20CURTIME和CURRENT_TIME功能：两个函数功能相同，返回当前系统时间示例：SELECTCURTIME();返回值：17:35:00NOW和SYSDATE功能：两个函数功能相同，返回当前系统日期和时间示例：SELECTNOW();返回值：2023-10-2017:35:00UNIX_TIMESTAMP功能：获取UNIX时间戳函数，返回从‘1970-01-0100:00:00’到当前时间的秒数差示例：SELECTUNIX_TIMESTAMP();返回值：16979385002.4.4类型转换函数函数名称说明ASCII(string)功能：返回string首个字符对应的ASCII值示例：SELECTASCII(‘hello’)；返回值：104CHAR(n)功能：返回ASCII值为n的字符，n的取值范围【0，127】示例：SELECTCHAR(67)返回值：CCAST(valueastype)功能：将列名或数值转换为指定的数据类型示例：SELECTCAST(‘10’asint);返回值：10CONERT(value,type)功能：将列名或数值转换为指定的数据类型示例：SELECTCAST(‘10’asint);返回值：102.4.5系统信息函数函数名称说明USER()SESSION_USER()SYSTEM_USER()CURRENT_USER()功能：返回当前用户示例：SELECTUSER()；SELECTSESSION_USER()；SELECTSYSTEM_USER()；SELECTCURRENT_USER()；DATABASE()功能：返回当前数据库名示例：SELECTDATABASE();VERSION()功能：返回数据库版本号示例：SELECTVERSION();2.4.6条件判断函数函数名称说明IF(condition,p1,p2)功能：判断，流程控制,condition为真，返回p1,否则返回p2。示例：SELECTIF(1>0,1,0)；返回值：1IFNULL(p1,p2)功能：判断是否为空，如果p1不为NULL，则返回p1，否则返回p2。示例：SELECTIFNULL(1,2)返回值：1NULLIF(p1,p2)功能：如果p1等于p2，则返回NULL，否则返回p1。示例：SELECTNULLIF(1,2);返回值：12.4.7聚合函数函数名称说明MAX功能：查询指定列的最大值示例：SELECTMAX(age)FORMstudent;MIN功能：查询指定列的最小值示例：SELECTMIN(age)FORMstudent;COUNT功能：统计查询结果的行数示例：SELECTCOUNT(ID)FORMstudent;SUM功能：求和，返回指定列的总和示例：SELECTSUM(Score)FORMstudent;AVG功能：求平均值，返回指定列数据的平均值示例：SELECTAVG(Score)FORMstudent;2.4.8加密函数函数说明COMPRESS(str)UNCOMPRESS(str)功能：COMPRESS函数对字符串str进行压缩，函数返回的值是二进制字符串。UNCOMPRESS函数用于解压缩字符串并将其恢复到原始状态，用于使用COMPRESSED()函数压缩的字符串。示例：SELECTCOMPRESS(‘MySQL’)；ENCODE(str,pswd_stringt)DECODE(crypt_strting,pswd_strting)功能：ENCODE函数使用加密密码pswd_strting来加密字符串str，函数返回一个二进制字符串。DECODE与ENCODE是一对，需要同样的密码才能够解密。示例：SELECTENCODE(‘123’,‘xxoo’)DES_ENCRYPT(str,[key_numberkey_string])DES_DECRYPT(str,[key_numberkey_string])功能：DES_ENCRYPT函数使用DES(数据加密标准)算法对字符串进行加密，返回值是加密的字符串或NULL。Str指定要加密的字符串。key_number是从DESkey文件中指定一个介于0到9之间的数字。key_string是用于加密string字符串的字符串。DES_DECRYPT函数使用DES(数据加密标准)算法解密加密的字符串,返回值是解密的字符串或NULL。示例：SELECTDES_ENCRYPT(‘MySQL’,5);2.4.9格式化函数函数名称说明DATE_FORMAT(date,fmt)功能：依照字符串fmt格式化日期date值示例：SELECTDATE_FORMAT(‘2023-10-2222:23:00’,‘%W%M%Y’);返回