# MySQL
MySQL 是最流行的关系型数据库管理系统，那么什么是数据库呢？
# MySQL vs SQLite
MySQL和SQLite是两种不同类型的数据库管理系统（DBMS），在一些方面有所不同：
- 性能：MySQL是一个客户端/服务器模型的DBMS，它需要一个专门的服务器进程来处理客户端请求。相比之下，SQLite是一个嵌入式的DBMS，它不需要网络通信和服务器进程，所有的数据操作都是在应用程序进程中进行的。因此，SQLite通常比MySQL更快，特别是在单用户、低并发的情况下。
- 功能：MySQL是一个功能非常丰富的DBMS，支持多种存储引擎、复制、集群、分区、备份、恢复等高级功能。而SQLite则相对简单，不支持分布式、高可用性、复制等高级功能。但是，SQLite支持标准的SQL语言、事务、触发器、视图等基本功能，足以满足许多应用程序的需求。
- 并发性：MySQL支持更高级别的事务隔离级别，如可重复读和串行化。而SQLite只支持较低的事务隔离级别，如读已提交和可重复读。因此，在高并发的多用户环境下，MySQL更适合处理大量的并发事务。
- 部署：MySQL通常部署在一个独立的服务器上，需要一个专门的管理员来管理和维护。而SQLite则直接嵌入到应用程序中，不需要额外的管理和维护，因此更容易部署和使用。
综上所述，MySQL和SQLite都有其独特的优缺点，应该根据具体的应用场景和需求来选择适合的DBMS。如果需要处理大量的并发事务、需要高可用性和复制等高级功能，那么MySQL是更好的选择；如果需要一个简单、轻量级的DBMS，或者需要在移动设备和桌面应用程序中使用，那么SQLite是更好的选择。
# MySQL 安装
apt 安装
# 安装服务
apt isntall mysql-server
# 启动服务
service mysqld start
# 停止服务
service mysqld stop
# 查看服务状态
service mysqld status
docker 安装
docker run -itd \
--restart always \
--name mysql-dev \
-p 3307:3306 \
-v /docker/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql
windows
使用这个安装 https://dev.mysql.com/downloads/mysql/ (opens new window)
mysqladmin --version
# 配置
# 步骤一：生成临时密码
grep 'temporary password' /var/log/mysqld.log
# 步骤二：使用临时密码登陆
mysql -uroot -p
# 步骤三：更改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '$oK123456!';
# 步骤四：退出并用新密码登陆
exit
- 登录 MySQL
mysql -h 主机名 -u 用户名 -p
mysql -u 用户名 -p
mysql -u 用户名
参数说明：
h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
u : 登录的用户名;
p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
修改密码
mysqladmin -u root password "new_password";
- 设置远程登录
#找到bind-address所在行 45
# Instead of skip-networking the default is now to listen only on 46
# localhost which is more compatible and is not less secure. 47
bind-address = 127.0.0.1
# 将 bind-address值修改为本机IP即可。
#注意注释说明，如果是较老版本的MySQL，此处就应该是skip-networking，直接将其注释即可。
# 内部操作
- 创建数据库 create database
mysql> create database <database_name>;
mysql> create database <database_name> charset utf8;
mysql> show create database <database_name>;
- 删除数据库 drop database
mysql> drop database <database_name>;
mysql> drop database if exists <database_name>;
# 外部操作
- 备份数据库（导出）mysqldump
mysqldump [参数] –h 主机地址 –u 登录名 –p 数据库名 > 文件.sql
mysql -h localhost -u login -p database_name > database_back.sql
- 恢复数据库（导入）source
mysql –u 登录名 –p 数据库名 < 文件名.sql
mysql -u login_name -p database_name < database_back.sql
mysql> source 文件名.sql
# MySQL 创建表
# 创建表
-- 创建表
DROP TABLE IF EXISTS class;
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(30) NOT NULL,
description VARCHAR(100)
);
-- 查看表结构
DESC class;
SHOW COLUMNS FROM class
# 插入数据项
-- 插入单条数据
INSERT class SET cname = "php" , description = "学习php 开发网站";
-- 插入多条数据
INSERT INTO class (cname, description)
VALUES
("linux","服务器知识"),
("mysql","数据库知识");
# 迁移数据到其他表
-- 复制表结构
CREATE TABLE tests LIKE class;
DESC tests;
-- 迁移数据
INSERT INTO test SELECT * FROM class;
# 复制表和表中数据
CREATE TABLE class_bak SELECT * FROM class;
-- 给列取别名
CREATE TABLE class_sub SELECT cname AS name FROM class;
# MySQL 增删改查
# 准备阶段
-- 建表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname CHAR(10),
class_id INT DEFAULT NULL,
age SMALLINT NOT NULL
);
-- 插入数据
INSERT
INTO student (sname, class_id, age)
VALUES
('小赵',1,31),
('小钱',1,22),
('小孙',1,33),
('小李',NULL,34),
('小周',2,21),
('小吴',2,22),
('小郑',2,33),
('小王',2,24);
SELECT * FROM student;
查询结果
id sname class_id age
------ ------ -------- --------
1 小赵 1 33
2 小钱 1 33
3 小孙 1 33
4 小李 (NULL) 33
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 模糊查询
SELECT * FROM student
WHERE class_id=2
OR sname LIKE '%李%';
id sname class_id age
------ ------ -------- --------
4 小李 (NULL) 33
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 范围查询
BETWEEN
-- AND
SELECT * FROM student WHERE age >= 25 AND age <= 40;
-- 等价
SELECT * FROM student WHERE age BETWEEN 25 AND 40;
-- NOT
SELECT * FROM student WHERE age NOT BETWEEN 25 AND 40;
查询结果
id sname class_id age
------ ------ -------- --------
1 小赵 1 33
2 小钱 1 33
3 小孙 1 33
4 小李 (NULL) 33
IN
SELECT * FROM student WHERE class_id IN (2,3);
id sname class_id age
------ ------ -------- --------
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 去重
SELECT DISTINCT class_id FROM stu;
# 分页
SELECT * FROM student LIMIT 5;
SELECT * FROM student LIMIT 5 OFFSET 5;
# NULL 处理
NULL 不能和任何值比较
-- 无效，查询无结果
SELECT * FROM student WHERE class_id = NULL;
SELECT * FROM student WHERE class_id IS NULL;
查询结果
id sname class_id age
------ ------ -------- --------
4 小李 (NULL) 33
SELECT sname, IF(class_id, class_id, '未分配') FROM student;
SELECT sname, IFNULL(class_id, '未分配') FROM student;
查询结果
sname IFNULL(class_id, '未分配')
------ -------------------------------
小赵 1
小钱 1
小孙 1
小李 未分配
小周 2
小吴 2
小郑 2
小王 2
# 排序
ORDER BY
SELECT * FROM student ORDER BY class_id, age ASC;
id sname class_id age
------ ------ -------- --------
2 小钱 1 22
1 小赵 1 31
3 小孙 1 33
5 小周 2 21
6 小吴 2 22
8 小王 2 24
7 小郑 2 33
# 组合使用
SELECT * FROM student
WHERE
class_id = 2
AND
age IS NOT NULL
ORDER BY
age ASC
LIMIT 1;
id sname class_id age
------ ------ -------- --------
5 小周 2 21
# 更新数据
UPDATE student SET class_id = 2 WHERE class_id IS NULL;
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 1 31
2 小钱 1 22
3 小孙 1 33
4 小李 2 34
5 小周 2 21
6 小吴 2 22
7 小郑 2 33
8 小王 2 24
-- 把年龄大于 30 的同学分配到 3 班
UPDATE student SET class_id = 3 WHERE age > 30;
SELECT * FROM student ORDER BY age;
id sname class_id age
------ ------ -------- --------
5 小周 2 21
2 小钱 1 22
6 小吴 2 22
8 小王 2 24
1 小赵 3 31
3 小孙 3 33
7 小郑 3 33
4 小李 3 34
-- 删除最后报名的两个同学
DELETE FROM student ORDER BY id DESC LIMIT 2;
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 3 31
2 小钱 1 22
3 小孙 3 33
4 小李 3 34
5 小周 2 21
6 小吴 2 22
# 添加数据
INSERT INTO student SET sname = '小盾', age = 33, class_id = 3;
INSERT INTO student
(sname, age, class_id)
VALUES
('张三', 22, 1),
('李四', 18, NULL);
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 3 31
2 小钱 1 22
3 小孙 3 33
4 小李 3 34
5 小周 2 21
6 小吴 2 22
9 小盾 3 33
10 张三 1 22
11 李四 (NULL) 18
# MySQL 查询
# 准备阶段
DROP TABLE IF EXISTS class;
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(30) NOT NULL,
DESCRIPTION VARCHAR(100)
);
DESC class
INSERT INTO class (cname, DESCRIPTION)
VALUES
("linux","服务器知识"),
("mysql","数据库知识"),
("php","学习 PHP"),
("java","学习 Java");
# 获取所有行
SELECT *
FROM class;
-- 只需要其中部分的列
SELECT name, id AS ids
FROM class;
# 条件查询
SELECT *
FROM class
WHERE
description LIKE '%p%';
SELECT *
FROM class
WHERE
description LIKE '%p%'
OR
id > 2;
# MySQL LIKE
# 语法
WHERE LIKE ''
# 占位符
- 下划线
_表示一个字符 - 百分号
%表示一个或做个字符
| 语法 | 作用 |
|---|---|
LIKE 'a%' | 以 a 开头的任意字符 |
LIKE '%a' | 以 a 结尾的任意字符 |
LIKE '%a%' | 包含 a 的任意字符 |
LIKE 'a%b' | 以 a 开头 b 结尾的任意字符 |
LIKE 'a__%' | 以 a 开头 长度至少为 3 的任意字符 |
# 举例
-- 查找地址是岳麓开头的用户
SeLECT * FROM customers
WHERE address LIKE '岳麓%'
-- 查找地址是小区结尾的用户
SeLECT * FROM customers
WHERE address LIKE '%小区'
-- 查找地址是岳麓开头并且长度至少为 4 的用户
SeLECT * FROM customers
WHERE address LIKE '岳麓__%'
# SQL 速查表
# 查询单个表
SELECT * FROM country;
SELECT id, name FROM city;
# 排序
SELECT name FROM city ORDER BY rating ASC;
SELECT name FROM city ORDER BY rating DESC;
# 别名
SELECT name AS city_name FROM city;
SELECT co.name AS country_name, ci.name AS city_name
FROM city AS ci
JOIN country AS co ON ci.country_id = co.id;
# 过滤
SELECT name FROM city WHERE rating > 3;
获取名称既不是 Berlin 也不是 Madrid 的城市：
SELECT name FROM city WHERE name NOT IN ('Berlin', 'Madrid');
# 文本操作符
SELECT name FROM city WHERE name LIKE 'P%' OR name LIKE '%s';
SELECT name FROM city WHERE name LIKE 'B%' AND name LIKE '%n';
SELECT name FROM city WHERE name LIKE '_r%' OR name LIKE '%t_';
# 数值操作符
SELECT name FROM city WHERE population BETWEEN 100000 AND 500000;
# 空值检查
SELECT name FROM city WHERE rating IS NULL;
SELECT name FROM city WHERE rating IS NOT NULL;
# 分组和聚合
SELECT country_id, COUNT(*) AS city_count
FROM city
GROUP BY country_id;
# 子查询
SELECT name
FROM city
WHERE id IN (SELECT city_id FROM airport);
# 连接类型
SELECT co.name AS country_name, ci.name AS city_name
FROM city AS ci
LEFT JOIN country AS co ON ci.country_id = co.id;
SELECT co.name AS country_name, ci.name AS city_name
FROM city AS ci
RIGHT JOIN country AS co ON ci.country_id = co.id;
# 联合查询
SELECT name FROM city WHERE rating > 5
UNION
SELECT name FROM airport WHERE capacity > 10000;