MySQL操作指南(概要总结篇)

1、准备环境

1.1、下载 MySQL

首先进入到 MySQL Server 下载页面: https://dev.mysql.com/downloads/mysql/ ，然后找到该页面中下图所示区域:

随后在用鼠标左键单击上图中的 “Looking for previous GA versions ?” 连接 ，

单击之后即可在 “Generally Available (GA) Releases” 下方选择 MySQL 版本 和 操作系统，如下图所示:

在上图中选择相应版本(Version) 、操作系统(Operating System) 、操作系统版本( OS Version ) 后，即可在其下方选择相应的 MySQL 来下载。

我们选择上图中红色方框所框起来的那两个压缩包。

点击相应版本之后的 " Download "按钮后，进入下一个页面，在该页面中找到如下图所示的区域:

在以上页面中，点击 " No thanks , just start my download " 连接即可进入下载相应的压缩包。

这里需要注意:

• 因为本文中的内容是基于 MySQL 5.x 完成的，因此建议下载 MySQL 5.6.X 或 5.7.X 。
• 如果你的操作系统是 32 位就下载 mysql-5.7.XX-win32.zip 。
• 如果你的操作系统是 64 位就下载 mysql-5.7.XX-winx64.zip 。
  下载之后，将 该压缩包解压到 之前约定的 X:/applications 目录下备用。

解压后的目录结构如下图所示:

1.2、初始化数据库

1.3、启动数据库

2、权限操作

2.0、 创建数据库

 create  database  数据库名 character  set  utf8 ;

2.1、创建用户

 create user 用户名 identified by '密码' ;

( 这里注意，密码必须使用引号包起来 )

2.2、删除用户

 drop  user 用户名 ;

2.3、修改用户名

 rename user 原用户名 to 新用户名 ;

2.4、授予权限

 grant 权限名 (columns) on 权限对象 to 用户名@域 [ identified by '密码' ] ;

(权限名称 和 权限对象 比较多，参见 《MySQL 技术内幕 第四版》 12.4 )

我们采用以下形式为用户授权:

 grant all on 数据库名称.* to 用户名@域 identified by '密码'; 

例如，假设存在数据库 taobao ，存在用户 malajava ，把 taobao 数据库的所有权限授予 malajava 用户 :

 grant all on taobao.* to malajava@localhost identified by 'malajava' ;

2.5、查看当前用户

 select user() ;

2.6、查看用户的权限

 show  grants  for  '用户名'@'域' 

查看当前用户的权限:

 show  grants ;

2.7、修改 root 用户的密码

第一种方法:

 mysql> set password for 'root'@'localhost' = password('新密码');

第二种方法:

 mysql> use  mysql ;
 mysql> update user set password = password('newpass')  where  user = 'root';
 mysql> flush privileges ;

 mysql> PRIVILEGES;

3、数据库操作

3.1、创建数据库

 create database 数据库名 character set 字符集编码名称;

例如：建立一个名为 malajava 的数据库

 create database malajava ;

创建 malajava 数据库并指定数据库的字符集编码:

 create database malajava character set utf8;

注意，在 MySQL 中使用 UTF-8 编码时，使用的都是 utf8 (别名)

3.2、显示所有的数据库

 show   databases 

对于root用户来说，可以看到所有数据库；

对于非root用户来说，只能看到该用户拥有操作权限的数据库；

3.3、删除数据库

 drop database  数据库名 ;

例如：删除名为 malajava 的数据库

 drop database malajava ;

3.4、连接数据库

连接数据库也称作选择数据库或打开数据库，采用 use 关键字实现:

 use 数据库名 ;

例如：如果 malajava 数据库存在，尝试打开它：

 mysql> use  malajava ;
 Database changed 

3.5、查看当前数据库

查看当前正在操作的数据库(当前已经选择的数据库)

 select  database(); 

3.6、当前数据库包含的表

 show  tables ;

注意，必须先选中一个数据库，即先使用 use 打开一个数据库，比如: use malajava ，之后才可以查看该数据库中所包含的表。

3.7、查看创建数据库时的相关信息

show create database 数据库名称 ;

4、表操作

操作表之前，必须先选中相应的数据库( 使用 user 命令，比如 use malajava ; )

4.1、创建表

 create  table  表名 ( 列名 数据类型(列宽) , ... ) ;

比如创建 student 表:

 create  table  student ( id int(10) primary key , name varchar(20) ) ;

4.2、查看表结构

语法:

 describe  表名 ;

其中，describe 可以缩写为 desc 。

举例(查看 student 表的表结构):

 desc  student ;

4.3、增加列

语法:

 alter  table   表名  add column ( 列名 类型 约束 ) ;

举例( 为student表添加 address 列 ):

 alter  table  student  add column ( address  varchar(50)  );

4.4、修改列

4.4.1 修改列的宽度、类型等:

语法:

 alter table 表名 modify 列名 类型(列宽) ; 

举例(修改 student 表的 address 列的 类型 和 宽度 ):

 alter table student modify address varchar(100) ; 

4.4.2 修改列名

语法:

 alert table 表名  change column 原列名   新列名   类型 ;

举例(将 student 表的 address 列名 修改为 hometown ):

 alert table student change column address  hometown  varchar(100) ;

4.5、删除列

语法:

 alter table 表名 drop column 列名 ;

举例 ( 删除 student 表中的 hometown 列 ) :

 alter table student drop column hometown ;

4.6、删除表

语法:

 drop table 表名 ;

举例( 删除 student 表 ):

 drop table student ;

4.7、截断表

语法:

 truncate table 表名 ;  

举例( 截断 student 表 ):

 truncate table student ;

4.8、重命名表

语法:

 rename table 原表名 to  新表名 ;

举例( 将 student 重命名为 t_students ):

 rename table student to  t_students ;

4.9、查看建表时的相关信息

语法:

 show  create  table  表名 ;

举例:

 show  create  table  t_students ;

5、数据操作

5.1、插入数据

语法:

 insert into 表名 ( 列名 , ... ) values ( 数值 , ... ) ;

举例:

 insert into t_students ( id , name , gender ) values ( 1 , '张无忌' , '男' ) ;

批量插入数据语法:

 insert into 表名 ( 列名 , ... ) values ( 数值 , ... ) , ( 数值 , ... ),( 数值 , ... ) , ... ;

举例:

 insert into t_students ( id , name ) values ( 2 , '周芷若' ) , ( 3 , '赵敏' ) ;

5.2、更新数据

语法:

 update 表名 set 列名 = 取值 , ... [ where 条件 ] ;

其中的 where 子句并不是必须的，可以根据实际需要来确定是否需要跟 where 子句。

举例:

 update  t_students set name='敏敏特穆尔' , gender = '女' where id = 3 ;

注意，如果在 update 语句中没有使用 where 子句筛选，则会更新整张表中的每一行数据。

5.3、删除数据

语法:

 delete from 表名 where 条件 ;

其中的 where 子句不是必须的，根据实际需要来确定是否需要跟 where 子句。

举例( 删除 t_students 表中 id 为 3 的记录 ):

 delete from  t_students where id = 3 ;

注意，如果在 delete 语句中没有使用 where 子句筛选，则会删除整张表中的所有数据。

如果要删除一张表中的所有数据，使用 delete from 效率是较低的，建议使用 truncate table 。

6、数据查询

《待整理》

7、事务控制

7.1、数据库引擎

MySQL 常用引擎: MyISAM 和 InnoDB 。

其中，InnoDB 是支持事务操作的 引擎，因此建表时建议使用该引擎:

create table 表名 ( 列名 类型(列宽) 约束 , … ) engine = InnoDB ;
如果建表时没有指定引擎，早期的MySQL默认采用 MyISAM 引擎，该引擎默认不支持事务操作。

故建完表后需要修改引擎:

 alter  table  表名  type = InnoDB ;  

或者使用:

 alter  table  表名  engine = InnoDB ;

查看修改结果:

 show  table  status from 数据库名 ;

或者使用:

 show create  table  表名 ;

注意：
我们使用的 MySQL 5.6 及 5.7 默认的引擎都是 InnoDB , 因此，建表时无须指定，建表后也无须修改。

7.2、事务隔离级别

7.2.1、ANSI/ISO SQL标准定义的 4 种事务隔离级别

 read uncommited	读未提交

 read commited	读已提交
 
 repeatable read	可重复读
 
 serializable	序列化

7.2.2、查看当前事务的隔离级别:

 select  @@tx_isolation ;

7.2.3、设置 InnoDB 的事务级别

第一种方法:

set 作用域 transaction isolation level 事务隔离级别 ;
第一种方法举例:

 set global transaction isolation level read committed; -- global 作用域表示全局的

 set session transaction isolation level read uncommitted ; -- session 作用域表示当前会话  

 set session transaction isolation level read committed;   

 set session transaction isolation level repeatable read ;

 set session transaction isolation level serializable ;

第二种方法:

set 作用域 tx_isolation = ‘事务隔离级别’ ;
第二种方法举例:

 set  global  tx_isolation='repeatable-read'; -- 全局 ( global )

 set  session  tx_isolation='uncommitted'; -- 当前会话 ( session )

 set  session  tx_isolation='committed'; 

 set  session  tx_isolation='serializable'; 

其中的作用域:

global 表示全局，修改后所有的会话都将生效
session 表示当前会话，修改后仅针对当前会话有效
修改事务隔离级别后，使用 select @@tx_isolation 来查看修改后的隔离级别。

7.3、事务控制语句(TCL)

7.3.1、显式开启一个事务

 begin ;

或:

start transaction ;

注意，以上语句在 Oracle 中不适用。

7.3.2、提交事务

 commit ;

7.3.3、回滚事务

 rollback ;

7.3.4、创建保存点

 savepoint 保存点名称 ;

7.3.5、删除某个事务保存点

 release   savepoint   保存点名称 ; 

7.3.6、闪回(回滚)到某个保存点

 rollback    to   保存点名称 ;

7.3.7、设置事务隔离级别:

参看 7.2.3

7.3.8、禁用 mysql 的自动提交

默认情况下，MySQL默认是自动提交事务的，可以通过以下语句来修改它:

 set   autocommit  =  false ;  -- 不要再自动提交

 set   autocommit  =  true ; -- 自动提交 (默认值)

7.3.9、查看自动提交状态

 mysql> select @@autocommit ;
 +--------------+
 | @@autocommit |
 +--------------+
 |            1 |
 +--------------+
 1 row in set (0.00 sec)

其中，1 表示 true (自动提交) ，0 表示false (不自动提交)。

7.4、隐式提交事务的SQL

当执行 DDL 语句 或 DCL 时，会导致当前未结束的事务被提交。

(DDL ，数据定义语言，比如 create table 、alter table 、drop table 、truncate table )

(DCL ，数据控制语言，比如 drop user 、grant 等)

8、备份与恢复

备份数据通常在命令提示符或终端下，使用 mysqldump 命令完成。

8.1、导出整个数据库

C:> mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名
数据库默认的字符编码为 latin1 ，如果创建数据库时指定了编码，请保持一致。

举例:

 C:\> mysqldump -u root -p malajava > C:/malajava.sql 

8.2、导出一张表

C:> mysqldump -u 用户名 -p 数据库名 表名 > 导出的文件名
举例:

 C:\> mysqldump -u root -p malajava t_students > C:/students.sql

8.3、导出一个数据库结构

 C:\> mysqldump -u root -p -d –add-drop-table malajava > C:/malajava.sql 

其中:

-d  表示仅仅导出表结构，不导出表中的数据 
–add-drop-table  表示在每个 create 语句之前增加一个 drop table 语句

8.4、导入数据库
在 mysql 提示符下，使用 source 命令即可导入相应的 sql 脚本:

 mysql> source 数据库脚本 ;

举例:

 mysql> source C:/malajava.sql 

9、常用数据类型
《待整理》

10、常用函数