MySQL笔记（MySQL入门）

至今为止，关系数据库 是最常用的数据库类型：

• 它是建立在关系模型上，其最基本的结构是 表
• 表中的每一列都有唯一的名称，列也叫做 域 或者 属性，每一行有对应的列的值组成，每个值必须与该列定义的数据类型相同
• 标志列 称为 键 或者 主键，主键用于标识表格中唯一的一条行记录
• 外键 就是其他表中的 主键，它用于引用其他表中的主键，从而获取相应的数据，通过外键关联，就可以实现 一对一 / 一对多 / 多对多 的关系

数据库整套表格的完整设计称为数据库的 模式，它是数据库的设计蓝图，这个模式应该表示表格及表格的列、每个表的主键和外键。一个模式不会包含任何数据。

设计数据库时，需要注意以下几点：

• 避免保存亢余数据，理由除了空间浪费外，还可能会导致数据更新的不一致，也就是说，在修改数据库之后容易产生数据不一致，数据的完整性将被破坏，
• 避免多个空属性的设计，它会大大地浪费空间同时还可能导致误会。

1 使用MySQL、创建用户、设置用户权限及数据库的操作

1.1 安装MySQL

参考文章：《MySQL8.0.12 安装及配置》

在官网下载完mysql，window系统需要在环境变量里添加路径，例如添加 C:\Program Files\MySQL\MySQL Server 8.0\bin。添加完路径之后就可以在命令行输入 mysql -u username -p 以进入MySQL了。
安装完 MySQL 之后，需要输入以下命令，以确保数据库 character_set_database 和 character_set_server 两个属性都是UTF-8，以防数据乱码：

show variables like '%char%';

UTF-8编码可能2个字节、3个字节、4个字节的字符，但是MySQL的UTF-8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。如果直接往采用UTF-8编码的数据库中插入表情数据，Java程序中将报SQL异常，此时需要将数据库编码改为 utf8mb4。

参考文章：《更改MySQL数据库的编码为utf8mb4》

1.2 创建操作用户及设置权限

创建用户可以使用 create user username identified by password 的语句；
而授予用户权限可以使用 grant 命令，其语法是：

GRANT privileges
on item
TO user_name
[WITH [GRANT OPTION | limit_options]]

其中：

• privileges：表示设置的用户权限，包括：
  • SELECT
  • INSERT
  • UPDATE：允许修改表里数值
  • DELETE
  • INDEX：允许创建和拖动特定表索引
  • ALTER：允许改变现存的结构，如可添加列，重命名列或表，修改猎德数据类型
  • CREATE：允许创建数据库或表
  • DROP：允许删除数据库或表
• item：表示权限所应用的数据库或表，形式如 dbname.*，可以将项目制定为 *.*，即将权限应用到所有数据库
• GRANT OPTION：表示该用户也有创建新用户的权利

1.3 创建数据库并进入

创建数据库:

create datebase dbname;

使用数据库：

use dbname;

1.4 删除数据库

drop database dbname;

2 SQL

SQL的全称是Structured Query Language，它是访问 关系数据库管理系统 的标准语言，它分为：

• DDL（Data Definition Language）：创建表/删除表/修改表结构
• DQL（Data Query Language）：查询记录
• DML（Data Manipulation Language）：添加/删除/更新记录

SQL 的关键字不区分大小写

2.1 MySQL 的数据类型

数据类型大致分为：数字、日期、时间以及字符串，其中每个类型又包含了多个类型。

2.1.1 字符串类型

字符串类型分为三类：

• 第一类是普通字符串，即小段文本，包括 char （固定长度字符）类和 varchar （可变长度字符）类：
  • char[(n)] 可以指定每种类型的长度 n，存储时会用空格符填补空白，但读取的时候又会取消掉多余的空格符，对应的Java类型是 String
  • varchar 列宽会随着数据的大小而改变，在存储数据的时候会过滤掉多余的空格，对应的Java类型是 String
• 第二类为 TEXT 和 BLOB 类型，它们大小可变，适用于长文本或二进制数据，BLOB 全称为 大二进制对象，它支持任何数据如图像或声音数据，而 TEXT 类型与 BLOB 的不同在于，TEXT 类型区分大小写，对应的Java类型是 String：
  • TINYBLOB：255
  • TINYTEXT：255
  • BLOB：65535
  • TEXT：65535
  • MEDIUMBLOB：16777215
  • MEDIUMTEXT：16777215
  • LONGBLOB：4294967295
  • LONGTEXT：4294967295
• 第三类包括两种特殊类型，SET 或 ENUM

2.1.2 数字类型

数字类型分为整数和浮点数。使用浮点数的时候可以指定显示宽度以及小数点后的位数。如：

create table books {
	price floate(4, 2)
}
// 前者数字表示数据宽度，后者表示小数点后位数

整数的类型：

• tinyint[m] / BIT / BOOL：取值范围是 -127~128 或 0~255
• smallint[m]：取值范围是 -32768~32767 或 0~65535
• mediumint[m]：取值范围 -8388608~8388607 或 0~16777215
• int[m] / integer[m]：取值范围 -2^31~2^31-1 或 2^32-1（int 有4个字节，1个字节是8个二进制串，所以4个字节就是32个二进制串，即可表示的最大数是 2^32-1 = 2147483647）
• bigint[m]：取值范围 -2^63~2^63-1 或 2^64

其中 m 值是用于 zerofill 的，即当存储的字符长度小于 m 值时，只有在设置了 zerofill 用0来填充，才能够看到效果，换句话就是说，没有 zerofill， m 值就是无用的。具体参考文章《MySQL中int(M)和tinyint(M)数值类型中M值的意义》

浮点的类型：

• flpat(精度)：取决于精度
• float[(M, D)]：类似于 float(4)
• double[(M, D)]：类似于 float(8)

2.1.3 日期和时间类型

使用以下这些类型，可以以字符串或数字的格式输入数据：

• date：1000-01-01~9999-12-31，对应java的 java.sql.Date
• time：-838:59:59~838:59:59
• datetime：1000-01-01 00:00:00~9999-12-31 23:59:59，对应java的 java.util.Date
• year[(2|4)]：70~69(1970~2069) 或 1901~2155
• timestamp[(m)]：其中根据m的数值变化而变化
  • 默认：YYYYMMDDHHMMSS
  • timestamp(2)：YY
  • timestamp(4)：YYMM
  • timestamp(6)：YYMMDD
  • timestamp(8)：YYYYMMDD
  • timestamp(10)：YYMMDDHHMM
  • timestamp(12)：YYMMDDHHMMSS
  • timestamp(14)：YYYYMMDDHHMMSS

MySQL Date 函数：

NOW()：返回当前的日期和时间
CURDATE()：返回当前的日期
CURTIME()：返回当前的时间
DATE_ADD(date,INTERVAL expr type)：给日期添加指定的时间间隔
DATE_SUB(date,INTERVAL expr type)：从日期减去指定的时间间隔
DATEDIFF(date1,date2)：返回两个日期之间的天数
DATE_FORMAT(date,format)：用不同的格式显示日期/时间

SQL Server Date 函数：

GETDATE()：返回当前日期和时间
DATEPART(datepart,date)：返回日期/时间的单独部分
DATEADD(datepart,number,date)：在日期中添加或减去指定的时间间隔
DATEDIFF(datepart,startdate,enddate)：返回两个日期之间的时间
CONVERT()：用不同的格式显示日期/时间

3 DDL

3.1 创建表

创建一个表的语法大致如下：

create table tableName {
	id int not null auto_increment primary key,
	....
};

其中要注意的除了要命名主键外，还有声明列属性的关键字

3.1.1 关键字

• not null：表示该列属性必须有一个值
• AUTO_INCREMENT：它是一个特殊特性，在整数 列中使用，它的用途是当往该列表中插入数据时，如果该列为空，则自动生成一个比本列中最大值更要大的数值，每个表中只能有一个这样的值，且指定该关键字的列一定是索引列
• primary key：主键，还可以单独提出来，使用 primary key(a, b) 的形式定义多个主键
• unsigned：表示只能是整数或者0

3.2 删除表

drop table tableName;

3.3 修改已经创建的表

有时候还需要修改数据库中的表的结构，此时要使用 alter table 语句

alter table [ignore] tablename alteration [, alteration ...]

以下列出常用的 alteration 类型：

• add [column] column_description [first | after column]：添加新列
• add [column] (column_description, column_description, ...)：在末尾添加一个或多个新的列
• drop [column] column：删除指定的列
• rename [as] new_table_name：重新命名一个表
• change [column] column new_column_description：修改列，可以修改列的名称，因为 new_column_description 中就包含了名称
• modify [column] column_description：类似于 change，用于修改列的类型，而不是名称

eg：

alter table customers
modify name char(70) not null;

4 DQL与DML

4.1 insert 语句

insert 语句用于保存数据，它的通常格式是：

insert into tableName
[(column1, column2, column3, ...)]
values
(value1, value2, value2, ...);

当只针对一些列或按照不同的顺序去指定它们时，可以使用以下两种形式：
一：

insert into customers
(name, city)
values ('seiei', 'beijing');

二：

insert into customers
set
name = 'seiei',
city = 'beijing';

在 insert 语句后面可以添加 low_priority 或 delayed，其中：

• low_priority：如果在SQL中指定 LOW_PRIORITY，可使写操作的优先级降低，相当于原来的写锁在写操作执行前就会产生，当指定 LOW_PRIORITY 后，写锁在写操作执行时才会产生，因此可降低读操作的等待时间，虽然修改 LOW_PRIORITY 可降低读操作的等待时间，但由于降低了写操作的优先级，写操作需等待读锁都释放后才可执行，使MySQL队列中有可能堆积写操作
• delayed：意味着插入数据会被缓存，如果服务器繁忙会运行其他查询，而不是等待 insert 操作完成
• ignore：ignore 存放在前两个关键字之后，作用是如果尝试插入一个可能导致重复唯一键的记录行都会被忽略

如果主键是自增属性，插入可以不显示声明

4.2 查询数据（select 语句）

select 语句可以通过匹配表中 指定规则的行 从数据库中获取数据，查找返回的可以看做是返回一个表格

select [options] items
[into file_details]
from tables
[where conditions]
[group by order_type]
[having where_definetion]
[order by order_type]
[limit limit_criteria]
[procedure proc_name(arguments)]
[lock_options];

4.2.1 从单个表格中寻找（where 子句）

要访问一个表中行的子集，需要指定一些选择条件，此时可以使用子句 where，where 字句指定了用于选择特定行的条件，MySQL支持所有比较操作符和正则表达式，常用的比较操作符有：

• =
• >
• <
• >=
• <=
• !=
• is not null
• is null
• between：测试一个值是否大于或者等于最小值并且小于或等于最大值
• in：测试一个值是否在特定的集合里
• not in
• like：形如 name like {'Fred %'}，这是一个简单的SQ匹配模式，该模式由常规文本和两个字符组成，其中这两个重要的字符分别是：% 表示可以匹配任何数量任意字符，_ 表示只匹配一个任意字符
• not like
• regexp：正则表达式

同时还可以使用 and 和 or 进行组合。

4.2.2 从多个表格中获取数据

要在多个不同的表中获取数据，就需要执行 关联 操作了。
关联的类型大致分为：

• 笛卡尔乘积 / 完全关联 / 交叉关联：所有表所有行的所有关联，在关联表名之间使用逗号或 inner join 或 cross join 链接
• 内部关联：如果没有 where 条件就等价于完全关联了，所以一定要使用 where 字句使其变成真正的内部关联
• 等价关联：在关联中使用带 = 的条件表达式来匹配不同表中的行
• 左关联：在SQL中使用 left join 关键词，试图匹配表的行，并在不匹配的行中添加 null

4.2.2.1 笛卡尔乘积 / 完全关联 / 交叉关联

笛卡尔乘积 / 完全关联 / 交叉关联 其意思是：将多个表列出来，形成一个中间表，该表来自所有表的每一行的所有可能组合（注意这里头“中间表”的意思，指的是被操作期间生成的数据库表，而不是操作后生成的表），所有表所有行的所有关联，在关联表名之间使用逗号或 inner join 或 cross join 链接，例如：

select orderid
from orders, customers
where customers.name = 'seiei'
and customers.id = orders.id;

或

select orderid
from orders inner join customers
on customers.id = orders.id
where customers.name = 'seiei';

直接使用进行多表查询会很可能导致查询结果数量爆炸，因为它的查询结果是表的总行数的乘积，因此使用这样形式的多表查询要格外小心

4.2.2.2 左关联

左关联通常用于返回包括左表中的所有记录和右表中匹配条件相等的记录，如果此时右表条件不匹配则返回 null。
如果不能区分 inner join 和 left join 的区别，可以查看下面参考文章，同时还要注意 where 与 on 条件的区别：

参考文章 《inner join 与 left join 之间的区别》，《on条件与where条件的区别》，《mysql left join中where和on条件的区别》

• 多表 left join 是会生成一张临时表，并返回给用户，where 条件是针对最后生成的这张临时表进行过滤，过滤掉不符合 where 条件的记录，是真正的不符合就过滤掉。
• on 条件是对 left join 的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为 NULL，on 条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说 on 中左表的限制条件只影响右表的匹配内容，不影响返回行数。

当使用 on 条件来表达条件相等的时候，可以使用 using ，形如：

 select a.*, b.* from a left join b using(colA);

等价于：

select a.*, b.* from a left join b on a.colA = b.colB;

4.2.3 按顺序获取数据

如果想要按照一定的顺序获取数据，可以使用 order by 字句，该特性可以方便的实现该功能。其默认是升序排列，降序可以在末尾添加 desc 关键字，例如：

select name from customers order by name desc;

4.2.4 分组和合计数据

有时候我们可能需要查找一些平均值什么的，这时可以使用MySQL的合计函数：

• avg(列名)：平均值
• count(列名)：求出行数：
  • 项目只指定一个列名，返回该列非 null 的总行数
  • 在列名之前添加 distinct 关键字，返回该列不同值的总行数
  • 项目指定 *，得到包括 null 在内的总行数
• sum(列名)：所有值之和
• min(列名)
• max(列名)
• std(列名) / steddev(列名)

在通过使用合计函数获取更多详细信息的时候，还可以使用 group by 子句，从而可以分组查询，计算。同时还可以使用 having 子句来测试一个合计的结果，直接放置在 having 子句之后。

select max(age) from users;

4.2.5 选择返回行的个数 limit

比如博客中主页显示文章的篇数就可以使用 limit 子句实现。如：

select name
from customers
limit 2, 3;

作用是返回3行，从第2行开始（注意：行号从0开始）

4.2.6 使用子查询

子查询是一个嵌套在另一个查询内部的查询，例如：

select customerid
from orders
where amount = (select max(amount) from orders);

4.2.6.1 子查询的操作符

特殊的子查询操作符共以下5个，其中前三个几乎一致：

• any：

select c1 from t1 where c1 > any(select c1 from t2);

• in，等价于 = any ：

select c1 from t1 where c1 in (select c1 from t2);

• some：等价于 any
• all：

select c1 from t1 where c1 > all(select c1 from t2);

• exists：

select isbn, titile from books where not exists (select * from order_items where order_items.isbn = books.isbn);

4.2.6.2 行子查询

select c1,c2,c3
from t1
where (c1,c2,c3) in (select c1,c2,c3 from t2);

4.2.6.3 使用子查询作为临时表

select * from
(select customerid, name from customers where city = 'Box Hill')
as box_hill_customers

4.3 更新数据库记录

从数据库修改数据，可以使用 update，常用格式是：

update [low_priority] [ignore] tablename
set column1 = expression1, column2 = expressoin2, ...
[where condition]
[order by order_criteria]
[limit number];

例如：

update books
set price = price*1.1
where isbn = 123;

4.4 删除表中的某个记录

可以使用 delete 语句，常见格式：

delete [low_priority] [quick] [ignore] from table
[where condition]
[order by order_cols]
[limit number]

例如：

delete from customers
where customerid = 5;

5 使用SQL脚本

SQL脚本就是以 sql 为文件后缀名的文件，如 init.sql，它可以使用MySQL调用，编写SQL文件与直接在命令行输入命令语法都是一致的，如：

SET NAMES utf8mb4;

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS classes;

CREATE TABLE classes (
	id BIGINT NOT NULL AUTO_INCREMENT,
	name VARCHAR(10) NOT NULL,
	PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
	id BIGINT NOT NULL AUTO_INCREMENT,
	class_id BIGINT NOT NULL,
	name VARCHAR(10) NOT NULL,
	PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO classes (name) VALUES ('一班');
INSERT INTO classes (name) VALUES ('二班');
INSERT INTO classes (name) VALUES ('三班');
INSERT INTO classes (name) VALUES ('四班');

INSERT INTO students (class_id, name) VALUES (1, '小明');
INSERT INTO students (class_id, name) VALUES (1, '小红');
INSERT INTO students (class_id, name) VALUES (1, '小刚');
INSERT INTO students (class_id, name) VALUES (2, '小智');

sql 文件的注释语法是：

• 单行注释：-- 开头
• 多行注释：/* */

ENGINE=InnoDB 表示其所建立的表格都用 innodb 引擎，该引擎支持事务管理，外键

使用MySQL执行脚本，可以通过输入以下命令：

mysql -u root -p -D databaseName<D:\Code\java_Seiei\ForLearningJava\src\main\resources\init.sql

其中 databaseName 是数据库名，紧接使用的绝对路径。