mysql的安装+数据库的管理

mysql的安装

教程：

https://blog.csdn.net/lsrzhangmin/article/details/132492978?spm=100

1.2014.3001.5501

步骤：

1.官网下载或者wget,然后解压 -xf, 卸载mariadb   yum -y install *mariadb

2. 确定 mysql-community-server 正常安装之后就可以开始配置

   依次安装所需要的依赖包

3. 初始化 mysqld 服务 mysqld  --initialize

4. 启动服务 systemctl start mysqld

注意：如果在启动时，出现以下的错误：

[root@mysql1 ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

这是因为/var/lib/mysql目录权限不够

修改权限并启动mysql:
 

[root@mysql1 ~]#setenforce 0

[root@mysql1 ~]#  chown -R mysql:mysql /var/lib/mysql
[root@mysql1 ~]# chmod -R 777 /var/lib/mysql
[root@mysql1 ~]# systemctl start mysqld.service
[root@mysql1 ~]# ps -ef |grep mysq
mysql      2396      1  6 10:03 ?        00:00:00 /usr/sbin/mysqld
root       2443   1355  0 10:03 pts/0    00:00:00 grep --color=auto mysq

[root@mysql1 ~]# cd /var/lib/mysql
[root@mysql1 mysql]# ll

5. 添加开机启动列表 systecmctrl enable mysqld

6. 在 /var/log/mysqld.log 中找到初始密码，前后复制空格

cat /var/log/mysqld.log | grep password

7. 登录管理 mysql -uroot -p

将复制的密码粘贴到输入密码的区域，实现登录

8. 设置新的密码

在MySQL内将密码修改为root

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
 

数据库的管理

数据库的对象

1）数据库系统  DBMS

2）数据库   DB

3)  表    table

4)  记录   record

5)  字段   field

1.数据库的操作

创建库:

mysql> create database test charset utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

删除库:

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

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

查看库:

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

2.表的操作

选择库：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

查看表：

mysql> show tables;

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

创建表：

语法： create table 表名();

删除表：

drop table 表名；

新增列：在password后加一列

mysql> ALTER TABLE `test`.`user` 
    -> ADD COLUMN `realname` VARCHAR(45) NULL AFTER `password`;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user;      //查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(45) | NO   | UNI | NULL    |                |
| password | varchar(45) | NO   |     | NULL    |                |
| realname | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

修改列名：修改realname列名为zsxm

mysql> ALTER TABLE `test`.`user` 
    -> CHANGE COLUMN `realname` `zsxm` VARCHAR(45) NULL DEFAULT NULL ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(45) | NO   | UNI | NULL    |                |
| password | varchar(45) | NO   |     | NULL    |                |
| zsxm     | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

删除列：删除zsxm列

mysql> ALTER TABLE `test`.`user` 
    -> DROP COLUMN `zsxm`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(45) | NO   | UNI | NULL    |                |
| password | varchar(45) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
 

3.记录的操作

主服务器：新增，删除，修改操作比较多，

从服务器：查询操作较多

新增：

insret into 表名 （字段列表名）values (字段值列表）

mysql> select * from user;
Empty set (0.00 sec)

mysql> insert into user (id,username,password) values(3,'zhangsan','zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  3 | zhangsan | zhangsan |
+----+----------+----------+
1 row in set (0.00 sec)

如果不写字段列表名，values必须写所有的字段值并且字段类型要正确

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

删除：

delete from 表名 where 条件;

不加条件的时候，整个表里面的数据都会被删除

mysql> delete from user where username='zhangsan';
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | lisi     | lisi     |
+----+----------+----------+
1 row in set (0.00 sec)

修改:

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | zhangsan |
|  2 | lisi     | lisi     |
|  3 | wangwu   | wangwu   |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql> update user set password=123 where username='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | lisi     |
|  3 | wangwu   | wangwu   |
+----+----------+----------+
3 rows in set (0.00 sec)