MySQL学习笔记（三）

1 MySQL表数据类型

参考自：http://www.runoob.com/mysql/mysql-data-types.html

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.1 数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

1.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

1.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

2 用SQL语句创建表

参考自：http://www.w3school.com.cn/sql/sql_constraints.asp
SQL 不仅用于表数据操纵，还用来执行数据库和表的所有操作，包括表
本身的创建和处理。

一般有两种创建表的方法：
● 多数 DBMS 都具有交互式创建和管理数据库表的工具；
● 表也可以直接用 SQL 语句操纵。

用程序创建表，可以使用 SQL 的 CREATE TABLE 语句。需要注意的是，使
用交互式工具时实际上就是使用 SQL 语句。这些语句不是用户编写的， 界
面工具会自动生成并执行相应的 SQL 语句（更改已有的表时也是这样）。

2.1 语句解释

利用 CREATE TABLE 创建表，必须给出下列信息：
● 新表的名字，在关键字 CREATE TABLE 之后给出；
● 表列的名字和定义，用逗号分隔；
● 有的 DBMS 还要求指定表的位置。

mysql> CREATE TABLE Products
(
	prod_id CHAR(10) NOT NULL,
	vend_id CHAR(10) NOT NULL,
	prod_name CHAR(254) NOT NULL,
	prod_price DECIMAL(8,2) NOT NULL,
	prod_desc VARCHAR(1000) NULL
);

2.2 设定列类型 、大小、约束

2.2.1 设定列类型 （字段类型 ）

# 添加列
	ALTER TABLE table_name ADD column_name datatype;

# 修改列类型
	ALTER TABLE table_name ALTER COLUMN column_name datatype;

# 删除列
	ALTER TABLE tableName DROP COLUMN columnName;

2.2.2 设定列大小（更改字段类型长度）

# 示例
	ALTER TABLE tableName ALTER COLUMN columnName VARCHAR(n);

2.2.3 设定列约束（其中包括：设定主键）

约束用于限制加入表的数据的类型。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

（1）NOT NULL 约束：

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

# 示例：（NOT NULL）
	CREATE TABLE Persons
	(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
	)

（2）DEFAULT 约束：

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。

# 示例：（DEFAULT）
	CREATE TABLE Persons
	(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255) DEFAULT 'Sandnes'
	)

（3）UNIQUE 约束：

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
注意： 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

# 示例：（UNIQUE）
	CREATE TABLE Persons
	(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	UNIQUE (Id_P)
	)

（4）PRIMARY KEY 约束：（即：设定主键）

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

# 示例：（PRIMARY KEY）
	CREATE TABLE Persons
	(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (Id_P)
	)

（5）FOREIGN KEY 约束：

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

# 示例：（FOREIGN KEY）
	CREATE TABLE Orders
	(
	Id_O int NOT NULL,
	OrderNo int NOT NULL,
	Id_P int,
	PRIMARY KEY (Id_O),
	FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
	)

（6）CHECK 约束：

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

# 示例：（CHECK）
	CREATE TABLE Persons
	(
	Id_P int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	CHECK (Id_P>0)
	)

2.3 设定主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

表中任意列只要满足以下条件，都可以用于主键。
● 任意两行的主键值都不相同。
● 每行都具有一个主键值（即列中不允许 NULL 值）。
● 包含主键值的列从不修改或更新。（大多数 DBMS 不允许这么做，但
如果你使用的 DBMS 允许这样做，好吧，千万别！）
● 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

方法一：
一种定义主键的方法是创建它，如下所示。

mysql> CREATE TABLE Vendors
(
	vend_id CHAR(10) NOT NULL PRIMARY KEY,
	vend_name CHAR(50) NOT NULL,
	vend_address CHAR(50) NULL,
	vend_city CHAR(50) NULL,
	vend_state CHAR(5) NULL,
	vend_zip CHAR(10) NULL,
	vend_country CHAR(50) NULL
);

navicat 结果显示如下

方法二：

mysql> ALTER TABLE Vendors
    -> ADD CONSTRAINT PRIMARY KEY (vend_name);

这里定义相同的列为主键，但使用的是 CONSTRAINT 语法。此语法也可以用于 CREATE TABLE 和 ALTER TABLE 语句。
注意不能重复定义主键，需要先删除原来的主键才能重新定义。

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

参考自：http://www.runoob.com/sql/sql-insert.html

3.1 语句解释

INSERT INTO 语句用于向表格中插入新的行。

3.2 多种添加方式（指定列名；不指定列名）

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

# 写法：
	INSERT INTO table_name
	VALUES (value1,value2,value3,...);

# 示例：往 Persons 表中插入 值 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing'
	INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

第二种形式需要指定列名及被插入的值：

# 写法：
	INSERT INTO table_name (column1,column2,column3,...)
	VALUES (value1,value2,value3,...);

# 示例：往 Persons 表中的 LastName, Address 列分别插入值 'Wilson', 'Champs-Elysees'
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

4 用SQL语句删除表

参考自：http://www.w3school.com.cn/sql/sql_drop.asp

4.1 语句解释

SQL DROP TABLE 语句用于移除表定义以及表中所有的数据、索引、触发器、约束和权限设置。

注意： 使用此命令应当特别小心，因为数据表一旦被删除，表中所有的信息就会永久丢失。

4.2 DELETE

DELETE 语句用于删除表中的记录。

# 删除表：
	DROP TABLE table_name;
	
# 删除表中的行：
	DELETE FROM table_name
	WHERE some_column=some_value;

4.3 DROP

DROP INDEX 语句用于撤销索引、撤销表以及撤销数据库。

# 删除表格中的索引：
	ALTER TABLE table_name DROP INDEX index_name

# 删除表：
	DROP TABLE table_name

# 删除数据库：
	DROP DATABASE database_name

4.4 TRUNCATE

SQL TRUNCATE TABLE 命令用于删除现有数据表中的所有数据。

# 用法：
	TRUNCATE TABLE  table_name;

4.5 不同方式的区别

参考自：http://www.runoob.com/note/22984

简单来说，它们之间的相同点与不同点如下。

相同点：
drop、delete、truncate 都是删除表的内容。

不同点：
delete：删除表的内容，表的结构还存在，不释放空间可以回滚恢复；
drop：删除表内容和结构，释放空间，没有备份表之前要慎用；
truncate：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用。

5. 用SQL语句修改表

5.1 修改列名

	ALTER TABLE table_name CHANGE column_name column_name_new column_type

5.2 修改表中数据

	UPDATE table_name SET field1=new-value1, field2=new-value2
	[WHERE Clause]

5.3 删除行

	DELETE FROM table_name
	WHERE some_column=some_value;

5.4 删除列

	ALTER TABLE table_name 
	DROP COLUMN column_name

5.5 新建列

	ALTER TABLE table_name
	ADD column_name datatype

5.6 新建行

# 写法：
	INSERT INTO table_name
	VALUES (value1,value2,value3,...);

# 示例：往 Persons 表中插入 值 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing'
	INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

4 实战

4.1 实战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:
学生在每个课中不应被重复计算。

# 创建表
	mysql> CREATE TABLE courses (
	    -> student VARCHAR(50) NOT NULL,
	    -> class VARCHAR(50) NULL
	    -> );
	
# 插入数据
	mysql> 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');

# 列出所有超过或等于5名学生的课
	mysql> SELECT class
	    -> FROM courses
	    -> GROUP BY class
	    -> HAVING COUNT(DISTINCT student)>=5;  --DISTINCT 关键字去除查询结果中的重复记录

4.2 实战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    |

# 创建表
	mysql> 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
	    -> );
	    
# 插入数据
	mysql> INSERT INTO salary (id, name, sex, salary) VALUES (1,'A','m',2500), 
		-> (2,'B','f',1500), (3,'C','m',5500), (4,'D','f',500);

# 更新查询
	mysql> UPDATE salary SET sex = (
	    -> CASE
	    -> WHEN 'sex' = 'f' THEN 'm'
	    -> ELSE 'm' END);
	    

欢迎关注个人公众号

厚颜自荐一下~~

HUST准研究生
方向：CV
个人公众号：三木小小推

主要内容：
1.推送三木精选的科技信息
2.推送三木编写的算法程序
3.推送三木收藏的音乐视频
4.提供保研、建模及其他咨询服务
5.提供每日个人计划打卡与分享服务（可提供模糊计划与完成度以保证隐私）

欢迎各位订阅~~

本文学习自：https://blog.csdn.net/weixin_42970195/article/details/89005356 诚谢！