|
前言导读 在日常开发中，存储数据的最常用的方式便是数据库了，其中最为著名的便是 MySQL数据库，因他简便易于上手而且可扩展性强大，跨平台使得它广为使用，今天我们就来具体聊聊。 1.安装 2.数据类型 3.库 表常见操作 4.索引 5.视图 6.函数 7.游标 8.触发器 9.存储过程 10.事务 11.备份与还原 12.用户账号 13.其他 一、安装过程 我们所使用的是MySQL 5.7版本，这个版本算是挺不错的。下面我们来介绍下这个版本的具体安装过程吧。 1.解压安装 因为官网目前下载不了，为了大家安装过程中方便，我们下载一个免安装版mysql， 地址： 
为了让mysql 可以在任意目录运行，我们可以将mysql的bin目录添加到环境变量，如图：  3.修改配置文件 在mysql目录下创建一个my.ini配置文件，修改配置： [mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir='E:\mysql\mysql-5.7.11-winx64'
# 设置mysql数据库的数据的存放目录
datadir='E:\mysql\mysql-5.7.11-winx64\data'
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB4.安装mysql 以管理员身份运行命令提示符，输入mysqld install命令（注意是mysqld而不是mysql）回车，即可安装MySQL，提示：Service successfully installed，则表示安装成功，如下图所示：  移除服务：mysqld remove 5.启动mysql服务 在命令提示符下执行命令， 启动mysql：net start mysql 停止：net stop mysql 发现启动不了，如图：  这是怎么回事了，哦~原来是因为还没有初始化，那么我们现在来进行初始化， 执行指令：mysqld --initialize-insecure --user=mysql， mysqld --initialize-insecure：自动生成无密码的root用户， mysqld --initialize：自动生成带随机密码的root用户， mysqld --initialize --user=mysql --console：生成随机密码并显示在控制台窗口， data文件夹不为空是不能执行这个命令的（删掉data文件即可）， 执行mysqld --initialize-insecure --user=mysql命令会自动新建data文件夹。执行结果如图：  可以看到执行成功了，再次启动mysql服务，启动成功。如图：  6.进入mysql，修改登陆密码确保安全 第一次进入mysql 是没有密码就可以登陆了，我们可以设置登陆密码， 更改root密码，输入： 因为新版mysql数据库下没有password字段，所以需要使用如下命令： update mysql.user set authentication_string=password('123321') where user='root';这是必须的，不然操作没有效果。 也可以这样修改密码： set password = password('123456');这样就将root用户的密码设置为了 123456了，下面我们退出mysql再重新进去，顺带说下退出命令： 接下来在进入mysql，如图：  这样root用户就有了密码，他的密码就是123321. 整个安装过程就完成了，是不是很简单了。 另外，为了提高交互效果，我们还可以一个软件-------sqlyong，他可以和mysql建立链接， 下载地址： 然后输入压缩包内自带的注册码破解就可以免费使用了。   2.mysql 数据类型 1.数值类型 MySQL支持所有标准SQL数值数据类型。 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。 作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。 类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节 (-3.402 823 466 E 38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E 38) 0，(1.175 494 351 E-38，3.402 823 466 E 38) 单精度 浮点数值 DOUBLE 8 字节 (-1.797 693 134 862 315 7 E 308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M 2否则为D 2 依赖于M和D的值 依赖于M和D的值 小数值 2.日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个'零'值，当指定不合法的MySQL不能表示的值时使用'零'值。 TIMESTAMP类型有专有的自动更新特性，将在后面描述。 类型 大小 (*字节)** 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 3.字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。 类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据 CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。 BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。 有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。 Enum('fds','fsa','fasf') ：枚举类型 set(val1,val2,val3)：集合类型 3.库表操作 １、MySQL触发器的创建语法：
CREATE[DEFINER = { 'user' | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body
2、MySQL创建语法中的关键词解释：
字段 含义 可能的值
DEFINER= 可选参数，指定创建者， DEFINER='root@%'
默认为当前登录用户（CURRENT_USER）；
该触发器将以此参数指定的用户执行， DEFINER=CURRENT_USER
所以需要考虑权限问题；
trigger_name 触发器名称，最好由表名 触发事件关键词 触发时间关键词组成；
trigger_time 触发时间，在某个事件之前还是之后； BEFORE、AFTER
INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发；
UPDATE：更新操作触发器，UPDATE操作时触发；
trigger_event 触发事件，如插入时触发、删除时触发； DELETE：删除操作触发器，DELETE、REPLACE操作时触发；
INSERT、UPDATE、DELETE
table_name 触发操作时间的表名；
可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（
如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以
trigger_order 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
FOLLOWS：当前创建触发器在现有触发器之后激活； FOLLOWS、PRECEDES
PRECEDES：当前创建触发器在现有触发器之前激活；
trigger_body 触发执行的SQL语句内容，一般以begin开头，end结尾 BEGIN .. END
触发执行语句内容（trigger_body）中的OLD，NEW
触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中，
我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED），
OLD表示将要删除的旧行（相当于MS SQL的DELETED）。
通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用，
下面是对应事件是否支持OLD、NEW的对应关系：
事件 OLD NEW
INSERT × √
DELETE √ ×
UPDATE √ √
由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW；
DELIMITER $
... --触发器创建语句；
$ --提交创建语句；
DELIMITER ;
select * FROM information_schema.triggers;
SHOW TRIGGERS; #查看触发器
-- 通过information_schema.triggers表查看触发器：
select * FROM information_schema.triggers;
-- mysql 查看当前数据库的触发器
SHOW TRIGGERS;
-- mysql 查看指定数据库'people'的触发器
SHOW TRIGGERS FROM people;
创建测试表
DROP TABLE IF EXISTS tb;
CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);
CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);
创建触发器
DELIMITER $
#drop trigger if exists df_names$ 删除前先判断触发器是否存在
CREATE DEFINER =CURRENT_USER
TRIGGER df_names
BEFORE INSERT ON tb
AFTER UPDATE ON bt
FOR EACH ROW
BEGIN
#set new.ct=new.id*5;
#SET @ct=12; 变量
#SET @pass='hjfd';
IF old.type=1 THEN
UPDATE bt SET ct=old.ct WHERE fid=old.id;
ELSE IF old.type=2 THEN
UPDATE bt SET pass=old.pass WHERE fid=old.id;
END$
DELIMITER;
测试
INSERT INTO tb(id) VALUES(4);
SELECT *FROM tb;
4.索引 5.视图 １、MySQL触发器的创建语法：
CREATE[DEFINER = { 'user' | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body
2、MySQL创建语法中的关键词解释：
字段 含义 可能的值
DEFINER= 可选参数，指定创建者， DEFINER='root@%'
默认为当前登录用户（CURRENT_USER）；
该触发器将以此参数指定的用户执行， DEFINER=CURRENT_USER
所以需要考虑权限问题；
trigger_name 触发器名称，最好由表名 触发事件关键词 触发时间关键词组成；
trigger_time 触发时间，在某个事件之前还是之后； BEFORE、AFTER
INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发；
UPDATE：更新操作触发器，UPDATE操作时触发；
trigger_event 触发事件，如插入时触发、删除时触发； DELETE：删除操作触发器，DELETE、REPLACE操作时触发；
INSERT、UPDATE、DELETE
table_name 触发操作时间的表名；
可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（
如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以
trigger_order 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
FOLLOWS：当前创建触发器在现有触发器之后激活； FOLLOWS、PRECEDES
PRECEDES：当前创建触发器在现有触发器之前激活；
trigger_body 触发执行的SQL语句内容，一般以begin开头，end结尾 BEGIN .. END
触发执行语句内容（trigger_body）中的OLD，NEW
触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中，
我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED），
OLD表示将要删除的旧行（相当于MS SQL的DELETED）。
通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用，
下面是对应事件是否支持OLD、NEW的对应关系：
事件 OLD NEW
INSERT × √
DELETE √ ×
UPDATE √ √
由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW；
DELIMITER $
... --触发器创建语句；
$ --提交创建语句；
DELIMITER ;
select * FROM information_schema.triggers;
SHOW TRIGGERS; #查看触发器
-- 通过information_schema.triggers表查看触发器：
select * FROM information_schema.triggers;
-- mysql 查看当前数据库的触发器
SHOW TRIGGERS;
-- mysql 查看指定数据库'people'的触发器
SHOW TRIGGERS FROM people;
创建测试表
DROP TABLE IF EXISTS tb;
CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);
CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);
创建触发器
DELIMITER $
#drop trigger if exists df_names$ 删除前先判断触发器是否存在
CREATE DEFINER =CURRENT_USER
TRIGGER df_names
BEFORE INSERT ON tb
AFTER UPDATE ON bt
FOR EACH ROW
BEGIN
#set new.ct=new.id*5;
#SET @ct=12; 变量
#SET @pass='hjfd';
IF old.type=1 THEN
UPDATE bt SET ct=old.ct WHERE fid=old.id;
ELSE IF old.type=2 THEN
UPDATE bt SET pass=old.pass WHERE fid=old.id;
END$
DELIMITER;
测试
INSERT INTO tb(id) VALUES(4);
SELECT *FROM tb;
6.函数 7.游标 创建、打开、关闭游标 # 定义名为ordernumbers的游标，检索所有订单
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
-- 使用游标数据
# 例1：检索 当前行 的order_num列，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- get order number 获得订单号
FETCH ordernumbers INTO o;
/*fetch检索 当前行 的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。
对检索出的数据不做任何处理。*/
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
-- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
-- open the cursor 打开游标
open ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获得订单号
FETCH ordernumbers INTO o;
-- FETCH在REPEAT内，因此它反复执行直到done为真
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- declare the cursor 声明游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- create a table to store the results 新建表以保存数据
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,total DECIMAL(8,2));
-- open the cursor 打开游标
OPEN ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获取订单号
FETCH ordernumbers INTO o;
-- get the total for this order 计算订单金额
CALL ordertotal(o,1,t); # 参见23章代码，已创建可使用
-- insert order and total into ordertotals 将订单号、金额插入表ordertotals内
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 调用存储过程 precessorders()
CALL processorders();
# 输出结果
SELECT * FROM ordertotals;
8.触发器 9.存储过程 SET AUTOCOMMIT=off ;禁用或启用事务的自动提交模式 off ON
SET SESSION AUTOCOMMIT = OFF； 禁用或启用事务的session自动提交模式 off ON
SHOW VARIABLES LIKE '%auto%'; -- 查看变量状态
执行DML语句是其实就是开启一个事务
只能回滚insert、delete和update语句
对于create、drop、alter这些无法回滚事务只对DML有效果
rollback，或者commit后事务就结束了
自动提交模式用于决定新事务如何及何时启动
START TRANSACTION; 启用自动提交模式下显式地启动事务
COMMIT和ROLLBACK; 禁用自动提交模式显式地提交或回滚
-- 事务 transaction 指一组sql语句
-- 回退 rollback 指撤销指定sql语句的过程
-- 提交 commit 指将未存储的sql语句结果写入数据库表
-- 保留点 savepoint 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）
-- 控制事务处理
# 开始事务及回退
SELECT * FROM ordertotals; # 查看ordertotals表显示不为空
START TRANSACTION; # 开始事务处理
DELETE FROM ordertotals; # 删除ordertotals表中所有行
SELECT * FROM ordertotals; # 查看ordertotals表显示 为空
ROLLBACK; # rollback语句回退
SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表显示不为空
# commit 提交
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; # 仅在上述两条语句不出错时写出更改
# savepoint 保留点
# 创建保留点
SAVEPOINT delete1;
# 回退到保留点
ROLLBACK TO delete1;
# 释放保留点
RELEASE SAVEPOINT delete1;
-- 更改默认的提交行为
SET autocommit = 0; # 设置autocommit为0（假）指示MySQL不自动提交更改10.事务 11.备份与还原 备份一个数据库:mysqldump -u root -p --opt people> df.txt #--opt优化执行速度
备份两个数据库:mysqldump -u root -p --opt --databases people hw > all.txt
备份全部数据库:mysqldump -u root -p --opt --all-DATABASES > all.txt
恢复数据库:mysqldump -u root -p --opt --databases people hw < all.txt
1. 导出一张表
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
mysqldump -u用户名 -p密码 --lock-ALL-TABLES --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件12.用户账号 13.其他 SHOW STATUS;显示广泛的服务器状态信息
SHOW PROCEDURE STATUS;
SHOW GRANTS;显示授予用户的安全权限
SHOW ERRORS;显示服务器的错误信息
SHOW WARNINGS;显示服务器的警告信息
SHOW PROCESSLIST;显示哪些线程正在运行
SHOW VARIABLES;显示系统变量信息
SELECT DATABASE(); 查看当前数据库
SELECT NOW(), USER(), VERSION():显示当前时间、用户名、数据库版本
SHOW ENGINES 引擎名 {LOGS|STATUS}:显示存储引擎的日志和状态信息
SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%'; 确定所用系统的字符集和校对
SHOW VARIABLES LIKE 'character_set_client%'; 客户端向服务器发送数据时使用的编码
SHOW VARIABLES LIKE 'character_set_results%'; 服务器端将结果返回给客户端所使用的编码
SHOW VARIABLES LIKE 'character_set_connection%'; 连接层编码
SHOW CHARACTER SET;查看所支持的字符集完整列表
SHOW COLLATION;查看所支持校对的完整列表,以及它们适用的字符集
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK; -- 相当于完成以上三个设置
创建window服务:sc CREATE mysql binPath= mysqld_bin_path
数据文件目录:DATA DIRECTORY='目录'
索引文件目录:INDEX DIRECTORY = '目录'希望能帮到大家，谢谢！！！ |
|
|
来自： piyanat > 《Database》
