初识MySQL

为什么学习数据库

安装建议

1. 解压到安装的目录，如：D:environmentmysql-5.7.31
2. 添加环境变量
   1. 我的电脑-》属性-》高级-》环境变量
   2. 选择PATH，在其后添加：MySQL安装目录下的bin目录路径，如：D:environmentmysql-5.7.31bin
3. 在MySQL安装目录D:environmentmysql-5.7.31下新建my.ini文件
4. 编辑my.ini文件
   1. 注意：data目录不需要创建，会在初始化时自动生成
   2. 注意：skip-grant-tables是跳过密码验证，设置密码后，需要注释掉

[mysqld]
basedir=D:environmentmysql-5.7.31
datadir=D:environmentmysql-5.7.31data
port=3306
skip-grant-tables
character-set-server=utf8

5. 安装MySQL服务：启动管理员模式下的cmd，并将路径切换到MySQL下的bin目录，然后输入mysqld -install，成功，返回：Service successfully installed.

cd /d D:environmentmysql-5.7.31bin
mysqld -install

6. 初始化数据文件：输入mysqld --initialize-insecure --user=mysql，成功，生成data目录。data目录下的目录对应数据库，如：mysql，sys，performance_schema。

mysqld --initialize-insecure --user=mysql

7. 启动MySQL：输入net start mysql，成功，返回：MySQL 服务正在启动 MySQL服务已经启动成功。并且任务管理器中出现MySQL服务。

net start mysql

8. 进入MySQL管理界面：输入mysql -u root -p,回车，不输密码，再回车，成功。
   1. 注意：-p后不能加空格，空格也是字符，也会认为是密码。
   2. 注意：配置skip-grant-tables，会跳过密码验证，输入密码时，不需要输入，直接回车。
   3. 注意：注释掉skip-grant-tables后，有两种输入密码方法：一是-p后直接回车，再输入密码；二是-p后直接输密码，再回车，如：mysql -u root -p123456，但是不推荐这样。

mysql -u root -p

9. 更改root密码，其实就是修改mysql数据库的user表的authentication_string字段，password()函数用于md5加密。

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

10. 刷新权限：flush privileges，

flush privileges;

11. 注释掉my.ini文件中的skip-grant-tables，不再跳过密码验证。

[mysqld]
basedir=D:environmentmysql-5.7.31
datadir=D:environmentmysql-5.7.31data
port=3306
#skip-grant-tables
character-set-server=utf8

12. 重启mysql：net stop mysql，net start mysql

# 退出mysql
exit
# 关闭mysql
net stop mysql
# 启动mysql
net start mysql

13. 测试。

C:UsersAdministrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 15
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

安装SQLyog

下载安装

连接数据库

简单操作

1. 新建数据库school，基字符集：utf8，数据库排序规则：utf8_general_ci。

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢。
如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。
ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的；
bin 是二进制, a 和 A 会别区别对待。
例如你运行：
SELECT * FROM table WHERE txt = 'a'
那么在utf8_bin中你就找不到 txt = 'A' 的那一行， 而 utf8_general_ci 则可以。
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

每一个SQLyog的执行操作，本质就是对应了一个SQL语句，都可以在软件的历史记录中查看。
如：创建数据库，在历史记录中如下，本质是执行语句：CREATE DATABASE schoolCHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 新建表student，引擎：InnoDB，字符集：utf8，核对：utf8_general_ci。

对应语句如下

CREATE TABLE `school`.`student` ( `id` INT(10) NOT NULL COMMENT '学员ID', `name` VARCHAR(100) NOT NULL COMMENT '学员姓名', `age` INT(3) NOT NULL COMMENT '学员年龄', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

3. 查看表：右键点击student，打开表数据窗口，查看表数据窗口。

4. 添加记录：在表数据窗口，输入记录，点击保存或者点击刷新，保存记录。

INSERT INTO `school`.`student` (`id`, `name`, `age`) VALUES ('1003', 'zhwj', '66');

命令行连接数据库

单行注释：--
多行注释：/* */

mysql -u root -p --连接数据库

exit; --退出连接

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改用户密码

flush privileges; --刷新权限

--------------------------------------------------

show databases; --查看所有的数据库

use school; --切换数据库

show tables; --查看数据库中所有的表

describe student; --查看表的结构
describe student id; --查看表中字段id的信息

create database westos; --创建数据库
CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci; --创建数据库

操作数据库

SQL语言的分类

注意事项

1. 学习顺序：操作数据库>操作数据表>操作数据。
2. mysql关键字不区分大小写。
3. 单行注释：--
4. 多行注释：/* */
5. 字符集的区别：utf8_unicode_ci、utf8_general_ci、utf8_general_cs、utf8_bin。

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢。
如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。
ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的；
bin 是二进制, a 和 A 会别区别对待。
例如你运行：
SELECT * FROM table WHERE txt = 'a'
那么在utf8_bin中你就找不到 txt = 'A' 的那一行， 而 utf8_general_ci 则可以。
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

1. 创建数据库
   

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢。
如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。
ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的；
bin 是二进制, a 和 A 会别区别对待。
例如你运行：
SELECT * FROM table WHERE txt = 'a'
那么在utf8_bin中你就找不到 txt = 'A' 的那一行， 而 utf8_general_ci 则可以。
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

CREATE DATABASE IF NOT EXISTS westos;
CREATE DATABASE IF NOT EXISTS westos1 CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 删除数据库

DROP DATABASE IF EXISTS westos1;

3. 使用数据库

USE school; --切换数据库
USE `school`; --如果表名或字段名是一个特殊字符，需要加反引号，如：`school`

4. 查看数据库

SHOW DATABASES; --查看所有数据库

5. 查看创建数据库的语句

SHOW CREATE DATABASE `school`; --查看创建数据库的语句
--查询结果
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

数据类型

数值

字符串

日期时间

字段属性

1. 创建表

-- 如果表名或者字段名是一个特殊字符，需要加反引号（tab键上面的符号），如：`student`
-- AUTO_INCREMENT 自增，默认+1
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(1) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATE DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '学员';

-- 格式
CREATE TABLE [IF NOT EXISTS] `表名` (
	`字段名` 列类型 [属性] [索引] [注释],
  ......
	`字段名` 列类型 [属性] [索引] [注释],
	PRIMARY KEY (`字段名`)
) [表类型] [字符集] [校对规则] [表注释];

2. 删除表

DROP TABLE IF EXISTS `student`;

3. 查看表

SHOW TABLES; --查看所有表

4. 查看表结构

DESCRIBE `student`;
DESC `student`;

5. 查看创建数据表的语句

SHOW CREATE TABLE `student`; --查看创建数据表的语句
--查询结果
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(1) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学员'

修改表

1. 修改表名

-- ALTER TABLE `旧表名` RENAME AS `新表名`;
ALTER TABLE `student` RENAME AS `student1`;

2. 增加字段

-- ALTER TABLE `表名` ADD `字段名` 数据类型 [约束] [注释];
ALTER TABLE `student1` ADD `phone` INT(11) DEFAULT NULL COMMENT '电话';

3. 修改字段名及字段类型、约束、注释，注意：字段名字段类型必填，其它可选。

-- ALTER TABLE `表名` CHANGE `旧字段名` `新字段名` 字段类型 [约束] [注释];
ALTER TABLE `student1` CHANGE `phone` `tel` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '联系方式';

4. 修改字段类型、约束、注释，注意：字段类型必填，其它可选，不能修改字段名。

-- ALTER TABLE `表名` MODIFY `字段名` 字段类型 [约束] [注释];
ALTER TABLE `student1` MODIFY `tel` INT(11) DEFAULT NULL COMMENT '电话';

5. 删除字段

-- ALTER TABLE `表名` DROP `字段名`;
ALTER TABLE `student1` DROP `tel`;

数据库引擎

1. mysql默认的字符集编码是Latin1，不支持中文。
2. 设置数据表字符集编码的两种方式，两种方式最好同时使用，防止创建表时，没有设置字符集编码，导致中文乱码。
   1. 创建表时添加CHARSET=utf8
   2. 在my.ini中配置默认编码character-set-server=utf8

CHARSET=utf8

character-set-server=utf8

MySQL数据管理

外键

1. 创建表时，增加外键约束

-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名称',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '年级';

-- 创建学生表，学生表的grade_id字段引用年级表的id字段
-- 定义外键key
-- 给这个外键添加约束（执行引用（reference））
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名称',
	`grade_id` INT(10) NOT NULL COMMENT '年级id',
	PRIMARY KEY (`id`),
	KEY `FK_grade_id` (`grade_id`),
	CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '学员';

2. 删除有外键关系的表时，必须先删除引用的表（从表），再删除被引用的表（主表）
3. 添加外键

-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名称',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '年级';

-- 创建学员表
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名称',
	`grade_id` INT(10) NOT NULL COMMENT '年级id',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '学员';

-- 添加外键
-- ALTER TABLE `表名` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`);
ALTER TABLE `student`
ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的字段名`) REFERENCES `被引用表名` (`被引用字段名`);

-- insert into 表名(字段1,字段2,字段3) values(值1,值2,值3)
-- 主键自增，可以不写
INSERT INTO `grade`(`name`) VALUES('大一');

-- 插入一条数据，部分字段
INSERT INTO `grade`(`name`) VALUES('大二');

-- 插入一条数据，全部字段
INSERT INTO `grade`(`id`,`name`) VALUES(11,'大三');

-- 插入多条数据时，values后面的值，需要用括号和逗号隔开，如：values(),(),()
-- 插入多条数据，部分字段
INSERT INTO `grade`(`name`) VALUES('大四'),('大五'),('大六');

-- 插入多条数据，全部数据
INSERT INTO `grade`(`id`,`name`) VALUES(100,'大旗'),(200,'大坝'),(300,'大酒');

修改数据

-- update `表名` set 字段名1=值/其它字段值/变量,字段名2=值 where 条件
-- 修改一个字段的值
UPDATE `grade` SET `name`='小一' WHERE `id`=1;

-- 修改一个字段的值为其它字段的值
UPDATE `grade` SET `name`=`id` WHERE `id`=11;

-- 修改多个字段的值
UPDATE `grade` SET `id`=20,`name`='小二' WHERE `id`=2;

删除数据

-- delete from `表名` where 条件
DELETE FROM `grade` WHERE `id`=1;

清空表

-- truncate table `表名`
TRUNCATE TABLE `grade`;

delete和truncate的区别

DQL数据查询语言

DQL

-- 查询全部字段
SELECT * FROM `grade`;

-- 查询指定字段
SELECT `name` FROM `grade`;

-- 别名 AS，字段和表都可以用别名
SELECT `name` AS `名称` FROM `grade`;

拼接(concat)和去重(distinct)

-- 函数 concat(a,b,c) 拼接abc，可以拼接字符串或字段值
SELECT CONCAT('名称：',`name`) AS `名称` FROM `grade`;
SELECT CONCAT(`id`,':',`name`) AS `id:名称` FROM `grade`;

-- 关键字 distinct 去重
-- 去除select查询出来的结果中重复的数据，重复的数据只显示一条
-- 去重一般配合count使用，用来统计数量，因为distinct不能显示其它不去重的字段
-- 单个字段去重
SELECT DISTINCT `name` FROM `grade`;

-- 多个字段去重
SELECT DISTINCT `id`,`name` FROM `grade`;

-- 配合count使用，用来统计数量
SELECT COUNT(DISTINCT `name`) AS `count` FROM `grade`;

查询函数/计算表达式/系统变量

数据库中的表达式：文本值、列、null、函数、计算表达式、系统变量......

-- select 表达式 [from 表名]

-- 查询系统版本（函数）
SELECT VERSION();
SELECT VERSION(),`name` FROM `grade`;

-- 用来计算（计算表达式）
SELECT 123*3-12 AS `结果`;
SELECT 123*3-12 AS `结果`,`name` FROM `grade`;
-- 数字类型的字段计算,其它类型不可以
SELECT `id`/100 FROM `grade`;

-- 查询自增的默认步长（变量）
-- @a用户变量，@@a系统变量
SELECT @@auto_increment_increment AS '自增步长';
SELECT @@auto_increment_increment AS '自增步长',`name` FROM `grade`;

where条件字句

运算符尽量使用英文字母

-- 查询成绩95到100分的学员
select `name`,`score` from `result` where `score`>=95 and `score`<=100;
select `name`,`score` from `result` where `score` between 95 and 100;

-- 查询成绩不是100分的学员
select `name`,`score` from `result` where `score`!=100;
select `name`,`score` from `result` where not `score`=100;

模糊查询：比较运算符

-- 通配符,只能用在like中 %代表0到任意个字符，_代表一个字符

select 'name' like '%me';
select 'name' like 'na%';
select 'name' like '%am%';

select 'name' in ('name','na','me');

联表查询join

自连接

-- 自连接：可以把一张表看成两张一样的表
select a.`id` as '父', b.`id` as '子'
from `menu` as a,`menu` as b
where a.`id`=b.`pid`

排序和分页

-- 排序 order by
-- 升序 asc
-- 降序 desc
order by `number` desc

-- 分页 limit 起始下标,条数
-- 起始下标从0开始
limit 0,5

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1) * pageSize, pageSize
-- n:当前页 pageSize:条数

分组和过滤

group by 分组
having 过滤

子查询和嵌套查询

-- 子查询
select id,name from student
where grade_id=(select id from grade where name='大二')

MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 返回一个0到1的随机数
SELECT SIGN(-10); -- 判断一个数的符号

-- 字符串函数
SELECT CHAR_LENGTH('走在路上。。。'); -- 返回字符串长度，以字符为单位
SELECT LENGTH('走在路上。。。'); -- 返回字符串长度，以字节为单位
SELECT CONCAT('走','在','路','上'); -- 拼接字符串
SELECT INSERT('我在路上',1,2,'你好吗') ; -- 插入替换，从某个位置开始替换指定长度
SELECT LOWER('Hello'); -- 转小写
SELECT UPPER('hello'); -- 转大写
SELECT INSTR('helloworld','l'); -- 返回第一次出现子串的索引
SELECT REPLACE('hello','ll','ss'); -- 替换出现的指定字符串
SELECT SUBSTR('helloworld',2,5); -- 返回指定的子字符串(源字符串,截取的位置,截取的长度)

-- 时间和日期函数
SELECT CURDATE(); -- 获取当前日期
SELECT CURTIME(); -- 获取当前时间
SELECT NOW(); -- 获取当前日期和时间
SELECT SYSDATE(); -- 获取系统时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统
SELECT SYSTEM_USER(); -- 查询当前用户
SELECT USER(); -- 查询当前用户
SELECT VERSION(); -- 查询版本

聚合函数

-- 聚合函数
SELECT COUNT(`name`) FROM student; -- count(字段)，会忽略所有的null值，字段是主键时，效率最高
SELECT COUNT(*) FROM student; -- count(*)，不会忽略null值，本质是计算行数
SELECT COUNT(1) FROM student; -- count(1)，不会忽略null值，本质是计算行数

数据库级别的MD5加密

1. 什么是MD5?

事务

什么是事务

-- 事务

/*
mysql默认开启事务自动提交
set autocommit=0; -- 关闭
set autocommit=1; -- 开启（默认）
*/

-- 手动处理事务
SET autocommit=0; -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这之后的SQL都在同一个事务内

INSERT xx;
INSERT xx;

-- 提交 commit
COMMIT;

-- 回滚 rollback
ROLLBACK;

-- 事务结束
SET autocommit=1; -- 开启自动提交

-- 了解即可
SAVEPOINT 保存点 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点

模拟转账

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `account`(`name`,`money`) VALUES('A','2000'),('B','10000');

-- 模拟转账：事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET `money`=`money`-500 WHERE `name`='A'; -- A减500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B'; -- B加500

COMMIT; -- 提交
ROLLBACK; -- 回滚

SET autocommit=1; -- 开启自动提交

SELECT @@autocommit; -- 查询自动提交状态

索引

MySQL官方对索引的定义为： 索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

-- 显示所有索引
SHOW INDEX FROM `student`;

-- 索引的创建
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，alter增加索引
-- 3.创建完毕后，create增加索引

-- 索引命名：id_表名_字段名
-- 添加索引：create index 索引名 on 表名 (字段名);
CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec

-- 增加一个全文索引 索引名(列名)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName` (`studentName`);

-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM `student`; -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentName`) AGAINST('刘'); -- 全文索引

测试索引

插入100万数据

CREATE TABLE `user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`phone` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志

CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHILE i<num DO
		-- 插入语句
		INSERT INTO `user`(`name`,`phone`,`pwd`) VALUES(CONCAT('用户',i),CONCAT('18',FLOOR(RAND()*1000000000)),UUID());
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data();

SELECT COUNT(`id`) FROM `user`;

分析查询语句

SELECT * FROM `user` WHERE `name`='用户9999'; -- 0.748 sec
SELECT * FROM `user` WHERE `name`='用户9999'; -- 0.689 sec
SELECT * FROM `user` WHERE `name`='用户9999'; -- 0.765 sec

EXPLAIN SELECT * FROM `user` WHERE `name`='用户9999';

-- 索引命名：id_表名_字段名
-- 添加索引：create index 索引名 on 表名 (字段名);
CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec

SELECT * FROM `user` WHERE `name`='用户9999'; -- 0.052 sec

SHOW INDEX FROM `user`;

ALTER TABLE `user` DROP INDEX `id_user_name`;

结论

索引在小数据量的时候，用处不大，但是在大数据量的时候，区别十分明显。

索引原则

用户管理

1. 用户表：mysql.user
2. 用户管理本质是对用户表数据的增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER qing IDENTIFIED BY '123456';

-- 修改当前用户密码
SET PASSWORD=PASSWORD('123456');

-- 修改指定用户密码
SET PASSWORD FOR qing=PASSWORD('123456');

-- 重命名 RENAME USER 原用户名 TO 新用户名;
RENAME USER qing TO qing2;

-- 用户授权 GRANT ALL PRIVILEGES ON 库.表 TO 用户名;
-- ALL PRIVILEGES 除了给人授权外的其他权限
-- *.* 所有库和表
GRANT ALL PRIVILEGES ON *.* TO qing2;

-- 查询权限
SHOW GRANTS FOR qing2; -- 查看指定用户的权限
-- qing2权限 GRANT ALL PRIVILEGES ON *.* TO 'qing2'@'%'
SHOW GRANTS FOR root@'%';
-- root权限 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

-- 撤销权限 REVOKE ALL PRIVILEGES ON 库.表 FROM 用户名;
REVOKE ALL PRIVILEGES ON *.* FROM qing2;

-- 删除用户
DROP USER qing2;

MySQL备份

1. 为什么要备份？
   1. 保证重要的数据不丢失
   2. 数据转移
2. MySQL数据库备份的方式
   1. 直接拷贝物理文件data目录
   2. 使用可视化工具中的导出功能
   3. 使用命令行导出 mysqldump

# 导出表
# mysqldump -h IP -u 用户名 -p 密码 数据库 表名 >盘符/文件名
C:UsersAdministrator>mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 导出多个表
mysqldump -h192.168.10.226 -uroot -p123456 school grade >d:/a.sql

# 导出库
mysqldump -h192.168.10.226 -uroot -p123456 school >d:/a.sql

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

D:>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use school;
Database changed
mysql> source d:/a.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 或者 mysql -u用户名 -p密码 库名< 备份文件

规范数据库设计

为什么需要设计

第一范式（1NF）-原子性

第二范式（2NF）

第三范式（3NF）

规范性和性能的问题

关联查询的表不得超过三张表

数据库驱动

JDBC

1. SUN公司为了简化开发人员对数据库的统一操作，提供了一个Java操作数据库的规范，俗称JDBC。
2. 这些规范的实现由具体的厂商去做。
3. 对于开发人员来说，我们只需要掌握JDBC接口的操作即可。

相关jar包

1. 创建测试数据库。

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	`id` INT(10) NOT NULL,
	`name` VARCHAR(100) NOT NULL,
	`pwd` VARCHAR(100) NOT NULL,
	`email` VARCHAR(100),
	`birthday` DATE,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `users`(`id`,`name`,`pwd`,`email`,`birthday`) 
VALUES(1,'zhangsf','123456','zhangsf@163.com','1988-03-13'),
(2,'zhangwj','123456','zhangwj@163.com','1983-04-03'),
(3,'zhangyq','123456','zhangtj@163.com','1989-05-23');

2. 创建一个普通项目。

3. 导入数据库驱动。
   1. 创建lib目录，拷贝mysql-connector-java-5.1.49.jar
   2. 添加到项目库，右键点击Add as Library

4. 编写测试代码。

package com.qing.demo01;

import java.sql.*;

/**
 * 第一个JDBC程序
 */
public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.用户信息和url
        //useUnicode=true 支持中文编码
        //characterEncoding=utf8 设定中文字符集编码为utf8
        //useSSL=true 使用安全的连接，如果服务器没有SSL证书会报错，使用false
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String user = "root";
        String password = "123456";

        //3.连接成功，获取数据库对象Connection
        Connection connection = DriverManager.getConnection(url,user,password);

        //4.创建执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL，如果有返回结果，查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("pwd=" + resultSet.getObject("pwd"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("=====================================");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结

//驱动管理
//DriverManager.registerDriver(new Driver());
//加载驱动，建议使用，Driver类中的静态代码块就是执行的上面的方法
Class.forName("com.mysql.jdbc.Driver");

//连接数据库
Connection connection = DriverManager.getConnection(url,user,password);
//Connection代表数据库对象，可以执行数据库层面的操作
//数据库设置自动提交
connection.setAutoCommit(false);
connection.setAutoCommit(true);
//事务提交
connection.commit();
//事务回滚
connection.rollback();

URL

//数据库连接信息
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//String url = "jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3";

Statement

//执行SQL的对象
boolean execute = statement.execute("");//执行任何SQL，但需要判断，效率最低
ResultSet resultSet = statement.executeQuery();//执行查询，返回查询结果集
int i = statement.executeUpdate();//执行更新、插入、删除，返回受影响的行数

释放资源

resultSet.close();
statement.close();
connection.close();//耗资源，用完关掉

初次优化，提取工具类

1. 创建db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=123456

2. 提取工具类JdbcUtils：加载驱动，获取连接，释放连接资源
3. 测试增删改查

package com.qing.demo02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类
 */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String user = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            //1.驱动只需要加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试插入
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = JdbcUtils.getConnection();
            //获取执行SQL的对象
            st = conn.createStatement();
            //执行SQL
            String sql = "insert into `users`(`id`,`name`,`pwd`,`email`,`birthday`) values(4,'zhangyq','123456','zhangyq2163.com','1988-09-09')";
            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放连接资源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试更新
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = JdbcUtils.getConnection();
            //获取执行SQL的对象
            st = conn.createStatement();
            //执行SQL
            String sql = "update `users` set `name`='zhangtj' where `id`=3";
            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放连接资源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试删除
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = JdbcUtils.getConnection();
            //获取执行SQL的对象
            st = conn.createStatement();
            //执行SQL
            String sql = "delete from `users` where `id`=4";
            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放连接资源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试查询
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = JdbcUtils.getConnection();
            //获取执行SQL的对象
            st = conn.createStatement();
            //执行SQL
            String sql = "select * from `users` where `id`=3";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放连接资源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入问题

//执行SQL
String id = "3 or 1=1";
String sql = "select * from `users` where `id`=" + id;
rs = st.executeQuery(sql);

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

--当输入了上面的用户名和密码，上面的SQL语句变成：
SELECT * FROM user_table WHERE username=
'’or 1 = 1 -- and password='’

"""
--分析SQL语句：
--条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

--然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都--能正确执行，用户轻易骗过系统，获取合法身份。
--这还是比较温柔的，如果是执行
SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
--其后果可想而知…
"""

PreparedStatement

因为sql语句是预编译的，而且语句中使用了占位符，规定了sql语句的结构。用户可以设置"?"的值，但是不能改变sql语句的结构，因此想在sql语句后面加上如“or 1=1”实现sql注入是行不通的。
实际开发中，一般采用PreparedStatement访问数据库，它不仅能防止sql注入，还是预编译的(不用改变一次参数就要重新编译整个sql语句，效率高)，此外，它执行查询语句得到的结果集是离线的，连接关闭后，仍然可以访问结果集。

package com.qing.demo03;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.*;

/**
 * 测试查询
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = JdbcUtils.getConnection();
            //使用?占位符代替参数
            String sql = "select * from `users` where `id`=?";
            //预编译，先写SQL，然后不执行
            st = conn.prepareStatement(sql);
            //手动给参数赋值
            st.setString(1,"3");
            //执行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放连接资源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

使用IDEA连接数据库

1. 连接mysql；

2. 选择mysql驱动；

3. 填写mysql用户名，密码，测试连接；

4. 选择数据库；

5. 双击数据表，打开表；修改数据，提交；打开命令行，写SQL；

JDBC操作事务

1. 开启事务：关闭数据库自动提交，自动会开启事务。
2. 一组业务执行完毕，提交事务。
3. 可以在catch语句中显式的定义回滚语句，也可以不写，默认失败就会回滚。
4. finally中开启数据库自动提交，然后释放连接资源。

package com.qing.demo04;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 测试事务
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //开启事务：关闭数据库自动提交，会自动开启事务
            conn.setAutoCommit(false);

            String sql1 = "update `account` set `money`=`money`-100 where `name`='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            String sql2 = "update `account` set `money`=`money`+100 where `name`='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //提交事务
            conn.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            //显式回滚，也可以不写，默认失败自动回滚
//            try {
//                conn.rollback();
//            } catch (SQLException e1) {
//                e1.printStackTrace();
//            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

数据库连接池

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！

1. DBCP
   1. jar包：commons-dbcp，commons-pool
   2. 配置文件：dbcpconfig.properties
   3. 工厂模式创建数据源：DataSource dataSource = BasicDataSourceFactory.creatDataSource(properties);
   4. 从数据源获取连接：dataSource.getConnection();
2. C3P0
   1. jar包：c3p0，mchange-commons-java
   2. 配置文件：c3p0-config.xml
   3. 创建数据源：DataSource dataSource = new ComboPooledDataSource("MySQL");//不写参数就是默认数据源，写参数就是指定数据源
   4. 从数据源获取连接：dataSource.getConnection();
3. Druid：阿里巴巴