MySQL 基础 （二）- 表操作

表数据类型

数据类型主要分为三种：数值类型、日期和时间类型、字符串类型。详情可参考MySQL 数据类型

创建表

• CREATE TABLE：使用此语句必须给出下列信息：

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

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

PRIMARY KEY (vend_id)

多个列组成的主键应该以逗号分隔的列表给出各列名，示例如下：

PRIMARY KEY (oreder_num, order_item)

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

• 自增量：AUTO_INCREMENT 告诉MySQL，本列每当增加一行时自动增量。每次执行一个 INSERT 操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。这样可以保证该列没有重复值，从而可以用作主键值。
• 约束：管理如何插入或处理数据库数据的规则，主键就是一种特殊的约束。除了主键约束之外，约束还有非空约束、唯一性约束、外键约束。详细规则请参考MySQL——约束(constraint)详解
• 列类型及大小：列类型在建表时需要进行指定，具体类型已在表数据类型中列出。而列的大小亦需要在建表时进行指定，详细规则请参考：mysql字段每个类型长度大小与建表的类型长度

添加数据

• 基本语法：INSERT 语句要求指定表名以及被插入到新行中的值，示例如下：

INSERT INTO 表名 VALUES (值1, 值2, 值3....);

这种语法虽然简单，但是并不安全，应该尽量避免使用。上面的语句高度依赖表中列的定义次序。安全的语句应当在插入时指定相对应的列名，示例如下：

INSERT INTO 表名(列名1, 列名2, 列名3...) VALUES(值1, 值2, 值3....);

• 插入多行：若有添加多行数据的需要，则可以以添入多行语句的形式进行添加。若多行所使用的列名均相同，可以用这种方式进行添加：

INSERT INTO 表名
(列名1, 列名2, 列名3...) VALUES
(值1, 值2, 值3....),
(值4, 值5, 值6....);

此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

删除表

• DROP：用法很简单，DROP TABLE 表名即可。执行该语句将永久删除该表
• DELETE：用法也很简单，DELETE FROM 表名即可。执行该语句将删除表中的数据，但是表的结构依然保留在数据库中
• TRUNCATE：用法与 DROP 类似，TRUNCATE TABLE 表名即可。执行该语句的作用与前面提到的 DELETE 一样。
• 三者的区别：前面已有提到与其他两种不同，DROP 是连着表结构一起删除，执行完成后数据库将不再存在这张表。TRUNCATE 专职于快速清空表，DELETE 则灵活的定位删除或清空整张表，更重要的是，DELETE 虽可以清空表但不会重置表水平线。具体体现在，执行完 DELETE 语句后再插入行，自增ID不会重置而是接着前面的计数继续加一

修改表

修改列名

• RENAME：格式如下：

RENAME TABLE 原表名 TO 新表名

更新数据

• UPDATE：该语句主要由三部分组成：
  • 要更新的表
  • 列名和他们的新值
  • 过滤条件

UPDATE 表名 SET 列名 = 新值 WHERE 过滤条件

更新多个列：

UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2 WHERE 过滤条件

删除行列

删除数据可以使用两个语句执行：DELETE 和 UPDATE。前者用于删除整行而不是删除列，后者则用于删除指定的列。

• DELETE：使用它有两种方式：删除特定的行和删除所有行。如果不是删除所有行，则需要注意不要忽略 WHERE 语句的使用。
• UPDATE：该语句本义是用于更新数据，可使用以下格式作删除用：

UPDATE 表名 SET 列名 = NULL

还可在该语句后加上 WHERE 语句进行条件筛选

新增行列

此处已在添加数据部分作出相应说明

作业

作业三

• 建表

CREATE TABLE `courses`  (
  `student` varchar(10) NOT NULL,
  `class` varchar(255) NOT NULL
);

• 插入数据

INSERT INTO courses ( `student`, `class` )
VALUES
	( 'A', 'Math' ),
	( 'B', 'English' ),
	( 'C', 'Math' ),
	( 'D', 'Biology' ),
	( 'E', 'Math' ),
	( 'F', 'Computer' ),
	( 'G', 'Math' ),
	( 'H', 'Math' ),
	( 'I', 'Math' ),
	( 'A', 'Math' );

• 查询

SELECT
	`class`
FROM
	`courses` 
GROUP BY
	`class` 
HAVING
	COUNT( DISTINCT `student` ) >= 5;

作业四

• 建表

CREATE TABLE `salary`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `salary` int(10) NOT NULL,
  PRIMARY KEY (`id`)
);

• 添加数据

INSERT INTO salary( `id`, `name`, `sex`, `salary` )
VALUES
	( 1, 'A', 'm', 2500 ),
	( 2, 'B', 'f', 1500 ),
	( 3, 'C', 'm', 5500 ),
	( 4, 'D', 'f', 500 );

• 交换数值

UPDATE salary 
SET sex =
CASE
	sex 
	WHEN 'm' THEN
	'f' ELSE 'm' END;