MySQL数据库表的基本操作——创建表CREATE TABLE

最新推荐文章于 2024-04-20 16:55:35 发布

netoscoder

最新推荐文章于 2024-04-20 16:55:35 发布

阅读量5.2w

点赞数 1

分类专栏： MySql 文章标签： mysql 创建表外键自增字段 create table

本文链接：https://blog.csdn.net/netoscoder/article/details/10716253

版权

MySql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

在操作数据库表之前要先选择数据库，USE <数据库名>

1，创建数据库表操作

查看帮助

mysql> \h create table

语法内容比较多，参照 CREATE TABLE语法

a). 直接创建

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

示例

mysql> create table book
    -> (
    -> id int(11),
    -> name varchar(45),
    -> price float
    -> );
Query OK, 0 rows affected (0.13 sec)

b). 复制表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

示例

mysql> create table tb1 select * from book;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

因为book表里没有记录，所以这里Records:0

c). 复制表结构

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

示例

mysql> create table tb2 like book;
Query OK, 0 rows affected (0.10 sec)

存在同名表时：

mysql> create table tb1(id int(10),t int(3));
ERROR 1050 (42S01): Table 'tb1' already exists
mysql> create table if not exists tb1(id int(10),t int(3));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(45) | YES  |     | NULL    |       |
| price | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create table tb2 select * from book;
ERROR 1050 (42S01): Table 'tb2' already exists
mysql> create table if not exists tb2 select * from book;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from tb2;
Empty set (0.00 sec)

mysql> select * from book;
+------+-------+-------+
| id   | name  | price |
+------+-------+-------+
|    1 | hhhhh |  34.2 |
+------+-------+-------+
1 row in set (0.00 sec)

小结：

表名称被指定为db_name.tbl_name，以便在特定的数据库中创建表。不论是否有当前数据库，都可以通过这种方式创建表。如果您使用加引号的识别名，则应对数据库和表名称分别加引号。例如，`mydb`.`mytbl`是合法的，但是`mydb.mytbl`不合法。

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

如果表已存在，则使用关键词IF NOT EXISTS可以防止发生错误。注意，原有表的结构与CREATE TABLE语句中表示的表的结构是否相同，这一点没有验证。注释：如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS，则不论表是否已存在，由SELECT部分选择的记录都会被插入。

以上这三段内容摘自MySql5.1的文档，第三段内容与实验结果不一致。如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS，如果表已存在，则由SELECT部分选择的记录也不会被插入

mysql> create table if not exists tb2 select * from book;

这一句可以证明，tb2和book的结构一致，book中有一条记录，结果却没有插入到tb2中

MySQL数据类型

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
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [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]
  | spatial_type

主键约束 PRIMARY KEY

mysql> create table tb3
    -> (id int(10) primary key,
    -> name varchar(20));

mysql> create table tb4
    -> (id int(10),
    -> name varchar(20),
    -> primary key (id));

联合主键

mysql> create table tb5
    -> (id int(10),
    -> name varchar(20),
    -> primary key(id, name));

外键约束

mysql>  create table tb7
    ->  (
    ->  id int(11),
    ->  b_id int (11),
    ->  name varchar(45),
    ->  primary key (id),
    ->  foreign key(b_id) references tb4(id)
    -> );

加入tb4.id不是主键，则会报150错误

mysql> create table tb8
    -> (id int(11) not null unique,
    -> name varchar(45));
Query OK, 0 rows affected (0.11 sec)

mysql> create table tb9
    -> (id int(11) primary key,
    -> f_id int(10),
    -> foreign key(f_id) references tb8(id)
    -> );
Query OK, 0 rows affected (0.09 sec)

上面两段代码：tb8的id没有定义为主键，但是有not null unique约束，不能为空，不能重复。在tb9中定义外键约束也可以

查看着两个表的结构发现，tb8.id已被设为主键。可以试验tb8.id只有unique约束时是否也能成功

mysql> desc tb8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(45) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc tb9;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| f_id  | int(10) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+

非空约束NOT NULL

唯一性约束 UNIQUE

mysql> create table tb10
    -> (id int (11),
    -> name varchar(20),
    -> unique(id));

UNIQUE约束允许有NULL值

默认值DEFAULT

自增字段AUTO_INCREMENT

mysql> create table tb11
    -> (id int(11) not null unique auto_increment,
    -> name varchar(20));

或者

mysql> create table tb11
    -> (id int(11) primary key auto_increment,
    -> name varchar(20));

查看表基本结构DESCRIBE

describe table_name;或desc table_name;

查看表的创建信息

show create table table_name [\G]

mysql> show create table tb11 \G
*************************** 1. row ***************************
       Table: tb11
Create Table: CREATE TABLE `tb11` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)