devbox
小丑西瓜9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql80-DBA数据库学习1-数据库安装

作者：小丑西瓜9 | 2024-03-28 20:00:18

赞

踩

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

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

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

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

  1. [root@localhost ~]# yum repolist all | grep mysq
  2. file:///media/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /media/repodata/repomd.xml"
  3. 正在尝试其它镜像。
  4. mysql-cluster-7.5-community/x86_64                  MySQL Cluster 7. 禁用
  5. mysql-cluster-7.5-community-source                  MySQL Cluster 7. 禁用
  6. mysql-cluster-7.6-community/x86_64                  MySQL Cluster 7. 禁用
  7. mysql-cluster-7.6-community-source                  MySQL Cluster 7. 禁用
  8. mysql-cluster-8.0-community/x86_64                  MySQL Cluster 8. 禁用
  9. mysql-cluster-8.0-community-debuginfo/x86_64        MySQL Cluster 8. 禁用
  10. mysql-cluster-8.0-community-source                  MySQL Cluster 8. 禁用
  11. mysql-cluster-innovation-community/x86_64           MySQL Cluster In 禁用
  12. mysql-cluster-innovation-community-debuginfo/x86_64 MySQL Cluster In 禁用
  13. mysql-cluster-innovation-community-source           MySQL Cluster In 禁用
  14. mysql-connectors-community/x86_64                   MySQL Connectors 启用:   242
  15. mysql-connectors-community-debuginfo/x86_64         MySQL Connectors 禁用
  16. mysql-connectors-community-source                   MySQL Connectors 禁用
  17. mysql-innovation-community/x86_64                   MySQL Innovation 禁用
  18. mysql-innovation-community-debuginfo/x86_64         MySQL Innovation 禁用
  19. mysql-innovation-community-source                   MySQL Innovation 禁用
  20. mysql-tools-community/x86_64                        MySQL Tools Comm 启用:   104
  21. mysql-tools-community-debuginfo/x86_64              MySQL Tools Comm 禁用
  22. mysql-tools-community-source                        MySQL Tools Comm 禁用
  23. mysql-tools-innovation-community/x86_64             MySQL Tools Inno 禁用
  24. mysql-tools-innovation-community-debuginfo/x86_64   MySQL Tools Inno 禁用
  25. mysql-tools-innovation-community-source             MySQL Tools Inno 禁用
  26. mysql-tools-preview/x86_64                          MySQL Tools Prev 禁用
  27. mysql-tools-preview-source                          MySQL Tools Prev 禁用
  28. mysql57-community/x86_64                            MySQL 5.7 Commun 禁用
  29. mysql57-community-source                            MySQL 5.7 Commun 禁用
  30. mysql80-community/x86_64                            MySQL 8.0 Commun 启用:   465
  31. mysql80-community-debuginfo/x86_64                  MySQL 8.0 Commun 禁用
  32. 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
  1. [root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
  2. 2024-03-20T09:36:13.003622Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !R,V#Fj8qi*P
  3. [root@localhost ~]# mysql -uroot -p                              
  4. Enter password: 
  5. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  6. [root@localhost ~]# mysql -uroot -p
  7. Enter password: 
  8. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  9. [root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
  10. 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
  1. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
  2.  
  3. 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

  1. [root@localhost ~]# grep "password" /var/log/mysqld.log 
  2. 2024-03-20T09:36:13.003622Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !R,V#Fj8qi*P
  3. [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;

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

数据库操作

建库：

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

查看数据库：

show databases;

  1. mysql> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | discuz             |
  6. | information_schema |
  7. | mysql              |
  8. | performance_schema |
  9. | sys                |
  10. +--------------------+
  11. 5 rows in set (0.01 sec)
  1. mysql> create database DISCUZ;
  2. Query OK, 1 row affected (0.01 sec)
  3.  
  4. mysql> show databases;
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | DISCUZ             |
  9. | discuz             |
  10. | information_schema |
  11. | mysql              |
  12. | performance_schema |
  13. | sys                |
  14. +--------------------+
  15. 6 rows in set (0.00 sec)
  16.  
  17. mysql>

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

DISCUZ 和discuz是不同的数据库

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

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

使用某个数据库

USE +数据库名字

  1. mysql> use discuz;
  2. Database changed

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

  1. mysql> select database();
  2. +------------+
  3. | database() |
  4. +------------+
  5. | discuz     |
  6. +------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql>

删除数据库drop database +名字

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

数据库备份

确定目录

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

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

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

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

创建表create tables 表名

create table t1 (id int);

  1. mysql> create table t2 (id int);
  2. Query OK, 0 rows affected (0.03 sec)
  3.  
  4. mysql>

表的描述说明 desc +表名

  1. mysql> create table t2 (id int);
  2. Query OK, 0 rows affected (0.03 sec)
  3.  
  4. mysql> create table t3 (id int,name varchar(20));
  5. Query OK, 0 rows affected (0.03 sec)
  6.  
  7. mysql> desc t2;
  8. +-------+------+------+-----+---------+-------+
  9. | Field | Type | Null | Key | Default | Extra |
  10. +-------+------+------+-----+---------+-------+
  11. | id    | int  | YES  |     | NULL    |       |
  12. +-------+------+------+-----+---------+-------+
  13. 1 row in set (0.01 sec)
  14.  
  15. mysql> desc t3;
  16. +-------+-------------+------+-----+---------+-------+
  17. | Field | Type        | Null | Key | Default | Extra |
  18. +-------+-------------+------+-----+---------+-------+
  19. | id    | int         | YES  |     | NULL    |       |
  20. | name  | varchar(20) | YES  |     | NULL    |       |
  21. +-------+-------------+------+-----+---------+-------+
  22. 2 rows in set (0.01 sec)

默认值 default

key主键

null 可否为空

type 字段类型

extra 外键

表的数据插入及查询  select insert  

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

  1. mysql> insert into t3 values (1,"liuyang");
  2. Query OK, 1 row affected (0.02 sec)
  3.  
  4. mysql> insert into t3 values (2,"wangyan");
  5. Query OK, 1 row affected (0.01 sec)
  6.  
  7. mysql> select * from t3;
  8. +------+---------+
  9. | id   | name    |
  10. +------+---------+
  11. |    1 | liuyang |
  12. |    2 | wangyan |
  13. +------+---------+
  14. 2 rows in set (0.00 sec)
  15.  
  16. mysql>

综合例子

  1. mysql> create database school;
  2. Query OK, 1 row affected (0.01 sec)
  3.  
  4. mysql> use school;
  5. Database changed
  6. mysql> create table student1 (id int,
  7.     -> name varchar(20),
  8.     -> sex enum('m','f'),
  9.     -> age int);
  10. Query OK, 0 rows affected (0.09 sec)
  11.  
  12. mysql> desc student1;
  13. +-------+---------------+------+-----+---------+-------+
  14. | Field | Type          | Null | Key | Default | Extra |
  15. +-------+---------------+------+-----+---------+-------+
  16. | id    | int           | YES  |     | NULL    |       |
  17. | name  | varchar(20)   | YES  |     | NULL    |       |
  18. | sex   | enum('m','f') | YES  |     | NULL    |       |
  19. | age   | int           | YES  |     | NULL    |       |
  20. +-------+---------------+------+-----+---------+-------+
  21. 4 rows in set (0.00 sec)
  22.  
  23. mysql> insert into student1 values (1,'zhangsan','m',23);
  24. Query OK, 1 row affected (0.01 sec)
  25.  
  26. mysql> insert into student1 values (2,'lisi','f',22);
  27. Query OK, 1 row affected (0.00 sec)
  28.  
  29. mysql> insert into student1 values (3,'wangwu','v',22);
  30. ERROR 1265 (01000): Data truncated for column 'sex' at row 1
  31. mysql> insert into student1 values (3,'wangwu','m',22);
  32. Query OK, 1 row affected (0.01 sec)
  33.  
  34. mysql> select * from student1;
  35. +------+----------+------+------+
  36. | id   | name     | sex  | age  |
  37. +------+----------+------+------+
  38. |    1 | zhangsan | m    |   23 |
  39. |    2 | lisi     | f    |   22 |
  40. |    3 | wangwu   | m    |   22 |
  41. +------+----------+------+------+
  42. 3 rows in set (0.00 sec)
  43.  
  44. mysql>

DQL查询

创造数据

  1.  
  2. mysql> create database haha;
  3. Query OK, 1 row affected (0.01 sec)
  4.  
  5. mysql> use haha;
  6. Database changed
  7. mysql> create table t3 (id int,name varchar(20),age int);
  8. Query OK, 0 rows affected (0.03 sec)
  9.  
  10. mysql> insert into t3 values values (1,'zhangsan',23);
  11. 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
  12. mysql> insert into t3 values (1,'zhangsan',23);
  13. Query OK, 1 row affected (0.01 sec)
  14.  
  15. mysql> insert into t3 values (2,'lisi',24);
  16. Query OK, 1 row affected (0.00 sec)
  17.  
  18. mysql> insert into t3 values (3,'wangwu',18);
  19. Query OK, 1 row affected (0.00 sec)
  20.  
  21. mysql>

create

  1.  
  2.  
  3.  
  4. mysql> create database company;
  5. Query OK, 1 row affected (0.01 sec)
  6.  
  7. mysql> create table company.employee5(
  8. id int primary key AUTO_INCREMENT not null,
  9. name varchar(30) not null,
  10. sex enum('male','female') default 'male' not null,
  11. hire_date date not null,
  12. post varchar(50) not null,
  13. job_description varchar(100),
  14. salary double(15,2) not null,
  15. office int,
  16. dep_id int);
  1. mysql> desc company.employee5
  2.     -> ;
  3. +-----------------+-----------------------+------+-----+---------+----------------+
  4. | Field           | Type                  | Null | Key | Default | Extra          |
  5. +-----------------+-----------------------+------+-----+---------+----------------+
  6. | id              | int                   | NO   | PRI | NULL    | auto_increment |
  7. | name            | varchar(30)           | NO   |     | NULL    |                |
  8. | sex             | enum('male','female') | NO   |     | male    |                |
  9. | hire_date       | date                  | NO   |     | NULL    |                |
  10. | job_description | varchar(100)          | YES  |     | NULL    |                |
  11. | salary          | double(15,2)          | NO   |     | NULL    |                |
  12. | office          | int                   | YES  |     | NULL    |                |
  13. | dep_id          | int                   | YES  |     | NULL    |                |
  14. +-----------------+-----------------------+------+-----+---------+----------------+
  15. 8 rows in set (0.00 sec)
  16.  
  17. mysql>

insert

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

select

  1. mysql> select * from company.employee5;
  2. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
  3. | id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
  4. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
  5. |  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
  6. |  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
  7. |  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
  8. |  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
  9. |  5 | tianyun   | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
  10. |  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
  11. |  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
  12. |  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
  13. |  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
  14. | 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
  15. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
  16. 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行

  1. mysql> select name,salary from company.employee5 order by salary desc limit 4;
  2. +-------+----------+
  3. | name  | salary   |
  4. +-------+----------+
  5. | emma  | 20000.00 |
  6. | robin |  8000.00 |
  7. | alice |  7200.00 |
  8. | harry |  6000.00 |
  9. +-------+----------+
  10. 4 rows in set (0.00 sec)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小丑西瓜9/article/detail/331019
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号