创建和操纵表

创建表

创建表基础

为利用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;

转载于:https://www.cnblogs.com/inmeditation/p/11601261.html

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页