数据库表操作

来源：互联网发布：游戏淘宝网商城编辑：程序博客网时间：2024/06/07 20:42

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) }

建表语句内容比较多，先来一张图看看整体结构。

在创建表格时，您可以使用TEMPORARY关键词。只有在当前连接情况下，TEMPORARY表才是可见的。当连接关闭时，TEMPORARY表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被取消时为止。）您必须拥有CREATE TEMPORARY TABLES权限，才能创建临时表。

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]

1）一个整数列可以拥有一个附加属性AUTO_INCREMENT。当您向一个已编入索引的AUTO_INCREMENT列中插入一个NULL值（建议）或0时，此列被设置为下一个序列的值。通常情况下为value+1，此处value是当前在表中的列的最大值。AUTO_INCREMENT序列从1开始。为了让MySQL与部分ODBC应用软件相兼容，您可以使用以下查询方法找到最后一个插入行的AUTO_INCREMENT值：SELECT * FROM tbl_name WHERE auto_col IS NULL。

2）DEFAULT子句用于为列指定一个默认值。默认值必须为一个常数，不能为一个函数或一个表达式，有一种情况例外。例如，一个日期列的默认值不能被设置为一个函数，如NOW()或CURRENT_DATE。不过，有一种例外，您可以对TIMESTAMP列指定CURRENT_TIMESTAMP为默认值。BLOB和TEXT列不能被赋予默认值。

3）对于列的评注可以使用COMMENT选项来进行指定。评注通过SHOW CREATE TABLE和SHOW FULL COLUMNS语句显示。

4）在UNIQUE索引中，所有的值必须互不相同。

5）PRIMARY KEY是一个唯一KEY，一个表只有一个PRIMARY KEY。PRIMARY KEY可以是一个多列索引。但是，在列规约中使用PRIMARY KEY关键字属性无法创建多列索引。这么做只能把一个列标记为主列。您必须使用一个单独的PRIMARY KEY（index_col_name, ...）子句。

实数据类型

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用于完全清空一个表。

0 0