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)

好文推荐