MySQL数据库详细教程

1.SQL分类

1.1 DDL（Data Definition Languages）语句

​ 数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。

1.2 DML（Data Manipulation Language）语句

​ 数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括 insert、delete、update和select等。

1.3 DCL（Data Control Language）语句

​ 数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

2.数据库的三大范式

1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

3.数据类型

3.1数值类型

​ MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（ INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION），并在此基础上做了扩展。扩展后增加了TINYINT、MEDIUMINT和BIGINT这3种长度不同的整型，并增加了BIT类型，用来存放位数据。

1. 整数类型：按照取值范围和存储方式不同，分为tinyint、smallint、mediumint、int和bigint这 5个类型。
2. 小数类型：浮点数和定点数。
   • 浮点数：float（单精度）和double（双精度）
   • 定点数：decimal。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
3. BIT（位）类型：用于存放位字段值，可以用来存放多位二进制数。

3.2日期时间类型

1. DATE:用来表示年月日
2. DATETIME:用来表示年月日时分秒
3. TIME:用来表示时分秒
4. TIMESTAMP:用于经常插入或者更新日期为当前系统时间
5. YEAR:用来表示年份

3.3字符串类型

3.3.1CHAR和VARCHAR类型

​ CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：

• CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值；
• VARCHAR列中的值为可变长字符串，长度可以指定为0～65535之间的值。
• 在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

3.3.2BINARY和VARBINARY类型

​ BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

3.3.3ENUM类型

​ ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～255个成员的枚举需要1个字节存储；对于255～65535个成员，需要2个字节存储。最多允许有65535个成员。ENUM类型是忽略大小写的.

3.3.4SET类型

​ SET和ENUM类型非常类似，也是一个字符串对象，里面可以包含0～64个成员。根据成员的不同，存储上也有所不同。

• 1～8成员的集合，占1个字节。
• 9～16成员的集合，占2个字节。
• 17～24成员的集合，占3个字节。
• 25～32成员的集合，占4个字节。
• 33～64成员的集合，占8个字节。

3.4选择合适的数据类型

3.4.1CHAR与VARCHAR

​ CHAR和VARCHAR类型类似，都用来存储字符串，但它们保存和检索的方式不同。

• **CHAR：**固定长度的字符类型，处理速度快。容易浪费存储空间，程序需要对行尾空格进行处理。
• **VACHAR；**可变长度的字符类型，处理速度慢。

​ 不同的存储引擎对CHAR和VARCHAR的使用原则有所不同。

• MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
• MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
• InnoDB存储引擎：建议使用VARCHAR类型。于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

3.4.2 TEXT与BLOB

• TEXT：保存字符数据，分为TEXT、MEDIUMTEXT、LONGTEXT三种不同的类型。
• **BLOB：**用来保存二进制数据，分为BLOB、MEDIUMBLOB、LONGBLOB三种不同的类型。

​ 提升性能：

1. 删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
2. 使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。
3. 在不必要的时候避免检索大型的BLOB或TEXT值。
4. 把BLOB或TEXT列分离到单独的表中。

3.4.3浮点数与定点数

• **浮点数：**用于表示含有小数部分的数值。如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。
• **定点数：**以字符串形式存放。。如果实际插入的数值精度大于实际定义的精度，在默认的SQLMode下MySQL 会进行警告，然后数据按照实际精度四舍五入后插入；在TRADITIONAL （传统模式），则系统会直接报错，导致数据无法插入。

3.4.4日期类型选择

​ 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。

​ 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

​ 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

4.运算符

4.1算术运算符

• “+”运算符用于获得一个或多个值的和。
• “-”运算符用于从一个值中减去另一个值。
• “*”运算符使数字相乘，得到两个或多个值的乘积。
• “/”运算符用一个值除以另一个值得到商。
• “%”运算符用一个值除以另外一个值得到余数。

4.2比较运算符

• “=”运算符，用于比较运算符两侧的操作数是否相等，如果两侧操作数相等，则返回值为1，否则为0。注意NULL不能用于“=”比较。
• “<>”运算符，和“=”相反，如果两侧操作数不等，则值为 1，否则为 0。NULL 不能用于“<>”比较。
• “<=>”运算符，和“=”类似，在操作数相等时值为 1，不同之处在于即使操作的值为NULL也可以正确比较。
• “<”运算符，当左侧操作数小于右侧操作数时，其返回值为1，否则其值为0。
• “<=”运算符，当左侧操作数小于等于右侧操作数时，其返回值为1，否则返回值为0。
• “>”运算符，当左侧操作数大于右侧操作数时，其返回值为1，否则返回值为0。
• “>=”运算符，当左侧操作数大于等于右侧操作数时，其返回值为1，否则返回值为0。
• “BETWEEN”运算符的使用格式为“a BETWEEN min AND max”，当 a大于等于min并且小于等于max，则返回值为1，否则返回0；当操作数a、min、max类型相同时，此表达式等价于（a>=min and a<=max），当操作数类型不同时，比较时会遵循类型转换原则进行转换后，再进行比较运算
• “IN”运算符的使用格式为“a IN (value1,value2,…)”，当 a的值存在于列表中时，则整个比较表达式返回的值为1，否则返回0。
• “IS NULL”运算符的使用格式为“a IS NULL”，当 a的值为NULL，则返回值为1，否则返回0。
• “IS NOT NULL”运算符的使用格式为“a IS NOT NULL”。和“IS NULL”相反，当 a的值不为NULL，则返回值为1，否则返回0。
• “LIKE”运算符的使用格式为“a LIKE %123%”，当 a中含有字符串“123”时，则返回值为1，否则返回0。
• “REGEXP”运算符的使用格式为“str REGEXP str_pat”，当 str字符串中含有str_pat相匹配的字符串时，则返回值为1，否则返回0。

4.3逻辑运算符（布尔运算符）

• “NOT”或“！”表示逻辑非。返回和操作数相反的结果：当操作数为0（假），则返回值为 1，否则值为 0。但是有一点除外，那就是NOT NULL的返回值为NULL.
• “AND”或“&&”表示逻辑与运算。当所有操作数均为非零值并且不为NULL时，计算所得结果为1，当一个或多个操作数为0时，所得结果为0，操作数中有任何一个为NULL则返回值为NULL.
• “OR”或“||”表示逻辑或运算。当两个操作数均为非NULL值时，如有任意一个操作数为非零值，则结果为1，否则结果为0。当有一个操作数为NULL时，如另一个操作数为非零值，则结果为1，否则结果为NULL。假如两个操作数均为NULL，则所得结果为NULL.
• “XOR”表示逻辑异或。当任意一个操作数为NULL时，返回值为NULL。对于非NULL的操作数，如果两个的逻辑真假值相异，则返回结果1，否则返回0.

4.4位运算符

​ 位运算是将给定的操作数转化为二进制后，对各个操作数每一位都进行指定的逻辑运算，得到的二进制结果转换为十进制数后就是位运算的结果。

• “位与”对多个操作数的二进制位做逻辑与操作.
• “位或”对多个操作数的二进制位做逻辑或操作.
• “位异或”对操作数的二进制位做异或操作.
• “位取反”对操作数的二进制位做NOT操.
• “位右移”对左操作数向右移动右操作数指定的位数.
• “位左移”对左操作数向左移动右操作数指定的位数.

4.5运算符优先级

5.常用函数

5.1字符串函数

• **INSERT(str,x,y,instr)函数：**将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。
• **LOWER(str)和UPPER(str)函数：**把字符串转换成小写或大写。
• **LEFT(str,x)和RIGHT(str,x)函数：**分别返回字符串最左边的x个字符和最右边的x个字符。如果第二个参数是NULL，那么将不返回任何字符串。
• **LPAD(str,n,pad)和RPAD(str,n,pad)函数：**用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度。
• **LTRIM(str)和RTRIM(str)函数：**去掉字符串str左侧和右侧空格。
• **REPEAT(str,x)函数：**返回str重复x次的结果。
• **REPLACE(str,a,b)函数：**用字符串b替换字符串str中所有出现的字符串a。
• **STRCMP(s1,s2)函数：**比较字符串s1和s2的ASCII码值的大小。
• **TRIM(str)函数：**去掉目标字符串的开头和结尾的空格。
• **SUBSTRING(str,x,y)函数：**返回从字符串str中的第x位置起y个字符长度的字串。

5.2数值函数

• **ABS(x)函数：**返回x的绝对值。
• **CEIL(x)函数：**返回大于x的最小整数。
• **FLOOR(x)函数：**返回小于x的最大整数，和CEIL的用法刚好相反。
• **MOD(x,y)函数：**返回x/y的模。
• **RAND()函数：**返回0～1内的随机值。
• **ROUND(x,y)函数：**返回参数x的四舍五入的有y位小数的值。
• **TRUNCATE(x,y)函数：**返回数字x截断为y位小数的结果。

5.3日期时间函数

• **CURDATE()函数：**返回当前日期，只包含年月日。
• **CURTIME()函数：**返回当前时间，只包含时分秒。
• **NOW()函数：**返回当前的日期和时间，年月日时分秒全都包含。
• **UNIX_TIMESTAMP(date)函数：**返回日期date的UNIX时间戳。
• **FROM_UNIXTIME(unixtime)函数：返回 UNIXTIME 时间戳的日期值，和UNIX_TIMESTAMP(date)**互为逆操作。
• **WEEK(DATE)和 YEAR(DATE)函数：**前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。
• **HOUR(time)和MINUTE(time)函数：**前者返回所给时间的小时，后者返回所给时间的分钟。
• **MONTHNAME(date)函数：**返回date的英文月份名称。
• **DATE_FORMAT(date,fmt)函数：**按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期.
  

5.4流程函数

5.5其他常用函数

• **DATABASE()函数：**返回当前数据库名。
• **VERSION()函数：**返回当前数据库版本。
• **USER()函数：**返回当前登录用户名。
• **INET_ATON(IP)函数：**返回IP地址的网络字节序表示。
• **INET_NTOA(num)函数：**返回网络字节序代表的IP地址。
• **PASSWORD(str)函数：**返回字符串str的加密版本，一个41位长的字符串。

6.字符集

6.1概述

​ 字符集就是一套文字符号及其编码、比较规则的集合。

​ 常用字符集比较：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U5bSygCw-1675922880626)(D:\image\笔记\数据库\图片\MySQL\常用字符集比较.jpg)]

6.2选择合适的字符集

1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集。对MySQL来说，目前就是UTF-8。
2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是GBK文字，如果选择GB 2312-80为数据库字符集，就很可能出现某些文字无法正确导入的问题。
3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK。
4. 如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
5. 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

6.3 MySQL支持的字符集

​ MySQL的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。其中字符集用来定义MySQL存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系，MySQL支持30多种字符集的70多种校对规则。

6.3.1MySQL字符集的设置

​ MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

1. ​ 服务器字符集和校对规则
   • 可以在my.cnf中设置
   • 在启动选项中指定
   • 在编译时指定
2. ​ 数据库字符集和校对规则
   • 如果指定了字符集和校对规则，则使用指定的字符集和校对规则
   • 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
   • 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
   • 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则
3. 表字符集和校对规则
   • 如果指定了字符集和校对规则，使用指定的字符集和校对规则
   • 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则
   • 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
   • 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则
4. 列字符集和校对规则
   • 列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。
5. 连接字符集和校对规则
   • 对于客户端和服务器的交互操作，MySQL提供了3个不同的参数：**character_set_client、character_set_connection和character_set_results，**分别代表客户端、连接和返回结果的字符集。通常情况下，这3个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出。D5(str)函数：**返回字符串str的MD5值，常用来对应用中的数据进行加密。