常用SQL语句

最新推荐文章于 2025-08-17 21:45:30 发布

原创最新推荐文章于 2025-08-17 21:45:30 发布 · 323 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

运维专栏收录该内容

81 篇文章

订阅专栏

一、数据库表备份

1、备份表结构

（1）创建一个表


MariaDB [test]> create table test_a(id int(10) not null,username varchar(15) not null,password varchar(20),age int(10) not null not null); 
MariaDB [test]> insert into test_a values('1','dream1','123','20');
MariaDB [test]> insert into test_a values('2','dream2','123','21');
MariaDB [test]> insert into test_a values('3','dream3','123','20'); 
MariaDB [test]> insert into test_a values('4','dream4','456','23');

（2）操作命令

###方法一
MariaDB [test]> create table test_a_bak select * from test_a where 1=2;

###方法二
MariaDB [test]> create table test_a_bak1 like test_a;

###查看结果
MariaDB [test]> select * from test_a_bak;
Empty set (0.00 sec)

MariaDB [test]> desc test_a_bak;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   |     | NULL    |       |
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| age      | int(10)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2、全备份（数据和表结构）

###方法一
MariaDB [test]> create table test_a_bak2 select * from test_a;          

###方法二
MariaDB [test]> create table test_a_bak3 as select * from test_a; 

###查看结果
MariaDB [test]> desc test_a_bak2;                             
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   |     | NULL    |       |
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| age      | int(10)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [test]> select * from test_a_bak2;
+----+----------+----------+-----+
| id | username | password | age |
+----+----------+----------+-----+
|  1 | dream1   | 123      |  20 |
|  2 | dream2   | 123      |  21 |
|  3 | dream3   | 123      |  20 |
|  4 | dream4   | 456      |  23 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

二、数据库表批量更新

通过test_b的内容来更新test_a的内容！！！

1、建立一个表test_b

MariaDB [test]> create table test_b(id int(10) not null,username varchar(15) not null,password varchar(20),age int(10) not null not null);  
MariaDB [test]> insert into test_b values('1','dreamya','123','20');
MariaDB [test]> insert into test_b values('2','dreamya2','123','21');

###查看表
MariaDB [test]> select * from test_b;                                
+----+----------+----------+-----+
| id | username | password | age |
+----+----------+----------+-----+
|  1 | dreamya  | 123      |  20 |
|  2 | dreamya2 | 123      |  21 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

MariaDB [test]> select * from test_a;                                   
+----+----------+----------+-----+
| id | username | password | age |
+----+----------+----------+-----+
|  1 | dream1   | 123      |  20 |
|  2 | dream2   | 123      |  21 |
|  3 | dream3   | 123      |  20 |
|  4 | dream4   | 456      |  23 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

2、update更新

把通过test_b把test_a中的password和age相同的地方进行更新！！！

MariaDB [test]> update test_a a set username=(select username from test_b b where a.password=b.password and a.age=b.age) where exists (select 1 from test_b b where a.password=b.password and a.age=b.age) ;

###查看结果
MariaDB [test]> select * from test_a;
+----+----------+----------+-----+
| id | username | password | age |
+----+----------+----------+-----+
|  1 | dreamya  | 123      |  20 |
|  2 | dreamya2 | 123      |  21 |
|  3 | dreamya  | 123      |  20 |
|  4 | dream4   | 456      |  23 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)