Linux運(yùn)維-day49-MySQL數(shù)據(jù)庫的基本操作整理

參考：https://blog.csdn.net/len_yue_mo_fu/article/details/79153870

進(jìn)入數(shù)據(jù)庫：命令行直接寫mysql痰滋，回車即可
進(jìn)入指定數(shù)據(jù)庫：mysql -uwordpress -p wordpress
退出數(shù)據(jù)庫：Ctrl+d

一、創(chuàng)建操作

1.1 創(chuàng)建數(shù)據(jù)庫

create database test;

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lianxi             |
| mysql              |
| performance_schema |
| test               |
| wordpress          |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]>

1.2 創(chuàng)建用戶

grant all on wordpress.* to 'wordpress'@'172.16.1.%' identified by '123456';
grant all on wordpress.* to 'wordpress'@'localhost' identified by '123456';

1.3 創(chuàng)建表和字段

create table tl_user(id int primary key auto_increment,name nvarchar(20),age int,sex varchar(4),address nvarchar(50),updatetime date);

MariaDB [test]> create table tl_user(id int primary key auto_increment,name nvarchar(20),age int,sex varchar(4),address nvarchar(50),updatetime date);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| tl_user        |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]>

1.4 向表中插入數(shù)據(jù)

insert into 數(shù)據(jù)庫名.表名(表字段,……) values(對應(yīng)字段的值,……)

如：insert into test.tl_user(name,age,sex,address,updatetime) values('張三',23,'男','北京朝陽區(qū)某某號','2019-6-11');

MariaDB [test]> insert into tl_user(name,age,sex,address,updatetime) values('張三',23,'男','北京朝陽區(qū)某某號','2019-6-11');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> select * from tl_user\G
*************************** 1. row ***************************
        id: 1
      name: 張三
       age: 23
       sex: ?
   address: 北京朝陽區(qū)某某號
updatetime: 2019-06-11
1 row in set (0.00 sec)

MariaDB [test]>

二、刪除操作

2.1 刪除數(shù)據(jù)庫

drop database 數(shù)據(jù)庫名;
如：drop database test;

MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lianxi             |
| mysql              |
| performance_schema |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>

2.2 刪除用戶

drop user 'oldboy'@'localhost'
用戶刪除之后：flush privileges

2.3 刪除表

drop table 數(shù)據(jù)庫名.表名;

2.4 刪除表中的某條數(shù)據(jù)

delete from 數(shù)據(jù)庫名. 表名 where 要?jiǎng)h除的條件

如：delete from test.tl_user where id=2;

MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name   | age  | sex  | address                  | updatetime |
+----+--------+------+------+--------------------------+------------+
|  1 | 張三   |   23 | ?    | 北京朝陽區(qū)某某號         | 2019-06-11 |
|  2 | dddd   |   23 | nv   | dfdfdfdfdfd              | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的輔導(dǎo)輔導(dǎo)輔導(dǎo)費(fèi)         | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
3 rows in set (0.00 sec)

MariaDB [test]> delete from tl_user where id=2;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name   | age  | sex  | address                  | updatetime |
+----+--------+------+------+--------------------------+------------+
|  1 | 張三   |   23 | ?    | 北京朝陽區(qū)某某號         | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的輔導(dǎo)輔導(dǎo)輔導(dǎo)費(fèi)         | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]>

三、查詢操作

3.1 查詢數(shù)據(jù)庫

show databases;

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

3.2 查詢數(shù)據(jù)庫中的用戶（指定字段）

select user,host from mysql.user;

MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | web01     |
| root | web01     |
+------+-----------+
6 rows in set (0.00 sec)

3.3 查詢數(shù)據(jù)庫中的用戶（所有字段）

select * from mysql.user;

為了顯示好看可以縱向顯示，直接在語句后面加\G，如：select * from mysql.user\G

3.4 顯示當(dāng)前所用的用戶

select user();

3.5 使用數(shù)據(jù)庫（進(jìn)入指定數(shù)據(jù)庫）

use 數(shù)據(jù)庫名；如：use mysql;

MariaDB [(none)]> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [wordpress]>

3.6 顯示當(dāng)前使用的數(shù)據(jù)庫

select database();

MariaDB [wordpress]> select database();
+------------+
| database() |
+------------+
| wordpress  |
+------------+
1 row in set (0.00 sec)

MariaDB [wordpress]>

3.7 只顯示1條數(shù)據(jù)

select * from mysql.user limit 1 ;
select * from mysql.user limit 1\G

MariaDB [wordpress]> select * from wp_posts limit 1 \G
*************************** 1. row ***************************
                   ID: 1
          post_author: 1
            post_date: 2019-06-10 09:30:38
        post_date_gmt: 2019-06-10 09:30:38
         post_content: <!-- wp:paragraph -->
<p>Welcome to WordPress. This is your first post. Edit or delete it, then start writing!</p>
<!-- /wp:paragraph -->
       ……
MariaDB [wordpress]>

四节值、更新操作

4.1 根據(jù)條件更新表中的某些字段的值

update 數(shù)據(jù)庫名.表名 set 要更新的字段=值 where 根據(jù)什么條件;

如：更新tl_user表中張三的地址
update test.tl_user set address='黑龍江省齊齊哈爾市某某號' where id=1;

MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name   | age  | sex  | address                  | updatetime |
+----+--------+------+------+--------------------------+------------+
|  1 | 張三   |   23 | ?    | 北京朝陽區(qū)某某號         | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的輔導(dǎo)輔導(dǎo)輔導(dǎo)費(fèi)         | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> update tl_user set address='黑龍江省齊齊哈爾市某某號' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------------------+------------+
| id | name   | age  | sex  | address                              | updatetime |
+----+--------+------+------+--------------------------------------+------------+
|  1 | 張三   |   23 | ?    | 黑龍江省齊齊哈爾市某某號             | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的輔導(dǎo)輔導(dǎo)輔導(dǎo)費(fèi)                     | 2019-06-11 |
+----+--------+------+------+--------------------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]>

五、備份操作

1>導(dǎo)出所有的數(shù)據(jù)庫

mysqldump -uroot -p -all-database >/root/all.sql
mysqldump -uroot -p -A >/root/all.sql

五榜聂、恢復(fù)數(shù)據(jù)庫

恢復(fù)所有數(shù)據(jù)庫：mysql -uroot -p </root/all.sql
給指定的數(shù)據(jù)庫恢復(fù)表：如：mysql -uzabbix -p123456 zabbixdb（數(shù)據(jù)庫名） <schema.sql