Mysql

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)