数据库MySQL的程序员使用指导

前言： MySQL是开源数据库的代表，为程序员和整个IT行业带来了巨大贡献。尽管目前mysql的拥有者对mysql的未来摇摆不定，但是业界还是一直关注mysql的动态。本文首先介绍了mysql的历史、关联版本、安装配置，然后介绍常用命令，最后介绍了两种开发mysql脚本的工具。

---

1.MySQL简介

---

​Mysql是一个关系型数据库管理系统，最早由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

但是，随着 MySQL 被 Oracle 收购，MySQL 的用户和开发者开始质疑开源数据库的命运，与此同时他们开始寻找替代品。

MySQL数据库的历史可以追溯到1979年，那时Bill Gates退学没多久，微软公司也才刚刚起步，而Larry Ellison的Oracle公司 也才成立不久。那个时候有一个天才程序员Monty Widenius为一个名为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。没过多久，Monty又将此工具用C语言进行了重新编写并移植到了UNIX平台上。当时， 这只是一个很底层且仅面向报表的存储引擎，名叫UNIREG。最初的UNIREG是运行在瑞典人制造的ABC800计算机上的。ABC800的内存只有32KB，CPU是频率只有4MHz的Z80。在1983年Monty Widenius遇到了David Axmark，两人相见恨晚，开始合作运营TcX，Monty Widenius负责技术，David Axmark负责搞管理。后来TcX将UNIREG移植到其他更加强大的硬件平台，主要是Sun的平台。虽然TcX这个小公司资源有限，但Monty Widenius天赋极高，面对资源有限的不利条件，反而更能发挥他的潜能。Monty Widenius总是力图写出最高效的代码，并因此养成了习惯。与Monty Widenius在一起的还有一些别的同事，很少有人能坚持把那些代码持续写到20年后，而Monty Widenius却做到了。

1990年，Monty接到了一个项目，客户需要为当时的UNIREG提供更加通用的SQL接口，当时有人提议直接使用商用数据库， 但是Monty Widenius觉得商用数据库的速度难以令人满意。于是Monty Widenius找到了David Hughes(mSQL的发明人)商讨合作事宜。想借助于mSQL的代码，将它集成到自己的存储引擎中。然而令人失望的是，在经过一番测试后，他们发现mSQL的速度并不尽如人 意，无法满足客户的需求。于是Monty Widenius雄心大起，决心自己重写一个SQL支持。从此MySQL就开始诞生了。

MySQL命名的由来：Monty Widenius有一个女儿，名叫My Widenius，因此他将自己开发的数据库命名为MySQL。Monty还有一个儿子，名为Max，因此在2003年，SAP公司与MySQL公司建立合作伙伴关系后，Monty Widenius又将与SAP合作开发的数据库命名为 MaxDB。而现在的MariaDB中的Maria便是Monty Widenius的小孙女的名字。

MaxDB是一种企业级数据库管理系统(DBMS)，以前称为SAPDB，是著名的企业管理软件供应商SAP公司的自有数据库技术，并由SAP公司开发和支持。2003年，SAP AG和MySQL AB确立了合作伙伴关系，并将数据库系统重命名为MaxDB。自此以后，MaxDB 的开发一直由SAP开发者团队负责，MaxDB是能够承受高负载的开源数据库，它适合于OLAP和OLTP应用，并能提供高可靠性、可用性 、扩展性和非常完善的特性集。

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB来代替MySQL的InnoDB。

MySQL官方logo是小海豚，名叫：sakila(塞拉)，它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供的。根据Ambrose所说，Sakila来自一种叫SiSwati 的斯威士兰方言，也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。

---

2.下载安装和配置​

---

1、下载mysql​

下载地址：​

​​https://dev.mysql.com/downloads/mysql/​​​

选5.7版本​

2、安装mysql​

解压到D:\mysql5727x64​

（注：Ubuntu下使用apt安装​

sudo apt-get install mysql-server​

sudo apt-get install mysql-client​

sudo apt-get install libmysqlclient-dev​

）​

3、设置mysql​

修改环境变量：​

MYSQL_HOME=D:\mysql5727x64​

PATH增加%MYSQL_HOME%\bin​

修改设置文件D:\mysql5727x64\my.ini为：​

[mysql]​
# 设置mysql客户端默认字符集​
default-character-set=utf8 ​
[mysqld]​
#设置3306端口​
port = 3306 ​
# 设置mysql的安装目录​
basedir=D:/mysql5727x64​
# 设置mysql数据库的数据的存放目录​
datadir=D:/mysql5727x64/data​
# 允许最大连接数​
max_cnotallow=10​
# 服务端使用的字符集默认为8比特编码的latin1字符集​
character-set-server=utf8​
# 创建新表时将使用的默认存储引擎​
default-storage-engine=INNODB​
explicit_defaults_for_timestamp=true​
# 全局开启group by​
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

（注：​

Ubuntu 16.02下修改mysql配置​

vim /etc/mysql/mysql.conf.d/mysqld.cnf​

新增一个大小写不敏感的配置：​

）​

4、初始化数据库​

以管理员身份进入cmd：​

执行：​

mysqld --initialize --console​

--initialize也可以使用--initialize-insecure代替，区别在于--initialize随机生成的密码会过期。​

5、安装服务​

mysqld install​

或者采用--install代替，例如指定服务名称：​

mysqld --install mysql-test​

执行后会产生一个密码，应保存起来：​

6、启动服务和修改密码​

启动服务：

net start mysql

修改密码：

mysqladmin -u root -ppfsJQQhQ1m?p password radar

进入命令行：​

mysql -uroot -pradar​

7、jdbc驱动​

在STS中，如果使用maven管理依赖包，则在POM绑定jdbc驱动。​

错误1：​

Loading class ’com.mysql.jdbc.Driver’. This is deprecated.​

The new driver class is `com.mysql.cj.jdbc.Driver’.​

The driver is automatically registered via the SPI and manual​

loading of the driver class is generally unnecessary.​

解决方案：​

原因是旧版本的‘com.mysql.jdbc.Driver’已经弃用​

需要使用新的驱动程序类`com.mysql.cj.jdbc.Driver’​

驱动程序是通过SPI自动注册的，手动加载驱动程序类通常是不必要的。​

错误2：​

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.​

解决办法：​

当spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver时，连接串改为：​

spring.datasource.url=jdbc:mysql://localhost:3306/db_activiti?serverTimeznotallow=Asia/Chongqing&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoRecnotallow=true&autoRecnotallow=true&allowMultiQueries=true​

---

2.常用命令​

---

2.1 基础命令​

1、显示数据库列表。

show databases;​

2、显示库中的数据表：

use mysql;
show tables;​

3、显示数据表的结构：

describe 表名; --缩写desc

4、建库：

create database 库名;​

5、建表：

use 库名；
create table 表名 (字段设定列表)；​

6、删库和删表:

drop database 库名;
drop table 表名；​

7、将表中记录清空：

delete from 表名; --这个清空表只是把数据表内容数据清掉，自增id不会被清掉，自增id会保留
truncate table 表名； --成功返回0，自增id也一同会被清掉

truncate与delete的区别：

a.事务：truncate是不可以rollback的，但是delete是可以rollback的；原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback

b.效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引

c.truncate 不能触发任何Delete触发器。

d.delete 删除可以返回行数

8、显示表中的记录：

select * from 表名

​9、连接MySQL

mysql -h 主机地址 -u用户名 -p用户密码

连接到本机上的 MySQL。

mysql -uroot -pmysql;

连接到远程主机上的 MYSQL。

mysql -h 127.0.0.1 -uroot -pmysql;

连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：

mysql -h110.110.110.110 -u root -p 123; --（注:u与root之间可以不用加空格，其它也一样）

10、退出MYSQL命令​

exit --（回车）

11、修改新密码​

（1）update user set password=PASSWORD('新密码') where user='用户名';​

在终端输入：mysql -u用户名 -p密码，回车进入Mysql。​

> use mysql;​

> update user set password=PASSWORD('新密码') where user='用户名';​

> flush privileges; #更新权限​

> quit; #退出​

（2）mysqladmin -u用户名 -p旧密码 password 新密码​

（3）alter user test identified by 密码;​

（4）set password for test=密码;​

（5）set password for test=password('密码');​

12、显示当前的user：​

mysql> SELECT USER();​

13、增加新用户​

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”
（1）增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入
MYSQL，然后键入以下命令：​

grant select,insert,update,delete on *.* to test1”%" Identified by “abc”;

但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。
（2）增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），​

这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。​

grant select,insert,update,delete on mydb.* to test2@localhost identifiedby “abc”;

如果你不想test2有密码，可以再打一个命令将密码消掉。​

grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

14、删除用户​

mysql -u用户名 -p密码​

mysql>delete from user where user='用户名' and host='localhost';​

mysql>flush privileges;​

2.2 高级操作​

1、存储过程​

（1）调用​

mysql> CALL procedureName(paramentList);
例：mysql> CALL addMoney(12, 500);​

（2）查看名称​

方法一：mysql> SELECT `name` FROM mysql.proc WHERE db = 'your_db_name' AND `type` = 'PROCEDURE';​

方法二：mysql> show procedure status;
​

（3）删除​

mysql> DROP PROCEDURE procedure_name;​

mysql> DROP PROCEDURE IF EXISTS procedure_name;
​

（4）查看定义​

mysql> SHOW CREATE PROCEDURE proc_name;​

mysql> SHOW CREATE FUNCTION func_name;
---------- 示例一-----------​

mysql> DELIMITER $$​

mysql> USE `db_name`$$ //选择数据库​

mysql> DROP PROCEDURE IF EXISTS `addMoney`$$ //如果存在同名存储过程，则删除之​

mysql> CREATE DEFINER= `root`@`localhost` PROCEDURE `addMoney`(IN xid INT(5),IN xmoney INT(6))​

mysql> BEGIN​

mysql> UPDATE USER u SET u.money = u.money + xmoney WHERE u.id = xid; //分号";"不会导致语句执行，因为当前的分割符被定义为$$​

mysql> END$$ //终止​

mysql> DELIMITER ; //把分割符改回分号";"
mysql> call addMoney(5,1000); //执行存储过程
---------- 示例二-----------​

mysql> delimiter //​

mysql> create procedure proc_name (in parameter integer)​

mysql> begin​

mysql> if parameter=0 then​

mysql> select * from user order by id asc;​

mysql> else​

mysql> select * from user order by id desc;​

mysql> end if;​

mysql> end;​

mysql> // //此处“//”为终止符​

mysql> delimiter ;​

mysql> show warnings;​

mysql> call proc_name(1);​

mysql> call proc_name(0);​

2、建表​

命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);​

例子：​

mysql> create table MyClass(​

> id int(4) not null primary key auto_increment,​

> name char(20) not null,​

> sex int(4) not null default '0',​

> degree double(16,2));​

3、插入数据​

命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]​

例子：​

mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);​

4、查询所有行​

mysql> select * from MyClass;​

5、查询前几行数据​

例如:查看表 MyClass 中前 2 行数据​

mysql> select * from MyClass order by id limit 0,2;​

或者​

mysql> select * from MyClass limit 0,2;​

6、删除表中数据​

命令:delete from 表名 where 表达式​

例如:删除表 MyClass 中编号为 1 的记录​

mysql> delete from MyClass where id=1;​

7、修改表中数据​

命令:update 表名 set 字段=新值,... where 条件​

mysql> update MyClass set name='Mary' where id=1;​

8、在表中增加字段​

命令:alter table 表名 add 字段 类型 其他;​

例如:在表 MyClass 中添加了一个字段 passtest,类型为 int(4),默认值为 0​

mysql> alter table MyClass add passtest int(4) default '0'​

9、更改表名​

命令:rename table 原表名 to 新表名;​

例如:在表 MyClass 名字更改为 YouClass​

mysql> rename table MyClass to YouClass;​

10、更新字段内容​

命令:update 表名 set 字段名 = 新内容​

update 表名 set 字段名 = replace(字段名, '旧内容', '新内容');​

例如:文章前面加入 4 个空格​

update article set cnotallow=concat(' ', content);​

11、从数据库导出数据库文件​

用“mysqldump”命令​

首先进入 DOS 界面,然后进行下面操作。​

1)导出所有数据库​

格式:mysqldump -u [数据库用户名] -p -A>[备份文件的保存路径]​

2)导出数据和数据结构​

格式:mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]​

举例:​

例 1:将数据库 mydb 导出到 e:\MySQL\mydb.sql 文件中。​

打开开始->运行->输入“cmd”,进入命令行模式。​

c:\> mysqldump -h localhost -u root -p mydb >e:\MySQL\mydb.sql​

然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。​

例 2:将数据库 mydb 中的 mytable 导出到 e:\MySQL\mytable.sql 文件中。​

c:\> mysqldump -h localhost -u root -p mydb mytable>e:\MySQL\mytable.sql​

例 3:将数据库 mydb 的结构导出到 e:\MySQL\mydb_stru.sql 文件中。​

c:\> mysqldump -h localhost -u root -p mydb --add-drop-table >e:\MySQL\mydb_stru.sql​

备注:-h localhost 可以省略,其一般在虚拟主机上用。​

3)只导出数据不导出数据结构​

格式:​

mysqldump -u [数据库w用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径]​

4)导出数据库中的Events​

格式:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]​

5)导出数据库中的存储过程和函数​

格式:mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]​

6)导出数据库中的数据结构​

格式:mysqldump -u [数据库用户名] -p -d要备份的数据库名称]>[备份文件的保存路径]​

例如：​

del test-125server-structure.sql​
mysqldump -h192.168.1.125 -uroot -pshlx@@2018 -d test>test-125server-structure.sql​
del test-125server-event.sql​
mysqldump -h192.168.1.125 -uroot -pshlx@@2018 -E test>test-125server-event.sql​
del test-125server-pro.sql​
mysqldump -h192.168.1.125 -uroot -pshlx@@2018 -R test>test-125server-pro.sql​
del test-125server-data.sql​
mysqldump -h192.168.1.125 -uroot -pshlx@@2018 -t test>test-125server-data.sql

注意test-125server-event.sql和test-125server-pro.sql大小相同，都比test-125server-data.sql大一点，如下：​

12、从外部文件导入数据库中​

)使用“source”命令​

首先进入“mysql”命令控制台,然后创建数据库,然后使用该数据库。最后执行下面操作。​

mysql>source [备份文件的保存路径]​

2)使用“<”符号​

首先进入“mysql”命令控制台,然后创建数据库,然后退出 MySQL,进入 DOS 界面。最后执行下面操作。​

mysql -u root –p < [备份文件的保存路径]​

例如：​

mysql -h127.0.0.1 -uroot -pshlx@@2018 < test-125server-structure.sql​
mysql -h127.0.0.1 -uroot -pshlx@@2018 < test-125server-pro.sql

3、备份数据库：​

注意，mysqldump命令在DOS的 mysql\bin 目录下执行,不能在mysql环境下执行，因此，不能以分号“;”结尾。若已登陆mysql，请运行退出命令mysql> exit
（1）导出整个数据库​

导出文件默认是存在mysql\bin目录下​

mysqldump -u用户名 -p数据库名 > 导出的文件名​

mysqldump -uroot -p123456 database_name > outfile_name.sql

（2）导出一个表​

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名​

mysqldump -u user_name -p database_name table_name > outfile_name.sql

（3）导出一个数据库结构​

mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
（4）带语言参数导出​

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql​

14、将文本数据转到数据库中​

（1）文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替.例：​

3 rose 大连二中 1976-10-10​

4 mike 大连一中 1975-12-23​

假设你把这两组数据存为school.txt文件，放在c盘根目录下。
（2）数据传入命令​

mysql> load data local infile "c:\school.txt" into table 表名;​

注意：你最好将文件复制到mysql\bin目录下，并且要先用use命令打表所在的库。​

---

3.使用SQL Developer来开发脚本

---

MySQL开发工具比较多，例如：​

Navicat for mysql​

PHPMyAdmin​

Mycli​

但是，由于更常用Oracle，我还是习惯于Oracle SQL Developer，一般使用它来开发Oracle数据库的PL/SQL脚本，但是对于Mysql也是适用的。而且SQL Developer也是免费的。​

1. 下载​官网下载链接为：​
   ​​https://www.oracle.com/cn/tools/downloads/oracle-sql-developer-download.html​​​
   不过有点慢，另外一个国内链接：​
   ​​http://down-ww3.7down.net/pcdown/soft/xiazai/sqldeveloper64.zip​​​
2. 设置进入“工具”--“首选项”菜单：

如上图，从maven本地库中找到jdbc驱动。​

然后，新增连接：​

如上图，输入连接名、用户名、密码后选择MySQL标签，这时候提示错误：​

The server time zone value is unrecognized or represents more than one time zone.​

解决办法：肯定和时区有关，查询时区：​

system_time_zone是空的。​

修改时区为中国，如下图：​

点“测试”就成功了：​

点连接即可在左边看到数据库：​

3、开始工作

（1）创建表

--创建学生表
drop table if exists student;
create table student(
 id int,
 name varchar(200),
 teacher_id int,
 teacher_name varchar(200)
);

--创建教师表：
drop table if exists teacher;
create table teacher(
 id int,
 name varchar(200)
);

（2）创建存储过程

drop procedure pr_testcursor;
create procedure pr_testcursor(in i_name varchar(200))
begin 
    #定义变量
    declare v_id varchar(32); 
    declare v_name varchar(50); 
    DECLARE v_done INT DEFAULT FALSE; 
    #创建游标并存储数据，
    DECLARE v_cursor CURSOR FOR ( SELECT id,name FROM teacher where name = i_name); 
    #游标中内容执行完设置done为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; 
    #设置手动提交
    set autocommit = 0;
    #打开游标
    OPEN v_cursor; 
    #执行循环
    handle_cur: LOOP 
        #判断是否结束循环
        IF v_done THEN 
            LEAVE handle_cur; 
        END IF;
        #取出游标中的值
        FETCH v_cursor into v_id, v_name; 
        #更新数据
        UPDATE student SET teacher_name = v_name WHERE teacher_id = v_id; 
    END LOOP handle_cur; 
    #释放游标
    CLOSE v_cursor; 
    #提交
    COMMIT; 
END

（3）调试存储过程​

SQL Developer功能强大，我感觉最强大最方便的还是用SQL Developer来调试存储过程，可惜的是sql developer还不支持mysql存储过程的单步调试，TOAD for mysql支持单步调试。​

先插入三条数据：​

然后，运行存储过程：​

要调试中间的变量，需要在存储过程中加入select [变量名]，也可以使用临时表记录运行过程。​

---

4.使用TOAD for mysql​来开发脚本

---

TOAD本身也是著名的sql脚本开发工具，用于mysql开发可能不多，更多用于oracle开发。

下载链接：

​​https://www.jb51.net/database/527579.html​​​​

安装后​

File-->new菜单：​

输入后：​

连接后，左边：​

调试存储过程，先选中左边Object Explorer-->procedures里面的已经创建好的存储过程，点右键，选中菜单中的Debug：​

执行后出现：​

点OK：​

输入参数：​

注意本工具使用可能因为操作系统原因遇到问题。如果无法正常使用，则推荐使用其他工具。

---

5.使用Navicat for mysql来开发脚本

---

Navicat用于mysql脚本开发非常普遍。如下图：

此处暂时留白，更详细的介绍待以后补充。