《数据库原理及MySQL应用（微课版）》课件 第5章标准的结构化查询语言SQL

第5章

标准的结构化查询语言SQL>>SQL语言及概述结构化查询语言，简称SQL语言，是用于管理和操作关系型数据库的标准化语言。SQL语言是一种声明性的语言，不需要指定具体的操作步骤，而只需描述所需的数据结果。SQL不是某个特定数据库供应商专有的语言，所有关系数据库都支持SQL。SQL简单易学，类似英语的语法。它的语句全都由具有很强描述性的英语单词所组成，而且这些单词的数目不多。灵活使用其语言元素，可以进行非常复杂和高级的数据操作。SQL优点SELECT语句从数据库中提取数据，可以指定要检索的列、表以及筛选条件INSERT语句向数据库中插入新的数据行。UPDATE语句更新数据库中已有的数据行。DELETE语句删除数据库中的数据行。SQL的基本语句和功能使用WHERE子句来对数据进行筛选，只返回符合特定条件的数据。使用ORDERBY子句对结果集进行排序，按照指定的列和顺序排列。SQL的基本语句和功能数据过滤数据排序SQL的基本语句和功能数据聚合和分组使用GROUPBY子句，我们可以将数据按照指定的列进行分组，并对每个组应用聚合函数。例：SUM、AVG、COUNT等进行求和、计算平均值、计数等操作。这使得我们能够轻松地对大量数据进行汇总和统计分析。支持多表查询，通过使用JOIN操作，我们可以在多个相关联的表之间建立连接，从而实现跨表的查询和操作。这为处理复杂的数据库结构和关系提供了便利，使得数据库的设计更加灵活和可扩展。SQL的基本语句和功能其他特性：子查询、视图、事务处理和安全性控制。管理和维护数据库，并进行高效的数据操作和分析。SQL的基本语句和功能使用SQL时需要注意一些重要的事项正确的数据定义和类型选择非常重要合理的索引设计可以显著提高查询性能。注意数据安全和隐私保护，避免未经授权的访问和恶意攻击。>>MySQL扩展增加的语言要素这部分不是SQL标准所包含的内容，而是为了用户编程的方便增加的语言元素。这些语言元素包括常量、变量、运算符、函数等。每个SQL语句都以分号结束，并且SQL处理器忽略空格、制表符和回车符。常量：是指在程序运行过程中值不变的量，也成为字面值或标量值。常量的使用格式取决于值的数据类型。（1）字符串常量：用单引号或双引号括起来的字符序列，分为ASCII字符串（一个字节）和Unicode字符串常量（两个字节）ASCII字符串：’hello’,’Howareyou!’Unicode字符串与ASCII字符串相似，但它前面有一个N标识符,N必须为大写，只能用单引号括起来：N’hello’序列含义\0一个ASCII0（nul）字符，没有值\n一个换行符\t一个定位符\r一个回车符（windows中使用\r\n作新行标志）\b一个退格符\z一个ASCII26字符（CTRL+Z）字符串转义序列表序列含义\’一个单引号\’’一个双引号\\一个斜杠\%一个‘%’字符\_一个‘_’字符>>MySQL扩展增加的语言要素（2）数值常量：可分为整数常量和浮点数常量整数常量即不带小数点的十进制数，例如：123，754，-5612浮点数常量带小数点的数值常量，例如：4.781，-1.39，-0.5E2,3.56E-2（3）十六进制常量：字符串常量最前面有一个大写字母‘X’或小写字母‘x’,在引号内使用0-9和A-F，例如：X’41’表示十六进制A，也可使用0x表示十六进制，后续字符无需引号，例如：0x41，注意x一定是小写。十六进制值的默认类型为字符串，如果想要表达数字，需要使用CAST（…ASUNSIGNED）

运行：Select0x41,CAST(0x41asunsigned)（4）日期时间常量：用单引号将表示日期时间的字符串括起来。日期型常量包括年月日，数据类型为date，表示为‘1997-06-17’时间型常量包括时分秒微秒，数据类型为time，表示为’12：30：40.0012’>>MySQL扩展增加的语言要素MySQL还支持日期/时间型常量datetime和timestamp，两者区别：Datetime:表示年份在1000-9999Timestamp:表示年份在1970-2037，忽略微秒正确的日期时间格式：'2008-05-1214:28:24:00'注意：年月日中间的分隔符-可为‘\’、’@’、’%’特殊符号（5）布尔值：只包含两个可能的值（TRUE、FALSE），其中FALSE的数字值是‘0’，TRUE的数字值是’1’例：获取TRUE和FALSE的值。SELECTTRUE,FALSE;（6）NULL值：NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。>>MySQL扩展增加的语言要素变量：用于临时存放数据，变量中的数据可以随着程序的运行而变化。变量有名字和数据类型两个属性。在mysql中，变量分为用户变量和系统变量。用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。用户可以先在用户变量中保存值，然后在以后引用它，这样可以将值从一个语句传递到另一个语句。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。用户变量前添加一个符号‘@’，用于将其与列名区分；在一个用户变量被创建后，使用时变量名前面也必须加上符号@。系统变量前添加两个‘@’符号>>MySQL扩展增加的语言要素例:创建用户变量name并赋值为“王林”

。SET@name=‘王林’;set为定义变量，=为变量赋值例:创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。

SET@user1=1,@user2=2,@user3=3;定义用户变量时变量值可以是一个表达式。例:创建用户变量user4，它的值为user3的值加1。SET@user4=@user3+1;例:查询上例中创建的变量name的值。SELECT@name;>>MySQL扩展增加的语言要素2）系统变量MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL服务器启动时就被引入并初始化为默认值。例：获得现在使用的MySQL版本。

SELECT@@VERSION;>>MySQL扩展增加的语言要素运算符：五种算术运算符：+、-、*、/（除）、%（求模，求余数）例：7%2，8%4比较运算符：=、>、<、>=、<=、<>或!=例：如果user1=10，user2=20，输出user1<>user2和user1=user2的结果位运算符：&、|、~、>>、<<逻辑运算符：not或！、and或&&、or或||例：selectnot0,not1,not(0=1),not(10<11);Select(1=1)and(10<9),(‘a’=‘a’)and(‘c’<‘d’);Select(1=1)or(10<9),(‘a’=‘a’)or(‘c’<‘d’);表达式：是常量、变量、列名、复杂计算、运算符和函数的组合，一个表达式通常可以得到一个值，根据表达式的值的数据类型，表达式可分为字符型表达式、数值型表达式、日期表达式。教学内容1SQL语言及概述2函数3数据类型>>内置函数在SQL中，通常可直接调用内置函数来对数据库表进行相关操作。MySQL包含100多个函数。常用函数：聚合函数，数学函数，字符串函数，日期时间函数，其他函数聚合函数：COUNT()--记数函数，常用count（*）返回选择集合中的所有记录总数SUM()–求和函数，求出表中某个字段取值的平均值AVG()--平均值函数，求出某个字段取值的平均值MAX()--最大值函数，求出某个字段取值的最大值MIN()–最小值函数，求出某个字段取值的最小值>>内置函数数学函数：主要用于处理数字。ABS()–绝对值函数FLOOR()–用于返回小于或等于参数x的最大整数，向下取整CEILING()-向上取整RAND()–用于返回0-1之间的随机数ROUND()-四舍五入取整TRUNCATE(x,y)–用于返回x保留到小数点后y位的值，截取，无四舍五入SQRT(x)–求出参数x的平方根，开根号>>内置函数字符串函数：UPPER(s)、UCASE(s)–均用于将字符串s中的所有字母变成大写字母LOWER()-转小写字母LEFT(s,n)–用于返回字符串s的前n个字符，左边截取RIGHT(s,n)-右边截取SUBSTRING(s,n,len)–用于从字符串s的第n个位置开始获取长度为len的字符串>>内置函数日期和时间函数：CURDATE()、CURRENT_DATE()–均用于获取当前日期CURTIME()、CURRENT_TIME()–用于获取当前时间NOW()–用于当前日期和时间>>内置函数其他函数：IF(expr,v1,v2)–是一种条件判断函数，其表示的是如果表达式expr成立，则执行v1,否则执行v2IFNULL(v1,v2)–也是一种条件判断函数，其表示的是如果表达式v1不为空，则现实v1的值，否则显示v2的值VERSION()–是一种系统信息函数，用户获取数据库的版本号教学内容1SQL语言及概述2函数3数据类型>>数据类型数据类型：作为数据的一种属性，能够表示数据的信息以及存储方式的类型。不同数据类型的存储方式不同，因此数据库中字段的数据类型对于数据库的优化非常重要。不同的DBMS，数据类型有所不同。MySQL支持的数据类型：Helpdatatypes;--查看mysql支持的所有数据类型Mysql支持的数据类型主要分为三种：数值类型、日期和时间类型、字符串类型。从Mysql5.7开始，支持json类型。>>数值类型数值类型包括：整数、浮点数和定点数、BIT类型一、整数类型整数类型，顾名思义是用来存储整数的。MySQL支持的整数类型有SQL标准中的整数类型INTEGER和SMALLINT，并在此基础上拓展了新的整数类型，如TINYINT、MEDIUMINT、BIGINT。整数类型大小表数范围（有符号）表数范围（无符号）作用TINYINT1字节(-128，127)(0，255)小整数值SMALLINT2字节(-32768，32767)(0，65535)大整数值MEDIUMINT3字节(-8388608，8388607)(0，16777215)大整数值INT/INTEGER4字节(-2147483648，2147483647)(0，4294967295)大整数值BIGINT8字节(-9233372036854775808，9223372036854775807)(0，18446744073709551615)极大整数值>>数值类型二、浮点数和定点数类型如果想要在数据库中存储小数类型，则需要下面两种MySQL指出的数据类型：浮点数类型和定点数类型。浮点数类型在数据库中存放的是近似值，因此也称为近似值类型，而定点数类型在数据库中存放的是精确值。

浮点数类型包括FLOAT（单精度）和DOUBLE（双精度）两种。浮点数类型大小表数范围（有符号）表数范围（无符号）作用FLOAT4字节(-3.402823466E+38，-1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38)0，(1.175494351E-38，3.402823466E+38)单精度浮点数值DOUBLE8字节(-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.7976931348623157E+308)0，(2.2250738585072014E-308，1.7976931348623157E+308)双精度浮点数值(M,D),M是总个数，D是小数位，超出四舍五入。例：double(6,3)3.141516—3.142314.23—314.230314.4567—314.457>>数值类型二、浮点数和定点数类型

定点数类型在数据库中是以字符串形式存的，因此是精确值。定点数只有一种数据类型，即DECIMAL，该数据类型用于精度要求非常高的计算中，如涉及金钱操作的领域。浮点数类型大小表数范围作用DECIMAL(M,D)M+2最小最大取值范围与DOUBLE相同；指定M和D时，有效取值范围由M和D的大小决定精度较高的小数值(1)DECIMAL类型的M默认值为10，D默认值为0。如果在创建表时，定义某字段为DECIMAL类型而没有带任何参数，则等同于DECIMAL(10,0)，比如我们要存储的数据是1.23，则保存到数据库中的实际是1，而不是1.23。如果只带一个参数，则该参数为M值，D则取默认值0。(2)M的取值范围为1~65，取0时会被设为默认值10，超出范围则会报错。(3)D的取值范围为0~30，同时必须满足D<=M，否则会报错。>>数值类型三、BIT类型

MySQL5.0以前，BIT与TINYINT表示同一种数据类型。但是在MySQL5.0以及之后的版本中，BIT是一个完全不同的数据类型。我们可以使用使用BIT数据类型保存位字段值，即BIT可以方便的存储二进制数据。BIIT类型大小表数范围作用BIT(M)1~8字节BIT(1)~BIT(64)位字段值（1）M指位数，默认为1，bit（1）的取值范围为0和1，bit（4）取值范围为0-15，bit（64）取值范围为0-264-1；（2）bit存储二进制数据，形式：b’value’,value指一个由0和1组成的二进制数据。b‘111’——表示十进制的7，若制定类型为bit（6），存入数据库的数据实际为b‘000111’>>日期和时间类型MySQL提供了5种不同的日期时间类型：TIME、DATE、YEAR.、DATETIME和TIMESTAMP。每种日期与时间类型都有一个取值范围和一个“0”值。在非严格模式下，当存储的数据格式不合法时，系统会给出警告，并将0值插入到数据库中；当插入的数据格式合法，但是超出数据类型的范围时，该数据将被裁剪为范围最接近的端点（最大值或最小值）。但是在严格模式下，非法或合法但超出范围的数据是不允许存入数据库的，系统会提示错误。Now()、sysdate（）可获取系统当前日期。类型格式取值范围0值TIME'HH:MM:SS'('-838:59:59','838:59:59')'00:00:00'DATE'YYYY-MM-DD'('1000-01-01','9999-12-31')'0000-00-00'YEARYYYY(1901,2155),00000000DATETIME'YYYY-MM-DDHH:MM:SS'('1000-01-0100:00:00','9999-12-3123:59:59')'0000-00-0000:00:00'TIMESTAMP'YYYY-MM-DDHH:MM:SS'('1970-01-0100:00:01'UTC,'2038-01-1903:14:07'UTC)'0000-00-0000:00:00'>>日期和时间类型time类型格式：‘DHH:MM:SS’、‘HH:MM:SS’、

‘HHMMSS’、HHMMSSDate类型格式：‘YYYY-MM-DD’、‘YY-MM-DD’、‘YYYYMMDD’、YYYYMMDDYear类型格式：YYYY、’YYYY’Time类型举例：‘114:13:12.8’--‘38：13：13’Time（fsp），fsp为小数部分的位数，取值范围1-6.如：time（3），14：13：12.88889-14：13：889>>字符串类型字符串类型是在数据库中存储字符串的数据类型。MySQL中提供了多种字符串类型，分别为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。使用不同的字符串类型可以实现从简单的一个字符到巨大的文本块或二进制字符串数据的存储。字符串类型大小描述CHAR(M)0-255字节允许长度0~M个字符的定长字符串VARCHAR(M)0~65535字节允许长度0~M个字符的变长字符串BINARY(M)0~255字节允许长度0~M个字节的定长二进制字符串VARBINARY(M)0~65535字节允许长度0~M个字节的变长二进制字符串TINYBLOB0~255字节二进制形式的短文本数据（长度为不超过255个字符）TINYTEXT0~255字节短文本数据BLOB0~65535字节二进制形式的长文本数据（图片、音乐、视频、PDF等）TEXT0~65535字节长文本数据（文本格式，新闻内容）MEDIUMBLOB0~16777215字节二进制形式的中等长度文本数据MEDIUMTEXT0~16777215字节中等长度文本数据LOGNGBLOB0~4294967295字节二进制形式的极大文本数据LONGTEXT