当前位置：Gxlcms > 数据库问题 > mysql之表相关操作

mysql之表相关操作

时间：2021-07-01 10:21:17 帮助过：23人阅读

[TEMPORARY] TABLE [IF NOT EXISTS] tbl_name( 字段名称 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT ‘string‘] [REFERENCES tbl_name (index_col_name,...)], ... )ENGINE=引擎名称 [DEFAULT] CHARSET=编码方式；

利用查询的结果创建表,同时插入数据

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TEMPORARY</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">IF NOT EXISTS</span><span style="color: #ff0000">]</span> tbl_name  <span style="color: #ff0000">[</span><span style="color: #ff0000">AS</span><span style="color: #ff0000">]</span> query_expression;

利用已有的表创建空表

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TEMPORARY</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">IF NOT EXISTS</span><span style="color: #ff0000">]</span> tbl_name <span style="color: #808080">LIKE</span> old_tbl_name;

2.显示当前数据库中所有表名称

  1. SHOW TABLES;

3.查询指定表结构或指定字段的类型及约束

  1. {<span style="color: #0000ff">DESC</span> <span style="color: #808080">|</span> DESCRIBE <span style="color: #808080">|</span> EXPLAIN} tbl_name <span style="color: #ff0000">[</span><span style="color: #ff0000">col_name</span><span style="color: #ff0000">]</span><span style="color: #000000">
  2. SHOW {COLUMNS </span><span style="color: #808080">|</span> FIELDS} <span style="color: #0000ff">FROM</span> tbl_name  <span style="color: #ff0000">[</span><span style="color: #ff0000">FROM db_name</span><span style="color: #ff0000">]</span>;

例如:

  1. <span style="color: #0000ff">DESC</span> <span style="color: #ff00ff">user</span>;  <span style="color: #008080">--</span><span style="color: #008080"> 显示user表的结构</span>
  2. <span style="color: #0000ff">DESC</span> <span style="color: #ff00ff">user</span> username; <span style="color: #008080">--</span><span style="color: #008080"> 只显示user表username字段的类型及约束</span>
  3. SHOW COLUMNS <span style="color: #0000ff">FROM</span> <span style="color: #ff00ff">user</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> testdb;
  4. SHOW COLUMNS </span><span style="color: #0000ff">FROM</span> testdb.<span style="color: #ff00ff">user</span>;

4.显示表的创建过程

  1. SHOW <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> tbl_name;

5.删除表格定义和对应的的数据

  1. <span style="color: #0000ff">DROP</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TEMPORARY</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">IF EXISTS</span><span style="color: #ff0000">]</span><span style="color: #000000"> 
  2. tbl_name </span><span style="color: #ff0000">[</span><span style="color: #ff0000">, tbl_name</span><span style="color: #ff0000">]</span> ...

需要注意: If any tables named in the argument list do not exist, the statement returns an error indicating by name which nonexisting tables it was unable to drop, but also drops all tables in the list that do exist. DROP TABLE 只会删除列表中存在的数据表，对于不存在的会返回错误。

6.清空表格数据，但表格仍然存在

  1. <span style="color: #0000ff">TRUNCATE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TABLE</span><span style="color: #ff0000">]</span> tbl_name

和DELETE from tbl_name的区别在于: TRUNCATE TABLE是先删除表，然后在重新创建，而不是逐行删除表格中的数据，因此对于数据量很大的表格要删除所有数据时，TRUNCATE TABLE的速度更快

 

表结构相关:

参考: alter-table

alter table 可以同时完成添加、删除、修改字段类型、修改字段名称等多种操作

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #ff0000">[</span><span style="color: #ff0000">alter_specification [, alter_specification</span><span style="color: #ff0000">]</span> ...]

1.修改表名称

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name  RENAME <span style="color: #ff0000">[</span><span style="color: #ff0000">TO|AS</span><span style="color: #ff0000">]</span><span style="color: #000000"> new_tbl_name; 
  2. RENAME </span><span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">TO</span> new_tbl_name <span style="color: #ff0000">[</span><span style="color: #ff0000">, tbl_name2 TO new_tbl_name2</span><span style="color: #ff0000">]</span> ... <span style="color: #008080">--</span><span style="color: #008080">可以同时修改多个表名称</span>

2.添加字段

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">ADD</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">COLUMN</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">col_name</span> column_definition <span style="color: #ff0000">[</span><span style="color: #ff0000">FIRST | AFTER col_name</span><span style="color: #ff0000">]</span>； <span style="color: #008080">--</span><span style="color: #008080"> first表示插入字段作为该表的首列</span>

3.删除字段

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">DROP</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">COLUMN</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">col_name</span>;

4.只修改字段的类型以及在表格中的位置

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name MODIFY <span style="color: #ff0000">[</span><span style="color: #ff0000">COLUMN</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">col_name</span> column_definition <span style="color: #ff0000">[</span><span style="color: #ff0000">FIRST | AFTER col_name</span><span style="color: #ff0000">]</span>;

同时修改字段类型、在表格中的位置和字段名称

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name CHANGE <span style="color: #ff0000">[</span><span style="color: #ff0000">COLUMN</span><span style="color: #ff0000">]</span> old_col_name new_col_name column_definition <span style="color: #ff0000">[</span><span style="color: #ff0000">FIRST|AFTER col_name</span><span style="color: #ff0000">]</span>;

5.设置、取消默认值

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">ALTER</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">COLUMN</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">col_name</span> {<span style="color: #0000ff">SET</span> <span style="color: #0000ff">DEFAULT</span> default_value <span style="color: #808080">|</span> <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">DEFAULT</span>};

6. 添加、删除主键

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">ADD</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">CONSTRAINT [symbol</span><span style="color: #ff0000">]</span>] <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>  <span style="color: #ff0000">[</span><span style="color: #ff0000">index_type</span><span style="color: #ff0000">]</span> (index_col_name,...); <span style="color: #008080">--</span><span style="color: #008080"> 添加主键</span>
  2. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>； <span style="color: #008080">--</span><span style="color: #008080"> 删除主键</span>

注意: 当主键设为自增长时,尝试删除主键会发生错误。需要先使用ALTER TABLE MODIFY去除AUTO_INCREMENT属性。

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> test(
  2.     </span><span style="color: #808080">-></span> id <span style="color: #0000ff">INT</span> UNSIGNED AUTO_INCREMENT <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>
  3.     <span style="color: #808080">-></span><span style="color: #000000">  );
  4. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> test <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">;
  5. ERROR </span><span style="color: #800000; font-weight: bold">1075</span> (<span style="color: #800000; font-weight: bold">42000</span>): Incorrect <span style="color: #0000ff">table</span> definition; there can be <span style="color: #0000ff">only</span> one auto <span style="color: #0000ff">column</span> <span style="color: #808080">and</span> it must be defined <span style="color: #0000ff">as</span> a <span style="color: #0000ff">key</span><span style="color: #000000">
  6. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> test MODIFY id <span style="color: #0000ff">INT</span> UNSIGNED; <span style="color: #008080">--</span><span style="color: #008080"> 去除主键的AUTO_INCREMENT属性</span>
  7. mysql<span style="color: #808080">></span> <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> test <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>;

7.添加、删除唯一索引

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name  <span style="color: #0000ff">ADD</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">CONSTRAINT [symbol</span><span style="color: #ff0000">]</span>] <span style="color: #0000ff">UNIQUE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">INDEX|KEY</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">index_name</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">index_type</span><span style="color: #ff0000">]</span> (index_col_name,...); <span style="color: #008080">--</span><span style="color: #008080"> 添加唯一索引</span>
  2. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name  <span style="color: #0000ff">DROP</span> {<span style="color: #0000ff">INDEX</span><span style="color: #808080">|</span><span style="color: #0000ff">KEY</span>} index_name;  <span style="color: #008080">--</span><span style="color: #008080"> 删除索引</span>

添加的索引默认以对应的字段名称命名

8.修改存储引擎

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name ENGINE <span style="color: #ff0000">[</span><span style="color: #ff0000">=</span><span style="color: #ff0000">]</span> engine_name;

9.修改自增长的值

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name  AUTO_INCREMENT <span style="color: #ff0000">[</span><span style="color: #ff0000">=</span><span style="color: #ff0000">]</span> value;

10.修改字符集

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> tbl_name <span style="color: #ff0000">[</span><span style="color: #ff0000">DEFAULT</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">CHARACTER</span> <span style="color: #0000ff">SET</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">=</span><span style="color: #ff0000">]</span> charset_name;

 

mysql之表相关操作

标签：tps   ror   delete   字符   默认   primary   字符集   sig   存储   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！