首页 / MYSQL / mysql 基本操作 三
mysql 基本操作 三
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 基本操作 三,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6778字,纯文字阅读大概需要10分钟。
内容图文
1.alter
创建测试表
MariaDB [jason]> create table testalter_tbl(i int,c char(1)); Query OK, 0 rows affected (0.08 sec)
MariaDB [jason]> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
删除 i 字段
MariaDB [jason]> alter table testalter_tbl drop i; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row inset (0.01 sec)
添加字段
MariaDB [jason]> alter table testalter_tbl add i int; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+
将字段添加在指定位置
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> alter table testalter_tbl add i int first -> ; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows inset (0.00 sec) MariaDB [jason]> alter table testalter_tbl drop i; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> alter table testalter_tbl add i int after c; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows inset (0.00 sec)
修改字段类型及名称
MariaDB [jason]> alter table testalter_tbl modify c char(10); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows inset (0.00 sec)
用change 修改 change 旧名字 新名字 字段类型
MariaDB [jason]> alter table testalter_tbl change i j bigint; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows inset (0.00 sec) MariaDB [jason]> alter table testalter_tbl change j j int; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows inset (0.00 sec)
alter 对null 和默认值的影响
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | 100 | | +-------+------------+------+-----+---------+-------+
修改字段默认值
MariaDB [jason]> alter table testalter_tbl alter j setdefault1000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | 1000 | | +-------+------------+------+-----+---------+-------+ 2 rows inset (0.00 sec)
删除默认值
MariaDB [jason]> alter table testalter_tbl alter j drop default; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> show columns from testalter_tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(10) | YES | | NULL | | | j | bigint(20) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows inset (0.01 sec)
修改表的引擎
MariaDB [jason]> SHOW TABLE STATUS LIKE ‘testalter_tbl‘ \G; *************************** 1. row *************************** Name: testalter_tbl Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2019-02-1320:50:40 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row inset (0.00 sec) ERROR: No query specified MariaDB [jason]> alter table testalter_tbl engine=myisam; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [jason]> SHOW TABLE STATUS LIKE ‘testalter_tbl‘ \G; *************************** 1. row *************************** Name: testalter_tbl Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 13792273858822143 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2019-02-1320:59:30 Update_time: 2019-02-1320:59:30 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row inset (0.00 sec) ERROR: No query specified
修改表名
MariaDB [jason]> alter table testalter_tbl rename to testalter;
2.
原文:https://www.cnblogs.com/jason-dong/p/10372018.html
内容总结
以上是互联网集市为您收集整理的mysql 基本操作 三全部内容,希望文章能够帮你解决mysql 基本操作 三所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。