mysql学习笔记---mysql的安装--rpm安装

安装mysql

rpm安装（以mysql 5.7.29为例）

1,在mysql官方网站下载rpm包,将文件传输到linux里

[root@localhost ~]# ls
anaconda-ks.cfg                           mysql-community-5.7.29-1.el7.src.rpm
mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

2，解压 tar xf

[root@localhost ~]# tar xf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar 
[root@localhost ~]# ls
anaconda-ks.cfg
mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
mysql-community-5.7.29-1.el7.src.rpm
mysql-community-client-5.7.29-1.el7.x86_64.rpm
mysql-community-common-5.7.29-1.el7.x86_64.rpm
mysql-community-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-server-5.7.29-1.el7.x86_64.rpm
mysql-community-test-5.7.29-1.el7.x86_64.rpm

3，开始安装yum install mysql-community-* -y

[root@localhost ~]# yum install mysql-community-*  -y
已加载插件：fastestmirror
正在检查 mysql-community-5.7.29-1.el7.src.rpm: mysql-community-5.7.29-1.el7.src
无法添加软件包 mysql-community-5.7.29-1.el7.src.rpm 至操作中。不属于任何可兼容的架构：src
正在检查 mysql-community

4，启动mysql

[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service

mysql daemon :守护进程就是一直在内存中运行的进程，不会停止，等着用户的访问

5，查看MySQL是否启动

如何判断一个进程是否启动？
1,看进程 
ps aux|grep mysqld
2，看端口:
netstat -anplut|grep mysqld
lsof -i :3306

6，找到mysql的登录密码（临时）

[root@localhost ~]# cat /var/log/mysqld.log|grep temp
2020-10-05T06:58:28.967937Z 1 [Note] A temporary password is generated for root@localhost: fQxcO>d-q32:
2020-10-05T06:58:31.509157Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

7，登录mysql：

mysql -uroot -p'>fQxcO>d-q32:'

8，重设mysql的root用户的密码（否则无法对数据库进行任何操作，会报错）

mysql> alter user 'root'@'localhost' identified by 'Sanchuang123#';
Query OK, 0 rows affected (0.01 sec)

root@localhost 代表root用户只能在本地登录，不能在其他电脑上登录过来，进入mysql

查看mysql 里有哪些库

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

mysql->库(database)–> 表(table)—>一行一行的数据(字段 ：field)
一个库对应一个文件

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

mysql> exit
Bye
#mysql里的库在/var/lib/mysql 下都有对应的文件
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ls
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql       mysql.sock.lock     private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysql.sock  performance_schema  public_key.pem   server-key.pem

创建一个库

mysql> create database sc;
Query OK, 1 row affected (0.00 sec)

进入sc数据库

mysql> use sc;
Database changed

查看数据库里有哪些表

mysql> show tables;
Empty set (0.00 sec)

创建一个表

mysql> create table student(id int,name varchar(20),sex char(1),age int(3));
Query OK, 0 rows affected (0.00 sec)

一个表格需要包括列名，数据类型，字符串长度

查看表结构

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

desc(describe 描述)
field：字段
id int（11） 但只能输入10个字符，有一位被符号位占用了

varchar （variable character）可变长，更节省空间一些，根据具体的字段，来分配内存空间给你

char 不可变长，指定多少字节就会分配多少字节的内存给你

插入数据：后面的数据必须跟前面的对应

mysql> insert into changsha_food(id,name,star,address) values(1,'臭豆腐',10,'坡子街');
ERROR 1366 (HY000): Incorrect string value: '\xE8\x87\xAD\xE8\xB1\x86...' for column 'name' at row 1

出错原因：
rpm安装，默认的字符集是latin1,不支持中文

可查看创建数据库的配置

mysql> show create database chenpeng;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| chenpeng | CREATE DATABASE `chenpeng` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

解决方法（永久：编译安装mysql 或二进制安装时指定字符集utf8）
解决方法（临时方法：在创建新的数据库时指定字符集为utf8(万国码)）

mysql> create database chenpeng9 default charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database chenpeng;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| chenpeng | CREATE DATABASE `chenpeng` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database chenpeng9;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| chenpeng9 | CREATE DATABASE `chenpeng9` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

再用中文就不会出错了

mysql> create table changsha_food(id int(3),name varchar(10),star int(2),address varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into changsha_food(id,name,star,address) values(1,'臭豆腐',10,'坡子街');
Query OK, 1 row affected (0.00 sec)

查询并将结果显示出来

#显示全部
mysql> select *from changsha_food;
+------+--------------+------+------------+
| id   | name         | star | address    |
+------+--------------+------+------------+
|    1 | 臭豆腐       |   10 | 坡子街     |
|    1 | 茶颜悦色     |   10 | everywhere |
+------+--------------+------+------------+
2 rows in set (0.00 sec)
#显示其中的几列
mysql> select name,address from changsha_food;
+--------------+------------+
| name         | address    |
+--------------+------------+
| 臭豆腐       | 坡子街      |
| 茶颜悦色     | everywhere |
+--------------+------------+
2 rows in set (0.01 sec)

删除库
删除库很危险，里面的表也会被一起删除，所以删除库之前要先备份。

mysql> drop database chenpeng;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenpeng9          |
| mysql              |
| performance_schema |
| sc                 |
| sc_data            |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

删除表

mysql> show tables;
+---------------------+
| Tables_in_chenpeng9 |
+---------------------+
| changsha_food       |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table changsha_food;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

退出数据库
quit 或exit