[大数据][数据库] MySQL基本操作（增、删、查、改）以及数据库的备份还原操作

背景：本科的时候学过数据库这门课，奈何学了之后没用过，两三年过去了，只知道数据库的基本概念，具体的指令操作早已忘光，所以整理了一些基础知识，回顾一下，也希望能帮到有需要的人

SQL基本语法

一、基础

SQL语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的DBMS以及配置

SQL支持三种注释：

# 注释
SELECT *
FROM mytable; -- 注释
/*注释1
  注释2*/

数据库的创建与使用：

CREATE DATABASE test;
USE test;

二、创建表

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL
  # 设置主键为 id
  PRIMARY KEY ('id'));

三、修改表

1、添加列

ALTER TABLE mytable
ADD col CHAR(20);

2、删除列

ALTER TABLE mytable
DROP COLUMN col;

3、删除表

DROP TABLE mytable;

四、插入

1、普通插入

INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

2、插入检索出来的数据

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2

3、将一个表的内容插入到一个新表

CREATE TABLE newtable AS
SELECT * FROM mytable;

五、更新

UPDATE mytable
SET col = val
WHERE id = 1;

六、删除

DELETE FROM mytable
WHERE id = 1;

清空表，也就是删除所有行

TRUNCATE TABLE mytable;

使用更新和删除操作时一定要用WHERE子句，不然会把整张表的数据都破坏。可以先用SELECT语句进行测试，防止错误删除

七、查询

DISTINCT，相同值只会出现一次，它作用于所有列，也就是所有列的值都相同才算相同

SELECT DISTINCT col1, col2
FROM mytable;

LIMIT，限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数

返回前5行

SELECT *
FROM mytable
LIMIT 5;

SELECT *
FROM mytable
LIMIT 0, 5;

返回3-5行

SELECT *
FROM mytable
LIMIT 2, 3;

八、备份数据库（比如test）

1、常用sql语句

mysql -u username -p password;-- 输入用户名和密码登录mysql
show databases;-- 查看mysql服务器中的数据库情况（会罗列出mysql中所有数据库信息）
use databases;-- 选择使用默认数据库
show table;--查看默认数据库中的表情况

2、备份整个数据库

直接进入后台，MySQL默认目录/var/lib/mysql

输入命令

mysqldump -u username -p test > /home/bak/2021-09-01/test.sql 

• 参数解析

  username：用户名

  test：要备份的数据库的名字，上面表示备份整个test数据库

  test.sql：表示将数据库备份到指定的这个文件中，文件名前面可添加绝对路径

此时，已经成功将数据库test备份到/home/bak/2021-09-01目录下的test.sql中

3、备份数据库中的指定表

-- table1 table2参数表示需要备份的数据库表的名称，为空则表示需要备份整个数据库
mysqldump -u username -p test table1 table2 > /home/bak/2021-09-01/test.sql 

4、备份多个数据库

mysqldump -u username -p --databases test1 test2 > /home/bak/2021-09-01/test.sql

–databases用于指定多个数据库

5、备份所有数据库

mysqldump -u username -p --all-databases > /home/bak/2021-09-01/test.sql

–all-databases用于指定备份所有数据库

6、直接赋值整个数据库项目

MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法

在此之前要停止服务，确保在复制期间数据库的数据不会发生变化

九、还原数据库（比如test）

首先将原表old_test删除

SELECT * FROM old_test;--查询old_test数据库，会显示数据库内容
DELETE FROM old_test;--删除原来的数据库，如果不存在老的数据库，则不需要删除操作
SELECT * FROM old_test;--提示数据库为空

然后还原新数据库

• 方法1

  mysql -u username -p test < /home/bak/2021-09-01/test.sql
  

  然后输入密码

  还原到备份数据

• 方法2

  先登录mysql数据库，输入命令

  mysql -u username -p 密码
  

  再使用数据库test

  use test;
  

  导入备份文件

  source /home/bak/2021-09-01/test.sql;
  

  还原到备份数据

• 方法3

  直接复制