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

首页 > 数码 > 内容页

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

2023-01-08 12:09:30 来源：51CTO博客

前言：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=INNODBexplicit_defaults_for_timestamp=true# 全局开启group bysql_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.sqlmysqldump -h192.168.1.125 -uroot -pshlx@@2018 -d test>test-125server-structure.sqldel test-125server-event.sqlmysqldump -h192.168.1.125 -uroot -pshlx@@2018 -E test>test-125server-event.sqldel test-125server-pro.sqlmysqldump -h192.168.1.125 -uroot -pshlx@@2018 -R test>test-125server-pro.sqldel test-125server-data.sqlmysqldump -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.sqlmysql -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也是免费的。

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

如上图，从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 [变量名]，也可以使用临时表记录运行过程。