首页 > 代码库 > MYSQL数据表操作语句
MYSQL数据表操作语句
1.查看某数据库中的表
SHOW [FULL] TABLES [FROM db_name] [LIKE ‘pattern‘]
SHOW TABLES列举了给定数据库中的非TEMPORARY表。也可以使用mysqlshow db_name命令得到此清单。
本命令也列举数据库中的其它视图。支持FULL修改符，这样SHOW FULL TABLES就可以显示第二个输出列。对于一个表，第二列的值为BASE TABLE；对于一个视图，第二列的值为VIEW。
2.查看数据表的详细信息
SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern‘]
3.查看建表语句
SHOW CREATE TABLE tbl_name
4.建表语句
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expressionCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
建表语句内容比较多，先来一张图看看整体结构。
create_definition: --列名 列定义 col_name column_definition --主键约束（主键索引） | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... --唯一约束 | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... --外键约束 | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition --自定义检查约束 | CHECK (expr)
列定义
column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT ‘string‘] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition] | data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [NOT NULL | NULL] [[PRIMARY] KEY]
实数据类型
data_type: BIT[(length)] | TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] | DATE | TIME[(fsp)] | TIMESTAMP[(fsp)] | DATETIME[(fsp)] | YEAR | CHAR[(length)] [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | VARCHAR(length) [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | JSON | spatial_type
索引与约束
index_col_name: col_name [(length)] [ASC | DESC]index_type: USING {BTREE | HASH}index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT ‘string‘reference_definition: REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION
表基本属性设置
table_options: table_option [[,] table_option] ...table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] ‘string‘ | COMPRESSION [=] {‘ZLIB‘|‘LZ4‘|‘NONE‘} | CONNECTION [=] ‘connect_string‘ | DATA DIRECTORY [=] ‘absolute path to directory‘ | DELAY_KEY_WRITE [=] {0 | 1} | ENCRYPTION [=] {‘Y‘ | ‘N‘} | INDEX DIRECTORY [=] ‘absolute path to directory‘ | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] ‘string‘ | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name | UNION [=] (tbl_name[,tbl_name]...)
表分区：
partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)]partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] ‘comment_text‘ ] [DATA DIRECTORY [=] ‘data_dir‘] [INDEX DIRECTORY [=] ‘index_dir‘] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [(subpartition_definition [, subpartition_definition] ...)]subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] ‘comment_text‘ ] [DATA DIRECTORY [=] ‘data_dir‘] [INDEX DIRECTORY [=] ‘index_dir‘] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name]
依据某个查询建立新表：
query_expression: SELECT ... (Some valid select or union statement)
对建表举例：
CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stu_num` varchar(30) NOT NULL, `stu_name` varchar(30) NOT NULL, `sex` enum(‘M‘,‘F‘) DEFAULT ‘M‘, `major` varchar(50) DEFAULT NULL, `grade` tinyint(4) DEFAULT NULL, --主键 PRIMARY KEY (`id`), --唯一约束 UNIQUE KEY `stu_num` (`stu_num`))--指定存储引擎和字符集ENGINE=InnoDB DEFAULT CHARSET=utf8;
依据某个查询建立新表：
CREATE TABLE students_clone SELECT * FROM students;
按照此方式建立的students_clone表表结构将与students一致，且数据一致。
CREATE TABLE students_clone_1 LIKE students;
按照此方式建立的students_clone表表结构将与students一致，新建的数据表为空。
5.删除数据表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]
DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心。
6.清空表数据
TRUNCATE [TABLE] tbl_name
TRUNCATE TABLE用于完全清空一个表。
MYSQL数据表操作语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。