MySQL笔记

adair-zhang

已于 2023-02-15 21:59:56 修改

阅读量1.3k

点赞数

文章标签： mysql 数据库 database

于 2022-03-13 22:55:54 首次发布

本文链接：https://blog.csdn.net/weixin_44042813/article/details/123468446

版权

文章目录

1、初识MySQL

引用

JavaEE：企业级开发Web：
- 前端：展示数据
- 后台：连接点JDBC(Java Database Connectivity)：MySQL需要mysql-connector-java-8.0.28.jar
- 数据库DB：存数据。DBMS管理数据。
去IOE：它是阿里巴巴造出的概念。其本意是，在阿里巴巴的IT架构中，去掉IBM的小型机、Oracle数据库、EMC存储设备。

DBMS种类

DBMS种类
- RDBMS：MySQL、Oracle数据库、SQL Server（ms）、DB2、SQLlite
- NoSQL(not only sql)：对象存储（利用键值对）Redis、MongoDB
- 层次数据库（Hierarchical Database, HDB）
- 面向对象数据库（Object Oriented Database，OODB）
- XML数据库（XML Database，XMLDB）
外部 → DBMS → DB。mysql就是关系型DBMS

MySQL简介

MySQL是一个（RDBMS），小快，属于 Oracle 旗下产品。不是将所有数据放在一个大仓库内。提高了灵活性和速度，中小型网站。大型的需要集群

安装MySQL

建议使用压缩包安装，不要使用.exe（卸载要清理组册表）

解压，并放到自己的相应的位置
**环境变量path：**D:\mysql-8.0.28\bin
在D:\mysql-8.0.28D:\mysql-8.0.28中添加my.ini配置文件

[mysqld]
basedir=D:\Environment\mysql-8.0.28\
datadir=D:\Environment\mysql-8.0.28\data\
port=3306
skip-grant-tables

mysqld是mysql的守护进程

管理员命令cmd安装：

cd /d D:\Environment\mysql-8.0.28\bin
mysqld -install

mysqld --initialize-insecure --user=mysql

启动服务

net start mysql
#反过来
net stop mysql

进入mysql

 update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
 
 flush privileges; //刷新权限
 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

在my.ini最后一句#skip -grant -tables跳过密码
重新启动mysql

exit

net stop mysql
net start mysql		//启动服务，但是没有进入mysql

mysql -uroot -p123456  //进入mysql

sc delete mysql //清除mysql，卸载的时候,servername command

总结：解压+环境变量+ini配置 → 安装+初始化 → 启动并进入mysql → 修改密码，刷新权限 → 重启并进入mysql。

注释：-- comment、/*comment*/

SQLyog可视化

安装SQLyog
输入user password即可连接

cmd操作DB

show databases;

use school； --切换数据库

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

desc student; --可以查看数据库中表的内容=descirbe student

create database westos; --创建数据库

exit; --退出

–（注释）

/*

多行注释

*/

sql语言分类

DDL：create\alter\drop
DQL：select
DML：insert\updata\delete
DCL：grant\revoke
事务控制语言（TCL）：commit\rollback
CRUD程序员，cv程序员，api程序员。CRUD是4个单词的首字母，CRUD分别指增加（Create）、读取查询（Read/Retrieve）、更新（Update）和删除（Delete）

2、操作数据库

基本

数据库 -> 表 -> 表的数据

sql 代码大小写不敏感(ci)

增加数据库：CREATE DATABASE IF NOT EXISTS westos（严谨）
删除数据库： DROP DATABASE IF EXISTS hello
查看所有数据库：show databases;
切换数据库：use school；
如果自定义的符号是关键字，需要用backquote引用

表中列的数据类型

数值的类型
- int 4字节、tinyint 1B、smallint 2B、mediumint 3B、bigint8B
- decimal 字符串类型的浮点数，用于金融(double和float的精度有问题)
字符串
- char 定长0~255(地址8bit)
- varchar 变长0~65535 (地址16bit)
时间的类型（java.util.date）
- date YYYY-MM-DD
- time HH-mm-ss
- datetime(最常用)
- timestamp：时间戳1970年

数据库字段属性

自增：唯一，理解成index

拓展：

id	主键
`version`   乐观锁
is_delete	伪删除
gmt-CREATE    创建时间
gmt-UPDATE    更新时间

GMT=UTC+0:00

CST=UTC+8:00

创建数据库表

student创建表是小括号

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(50)DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)			--主键，只有一个
)ENGINE=INNODB DEFAULT CHARSET=utf8

#格式,创建table用()
#利用可视化等效代码

反向操作

SHOW CREATE DATABASE school	--查看创建数据的代码：CREATE DATABASE `school` /*!40100 DEFAULT 							CHARACTER SET utf8 */
SHOW CREATE TABLE student	--查看数据表的代码
DESC student			--显示表的结构

引擎

引擎	MYISAM	INNODB
事务处理!!	不支持	支持
外键约束	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	不支持(最新版的支持)
表的空间大小	小	2*MYISAM

MYISAM：小快
INNODB：安全，支持事务处理，多用户操作表
cmd操作mysql -uroot -p123

show variables like 'basedir';
show variables like 'datadir';		#都存在于环境，sql\data目录下

select 函数名();

INNODB建立表*.frm文件和*.ibd文件。数据库本质是文件夹
MYISAM：.frm是表结构文件。.MYD数据文件.MYI索引文件index

CREATE TABLE `teacher` (
  `name` VARCHAR(11) NOT NULL COMMENT '名字',
  PRIMARY KEY (`name`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

alter table t_name+操作

更改表的字段
rename as \ add \ modify \ change \ drop

#范式
alter table students + 操作

ALTER TABLE teacher RENAME AS teacher1		-- 修改表名

ALTER TABLE teacher1 ADD age INT(11)		-- 增加字段属性

ALTER TABLE teacher1 MODIFY age VARCHAR(10)	-- 修改字段约束

ALTER TABLE teacher1 CHANGE age score INT(2)	-- 字段重命名

ALTER TABLE teacher1 DROP age1			-- 删除字段

3、MYSQL的数据管理

外键

外键的引入

CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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(50)DEFAULT NULL COMMENT '邮箱',
    `gradeid` INT(11) NOT NULL COMMENT '年级',
    PRIMARY KEY(`id`),		
    KEY `KF_gradeid` (`gradeid`),
    CONSTRAINT `KF_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)  
)ENGINE=INNODB DEFAULT CHARSET=utf8

外键引入

-- 创建表的时候没有外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

删除表：先删除student(从表，)，再删除grade（主表，grade主表被引用了），反之报错
以上都是物理外键，数据库级别的外键，不建议使用（如果数据库中表引用太多，不易删除）

数据库就是单纯的表，只有行（数据）和列（字段）

DML语言

insert/update/delete是对数据操作 update和delete配合where使用

insert：：insert to …… values……
update
delete
总结

manage + `table` + 其他

insert into t_name() values ()

INSERT INTO `grade`(`gradename`) VALUES ('大三')

INSERT INTO `grade`(`gradename`) 
VALUES ('大二'),('大一')


INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('张四','123456','男'),('张五','123456','男')

INSERT INTO `student`		-- 不写字段，要全部写值
VALUES (7,'adair','456','女','2000-01-01','焦作','email')

-- 总结：insert into t_name(`name`,`pwd`,`sex`)  values (值a1,值2),(值b1,值b2)

update t_name set

UPDATE `student` SET `name`='adair1',`email`='123@qq.com' WHERE id=6	-- 指定
UPDATE `student` SET `name`='adair1' WHERE id between 2 and 6	-- 指定

UPDATE `student` SET `name`='adair'		-- 不指定，会改变所有的表

UPDATE `student` SET `name`='adair1',`email`='123@qq.com' WHERE id<6 AND `name`='adair'

UPDATE `student` SET `birthday`=SYSDATE() WHERE id=2 -- birthday变为系统时间

-- set `birthday`=SYSDATE()
-- set `birthday`=current_Time

#总结
update t_name set 字段=值1,字段=值2 where ……

delete from、truncate

truncate(截断)

DELETE FROM `student`		-- 全删

DELETE FROM `student` WHERE id=1
-- 总结：delete from 表名 where……

TRUNCATE `student` -- 清空表的数据

-- delete自增不结束，truncate自增从1再次开始

delete删除后，重启数据库,

Innodb 自增列会从1开始（存于内存中）
MyISAM 自增从上一个继续增（存于文件中）

drop truncate delete

drop删除表的结构和内容

truncate只删除表的内容，属于DDL，使用后，重新从1开始，省空间而且快。自动commit，不可rollback

delete只删除表的内容，属于DML，使用后，从所在行继续。可以rollback。而且可可以指定删除行（truncate只能）

4、DQL

数据库中最重要的语句

查询select

select * from student  -- 查询student表所有的信息

-- 别名，给结果取名字, as
SELECT `studentno` AS 学号,`studentname` AS 名字 FROM student AS s

-- 拼接字符串 concat(a,b)
SELECT CONCAT('名字：',studentname)  AS '拼接的名字' FROM student

distinct(不同的)、版本、计算、自增步长

-- 去重distinct
SELECT DISTINCT `studentno` FROM result

-- 版本号
SELECT VERSION()

SELECT 7*8 AS 计算结果

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

-- 全员+1分
SELECT `studentno`,`studentresult`+1 AS '提高后成绩' FROM result

字段就要加backquote，数据要用'，要不可能会报错

模糊查询like

where关键字：is null、null、between、like、in、

-- 查询姓张的。%表示任意字符串，也可是空字符串
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%张%'

-- 查询姓张，并且姓后只有一个字。一个下划线表示一个字
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_'

-- 查询编号是1000,1001,1002的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001,1002)

-- 查询地址是……
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('北京朝阳','河南焦作')

-- 查询地址为空,即 null/''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IS NULL OR `address`=''

-- 查询地址非空
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IS NOT NULL

联表查询

一共七种联表查询（2个表）

需求分析：
1. 查询什么数据
2. 从哪几个表中查询
3. 假设要查询n个表，先查2个然后逐渐增加
inner join：公共数据

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
ON r.studentno=s.studentno

right join：以右表为基准

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno

left join：

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON r.studentno=s.studentno

没有考试的同学

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON r.studentno=s.studentno
WHERE studentresult IS NULL		#不是studentresult=NULL

查询参加考试的同学，查询三张表

SELECT s.`studentno`,s.`studentname`,sub.`subjectname`,r.`studentresult`
FROM student AS s
INNER JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`

-- 同上，没有歧义的不用写s.,as可以省略
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`

查询父子信息：把一张表看成两张一样的表

SELECT a.`categoryName` AS 父 ,b.`categoryName` AS 子
FROM `category` AS a
INNER JOIN `category` AS b
ON a.`categoryid`=b.`pid`

查询学生id，name所属年级

SELECT `studentno`,`studentname`,`gradename`
FROM student s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`

查询科目所属的年级

SELECT `gradename`,`subjectname`
FROM `grade` g
INNER JOIN `subject` s
ON g.`gradeid`=s.`gradeid`

参加高等数学-1 考试同学，学号，姓名，科目名，分数

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'

条件where

SELECT `studentno`,`studentresult` FROM result

SELECT `studentno`,`studentresult` FROM result 
WHERE studentresult>=70 AND studentresult<90	-- &&也可以

SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 70 AND 90
-- 取非
WHERE studentno!=1000

WHERE NOT studentno=1000

-- 查询姓刘的
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '张%'

WHERE studentname LIKE '赵_'

WHERE studentname LIKE '_强'

WHERE studentname LIKE '%庆%'

-- in 是具体的一个或多个
-- 查询1000,1001,1002
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1000,1001,1002)

-- 查询北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE address IN ('北京朝阳','广东深圳')

null/not null

-- null和not null
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL

WHERE borndate IS  NULL

WHERE borndate IS NOT NULL

group by、having

SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
GROUP BY sub.`subjectno`
HAVING 平均分<80

order by、limit

排序：order by column1 desc/asc

分页：limit 起，页大小

SELECT *  FROM `result`
ORDER BY `studentresult` ASC
LIMIT 0,3	-- 从1条展示，每页三条

# limit n,m  -- 从第n条展示，每页m条，tip:n最好是m的整倍数

子查询

-- 方式一
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `studentresult`>=0 AND `subjectname`='高等数学-1'

-- 方式二
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=(SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1')

-- 方式三
SELECT `studentno`,`studentname` FROM `student` s WHERE `studentno` IN (
   SELECT `studentno` FROM `result` WHERE `studentresult`>= 80 AND `subjectno`=(
   	SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1'
)
)

总结

SELECT s.`studentno`,`subjectno`
FROM table_name1 AS tn1
LEFT JOIN table_name2 AS tn2
ON.....  -- 2张表的共同项
WHERE ……
GROUP BY ……		-- 分组的依据字段，eg:avg(),max(),sum()
HAVING ……		-- 分组的条件设置:组的条件
ORDER BY ……
LIMIT ……

# 
select ... 
from table1 as t1
inner join table2 as t2
on t1.字段=t2.字段
where 条件

5、MySQL函数:select

函数很多，没必要背，知道英文和大致用法即可

常用函数

数学

SELECT ABS(-8)

SELECT CEILING(9.1)

SELECT FLOOR(9.2)

SELECT RAND()	-- 返回0~1随机

SELECT SIGN(-10)  -- 返回实数的符号0/-1/1

字符串

SELECT CHAR_LENGTH('你去啊')  #3
SELECT LENGTH('你去啊')     #9

SELECT CONCAT('我','爱','电脑')

SELECT INSERT('12345',2,1,'abcd')  -- 第二位插入，代替一个字符

SELECT LOWER('dsfLIKFHJIOL')
SELECT UPPER('dsfLIKFHJIOL')

SELECT INSTR('adair','da')	-- 查询子串位置

SELECT REPLACE('adair','da','sss') -- 代替旧字符串

SELECT SUBSTR('123456',2,3)	-- 截取字符串234

SELECT REVERSE('123456')	

SELECT REPLACE(studentname,'张','蛋') FROM student		#把张改为蛋
WHERE `studentname` LIKE '张%'

时间，

SELECT CURRENT_DATE()
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()

聚合函数

count()、max()、min()、sum()、avg()

SELECT COUNT(studentname) FROM student	-- count(字段),忽略null

SELECT COUNT(*) FROM student 	-- 行数（包括null）,计算所有列

SELECT COUNT(1) FROM student	-- 行数（包括null），忽略所有列


SELECT SUM(`studentresult`) AS 总和,MIN(`studentresult`) AS 最小,
	MAX(`studentresult`) AS 最大,AVG(`studentresult`) AS 平均 FROM result

SELECT MIN(`studentresult`) AS 最小 FROM result
SELECT MAX(`studentresult`) AS 最大 FROM result
SELECT AVG(`studentresult`) AS 平均 FROM result

#上面写的有
SELECT `subjectname`,AVG(`studentresult`) AS 平均分
FROM result r
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
GROUP BY sub.`subjectno`	-- 通过什么字段分组
HAVING 平均分>80		      -- 分组必须满足的条件，不可用where判定组的条件

md5函数

md (message digest信息摘要) 5是版本。
不可逆。md5破解，是有一个字典
暴力破解

-- 明文插入
INSERT INTO testmd5 VALUES (1,'adair','213456'),(2,'adair2','213456'),(3,'adair3','213456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd)

-- 直接插入密文
INSERT INTO testmd5 VALUES (4,'xiaoming',MD5('123456'))
SELECT MD5('123456') FROM testmd5

6、Transaction:ACID

概念

事务transaction要么都成功，要么都失败
ACID基本要素：原子，(最终)一致，隔离，持久
- 原子atom：要么都成功，要么都失败
- 一致consist：前后要一致，eg：转账后的总价一致
- 隔离isolate：多个用户共同操作数据库
- 持久during：一旦提交不可逆，被持久化。没有提交成功(eg宕机/停电)就恢复到原状，成功就成功了
**隔离**导致的问题：
- 脏读（读取未提交的数据），
- 不可重复读（场合不一致）：读取前和读取后的数据不一致
- 幻读：多读一行（有可能时刚插入的，但是却读取了）

转账事务

-- 转账
CREATE DATABASE shop CHAR SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL ,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

-- 转账
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;		-- 开启自动提交

transaction类比java

try(){事务,commit()}
catch(){rollback()}
final{}

保存点

SAVEPOINT 保存点	-- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点	-- 回滚到保存点

RELEASE SAVEPOINT 保存点	-- 撤销保存点

7、Index

索引的分类

主键primary key：唯一，不可重复。
唯一unique key：唯一索引可以重复，多个列都可以标识为唯一索引，例如身份证ID
常规key/index：
全文fulltext：在特定数据库才有，快速定位数据

-- 展示表中索引
SHOW INDEX FROM student

-- 增加索引
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`)

EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('赵') -- 全文

测试索引

插入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 '年龄',

`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

写一个函数

DELIMITER $$		-- 写函数的规定
CREATE FUNCTION mock_data3()	-- 模拟数据
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
	INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES (CONCAT('户',i),'123@qq.com',
	CONCAT('18',100000000+FLOOR(RAND()*(999999999-100000000))),
	FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)
	);
	SET i=i+1;
  END WHILE;
  RETURN i; 
END;

SELECT mock_data3();

#如果函数建立不成功，log_bin_trust_function_creators设置为1
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1;

测试有无name索引的速度

SELECT * FROM `app_user` WHERE `name`='户9999' -- 执行耗时s级
EXPLAIN SELECT * FROM `app_user` WHERE `name`='户9999' -- 查看执行语句的消耗

CREATE INDEX id_app_user_name ON `app_user`(`name`)	-- 建立索引
-- ALTER TABLE `app_user` ADD KEY (`name`); -- 同上

SELECT * FROM `app_user` WHERE `name`='户9999'-- 执行耗时ms级
EXPLAIN SELECT * FROM `app_user` WHERE `name`='户9999' -- 查看执行语句的消耗

索引原则

数据多采用索引。
经常变动的数据不用索引，因为还要刷新索引
索引一般用于经常查询的字段上

索引的数据结构

Hash类型的索引

BTree：innodb默认的数据结构

8、权限管理和备份

用户管理

SQL yog 可视化：用户管理

如何yog中如何创建一个用户
再利用这个用户进行连接数据库

在mysql数据库中的table中有use可以查看用户的权限

用户的管理

-- 创建用户
CREATE USER adair IDENTIFIED BY '123'

-- 修改当前密码
SET PASSWORD = PASSWORD('123')
-- 修改指定用户密码
SET PASSWORD FOR adair=PASSWORD('123')

-- 重名用户
RENAME USER adair TO adair2

-- 授权,除了给别人授权其他均可做 权限+库.表+用户
GRANT ALL PRIVILEGES ON *.* TO adair2

-- 查询权限
SHOW GRANTS FOR adair2 -- GRANT ALL PRIVILEGES ON *.* TO 'adair2'@'%'。 @%代表对主机
SHOW GRANTS FOR root@localhost -- 比上边多GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM adair2

-- 删除用户
DROP USER adair2

备份

备份的方式

直接拷贝物理磁盘：data文件
在SQLyog可视化中直接主动导出：直接鼠标右键导出
使用命令行导出mysqldump：

#导出
-- 导出一张表
mysqldump -hlocalhost -uroot -p123 school student >D:\a.sql
-- 导出多张表
mysqldump -hlocalhost -uroot -p123 school student result subject >D:\b.sql
-- 导出数据库
mysqldump -hlocalhost -uroot -p123 school >D:\b.sql

#导入 , 
mysql -hlocalhost -uroot -p123 --在登陆的情况下，进入数据库
use school  -- 切换数据库
-- 先SQLyog删除student
source D:/a.sql	 -- 再次导入student，

规范DB设计

软件开发，数据库设计

过程：

分析需求：分析业务和需要处理的数据库需求
概念设计：设计E-R图

设计数据库步骤

收集信息，分析需求
- 用户表user：id、user_name、password、sex、age、sign
- 分类表category：id、category_name、create_user_id
- 文章表blog：id、title、author_id、content、create_time、update_time、love
- 评论表comment：id、blog_id、user_id、content、create_time、update_time
- 友链表links：id、links、href、sort
- 自定义表(eg：follow)：id、user_id、follow_id
- 说说表：user_id、create_time、content
标识实体（把需求落地到每个字段）
实体标识之间的关系，创建关系
- 写博客：user --> blog --> comment
- 创建分类：user --> category
- 关注：user --> user
- 头像/友链：links

三大范式

第一范式：原子性（每列不可再分）。反例：一列：硕士+研一
第二范式：每张表只描述一件事情。表中的每一列都与主键（或联合主键）有关，要不然拆表。反例：一个列只和一个主键有关，
第三范式：每一列数据都和主键直接相连（非间接）。反例：学生+老师的信息都在一张表

规范和性能权衡

规范越多，拆的表也越多
在规范的时候，要考虑到性能。
有时候故意冗余（防止太多表查询）

JDBC

sun公司提供统一JDBC，解决下层不同驱动（MySQL和Oracle的驱动不同）的问题
没有什么是加一层不能解决的

第一个JDBC

准备步骤

创建一个项目
导入数据库驱动mysql-connector-java-5.1.47.jar
- 建立一个lib包
- 导入mysql-connector-java-5.1.47.jar
- add as library……

package com.adair.lesson01;

import java.sql.*;

//
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123";


        //3.连接Connection数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.得到SQL对象  statement执行SQL对象
        Statement statement = connection.createStatement();


        //5.用SQL对象statement  去   执行SQL
        String sql="SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//返回结果集
        while(resultSet.next()){
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }

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

connection

connection.commit();
connection.rollback();
connection.setAutoCommit();

statement对象，通过statement对象对数据库进行增删改查

String sql="SELECT * FROM users";

statement.executeQuery(sql);//查询，返回字符集
statement.execute(sql);    //执行所有
statement.executeUpdate(sql);//增、删、改

resultSet

resultSet.getObject();//不知道列的类型，就用这个
resultSet.getInt();//如果知道就用指定类型
resultSet.getString();

resultSet.beforeFirst();//指定到第一个
resultSet.afterLast();//指定到最后一个
resultSet.next();
resultSet.previous();
resultSet.absolute(row); //移动到指定行

idea操作数据库

db.properties文件（在src下）

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudyuseUnicode=true&characterEncoding=utf8&useSSL=true
use=root
password=123

adair.lesson02.utils文件，（提前写好方法）

package com.adair.lesson02.utils;

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

public class jdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String use = null;
    private static String password = null;

	//加载驱动，获取url,username,password
    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");
            use = properties.getProperty("use");
            password = properties.getProperty("password");

            //驱动只用加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
          return DriverManager.getConnection(url, use, password);
    }

    //释放连接
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

插入

package com.adair.lesson02;

import com.adair.lesson02.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();
            st = conn.createStatement();
            String sql="INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES (4,'adair','123','123@qq.com','2022-10-10')";
            int i = st.executeUpdate(sql);  //受到影响的行数
            if(i>0){
                System.out.println(i+"行改变了");
            }else{
                System.out.println("没有改变");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }

    }
}

删除/更新：只需要改变字符串sql内容即可
查询

package com.adair.lesson02;

import com.adair.lesson02.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();
            st = conn.createStatement();
            String sql = "SELECT * FROM `users` WHERE NAME='adair2'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getObject("email"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }


    }
}

statement对象

利用statement对象不安全

package com.adair.lesson02;

import com.adair.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLzhuru {
    public static void main(String[] args) {
        //正常登陆
//        login("adair","123456");

        //异常登陆，注入登陆
        login("'or '1=1","'or '1=1");
    }
    public static void login(String name,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = jdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "SELECT * FROM `users` WHERE NAME='"+name+"' AND `PASSWORD`='"+password+"'";

            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getObject("NAME"));
                System.out.println(rs.getObject("PASSWORD"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

PreparedStatement对象

防止SQL注入

插入

package com.adair.lesson03;

import com.adair.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try{
            conn = jdbcUtils.getConnection();
            String sql="INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值参数
            st.setInt(1,4);
            st.setString(2,"adair4");
            st.setString(3,"123");
            st.setString(4,"123@qq.com");
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = st.executeUpdate();//执行
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

删除

package com.adair.lesson03;

import com.adair.lesson02.utils.jdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try{
            conn = jdbcUtils.getConnection();
            String sql="DELETE FROM `users` WHERE id=?";

            st = conn.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值参数
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

更新

package com.adair.lesson03;

import com.adair.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try{
            conn = jdbcUtils.getConnection();
            String sql="update users set `NAME`=? where id=?";

            st = conn.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值参数
            st.setString(1,"adair3");
            st.setInt(2,1);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

查询

package com.adair.lesson03;

import com.adair.lesson02.utils.jdbcUtils;

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

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=?";

            st = conn.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值参数
            st.setInt(1,3);
            //执行
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getObject("email"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

SQL注入

反SQL注入

package com.adair.lesson03;

import com.adair.lesson02.utils.jdbcUtils;

import java.sql.*;

public class SQLzhuru {
    public static void main(String[] args) {
        //正常登陆
        login("adair","123456");

        //异常登陆
//        login("'or '1=1","'or '1=1");
    }
    public static void login(String name,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = jdbcUtils.getConnection();
            //传递参数的本质，把传进来的参数当作字符串
            //转义字符会被直接转义。比如'
            String sql = "SELECT * FROM `users` WHERE NAME=? AND `PASSWORD`=?";
            st = conn.prepareStatement(sql);

            st.setString(1,name);
            st.setString(2,password);

            rs = st.executeQuery();
            while(rs.next()){
                System.out.println(rs.getObject("NAME"));
                System.out.println(rs.getObject("PASSWORD"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

idea可视化连接Mysql

在idea右侧的database中进行操作

事务

ACID：原子性、一致性、隔离性、持久性（不可变）

隔离性的问题：

脏读：一个事务读取另一个没有提交的事务
不可重复读：在同一个事务，重复读取表中的数据，数据会发生改变
虚读（幻读）：在一个事务内，读取了别人插入的数据，导致读出来前后数据不一样

代码实现

conn.setAutoCommit(false);

conn.commit();

package com.adair.lesson04;

import com.adair.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = 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 throwables) {
            //默认回滚，开发人员知道这个问题
//            try {
//                conn.rollback();        //如果失败，回滚
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }
    }
}

上面的代码：就算后一条sql失败，也是默认回滚的（因为没有提交sql1）

数据库连接池

数据库连接 — 执行 — 释放：连接和释放是十分浪费资源。eg(开门 – 服务一个人 – 关门)
池化技术：准备预先的资源，过来就是预先准备好的。
- 最小连接数
- 最大连接数
- 等待超时：100ms
编写连接池，实现接口DataSource

开源数据实现(原生代码)，自己之前写过，不过别人直接封装了

DBCP

C3P0

Druid：阿里巴巴

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

DBCP：配置文件使用*.properties

使用commons-dbcp-1.4.jar 和 commons-pool-1.6.jar

public class DBCP_c3p0 {
    public static DataSource dataSource = null;
    static {
        try {
            //加载资源db.properties
            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(is);

            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection != null) connection.close();
        if (statement != null) statement.close();
        if (resultSet != null) resultSet.close();
    }
}

C3P0：配置文件使用*.xml

使用c3p0-0.9.5.5.jar和mchange-commons-java-0.2.19.jar

结论

无论使用什么数据源，本质还是一样的，DataSource接口不变，dataSource.getConnection()就不会变

adair-zhang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记

初识MySQL引用JavaEE：企业级开发Web：前端：展示数据后台：相当于连接点，JDBC(Java Database Connectivity)，控制前端，给前端传数据数据库：存数据，管理数据去IOE：它是阿里巴巴造出的概念。其本意是，在阿里巴巴的IT架构中，去掉IBM的小型机、Oracle数据库、EMC存储设备。DB数据库分类关系型DB：通过表格（行列）MySQL、Oracle、SQL Server、DB2、SQLlite非关系型DB（NoSQL,not onl
复制链接

扫一扫