安装
版本: Mysql 8.0
OS: centos 7
下载源
https://dev.mysql.com/downloads/repo/yum/
sh
wget https://dev.mysql.com/get/mysql80-community-release-fc36-1.noarch.rpm
1
安装源
sh
rpm -Uvh mysql80-community-release-fc36-1.noarch.rpm
1
查看 Mysql 相关资源
sh
yum repolist enabled | grep "mysql.*-community.*"
# 输出
# !mysql-connectors-community/x86_64 MySQL Connectors Community 206
# !mysql-tools-community/x86_64 MySQL Tools Community 94
# !mysql80-community/x86_64 MySQL 8.0 Community Server 367
1
2
3
4
5
2
3
4
5
切换版本
默认开启的是 MySQL 8.0 Community Server
sh
yum repolist all | grep mysql
# 输出
# mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Communit disabled
# mysql-cluster-7.5-community-source MySQL Cluster 7.5 Communit disabled
# mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Communit disabled
# mysql-cluster-7.6-community-source MySQL Cluster 7.6 Communit disabled
# mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Communit disabled
# mysql-cluster-8.0-community-source MySQL Cluster 8.0 Communit disabled
# !mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 206
# mysql-connectors-community-source MySQL Connectors Community disabled
# !mysql-tools-community/x86_64 MySQL Tools Community enabled: 94
# mysql-tools-community-source MySQL Tools Community - So disabled
# mysql-tools-preview/x86_64 MySQL Tools Preview disabled
# mysql-tools-preview-source MySQL Tools Preview - Sour disabled
# mysql55-community/x86_64 MySQL 5.5 Community Server disabled
# mysql55-community-source MySQL 5.5 Community Server disabled
# mysql56-community/x86_64 MySQL 5.6 Community Server disabled
# mysql56-community-source MySQL 5.6 Community Server disabled
# mysql57-community/x86_64 MySQL 5.7 Community Server disabled
# mysql57-community-source MySQL 5.7 Community Server disabled
# !mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 367
# mysql80-community-source MySQL 8.0 Community Server disabled
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
yum 安装 Mysql
sh
# 安装
yum install mysql-community-server
# 启动
systemctl start mysqld
# 开启自启
systemctl enable mysqld
1
2
3
4
5
6
2
3
4
5
6
修改密码
sh
# 查看临时密码
grep 'temporary password' /var/log/mysqld.log
ALTER USER '用户'@'host' IDENTIFIED BY '新密码'
flush privileges
1
2
3
4
5
6
2
3
4
5
6
ADD COLUMN
添加表列
sql
ALTER TABLE user
ADD COLUMN age int not NULL after name
1
2
2
CHANGE COLUMN
修改表列
sql
ALTER TABLE user
CHANGE name1 name VARCHAR(255)
1
2
2
DELETE COLUMN
删除表列
sql
ALTER TABLE user
DROP COLUMN name;
1
2
2
UPDATE
更新数据
join
内连接, 获取两个表字段匹配关系的记录
left join
左连接, 列出左表所有记录，即使右表没有匹配
举个简单的例子，文章表跟用户表，文章表存的用户信息是用户 id ，但是想查出对应的用户名称，就可以使用左连接
sql
SELECT article.id, article.title, user.name from article left join user on article.user_id = user.id
1
NOT IN
LIMIT
限定返回行数
查询用户并限定结果为 10 行
sql
SELECT name from user LIMIT 10
1
DISTINCT
检索不同的行, 去重匹配, 有多个列时, 多个列都相同才会被过滤
查用户, 过滤名字和年龄相同的用户
sql
SELECT DISTINCT name, age from user
1
Group By
IS NULL 和 IS NOT NULL
对于 NULL 字段 需要使用 IS NULL 和 IS NOT NULL 判断
比如 有些 name 字段为 NULL 时
sql
SELECT name from user where name!='张三' or name IS NULL
1
Order By
排序默认升序(ASC)排序
查询用户 以年龄降序排序
sql
SELECT name from user Order By age DESC
1
IFNULL
用于判断一个表达式是否为 NULL, 如果是 NULL 则返回第二个参数的值
sql
SELECT IFNULL((SELECT name from user LIMIT 1), 'xjq')
1
UPDATE
批量修改时间，统一 +8 小时
sql
update stat set createdAt = DATE_ADD(createdAt, INTERVAL 8 HOUR);
1