创建和操纵表

2023-12-27 13:23:39

创建表

创建表基础

为利用CREATE TABLE创建表，必须给出下列信息

新表的名字，在关键字CREATE TABLE之后给出
表列的名字和定义，用逗号分隔

创建customers表

CREATE TABLE CUSTOMERS
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
primary key(cust_id)
)ENGINE=InnoDB

mysql> CREATE TABLE CUSTOMERS
    -> (
    -> cust_id int NOT NULL AUTO_INCREMENT,
    -> cust_name char(50) NOT NULL,
    -> cust_address char(50) NULL,
    -> cust_city char(50) NULL,
    -> cust_state char(5) NULL,
    -> cust_zip char(10) NULL,
    -> cust_country char(50) NULL,
    -> cust_contact char(50) NULL,
    -> cust_email char(255) NULL,
    -> primary key(cust_id)
    -> )ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.33 sec)

mysql>

表名紧跟在create table后面，实际的表定义括在圆括号之中。各列之间用逗号分隔
表的主键使用PEIMARY KEY关键字指定，
在创建新表时，指定的表名必须不存在，否则将出错。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样只是查看表名是否存在，并且仅在表名不存在时创建它

使用NULL值

NULL值就是没有值或缺值，允许NULL值的列，就是允许在插入行时不给出该列的值
不允许NULL值的列在插入或更新行时，该列必须有值

理解NULL

不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。
如果指定‘’(两个单引号，其间没有字符，这在NOT NULL列是允许的

主键

主键值必须为1，即表中的每个行必须具有唯一的主键值
如果主键使用单个列，则它的值必须唯一，如果使用多个列，则这些列的组合值，必须唯一

单个列作为主键，其中主键用以下的类似的语句定义

PRIMARY KEY(vend_id)

为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名

CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
PRIMARY KEY(order_num, order_item)
)ENGINE=InnoDB

订单号（order_num)和订单物品(order_item)的组合是唯一的，从而适合作为主键，其定义为 PRIMARY KEY(order_num, order_item)

AUTO_INCREMENT

AUTO_INCREMENT告诉mysql，本列每增加一行时自动增量，每次执行一个INSERT操作时，MYSQL自动对该列增量（从而才有了AUTO_INCREMENT），给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id,从而可以用作主键值

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（通常使它成为主键）
可以通过SELECT last_insert_id()语句来获取最后一个AUTO_INCREMENT

指定默认值

如果在插入行时，没有给出值，mysql允许指定此时使用的默认值，默认值在CREATE TABLE时指定

CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
quantity int NOT NULL DEFAULT,
PRIMARY KEY(order_num, order_item)
)ENGINE=InnoDB

quantity列包含订单中每项物品的数量，
在此例子中，给该列的描述添加文本DEFAULT 1指示MYSQL,在未给出数量的情况下使用数量1

不允许函数：mysql不允许使用函数作为默认值，只支持常量
使用默认值，而不是NULL值。

引擎类型

你可能注意到，前面在创建表时一直都是使用ENGINE=InnoDB结束

mysql具有多种引擎，它打包了多个引擎，这些引擎都隐藏在DBMS中，全都能执行CREATE TABLE和SELECT等命令

查看默认使用的引擎

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

mysql>

查看支持的引擎
show engines;

查看某个表使用的引擎

mysql> show create table CUSTOMERS;
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                           | CUSTOMERS | CREATE TABLE `CUSTOMERS` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

需要知道的引擎

InnoDB是一个可靠的事物处理引擎，它不支持全文本搜索
MEMORY在功能等同于MyISAM，但是由于数据存储在内存中，速度很快（适合于临时表）
MyISAM是一个性能极高的引擎，他支持全文本搜索，但是不支持事务处理

外键不能跨引擎，即使用一个引擎的表不能引用使用不同引擎的表的外键

更新表

在理想状态下，当表中存储数据后，该表就不应该再被更新，在表的设计过程中需要花费大量时间来考虑，以便后去不对表进行大的改动
添加列或者删除列都应该在进行改动之前做一个完整的备份

添加一个列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

mysql> ALTER TABLE CUSTOMERS 
    -> ADD cust_phone CHAR(20);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

这条语句给CUSTOMERS表增加一个名为vend_phone的列，必须明确其数据类型

删除刚刚添加的列，可以这样

ALTER TABLE vendors
DROP COLUMN vend_phone

mysql> ALTER TABLE CUSTOMERS DROP COLUMN cust_phone;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句
DROP TABLE customers
这条语句删除customers表，没有确认，也不能撤销，执行这条语句将永久删除该表

mysql> DROP TABLE customers;
Query OK, 0 rows affected (0.03 sec)

mysql>

重命名表

将CUSTOMERS表名改为customers

RENAME TABLE CUSTOMERS TO customers；

mysql> RENAME TABLE CUSTOMERS TO customers;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_sumyum |
+------------------+
| customers        |
+------------------+
1 row in set (0.00 sec)

mysql>

对多个表重命名

RENAME TABLE backup_customers TO customers,
                            backup_vendors TO vendors,
                            backup_products TO products;

码农公寓

创建表