Linux运维-day49-MySQL数据库的基本操作整理

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

进入数据库：命令行直接写mysql，回车即可
进入指定数据库：mysql -uwordpress -p wordpress
退出数据库：Ctrl+d

一、创建操作

1.1 创建数据库

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 创建用户

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

1.3 创建表和字段

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 向表中插入数据

insert into 数据库名.表名(表字段,……) values(对应字段的值,……)

如：insert into test.tl_user(name,age,sex,address,updatetime) values('张三',23,'男','北京朝阳区某某号','2019-6-11');

MariaDB [test]> insert into tl_user(name,age,sex,address,updatetime) values('张三',23,'男','北京朝阳区某某号','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: 北京朝阳区某某号
updatetime: 2019-06-11
1 row in set (0.00 sec)

MariaDB [test]>

二、删除操作

2.1 删除数据库

drop database 数据库名;
如：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 数据库名.表名;

2.4 删除表中的某条数据

delete from 数据库名. 表名 where 要删除的条件

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

MariaDB [test]> select * from tl_user;
+----+--------+------+------+--------------------------+------------+
| id | name   | age  | sex  | address                  | updatetime |
+----+--------+------+------+--------------------------+------------+
|  1 | 张三   |   23 | ?    | 北京朝阳区某某号         | 2019-06-11 |
|  2 | dddd   |   23 | nv   | dfdfdfdfdfd              | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的辅导辅导辅导费         | 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 | ?    | 北京朝阳区某某号         | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的辅导辅导辅导费         | 2019-06-11 |
+----+--------+------+------+--------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]>

三、查询操作

3.1 查询数据库

show databases;

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

3.2 查询数据库中的用户（指定字段）

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 查询数据库中的用户（所有字段）

select * from mysql.user;

为了显示好看可以纵向显示，直接在语句后面加\G，如：select * from mysql.user\G

3.4 显示当前所用的用户

select user();

3.5 使用数据库（进入指定数据库）

use 数据库名；如：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 显示当前使用的数据库

select database();

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

MariaDB [wordpress]>

3.7 只显示1条数据

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 根据条件更新表中的某些字段的值

update 数据库名.表名 set 要更新的字段=值 where 根据什么条件;

如：更新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 | ?    | 北京朝阳区某某号         | 2019-06-11 |
|  3 | 李四   |   23 | nv   | 的辅导辅导辅导费         | 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   | 的辅导辅导辅导费                     | 2019-06-11 |
+----+--------+------+------+--------------------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]>

五、备份操作

1>导出所有的数据库

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

五、恢复数据库

恢复所有数据库：mysql -uroot -p </root/all.sql
给指定的数据库恢复表：如：mysql -uzabbix -p123456 zabbixdb（数据库名） <schema.sql