【MySQL——第三章 ：MySQL库表操作】

第三章 ：MySQL库表操作

1.SQL语句基础

1.1 SQL简介

• SQL：结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。使用SQL语句，程序员和数据库管理员可以完成如下的任务
  • 改变数据库的结构
  • 更改系统的安全设置
  • 增加用户对数据库或表的许可权限
  • 在数据库中检索需要的信息
  • 对数据库的信息进行更新

1.2 SQL语句分类 四大类

• MySQL致力于支持全套ANSI/ISO SQL标准。在MySQL数据库中，SQL语句主要可以划分为以下几类
  • DDL(Data Definition Language)：数据定义语言，定义对数据库对象(库、表、列、索引)的操作，如：CREATE（创建）、DROP（删除）、ALTER（修改）、RENAME（重名名）、show 查看 等
  • DML(Data Manipulation Language)：数据操作语言，定义对数据库记录的操作，如：INSERT（插入）、DELETE/ TRUNCATE（删除）、UPDATE（更新）、
  • DQL 数据查询语言： select （查询）
  • DCL(Data Control Language)：数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别，如：
    • GRANT（授权）、REVOKE（回收特权）等
    • DTL
    • Transaction Control:事务控制
    • COMMIT、ROLLBACK、SAVEPOINT等

1.3 SQL语句的书写规范

• 在数据库系统中，SQL语句不区分大小写(建议用大写) ，但字符串常量区分大小写
• SQL语句可单行或多行书写，以“;”结尾。
• 关键词不能跨多行或简写。
• 用空格和缩进来提高语句的可读性。
• 子句通常位于独立行，便于编辑，提高可读性。
• 注释：
  • /* */ 为多行注释 —(远程终端执行有问题)
  • – 和 # 为单行注释

1.4 库/表的命名约束

​ 64字符

​ _ student_info

​ `` 引用关键字作为数据对象 “” ‘ ’

名称由字母、数字、#、下划线、$组成，必须以英文字母开头

​ 在同一个数据库对象集合内对象不能同名

1.5 mysql内置指令

? mysql内置指令—结束可以没有分号

​ use \u

​ system \!

​ dilimiter \d 自动以sql语句终止结束符

​ clear \c 清空当前命令行的语句

​ exit\quit \q

2. 数据库操作

2.1 数据库的登录及退出

• 登录格式

mysql  -u用户名  -h服务器的主机地址 -p密码 -A  -P 端口 -D 数据库名 -e sql语句

• 退出：exit quit \q

2.2 查看数据库

• 格式：like 模糊查询 % 任意长度的任意字符 _ 任意单个字符

# 新建数据库
create database 数据库名
create database if not exists 数据库名;
# 设为当前数据库
use  数据库名
# 查看当前使用的数据库
select  database();
# 显示所有数据库
show  databases;
show create database 数据库名;    #查看完整建库语句
# 删除数据库
drop database 数据库名
# 查看当前登录的用户及主机名
select user();

3. 字符集：

3.1 ASCII 及其扩展

ASCII（American Standard Code for Information Interchange）
字符集：包含英文字母（大小写）、数字、标点符号及控制字符（如换行、回车），共 128 个字符。
编码规则：每个字符用 1 字节（8 位） 表示，最高位为 0，范围是 0x00 到 0x7F。
局限：仅支持英文，无法表示中文、日文等其他语言字符。
扩展 ASCII
在 ASCII 基础上利用最高位（第 8 位）扩展，支持 0x80 到 0xFF 共 128 个额外字符，可表示部分欧洲语言符号（如 é、ñ）。
问题：无统一标准，不同国家 / 地区定义不同（如 Windows-1252、ISO-8859-1），兼容性差。

3.2 多字节编码（针对亚洲语言）

1. GB 系列（中文）
GB2312（国标 2312）
字符集：包含 6763 个简体汉字、682 个非汉字字符（如标点、字母），主要覆盖常用中文。
编码规则：汉字用 2 字节 表示，每个字节最高位为 1（区别于 ASCII），范围是 0xA1-A9（第一字节）和 0xA1-FE（第二字节）。
局限：不支持繁体汉字、生僻字。
GBK（汉字内码扩展规范）
字符集：兼容 GB2312，新增繁体汉字、生僻字等，共收录 21003 个汉字 和 883 个非汉字字符。
编码规则：汉字仍用 2 字节表示，范围扩展到 0x81-FE（第一字节）和 0x40-FE（第二字节，不含 0x7F）。
应用：Windows 系统默认中文编码之一，广泛用于中文文档、早期网站。
GB18030
字符集：兼容 GB2312 和 GBK，进一步扩展支持少数民族文字（如藏文、蒙古文）和更多生僻字，总字符数超 7 万。
编码规则：采用 1 字节（ASCII）、2 字节（兼容 GBK）、4 字节 混合编码，覆盖 Unicode 所有字符。
地位：中国国家标准强制编码，用于政府、金融等正式场景。
2. Big5（大五码）
字符集：主要用于繁体中文，包含 13053 个繁体汉字、408 个非汉字字符（如标点、字母）。
编码规则：汉字用 2 字节表示，第一字节 0x81-0xFE，第二字节 0x40-0x7E 或 0xA1-0xFE。
应用：中国台湾、香港地区常用，早期繁体中文系统（如 macOS 繁体版）默认编码。

3.3 Unicode 与 UTF 编码（全球通用）

1. Unicode（字符集）
定义：全球统一的字符集，为世界上几乎所有语言的字符（包括文字、符号、表情符号）分配唯一的数字编号（称为 码点，如 U+4E2D 代表 “中”）。
范围：码点从 U+0000 到 U+10FFFF，可容纳超 100 万个字符，涵盖中文、英文、日文、 emoji 等。
注意：Unicode 仅定义字符与码点的对应关系，不直接规定存储方式，需通过 UTF 编码实现二进制存储。
2. UTF 编码（Unicode 转换格式）
UTF-8
编码规则：可变长度编码，用 1-4 字节 表示不同码点：
ASCII 字符（U+0000-U+007F）：1 字节（与 ASCII 完全兼容）；
欧洲 / 阿拉伯字符（U+0080-U+07FF）：2 字节；
中文、日文等（U+0800-U+FFFF）：3 字节；
生僻字、emoji（U+10000-U+10FFFF）：4 字节。
优势：兼容 ASCII，节省英文存储空间，是 互联网、网页（HTML/XML）、数据库（MySQL、PostgreSQL）的首选编码。
应用：Linux 系统默认编码、大部分编程语言（Python 3 字符串默认）、网页 meta 标签指定（charset="UTF-8"）。
UTF-16
编码规则：大部分字符用 2 字节 表示，超出范围的用 4 字节（通过代理对实现）。
特点：编码效率高于 UTF-8（中文、日文存储更紧凑），但不兼容 ASCII。
应用：Windows 系统内部编码（WCHAR 类型）、Java 字符串默认编码、.NET 框架。
UTF-32
编码规则：每个字符固定用 4 字节 表示，直接对应 Unicode 码点。
特点：编码 / 解码速度快，但存储空间占用大（是 UTF-8 的 4 倍）。
应用：较少用于日常存储，主要用于字符处理、字体文件等场景。

3.4 定义语句

#查看所有支持的字符集
show character set; 
# 或者使用\g替代定界符分号
mysql> show character set\g
# \G表示将查询结果进行按列打印，即将查到的结构旋转90度变成纵向显示,不需要分号
mysql> show character set\G
show character set like 'utf%';
show character set;=show charset;
#查看支持的字符序
show collation;
#通过变量查看
show variables  like '%character%';
#修改字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集名 COLLATE 排序规则;
ALTER TABLE tb4 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tb4 MODIFY num VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 表的基本操作

4.1 数据类型

• 类型组成：数字、文本、日期/时间类型

  数值类型 整数int 小数浮点类型
  数据类型：

1.数值类型( unsigned)int  4    42亿floatdoubledecimal2.字符类型char(20)varchar(20)           255textblob3.boolean布尔类型==tinyint4.日期时间类型datetimedatetime

4.2库定义语句：

# 创建表
create  table 表名 (字段名1   数据类型,字段名2   数据类型,...);
create  table 表名 (字段名1   数据类型,字段名2   数据类型,...) character set 字符集  collate 字符集_ci;# 删除表
drop  table  表名;#查看表
show tables;
show tables from db1;show table [like] 'stu%';     # %任意长度的任意字符   _ 任意单个字符
#查看完成的建表语句
show  create table 表名;#修改表名
alter  table 表名  rename  新表名;
== rename table 表名  to  新表名;#查看表字段（表结构）
desc|explain|describe   表名;
show  columns from  表名;
#添加列（字段）
alter table 表名  add  字段名  数据类型 [after|first] 字段名;
#删除列
alter table 表名   drop 字段名;
#修改该列（字段名/字段的数据类型）
alter table 表名  change 旧的字段名  新的字段名 数据类型;
alter table 表名  modify  字段名 新的数据类型；  

#表复制 (备份)
create table  新表名  like 旧表名 ;             #备份表结构insert into 新表名 select * from 旧表名;  #复制表数据
create table 新表名 select * from 旧表；        #备份表结构和表数据