mysql备份与还原

By | 2021年1月19日

MySQL 备份和恢复机制

原文链接: segmentfault.com

一、 备份恢复策略

进行备份或恢复操作时需要考虑一些因素:

  • 确定要备份的表的存储引擎是事务型还是非事务型,两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。
  • 确定使用全备份还是增量备份。全备份的优点是备份保持最新备份,恢复的时候可以花费更少的时间;缺点是如果数据量大,将会花费很多的时间,并对系统造成较长时间的压力。增量备份相反,只需要备份每天的增量日志,备份时间少,对负载压力也小;缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志,恢复时间长一些。
  • 可以考虑采用复制的方法来做异地备份,但不能代替备份,它对数据库的误操作也无能为力。
  • 要定期做备份,备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行
  • 确保 MySQL 打开 log-bin 选项,有了 binlog,MySQL 才可以在必要的时候做完整恢复,或基于时间点的恢复,或基于位置的恢复。
  • 经常做备份恢复测试,确保备份时有效的,是可以恢复的。

二、 逻辑备份和恢复

在 MySQL 中,逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份;而物理备份则不同,不同的存储引擎有着不同的备份方法,因此,对于不同存储引擎混合的数据库,逻辑备份会简单一点。

1. 备份

MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件,备份的文件可以被查看和编辑。在 MySQL 中,可以使用 mysqldump 工具来完成逻辑备份:

// 备份指定的数据库或者数据库中的某些表  
shell> mysqldump [options] db_name [tables]  

// 备份指定的一个或多个数据库  
shell> mysqldump [options] --database DB1 [DB2,DB3...]  

// 备份所有数据库  
shell> mysqldump [options] --all-database复制代码

如果没有指定数据库中的任何表,默认导出所有数据库中的所有表。

示例:
1. 备份所有数据库:
shell>mysqldump -uroot -p --all-database > all.sql复制代码
2. 备份数据库 test
shell>mysqldump -uroot -p test > test.sql复制代码
3. 备份数据库 test 下的表 emp
shell> mysqldump -uroot -p test emp > emp.sql复制代码
4. 备份数据库 test 下的表 emp 和 dept
shell> mysqldump -uroot -p test emp dept > emp_dept.sql 复制代码
5. 备份数据库test 下的所有表为逗号分割的文本,备份到 /tmp:
shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','
shell> more emp.txt  

1,z1
2,z2
3,z3
4,z4复制代码

注意: 为了保证数据备份的一致性,myisam 存储引擎在备份时需要加上 -l 参数,表示将所有表加上读锁,在备份期间,所有表将只能读而不能进行数据更新。但是对于事务存储引擎来说,可以采用更好的选项 –single-transaction,此选项使得 innodb 存储引擎得到一个快照(snapshot),使得备份的数据能够保证一致性。

2. 完全恢复

mysqldump 的恢复也很简单,将备份作为输入执行即可:

mysql -uroot -p db_name < backfile复制代码

注意,将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做:

mysqlbinlog binlog-file | mysql -uroot -p复制代码
完整的 mysqldump 备份与恢复示例:
1. 凌晨 2:00,备份数据库:
root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp
Enter password: 复制代码

其中 -l 参数表示给所有表加读锁,-F 表示生成一个新的日志文件,此时,t2 中 emp 表的数据如下:

# 为了便于测试,执行 reset master 删除所有 binlog。
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)

# 此时只有一个 binlog 日志文件   mysql-bin.000001
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)复制代码
2. 备份完毕后,插入新的数据:
# 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件,即名为 mysql-bin.000002 的文件
MySQL [t2]> insert into test values (3,'c');
Query OK, 1 row affected (0.00 sec)

MySQL [t2]> insert into test values (4,'d');
Query OK, 1 row affected (0.00 sec)复制代码
3. 数据库突然故障(其实是小伙伴没事儿删库练手玩儿),数据无法访问。需要恢复备份:

删库跑路:

# 这里为了便于测试,不把删库操作记入日志,当前 session 设置 sql_log_bin 为 off。
# 删库后,执行 flush logs,让后续的 binlog 到新的文件中,即名为 mysql-bin.000003中
MySQL [t2]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MySQL [t2]> show variables like "%sql_log_bin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [t2]> drop database t2;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.22 sec)


MySQL [t2]> drop database t2;
Query OK, 3 rows affected (0.23 sec)

MySQL [(none)]> exit;
Bye复制代码

数据恢复:

root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2"   
root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp 

*******************************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)
复制代码
4. 使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binglog

根据前面操作的内容,可知从备份的时间点到删库的时间点之间的操作被记录到了 mysql-bin.000002 文件中

root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2

*******************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)
复制代码

至此,数据恢复成功。

3. 基于时间点恢复

由于误操作,比如误删除了一张表,这时使用完全恢复时没有用的,因为日志里面还存在误操作的语句,我们需要的是恢复到误操作之前的状态,然后跳过误操作语句,再恢复后面执行的语句,完成恢复。这种恢复叫不完全恢复,在 MySQL 中,不完全恢复分为 基于时间点的恢复和基于位置的恢复。 基于时间点恢复的操作步骤:

(1) 如果是上午 10 点发生了误操作,可以用以下语句用备份和 binlog 将数据恢复到故障前:
shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword复制代码
(2) 跳过故障时的时间点,继续执行后面的 binlog,完成恢复。
shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword复制代码

4. 基于位置恢复

和基于时间点的恢复类似,但是更精确,因为同一个时间点可能有很多条 sql 语句同时执行。恢复的操作步骤如下:

(1) 在 shell 下执行命令:
shell>mysqlbinlog --start-date="2017-09-30 9:59:59" --stop-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql复制代码

该命令将在 /tmp 目录创建小的文本文件,编辑此文件,知道出错语句前后的位置号,例如前后位置号分别为 368312 和 368315。

(2) 恢复了以前的备份文件后,应从命令行输入下面的内容:
shell>mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword  
shell>mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword 复制代码

上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 sql 语句记录之前的 set timestamp 语句,因此恢复的数据和相关的 mysql 日志将反应事务执行的原时间。

三、物理备份和恢复

物理备份又分为冷备份和热备份两种,和逻辑备份相比,它的最大优点是备份和恢复的速度更快,因为物理备份的原理都是基于文件的 cp。

1. 冷备份

冷备份其实就是停掉数据库服务,cp 数据文件的方法。(基本不考虑这种方法)

2. 热备份

在 MySQL 中,对于不同的存储引擎热备份的方法也有所不同。

(1) myisam 存储引擎

myisam 存储引擎的热备份有很多方法,本质其实就是将要备份的表加读锁,然后再 cp 数据文件到备份目录。常用的有以下两种方法:

  • 使用 mysqlhotcopy 工具
// mysqlhotcopy 是 MySQL 的一个自带的热备份工具  
shell> mysqlhotcopy db_name [/path/to/new_directory]复制代码
  • 手工锁表 copy
// 在 mysqlhotcopy 使用不正常的情况下,可以用手工来做热备份

mysql>flush tables for read;

cp 数据文件到备份目录即可,复制代码
(2) innodb 存储引擎(另写)

使用第三方工具 ibbackup、xtrabackup、innobacupex

四、 表的导入导出

在数据库的日常维护中,表的导入导出时很频繁的一类操作。

1. 导出

在某些情况下,为了一些特定的目的,经常需要将表里的数据导出为某些符号分割的纯数据文本,而不是 sql 语句:

  • 用来作为 Excel 显示;
  • 单纯为了节省备份空间;
  • 为了快速的加载数据,load data 的加载速度比普通 sql 加载要快 20 倍以上。
使用 select …into outfile … 命令来导出数据,具体语法如下:
mysql> select * from tablename into outfile 'target_file' [option];复制代码

其中 option 参数可以是以下选项:

fields terminated by 'string'                   // 字段分隔符,默认为制表符'\t'
fields [optionally] enclosed by 'char'          // 字段引用符,如果加 optionally 选项则只用在 char、varchar 和 text 等字符型字段上,默认不使用引用符  
fields escaped by ‘char’                        // 转移字符、默认为 '\'  
lines starting by 'string'                      // 每行前都加此字符串,默认''  
lines terminated by 'string'                    // 行结束符,默认为'\n'  

# char 表示此符号只能是单个字符,string表示可以是字符串。复制代码

例如,将 test 表中数据导出为数据文本,其中,字段分隔符为“,”,字段引用符为“””,记录结束符为回车符:

MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';
Query OK, 4 rows affected (0.02 sec)
复制代码
zj@bogon:/data/mysql$ more outfile.txt 
"1","a","helloworld"
"2","b","helloworld"
"3","c","helloworld"
"4","d","helloworld"复制代码

发现第一列是数值型,如果不希望字段两边用引号引起,则语句改为:

MySQL [t2]> select * from test into outfile '/data/mysql/outfile2.txt' fields terminated by "," optionally  enclosed by '"';
Query OK, 4 rows affected (0.03 sec)

zj@bogon:/data/mysql$ more outfile2.txt 
1,"a","helloworld"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"复制代码

测试转义字符,MySQL 导出数据中需要转义的字符主要包括以下 3 类:

  • 转义字符本身
  • 字段分隔符
  • 记录分隔符
MySQL [t2]> update test set content = '\\"##!aa' where  id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [t2]> select * from test into outfile '/data/mysql/outfile3.txt' fields terminated by "," optionally enclosed by '"';
Query OK, 4 rows affected (0.03 sec)

*******************************************
zj@bogon:/data/mysql$ more outfile3.txt 
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"复制代码
  • 当导出命令中包含字段引用符时,数据中含有转义字符本身和字段引用符的字符需要被转义;
  • 当导出命令中不包含字段引用符时,数据中含有转义字符本身和字段分割符的字符需要被转义。

注意: select … into outfile … 产生的输出文件如果在目标目录下有重名文件,将不会被创建成功,源文件不会被自动覆盖。

使用 mysqldump 导出数据为文本的具体语法如下:

mysqldump -u username -T target_dir dbname tablename [option]

其中,option 参数可以是以下选项:

  • –fields-terminated-by=name (字段分隔符);
  • –fields-enclosed-by=name (字段引用符);
  • –fields-optionally-enclosed-by=name (字段引用符,只用在 char、varchar 和 test 等字符型字段上);
  • –fields-escaped-by=name (转义字符);
  • –lines-terminated-by=name (记录结束符);

例子:

root@bogon:/usr/local/mysql/bin# ./mysqldump -uroot -p -T /data/mysql/dump t2 test --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

**************** test.txt **********************
zj@bogon:/data/mysql/dump$ more test.txt 
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"

***************** test.sql *********************
zj@bogon:/data/mysql/dump$ more test.sql 
-- MySQL dump 10.13  Distrib 5.7.18, for Linux (x86_64)
--
-- Host: localhost    Database: t2
-- ------------------------------------------------------
-- Server version    5.7.18-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `content` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-09-25 11:14:06复制代码

可以发现,除多了一个表的创建脚本文件,mysqldump 和 select … into outfile … 的选项和语法非常相似。其实 mysqldump 实际调用的就是后者提供的接口,并在其上面添加了一些新的功能而已。

2. 导入 (导入用 select … into outfile 或者 mysqldump 导出的纯数据文本)

和导出类似,导入也有两种不同的方法,分别是 load data infile… 和 mysqlimport,它们的本质是一样的,区别只是在于一个在 MySQL 内部执行,另一个在 MySQL 外部执行。

使用 “load data infile…” 命令,具体语法如下
mysql> load data [local]infile 'filename' into table tablename [option]  
复制代码

option 可以是以下选项:

  • fields terminated by ‘string’ (字段分割符,默认为制表符’t’);
  • fields [optionally] enclosed by ‘char’ (字段引用符,如果加 optionally 选项则只用在 char varchar text 等字符型字段上。默认不使用引用符);
  • fields escaped by ‘char’ (转义字符,默认为”)
  • lines starting by ‘string’ (每行前都加此字符串,默认为”)
  • lines terminated by ‘string’ (行结束符,默认为’n’)
  • ignore number lines (忽略输入文件中的前几行数据)
  • (col_name_or_user_var,…) (按照列出的字段顺序和字段数量加载数据);
  • set col_name = expr,…将列做一定的数值转换后再加载。

fields 、lines 和前面 select…into outfile…的含义完全相同,不同的是多了几个不同的选项,下面的例子将文件’test.txt’中的数据加载到表 test 中:

// 清空表 test  
MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.07 sec)

MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"';
Query OK, 4 rows affected (0.10 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


MySQL [t2]> select * from test;
+------+------+------------+
| id   | name | content    |
+------+------+------------+
|    1 | a    | helloworld |
|    2 | b    | helloworld |
|    3 | c    | helloworld |
|    4 | d    | helloworld |
+------+------+------------+
4 rows in set (0.00 sec)复制代码

如果不希望加载文件中的前两行,可以进行如下操作:

MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.02 sec)

MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"' ignore 2 lines;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [t2]> select * from test;
+------+------+------------+
| id   | name | content    |
+------+------+------------+
|    3 | c    | helloworld |
|    4 | d    | helloworld |
+------+------+------------+
2 rows in set (0.02 sec)复制代码

使用 mysqldump 实现

语法:

shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option]  复制代码

其中,option 参数可以是以下选项:

  • –fields-terminated-by=name (字段分隔符)
  • –fields-enclosed-by=name (字段引用符)
  • –fields-optionally-enclosed-by=name (字段引用符,只用在 char、varchar、text等字符型字段上)
  • –fields-escaped-by=name (转义字符)
  • –lines-terminated-by=name (记录结束符)
  • –ignore-lines=number (忽略前几行)

注意:
如果导入和导出时跨平台操作的(windows 和 linux),那么要注意设置参数 line-terminated-by,windows 上设置为 line-terminated-by=’rn’, linux 上设置为 line-terminated-by=’n’。关注下面的标签,发现更多相似文章

One thought on “mysql备份与还原

  1. junchol Post author

    1.什么是数据库?

    一个装数据的大容器,有数据文件,日志文件,DBMS(数据库管理软件)

    2.数据库是用来做什么的?

    存储数据的

    3.有哪几个数据库?

    DB2 
    ORACLE 
    SQL Server
    mySql
    

    4.SQL语言分类

    DDL语句:数据定义语言
        create(创建),drop(移除),alter(改变)等
        对数据库,表,索引等数据对象进行定义
    DML语句:数据操作语言
        insert(添加)
        update(修改)
        delete(删除)
        select(查询)
    
    DCL语句:数据控制语言
        主要控制数据库,表,字段,用户的访问权限和安全级别的授予
    

    5.DDL语句

    注意:每一条语句后面都要跟分号(;)或\g结束,或者ctrl+c

    1.创建数据库语法:

    create databases 数据库名称
    

    代码示例:

    mysql> create database lanqiao;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases
        -> \g
    

    测试结果:

    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | lanqiao            |
    | mysql              |
    | performance_schema |
    | sakila             |
    | sys                |
    | world              |
    +--------------------+
    7 rows in set (0.00 sec)
    
    • information_schema:注意存储了数据中存储的数据库对象,比如用户表,列的信息。权限信息,字符集信息
    • mySql:存储系统的用户权限
    • world:测试案例表

    2.选择数据库语法

    use 数据库名称
    

    代码测试:

    mysql> use lanqiao;
    Database changed
    

    3.显示数据库中所有表语法

    show tables;
    

    4.删除数据库语法

    drop database 数据库名称
    

    代码测试:

    mysql> drop database lanqiao
        -> \g
    Query OK, 0 rows affected (0.03 sec)
    
    数据库删除后,下面的所有的表都被删除
    

    5.创建表语法

    create table 表名(
    列名1:列的类型
    列名2:列的类型
    列名3:列的类型
    
    )
    

    代码测试:

     mysql> create table emp(
        -> ename varchar(10),
        -> hiredate date,
        -> sal int,
        -> deptno int
        -> );
        Query OK, 0 rows affected (0.18 sec)
    
    • 一个表相当一个类,列名相当类的属性
    • 表名:等价于文件的名称可以为任意目录
    • 列名:表头
    • 列的类型:int , char,varchar ….
    • 注意:创建表的时候需要先选中数据库,就是制定将表创建到那个数据库中

    6.显示表结构(desc语法)

    代码测试:

    mysql> desc emp
           -> \g
    

    测试结果

    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | ename    | varchar(10) | YES  |     | NULL    |       |
    | hiredate | date        | YES  |     | NULL    |       |
    | sal      | int(11)     | YES  |     | NULL    |       |
    | deptno   | int(11)     | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)
    

    7.显示创建表的语法

    show create table emp;
    

    测试代码:

    mysql> show create table employ;
    

    8.删除表

    drop table 表名
    

    9.修改列的类型语法

    alter table 表名 modify 列名 列的类型;
    

    测试代码:

    mysql> alter table emp modify ename varchar(20);
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    10.添加列语法

    alter table 表名 add column 列名 类型;
    

    测试代码:

    mysql> alter table emp add age int(3);
    Query OK, 0 rows affected (0.24 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    11.删除表的列:

    alter table 表名 drop 列名;
    

    测试代码:

    mysql> alter table emp drop age;
    Query OK, 0 rows affected (0.29 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    12.修改列的名称

    语法:
    alter table 表名 change 原始名称 新的名称 列的类型;
    测试代码:

    mysql> alter table emp change ename name varchar(20);
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    注意:

    • change和modify都可以修改表的定义
    • change后面需要写2次列名,不方便
    • 但是change的优点可以修改列的名称,modify不可以

    13.表改名

    语法:

    alter table 表名 rename 新表名
    

    代码:

    mysql> alter table emp rename example;
    Query OK, 0 rows affected (0.10 sec)
    

    14.修改列的排列顺序

    DML语句

    DML是操作对数据库中表记录的操作
    主要表的记录操作有:
    插入(insert)
    查询(select)
    删除(delete)
    更改(update)
    

    1.插入(insert)一次添加多条

    语法:

    insert into tablename(列名1,列名2.....列名n)
    ,(值1,值2.......值n),
    ,(值1,值2.......值n),
    ,(值1,值2.......值n);
    

    代码:

    mysql> insert into example(name,age,date)values('csw',10,'1');
    Query OK, 1 row affected (0.04 sec)
    
    对于含可空列的,非空但是含有默认值的字段,自增字段,可以不用在insert后添加列名,values后面直接对应列就行
    

    代码:

    mysql> insert into emp(name)values('guang');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from emp;
    +-------+------+------+
    | name  | age  | date |
    +-------+------+------+
    | csw   |   10 | 1    |
    | guang | NULL | NULL |
    +-------+------+------+
    2 rows in set (0.00 sec)
    
    表结构(允许列为空):
    +------+
    | Null |
    +------+
    | YES  |
    | YES  |
    | YES  |
    +------+
    

    2.更改(update)

    语法:

    update 表名 set 列名1 = 值1,列名2 = 值2...列名n = 值n;
    对表中的数据,可以通过update命令进行修改
    

    代码:

    mysql> update csw set  name = '关头强';
    Query OK, 4 rows affected (0.04 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    //一般都会加上where语句
    

    代码:

    mysql> update csw set sal = '1000' where name = '关头强';
    Query OK, 4 rows affected (0.03 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    

    代码:

    mysql> select * from csw;
    

    结果:

    +-----------+-----------+------+-------+
    | name      | hiredate  | sal  | depno |
    +-----------+-----------+------+-------+
    | 关头强    | 2017-4-15 | 1000 | 1000  |
    | 关头强    | 2080-5-1  | 1000 | NULL  |
    | 关头强    | 2080-5-1  | 1000 | NULL  |
    | 关头强    | 2001-1-1  | 1000 | NULL  |
    +-----------+-----------+------+-------+
    4 rows in set (0.00 sec)
    

    由于没有加where条件,所以将所有的name都修改了。而下面是只修改了depno=1000那一项
    代码:

    mysql> update csw set sal = sal+500 where depno = 1000;
    Query OK, 1 row affected (0.14 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    

    结果:

    mysql> select * from csw;
    +-----------+-----------+------+-------+
    | name      | hiredate  | sal  | depno |
    +-----------+-----------+------+-------+
    | 关头强    | 2017-4-15 | 1500 | 1000  |
    | 关头强    | 2080-5-1  | 1000 | NULL  |
    | 关头强    | 2080-5-1  | 1000 | NULL  |
    | 关头强    | 2001-1-1  | 1000 | NULL  |
    +-----------+-----------+------+-------+
    4 rows in set (0.00 sec)
    

    修改表中多列的数据:

            mysql> update emp set sal = 3000,deptno = 1 where name = 'aaa';
            Query OK, 1 row affected (0.04 sec)
            Rows matched: 1  Changed: 1  Warnings: 0
            
            mysql> select * from emp;
    

    结果:

            +-------+------------+------+--------+
            | name  | hiredate   | sal  | deptno |
            +-------+------------+------+--------+
            | admin | 1999-09-09 | 1500 |      1 |
            | wuyu  | 2014-08-09 | 2000 |      2 |
            | aaa   | NULL       | 3000 |      1 |
            +-------+------------+------+--------+
            3 rows in set (0.00 sec)
    

    3.删除(delete)

    语法:

    delete from tablename [where condition]
    

    代码:

    mysql> delete from csw where sal = '1000';
    Query OK, 3 rows affected (0.03 sec)
    

    结果:

    mysql> select * from csw;
    +-----------+-----------+------+-------+
    | name      | hiredate  | sal  | depno |
    +-----------+-----------+------+-------+
    | 关头强    | 2017-4-15 | 1500 | 1000  |
    +-----------+-----------+------+-------+
    1 row in set (0.00 sec)
    

    注意:如果不加条件会把表的所有信息删除

    4.查询(select)

    数据插入到表中,就可以使用select命令进行各式各样的查询
    mysql> select * from csw;
    +-----------+-----------+------+-------+
    | name      | hiredate  | sal  | depno |
    +-----------+-----------+------+-------+
    | qiang     | 2017-4-15 | 3000 | 1000  |
    | 小强      | 2080-5-1  | NULL | NULL  |
    | 小强      | 2080-5-1  | NULL | NULL  |
    | 打不死    | 2001-1-1  | NULL | NULL  |
    +-----------+-----------+------+-------+
    
    *表示要将所有的列信息全部显示出来.
    
        mysql> select name,sal from emp;
        +-------+------+
        | name  | sal  |
        +-------+------+
        | admin | 2000 |
        | wuyu  | 2000 |
        +-------+------+
        2 rows in set (0.00 sec)
    

    注意:性能问题,推荐写列名,不要使用*号

    5.查询不重复的记录.distinct 去除重复.###

        mysql> select * from dept;
        +--------+----------+
        | deptno | deptname |
        +--------+----------+
        |      1 | aa       |
        |      2 | aa       |
        |      3 | bb       |
        |      4 | cc       |
        +--------+----------+
        4 rows in set (0.00 sec)
        
        mysql> select distinct deptname from dept;
        +----------+
        | deptname |
        +----------+
        | aa       |
        | bb       |
        | cc       |
        +----------+
        3 rows in set (0.04 sec)
    

    6.条件查询 where关键字可以显示用户指定的数据

    逻辑运算符:

        =,>,<,>=,<=,!=,<>
    

    测试语句:

        mysql> select * from dept where deptno > 2;
        +--------+----------+
        | deptno | deptname |
        +--------+----------+
        |      3 | bb       |
        |      4 | cc       |
        +--------+----------+
        2 rows in set (0.00 sec)
    
        逻辑运算符(or,and)
        mysql> select * from dept where deptno = 1 or deptname = 'cc';
        +--------+----------+
        | deptno | deptname |
        +--------+----------+
        |      1 | aa       |
        |      4 | cc       |
        +--------+----------+
        2 rows in set (0.02 sec)
        
        mysql> select * from emp where name ='admin' and sal >1000;
        +-------+------------+------+--------+
        | name  | hiredate   | sal  | deptno |
        +-------+------------+------+--------+
        | admin | 1999-09-09 | 2000 |      1 |
        +-------+------------+------+--------+
        1 row in set (0.00 sec)
    

    重点 : 排序(order by)

    语法:

        select * from 表名 [where 条件] [order by 列名1[desc|asc],列名2 [desc|asc]....列名n [desc|asc]]
        
        desc : 降序
        asc : 升序
    

    语句:

        mysql> select * from emp order by sal desc ,deptno desc;
        +-------+------------+------+--------+
        | name  | hiredate   | sal  | deptno |
        +-------+------------+------+--------+
        | wuyu  | 2014-08-09 | 2000 |      2 |
        | admin | 1999-09-09 | 2000 |      1 |
        | wwwaa | NULL       | 1000 |      3 |
        +-------+------------+------+--------+
        3 rows in set (0.00 sec)
    

    DCL语句

    1.主要是DBA用来管理系统中的对象权限.

        创建一个数据库用户wuyu,具有对lq数据库中所有表的查询和添加权限.
        mysql> grant select,insert on lq.* to 'wuyu'@'localhost' identified by '123';
        Query OK, 0 rows affected, 1 warning (0.22 sec)
    
        C:\Users\Administrator>mysql -uwuyu -p123
        mysql -u用户名 -p密码
    

    2.MySQL支持的数据类型

        1. 数值类型
            1. integer      java int
            2. smallint     32767~65535
            3. decimal,dec(m,d) 和doublt范围相同 
            4. float
            5. real  不知道
            6. double
            7. money
        2. 字符类型
            1. char         0~255 之间的字符
            2. varcahr      0~65535的字符  
            3. text         65535
            4. longtext     ~~~~~~
            5. VARBINARY(M) 可以存储多少M的字节.
        3. 日期和时间
            1. date         yyyy-mm-dd
            2. datetime     yyyy-mm-dd hh:MM:ss
            3. timestamp    时间戳
            4. time         hh:MM:ss
            5. year         yyyy
    

    MySQL的左连接与右连接:http://www.jianshu.com/p/57e49960abb4

    2人点赞

    数据库

    作者:风吹稻子
    链接:https://www.jianshu.com/p/3b67b481aa20
    来源:简书
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    Reply

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注