MySQL--操作简记(create创建操纵表、insert插入、update更新、delete删除数据)

MySQL操作笔记:创建、更新、删除与插入数据
这篇博客总结了MySQL中创建和操纵表的基本操作,包括CREATE TABLE创建表、ALTER TABLE更新表、DROP TABLE删除表、RENAME TABLE重命名表。此外,还详细介绍了插入数据的方法,如插入完整行、多个行以及插入检索出的数据。更新和删除数据的部分包括UPDATE语句的使用和DELETE语句删除特定行的操作。

啊啊啊啊啊啊啊,最近看了MySQL必知必会,感觉操作上还可以吧,就是关键字也太多多多多多了~~~为了自己查找,复习起来方便,所以把这些操作简单总结一下(→_→)

创建和操纵表

1.创建表

  • CREATE TABLE
CREATE TABLE customers
(
    cust_id       int       NOT NULL  AUTO_INCREMENT,
    cust_name     char(50)  NOT NULL,
    cust address  char(50)  NULL,
    cust city     char(50)  NULL,
    quantity      int       NOT NULL  DEFAULT 1,
    PRIMARY KEY(cust_id)
) ENGINE = InnoDB;

【1】表名紧跟在CREATE TABLE后;
【2】所有列都在()中,各列用逗号隔开;每列的定义以列名开始,数据类型,是否允许NULL值;
【3】AUTO_INCREAMENT告诉MySQL,本列每当增加一行时自动增量;每个表只允许一个AUTO_INCREAMENT列;
【4】如果在插入时没有给出值,此时使用默认值,由DEFAULT关键字指定;
【5】PRIMARY KEY关键字指定主键,如果是多个列组成的主键则用(a,b,c)格式;
【6】ENGINE = 指定了引擎类型

2.更新表

  • ALTER TABLE
ALTER TABLE vendors
ADD vend_phone CHAR(20);

给vendors表增加一个名为vend_phone的列,必须给出其数据类型。

ALTER TABLE vendors
DROP COLUMN vend_phone;

给vendors表删除一个名为vend_phone的列。

ALTER TABLE orderitems
FOREIGN KEY (order_num) REFERENCES orders(order_num)

定义外键:参照表orders中的主键order_num,将表orderitems中order_num这一列设为外键。

3.删除表

  • DROP TABLE
DROP TABLE customers;

删除了customers表。删除表没有确认也没有撤销。

4.重命名表

  • RENAME TABLE
RENAME TABLE customers TO customer1;

对多个表重命名

RENAME TABLE customers TO customer1;
             vendors TO vendor1;
             products TO product1;

插入数据

1.插入完整的行

  • INSERT
INSERT INTO customers
VALUES(
NULL,
'PEP E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'USA'
NULL,
NULL
);

VALUES字句中为要插入的值,如果某一列没有值应该使用NULL;各个列必须以它们在表定义中出现的顺序填充

INSERT INTO customers(
cust_name,
cust_address,
cust_city,
cust_state,
cust_country,

)
VALUES(
'PEP E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'USA'
);

还可以明确给出列名,VALUES中的值与之对应即可,即使顺序不同,也可正确插入。

2.插入多个行

按照以下格式

INSERT INTO ......
)
VALUES(
...
);
INSERT INTO ......
)
VALUES(
...
);

或者

INSERT INTO ......
)
VALUES(
...
),
VALUES(
...
);

3.插入检索出的数据

将SELECT检索出的结果利用INSERT插入

INSERT INTO customers(
...
)
SELECT ...
FROM custnew;

将custnew中检索出的值插入到customers中,要保证customers中主键值不会重复,否则操作将失败。

更新和删除数据

1.更新数据

  • UPDATE
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cusr_id = 1005;

将表customers表中id为1005的用户的email进行更新

UPDATE ...
SET ...= '...'
    ...= '...'
WHERE  ...

更新多个列

为了删除某个列的值,可以将它设置为NULL(允许设置NULL值时)

2.删除数据

  • DELETE
DELETE FROM customers
WHERE cust_id = 1006;

从customers表中删除id=1006的那一行。DELETE从表中删除行,但是不删除表本身。

如果删除表中所有行可以用TRUNCATE TABLE语句,它速度更快,它实际上是删除了原来的表,再重新创建一个表。

<think>我们参考用户提供的引用内容,并结合MySQL字符集转换的实际操作步骤和潜在影响进行回答。 用户问题:调整MySQL的字符集从utf8到utf8mb4的操作方法及其影响分析 根据引用内容,我们可以总结以下关键点: 1. 引用[1]:varchar(255)MySQL5.0以上指的是255个字符,最大字节数为65532字节。由于utf8mb4每个字符最多4字节,255个字符最多1020字节,远小于65532字节,因此修改字符集不会导致字段超出最大字节限制而失败。 2. 引用[2]:提供了使用mysqldump导出数据时指定字符集为utf8mb4的方法。 3. 引用[3]:在转换字符集后,如果进行连接时字符集不同,可能会出现问题(如使用convert函数进行转换)。 4. 引用[4]:提供了使用ALTER TABLE ... CONVERT TO CHARACTER SET ... 的官方语法。 下面我们将详细说明操作步骤和潜在影响。 ### 操作步骤 #### 1. 备份数据 在进行任何修改之前,务必备份数据库。可以使用mysqldump工具,如引用[2]所示: ```bash mysqldump -u [username] -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases [database_name] > /backup/[database_name]_schema.sql ``` - `--no-data`:只导出结构,不导出数据(如果也要备份数据,则去掉此选项)。 - `--default-character-set=utf8mb4`:设置导出的默认字符集为utf8mb4。 - `--single-transaction`:保证导出的一致性(针对InnoDB)。 - `--set-gtid-purged=OFF`:如果启用了GTID,需要设置为OFF以避免在导入时出现问题。 #### 2. 检查当前字符集和排序规则 在转换前,检查数据库、和列的当前字符集: ```sql SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'your_database_name'; SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name'; SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND CHARACTER_SET_NAME IS NOT NULL; ``` #### 3. 修改数据库默认字符集 ```sql ALTER DATABASE `your_database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; ``` #### 4. 修改的字符集 有两种方法: - 方法一:仅修改的默认字符集(不会修改已有列的字符集): ```sql ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` 注意:使用`CONVERT TO`会同时转换中所有字符类型列(CHAR, VARCHAR, TEXT等)的字符集,并重新编码数据- 方法二:如果不想重新编码数据(即只修改元数据),可以使用: ```sql ALTER TABLE `table_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` 但这种方法不会改变已有列的字符集,新增加的列才会使用新的字符集。 推荐使用`CONVERT TO`,因为它会确保中所有列都转换为新的字符集,避免数据不一致。 #### 5. 修改连接字符集 确保应用程序连接MySQL时使用的字符集也是utf8mb4。在连接字符串中设置: ``` jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8mb4 ``` ### 潜在影响分析 1. **存储空间增加**:utf8mb4每个字符最多占用4字节(而utf8最多3字节),因此转换后,存储相同文本可能需要更多空间。尤其是对于VARCHAR、TEXT等类型,最大长度可能会受到限制(但如引用[1]所述,varchar(255)不会超过最大行大小限制)。 2. **索引长度限制**:InnoDB引擎对索引键长度有限制(767字节或3072字节,取决于版本和设置)。例如,在MySQL5.6及以下版本,使用utf8mb4的VARCHAR(255)列作为索引: - 每个字符4字节,255个字符需要1020字节,超过767字节的限制,会导致索引创建失败。解决方法: - 减少索引列的长度(如改为VARCHAR(191))。 - 使用前缀索引(如`INDEX (column_name(191))`)。 - 升级到MySQL5.7+,并设置`innodb_large_prefix=ON`(默认开启)和`innodb_file_format=Barracuda`,这样索引长度上限可提高到3072字节。 3. **排序规则问题**:如果转换后排序规则(collation)发生变化,可能会导致字符串比较和排序结果不同。应选择一致的排序规则(如`utf8mb4_unicode_ci`)。 4. **数据编码转换的正确性**:MySQL转换字符集时会重新编码数据。如果原始数据中有非法的utf8字符,转换可能会失败或数据丢失。因此,在转换前应检查数据有效性。 5. **性能影响**:转换时,`ALTER TABLE`操作会重建(特别是使用`CONVERT TO`时),可能长时间锁(阻塞写操作)。建议: - 在低峰期操作- 使用在线DDL(如MySQL5.6+的InnoDB支持在线DDL,但数据重建阶段仍可能锁)。 - 使用pt-online-schema-change工具在线修改。 6. **兼容性问题**:如果应用程序没有设置为使用utf8mb4连接,可能会出现乱码。确保应用程序连接字符串设置正确(如上述步骤5)。 7. **外键和触发器**:如果有外键约束,修改字符集时需要确保相关也使用相同的字符集,否则可能破坏外键约束。 ### 总结 将MySQL字符集从utf8转换为utf8mb4的步骤包括备份、检查、修改数据库和字符集,并调整应用程序连接设置。主要影响在于存储空间、索引长度、排序规则和性能。操作前需充分测试,并确保应用程序兼容。 §§相关问题§§ 1. 如何检查MySQL中哪些或列仍在使用非utf8mb4字符集? 2. 在MySQL转换字符集时,如何避免长时间锁? 3. 如何解决由于utf8mb4字符集导致的索引键长度超限问题? 4. MySQL的utf8mb4字符集与排序规则(collation)如何选择? [^1]: mysql 数据库字符集,字符集 ,列字符集优先级,以及修改字符集utf8为utf8mb4对之前的数据是否有影响 简记。第一点。因 mysql5.0版本以上,varchar(255),指的是255字符; 最大65532字节; 所以 varchar(255) 这个限制即使改变 字符集,也没有超过最大字节数,所以不会受影响; [^2]: mysql 8.0字符集_技术分享 | MySQL 8.0:字符集从 utf8 转换成 utf8mb4。mysqldump -u -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases testdb > /backup/testdb.sql [^3]: 【mysql】utf8和utf8mb4关联字符集转化问题。| Note | 1003 | /*select#1 */selectcount(0)AS`count(*)`from`test_ghost`.`emp_mb4` `a` straight_join `test_ghost`.`emp` `b`where(`test_ghost`.`a`.`ename` =convert(`test_ghost`.`b`.`ename` using utf8mb4)) | [^4]: mysql修改字符集utf8_to_utf8mb4。https://dev.mysql.com/doc/refman/5.7/en/alter-table.html ALTER TABLE *tbl_name* CONVERT TO CHARACTER SET *charset_name*;</think>### MySQL 修改字符集 utf8 到 utf8mb4 的操作步骤与影响分析 #### **一、操作步骤** 1. **备份数据**(强制建议) 使用 `mysqldump` 导出结构和数据,避免转换失败导致数据丢失: ```bash mysqldump -u [user] -p --default-character-set=utf8mb4 --single-transaction --databases [db_name] > backup.sql ``` 参数说明: - `--default-character-set=utf8mb4`:确保导出文件使用目标字符集[^2] - `--single-transaction`:保证导出数据一致性(InnoDB适用) 2. **修改数据库默认字符集** ```sql ALTER DATABASE [db_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; ``` 3. **修改字符集** **方法1:仅修改数据(快速但可能遗留问题)** ```sql ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` *说明:此命令会同时转换所有列的字符集,并重新编码数据[^4]* **方法2:分步修改(推荐彻底转换)** ```sql -- 步骤1:修改默认字符集 ALTER TABLE [table_name] CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 步骤2:逐列修改字符集 ALTER TABLE [table_name] MODIFY [column_name] VARCHAR(255) CHARACTER SET utf8mb4; ``` 4. **验证与修复** ```sql -- 检查字符集 SHOW CREATE TABLE [table_name]; -- 修复可能的数据截断 REPAIR TABLE [table_name]; ``` --- #### **二、潜在影响分析** 1. **存储空间变化** - **增加**:utf8mb4 最大支持 4 字节/字符(比 utf8 的 3 字节多 33%) - **例外**:纯 ASCII 字符存储空间不变 - *实际案例*:`VARCHAR(255)` 列在 utf8 下最大 765 字节,utf8mb4 下最大 1020 字节,但仍低于行最大限制 65535 字节[^1] 2. **索引长度限制** - **问题**:InnoDB 索引键长度上限 767 字节 ```sql -- utf8mb4 下索引可能失败 CREATE INDEX idx_name ON users(name); -- name VARCHAR(255) ``` - **解决方案**: - 缩短索引字段:`VARCHAR(191)`(191*4=764 < 767) - 启用 `innodb_large_prefix`(MySQL 5.7+ 默认开启) 3. **数据兼容性问题** - **emoji/生僻字支持**:✓ 可正常存储(如 😊 → 0xF09F988A) - **乱码风险**: - 原有非法 utf8 数据(如截断的字符)转换时可能变成
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值