MySQL笔记

一、初识MySQL

1.1、数据库分类

关系型数据库：（SQL）

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

非关系型数据库：(NoSQL) Not Only

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

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；

1.2、MySQL简介

MySQL是一个关系型数据库管理系统

之前是属于瑞典MySQL AB公司，现在属于Oracle旗下产品

MySQL是最好的RDBMS（关系数据库管理系统）应用软件之一。

是开源的数据库软件~体积小、速度快、招人成本低、所有人必须会，适用于中小型网站、或者大型网站，集群。

官网：http://ww.mysql.com

1.3、链接数据库

mysql -uroot -p123456   --连接数据库

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

flush privileges; --刷新权限

-------------------------------------------------
show databases; --查看所有的数据库
use school  --切换数据库  use  数据库名
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息

create database 表名  --创建一个数据库
exit;   --退出链接

二、操作数据库

• 操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
• 操作数据库

1.创建数据库

create database [if not extsts] teacher --创建数据库

2.删除数据库

drop database [if exists] teacher  --删除数据库

3.使用数据库

use 'school'   --使用数据库  如果表名或者字段是一个特殊字符，就需要带``

4.查看数据库

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

2.1、数据库的列类型

数值:

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

字符串：

char	字符串固定大小	0~255
varchar	可变字符串	0~65535 常用的变量 对应Java中的String
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

2.2、数据库的字段属性（重点）

Unsigned：

• 无符号的整数
• 声明了该列不能声明为负数

zerofill ：

• 0填充的
• 不足的位数，使用0来填充，

自增：

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

非空 NULL not null :

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

拓展：

每一个表，都必须存在以下5个字段,未来做项目时用的，表示一个记录存在的意义！
id  主键
`version`    乐观锁
is_delete    伪删除
gmt_create   创建时间
gmt_updata   修改时间

2.3、创建数据库表

create table if nof extsts `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(50) default null comment '邮箱',
    primary key(`id`)--设置id为主索引
)engine=innodb default charset=utf8

格式：
CREATE TABLE [IN NOT EXTSTS]`表名`（
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
...
'字段名' 列类型 [属性] [索引] [注释],
）[表类型][字符集设置][注释]

show create database school  --查看创建数据库的语句
show create table student --查看student数据表的定义语句
desc student  --显示表的结构

2.4、数据表的类型

--关于数据库的引擎
/*
innodb   数据库默认使用的
myisam   早些年使用的
*/

2.5、修改表

--修改表名 alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1

--增加表的字段 alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11)

--修改表的字段 （重命名，修改约束！）
--alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(11)  --修改约束

--alter table 表名 change 旧名字  新名字  列属性[]
alter table teacher1 change age age1 int(1)  --字段重命名

--删除表的字段 alter table 表名 drop 字段名
alter table teacher1 drop age1

--删除表（如果表存在再删除）
drop table if exists teacher1

--所有的创建和删除尽量加上判断，以免报错

注意点：

• ``字段名，使用这个包裹！
• 注释 – 或者 /**/
• sql大小写不敏感，建议写小写 因为大写有些都不认识
• 所有符号全部用英文

三、数据管理

3.1、外键

--创建表成功后添加外键约束 
alter table `student` add constraint `FK_qradeid` foreing key(`gradeid`) references `grabe`(`gradeid`);

3.2 DML语言（重要，最好全部背下来）

--插入语句（添加）
--insert into 表名（[字段1,字段2,字段3,...]) values('值1'）,（'值2'）,（'值3'）...)
insert into `student`(`name`,`pwd`,`sex`) valuse (`张三`,`aaaaa`,`男`),(`李四`,`520131`,`男`)

--修改表类容   加了条件
update `student` set name ='小红',eml = 137089080@qq.com where id = 1; 

--不指定条件的情况下，会改动所有表
update `student` set name = `祖国我爱你`;

语法：
--update 表名 set 字段属性 = [修改的值],字段属性 = [修改的值] where（条件） 字段属性 = 值;  

• where 通过条件定位数据

条件操作符：

操作符	含义	范围	结果
=	等于	1=1	true
<>或！=	不等于	2<>1	true
>
<
>=
<=
between…add…	再某个范围内	[5,10]
add	我和你 &&
or	我或你 ||

--删除
语法：delete from 表名 where 条件
--删除表
delete from `student`		

--删除表类容
delete from `student` where id = 1;

--清空表里面所有类容
truncate `student`

3.3、DQL查询数据（*最重要）

• DQL：（Data Query Langage:数据查询语言）
• 所有的查询操作都用它 Select（选择）
• 简单的查询，复杂的查询它都能做
• 是数据库最核心的语言

--查询全部的学生 select 字段 from 表
select * from student

--查询指定字段
select `studentNo`,`studentName` from student

--可以起别名，给结果起一个名字 用 AS  就可以给字段起别名，也可以给表起别名
select `studentNo` AS 学号,`studentName` AS 学生姓名 from student AS 学生表

--函数 concat(a,b)字符串的形式拼接
select concat('姓名',studentName) AS 新名字 from student

--去重 distinct
--作用，去除select查询出来的结果中重复的数据，重复的数据只显示一条
select * from result  --查询全部的考试成绩
select `StudentNo` from result  --查询有哪些同学参加了考试
select distinct `StudentNo` from result  --发现重复数据，使用distinct去重

=============== 条件查询where ============================
--查询考试成绩再95-100分之间
select studentNo,`StudentResult` from result where StudentResult>=95 and StudentResult<=100

--模糊查询（区间）
select studentNo,`StudentResult` from result where StudentResult between 95 and 100

--除了1000号学生之外的同学成绩
select student, `StudentResult` from result where studentNo!=1000;

--!=  not
select studentNo,`StudentResult` from result where not studentNo = 1000;

• 模糊查询

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符不为null，结果为真
between	a between b and c	若a在b和c之间，结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,a3…)	假设a在a1，或者a2…其中的某一个值中，结果为真

====================  模糊查询  ======================
--查询表中姓刘的同学  使用like结合通配符 %（代表0到任意个字符）,_(一个字符)
select `StudentNo`,`StudentName` from `student` where StudentName like '刘%'
select `StudentNo`,`StudentName` from `student` where StudentName like '刘_'
--查询名字中带有嘉字的同学
select `StudentNo`,`StudentName` from `student` where StudentName like '%嘉%'

--使用in查询在某个地方的学生
select `StudentNo`,`StudentName` from `student` where `Address` in('安徽','河南洛阳');

--查询地址为空的学生null
select `StudentNo`,`StudentName` from `student` where `Address`='' or Address is null

--查询有出生日期的同学  出生日期不为空
select `StudentNo`,`StudentNane` from `student` where BornData is not null
--查询没有出生日期的同学  出生日期为空
select `StudentNo`,`StudentName` from `student` shere BornData is null

3.4 、联表查询

=============== 联表查询 ========================
==查询参加了考试的同学（学号，姓名，科目编号，分数）
select * from student  ==查询学生表
select * from result   ==查询成绩表

==思路：1.分析查询的字段来自那些表，（链接查询） 2.确定使用那种查询 3.确定交叉点（这两个表中那个数据是相同的）
==判断条件：学生表中的student = 成绩表 studentNo
--使用inner join查询
select s.studentNo,studentName,SubjetNo,StudentResult
from student AS s
inner join result AS r
on s.studentNo = r.studentNo

--使用right join查询
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
right join result r
on s.studentNo = r.studentNo

--使用left join查询 查询两个表中缺考的同学
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
left join result r
on s.studentNo = r.result
where StudentResult is null

操作	描述
inner join	如果两个表中至少有一个匹配，就会返回执行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

3.5、分页和排序

===================分页 limit  和排序  order by
--排序： 升序：asc  降序：desc
order by Student desc  --降序
order by Student asc   --升序

limit 0,5 --0代表起始值，5代表几条数据

--查询 JAVA第一学年  课程成绩排名前十的学生，并且分数要大于80分的学生信息（学号，姓名，课程名称，分数）
select s.`StudentNo`,`StudentName`,`SubjecName`,`StudentResult`
from `student` as s
inner join `result` as r
on s.`StudentNo` = r.`StudentNo`
inner join `subject` as sub
on sub.`SubjectNo` = r.`SubjectNo`
where SubjectName = 'Java第一学年' and StudentResult>=80
order by StudentResult desc
limit 0,10

3.6、子查询

--查询数据库结构-1 的所有考试结果（学号、科目编号、成绩），降序排列
--方式一：使用连接查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` as r
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
where SubjectName = '数据库结构-1'
order by StudentResult desc

--方式二：使用子查询（由里及外判断）
select `StudentNo`,`SubjectNo`,`StudentResult`
from `result`
where SubjectNo = (
	select SubjectNo from `subject`
    where SubjectName = '数据结构-1'
)
order by StudentResult desc

3.7、MySQL函数学习

常用函数	含义
select abs(-8)	绝对值
select ceiling(9.4)	向上取整
select floor(9.4)	向下取整
select rand	返回一个0~1之间的随机数
select sign	判断一个数的符号 0-0 负数返回-1，正数返回1
select char_length(‘即使再小的帆也能远航’)	返回字符串的长度
select concat(‘我’,‘爱’,‘你’,‘们’)	拼接字符串
select insert(‘我爱编程hellworld’,1,2,‘超级热爱’)	从某个位置开始替换某个长度
select lower(‘KuangShen’)	小写字母
select upper(‘KuangShen’)	大写字母
select instr(‘kuangshen’,‘h’)	返回第一次出现的子串的索引
select current_date()	获取当前日期
select curdate	获取当前的日期
select now	获取当前的时间
select localtime()	获取本地时间
select sysdate	获取系统时间

• 聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg	平均值
max()	最大值
min()	最小值

四、事务

要么都成功，要么都失败

将一组SQL放在一个批次中去执行~

• ACID ：原子性 一致性 隔离性 持久性 （幻读，脏读）
• 原子性：

​ 要么都成功，要么都失败。

• 一致性：

  ​ 事务前后完整性要保证一致

• 隔离性：

​ 两个用户同时操作时，数据库为每个用户开启一个单独的事务，不会被其他事务的操作数据所干扰。

• 持久性：

​ 事务一旦提交则不可逆，被持久化到数据库中

​

--转账 创建一个商店
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

--设置钱
insert into account(`name`,`money`)
values ('A',2000.00),('B',10000.00)

--模拟转账：事务
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;

五、索引

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

1. 主键索引：primary key

• 唯一的标识，主键不可重复

2. 唯一索引：unique key

• 避免重复的列出现，它可以重复

3. 常规索引：key 或者 index

• 默认的，index key关键字来设置

4. 全文索引：fullText

• 在特定的数据库引擎下才有

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

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

用户管理

--创建用户 create user 用户名 identified by '密码'
create user huawang identified by '123456'

--修改密码(修改当前用户密码)
set password = password('123456')

--修改密码（修改指定用户密码）
set password for huawang = password('123456')

--重命名，rename user 原来的名字 to 新的名字
rename user huawang to huawang2

--用户授权 
crant all privileges 

MySQL备份

为什么要备份：

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

MySQL数据库备份的方式

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

mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 student >D:/a.sql

规范数据库设计

当数据库比较复杂的时候，我们就需要设计了=======

• 糟糕的数据库设计：会造成数据沉于，浪费空间，数据库插入删除都会麻烦、会造成程序的性能差
• 良好的数据库设计：可以节省内存空间，保证数据库的完整性，方便我们开发系统

软件开发中，关于数据库的设计

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

数据库三大范式

• 第一范式

  • 原子性：保证每一列不可再分

• 第二范式

  • 前提：满足第一范式
  • 每张表只描述一件事情

• 第三范式

  • 前提：满足第一范式和第二范式
  • 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

• 规范性 和 性能的问题

  • 关联查询的表不得超过三张表
  • 考虑商业化的需求和目标，（成本以及用户体验！）数据库的性能更加重要
  • 在数据库性能问题的时候，需适当牺牲下规范性，故意增加一些数据沉余。

JDBC(重要)

• sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称 JDBC.

• 这些规范的实现由具体的厂商去做~

• 对于开发人员来说，我们只需要掌握 JDBC接口的操作即可

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5vniDqUg-1635737901524)(C:\Users\86150\AppData\Roaming\Typora\typora-user-images\image-20211031201700629.png)]

//第一个JDBC程序
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=true";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);
        
        //4.执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();
        
        //5.执行SQL的对象 去 执行SQL，可能存在结构，查看返回结构
        String sq1 = "SELECT * FROM users";
        
        //返回的结果集，结果集中封装了我们全部的查询出来的结果
        ResultSet resultSet = statement.executeQuery(sq1);
        
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject(columnLable:"id"));
            System.out.println("name="+resultSet.getObject(columnLable:"name"));
            System.out.println("pwd="+resultSet.getObject(columnLable:"pwd"));
            System.out.println("email="+resultSet.getObject(columnLable:"email"));
            System.out.println("birth="+resultSet.getObject(columnLable:"birth"));
            System.out.println("================================="));
            
        }
        
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}