Mysql

最新推荐文章于 2024-12-10 09:45:27 发布

feige_linux

最新推荐文章于 2024-12-10 09:45:27 发布

阅读量364

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/feige_linux/article/details/109193216

版权

这篇博客详细介绍了MySQL的安装配置、数据库及表的操作，包括启动MariaDB、创建与删除数据库、表结构修改、用户权限管理以及数据的增删改查操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.mysql安装与配置

[root@client ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server
Installed:
  keyutils-libs-devel-1.5.10-6.el8.x86_64                       krb5-devel-1.17-18.el8.x86_64                                       
  libcom_err-devel-1.45.4-3.el8.x86_64                          libkadm5-1.17-18.el8.x86_64                                         
  libselinux-devel-2.9-3.el8.x86_64                             libsepol-devel-2.9-1.el8.x86_64                                     
  libverto-devel-0.3.0-5.el8.x86_64                             mariadb-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64              
  mariadb-backup-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 mariadb-common-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64       
  mariadb-connector-c-3.0.7-1.el8.x86_64                        mariadb-connector-c-config-3.0.7-1.el8.noarch                       
  mariadb-connector-c-devel-3.0.7-1.el8.x86_64                  mariadb-devel-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64        
  mariadb-errmsg-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 mariadb-gssapi-server-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
  mariadb-server-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 mariadb-server-utils-3:10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 
  openssl-devel-1:1.1.1c-15.el8.x86_64                          pcre2-devel-10.32-1.el8.x86_64                                      
  pcre2-utf16-10.32-1.el8.x86_64                                pcre2-utf32-10.32-1.el8.x86_64                                      
  perl-DBD-MySQL-4.046-3.module_el8.1.0+203+e45423dc.x86_64     zlib-devel-1.2.11-13.el8.x86_64                                     

Complete!

2.启动mariadb并设置开机自动启动

[root@client ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@client ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2020-10-21 01:22:44 CST; 1min 3s ago
     Docs: man:mysqld(8)

3.进入mysql并设置账户和密码

[root@client ~]# mysql -uroot 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password = password('123');
Query OK, 0 rows affected (0.001 sec)

4.查看数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

5.创建一个feige数据库

MariaDB [(none)]> create database feige;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| feige              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

6.并创建一张表student，该表包含三个字段（id，name，age）

MariaDB [feige]> create table student(id int not null,name varchar(10),age tinyint);
Query OK, 0 rows affected (0.006 sec)
MariaDB [feige]> show tables;
+-----------------+
| Tables_in_feige |
+-----------------+
| student         |
+-----------------+
1 row in set (0.001 sec)
MariaDB [feige]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

7.修改表中的内容。加入分数字段score

MariaDB [feige]> alter table student add score float;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [feige]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

8.删除表中的字段age

MariaDB [feige]> alter table student drop age;
Query OK, 0 rows affected (0.010 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [feige]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

9.修改表中字段的定义

MariaDB [feige]> alter table student modify score float not null; 
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [feige]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| score | float       | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

10.数据库创建一个用户并删除

MariaDB [(none)]> create user tom@192.168.240.134 identified by '123';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> drop user tom@192.168.240.134;
Query OK, 0 rows affected (0.000 sec)

11.授权tom用户在远程主机192.168.240.134上对feige数据库有所有权限,然后刷新权限

MariaDB [(none)]> show tables from feige;
+-----------------+
| Tables_in_feige |
+-----------------+
| student         |
+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> grant all on feige.student to tom@192.168.240.134 identified by '123';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

12.取消tom用户在远程主机192.168.240.134上对feige数据库有所有权限,然后刷新权限

MariaDB [(none)]> revoke all on feige.student from tom@192.168.240.134 ;
Query OK, 0 rows affected (0.000 sec)

13.insert给表中插入内容，select查看内容

MariaDB [feige]> insert student value(1,'tom',20);
Query OK, 1 row affected (0.007 sec)
MariaDB [feige]> select * from student;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | tom  |    20 |
+----+------+-------+
1 row in set (0.001 sec)

14.updata更新表中字段的内容

MariaDB [feige]> select * from student;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | tom  |    20 |
+----+------+-------+

MariaDB [feige]> update student set name = 'jerry' where score = 20;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [feige]> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | jerry |    20 |
+----+-------+-------+
1 row in set (0.000 sec)

15.delete删除表中的内容

MariaDB [feige]> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | jerry |    20 |
+----+-------+-------+
1 row in set (0.000 sec)

MariaDB [feige]> delete from student where id = 1;
Query OK, 1 row affected (0.002 sec)

MariaDB [feige]> select * from student;
Empty set (0.000 sec)