零基础入门学习MySQL全套完整学习笔记(包含DML,DQL基本语法,常用函数,索引,JDBC以及完整案例)

本篇学习笔记总结自bilibiliup主【狂神说Java】系列视频:【狂神说Java】MySQL最新教程通俗易懂
视频作者公众号:狂神说

1、初始MySQL

1.1、数据库分类

关系型数据库(SQL):

  • MySQL, Oracle, Sql Server, DB2
  • 通过表与表之间的关系,行和列之间的关系进行数据的存储

非关系型数据库(NoSQL):

  • Redis, MongDB
  • 非关系型数据库使用对象存储,通过对象的自身属性来决定

1.2、MySQL安装配置

1.2.1、安装

  • 在MacOS环境下,从MySQL官网上找到下载,并且保存初始密码

  • 修改初始密码

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');修改密码。

1.2.2、解决错误ERROR 1045 (28000)

  1. 关闭MySQL服务

  2. 在终端输入命令行

    cd /usr/local/mysql/bin
    sudo su
    
  3. 终端命令行开头变为sh-3.2#后,输入如下命令

    ./mysqld_safe --skip-grant-tables &
    
  4. 打开新终端,输入

    mysql -u -root
    
  5. 现在命令行变为mysql开头,已经无密码登陆数据库,输入以下语句

    UPDATE mysql.user SET authentication_string = PASSWORD('123456') WHERE User = 'root' AND Host = 'localhost';
    
  6. 启动MySQL,输入

    mysql -u root -p
    
  7. 可以使用新密码(123456)登陆了

1.2.3 终端连接数据库

打开终端

mysql -u root -p /*连接数据库*/
flush privileges; /*刷新权限*/

show tables;  /*查看表*/
describe student; /*显示数据库中表的信息*/

2、MySQL使用

2.1、MySQL的SQL语言分类

SQL语言一共分为4大类:数据定义语言DDL,数据操纵语言DML,数据查询语言DQL,数据控制语言DCL

2.1.1、数据定义语言DDL

  • 英文:Data Definition Language

  • 对象: 数据库和表

    • 关键词: create alter drop truncate; (删除当前表再新建一个一模一样的表结构)
    • 创建数据库:create database school;
    • 删除数据库:drop database school;
    • 切换数据库:use school;
    • 查看数据库里存在的表:show tables;

2.1.2、数据操纵语言DML

  • 英文:Data Manipulation Language

  • 对象:纪录(行)

    • 关键词:insert update delete
    • 插入:insert into student values(01,'tonbby',99); (插入所有的字段)
    • 更新:update student set name = 'tonbby',score = '99' where id = 01;
    • 删除:delete from tonbby where id = 01;
  • 开发中很少使用delete,删除有物理删除和逻辑删除,其中逻辑删除可以通过给表添加一个字段(isDel),若值为1,代表删除;若值为0,代表没有删除。此时,对数据的删除操作就变成了update操作了

  • truncatedelete的区别:

    • truncate是删除表,再重新创建这个表。属于DDL,delete是一条一条删除表中的数据,属于DML。

2.1.3、数据查询语言DQL

  • 英文:Data Query Language

    select ... from student where 条件 group by 分组字段 having 条件 order by 排序字段
    
    • 执行顺序:from->where->group by->having->order by->select
    • 注意:group by通常和聚合函数(avg(),count()...)一起使用 ,经常先使用group by关键字进行分组,然后再进行集合运算。
    • group byhaving 一起使用,可以限制输出的结果,只有满足条件表达式的结果才会显示。
  • havingwhere的区别:

    两者起作用的地方不一样,where作用于表或视图,是表和视图的查询条件。having作用于分组后的记录,用于选择满足条件的组。

2.1.4、数据控制语言DCL在这里插入图片描述

  • 英文:Data Control Language
  • 数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视,用户,权限,事务等。
    • grant:授权
    • rollback:回滚
    • commit:提交

2.1、操作数据库

  1. 创建数据库

    create database [if not exists] db;
    
  2. 删除数据库

    drop database [if exists] db;
    
  3. 使用数据库

    use `db`;   /*如果数据库名是一个特殊字符,需要加``框起来*/
    
  4. 查看数据库

    show database; /*查看所有数据库*/
    

2.2、数据库的列类型

2.2.1、整数类型

  • tinyint :一个字节
  • smallint:两个字节
  • mediumint 三个字节
  • int四个字节 标准的整数(常用)
  • bigint :八个字节

2.2.2、浮点数类型

  • float :浮点数 四个字节
  • double:浮点数 八个字节
  • decimal:字符串形的浮点数(金融计算一般使用,避免精度问题)

2.2.3、字符串类型

  • char :字符串固定大小 0~255 字节
  • varchar可变字符串 0~65535 字节(对应Java的String类型,常用)
  • tinytext:微型文本 2^8 - 1 比特
  • text: 文本串 2^16 - 1 比特(保存大文本)

2.2.4、时间类型

  • date:YYYY-MM-DD 日期
  • time:HH:MM:SS 时间格式
  • datetimeYYYY-MM-DD HH:MM:SS 最常用的时间格式
  • timestamp:1970.1.1 到现在的毫秒数 全世界时间地区统一,较为常用
  • year:年份表示

2.2.5、Null类型

  • 没有值,未知
  • 不要使用Null进行运算,结果一定为Null

2.3、数据库的字段属性

  • unsigned:无符号的整数,不能声明为负数
  • zerofill:默认进行零填充,不足的位数使用0进行填充
  • AutoIncreasment:自增,自动在上一条记录上加一,可以自定义主键子增的起始值和步长
  • NotNull:非空,如果不赋值会报错
  • default:设置默认值
/*每一个表都必须存在以下5个字段,用来保证安全性和健壮性*/
`id` 主键
`version` 乐观锁
`is_delete` 伪删除
`gmt_create` 创建时间
`gmt_update` 修改时间

2.4、创建数据库表

2.4.1、语法格式

/*表的名称和字段用``框起来*/
create table if not exists `student`(
    `id` int(4) not null auto_increment comment '主键自增',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(30) not null default '123456'comment '密码',
    `gender` 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

重点格式

create table [IF NOT EXISTS] `表名`(
  `字段名` 列类型[属性] [索引] [注释],
  `字段名` 列类型[属性] [索引] [注释],
  ...
  `字段名` 列类型[属性] [索引] [注释],
) [表类型][字符集设置][注释]

查看创建数据库的语句

show create Table `student`   /*查看创建时的语句*/
desc `student`   /*显示表的结构*/

2.4.2、数据库引擎分类

  • InnoDB:MySQL 5.8默认使用

  • MYISAM:MySQL 早期使用

    MYISAMInnoDB
    事务支持不支持支持
    数据行锁定不支持支持
    外键约束不支持支持
    全文索引支持现在仅支持英文
    表空间大小较小较大,约等于MYISAM两倍

常规使用操作

  • MYISAM:节约空间,速度较快
  • InnoDB:安全性比较高,支持事务处理,支持多表对用户操作

2.4.3、在物理空间的位置

所有的数据库文件都存在在data目录下,本质还是文件的存储

MySQL数据的引擎在物理文件上的区别

  • InnoDB:在数据库表张只有一个*.frm文件,以及上级目录下的 ibdata1文件
  • MYISAM:对应文件
    • *.frm 表结构文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件

设置数据库表的字符集编码

Charset = utf8

默认编码是 Latin1,不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6、修改数据库表

2.6.1、语法格式

修改

/*修改表名:ALTER TABLE 旧表名 RENAME AS 新表名*/
alter table teacher rename as teacher1

/*增加表的字段:ALTER TABLE 表名 ADD 字段名 列属性*/
alter table teacher add Email varchar(10)

/*修改表的字段(重命名,修改约束)*/
alter table teacher modify age varchar(11) /*修改约束*/
alter table teacher change age age1 varchar(11) /*字段重命名*/

/*删除表的字段*/
alter table teacher1 drop age1

删除表

drop table if exists teacher1

3、MySQL数据管理

3.1、外键

  • 创建

    • 创建外键可以在创建表的时候创建(非常麻烦)

    • 在创建两个不相关的表之后通过添加约束创建

    /*ALTER TABLE 表 
      ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用到哪个表的哪个字段
    */
    alter table `student` 
    add constraint `fk_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
    
    
    • 删除有外键关系的表的时候,必须要删除引用别人的表(从表),再删除被引用的表(主表)

以上的操作都是物理外键,数据库级别的外键,不建议使用!避免数据库过多造成困扰

  • 最佳实践
    • 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
    • 我们想使用外键,使用程序级别实现

3.2、DML语言

  • Insert

    /*插入语句(添加)*/
    /*insert into 表名([字段名1,字段名2,字段名3)) value ('值1','值2','值3') */
    insert into `student`(gender, birthday, address, email)value ('男','1996-2-28','重庆','303333549@qq.com'),('女','1996-2-18','悉尼','303333549@qq.com');
    
    • 字段与字段使用英文逗号隔开
    • 可以同时插入多条数据,value后面的值需要隔开,如value (),(),()
  • update

    /**UPDATE 表名 set colnum_name = value where [条件]**/
    update `student` set gender = '女' where `id` = 1;
    update `student` set gender = '男',email = '32232@qq.com' where `id` = 1;
    

    条件:where 子句 运算符

    操作符含义范围结果
    =等于5false
    <> 或者!=不等于5 <> 6true
    beteen…and…在某个范围内a[][2,5]
    and&&两个条件都成立条件a&&条件b符合a,b条件
    or||两个条件其中一个成立条件a||条件b符合a,b条件之一
  • delete

    /*删除指定数据*/
    delete from student where id = 1;
    
    /*清空一个表*/
    truncate `student`
    
    • 相同点:都能删除数据,都不会删除表结构
    • 不同:
      • truncate 会重新设置自增链,delete会影响自增链
      • truncate不会影响事务

3.2、DQL语言

  • 所有的查询操作 select
  • 数据库中最核心的语言
  • 使用频率最高

基本语法

select [distinct] {* | table.* | [table.field1[as something1],table.field2[as something2],...]}
from table_name1
/*联表查询 如果链接条件,加 on table_name1.something = table_name2.something*/
[left | right | inner join table_name2] 
/*指定结果需要满足的条件*/
[where ..]
/*指定结果按照哪几个字段分组,不可以使用聚合函数*/
[group by]
/*过滤分组的记录必须满足的次要条件*/
[having]
/*指定查询记录按照一个或者多个条件排序*/
[order by]
/*指定查询的条件从哪里到哪里*/ 
[limit {[offset.]row_count | row_countOFFSET offset}];

注意:[]括号代表可选,{}代表必选,并且子句顺序不能换

3.2.1、简单查询

/*查询所有学生*/
select * from student;

/*查询指定字段 (可以用别名)*/
select 'studentNo' as 学号,'StudentName' as 学生姓名 from student as s;

/*函数concat进行拼接*/
select concat('姓名:',StudentName) as 新名字 from student;

3.2.2、表达式

数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…

/*查询哪些学生参加了考试,成绩*/
/*去除select语句中所有重复的数据,重复数据只查询一条*/
select distinct `studentNo` fromn result;

select version()  /*查询系统版本(函数)*/
select 100*3 as 计算结果 /*用来计算(表达式)*/
select @@auto_increment_increment /*查询自增的步长(变量)*/
select 'StudentNo','StudentResult' + 1 as '提分后' from result /*学生考试成绩+1分后查看*/ 

select 表达式 from 表

3.2.3、where条件子句

所有的where语句的返回结果都要为布尔值

select stduentNo from result where StudentResult > 90 && StudentResult < 100

/*除了1000号学生之外的成绩*/
select studentNo, 'StudentResult' from result where studentNo != 1000;

模糊查询

  • like: a like b SQL匹配,如果a匹配到b,则结果为真

    /*查询姓刘的同学*/
    /*like结合 %(代表0到任意个字符)*/
    select `studentNo`,`studentName` from `student` where StudentName like '刘%';
    
    /*查询姓刘的同学,名字后面只有一个字 _(代表0到一个)*/
    select `studentNo`,`studentName` from `student` where StudentName like '刘_';
    
    /*查询姓刘的同学,名字后面只有两个字 __(代表0到任意个字符)*/
    select `studentNo`,`studentName` from `student` where StudentName like '刘__';
    
    /*查询名字中带有某个字的同学*/
    select `studentNo`,`studentName` from `student` where StudentName like '%某%';
    
  • in:a in (a1,a2,a3…) 假设a在a1,或者a2…其中的某一个值中,只能是具体的一个值,结果为真

    /*查询1001,1002,1003号学员*/
    select `studentNo`,`studentName` from `student` where StudentNo in(1001,1002,1003)
    

3.2.4、联表查询

在这里插入图片描述

/*查询参加了考试的同学(学号,姓名,科目编号,分数)*/
select * from student;
select * from result;
/*
思路:1、分析需求,分析哪些字段来自哪些表
		 2、确定使用哪种连接查询?一共有7种
		 3、确定交叉点(两张表中哪个数据是相同的)
		 
		 判断条件,学生表中的studentNo 等于 成绩表中的 studentNo
		 
*/

/*Inner join 实例*/
select s.studentNo, studentName, SubjectNo, StudentResult 
from student as s inner join result as r 
where s.studentNo = r.studentNo

/*Right join 实例*/
/*比如在student表中有一个同学没有参加考试,在result信息表中没有该同学*/
/*这个查询不能查到该同学(因为他没有出现在result表)*/
select s.studentNo, studentName, studentResult
from student as s right join result r on s.studentNo = r.studentNo;

/*left join实例 包括左边的表result的所有东西*/
/*比如在student表中有一个同学没有参加考试,在result信息表中没有该同学*/
/*这个查询可以查到该同学(因为他出现在student表)*/
select s.studentNo, studentName, studentResult
from student as s left join result r on s.studentNo = r.studentNo;

/*查询缺考的同学*/
/*比如在student表中有一个同学没有参加考试,在result信息表中没有该同学*/
/*该查询可以查到所有缺考的同学,即存在在student表,却没有存在在result表的所有同学*/
select s.studentNo, studentName, studentResult
from student as s left join result r on s.studentNo = r.studentNo
where studentResult is null;

/*查询参加了考试的同学的 学号,学生姓名,科目名,科目分数*/
/*分别来源于 student表,student表,subject表,result表*/
/*student表 result表 用 studentNo 链接*/
/*result表 subject表 用 studentNo 链接*/
select s.studentNo, studentName, subjectName, studentResult
from student as s right join result as r on s.studentNo = r.studentNo
where studentResult is null
inner join `subject` sub on r.subjectNo = sub.subjectNo
操作描述
Inner join如果表中至少有一个匹配,就返回值
right join会从右表中返回所有的值,即使左表中没有匹配
left join会从左表中返回所有的值,即使右表中没有匹配
  • join + [链接的表]) 一般和 on一起使用,表示连接查询
  • where表示 等值查询

3.2.5、自链接

自己的表和自己的表链接,核心:一张表拆成两张一样的表

  • 自链接案例表(pid为父id)
categoryIdpidcategoryName
31软件开发
51美术设计
43数据库
82办公信息
21信息技术
63web开发
75ps技术
  • 父类表
categoryIdcategoryName
2信息技术
3软件开发
5美术设计
  • 子类表
categoryIdpidcategoryName
43数据库
82办公信息
63web开发
75ps技术
  • 操作父类对应子类的关系
父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
/*查询父子信息*/
select a.`categoryName` as '父栏目', b.`categoryName` as '子栏目' from 'category' as a, 'category' as b where a.`categoryId` = b.`pid`;

3.2.6、分页和排序

  • order by:排序

    升序 (asc) 降序(desc)

    /*查询某一科成绩结果,成绩按照一定顺序排序*/
    /*查询到的结果按照 成绩降序 排序*/
    select s.`StudentNo`,`StudentName`,`SubjectName`,`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`
    where subjectName = '数据结构'
    order by studentResult desc;
    
  • limit :分页

    /*查询某一科成绩结果,成绩按照一定顺序排序*/
    /*查询到的结果按照 成绩降序 排序*/
    
    /*分页,每页只显示五条数据*/
    /*语法:limit 起始值,页面的大小*/
    /*网页应用:当前页,总页数,页的大小*/
    
    /*第一页 limit 0,5  (1-1) * 5 */
    /*第二页 limit 5,5  (2-1) * 5 */
    /*第三页 limit 10,5  (3-1) * 5 */
    /*第四页 limit x,PageSize  x = (n-1) * pageSize, pageSize */
    /*【页面大小:PageSize】*/
    /*【页面起始值:(n-1) * pageSize】*/
    /*【当前页:n】*/
    /*【总页数 = 总数/页面大小】*/
    
    
    
    /*第一页为:limit 0,5   查找为第一到第五的数据*/
    /*第二页为:limit 5,5  查找为第六到第十的数据*/
    select s.`StudentNo`,`StudentName`,`SubjectName`,`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`
    where subjectName = '数据结构'
    order by studentResult desc;
    limit 1,5
    

3.2.7、子查询

本质:在where语句中嵌套一个子查询语句

/*查询数据结构的所有考试结果(学号,科目编号,成绩),降序排列*/

/*方式一:使用链接查询*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r
inner join `subject` as sub
on r.`subjectNo` = sub.`subjectNo`
where subjectName = '数据结构'
order by desc

/*方式二:使用子查询*/
/*第一步,找到所有考试的学生学号,科目编号,成绩*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r where `studentNo` = ()

/*第二部:子查询中,找到参加了数据结构的学生学号*/
select StudentNo from `subject` where SubjectName = '数据结构'

/*第三部:放进第一步,由子查询开始查,链接到主语句*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r where `studentNo` = (
	select StudentNo from `subject` where SubjectName = '数据结构'
)

4、MySQL函数

4.1、常用函数

/*数学运算*/
select abs(-8)   /*绝对值*/
select ceilling(6.33) /*向上取整*/
select floor(6.32)  /*向下取整*/
select rand(); /*返回一个随机数*/
select sign(23)   /*判断一个数的符号,负数返回-1,正数返回1*/

/*字符串函数*/
select char_length('Neillll');  /*字符串的长度*/
select concat('Neil','Join','mmm')  /*拼接字符串*/
select insert('I love helloword',1,1,'you') /*从第一个位置替换一个长度*/

/*查询姓周的同学,并替换成邹*/
select replace(studentname,'周','邹') from student;

/*时间和日期函数*/
select current_date() /*获取当前日期*/
select now()   /*获取当前时间*/
select localdate(); /*获取当地时间*/
select sysdate();  /*获取系统时间*/
select version();  /*MySQL当前版本*/

4.2、聚合函数

函数名称描述
count()计数
sum()求和
avg()求平均值
max()求最大值
min()求最小值
/*查询不同课程的平均分,最高分,最低分,平均分大于80*/
select subejctName,AVG(studentResult) as 平均分 ,MAX(studentResult),MIN(studentResult)
from result r
inner join subject sub
on r.subjectNo = sub.subjectNo
group by r.SubjectNo     /*通过学科进行分组*/
having 平均分 > 80

4.3、数据库级别的MD5加密

/*假设现在有一个user表,里面的字段为id, user_name, pwd*/

/*明文密码*/
insert into `user` value(1,'zhangsan','12333');

/*加密*/
update `user` set pwd = MD5(pwd);  /*所有密码MD5加密*/

/*插入时加密*/
insert into `user` value (1,'zhangsan',MD5('12333'))

/*校验*/
select * from `user` where `user_name`='zhangsan' and pwd=MD5('12345');

5、事务

5.1、基本概念

5.1.1、事务的ACID

谈到事务一般都是以下四点

  • 原子性(Atomicity)
    原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
  • 一致性(Consistency)
    事务前后数据的完整性必须保持一致。
  • 隔离性(Isolation)
    事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
  • 持久性(Durability)
    持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响 (一旦提交不可逆)

具体案例分析:https://blog.csdn.net/dengjili/article/details/82468576

###5.1.2、事务隔离级别

  • 脏读: 指一个事务读取了另外一个事务未提交的数据。
  • 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
  • 虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。(一般是行影响,多了一行)

5.1.3、隔离级别设置

在这里插入图片描述

5.2、事务的操作

MySQL 默认开启事务自动提交的

set autocommit = 0:关闭提交

set autocommit = 1:开启(默认)

  • 事务开启

    1. set autocommit = 0:关闭提交

    2. start tansaction:标记一个事务的开始,从这个之后的SQL都在同一个事物内

    3. 执行若干操作,可以使用savepoint设置事务一个保存点

    4. commit:提交:持久化(成功)

      • rollback:回滚
      • rollback to savepoint 保存点:来回滚到保存点
      • release savepoint 保存点名:删除保存点
    5. set autocommit = 1:事务结束之后开启自动提交

  • 案例

    /*创建数据库以及表*/
    create database shop charset 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;
    
    insert into account(`name`,`money`)
    values ('Neil',2000.00),('Andy',10000);
    
    /*模拟转账操作*/
    set autocommit = 0;  /*关闭自动提交*/
    start transaction;  /*开启一个事务*/
    /*模拟Neil给Andy进行转账*/
    update account set  money = money - 500 where name = 'Neil';
    update account set  money = money + 500 where name = 'Andy';
    /*提交*/
    commit ;
    /*回滚*/
    rollback ;
    /*恢复默认值*/
    set autocommit = 1;
    

6、索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构

索引的本质是数据结构

6.1、索引的分类

  • 主键索引(primary key)
    • 唯一的标识,主键不可重复,只有一个列
  • 唯一索引(unique key)
    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引。
  • 常规索引(key/index)
    • 默认的,可以用index关键字和key关键字来设置
  • 全文索引(FullText)
    • 在特定的数据库引擎下才有,可以快速定义

6.2、索引的使用

基础语法

/*显示所有的索引信息*/
show index from student

/*增加一个全文索引(索引名) 列名*/
alter table school.student add fulltext index `studentName`(`studentName`);

/*explain 分析SQL执行的状况*/
explain select * from student; /*非全文索引*/
explain select * from student where match(studentName) against('刘');

6.3、测试索引

  • 创建表以及随机生成一百万条数据

    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` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,          /*自动设置创建时间*/
        `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  /*自动设置更新时间*/
        primary key (`id`)
    )engine = InnoDB default charset =utf8mb4 comment ='app用户表';
    
    /*插入100万条数据*/
    
    /*写函数之前必须要写*/
    DELIMITER $$
    
    create function mock_data2()
    returns int
    begin
        declare num int default 1000000;
        declare i int default 1;
        while i < num do
            insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
            values (concat('用户',i),'303333@qq.com',concat('18',floor(rand()*((999999999-100000000) + 100000000))),floor(rand()*2),UUID(),floor(rand()*100));
            set i = i + 1;
        end while;
        return i;
    end;
    /*有点像执行函数的意思*/
    select mock_data2();
    
  • 测试没有索引的查询,并且使用explain查看效率

    /*在MacBook Pro16环境下测试查询效率*/
    select * from app_user where name = '用户559999' /*用时400 ms*/
    /*使用explain 查看效率,发现rows一览993160*/
    
  • 创建索引

    /*create index 索引名 on 表(字段名)*/
    /*索引命名规则:id_表名_字段名*/
    create index id_app_user_name on app_user(`name`);
    /*使用explain 查看效率,发现rows一览为1*/
    
  • 再次查询

    select * from app_user where name = '用户559999' /*用时68 ms*/
    
  • 索引在100万条数据下,查询效果明显提升

6.4、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

InnoDB默认数据结构为B树

索引背后的数据结构与原理 :http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8、权限管理与备份

8.1、用户管理

一般通过IDE可以可视化操作用户,实际上是对系统用户表进行操作,以下为一些常用命令

/*创建用户*/
create user neil identified by '123123';

/*修改密码*/
set password = PASSWORD('1231321');

/*重命名 rename user 原来的名字 to 新的名字*/
rename user neil to neillll;

/*用户授权 all privileges 全部的权限,库,表,除了grant权限*/
grant all privileges on *.* to neillll;

/*查看权限*/
show grants for neillll;

/*撤销权限,在哪个库撤销,给谁撤销*/
revoke all privileges on *.* from neilll;

8.2、MySQL备份

备份的原因:

  • 保证重要数据不丢失
  • 数据转移

MySQL数据备份的方式

  • 直接拷贝物理文件
  • 在可视化工具手动导出
  • 使用终端mysqldump命令行使用

9、规范数据库设计

9.1、数据库设计

  • 糟糕的数据库设计
    • 数据冗余,浪费空间
    • 数据库插入和删除会麻烦和异常
    • 程序性能差
  • 良好的数据库设计
    • 节省内存空间
    • 保存数据库完整性
    • 方便开发系统

9.2、三大范式

  • 第一范式(原子性):要求数据库表的每一行每一类都是不可分割的原子数据项
  • 第二范式:第一范式基础上,每张表只描述一件事情
  • 第三范式:每一列直接数据都与主键直接相关,而不是间接相关

规范性和性能冲突的问题:

**阿里文档要求:**根据商业化需求,关联查询表不得超过三张

  • 在商业化的需求和目标下,(成本,体验)数据库性能更加重要
  • 在满足性能问题的时候,需要适当考虑规范性
  • 故意给表增加一些冗余字段(从多表查询变成单表查询)
  • 故意增加一些计算列(从大数据量减为小数据量:索引)

10、JDBC

10.1、创建驱动手动链接数据库

  • 创建数据库

    /*创建数据库*/
    create database jdbsStudy character set utf8 collate utf8_general_ci;
    
    use jdbsStudy;
    
    create table users(
        id int primary key,
        name varchar(40),
        password varchar(40),
        email varchar(60),
        birthday date
    );
    
    insert into users(id,name,password,email,birthday)
    values(1,'Neil','123456','asdas12@qq.com','1980-12-23'),
         (2,'andy','123456','asdas12@qq.com','1977-2-12'),
           (3,'Dwan','123456','356489@qq.com','1996-2-14')
    
  • java代码

    public class jdbcDemo01 {
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
            //1、加载驱动,固定写法
            Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
            //2、用户信息和url
            // useUnicode=true&characterEncoding=utf8&useSSL=true  支持中文编码,设置字符集,安全链接(这里改成True链接失败了,查阅资料发现在Mac上会失败,windows不会,所以变成False)
            String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
            String userName = "root";
            String userPwd = "123456";
            //3、链接成功,数据库对象  connection代表数据库
            Connection connection = DriverManager.getConnection(url, userName, userPwd);
            //4、执行sql对象
            Statement statement = connection.createStatement();
            //5、执行sql对象去执行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("password" + resultSet.getObject("password"));
                System.out.println("email" + resultSet.getObject("email"));
                System.out.println("birthday" + resultSet.getObject("birthday"));
            }
            //6、释放链接
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
    
  • 详解

    • Url

      // mysql: 3306
      //jdbc:mysql://主机地址:端口号/数据库?参数1&参数2参数3
      String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
      
    • DriverManager

      // connection 代表数据库
      //数据库设置自动提交回滚
      Connection connection = DriverManager.getConnection(url, userName, userPwd);
      //如:
      connection.rollback();
      connection.commit();
      connection.setAutoCommit();
      
    • Statement

      //statemnt :执行SQL的对象
      statement.executeQuery();  //查询返回一个ResultSet,返回所有信息
      statement.execute();    //可以执行任何sql
      statement.executeUpdate;  //执行增删改,返回受影响的行数
      
    • ResultSet

      resultSet.getObject(); //在不知道类型的情况下使用
      //如果知道类型就使用指定类型
      resultSet.getString(); 
      resultSer.getInt();
      resultSer.getDate();
      ...
      
      resultSet.next()  //遍历
      resultSet.absolute(row) //移动到指定行
      
      

10.2、PreparedStatement对象

PreparedStatemnt 可以防止SQL注入,并且效率更高

/*statement 对象有可能存在sql注入问题*/
import java.sql.*;

public class jdbcDemo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        PreparedStatement st = null;
      	//通过?代替参数
        String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";

        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、用户信息和url
        // useUnicode=true&characterEncoding=utf8&useSSL=true  支持中文编码,设置字符集,安全链接(这里改成True链接失败了,查阅资料发现在Mac上会失败,windows不会,所以变成False)
        String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
        String userName = "root";
        String userPwd = "123456";
        //3、链接成功,数据库对象  connection代表数据库
        conn = DriverManager.getConnection(url, userName, userPwd);     
        st  = conn.prepareStatement(sql);//预编译sql
        //手动给st赋值
        st.setInt(1,5);//给id赋值
        st.setString(2,"Neil");//给名字
        st.setString(3,"123132");//密码
        st.setString(4,"30564968@qq.com");
        st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
       //执行
        st.executeUpdate();
        //释放
        st.close();
        conn.close();
    }
}

10.3、数据库连接池

数据库连接 – 执行完毕 – 释放

连接 - 释放 十分浪费资源

池化技术:准备一些预先的资源,过来就连接准备好的资源

常用连接数:10个左右

最小连接数:10

最大连接数:100(业务最高承载上限)

超过最大连接数,就排队等待–> 等待超时:100ms

编写连接池:实现一个接口:DateSource

  • 开源数据源实现:
    • DBCP
    • C3P0
    • Druid:阿里巴巴

使用数据库连接池之后,我们在项目开发中不需要编写连接数据库的代码

===========================================================

参考文献:本篇学习笔记主要总结于bilibili up主 “狂神学Java” 的系列视频— 【狂神说Java】MySQL最新教程通俗易懂

若有侵权请联系我删除

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值