MySQL 基础 （二）- 表操作

0.引言

本文介绍MySQL中有关表操作的基础知识。

1.MySQL表数据类型

1.1 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

1.2 日期和时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

1.3 字符类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

2. 用SQL语句创建表

通用语法

CREATE TABLE table_name（column_name，column_type）;
#创建含有数据类型为column_type的column_name这一列的数据表table_name;

设定
列类型：见前文数据类型；
大小：数据类型分为定长和变长数据类型，变长数据类型可以控制大小，如VARCHAR（n），n可取1-65535；
约束：
NOT NULL：在操作数据库时如果输入该字段的数据为NULL ，就会报错；
AUTO_INCREMENT：定义列为自增的属性，一般用于主键，数值会自动加1；
主键：
PRIMARY KEY：定义列为主键，可以使用多列来定义主键，列间以逗号分隔；
ENGINE：设置数据库所用引擎；
CHARSET：设置使用编码。

CREATE TABLE IF NOT EXISTS `human`(
`human_id` INT UNSIGNED AUTO_INCREMENT,
`human_essay` VARCHAR(100) NOT NULL,
`human_name` VARCHAR(40) NOT NULL,
`birth_date` DATE,
  PRIMARY KEY ( `human_id` )
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 用SQL语句向表中添加数据

INSERT INTO table_name ( field1, field2,...fieldN )
                   VALUES
                   ( value1, value2,...valueN );
#向table_name表中添加一行数据，新建一行；

INSERT INTO table_name
                   VALUES
                  ( value1, value2,...valueN );
#所有列都要添加数据时可以不指定列名。

4. 用SQL语句删除表

DROP TABLE table_name;
TRUNCATE TABLE table_name;
DELETE FROM table_name WHERE condition;

1. DROP 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
2. TRUNCATE 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM
3. DELETE：
   不加WHERE条件时，删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间 ；
   加WHERE条件时，带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间。

5. 用SQL语句修改表

5.1 UPDATE和DELETE

更新数据

UPDATE table_name
SET column=1
WHERE column=2;
--修改table_name表中column列的值，原来为2的行的值修改为1；
--若没有WHERE子句，则修改column的所有值为1；
--删除某列中的值，可以用column=NULL。

删除数据

DELETE FROM table1
WHERE condition;
--删除符合condition条件的表table1中的所有行的数据；
--WHERE语句没有时，删除表中所有行的数据；
--以列为单位删除数据可以用UPDATE语句。

5.2 ALTER TABLE

ALTER TABLE table_name
ADD column_name column_type;
--新建列

ALTER TABLE table_name
DROP column_name;
--删除列

ALTER TABLE table_name
MODIFY column_name new_type NOT NULL DEFAULT default_vlaue;
--修改列数据类型并设置默认值，不设默认值默认为NULL

ALTER TABLE table_name
CHANGE column_name new_column_name new_column_type;
--修改列名和数据类型

ALTER TABLE table_name
RENAME AS new_table_name;
--修改表名

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

代码：

#创建表
CREATE TABLE IF NOT EXISTS courses(
student varchar(5) NOT NULL,
class  varchar(10) NOT NULL
);
#导入数据
INSERT INTO courses VALUES("A","Math");
INSERT INTO courses VALUES("B","English");
INSERT INTO courses VALUES("C","Math");
INSERT INTO courses VALUES("D","Biology");
INSERT INTO courses VALUES("E","Math");
INSERT INTO courses VALUES("F","Computer");
INSERT INTO courses VALUES("G","Math");
INSERT INTO courses VALUES("H","Math");

结果：

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

class
Math

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

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

代码：

#创建数据库
CREATE TABLE IF NOT EXISTS salary (
id INT NOT NULL,
NAME VARCHAR (5) NOT NULL,
sex VARCHAR (5) NOT NULL,
salary INT 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);

结果：

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

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 sex
WHEN 'f' THEN 'm'
WHEN 'm' THEN 'f'
END;
SELECT * FROM salary;

结果：