Linux中的数据库操作基础
一.什么是数据库
关系型数据库
高级的excel
非关系型数据库
键值对
二.在Linux中安装数据库并完成安全初始化
1.安装mysql数据库
#查找数据库软件包
[root@mariadb ~]# dnf search mysql
#安装
[root@mariadb ~]# dnf install mysql-server.x86_64 -y
#启动数据库
[root@mariadb ~]# systemctl enable --now mysqld.service
#访问数据库
[root@mariadb ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2024, 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.00 sec)
mysql> QUIT2.安全初始化
默认安装好的数据库可以不需要密码直接登录，为了让数据库更安全，需要对当前mysql进行安全初始化
[root@mariadb ~]# 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@mariadb ~]# mysql #没有密码登陆舰失败
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:
NO)
[root@mariadb ~]# mysql -u root -p12345678 #使用密码登录成功
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2024, 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 |
+--------------------+
mysql> CREATE DATABASE timinglee; #建立库
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| timinglee |
+--------------------+
5 rows in set (0.01 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 );
#插入信息
mysql> INSERT INTO userlist VALUES ('user1','123');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO userlist VALUES ('user3','333'),('user2','222');
#查看数据
mysql> SELECT * FROM userlist; #查询所有数据
+----------+--------+
| username | passwd |
+----------+--------+
| user1 | 123 |
| user3 | 333 |
| user2 | 222 |
+----------+--------+
3 rows in set (0.00 sec)
mysql> SELECT username FROM userlist WHERE passwd='333'; #查询username字段中
passwd字段等于333的信息
#修改表名称
mysql> ALTER TABLE userlist RENAME user_list;
#删除列
mysql> ALTER TABLE user_list DROP age;
#添加列到指定位置
mysql> ALTER TABLE user_list ADD age varchar(4) AFTER username;
#更新整列数据
mysql> UPDATE user_list SET age='20';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM user_list;
+----------+------+--------+
| username | age | passwd |
+----------+------+--------+
| user1 | 20 | 123 |
| user3 | 20 | 333 |
| user2 | 20 | 222 |
+----------+------+--------+
3 rows in set (0.00 sec)
#更新指定数据
mysql> UPDATE user_list SET age='15' WHERE username='user3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM user_list;
+----------+------+--------+
| username | age | passwd |
+----------+------+--------+
| user1 | 20 | 123 |
| user3 | 15 | 333 |
| user2 | 20 | 222 |
+----------+------+--------+
3 rows in set (0.00 sec)
#删除某行数据
mysql> DELETE FROM user_list WHERE username='user3';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user_list;
+----------+------+--------+
| username | age | passwd |
+----------+------+--------+
| user1 | 20 | 123 |
| user2 | 20 | 222 |
+----------+------+--------+
2 rows in set (0.00 sec)
#删除表
mysql> DROP TABLE user_list;
#删除库
mysql> DROP DATABASE timinglee;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#备份数据库中所有数据到all.sql文件中
[root@mariadb mysql]# mysqldump -uroot -p12345678 -A > /mnt/all.sql
#备份数据库结构但不备份数据
[root@mariadb mysql]# mysqldump -uroot -p12345678 -A --no-data >/mnt/allnodata.sql
#备份timinglee库
[root@mariadbmysql]# mysqldump -uroot -p12345678 timinglee > /mnt/timinglee.sql
#恢复数据到指定库中
[root@mariadb mnt]# mysql -uroot -p12345678 -e "drop database timinglee;"
[root@mariadb mnt]# mysql -uroot -p timinglee < /mnt/timinglee.sql