冬季实战营第三期：MySQL数据库进阶实战

2022-02-04 19:34:31

冬季实战营第三期：MySQL数据库进阶实战

2月14日动手实战-MySQL数据库快速部署实践

学到了一些简单的命令，例如：
1.更新YUM源。

rpm -Uvh  http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

2.安装MySQL。

yum -y install mysql-community-server --nogpgcheck

3.查看MySQL版本号。

mysql -V

4.启动MySQL服务。

systemctl enable mysqld

5.配置MySQL的root用户密码。

#查看/var/log/mysqld.log文件，获取root用户的初始密码。
grep 'temporary password' /var/log/mysqld.log
#登录数据库。
mysql -uroot -p
#修改MySQL的root用户的初始密码。
set global validate_password_policy=0;  #修改密码安全策略为低（只校验密码长度，至少8位）。
ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
#授予root用户远程管理权限。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '12345678';
#输入exit退出数据库。

6.创建备份数据库

#进入 mysql 之后，每条命令结尾都要有分号。
create database test;  #创建test数据库
show databases;  #查看当前数据库列表
use mysql;  #连接 mysql 数据库
show tables;  #查看 mysql 数据库中的数据表
#使用命令 exit ，退出 MySQL 界面

#将名为 test 的数据库备份到当前目录的 test.sql，需要输入密码
mysqldump -uroot -p test >test.sql
#完成后，使用命令 ll 查看备份文件，界面查看到备份文件 test.sql ，完成备份

#将 test.sql 导入数据库，需要输入密码
mysql -uroot -p -Dtest<test.sql

#还有一种导入方法：输入命令：mysql -uroot -p12345678 ，进入 MySQL 数据库。输入 use test; #，连接数据库 test ，输入 source /root/test.sql; ，将 test.sql 导入数据库 test ，全部出现 #Query OK ，则表示数据库导入成功。
mysql -uroot -p12345678
use test;
source /root/test.sql;

7.创建表，添加数据，增删改查

create table test1 (id int,name char(20));  #创建一张表，表名为 test1 
insert into test1 values(1,"zhangsan");  #插入一条数据
select * from test1;  #查看 test1 表中的全部数据信息
update test1 set name = "lisi" where id =1;  #更新 test1 表中的数据
delete from test1 where id =1;  #删除 test1 表中，所有 id 值为 1 的数据
drop table test1;  #删除名为 test1 的数据表
drop database test;  #删除名为 test 的数据库

2月15日动手实战-如何在云端创建MySQL数据库

学到了如何在数据传输服务DTS通过数据传输控制台进行迁移数据库。

2月16日动手实战-基础学习之MySQL数据库基础

1.学到了如何对数据库进行操作：
数据库表操作

#使用CREATE TABLE语句创建一个数据表
CREATE TABLE `USER` (
  `user_id` int(128) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

语句解析：

CREATE TABLE USER：新表的名字，表名称在关键字CREATE TABLE后给出。
name varchar(64) NOT NULL,：列名name，类型varchar，长度限制64，此列不能为空。
PRIMARY KEY (user_id)：设置USER表主键为user_id。
user_id int(128) NOT NULL AUTO_INCREMENT,：AUTO_INCREMENT通常用于主键，表示主键自增，数值会自动+1。

2.使用ALTER TABLE语句更新数据表

ALTER TABLE user ADD COLUMN sex tinyint(1) NOT NULL COMMENT 'sex,woman:0,man:1' AFTER `age`;

3.使用RENAME TABLE语句对数据表重命名

RENAME TABLE USER TO student;
#语句格式：
RENAME TABLE table_name_a TO table_name_b;

4.使用DROP TABLE语句删除数据表

DROP TABLE student;
#语句格式：
DROP TABLE table_name;

数据操作
1.创建一个数据表

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT NULL COMMENT 'name',
 `age` int(11) DEFAULT NULL COMMENT 'age',
 `address` varchar(32) DEFAULT NULL COMMENT 'address',
 `sex` tinyint(1) DEFAULT NULL COMMENT 'sex,woman:0,man:1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.插入数据

INSERT INTO USER
       (name, age, address, sex)
VALUES
       ('zhangsan', 21, 'jiangxi', 0),
       ('lisi', 22, 'hubei', 0),
       ('wangwu', 23, 'hunan', 0),
       ('lilei', 24, 'henan', 1),
       ('hanmeimei', 25, 'hebei', 1),
       ('xiaoming', 26, 'shandong', 1),
       ('xiaoli', 27, 'shanxi', 1) ;

插入语句说明：

table_name(col_name,...)中的字段名列表必须和values(expr,...)字段值列表一致。
如果数据是字符型，必须使用单引号或者双引号。
被AUTO_INCREMENT标记的自动递增的主键，在插入数据时，可以不设置值。

3.查询数据
查询语句格式：

SELECT col_name,...
  FROM table_name
 WHERE where_condition
 GROUP BY col_name,...
HAVING where_condition
 ORDER BY col_name,...
 LIMIT offset,row_count
#查询语句
SELECT id,nameage,address,sex FROM user;

4.更新数据
更新语句格式：

UPDATE table_name SET col_name=expr,... WHERE where_condition
#更新语句
UPDATE `user` SET `age` = 28,`address` = 'sichuan' WHERE `name` = 'xiaoming' ;

5.删除数据
删除语句格式：

DELETE FROM table_name WHERE where_condition
#删除语句
DELETE FROM `user` WHERE `name` = 'xiaoming' ;

函数操作
1.创建一个数据表

CREATE TABLE student_score(sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8), score INT(11));
insert into `student_score`(`sid`, `sname`, `sage`, `ssex`,`score`)
values (1001, 'xiaohua', 17,'0', 75),
 (1002, 'xiaohong', 18,'0', 80),
 (1003, 'wangwu', 18,'1', 90),
 (1004, 'lisi', 17,'1', 68),
 (1005, 'zhangsan', 19,'1', 73),
 (1006, 'xiaohei', 19,'1', 100),
 (1007, 'xiaoma', 20,'0', 77),
 (1008, 'xiaoli', 17,'1', 82),
 (1009, 'xiaobai', 19,'0', 88),
 (1010, 'wentong', 18,'0', 53);

2.函数操作

SELECT avg(score) FROM student_score;  #AVG()函数
SELECT COUNT(*) FROM student_score;  #COUNT()函数
SELECT MAX(score) FROM student_score;  #MAX()函数
SELECT MIN(score) FROM student_score;  MIN()函数
SELECT SUM(score) FROM student_score;  SUM()函数

组合查询
1.创建数据表

CREATE TABLE `student` (
 `sid` int(11) NOT NULL,
 `sname` varchar(30) DEFAULT NULL,
 `sage` int(11) DEFAULT NULL,
 `ssex` varchar(8) DEFAULT NULL,
 PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values
(1005,'xiaohua',19,'0'),
(1004,'xiaohong',18,'0'),
(1003,'wangwu',18,'1'),
(1002,'lisi',17,'1'),
(1001,'zhangsan',18,'1');
CREATE TABLE `sc` (
 `sid` int(11) NOT NULL,
 `cid` int(11) NOT NULL,
 `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `sc`(`sid`,`cid`,`score`) values
(1001,101,75),
(1001,102,85),
(1002,101,65),
(1002,102,95),
(1003,101,65),
(1003,102,95),
(1004,101,80),
(1004,102,80),
(1005,101,75),
(1005,102,85);
CREATE TABLE `course` (
 `cid` int(11) NOT NULL,
 `cname` varchar(30) DEFAULT NULL,
 `tid` int(11) DEFAULT NULL,
 PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `course`(`cid`,`cname`,`tid`) values
(102,'yuwen',2),
(101,'shuxue',1);
CREATE TABLE `teacher` (
 `tid` int(11) NOT NULL,
 `tname` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `teacher`(`tid`,`tname`) values
(2,'liulaoshi'),
(1,'wanglaoshi');

2.查询所有同学的学号和成绩

# 使用WHERE s. sid = sc.sid 来消除笛卡尔积。
SELECT s.sid ,s.sname ,c.score FROM student AS s ,sc AS c WHERE s.sid = c.sid ;

3.查询语文成绩在80以上同学

SELECT a.score,
       b.cname,
       s.sname
  FROM sc as a,
       course AS b,
       student AS s
 WHERE a.cid= b.cid
   AND s.sid= a.sid
   AND a.cid= 102
   AND a.score> 80

查询语文成绩比数学成绩高的同学

SELECT a.sid,
       a.score,
       s.sname
  FROM(
SELECT sid, score
  FROM sc
 WHERE cid= "102") a,
       (
SELECT sid, score
  FROM sc
 WHERE cid= "101") b,
       student as s
 WHERE a.score > b.score
   AND a.sid= b.sid
   AND s.sid= a.sid

2月17日动手实战-最佳应用实践之使用RDS MySQL和ECS搭建个人博客

学到了使用ECS和RDS搭建个人博客。
创建数据库账号和密码
访问RDS管理控制台，创建并配置数据库账号信息

部署环境
1.在ECS服务器上，安装Apache服务及其扩展包

yum -y install httpd httpd-manual mod_ssl mod_perl mod_auth_mysql

返回类似如下图结果则表示安装成功:

2.启动Apache服务

systemctl start httpd.service

3.打开浏览器输入ECS服务器的公网IP，如果显示如下图的测试页面表示Apache服务安装成功。

4.WordPress是使用PHP语言开发的博客平台，安装PHP。

#安装PHP
yum -y install php php-mysql gd php-gd gd-devel php-xml php-common php-mbstring php-ldap php-pear php-xmlrpc php-imap
#创建PHP测试页面
echo "<?php phpinfo(); ?>" > /var/www/html/phpinfo.php
#重启Apache服务
systemctl restart httpd

打开浏览器，访问http://<ECS公网IP>/phpinfo.php，显示如下页面表示PHP安装成功

安装和配置WordPress
1.安装WordPress

yum -y install wordpress

显示如下信息表示安装成功：

2.修改WordPress配置文件

#a.修改wp-config.php指向路径为绝对路径
# 进入/usr/share/wordpress目录。
cd /usr/share/wordpress
# 修改路径。
ln -snf /etc/wordpress/wp-config.php wp-config.php
# 查看修改后的目录结构。
ll

#b.移动WordPress到Apache根目录
# 在Apache的根目录/var/www/html下，创建一个wp-blog文件夹。
mkdir /var/www/html/wp-blog
mv * /var/www/html/wp-blog/

#c.修改wp-config.php配置文件
#database_name_here为之前步骤中创建的数据库名称，本示例为wordpress。
#username_here为之前步骤中创建的数据库用户名，本示例为test_user。
#password_here为之前步骤中创建的数据库登录密码，本示例为Password1213。
#数据库地址为RDS的连接地址（在RDS管理控制台 实例详情 页查看或者开通资源后再左侧资源栏列表中查看RDS连接地址）
sed -i 's/database_name_here/wordpress/' /var/www/html/wp-blog/wp-config.php
sed -i 's/username_here/test_user/' /var/www/html/wp-blog/wp-config.php
sed -i 's/password_here/Password1213/' /var/www/html/wp-blog/wp-config.php
sed -i 's/localhost/数据库地址/' /var/www/html/wp-blog/wp-config.php

#d.查看配置文件信息是否修改成功
cat -n /var/www/html/wp-blog/wp-config.php

d.修改内容如下图所示：

3.重启Apache服务

systemctl restart httpd

4.初始化WordPress
a. 打开浏览器并访问http://<ECS公网IP>/wp-blog/wp-admin/install.php。
b. 设置站点名称、管理员用户名和密码，然后单击Install WordPress完成WordPress初始化。

c. 单击Log In，输入用户名和密码，然后再次单击Log In打开WordPress站点。

登录后，就可以根据需要创建内容进行发布了。至此已完成WordPress的搭建。

2月18日 RDS MySQL的SQL问题诊断与调优

RDS MySQL的SQL问题诊断与调优
MySQL实例实操
1.创建数据库表

CREATE TABLE IF NOT EXISTS `test_tb`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `address` VARCHAR(40) NOT NULL,
   `birthday` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.执行如下命令，查看SQL执行计划

explain select * from test_tb where name='abc';

返回如下如下结果，你可以发现key和key_len的值都是null。

3.执行如下命令，查看test_tb表的表结构

show create table test_tb;

返回如下结果，您可以查看到test_tb表的表结构

4.在执行结果中，单击图标

返回如下结果，您可以看到完整的test_tb表的表结构。

5.创建test_tb表的索引

alter table test_tb add index(name);

返回如下结果，表示test_tb表的索引创建成功。

6.重复第2步和第3步的操作，再次查看完整的test_tb表的表结构，会发现表结构中多了一个KEY name (name)

7.执行以下命令，查看执行SQL计划

explain select * from test_tb where name='abc';

返回如下如下结果，你会发现key和key_len的值发生了改变。

8.查看数据库运行中的SQL

select * from `information_schema`.`PROCESSLIST`

返回如下结果，可以查看到数据库运行中的SQL。

码农公寓