MySQL数据库教程

MySQL数据库教程

常用的数据类型：

目前，主流的关系数据库主要分为以下几类：

1. 商用数据库，例如：Oracle，SQL Server，DB2等；
2. 开源数据库，例如：MySQL，PostgreSQL等；
3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

MySQL下载安装

MySQL下载：https://dev.mysql.com/downloads/mysql/（可以zip安装或者msi安装）

zip安装的话就是解压就可以了，之后配置环境变量，进到安装bin目录下安装mysql服务

教程：https://zhuanlan.zhihu.com/p/50159238（我得进到管理员才可以安装服务和启动关闭数据库）

MySQL Client的可执行程序是mysql.exe，MySQL Server的可执行程序是mysqld.exe。

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。

命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。

一些常用命令：

mysql --version(查看mysql安装的版本)

启动数据库服务：net start mysql（也可以去服务里面开启）

关闭数据库服务：net stop mysql

连接数据库：mysql -u root -p 之后输入密码（默认localhost，3306端口）

（默认用户名root，密码root。一般来说超级用户尽量修改密码，否则会被攻击）

在程序中调用数据库尽量不要使用root用户，Mysql的用户创建授权参考：https://www.jianshu.com/p/d7b9c468f20d

连接远程数据库：mysql -u 用户名 -p密码 -h 服务器IP地址 -P 服务器端MySQL端口号 -D 数据库名

（Mysql安装完成后默认也是不能远程访问的，设置远程访问参考：https://zhuanlan.zhihu.com/p/60539230）

退出数据库：exit/quit

查看所有数据库: show databases;

创建数据库: create database 数据库名;

使用数据库: use 数据库名; //对一个数据库进行操作时，必须先切换

删除数据库: drop database 数据库名; //会删除数据库中的所有表

显示数据库中所有表: show tables;

查看表结构: show columns from 表名;或者使用简洁的语句：DESC 表名

查看建表的语句：SHOW CREATE TABLE 表名;

创建数据表：

CREATE TABLE `students` (                             
	`id` bigint(20) NOT NULL AUTO_INCREMENT,            
	`class_id` bigint(20) NOT NULL,                     
    `name` varchar(100) NOT NULL,                       
    `gender` varchar(1) NOT NULL,                       
    `score` int(11) NOT NULL,                           
     PRIMARY KEY (`id`)                                  
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

修改数据表：

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;  --添加列

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
--修改列名

ALTER TABLE students DROP COLUMN birthday;  --删除列

删除数据表： DROP TABLE 表名;

关系型数据库：

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义（身份证号，手机号这些可以被修改，所以最好不要作为主键），而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

关系数据库通过外键可以实现一对多、多对多和一对一的关系。

对于一对多：可以在多这个表中设置外键，关联到一中的某一列

ALTER TABLE students
ADD CONSTRAINT fk_class_id  --外键约束的名称
FOREIGN KEY (class_id)  --外键
REFERENCES classes (id);  --将这个外键关联到classes表的id列

对于多对多：通过两个一对多的关系实现，通过一个中间表，关联两个一对多关系

对于一对一：其实可以合成一个表，但是拆开后副表存储不常用的数据，可以提高性能

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

ALTER TABLE students
ADD INDEX idx_score (score);  --创建名为idx_score 使用列score的索引

索引的缺点是在插入、更新和删除记录时，需要同时修改索引，插入、更新和删除记录的速度就慢。

对于主键，关系数据库会自动对其创建主键索引。

看上去唯一的列，例如身份证号、邮箱地址等可以添加一个唯一索引。

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

SQL语言：

Structured Query Language

做些约定：SQL关键字总是大写，以示突出，表名和列名均使用小写

查询数据SELECT:

（SELECT语句居然可以进行数学运算）

SELECT 300+200输出

SELECT * FROM <表名>  --查询表中所有数据

SELECT * FROM <表名> WHERE <条件表达式>  --查询满足条件的所有数据

SELECT 列1, 列2, 列3 FROM ...  --查询指定列数据

SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...  --指定列起个别名，返回的数据列名就和数据表中的不一样了

SELECT id, name, gender, score FROM students ORDER BY score DESC;  
--对选择的数据进行排序，默认从低到高，升序ASC，DESC是倒序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
--先按score列倒序，如果有相同分数的，再按gender列排序

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;  --带where条件再排序

条件运算按照NOT、AND、OR的优先级进行

分页查询：

使用LIMIT OFFSET可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

比如把结果集分成三页，每一页（最多）3条记录，获取第一页的记录：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;  -- 结果集从0号记录开始，最多取3条。

查询第二页：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

可以看出，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集。

聚合查询：

SELECT COUNT(*) FROM students;  --计算一共有多少条记录

SELECT COUNT(*) num FROM students;  --使用聚合查询并设置结果集的列名为num

其他聚合函数：

SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

分组聚合：

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;  --根据id进行分组之后聚合查询，返回多个

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;  --按多个列进行分组

多表查询：

查询多张表的语法是：

SELECT * FROM <表1>，<表2>

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c  --为数据表设置别名
WHERE s.gender = 'M' AND c.id = 1;  --查询条件

连接查询：

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

比如：存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。

内连接——INNER JOIN，只返回同时存在于两张表的行数据（两张表的数据组合成一张表）

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s  --先确定主表
INNER JOIN classes c  --需要连接的表
ON s.class_id = c.id;  --连接条件，students表的class_id列与classes表的id列相同的行需要连接

外连接（OUTER JOIN）

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s //左表
RIGHT OUTER JOIN classes c //右表
ON s.class_id = c.id;

(左表是主表，右表是连接表)

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。

FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL。

JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

插入数据INSERT：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

自增主键以及字段有默认值的字段可以不出现。

字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),  --可以一次性添加多条记录
  (2, '二宝', 'M', 81);

更新数据UPDATE：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
//一次更新多条记录

UPDATE students SET score=score+10 WHERE score<80;  --可以使用表达式

注意：不带WHERE条件的UPDATE语句会更新整个表的数据。

删除数据DELETE:

DELETE FROM <表名> WHERE ...;

注意：不带WHERE条件的DELETE语句会删除整个表的数据：。

其他操作：

插入或替换REPLACE：

我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。

使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新：

我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录。此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略：

我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照：

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

数据库事务：

把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。（比如转账必须是一个扣成功一个加成功）

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-