Linux中的数据库操作基础
一.什么是数据库
关系型数据库 ---------高级的excel
非关系型数据库 ------键值对
二.在Linux中安装数据库并完成安全初始化
1 安装mysql数据库
#查找数据库软件包
[root@lhel9 ~]# dnf search mysql #安装mysql
[root@lhel9 ~]# dnf install mysql-server-8.0.30-3.el9_0.x86_64 -y#启动数据库
[root@lhel9 ~]# systemctl enable --now mysqld.service
#访问数据库
[root@lhel9 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 Source distributionCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)mysql>
2 安全初始化
默认安装好的数据库可以不需要密码直接登录，为了让数据库更安全，需要对当前mysql进行安全初始化
[root@lhel9 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y #是否要更改密码
There are three levels of password validation policy:
LOW Length >= 8 #低等级安全密码，大于8位字符即可，设定代码为0
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0 #密码安全级别
Please set the password for root here.
New password: #输入密码1次
Re-enter new password: #再次输入密码1次
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other
key for No) : yes #再次确认是否要更改密码
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : yes #是否要禁止匿名用户
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : yes #是否要禁止root用户远程登录
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
#是否要删除测试表
Remove test database and access to it? (Press y|Y for Yes, any other key for No)
: yes - Dropping test database...
Success.- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
#是否要刷新数据库
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : yes
Success.
All done!
#测试安装成功与否
[root@lhel9 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)#使用密码登录成功[root@lhel9 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.30 Source distributionCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
三 sql语句的基本知识
显示当前的库名称
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
建立库
mysql> create database timinglee;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| timinglee |
+--------------------+
5 rows in set (0.00 sec)
使用库
mysql> use timinglee;
Database changed
显示库中的表
mysql> show tables;
Empty set (0.00 sec)建立userlist表
mysql> create table userlist-> (username varchar(10) not null,-> passwd varchar(50) not null);
Query OK, 0 rows affected (0.02 sec)插入信息
mysql> insert into userlist values ("hui","123");
Query OK, 1 row affected (0.06 sec)查看数据
mysql> select * from userlist;
+----------+--------+
| username | passwd |
+----------+--------+
| hui | 123 |
+----------+--------+
1 row in set (0.00 sec)查询username字段中 passwd字段等于123的信息
mysql> select username from userlist where passwd="123";
+----------+
| username |
+----------+
| hui |
+----------+
1 row in set (0.00 sec)
修改表名称
mysql> alter table userlist rename user_list;
Query OK, 0 rows affected (0.01 sec)
增加字段age
mysql> alter table user_list add age varchar(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc user_list;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| passwd | varchar(50) | NO | | NULL | |
| age | varchar(4) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
更新整列数据
mysql> update user_list set age="18";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user_list;
+----------+--------+------+
| username | passwd | age |
+----------+--------+------+
| hui | 123 | 18 |
+----------+--------+------+
1 row in set (0.00 sec)
删除列
mysql> ALTER TABLE user_list DROP age;
删除表
mysql> DROP TABLE user_list;删除库
mysql> DROP DATABASE timinglee;四 数据库备份
备份数据库中所有数据到all.sql文件中
[root@lhel9 ~]# mysqldump -uroot -p12345678 -A > /mnt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份数据库结构但不备份数据
[root@lhel9 ~]# mysqldump -uroot -p12345678 -A --no-data > /mnt/all-nodata.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.备份timinglee库
[root@lhel9 ~]# mysqldump -uroot -p12345678 timinglee > /mnt/timinglee.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.在数据库中删除timinglee数据库
mysql> drop database timinglee;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)mysql>
恢复数据到指定库中
[root@lhel9 ~]# mysql -uroot -p12345678 -e "create database timinglee;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lhel9 ~]# mysql -uroot -p timinglee < /mnt/timinglee.sql
Enter password:
[root@lhel9 ~]#
在数据库查看timinglee数据库的数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| timinglee |
+--------------------+
5 rows in set (0.00 sec)mysql> use timinglee;mysql> show tables;
+---------------------+
| Tables_in_timinglee |
+---------------------+
| user_list |
+---------------------+
1 row in set (0.00 sec)mysql> select * from user_list;
+----------+--------+------+
| username | passwd | age |
+----------+--------+------+
| hui | 123 | 18 |
+----------+--------+------+
1 row in set (0.00 sec)