MySQL必知必会—表操作篇

方旭

MySQL必知必会—概念篇

MySQL必知必会—安装应用篇

MySQL必知必会—检索数据篇

MySQL必知必会—联结表和高级查询篇

MySQL必知必会—文本搜索篇

下面用到的数据库文件可在 mysql_scripts 找到。


创建和操纵表

创建表

利用 CREATE TABLE 创建表,必须紧跟着给出新表的名字,然后是表列的名字和定义,用逗号分隔。

NULL 值

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。 NOT NULL 即不允许 NULL 值的列不接受该列没有值的行,在插入或更新行时,该列必须有值。NULL 是默认设置,如果不指定 NOT NULL,则认为指定的是 NULL。

主键

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

使用类似 PRIMARY KEY (id) 的语句来定义。为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名,例如 orderitems 表的创建用到的 PRIMARY KEY (order_num, order_item)

主键可以在创建表时定义,或者在创建表之后定义。

主键为唯一标识表中每个行的列,主键中只能使用不允许 NULL 值的列。

AUTO_INCREMENT

每次执行一个 INSERT 操作时, MySQL 自动对该列增量。

每个表只能允许一个 AUTO_INCREMENT 列,而且它必须被索引(比如通过使用它成为主键)

在执行 INSERT 时可以给 AUTO_INCREMENT指定一个值,只要它是至今为止唯一的就行,该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

last_insert_id() 这个函数能返回最后一个 AUTO_INCREMENT 值

指定默认值

列定义中的 DEFAULT 关键字指定。 MySQL 跟大多数 DBMS 一样, 不允许使用函数作为默认值,它只支持常量。

引擎类型

大多数时候, CREATE TABLE 语句全都以 ENGINE=InnoDB 语句结束。MySQL具有多种引擎,这些打包的多个引擎都隐藏在 MySQL 的服务器内,全都能执行 CREATE TABLE 和 SELECT 等命令。这些引擎具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。

InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索。

MyISAM 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理

MEMORY 在功能等同于 MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合用于临时表)

########################
# 看一下 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;

更新表

为了更新表定义,可使用 ALTER TABLE 语句。

### vendors 表中增加 vend_phone 列
ALTER TABLE vendors ADD vend_phone CHAR(20);

### 删除刚刚增加的 vend_phone 列
ALTER TABLE vendors DROP COLUMN vend_phone;

### ALTER TABLE 常见的用途就是定义外键
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id)

删除表

DROP TABLE customers2;

重命名表

### 重命名一个表
RENAME TABLE customers2 TO customers;

### 重命名多个表
RENAME TABLE backup_customers TO customers,backup_vendors TO vendors;

注意

  • 创建新表时,指定的表名必须不存在,否则将出错。如果仅想在一个表不存在时创建它,应该在表名后面给出 IF NOT EXISTS。
  • 使用 ALTER TABLE 要极为小心,应该在进行改动之前做一个完整的备份(模式和数据的备份)


插入数据

插入完整的行

### 如下的语句中,对每个列必须提供一个值,如果某个列没有值,应该使用 NULL 值(假设表允许对该列指定空值)。每个列必须按照顺序给出,自动增量的值也不能忽略,而且如果不想赋值,就需要指定为 NULL 。
INSERT INTO customers VALUES( NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

上面的语法应该避免使用,因为不安全,建议用下面的语句,可以不按照次序填充,只要保证 VALUES中的次序跟前面给出的列名次序一致就行。

INSERT INTO customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', NULL, NULL, '100 Main Street', 'Los ANGELES', 'CA', '90046', 'USA');

插入多个行

### 可以使用多条 INSERT 语句,甚至一次提交它们,每条语句用一个分号结束。
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA');INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

### 或者每条 INSERT 语句中的列名(和次序)相同,可以如下组合语句
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA'), ('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

注意

  • 在 INSERT 操作中可以省略某些列,省略的列必须满足:该列定义为允许 NULL 值(无值或者空值),或在表定义中给出默认值,这表示如果不给出值,将使用默认值,否则插入时省略会报错。
  • 可以使用 INSERT LOW PRIORITY INTO 来降低插入语句的优先级。


更新数据

### 更新id是10009客户名字的邮箱。
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10009;

### 为了删除某个列的值,可设置它为 NULL(假定表定义为允许 NULL 值)
UPDATE customers SET cust_email = NULL WHERE cust_id = 10009;

注意

  • 使用 UPDATE 时,一定不能省略 WHERE 子句,否则就会更新表中的所有行。
  • UPDATE 操作如果报错,则整个UPDATE操作被取消,错误发生前更新的所有行被恢复到它们原来的值,如果想发生错误的时候也继续进行更新,可以使用 IGNORE 关键字 UPDATE IGNORE customers


删除数据

### 删除10011这一行
DELETE FROM customers WHERE cust_id = 10011;

注意

  • 使用 DELETE 时,一定不能省略 WHERE 子句,否则就会删除表中的所有行。即使删除所有行, DELETE 也不会删除表本身。
  • 如果想从表中删除所有行,不要使用 DELETE。可以使用 TRUNCATE TABLE 语句,速度更快(TRUNCATE 实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据)。

一个持续更新的github笔记,链接地址:Front-End-Basics,可以watch,也可以star。

此篇文章的地址:MySql必知必会

阅读 501

陈工移山
移山之志,一点一滴。

移山之志,一点一滴。

3.2k 声望
435 粉丝
0 条评论
你知道吗?

移山之志,一点一滴。

3.2k 声望
435 粉丝
宣传栏