服务端学习 - MySQL入门与应用

MySQL安装与配置

MySQL简介

MySQL是目前最流行的关系型数据库管理系统之一，在web应用方面，MySQL是最好的关系型数据库管理软件之一。

关系型数据库是将数据保存在不同的表中，使用SQL语言作为访问数据库的标准语言。MySQL采用了双授权政策，分为社区版和商业版，由于体积小、速度快、总体拥有成本低，一些中小型网站都选择MySQL作为网站数据库。

MySQL安装及基础配置

• 通过yum源安装

https://dev.mysql.com/downloads/repo/yum/
https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
https://mirrors.tuna.tsinghua.edu.cn/help/mysql/

/etc/yum.repos.d> vim mysql-community.repo

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql56-community-el7/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql80-community-el7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

> yum install -y mysql-community-server

• 镜像站下载离线安装

http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar

/tmp/>wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar
/tmp/>tar -xvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar

# 判断是否安装了依赖
> yum list installed|grep openssl
> yum list installed|grep liaio
> yum list installed|grep perl

# 临时进入宽松模式
> setenforce=0

# 永久宽松模式
> vim /etc/selinux/config
SELINUX=disabled

# 按顺序安装
> rpm -ivh mysql-community-common-8.0.19-1.el7.x86_64.rpm 
# 卸载mariadb
> yum list installed|grep mariadb
> yum remove mariadb-libs 

> rpm -ivh mysql-community-libs-8.0.19-1.el7.x86_64.rpm
> rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm
> rpm -ivh mysql-community-server-8.0.19-1.el7.x86_64.rpm

# 启动mysql服务
> service mysqld start

# 查询默认密码
> grep password /var/log/mysqld.log
2020-03-28T01:34:22.138193Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: bbrk>&igy5M5

> mysql -uroot -p
mysql> alter user user() identified by '123abcA.';
mysql> exit

> mysql -uroot -p123abcA.
# 更改root可访问地址
mysql> select Host,user from mysql.user where user='root';
+-----------+------+
| Host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

mysql> update mysql.user set Host='%' where user='root';
mysql> flush privileges;

> mysql -h192.168.129.129 -uroot -p


> firewall-cmd --add-port=3306/tcp --permanent
> firewall-cmd --reload

# 密码验证组件
mysql> select * from mysql.component;
mysql> uninstall compoent 'file://component_validate_password';

# 修改密码作为8.0版本以前的加密方式
mysql> alter user 'root'@'%' identified with mysql_native_password by '123abcA.'

配置文件

> vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

# 数据目录
datadir=/var/lib/mysql
# socket 文件目录
socket=/var/lib/mysql/mysql.sock

# error_log 日志路径
log-error=/var/log/mysqld.log
# 进程号保存文件
pid-file=/var/run/mysqld/mysqld.pid

MySQL基本概念

结构化查询语言

结构化查询语言（Structured Query Language） 简称：SQL，是一种特殊目的的编程语言，是数据库查询与程序设计语言，用于存取数据以及查询、更新和管理关系型数据库，同时也是数据库脚本文件的扩展名。

SQL语言分类

数据查询（DQL，Data Query Language）
数据检索语句，用以从表中获取数据，select是DQL用的最多的关键字，其它还包括where、order by、group by、having

数据操作（DML，Data Manipulation Language)
关键字：insert、update、delete，分别用于插入、修改以及删除表中的行

事务处理（TPL，Transaction Process Language）
确保被DML语句影响的表的所有行为得以更新。TPL语句包含begin transaction、commit、rollback

数据控制（DCL，Data Control Language）
通过grant或者revoke获得许可，确定单个用户和用户组对数据库对象的访问

数据定义（DDL，Data Defination Language）
关键字：create和drop。在数据库中创建新表或者删除表，以及为表加入索引等

指针控制（CCL，Cursor Control Language）
declare cursor（声明游标）、fetch into（进入）和update where current（更新当前位置）用于对一个或者多个表单独进行的操作

命令行连接

mysql [options][database]

• -u,–user=username 用户名，默认root
• -h,–host=hostname 远程主机，默认为localhost
• -p,–password[=password] 密码，默认为空，一般不建议明文
• -P,–port=3306 监听端口，默认为3306/tcp
• -D,–database=database 连接到服务器后指明默认数据库
• -e,–execute=‘sql statement’ 连接到服务器后执行命令后直接返回

mysql> help contents;

数据类型 - 字符型

• 定长字符

char 不区分大小写
binary 二进制数据流，区分大小写

• 变长字符

varchar 不区分大小写
varbinary 二进制数据流，可用于存储图片、音乐、短视频

• 对象存储

text、tinytext、smalltext、mediumtext、longtext
blog、tinyblog、smallblog、mediumblog、longblog

• 内置类型

enum 单选字符串，适合存储表单界面中的“单选值”
set 多选字符串，适合存储表单界面中的“多选值”

数据类型 - 数值型

• 精确数值

tinyint、smallint、mediumint、int/integer、bigint
decimal

• 近似值

float、double

数据类型 - 日期时间

date(YYYY-MM-DD)
time(HH:mm:ss)
year(YYYY)
datetime(YYYY-MM-DD HH:mm:ss)
timestamp(时间戳)

MySQL数据库操作

数据库管理

mysql> use information_schema;
mysql> show tables;

# 创建数据库
create {database|schema} [if not exists] db_name [create_specification];
# 修改数据库
alter {database|schema} [db_name] alter_specification;
# 删除数据库
drop {databbase|schema} [if exists] db_name;
# 查看数据库
show {database|schema} [like 'pattern' | where expr];
# 切换数据库
use db_name; 

表管理

# 创建表
create [temporary] table [if not exists] tbl_name (create_definition);
[table_options] [partition_options];

# 查看表
{explain | desc | describe } tbl_name;
show create table tbl_name;

# 删除表
drop [temporary] table [if exists] tbl_name [, tbl_name...];

# 可快速复制表
create table tb1 like tb;

# 查看结构
desc

# 查看sql
show create

# 修改表名
alter table tab_name rename [to | as] new_table_name;

# 修改字段名
alter table tbl_name rename column old_col_name to new_col_name;

# 修改索引名
alter table tbl_name rename {index | key} old_index_name to new_index_name;

# 加入新字段（最后、最前、指定字段后）
alter table tbl_name add [column] col_name col_definition [first | after col_name];

# 删除字段
alter table tbl_name drop [column] col_name;

# 修改字段属性
alter table tbl_name modify [column] col_name col_definition [first | after col_name];

alter table tbl_name change [column] old_col_name new_col_name col_definintion [first | after col_name]

数据操作

# 插入数据
insert into tbl_name values (value1,value2...);
insert into tbl_name (col1,col2...) values (value1,value2...);
insert into tbl_name values (value2,value2...),(value1,value2...)...;
insert into tbl_name (vol1,vol2,....) values (value1,value2...),(value1,value2...)...;

# 修改数据
update tbl_name set col1 = value1, col2 = value2... [where where_condition] [order by...] [limit row_count];

# 删除数据
delete from tbl_name [where condition];

# 清空表
truncate tbl_name;

数据操作 - 查询
简单查询 select * from tbl_name;
条件查询 where
单条件
多条件 and/or
其他条件 between…and/not/is null/is not null/in/not in/like/not like
排序 order by
限制查询数量 limit
统计函数 count/avg/sum/max/min
分组查询 group by

知识技能获取，感谢[网易云课堂 - 微专业 - 前端高级开发工程师]运营团队。