掌握能力
核心技能
核心技能
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)