MySQL8数据库 | MySQL调优｜MySQL底层原理

短命女 2023-10-14 01:47 141阅读 0赞

### MySQL8数据库安装 ###

#### 一、Windows 环境下安装 ####

##### A、[下载 MySQL][MySQL] #####

Select Operating System:  
`Microsoft Windows`

快捷下载：[mysql-8.0.22-winx64.zip][]

##### B、解压并配置MySQL环境变量 #####

MYSQL_HOME:
    C:\MySQL\mysql-8.0.22-winx64

##### C、在解压根目录创建`my.ini`配置文件 #####

[mysqld]
    #设置3306端口
    port = 3306
    # 设置mysql的安装目录
    basedir=C:/MySQL/mysql-8.0.22-winx64
    # 设置mysql数据库的数据的存放目录
    datadir=C:/MySQL/mysql-8.0.22-winx64\data
    # 允许最大连接数
    max_connections=200
    # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
    max_connect_errors=10
    # 服务端使用的字符集默认为utf8
    character-set-server=utf8mb4
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    # 默认使用 “mysql_native_password” 插件认证
    default_authentication_plugin=mysql_native_password
    
    [mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8mb4
    
    [client]
    # 设置mysql客户端连接服务端时默认使用的端口
    port=3306
    # 设置mysql客户端连接服务端时默认使用的字符集
    default-character-set=utf8mb4

##### D、安装 MySQL (以下操作必须是管理员身份) #####

1.  初始化MySQL

mysqld --defaults-file=C:\MySQL\mysql-8.0.22-winx64\my.ini --initialize --console

**注意**：复制保存 MySQL初始化密码 `fVdpg:bM9pAk`

1.  安装MySQL服务

mysqld --install mysql8

1.  启动MySQL服务

net start mysql8

##### E、登录、修改密码 #####

1.  登录 MySQL

mysql -u账号 -p密码

> 使用上面方式无法登录的解决方案
> 
> 1、停止 mysql8 `net stop mysql8`
> 
> 2、无密码启动 `mysqld --console --skip-grant-tables --shared-memory`
> 
> 3、前面窗口不能关闭，再开启一个新的窗口进行无密码登录 `mysql -u root -p`
> 
> 4、清空密码 `update mysql.user set authentication_string='' where user='root' and host='localhost;'`
> 
> 5、刷新权限 `plush privileges;`
> 
> 6、重新启动 mysql 服务，再以无密码登录 mysql

1.  登录后使用MySQL修改密码

ALTER USER root@localhost IDENTIFIED BY '123456';

1.  开启远程访问

CREATE USER 'root' @'%' IDENTIFIED BY '123456'; -- 这一步执行失败也没关系
    
    GRANT ALL ON *.* TO 'root' @'%';
    
    # alter user 'root'@'%' identified with mysql_native_password by '123456';
    
    FLUSH privileges;

#### 二、Linux 环境下安装 ####

##### A、[下载 MySQL][MySQL] #####

Select Operating System:  
`Source Code`

Select OS Version:  
`Generic Linux (Architecture Independent)`

快捷下载：[mysql-8.0.22.tar.gz][]

##### B、把下载的 MySQL 压缩包上传到 Linux 服务器 #####

##### C、解压`mysql-8.0.22.tar.gz` #####

tar -zxvf mysql-8.0.22.tar.gz

##### D、把解压后的文件移动到 /usr/local 目录下 #####

mv mysql-8.0.22 /usr/local/mysql

##### E、添加MySQL组合用户 （默认会添加，没有添加就手动添加） #####

groupadd mysql
    useradd -r -g mysql mysql

##### F、进入 `/usr/local/mysql` 目录，修改相关权限 #####

cd /usr/local/mysql
    chown -R mysql:mysql ./

##### G、MySQL初始化操作，记录临时密码 #####

cd /usr/local/mysql/bin
    ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

**注意**：复制保存 MySQL初始化密码 `fVdpg:bM9pAk`

##### H、创建MySQL配置文件 `/etc/my.cnf` #####

cd /etc
    vi my.cnf

**my.cnf**

[mysqld]
    port = 3306
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data
    max_connections=200
    max_connect_errors=10
    character-set-server=utf8mb4
    default-storage-engine=INNODB
    default_authentication_plugin=mysql_native_password
    
    [mysql]
    default-character-set=utf8mb4
    
    [client]
    port=3306
    default-character-set=utf8mb4

##### I、启动MySQL服务 #####

cd /usr/local/mysql/support-files
    ./mysql.server start

##### J、通过临时密码登录MySQL并修改密码 #####

cd /usr/local/mysql/bin
    ./mysql -u root -p生成的临时密码 
    ALTER USER 'root' @'localhost' IDENTIFIED BY '123456';

##### K、开启远程访问 #####

CREATE USER 'root' @'%' IDENTIFIED BY '123456';  -- 这一步执行失败也没关系
    
    GRANT ALL ON *.* TO 'root' @'%';
    
    FLUSH privileges;

### MySQL 数据库操作 ###

##### 数据库操作 #####

**创建数据库**

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

**查询数据库**

-- 查询所有数据库
    SHOW DATABASES;
    -- 查询数据库建表时的sql脚本
    SHOW CREATE DATABASE db_name;

**删除数据库**

DROP DATABASE db_name;

**修改数据库**

-- 修改数据库的字符编码和排序方式
    ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

**选择数据库**

USE db_name;

**设置操作的编码格式**

SET NAMES utf8;

##### 表操作 #####

###### 创建表 ######

> CREATE TABLE tb\_name (建表的字段、类型、长度、约束、默认、注释)

**约束**

*  非空 `NOT NULL`
 *  非负 `UNSIGNED`
 *  主键 `PRIMARY KEY`
 *  自增 `AUTO_INCREMENT`
 *  默认 `DEFAULT`
 *  注释 `COMMENT`

-- 数据库存在就删除
    DROP DATABASE IF EXISTS testdb;
    -- 创建数据库的操作
    CREATE DATABASE IF NOT EXISTS testdb;
    -- 使用数据库
    USE testdb;
    -- 数据表存在就删除
    DROP TABLE IF EXISTS testdb;
    -- 创建表的操作
    CREATE TABLE IF NOT EXISTS tb_test 
    ( 
        test_id INTEGER ( 10 ), 
        test_name VARCHAR ( 50 ) 
    );

-- 使用数据库
    USE testdb;
    -- 数据表存在就删除
    DROP TABLE IF EXISTS testdb;
    -- 创建表的操作
    CREATE TABLE IF NOT EXISTS tb_test 
    ( 
        test_id INTEGER (10) AUTO_INCREMENT PRIMARY KEY COMMENT '测试ID', 
        test_name VARCHAR (50) NOT NULL COMMENT '测试名称',
        test_password VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '测试密码'
    );

**常用类型**

*  极小整形 `TIYINT` 1个字节，无符号最大值 256 (2^8 -1)，正负 -128 ~ 127 (-2^7 -1 ~ 2^7 -1)
 *  小整形 `SMALLINT` 2个字节，无符号最大值 65535 (2^16 - 1)，正负 -32768 ~ 32767 (-2^15 - 1 ~ 2^15 - 1)
 *  中整形 `MEDIUMINT` 3个字节，无符号最大值 16777215 (2^24 - 1)，正负 (-2^23-1 ~ 2^23-1)
 *  整形 `INT` 4个字节，无符号最大值 2^32 -1，正负 (-2^31-1 ~ 2^31-1)
 *  长整形 `BIGINT` 8个字节，无符号最大值 2^64 - 1, 正负 (-2^63-1 ~ 2^63-1)
 *  单精度 `FLOAT` 4个字节 Float \[(M,D)\] -3.4E+38~3.4E+38( 约 )
 *  双精度 `DOUBLE` 8个字节 Double \[(M,D)\] -1.79E+308~1.79E+308( 约 )
 *  小数值 `DECIMAL` `M>D ? M+2 : D+2` 个字节 Decimal \[(M,D)\] 注：M 为长度， D 为小数
 *  定长字符串`CHAR` 最大保存255个字节，如果值没有达到给定的长度，使用空格补充
 *  变长字符串`VARCHAR` 最大保存255个字节，用多大长度占多大长度
 *  极小文本 `TINYTEXT` 最大长度255个字节(2^8-1)
 *  中文本 `MEDIUMTEXT` 最大长度 16777215 个字节(2^24-1)
 *  文本 `TEXT` 最大长度65535个字节(2^16-1)
 *  长文本 `LONGTEXT` 最大长度4294967295个字节 (2^32-1)
 *  日期 `DATE` 日期(yyyy-mm-dd)
 *  时间 `TIME` 时间(hh:mm:ss)
 *  日期时间 `DATETIME` 日期与时间组合(yyyy-mm-dd hh:mm:ss)
 *  时间戳 `TIMESTAMP` yyyymmddhhmmss
 *  年份 `YEAR` 年份yyyy

-- 创建表的操作
    CREATE TABLE IF NOT EXISTS tb_user
    ( 
        user_id int(11) AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID', 
        user_name VARCHAR (30) NOT NULL COMMENT '用户名称',
        user_birthday date COMMENT '用户生日',
        user_gender CHAR(3) COMMENT '用户性别',
        user_status TINYINT(1) NOT NULL COMMENT '用户状态',
        user_height DECIMAL(4,1) NOT NULL COMMENT '用户身高',
        user_desc text COMMENT '用户简介'
    );

**表字段索引**

*  主键索引：ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)，用于唯一标识一条记录
 *  唯一索引：ALTER TABLE `table_name` ADD UNIQUE (`column`) 往往不是为了提高访问速度，而是为了避免数据出现重复
 *  普通索引：ALTER TABLE `table_name` ADD INDEX index\_name (`column`)，唯一任务是加快对数据的访问速度
 *  全文索引：ALTER TABLE `table_name` ADD FULLTEXT index\_name (`column1`, `column2`) ，仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间
 *  联合索引：ALTER TABLE `table_name` ADD INDEX index\_name (`column1`, `column2`, `column3`) ，为了更多的提高mysql效率

# 删除主键索引
    ALTER TABLE `table_name` DROP PRIMARY KEY
    
    # 删除唯一索引
    ALTER TABLE `table_name` DROP INDEX unique_index_name;
    ALTER TABLE `table_name` DROP INDEX cloumn;
    
    # 删除普通索引
    ALTER TABLE `table_name` DROP INDEX index_name;
    
    # 删除全文索引
    ALTER TABLE `table_name` DROP INDEX fulltext_index_name;
    ALTER TABLE `table_name` DROP INDEX cloumn;

###### 修改表 ######

**字段添加**

# ALTER TABLE tb_name ADD 字段 字段类型 非空约束 默认值 注释
    ALTER TABLE tb_name ADD address VARCHAR ( 100 ) NOT NULL DEFAULT COMMENT '用户地址';

**字段类型修改**

# ALTER TABLE tb_name MODIFY 字段 新的字段类型 非空约束 默认值 注释
    ALTER TABLE tb_name MODIFY address VARCHAR ( 50 ) NOT NULL DEFAULT COMMENT '用户地址';

**字段名称类型修改**

# ALTER TABLE tb_name MODIFY 旧的字段 新的字段 新的字段类型 非空约束 默认值 注释
    ALTER TABLE tb_name CHANGE address addr VARCHAR ( 50 ) NOT NULL DEFAULT COMMENT '用户地址';

**字段类型查询**

DESC tb_name;

**字段删除**

# ALTER TABLE tb_name DROP 字段
    ALTER TABLE tb_name DROP addr;

**表名修改**

# ALTER TABLE 旧表名 RENAME TO 新表名
    ALTER TABLE tb_name RENAME TO tb_name1

**表引擎修改**

# ALTER TABLE tb_name ENGINE = 新引擎
    ALTER TABLE tb_name ENGINE = MyISAM;

###### 删除表 ######

# DROP TABLE 表名
    DROP TABLE tb_name;
    # 如果表存在就删除
    DROP TABLE IF EXISTS tb_name;

###### 查询表 ######

# 查询所有表
    SHOW TABLES;
    # 查询建表时的脚本
    SHOW CREATE TABLE tb_name;

### MySQL DML 操作 ###

###### 新增数据 ######

# insert into 表名 (字段名：字段1,字段2,...字段n) values (值1,值2,...值n);
    
    # 全表插入
    INSERT INTO `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`, `user_desc`) VALUES ('曾小贤', '2020-11-22', '男', 1, 174.5, '好男人就是我，我就是好男人曾小贤');
    
    # 指定列插入，前提是其他列没有非空的约束
    INSERT INTO `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`) VALUES ('胡小梅', '2020-11-22', '女', 1, 174.5);

###### 修改数据 ######

# update 表名 set 字段1=新值1,字段2=新值2,...字段n=新值n where 条件
    UPDATE `tb_user` SET user_birthday='1995-10-20' WHERE user_id=2;
    UPDATE `tb_user` SET user_birthday='1995-10-20', user_status = 2 WHERE user_id=2;
    
    UPDATE `tb_user` SET user_status = 1 where user_id > 1;
    
    UPDATE `tb_user` SET user_status = 1;

###### 删除数据 ######

# delete from 表名 where 条件
    DELETE FROM `tb_user` WHERE user_id=2;

###### 查询数据 ######

# select 字段1,字段2,...字段n from 表名 where 条件
    
    # 不带条件查询
    select * from tb_user;
    select user_id,user_name from tb_user;
    
    # 带条件查询 (比较运算 >, <, >=, <=, !=, <>, =)
    select * from tb_user where user_id > 1;
    
    # 带逻辑条件查询 (and,or)
    select * from tb_user where user_status = 1 and user_id > 1;
    select * from tb_user where user_id = 1 or user_name = '胡小梅';
    
    # 模糊查询 (like %%)
    select * from tb_user where user_name like '曾%';
    select * from tb_user where user_name like '%闲';
    select * from tb_user where user_name like '%小%';
    
    # 范围查询
    select * from tb_user where tb_status in (0,1,2);
    
    # 聚合函数
    -- count(field)
    select count(user_id) 用户数量 from tb_user;
    -- sum(field)
    select sum(user_height) 总身高 from tb_user;
    -- avg(field)
    select avg(user_height) 平均身高 from tb_user;
    
    ...
    
    # 分组查询
    -- group by 统计男女的平均身高: group by 查询中出现的字段必须是 group by 后面的字段
    select user_gender as 性别,avg(user_height) 平均身高 from tb_user group by user_gender;
    
    select user_status,user_gender as 性别,avg(user_height) 平均身高 from tb_user group by user_gender,user_status;
    
    select user_gender as 性别,avg(user_height) 平均身高,sum(user_height),count(user_id) 用户数量 from tb_user group by user_gender;
    
    # 排序查询
    -- order by 默认是 asc 升序, desc 降序; order by 是放在 group by 之后的
    select * from tb_user order by user_id asc;
    
    select * from tb_user order by user_id desc;
    
    select * from tb_user where user_id < 10 order by user_id desc;
    
    select * from tb_user where user_id < 10 order by user_id,user_status desc;
    
    select user_gender as 性别,avg(user_height) 平均身高,sum(user_height),count(user_id) 用户数量 from tb_user group by user_gender order by 用户数量;

# 创建分数表
    CREATE TABLE `tb_score` (
      `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `stu_id` int(11) NOT NULL,
      `cou_id` int(11) NOT NULL,
      `score` decimal(4,1) NOT NULL
    );
    
    -- 插入测试数据 
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,1,89.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,2,78.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,3,94.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,4,77.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,5,99.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,1,90.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,2,88.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,3,69.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,4,83.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,5,92.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,1,77.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,2,84.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,3,91.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,4,80.0);
    INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,5,99.0);
    
    # 分页查询
    -- 查询科目id为1的最高成绩
    select max(score) from tb_score where course_id = 1;
    select * from tb_score where course_id = 1 limit 1;
    -- 查询课程id为4的前五名成绩信息
    select * from tb_score where course_id = 4 order by score limit 5;
    -- limit 分页, 起始值是 0： (pageIndex - 1) * pageSize, pageSize
    select * from tb_score limit 0,10
    select * from tb_score limit 10,10
    select * from tb_score limit 20,10

[MySQL]: https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fdownloads%2Fmysql%2F
[mysql-8.0.22-winx64.zip]: https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fget%2FDownloads%2FMySQL-8.0%2Fmysql-8.0.22-winx64.zip
[mysql-8.0.22.tar.gz]: https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fget%2FDownloads%2FMySQL-8.0%2Fmysql-8.0.22.tar.gz