mysql80-DBA数据库学习1

掌握能力

核心技能

mysql部署

官网地址www.mysql.com 或者www.oracle.com

https://dev.mysql.com/downloads/repo/yum/

Install the RPM you downloaded for your system, for example:

yum install mysql80-community-release-{platform}-{version-number}.noarch.rpm

yum repolist

(3/3): mysql80-community/x86_64/primary_db                                               | 278 kB  00:00:01     
源标识                                                   源名称                                            状态
!c7-media                                                CentOS-7 - Media                                  4,070
mysql-connectors-community/x86_64                        MySQL Connectors Community                          242
mysql-tools-community/x86_64                             MySQL Tools Community                               104
mysql80-community/x86_64                                 MySQL 8.0 Community Server                          465
repolist: 4,881

新安装了三个关于mysql的仓库

[root@localhost ~]# yum repolist all | grep mysq

[root@localhost ~]# yum repolist all | grep mysq
file:///media/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /media/repodata/repomd.xml"
正在尝试其它镜像。
mysql-cluster-7.5-community/x86_64                  MySQL Cluster 7. 禁用
mysql-cluster-7.5-community-source                  MySQL Cluster 7. 禁用
mysql-cluster-7.6-community/x86_64                  MySQL Cluster 7. 禁用
mysql-cluster-7.6-community-source                  MySQL Cluster 7. 禁用
mysql-cluster-8.0-community/x86_64                  MySQL Cluster 8. 禁用
mysql-cluster-8.0-community-debuginfo/x86_64        MySQL Cluster 8. 禁用
mysql-cluster-8.0-community-source                  MySQL Cluster 8. 禁用
mysql-cluster-innovation-community/x86_64           MySQL Cluster In 禁用
mysql-cluster-innovation-community-debuginfo/x86_64 MySQL Cluster In 禁用
mysql-cluster-innovation-community-source           MySQL Cluster In 禁用
mysql-connectors-community/x86_64                   MySQL Connectors 启用:   242
mysql-connectors-community-debuginfo/x86_64         MySQL Connectors 禁用
mysql-connectors-community-source                   MySQL Connectors 禁用
mysql-innovation-community/x86_64                   MySQL Innovation 禁用
mysql-innovation-community-debuginfo/x86_64         MySQL Innovation 禁用
mysql-innovation-community-source                   MySQL Innovation 禁用
mysql-tools-community/x86_64                        MySQL Tools Comm 启用:   104
mysql-tools-community-debuginfo/x86_64              MySQL Tools Comm 禁用
mysql-tools-community-source                        MySQL Tools Comm 禁用
mysql-tools-innovation-community/x86_64             MySQL Tools Inno 禁用
mysql-tools-innovation-community-debuginfo/x86_64   MySQL Tools Inno 禁用
mysql-tools-innovation-community-source             MySQL Tools Inno 禁用
mysql-tools-preview/x86_64                          MySQL Tools Prev 禁用
mysql-tools-preview-source                          MySQL Tools Prev 禁用
mysql57-community/x86_64                            MySQL 5.7 Commun 禁用
mysql57-community-source                            MySQL 5.7 Commun 禁用
mysql80-community/x86_64                            MySQL 8.0 Commun 启用:   465
mysql80-community-debuginfo/x86_64                  MySQL 8.0 Commun 禁用
mysql80-community-source                            MySQL 8.0 Commun 禁用

Install MySQL by the following command (for dnf-enabled systems, replace yum in the command ):

$> sudo yum install mysql-community-server

Start the MySQL server with the following command:

$> systemctl start mysqld

You can check the status of the MySQL server with the following command:

$> systemctl status mysqld

$>systemctl enable mysqld

mysql -uroot -p 登录异常，

A superuser account 'root'@'localhost is created. A password for the superuser is set and stored in the error log file. To reveal it, use the following command:

$> sudo grep 'temporary password' /var/log/mysqld.log

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2024-03-20T09:36:13.003622Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !R,V#Fj8qi*P
[root@localhost ~]# mysql -uroot -p                              
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2024-03-20T09:36:13.003622Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !R,V#Fj8qi*P

!R,V#Fj8qi*P就是root密码

mysql -uroot -p

输入!R,V#Fj8qi*P

Change the root password as soon as possible by logging in with the generated, temporary password and set a custom password for the superuser account:

$> mysql -uroot -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@098';

国内镜像安装

mirrors.ustc.edu.cn这个是中科大的镜像系统

下载如下rpm

mysql-community-client-8.0.36-1.el7.x86_64
mysql-community-common-8.0.36-1.el7.x86_64
mysql-community-libs-8.0.36-1.el7.x86_64
mysql-community-icu-data-files-8.0.36-1.el7.x86_64
mysql-community-libs-compat-8.0.36-1.el7.x86_64
mysql-community-client-plugins-8.0.36-1.el7.x86_64
mysql-community-server-8.0.36-1.el7.x86_64

如果用centos7去装MySQL5.7 还需要额外的安装3个包

libaio.x86_64 perl.x86_64 net-tools.x86_64

到当前下载文件的目录下

yum install -y *.rpm就会安装下载的包了

systemctl start mysqld 启动mysql

systemctl enable mysqld 开机启动mysql

netstat -anpt 查看端口号

systemctl status mysqld 查看服务启动状态

mysql -uroot -p提示登录失败

解决方法

grep “password” /var/log/mysqld.log

[root@localhost ~]# grep "password" /var/log/mysqld.log 
2024-03-20T09:36:13.003622Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !R,V#Fj8qi*P
[root@localhost ~]#

在root@localhost：后的内容为mysql的root密码

mysql》show databases;报错提示需要改密码

#mysqladmin -uroot -p'!R,V#Fj8qi*P' password 'Qianfeng@123'

有个小警告，

或者mysql>下使用

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

虚拟机环境这时可以做一个快照名字为mysql安装后，后期操作混乱后可以恢复到次快照

1.rpm安装

2.源码安装

3自建mysql-yum仓库

--------------------------------------------------------------------------------------------------------------------------------

mysql忘记root密码

首先，去配置文件/etc/my.cnf）：

【mysqld】

添加skip-grant-tables

重启mysql systemctl restart mysqld

mysql -uroot -p 此时无密码

use mysql；

配置root密码为空

update user set authentication_string='' where user='root';

quit 退出

使用# 注销配置文件my.cnf的 skip-grant-tables

重启mysql systemctl restart mysqld

mysql -uroot 回车无密码

ALTER user 'root'@'localhost' IDENTIFIED BY 'Root@098';

Root@098为新密码

为了保障，最好还是多创建一个超级特权用户：

CREATE USER '账号'@'%' IDENTIFIED BY '密码';

GRANT ALL PRIVILEGES ON *.* TO '账号'@'%' WITH GRANT OPTION;

最后刷新权限 flush privileges;

---------------------------------------------------------------------------------------------------------------------------------

数据库操作

建库：

mysql> create database discuz;
Query OK, 1 row affected (0.01 sec)

查看数据库：

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| discuz             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database DISCUZ;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DISCUZ             |
| discuz             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

注意：数据库的名字严格区分大小写

DISCUZ 和discuz是不同的数据库

mysql输入 “”回车“”不是执行和结束，结束需要用“；

数据库名字不能用关键字、不能单独用数字和特殊字符、常用拼音和单词来定义。

使用某个数据库

USE +数据库名字

mysql> use discuz;
Database changed

mysql> select database();查看我在那个数据库中

mysql> select database();
+------------+
| database() |
+------------+
| discuz     |
+------------+
1 row in set (0.00 sec)

mysql>

删除数据库drop database +名字

mysql> drop database DISCUZ;
Query OK, 0 rows affected (0.01 sec)

数据库备份

确定目录

如果是源码安装，配置文件中的配置项 datadir=/usr/local/mysql/这个目录就是后期的数据库实体目录。

yum或者rpm安装的mysql的文件默认目录为/var/lib/mysql ，这个目录为数据库的实体文件目录，

[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ls
auto.cnf       binlog.000004  ca-key.pem       discuz             ibdata1       mysql            performance_schema  server-key.pem
binlog.000001  binlog.000005  ca.pem           #ib_16384_0.dblwr  ibtmp1        mysql.ibd        private_key.pem     sys
binlog.000002  binlog.000006  client-cert.pem  #ib_16384_1.dblwr  #innodb_redo  mysql.sock       public_key.pem      undo_001
binlog.000003  binlog.index   client-key.pem   ib_buffer_pool     #innodb_temp  mysql.sock.lock  server-cert.pem     undo_002
[root@localhost mysql]#

discuz目录就是我们创建的数据库。

创建表create tables 表名

create table t1 (id int);

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql>

表的描述说明 desc +表名

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t3 (id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

默认值 default

key主键

null 可否为空

type 字段类型

extra 外键

表的数据插入及查询 select insert

insert 字符需要用到双引号例如"liuyang"

mysql> insert into t3 values (1,"liuyang");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t3 values (2,"wangyan");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+---------+
| id   | name    |
+------+---------+
|    1 | liuyang |
|    2 | wangyan |
+------+---------+
2 rows in set (0.00 sec)

mysql>

综合例子

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed
mysql> create table student1 (id int,
    -> name varchar(20),
    -> sex enum('m','f'),
    -> age int);
Query OK, 0 rows affected (0.09 sec)

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | YES  |     | NULL    |       |
| name  | varchar(20)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int           | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into student1 values (1,'zhangsan','m',23);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student1 values (2,'lisi','f',22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student1 values (3,'wangwu','v',22);
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into student1 values (3,'wangwu','m',22);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student1;
+------+----------+------+------+
| id   | name     | sex  | age  |
+------+----------+------+------+
|    1 | zhangsan | m    |   23 |
|    2 | lisi     | f    |   22 |
|    3 | wangwu   | m    |   22 |
+------+----------+------+------+
3 rows in set (0.00 sec)

mysql>

DQL查询

创造数据


mysql> create database haha;
Query OK, 1 row affected (0.01 sec)

mysql> use haha;
Database changed
mysql> create table t3 (id int,name varchar(20),age int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 values values (1,'zhangsan',23);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (1,'zhangsan',23)' at line 1
mysql> insert into t3 values (1,'zhangsan',23);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values (2,'lisi',24);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values (3,'wangwu',18);
Query OK, 1 row affected (0.00 sec)

mysql>

create




mysql> create database company;
Query OK, 1 row affected (0.01 sec)

mysql> create table company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int);

mysql> desc company.employee5
    -> ;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| id              | int                   | NO   | PRI | NULL    | auto_increment |
| name            | varchar(30)           | NO   |     | NULL    |                |
| sex             | enum('male','female') | NO   |     | male    |                |
| hire_date       | date                  | NO   |     | NULL    |                |
| job_description | varchar(100)          | YES  |     | NULL    |                |
| salary          | double(15,2)          | NO   |     | NULL    |                |
| office          | int                   | YES  |     | NULL    |                |
| dep_id          | int                   | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql>

insert

insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);

select

mysql> select * from company.employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | tianyun   | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

select 排序

order by

例如 +asc升序默认

select name,salary from company.employee5 order by salary asc; 按照salary升序排列。

mysql> select name,salary from company.employee5 order by salary asc;
+-----------+----------+
| name | salary |
+-----------+----------+
| tianyun | 600.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| jack | 5000.00 |
| tom | 5500.00 |
| harry | 6000.00 |
| alice | 7200.00 |
| robin | 8000.00 |
| emma | 20000.00 |
+-----------+----------+
10 rows in set (0.00 sec)

desc 降序

mysql> select name,salary from company.employee5 order by salary desc;
+-----------+----------+
| name | salary |
+-----------+----------+
| emma | 20000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
| tom | 5500.00 |
| jack | 5000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| tianyun | 600.00 |
+-----------+----------+
10 rows in set (0.00 sec)

limit 3 限制，只输出3行

mysql> select name,salary from company.employee5 order by salary desc limit 4;
+-------+----------+
| name  | salary   |
+-------+----------+
| emma  | 20000.00 |
| robin |  8000.00 |
| alice |  7200.00 |
| harry |  6000.00 |
+-------+----------+
4 rows in set (0.00 sec)