MySQL 学习笔记第二期SQL 语言之库/表操作与数据类型本笔记承接第一期进入 SQL 语言核心部分。本期重点数据库与表的创建、修改、删除操作MySQL 数据类型详解字符集与排序规则以及约束属性的使用。所有代码经过整理与注释便于理解和实践。一、SQL 语言概述1.1 SQL 分类类别全称功能常用语句DDLData Definition Language定义数据结构库、表、索引等CREATE,DROP,ALTERDMLData Manipulation Language操作表中数据INSERT,DELETE,UPDATEDQLData Query Language查询表中数据SELECTDCLData Control Language权限控制GRANT,REVOKETCLTransaction Control Language事务控制BEGIN,COMMIT,ROLLBACK,SAVEPOINT1.2 SQL 语言规范SQL 语句不区分大小写建议关键字大写表名/库名在某些系统中区分大小写语句以分号;结尾可单行或多行书写关键词不能跨行或简写使用空格和缩进提高可读性注释方式sql-- 单行注释 # MySQL 特有的单行注释 /* 多行注释 */1.3 数据库对象与命名规则数据库组件数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。命名规则可包含字母、数字和#_$不能使用 MySQL 保留字二、字符集与排序规则2.1 查看支持的字符集sqlSHOW CHARSET;MySQL 8.0 默认字符集为utf8mb4早期为latin1utf8mb4支持完整的 Unicode包括 emoji推荐使用2.2 查看当前字符集变量sqlSHOW VARIABLES LIKE character%;示例输出默认 utf8mb4Variable_nameValuecharacter_set_clientutf8mb4character_set_connectionutf8mb4character_set_databaseutf8mb4character_set_resultsutf8mb4character_set_serverutf8mb42.3 修改默认字符集配置文件中ini# /etc/my.cnf.d/mysql-server.cnf [mysqld] character-set-serverutf8mb4 # /etc/my.cnf.d/client.cnf [client] default-character-setutf8mb4修改后需重启服务bashsystemctl restart mysqld2.4 查看排序规则sqlSHOW COLLATION; SHOW VARIABLES LIKE collation%;utf8mb4_0900_ai_ci是 MySQL 8.0 的默认排序规则不区分大小写utf8mb4_bin区分大小写按二进制值比较三、管理数据库DDL3.1 查看数据库列表sqlSHOW DATABASES;默认四个系统库information_schema,mysql,performance_schema,sys不可删除。3.2 创建数据库语法sqlCREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] CHARACTER SET [] charset_name | [DEFAULT] COLLATE [] collation_name | [DEFAULT] ENCRYPTION [] {Y | N}范例sql-- 基本创建 CREATE DATABASE testdb1; -- 如果已存在则忽略错误 CREATE DATABASE IF NOT EXISTS testdb1; -- 查看创建语句含默认字符集 SHOW CREATE DATABASE testdb1; -- 创建时指定字符集 CREATE DATABASE testdb2 DEFAULT CHARACTER SET latin1; -- 创建后修改字符集 ALTER DATABASE testdb2 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;原理创建数据库本质是在数据目录如/var/lib/mysql/下创建一个同名目录。3.3 修改数据库sqlALTER {DATABASE | SCHEMA} [db_name] alter_option ...范例sqlALTER DATABASE testdb2 READ ONLY 1; -- 设为只读 ALTER DATABASE testdb2 READ ONLY 0; -- 取消只读 ALTER DATABASE testdb2 ENCRYPTION Y; -- 启用加密3.4 删除数据库sqlDROP {DATABASE | SCHEMA} [IF EXISTS] db_name;范例sqlDROP DATABASE testdb2; DROP DATABASE IF EXISTS testdb2; -- 如果不存在不报错命令行等价操作bashmysqladmin create db1 mysqladmin drop testdb1 # 会提示确认加 -f 强制删除四、数据类型核心数据类型定义了字段可存储的数据种类、范围和格式目的是保证规范性、节省空间、提升效率。4.1 数值类型整数类型类型字节有符号范围无符号范围用途TINYINT1-128 ~ 1270 ~ 255小整数如性别 0/1SMALLINT2-32768 ~ 327670 ~ 65535较大整数MEDIUMINT3-8388608 ~ 83886070 ~ 16777215中等整数INT(INTEGER)4-2.1e9 ~ 2.1e90 ~ 4.29e9最常用年龄、IDBIGINT8-9.22e18 ~ 9.22e180 ~ 1.84e19极大整数手机号、身份证号浮点与定点数类型字节说明FLOAT4单精度约 7 位有效数字DOUBLE8双精度约 15 位有效数字DECIMAL(M,D)变长定点数精确用于金额、价格避免精度丢失。M 为总位数D 为小数位数示例DECIMAL(10,2)表示总共 10 位其中小数 2 位如 12345678.90。4.2 日期和时间类型类型字节范围格式用途DATE3‘1000-01-01’ ~ ‘9999-12-31’YYYY-MM-DD日期值出生日期TIME3‘-838:59:59’ ~ ‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901 ~ 2155YYYY年份DATETIME8‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’YYYY-MM-DD HH:MM:SS最常用注册时间、订单时间TIMESTAMP4‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTCYYYY-MM-DD HH:MM:SS时间戳自动更新范围较小注意TIMESTAMP 存储的是 UTC 值受时区影响DATETIME 不受时区影响。推荐使用 DATETIME。4.3 字符串类型类型最大长度字符/字节说明CHAR(n)0 ~ 255 字符定长字符串效率高存储固定长度信息如身份证号VARCHAR(n)0 ~ 65535 字符变长字符串节省空间推荐用于姓名、地址等TINYTEXT255 字节短文本TEXT65535 字节约 64KB长文本MEDIUMTEXT16,777,215 字节约 16MB中等长度文本LONGTEXT4,294,967,295 字节约 4GB极大文本BINARY/VARBINARY类似 CHAR/VARCHAR存储二进制字节串无字符集BLOB 系列TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB二进制数据图片、文件ENUM最多 65535 个值枚举从预设值中选择一个如性别 ‘M’,‘F’SET最多 64 个成员集合可选择多个值使用建议定长且长度稳定的字段用CHAR如 MD5 密码长度可变用VARCHAR大文本用TEXT系列金额用DECIMAL避免FLOAT/DOUBLE的精度问题4.4 列属性属性含义NOT NULL该列不能为空DEFAULT默认值PRIMARY KEY主键唯一且非空AUTO_INCREMENT自动递增通常用于 INT 主键UNSIGNED无符号非负CHARACTER SET name指定该列的字符集五、管理表DDL5.1 创建表基本语法sqlCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name ( column_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY], ... ) ENGINEengine_name DEFAULT CHARSETcharset_name;范例创建学生表sqlUSE db1; -- 先切换到目标数据库 CREATE TABLE student ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 无符号自增主键 name VARCHAR(20) NOT NULL, -- 变长字符串非空 age TINYINT UNSIGNED, -- 小整数非负 gender ENUM(M,F) DEFAULT M -- 枚举默认为 M ) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8mb4;查看表sqlSHOW TABLES; -- 查看当前库所有表 DESC student; -- 查看表结构字段 SHOW COLUMNS FROM student; -- 同上 SHOW CREATE TABLE student; -- 查看建表语句含所有细节插入测试数据sqlINSERT INTO student (name, age) VALUES (xiaoming, 20); INSERT INTO student (name, age, gender) VALUES (xiaohong, 18, F);查询数据sqlSELECT * FROM student;5.2 通过查询结果创建表复制数据sql-- 复制数据包含结构和数据 CREATE TABLE student2 SELECT name, age FROM student; -- 复制结构但不复制数据 CREATE TABLE student3 LIKE student;5.3 修改表ALTER TABLE修改表名sqlALTER TABLE student RENAME TO stu;添加字段sqlALTER TABLE stu ADD phone VARCHAR(11) AFTER name; -- 在 name 之后添加修改字段类型sqlALTER TABLE stu MODIFY phone INT;修改字段名和类型sqlALTER TABLE stu CHANGE COLUMN phone mobile CHAR(11);删除字段sqlALTER TABLE stu DROP COLUMN mobile;修改表字符集sqlALTER TABLE stu CHARACTER SET utf8;同时修改字段类型和字符集sqlALTER TABLE stu CHANGE name name CHAR(30) CHARACTER SET utf8;设置字段默认值sqlALTER TABLE stu ALTER COLUMN gender SET DEFAULT M;添加字段并设置默认值sqlALTER TABLE stu ADD is_del BOOL DEFAULT false; ALTER TABLE stu MODIFY is_del BOOL DEFAULT true; -- 修改默认值添加主键sqlALTER TABLE stu2 ADD PRIMARY KEY (id);删除主键sqlALTER TABLE stu2 DROP PRIMARY KEY;5.4 查看表状态sql-- 查看指定表状态 SHOW TABLE STATUS LIKE student\G -- 查看当前库所有表状态 SHOW TABLE STATUS FROM db1; -- 查看所有存储引擎 SHOW ENGINES;5.5 删除表sqlDROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...;范例sqlDROP TABLE student2, student3; DROP TABLE IF EXISTS student4;六、本期知识点归纳一览表类别知识点关键点/命令SQL 分类DDL / DML / DQL / DCL / TCL各司其职字符集utf8mb4推荐SHOW CHARSET,SHOW VARIABLES LIKE character%排序规则utf8mb4_0900_ai_ci默认不区分大小写SHOW COLLATION,SHOW VARIABLES LIKE collation%数据库操作创建、修改、删除CREATE DATABASE,ALTER DATABASE,DROP DATABASE数值类型INT, DECIMAL, TINYINT, BIGINT金额用 DECIMAL年龄用 INT UNSIGNED日期类型DATETIME推荐, DATE, TIMESTAMP注册时间用 DATETIME字符串类型VARCHAR变长, CHAR定长, TEXT, ENUM姓名用 VARCHAR固定码用 CHAR列属性NOT NULL, DEFAULT, AUTO_INCREMENT, PRIMARY KEY主键通常为自增 INT创建表CREATE TABLE ...需指定引擎InnoDB、字符集查看表SHOW TABLES,DESC,SHOW CREATE TABLE快速获取结构复制表CREATE TABLE ... SELECT含数据CREATE TABLE ... LIKE仅结构灵活复用修改表ALTER TABLE ADD/MODIFY/CHANGE/DROP/RENAME谨慎使用DROP COLUMN删除表DROP TABLE不可恢复慎用下一期预告SQL 语言之数据操作与单表查询DMLINSERT/UPDATE/DELETEDQLSELECT 详解、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT、聚合函数等。
20260604 MySQL 语言之库表操作与数据类型
MySQL 学习笔记第二期SQL 语言之库/表操作与数据类型本笔记承接第一期进入 SQL 语言核心部分。本期重点数据库与表的创建、修改、删除操作MySQL 数据类型详解字符集与排序规则以及约束属性的使用。所有代码经过整理与注释便于理解和实践。一、SQL 语言概述1.1 SQL 分类类别全称功能常用语句DDLData Definition Language定义数据结构库、表、索引等CREATE,DROP,ALTERDMLData Manipulation Language操作表中数据INSERT,DELETE,UPDATEDQLData Query Language查询表中数据SELECTDCLData Control Language权限控制GRANT,REVOKETCLTransaction Control Language事务控制BEGIN,COMMIT,ROLLBACK,SAVEPOINT1.2 SQL 语言规范SQL 语句不区分大小写建议关键字大写表名/库名在某些系统中区分大小写语句以分号;结尾可单行或多行书写关键词不能跨行或简写使用空格和缩进提高可读性注释方式sql-- 单行注释 # MySQL 特有的单行注释 /* 多行注释 */1.3 数据库对象与命名规则数据库组件数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。命名规则可包含字母、数字和#_$不能使用 MySQL 保留字二、字符集与排序规则2.1 查看支持的字符集sqlSHOW CHARSET;MySQL 8.0 默认字符集为utf8mb4早期为latin1utf8mb4支持完整的 Unicode包括 emoji推荐使用2.2 查看当前字符集变量sqlSHOW VARIABLES LIKE character%;示例输出默认 utf8mb4Variable_nameValuecharacter_set_clientutf8mb4character_set_connectionutf8mb4character_set_databaseutf8mb4character_set_resultsutf8mb4character_set_serverutf8mb42.3 修改默认字符集配置文件中ini# /etc/my.cnf.d/mysql-server.cnf [mysqld] character-set-serverutf8mb4 # /etc/my.cnf.d/client.cnf [client] default-character-setutf8mb4修改后需重启服务bashsystemctl restart mysqld2.4 查看排序规则sqlSHOW COLLATION; SHOW VARIABLES LIKE collation%;utf8mb4_0900_ai_ci是 MySQL 8.0 的默认排序规则不区分大小写utf8mb4_bin区分大小写按二进制值比较三、管理数据库DDL3.1 查看数据库列表sqlSHOW DATABASES;默认四个系统库information_schema,mysql,performance_schema,sys不可删除。3.2 创建数据库语法sqlCREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] CHARACTER SET [] charset_name | [DEFAULT] COLLATE [] collation_name | [DEFAULT] ENCRYPTION [] {Y | N}范例sql-- 基本创建 CREATE DATABASE testdb1; -- 如果已存在则忽略错误 CREATE DATABASE IF NOT EXISTS testdb1; -- 查看创建语句含默认字符集 SHOW CREATE DATABASE testdb1; -- 创建时指定字符集 CREATE DATABASE testdb2 DEFAULT CHARACTER SET latin1; -- 创建后修改字符集 ALTER DATABASE testdb2 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;原理创建数据库本质是在数据目录如/var/lib/mysql/下创建一个同名目录。3.3 修改数据库sqlALTER {DATABASE | SCHEMA} [db_name] alter_option ...范例sqlALTER DATABASE testdb2 READ ONLY 1; -- 设为只读 ALTER DATABASE testdb2 READ ONLY 0; -- 取消只读 ALTER DATABASE testdb2 ENCRYPTION Y; -- 启用加密3.4 删除数据库sqlDROP {DATABASE | SCHEMA} [IF EXISTS] db_name;范例sqlDROP DATABASE testdb2; DROP DATABASE IF EXISTS testdb2; -- 如果不存在不报错命令行等价操作bashmysqladmin create db1 mysqladmin drop testdb1 # 会提示确认加 -f 强制删除四、数据类型核心数据类型定义了字段可存储的数据种类、范围和格式目的是保证规范性、节省空间、提升效率。4.1 数值类型整数类型类型字节有符号范围无符号范围用途TINYINT1-128 ~ 1270 ~ 255小整数如性别 0/1SMALLINT2-32768 ~ 327670 ~ 65535较大整数MEDIUMINT3-8388608 ~ 83886070 ~ 16777215中等整数INT(INTEGER)4-2.1e9 ~ 2.1e90 ~ 4.29e9最常用年龄、IDBIGINT8-9.22e18 ~ 9.22e180 ~ 1.84e19极大整数手机号、身份证号浮点与定点数类型字节说明FLOAT4单精度约 7 位有效数字DOUBLE8双精度约 15 位有效数字DECIMAL(M,D)变长定点数精确用于金额、价格避免精度丢失。M 为总位数D 为小数位数示例DECIMAL(10,2)表示总共 10 位其中小数 2 位如 12345678.90。4.2 日期和时间类型类型字节范围格式用途DATE3‘1000-01-01’ ~ ‘9999-12-31’YYYY-MM-DD日期值出生日期TIME3‘-838:59:59’ ~ ‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901 ~ 2155YYYY年份DATETIME8‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’YYYY-MM-DD HH:MM:SS最常用注册时间、订单时间TIMESTAMP4‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTCYYYY-MM-DD HH:MM:SS时间戳自动更新范围较小注意TIMESTAMP 存储的是 UTC 值受时区影响DATETIME 不受时区影响。推荐使用 DATETIME。4.3 字符串类型类型最大长度字符/字节说明CHAR(n)0 ~ 255 字符定长字符串效率高存储固定长度信息如身份证号VARCHAR(n)0 ~ 65535 字符变长字符串节省空间推荐用于姓名、地址等TINYTEXT255 字节短文本TEXT65535 字节约 64KB长文本MEDIUMTEXT16,777,215 字节约 16MB中等长度文本LONGTEXT4,294,967,295 字节约 4GB极大文本BINARY/VARBINARY类似 CHAR/VARCHAR存储二进制字节串无字符集BLOB 系列TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB二进制数据图片、文件ENUM最多 65535 个值枚举从预设值中选择一个如性别 ‘M’,‘F’SET最多 64 个成员集合可选择多个值使用建议定长且长度稳定的字段用CHAR如 MD5 密码长度可变用VARCHAR大文本用TEXT系列金额用DECIMAL避免FLOAT/DOUBLE的精度问题4.4 列属性属性含义NOT NULL该列不能为空DEFAULT默认值PRIMARY KEY主键唯一且非空AUTO_INCREMENT自动递增通常用于 INT 主键UNSIGNED无符号非负CHARACTER SET name指定该列的字符集五、管理表DDL5.1 创建表基本语法sqlCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name ( column_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY], ... ) ENGINEengine_name DEFAULT CHARSETcharset_name;范例创建学生表sqlUSE db1; -- 先切换到目标数据库 CREATE TABLE student ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 无符号自增主键 name VARCHAR(20) NOT NULL, -- 变长字符串非空 age TINYINT UNSIGNED, -- 小整数非负 gender ENUM(M,F) DEFAULT M -- 枚举默认为 M ) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8mb4;查看表sqlSHOW TABLES; -- 查看当前库所有表 DESC student; -- 查看表结构字段 SHOW COLUMNS FROM student; -- 同上 SHOW CREATE TABLE student; -- 查看建表语句含所有细节插入测试数据sqlINSERT INTO student (name, age) VALUES (xiaoming, 20); INSERT INTO student (name, age, gender) VALUES (xiaohong, 18, F);查询数据sqlSELECT * FROM student;5.2 通过查询结果创建表复制数据sql-- 复制数据包含结构和数据 CREATE TABLE student2 SELECT name, age FROM student; -- 复制结构但不复制数据 CREATE TABLE student3 LIKE student;5.3 修改表ALTER TABLE修改表名sqlALTER TABLE student RENAME TO stu;添加字段sqlALTER TABLE stu ADD phone VARCHAR(11) AFTER name; -- 在 name 之后添加修改字段类型sqlALTER TABLE stu MODIFY phone INT;修改字段名和类型sqlALTER TABLE stu CHANGE COLUMN phone mobile CHAR(11);删除字段sqlALTER TABLE stu DROP COLUMN mobile;修改表字符集sqlALTER TABLE stu CHARACTER SET utf8;同时修改字段类型和字符集sqlALTER TABLE stu CHANGE name name CHAR(30) CHARACTER SET utf8;设置字段默认值sqlALTER TABLE stu ALTER COLUMN gender SET DEFAULT M;添加字段并设置默认值sqlALTER TABLE stu ADD is_del BOOL DEFAULT false; ALTER TABLE stu MODIFY is_del BOOL DEFAULT true; -- 修改默认值添加主键sqlALTER TABLE stu2 ADD PRIMARY KEY (id);删除主键sqlALTER TABLE stu2 DROP PRIMARY KEY;5.4 查看表状态sql-- 查看指定表状态 SHOW TABLE STATUS LIKE student\G -- 查看当前库所有表状态 SHOW TABLE STATUS FROM db1; -- 查看所有存储引擎 SHOW ENGINES;5.5 删除表sqlDROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...;范例sqlDROP TABLE student2, student3; DROP TABLE IF EXISTS student4;六、本期知识点归纳一览表类别知识点关键点/命令SQL 分类DDL / DML / DQL / DCL / TCL各司其职字符集utf8mb4推荐SHOW CHARSET,SHOW VARIABLES LIKE character%排序规则utf8mb4_0900_ai_ci默认不区分大小写SHOW COLLATION,SHOW VARIABLES LIKE collation%数据库操作创建、修改、删除CREATE DATABASE,ALTER DATABASE,DROP DATABASE数值类型INT, DECIMAL, TINYINT, BIGINT金额用 DECIMAL年龄用 INT UNSIGNED日期类型DATETIME推荐, DATE, TIMESTAMP注册时间用 DATETIME字符串类型VARCHAR变长, CHAR定长, TEXT, ENUM姓名用 VARCHAR固定码用 CHAR列属性NOT NULL, DEFAULT, AUTO_INCREMENT, PRIMARY KEY主键通常为自增 INT创建表CREATE TABLE ...需指定引擎InnoDB、字符集查看表SHOW TABLES,DESC,SHOW CREATE TABLE快速获取结构复制表CREATE TABLE ... SELECT含数据CREATE TABLE ... LIKE仅结构灵活复用修改表ALTER TABLE ADD/MODIFY/CHANGE/DROP/RENAME谨慎使用DROP COLUMN删除表DROP TABLE不可恢复慎用下一期预告SQL 语言之数据操作与单表查询DMLINSERT/UPDATE/DELETEDQLSELECT 详解、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT、聚合函数等。
