狂神说——MySQL最新教程通俗易懂

参考资料

初始MySQL

前端：页面、展示、数据
后端：连接点：JDBC、链接前端（控制、控制视图跳转、给前端传送数据）
数据库：存数据

只会写代码，学好数据库，基本混饭吃
操作系统、数据结构与算法！当一个不错的程序员
离散数学、数字电路、体系结构、编译原理+实战经验。高级程序员

为什么学习数据库？

1. 岗位需求
2. 现在世界，大数据时代，得数据库者的天下
3. 被迫需求：存数据
4. 数据库是所有软件体系中最核心的存在 DBA

什么是数据库？

数据库 DB database
概念：数据库仓库，安装在系统中的软件（Windows Linux mac）
作用：存储数据、管理数据

数据库分类？

关系型数据库：SQL
MYSQL ，Oracle ，Sql Server，DB2 , SQLite
通过表和表之间，行和列之间的关系进行数据的存储。

非关系型数据库：NoSQL(not only sql)
Redis MongDB
非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS数据库管理系统
数据库管理软件，科学有效的管理我们的数据。维护或获取数据。

MYSQL简介

MYSQL是一个关系型数据库，现在属于Oracle公司

安装MySQL

尽量不使用exe，使用zip压缩包

本地：用户名 root 密码 123456

Sqlyog安装使用

安装可视化管理软件，Sqlyog

下载地址
报错处理
更高版本下载地址

基本的命令行操作

# 命令行登录 

# windows找到对应路径，执行命令 注意要./mysql
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> ./mysql -uroot -p

# 输入密码 进入mysql命令行操作

#查看所有数据库 命令
show databases;

#切换数据库 命令
use kuangshen; # use 数据库名

#查看数据库对应的所有表
show tables;

#显示表中的信息
describe student;

#新建数据库
create database test;

# 退出
exit;

# 注释
--单行注释 （SQL本来的注释）
/* */ 多行注释

CRUD程序员 CV程序员 API程序员
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言

操作数据库语句 （了解）

mysql关键字不区分大小写

1. 创建数据库

CREATE DATABASE  [IF NOT EXISTS]  test

** 2. 删除数据库**

DROP DATABASE [IF EXISTS] test

3. 使用数据库
如果表名或者字段名是特殊字符，用``

USE  kuangshen 
USE  `user`

4. 查看数据库

SHOW DATABASES

数据库列的数据类型讲解

数值

类型	说明	字节
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小数据	3个字节
int	标准的整数	4个字节 最常用
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数	金融计算的时候常用

字符串

类型	说明	字节
char	字符串固定大小的	0-255
varchar	可变字符串	0-65535 最常用
tinytext	微型文本	2^8 -1
text	文本串	2^16-1 保存大文本

日期时间

类型	说明	字节
date	YYYY-MM-DD	日期格式
time	HH:mm:ss	时间格式
datetime	YYYY-MM-DD HH:mm:ss	最常用的时间格式
timestamp	时间戳	1970.1.1 到现在的毫秒数 也较常用
year	年份

NULL

没有值、未知
注意：不要用NULL进行运算，结果还是NULL

数据库的字段属性 (重点)

Unsigned 无符号整数

声明了该列不能为负数

ZeroFill 是否0填充

不足的位数，使用0来填充 int（3） 5-----005

Auto Inner自增加

自动在上一条记录的基础+1（默认）
通常用来设置唯一的主键key的index，必须是整数类型
可以自定义设计主键的起始值和步长

Not Null 非空

假设设置not null，如果不给它赋值，就会报错！
NULL，如果不填写值，默认就是null！

Default 默认

设置默认的值

Comment 注释

给字段加注释

扩展

创建数据库表（重点）

CREATE TABLE IF NOT EXISTS student(
`id`  INT(4) NOT NULL AUTO_INCREMENT  COMMENT '序号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)

MyISAM和InnoDB区别

# 物理空间存在的位置
mysql 命令行输入
show variables like 'datadir';

# mysql配置文件
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

修改和删除数据表字段

# 表新增字段 ALTER TABLE 表名 ADD 新字段 列属性
ALTER TABLE test ADD age INT(10)

# 表修改字段类型 ALTER TABLE 表名 MODIFY 字段 列属性
ALTER TABLE test MODIFY age INT(1)

# 表修改字段名 ALTER TABLE 表名 CHANGE 老字段名 新字段名 列属性
ALTER TABLE test CHANGE age age1 INT(10)

# 表删除字段名 ALTER TABLE 表名 DROP 字段名
ALTER TABLE test DROP age1

数据库级别的外键

方法一：创建的时候新建 不常用

CREATE TABLE IF NOT EXISTS `grade`(
`grade_id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT '姓名',
PRIMARY KEY (`grade_id`)
)


CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`age` INT(10) NOT NULL COMMENT '年龄',
`gradeid` INT(4)  NOT NULL COMMENT '年级id',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`grade_id`)
)

不推荐外键，删除表的时候，需要先删除对应的外键表才可以删掉。

Insert语句详解

DML语言

1. insert
2. update
3. delete

# 同时插入多个值 values (),(),......
INSERT INTO `grade` (`gradename`) VALUES('大一'),('大二'),('大三')

# 语法
INSERT INTO 表名 (字段名,字段名) VALUES(值,值,值)

Update语句详解

Delete和Truncate详解

基本的Select语句和别名使用(超级重点)

# 语法
SELECT 列名1,列名2...... FROM student
# 查询表中所有列数据
SELECT * FROM student
# 查询表中指定列数据
SELECT `name` FROM student
# 查询表中指定列数据 并 设置别名
SELECT `name` AS 姓名 FROM student
# 查询表中指定列数据 并 使用函数concat连接字符串
SELECT CONCAT('姓名：', `name`) AS  新姓名 FROM student

去重及数据库的表达式

# 查询列
SELECT `name` FROM student
# 查询列 并 去重 DISTINCT
SELECT DISTINCT `name` FROM student

-- 查询数据库版本
SELECT VERSION()

-- 用来计算
SELECT 100-3*4 AS 计算结果

-- 查询自增步长
SELECT @@auto_increment_increment

-- 数据库中的表达式：文本值、列、Null、函数，计算表达式、系统变量。。。
select 表达式 from 表

where之逻辑运算符

模糊查询操作符详解

-- like结合 %任意字符 _一个字符
SELECT `name` FROM student
WHERE `name` LIKE '刘_'

SELECT `name` FROM student
WHERE `name` LIKE '刘__'

SELECT `name` FROM student
WHERE `name` LIKE '刘%'

-- in 具体一个或者多个值
SELECT * FROM student
WHERE age IN (13,18)

-- not null, null
SELECT * FROM student
WHERE address IS NOT NULL

SELECT * FROM student
WHERE birthday IS  NULL

联表查询JoinON详解

自连接及联表查询练习

SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目' FROM category AS a,category AS b
WHERE a.`categoryid` = b.`pid`

分页limit 和排序 order by

ASC 升序
DESC 降序

子查询和嵌套查询

where查询语句中值不是固定，值是计算出来的
本质：在where语句中嵌套一个语句
where(select * from)

MySQL常用函数

官网地址

-- 日期时间函数--
SELECT  CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT  CURRENT_TIME() -- 获取当前时间
SELECT NOW() -- 获取当前日期时间
SELECT LOCALTIME()  -- 获取本地日期时间
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() -- 查询数据库版本

聚合函数（常用）及分组过滤

拓展之数据库级别的MD5加密

-- test md5 --
CREATE TABLE testMD5(
id INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
pwd VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)

-- 明文密码
INSERT INTO testmd5 VALUES(2,'wangwu','234567'),(3,'lisi','345678')

-- 更新密码
-- 加密全部密码
UPDATE testmd5 SET pwd = MD5(PWD) 

-- 插入的时候就直接加密
INSERT INTO testmd5 VALUES(4,'yiyi',MD5('234567'))
INSERT INTO testmd5 VALUES(5,'erer',MD5('123456'))

-- 如何校验 将用户传递过来的密码md5加密 然后比对
SELECT * FROM testmd5 WHERE pwd = MD5('234567')

Select小结

事务ACID原则、脏读、不可重读和幻读

参考资料

事务原则：ACID原则 原子性、一致性、隔离性、持久性 

隔离所导致的问题：

测试事务实现转账

-- 新建表
CREATE TABLE account(
id INT(3) NOT NULL,
`name` VARCHAR(30) NOT NULL,
money DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)

-- 插入数据
INSERT INTO account VALUES(1,'A',2000.0),(2,'B',1000.0)

-- 新建事务 转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION ;-- 开启事务

UPDATE account SET money=money-500 WHERE `name` = 'A';
UPDATE account SET money=money+500 WHERE `name` = 'B';

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

SET autocommit =1; -- 恢复默认值

索引介绍及索引的分类

SQL编程创建100万条数据测试索引

-- 创建数据库
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入百万数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 100000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num  DO
		-- 插入语句
		INSERT INTO `app_user` (`name`,`email`,`phone`,`gender`,`password`,`age`) VALUE(CONCAT('用户',i),'843114@qq.com',CONCAT('18',FLOOR(RAND()*(100000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)) ;
		SET i = i+1;
	END WHILE;
	RETURN i;
END;
-- 执行函数
SELECT mock_data();

查询时间比对：
未新增索引

新增索引

-- 创建索引 id_表名_字段名
CREATE INDEX id_app_user_name ON  app_user(`name`)

索引原则和明日安排

阅读文章

数据库用户管理

SQLyog可视图操作：

SQL命令操作：

MySQL备份

MYSQL 数据文件路径  C:\ProgramData\MySQL\MySQL Server 8.0\Data
通过服务，查找到对应的配置文件路径。

SQLyog备份数据库：

mysql命令行备份数据库：
首先要配置环境变量
导出数据、数据库：
mysqldump -hlocalhost -uroot -ptaohongyu kuangshen student >D:/a.sql

mysql命令还原数据库：
首先要登录，然后用source命令导入数据库：
C:\Users\Administrator>mysql -uroot -p
mysql> use kuangshen;
Database changed
mysql> source d:/a.sql;

如何设计一个项目的数据库

数据库的三大范式

第一范式 1NF
原子性：保证每一列都不可再分

第二范式 2NF
前提满足第一范式
每张表只描述一件事情

第三范式 3NF
前提满足第一、二范式
每一列都跟主键直接相关，不能间接相关

规范和性能问题：
关联表不能超过三张。

数据库驱动和JDBC

第一个JDBC程序

JDBC中对象解释

Statement对象详解

SQL注入问题

PreparedStatement对象

使用IDEA连接数据库

JDBC操作事务

DBCP-C3PO连接池