菜鸟的MySQL学习之旅（三）—表操作

1. MySQL表数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。在菜鸟教程有详细解释：数据类型
数值类型：

时间类型：
字符串类型：

2. 使用SQL语句创建表

• 语句解释

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
) engine=table_type;

(1) 首先，指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。 如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。
(2) 其次，在column_list部分指定表的列表。字段的列用逗号(，)分隔。
(3) 第三，需要为engine子句中的表指定存储引擎。可以使用任何存储引擎，如：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

• 设定列类型，大小，约束，主键

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
[AUTO_INCREMENT]

(1) column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。
(2) NOT NULL或NULL表示该列是否接受NULL值。
(3) DEFAULT值用于指定列的默认值。
(4) AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。

PRIMARY KEY (col1,col2,...)

将表的特定列设置为主键

• 实例
  使用CREATE TABLE语句创建这个tasks表

mysql> USE test01;
Query OK, 0 rows affected

mysql> CREATE TABLE IF NOT EXISTS tasks (
    task_id INT(11) AUTO_INCREMENT,
    subject VARCHAR(45) DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    description VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (task_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

3. 向表中添加数据

• 简单插入

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);

INSERT语句允许您将一行或多行插入到表中，
在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表；将括号内的相应列的逗号分隔值放在VALUES关键字之后。

/*示例*/
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');

• 插入多行

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

/*插入多行*/
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

注意：如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表，如下所示

INSERT INTO table
VALUES (value1,value2,...),
       (value1,value2,...),
...;

• 使用select子句复制表

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

（1）通过复制tasks表的结构，创建一个名为tasks_bak的新表，如下所示：

CREATE TABLE tasks_bak LIKE tasks;

（2）使用以下INSERT语句将tasks表中的数据插入tasks_bak表：

INSERT INTO tasks_bak
SELECT * FROM tasks;

4. 用SQL语句删除表

• DELETE

DELETE FROM table_name [WHERE Clause]

（1）如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
（2）可以在 WHERE 子句中指定任何条件

• TRUNCATE

TRUNCATE TABLE table_name;

删除表内所有数据，但保留表结构

• DROP

DROP TABLE table_name ;

删除所有数据和表结构

• 区别
  delete 和 truncate 仅仅删除表数据，且delete可以用where限定删除部分记录，drop 连表数据和表结构一起删除

5. 用SQL语句修改表

• 修改列名
  ALTER TABLE语句来更改现有表结构(如添加或删除列，更改列属性等)。具体语法如下：

ALTER TABLE table_name action1[,action2,…]

修改表名

ALTER TABLE table_name
RENAME TO 新表名;

修改列名及列属性

ALTER TABLE table_name
CHANGE COLUMN 列表名 新列表名 列表其他属性;

• 修改表中数据

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;

首先，在UPDATE关键字后面指定要更新数据的表名。
其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。
第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行

• 删除行和新建行
  使用where子句来选定所要删除的行

DELETE FROM table_name [WHERE Clause]

插入新行

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);

• 删除列和新建列
  ALTER TABLE语句来更改现有表结构(如添加或删除列，更改列属性等)。具体语法如下：

ALTER TABLE table_name action1[,action2,…]

向表中添加列

ALTER TABLE table
ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
...;

删除列：

ALTER TABLE table
DROP COLUMN column_1,
DROP COLUMN column_2,
…;

6. 实践项目

6.1 超过5名学生的课

创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。
例如,表:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math
A	Math

编写一个 SQL 查询，列出所有超过或等于5名学生的课。
应该输出:

class
Math

Note:
学生在每个课中不应被重复计算。

/*建立表结构*/
CREATE TABLE IF NOT EXISTS courses (
    id INT(11) AUTO_INCREMENT,
    student char(4) DEFAULT NULL,
    class VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入数据*/
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');
/*结果：*/
+---------+----------+
| student | class    |
+---------+----------+
| 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;

结果：

6.2 交换工资

创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。
例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

/*建立表结构*/
CREATE TABLE IF NOT EXISTS salary (
    id INT(11) AUTO_INCREMENT,
    name char(4) DEFAULT NULL,
    sex char(4) DEFAULT NULL,
    salary int(11) DEFAULT NULL,
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入数据*/
INSERT INTO salary(id,name,sex,salary)
VALUES (1,'A','f','2500'),
(2,'B','m','1500'),
(3,'C','f','5500'),
(4,'D','m','500');
/*结果：*/
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  2 | B    | m   |   1500 |
|  3 | C    | f   |   5500 |
|  4 | D    | m   |    500 |
+----+------+-----+--------+

/*查询命令*/
UPDATE salary
SET sex = 
    CASE WHEN sex = 'f' THEN 'm'
         ELSE 'f'
    END;

SELECT * 
FROM salary;

结果：