Linux~MySQL数据库具体操作

一、数据库的字符集编码设置

 （一）查看数据库默认的字符集

MariaDB [(none)]> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)MariaDB [(none)]> 

（二）永久设置字符集

#1,服务端配置文件配置字符集
[root@10 code]# vim /etc/my.cnf
。。。。。
[mysqld]
character-set-server=utf8mb4
。。。。。#2，客户端配置文件配置字符集
[root@10 ~]# vim /etc/my.cnf.d/client.cnf 
......
[client]
default-character-set=utf8mb4
......
[client-mariadb]
default-character-set=utf8mb4
......#3，重启数据库服务
[root@10 ~]# systemctl restart mariadb.service #4,验证是否配置成功
MariaDB [(none)]> create database newDataTest01-> ;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show create database newDataTest01;
+---------------+---------------------------------------------------------------------------+
| Database      | Create Database                                                           |
+---------------+---------------------------------------------------------------------------+
| newDataTest01 | CREATE DATABASE `newDataTest01` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+---------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)MariaDB [(none)]> 

二、数据库的CURD

（一）创建数据数据库

1.创建数据库方式一（内部）：

MariaDB [(none)]> create database newDataTest02 character set utf8;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqlTest03        |
| mysqlTest04        |
| mysqlTest05        |
| newDataTest01      |
| newDataTest02      |
| performance_schema |
| phpshop            |
| test               |
| wordpress          |
| world              |
+--------------------+
12 rows in set (0.00 sec)MariaDB [(none)]> 

2.创建数据库方式二（外部）：

第一种：mysqladmin -u root -p create my_new_database

[root@10 code]# mysqladmin -uroot -p1 create newDataTest03
[root@10 code]# mysqladmin -uroot -p1 create newDataTest04
[root@10 code]# MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqlTest03        |
| mysqlTest04        |
| mysqlTest05        |
| newDataTest01      |
| newDataTest02      |
| newDataTest03      |
| newDataTest04  

第二种：mysql -u root -p -e "CREATE DATABASE my_new_database;"

[root@10 code]# mysql -uroot -p1 -e "create database newDataTest05"
[root@10 code]# mysql -uroot -p1 -e "create database newDataTest06"
[root@10 code]# MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqlTest03        |
| mysqlTest04        |
| mysqlTest05        |
| newDataTest01      |
| newDataTest02      |
| newDataTest03      |
| newDataTest04      |
| newDataTest05      |
| newDataTest06 

（二）修改数据库

1.修改数据库字符集

第一种方法：

MariaDB [(none)]> create database newDataTest03 character set utf8;
MariaDB [(none)]> show create database newDataTest03 ;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| newDataTest03 | CREATE DATABASE `newDataTest03 ` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+

第二种方法：

#2，创建数据库，指定字符集
MariaDB [(none)]> create database newDataTest05 character set utf8;
MariaDB [(none)]> show create database newDataTest05 ;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| newDataTest05 | CREATE DATABASE `newDataTest05 ` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+

（三）查询数据库

1.查看数据库方式一：

information_schema ：【默认】：系统运行的状态信息，性能信息的存储库；

mysql :  #【默认】：授权权限，用户管理的数据库

performance_schema : #【默认】：系统运行的状态信息，性能信息的存储库；

test : #测试库，让用户先测试使用的；

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqlTest03        |
| mysqlTest04        |
| mysqlTest05        |
| newDataTest01      |

2.查看指定的数据库（模糊查询）：

##查看指定的库
MariaDB [(none)]> show databases like "%newData%";
+----------------------+
| Database (%newData%) |
+----------------------+
| newDataTest01        |
| newDataTest02        |
| newDataTest03        |
| newDataTest04        |
| newDataTest05        |
| newDataTest06        |
+----------------------+
6 rows in set (0.00 sec)MariaDB [(none)]> 

3.查看创建数据时的SQL语句信息

MariaDB [(none)]> show create database newDataTest01-> ;
+---------------+---------------------------------------------------------------------------+
| Database      | Create Database                                                           |
+---------------+---------------------------------------------------------------------------+
| newDataTest01 | CREATE DATABASE `newDataTest01` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+---------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)MariaDB [(none)]> 

（四）删除数据库

drop database test;

三、数据库表结构CURD

（一）表结构新增字段

语法：【alter  table 表名  add  新增字段名  新增字段类型  not  null  comment '此用户比较好'】

（二）查看数据库表结构

MariaDB [newDataTest01]> desc demoUser-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint(3)  | YES  |     | NULL    |       |
| sex   | varchar(3)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)MariaDB [newDataTest01]> 

（三）修改表名称

MariaDB [newDataTest01]> rename table demoUser to demo01-> ;
Query OK, 0 rows affected (0.00 sec)MariaDB [newDataTest01]> show tables;
+-------------------------+
| Tables_in_newDataTest01 |
+-------------------------+
| demo01                  |
+-------------------------+
1 row in set (0.00 sec)MariaDB [newDataTest01]> 

四、数据库表数据CURD

（一）创建数据库表

MariaDB [newDataTest01]> create table demoUser(id int(10),name varchar(20),age tinyint(3),sex varchar(3));
Query OK, 0 rows affected (0.00 sec)MariaDB [newDataTest01]> 

#1,语法一插入方式：
MariaDB [newDataTest01]> insert into demo01(id,name,age,sex)  value (1,"name",22,'男');
Query OK, 1 row affected (0.01 sec)

（二）更新表数据

MariaDB [newDataTest01]> select * from demo01;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | name |   22 | 男   |
+------+------+------+------+
1 row in set (0.00 sec)MariaDB [newDataTest01]> update demo01 set age = 2222 where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1MariaDB [newDataTest01]> select * from demo01;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | name |  127 | 男   |
+------+------+------+------+
1 row in set (0.00 sec)MariaDB [newDataTest01]> 

（三）查询表数据

MariaDB [newDataTest01]> select * from demo01;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | name |  127 | 男   |
+------+------+------+------+
1 row in set (0.00 sec)MariaDB [newDataTest01]> 

（四）删除表数据

MariaDB [newDataTest01]> select * from demo01;
+------+-------+------+------+
| id   | name  | age  | sex  |
+------+-------+------+------+
|    1 | name  |  127 | 男   |
|    2 | name2 |    2 | 男   |
+------+-------+------+------+
2 rows in set (0.00 sec)MariaDB [newDataTest01]> delete from demo01 where id = 2;
Query OK, 1 row affected (0.00 sec)MariaDB [newDataTest01]> select * from demo01;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | name |  127 | 男   |
+------+------+------+------+
1 row in set (0.00 sec)MariaDB [newDataTest01]> 

五、数据库服务的备份

（一）查看数据库数据目录

[root@10 code]# ll /var/lib/mysql
总用量 28712
-rw-rw---- 1 mysql mysql    16384 8月  15 10:55 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 8月  15 10:55 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 8月  15 21:32 ibdata1
-rw-rw---- 1 mysql mysql  5242880 8月  15 21:32 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 8月   9 10:16 ib_logfile1
drwx------ 2 mysql mysql     4096 8月  13 15:28 mysql
srwxrwxrwx 1 mysql mysql        0 8月  15 13:11 mysql.sock
drwx------ 2 mysql mysql       56 8月  13 11:04 mysqlTest03
drwx------ 2 mysql mysql       20 8月  13 10:40 mysqlTest04
drwx------ 2 mysql mysql       20 8月  13 10:41 mysqlTest05
drwx------ 2 mysql mysql       38 8月  15 21:13 newDataTest01
drwx------ 2 mysql mysql       20 8月  15 20:22 newDataTest02
drwx------ 2 mysql mysql       20 8月  15 20:27 newDataTest03
drwx------ 2 mysql mysql       20 8月  15 20:27 newDataTest04
drwx------ 2 mysql mysql       20 8月  15 20:29 newDataTest05
[root@10 code]# 

（二）备份数据库~物理备份

注意：需要先关闭数据库进行操作

[root@10 ~]# tar zcvf sql-`date +%F`.tar.gz /var/lib/mysql[root@10 ~]# ll
总用量 1164
-rw-r--r--  1 root    root     1534901 8月  15 21:41 sql-2024-08-15.tar.gz

（三）备份数据库~逻辑备份

mysqldump命令；

语法：【mysqldump -uroot -p1 [备份参数] > /路径/文件名.sql】

• 参数

  • -A #全备，备份所有数据库的数据信息

  • -B 数据库名 #备份指定数据库的数据信息

  • -F #备份启动之前，自动刷新日志文件（落盘）；

 1.-A #全备，备份所有数据库的数据信息

#【-A】全备
[root@10 tmp]# mysqldump -uroot -p1 -A > ./qb.sql
[root@10 tmp]# ll
总用量 1912
-rw-r--r--  1 root    root     1302576 8月  15 22:02 qb.sql

2.-B 数据库名 #备份指定数据库的数据信息

#【-B】指定库备份
[root@10 tmp]# mysqldump -uroot -p1 -B newDataTest01 newDataTest02 > ./kb.sql
[root@10 tmp]# [root@wa ~]# ll
总用量 2156
-rw-r--r--  1 root    root        2269 8月  15 22:04 kb.sql

 3.-F #备份启动之前，自动刷新日志文件（落盘）；

#指定数据库中的部分数据表进行备份
[root@10 ~]# mysqldump -uroot -p1 school sc teacher > ./bb.sql
[root@10 ~]# ll
总用量 2160
-rw-r--r-- 1 root root    2864 8月  13 16:24 bb.sql
-rw-r--r-- 1 root root  247769 8月  13 16:22 kb.sql
-rw-r--r-- 1 root root  762495 8月  13 16:22 qb.sql

（四）删库恢复数据

（五）使用备份恢复数据

MariaDB [school]> source ~/bb.sqlMariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
| teacher          |
+------------------+
4 rows in set (0.00 sec)

（六）登录前恢复导入

[root@10 ~]# mysql -uroot -p1 < ~/qb.sql
[root@10 ~]# mysql -uroot -p1 -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| harbor             |
| performance_schema |
| school             |
| world              |
+--------------------+

（七）删库中的表

六、导入数据数据

（一）将world.sql文件导入到数据库中

#第一种方式：
[root@10 ~]# mysql -uroot -p1 < ./world.sql
[root@10 ~]# mysql -uroot -p1 -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| harbor             |
| performance_schema |
| world              |
+--------------------+

（二）在数据库里面导入

MariaDB [(none)]> source ./world.sql;

七、拓展~数据库联表查询

（一）内连拼接查询

#语法：
#第一种：【select * from 表1,表2 where 表1.字段=表2.字段】
MariaDB [school]> select * from  teacher,course where teacher.tno=course.tno;
+-----+-----------+------+--------+-----+
| tno | tname     | cno  | cname  | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师    | 1001 | linux  | 101 |
| 102 | 李老师    | 1002 | python | 102 |
| 103 | 王老师    | 1003 | golang | 103 |
| 104 | 赵老师    | 1004 | java   | 104 |
+-----+-----------+------+--------+-----+#第二种：【select * from 表1 join 表2 on 表1.字段=表2.字段】
MariaDB [school]> select * from  teacher join course on  teacher.tno=course.tno;
+-----+-----------+------+--------+-----+
| tno | tname     | cno  | cname  | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师    | 1001 | linux  | 101 |
| 102 | 李老师    | 1002 | python | 102 |
| 103 | 王老师    | 1003 | golang | 103 |
| 104 | 赵老师    | 1004 | java   | 104 |
+-----+-----------+------+--------+-----+

（二）左右外连接查询

#左外连接：以左边的表为基准表，做拼接
MariaDB [school]> select * from teacher left join course on teacher.tno=course.tno;
+-----+-----------+------+--------+------+
| tno | tname     | cno  | cname  | tno  |
+-----+-----------+------+--------+------+
| 101 | 张老师    | 1001 | linux  |  101  |
| 102 | 李老师    | 1002 | python |  102  |
| 103 | 王老师    | 1003 | golang |  103  |
| 104 | 赵老师    | 1004 | java   |  104  |
+-----+-----------+------+--------+------+#右外连接：以右边的表为基准表，做拼接；
MariaDB [school]> select * from teacher right join course on teacher.tno=course.tno;
+------+-----------+------+--------+-----+
| tno  | tname     | cno  | cname  | tno |
+------+-----------+------+--------+-----+
|  101 | 张老师     | 1001 | linux  | 101 |
|  102 | 李老师     | 1002 | python | 102 |
|  103 | 王老师     | 1003 | golang | 103 |
|  104 | 赵老师     | 1004 | java   | 104 |
| NULL | NULL      | 1005 | c++    | 105 |
+------+-----------+------+--------+-----+

（三）联合查询union

MariaDB [school]> select * from sc where sno=1 union select * from sc where sno=9;
+-----+------+-------+
| sno | cno  | score |
+-----+------+-------+
|   1 | 1001 |    80 |
|   1 | 1003 |    56 |
|   9 | 1003 |    76 |
+-----+------+-------+