MySQL学习记录（三）—— 表操作

MySQL表数据类型

参考 菜鸟教程—MySQL 数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

• 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 字节	(-8388608，8 388607)	(0，16777215)	大整数值
INT或INTEGER	4 字节	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 字节	(-9223372036854775808，9223372036854775807)	(0，18446744073709551615)	极大整数值
FLOAT	4 字节	(-3.402823466 E+38，-1.175494351 E-38)，0，(1.175494351 E-38，3.402823466351 E+38)	0，(1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.7976931348623157 E+308，-2.2250738585072014 E-308)，0，(2.2250738585072014 E-308，1.7976931348623157 E+308)	0，(2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

• 日期和时间类型

类型	大小(字节)	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 字符串类型

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

用SQL语句创建表

一般有两种创建表的方法：

1. MySQL 具有交互式创建和管理数据库表的工具（如 Workbench）
2. 表也可以直接用 SQL 语句创建

用程序创建表，可以使用 SQL 的 CREATE TABLE 语句。 利用 CREATE TABLE 创建表，必须给出下列信息：

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

 CREATE TABLE table_name 
 (
      column_name1 column_type,
      column_name2 column_type,
      column_name3 column_type
 );

设定列类型和大小：

数据类型	描述
integer(size)， int(size)，smallint(size)，tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)，numeric(size,d)	容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

• 如果不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
• AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY 关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。

实例演示： 创建名为 “Person” 的表。该表包含 5 个列，列名分别是：“Id”、“LastName”、“FirstName”、“Address” 以及 “City”，其中 Id 为主键，LastName 和 FirstName 不允许为空：

 CREATE TABLE Persons
 (
      Id INT UNSIGNED AUTO_INCREMENT,
      LastName VARCHAR(255) NOT NULL,
      FirstName VARCHAR(255) NOT NULL,
      Address VARCHAR(500),
      City VARCHAR(255),
      PRIMARY KEY (Id)
 );

用SQL语句向表中添加数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

不指定列名：

 INSERT INTO table_name VALUES ( value1, value2, ..., valueN );

指定所要插入数据的列名：

 INSERT INTO table_name ( column_name1, column_name2, ..., column_nameN )
                        VALUES
                        ( value1, value2, ..., valueN );   

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

用SQL语句删除表

• DROP TABLE table_name：
  删除表的全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM；

• TRUNCATE TABLE table_name ：
  删除表的全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM；

• DELETE FROM table_name ：
  在不删除表的情况下删除所有的行，表的结构、属性和索引都是完整的，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间；

• DELETE FROM table_name WHERE Clause：
  带条件的删除，删除指定的行，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间；

DELETE，DROP，TRUNCATE 都有删除表的作用，区别在于：

• DELETE 和 TRUNCAT 仅仅删除表数据，DROP 连表数据和表结构一起删除

• DELETE 是 DML 语句，操作完以后如果没有提交事务还可以回滚， TRUNCATE 和 DROP 是 DDL 语句，操作完马上生效，不能回滚

• 执行的速度上，DROP > TRUNCATE > DELETE

• 当你不再需要该表时， 用 DROP；当你仍要保留该表，但要删除所有记录时， 用 TRUNCAT；当你要删除部分记录时， 用 DELETE 。

用SQL语句修改表

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

• 添加列：

 ALTER TABLE table_name
     ADD column_name datatype;

• 删除列：

 ALTER TABLE table_name 
     DROP COLUMN column_name;

• 修改列，修改列类型：

 ALTER TABLE table_name
     ALTER COLUMN column_name datatype;
        
 ALTER TABLE able_name
     MODIFY column_name datatype;

• 修改列，修改列名称：

 ALTER TABLE table_name 
     CHANGE old_column_name new_column_name datatype;

• 修改表中数据：

 UPDATE table_name 
     SET
         column_name1 = new_value1, 
         column_name2 = new_value2
     [WHERE Clause];

注：当需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

• 删除行：

 DELETE FROM table_name WHERE Clause;

• 新建行：

 INSERT INTO table_name VALUES ( value1, value2, ..., valueN );

小练习

项目三：超过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：学生在每个课中不应被重复计算。

 -- 创建表
 USE datawhale;
 CREATE TABLE  IF NOT EXISTS `courses` 
 (
     `student` VARCHAR(10) NOT NULL,
     `class` VARCHAR(20) NOT NULL
 );
 -- 插入数据
 INSERT INTO  courses 
     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;

项目四：交换工资（难度：简单）

创建一个 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

# 创建表
 USE datawhale;
 CREATE TABLE salary 
 (
	 id INT NOT NULL PRIMARY KEY,
	 name VARCHAR(50) NULL,
	 sex VARCHAR(2) NULL CHECK (sex IN ('f', 'm')),
	 salary INT NULL
 );
-- 插入数据
 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 = IF (sex = 'f', 'm', 'f');

由于 MySQL 运行在 safe-updates 模式下，上述代码出现报错： Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences

具体解决方法请看 Error Code: 1175. You are using safe update