MySQL详解

基本的命令行操作

查看所有的数据库：

show databases;

切换数据库：

use 切换的数据库;

查看数据库中的表：

show tables;

查看表的属性信息：

describe 表名;

创建一个数据库：

create database 数据库名;

退出连接：

exit;

单行注释：

-- 单行注释

多行注释：

/* 多行注释 */

数据库操作

操作数据库（了解）

创建数据库：

 create database [if not exists] 数据库名;

删除数据库：

drop database [if exists] 数据库名;

使用数据库：

use 数据库名;

查看数据库：

show databases;

数据库的列类型

数值型：

类型	描述	大小
tinyint	十分小的整数	1个字节
smallint	较小的整数	2个字节
mediumint	中等大小的整数	3个字节
int	标准的整数	4个字节
bigint	较大的整数	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数（金融计算的时候，一般是使用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填充

自增：

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

null：

如果不填写值，默认就是null

not null：

如果不为字段赋值，就会报错

default：

给字段设置默认值，如果不给该字段赋值，就使用默认值

拓展：

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

操作数据表（重点）

创建

create table if not exists student(    `id` int(4) not null auto_increment comment '学号',    `name` varchar(30) not null default '匿名' comment '姓名',    `password` 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;

格式：

create table [if not exists] `表名`{    `字段名` 字段类型 [属性] [索引] [注释],    `字段名` 字段类型 [属性] [索引] [注释],    ......    `字段名` 字段类型 [属性] [索引] [注释]}[表类型][字符集设置][注释]

查看创建数据库的语句：

show create database 数据库名;

查看创建数据表的语句：

show create table 表名;

显示表的结构：

desc 表名;

数据表的类型：

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

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

MYISAM：节约空间，速度较快
INNODB：安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

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

InnoDB在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
MYISAM对应文件
- *.frm：表结构的定义文件
- *.MYD：数据文件（data）
- *.MYI：索引文件（index）

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

charset=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文）

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认编码

character-set-server=utf8

修改

-- 修改表名：alter table 旧表名 rename as 新表名alter table student rename as `user`;-- 增加表的字段：alter table 表名 add 字段名 列属性alter table user add age int(5);-- 修改表的字段（重命名、修改约束）-- alter table 表名 modify 字段名 列属性alter table user modify age int(3);-- 修改约束-- alter table 表名 change 旧名字 新名字 列属性alter table user change sex gender varchar(5);-- 字段重命名-- 删除表的字段：alter table 表名 drop 字段名alter table user drop age;

删除

-- 删除表（如果存在再删除）drop table if exists `user`;

所有的创建和删除操作尽量加上（if exists/if not exists），以免报错

注意点：

字段名，使用``这个包裹
sql关键字大小写不敏感，建议使用小写
所有的符号全部用英文

约束

添加约束

-- 1、建表时添加约束：CREATE TABLE goodstest (    GID INT(10) NOT NULL AUTO_INCREMENT,  GNAME VARCHAR(20) NOT NULL,  GPRICE FLOAT NOT NULL,  GNUM INT(10) NOT NULL,  PRIMARY KEY (GID)，　UNIQUE KEY (GNAME),　FOREIGN KEY (col_name) REFERENCES tab_name(col_name));-- 2、通过ALTER语句添加约束-- 主键约束-- 语法：ALTER TABLE tab_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option]ALTER TABLE goodstest ADD PRIMARY KEY(GID);-- 语法：ALTER TABLE tab_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]/* 可以更改列定义但不能更改其名称,在不重命名列定义的情况下更改列定义比CHANGE更方便。*/-- 通过修改列定义添加或者添加主键，修改约束一般是先删掉原有的后重新添加ALTER TABLE goodstest MODIFY GID INT(10) PRIMARY KEY;-- 语法：ALTER TABLE tab_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRET | AFTER col_name] /*原始定义中存在但未为新定义指定的属性不会继续使用。即以新定义为准*/ALTER TABLE goodstest CHANGE GID GID INT(10) PRIMARY KEY;/* 当不需要重命名时两个相同的列名是必要的*/   -- 唯一性约束-- 语法：ALTER TABLE tab_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part,...) [index_option] ...ALTER TABLE goodstest ADD CONSTRAINT GNAME_UNI UNIQUE KEY(GNAME);/*如果没有用CONSTRAINT设置约束名 系统会自动生成*/-- 同理可用MODIFY COLUMN及CHANGE COLUMN方法添加-- 外键约束-- 语法：ALTER TABLE tab_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definitionALTER TABLE gsales ADD FOREIGN KEY(GID) REFERENCES goods(GID);-- 默认约束-- 语法：AlTER TABLE goodstest ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}/*删除以及设置*/-- 非空约束以及自增约束：在定义时设置，或者通过MODIFY COLUMN 和 CHANGGE COLUMN设置及删除-- 一张表只能有一个自增长列，并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)

删除约束

-- 删除约束-- 语法：ALTER TABLE tab_name DROP PRIMARY KEY；/*主键是唯一的就这样就行了*/-- 语法：ALTER TABLE tab_name DROP [INDEX | KEY] index_name /*删除唯一性约束*/-- 语法：ALTER TABLE tab_name DROP FOREIGN KEY fk_name/*删除外键约束*/

MySQL数据管理

外键

方式一：在创建表的时候增加约束（麻烦，比较复杂）

create table if not exists student(    `id` int(4) not null auto_increment comment '学号',    `name` varchar(30) not null default '匿名' comment '姓名',    `password` varchar(20) not null default '123456' comment '密码',    `sex` varchar(2) not null default '男' comment '性别',    `birthday` datetime default null comment '出生日期',    `gradeId` int(10) not null comment'学生的年级',    `address` varchar(100) default null comment '家庭住址',    `email` varchar(50) default null comment '邮箱',    primary key(id),    constraint FK_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);-- alter table 表名 add constraint 约束名 foreign key(作为外键的字段) references 被引用的表(被引用表的字段)

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，这里了解即可）

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，如果想使用外键（利用程序去实现）

DML语言（全部记住）

数据库的意义：数据存储，数据管理

DML语言：数据操作语言

insert
update
delete

添加（insert）

insert

-- 插入语句（添加）-- insert into 表名(字段1,字段2,...) values(值1,值2,...);-- 由于设置了主键自增，我们可以省略主键（字段和值必须一一对应）insert into grade('gradeName') values('大三');-- 插入多条记录insert into grade('gradeName') values('大一'),('大二'),('大四');

插入全部字段：

insert into 表名 values(全部字段的值以逗号隔开);

插入全部字段的多条记录：

insert into 表名 values(全部字段),(全部字段),...;

插入指定字段：

insert into 表名(字段1,字段2,...) values(值1,值2,...);

插入指定字段的多条记录：

insert into 表名(字段1,字段2,...) values(值1,值2,...),(值1,值2,...);

注意事项：

字段是可以省略的，但是后面的值必须包含表的全部字段

修改（update）

update

-- 修改id=1的学生的名字update student set sex='男' where id=3;-- 在不指定条件的情况下，会改动所有记录（一定要加上条件）update student set name='你死定了';-- 修改多个属性，属性之间用,隔开update student set name='阿狸',sex='女' where id=1;

修改全部记录：（避免使用）

update 表名 set 属性=值;

修改指定记录：

update 表名 set 属性=值 where 条件;

修改多个属性：

update 表名 set 属性1=值1,属性2=值2... where 条件;

删除（delete）

delete

-- 删除表中的全部记录delete from student;-- 删除指定记录delete from student where id=1;

删除全部记录：

delete from 表名;

删除指定记录：

delete from 表名 where 条件;

truncate

清空表：

truncate table 表名;

作用：完全清空一个数据库表，表的结构和索引约束不会变

delete 和 truncate 的区别

相同点：都能删除数据，都不会删除表结构
不同点：
- truncate重新设置自增列，计数器归零
- truncate不会影响事务

了解即可：delete删除后重启数据库出现的现象

innoDB：自增列会重1开始（存在内存当中，断点即失）
MyISAM：继续从上一个自增量开始（存在文件中的，不会丢失）

DQL查询数据（最重点）

DQL

Data Query Language 数据查询语言

-- 查询student表中的全部记录select * from student;-- 查询指定字段select studentno,studentname from student;-- 给字段起别名，也可以给表起别名select studentno '学号',studentname '学生姓名' from student;-- 函数，拼接字符串select concat("hello ","mysql");select concat("姓名：",studentname) '学生名字' from student;

查询表中的全部记录：

select * from 表名;

查询指定字段的全部记录

select 字段1,字段2,... from 表名;

distinct

作用：去重查询结果中重复的记录

-- 查询全部的成绩select * from result;-- 查询有哪些同学参加了考试select studentno from result;-- 去重select distinct studentno from result;

select 的其他应用

select version();-- 查询系统版本select 100%3-1 as 结算结果;-- 用来计算select @@auto_increment_increment;-- 查询自增的步长-- 查询时使学生成绩+1select studentno,studentresult+1 '提分后' from result;

where条件子句

操作符	举例	描述
=	where id = 1;	插叙id等于1的记录
<>或!=	where id <> 1;	查询id不等于1的记录
>	where id > 1;	查询id大于1的记录
<	where id < 1;	查询id小于1的记录
<=	where id <= 1;	查询id小于等于1的记录
>=	where id >= 1	查询id大于等于1的记录
between ... and ...	where id between 1 and 3;	查询id在1~3之间的记录（包含1和3）
and	where id = 1 and name = '张三';	查询id等于1并且name等于张三的记录
or	where id = 1 or id = 3;	查询id等于1或id等于3的记录
not	where not id = 1;	查询id不等于1的记录
in	where id in (1,2,3);	查询id等于1/2/3的记录

-- 查询成绩在90分以上的select studentno,studentresult from result where studentresult>90;-- 查询除了学号等于1000以外的学生的成绩select studentno,studentresult from result where not studentno=1000;select studentno,studentresult from result where studentno!=1000;

模糊查询

操作符	举例	描述
is null	where name is null;	查询name等于null的记录
is not null	where name is not null;	查询name不等于null的记录
between ... and ...	where id between 1 and 3;	查询id在1~3之间的记录（包含1和3）
like	where name like '张%'	查询name以张开头的记录
in	where id in (1,2,3);	查询id等于1/2/3的记录

说明：在like中，_ 表示单个任意字符，% 表示多个任意字符

联表查询

-- ====================联表查询=======================-- 内联查询select s.studentno,studentname,subjectno,studentresult from student s inner join result r on s.studentno = r.studentno;-- 左联接查询select s.studentno,studentname,subjectno,studentresult from student s left join result r on s.studentno = r.studentno;-- 有联接查询select s.studentno,studentname,subjectno,studentresult from student s right join result r on s.studentno = r.studentno;

操作符	描述
inner join	返回两表中同时符合条件的记录
left join	返回左表中的所有值，即使右表中的值不符合条件
right join	返回右表中的所有值，即使左表中的值不符合条件

三表连接查询

-- 查询参加了考试的学生信息：学号、学生姓名、科目名、分数select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno = r.studentno left join subject sb on r.subjectno = sb.subjectno;

自连接（了解）

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

categoryId	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

categoryId	pid	categoryName
4	3	数据库
6	3	web开发
7	5	ps技术
8	2	办公信息

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父类对应的子类关系-- 内连接方法select father.categoryName,son.categoryName from category father inner join category son on father.categoryid=son.pid;-- 等值连接方法select father.categoryName,son.categoryName from category father,category son where father.categoryid=son.pid;

分页和排序

排序

-- 排序：升序	asc，降序	desc-- 查询数据库结构-1的成绩按降序排序select s.studentno,studentname,subjectname,studentresult from student s,result r,subject sb where s.studentno=r.studentno and sb.subjectname='数据结构-1' and sb.subjectno=r.subjectno order by studentresult desc;

分页

-- 分页，每页只显示5条数据-- 语法：limit 当前记录下标,偏移量select s.studentno,studentname,subjectname,studentresult from student s,result r,subject sb where s.studentno=r.studentno and sb.subjectname='数据结构-1' and sb.subjectno=r.subjectno order by studentresult desc limit 5,5;

语法：

limit 起始下标,偏移量;

子查询

-- 查询数据结构-1的学生（学号，课程号，分数）select studentno,subjectno,studentresult from result where subjectno = (select subjectno from subject where subjectname='数据结构-1') order by studentresult desc;

MySQL函数

常用函数

-- 数学运算select abs(-4);-- 绝对值select ceiling(9.9);-- 向上取整select floor(9.9);-- 向下取整select rand();-- 返回一个0~1之间的随机数select sign(4);-- 判断一个数的符号。0返回0，负数返回-1，正数返回1-- 字符串函数select char_length('hello mysql');-- 字符串长度select concat('hello ','mysql');-- 拼接字符串select insert('llello mysql',1,2,'h');-- 从指定下标（下标从1开始）替换指定长度的字符串select lower('Hello MySQL');-- 转小写select upper('Hello MySQL');-- 转大写select instr('Hello MySQL','y');-- 返回第一次出现指定字符串的索引select replace('Henno MySQL','nn','ll');-- 替换指定字符串select substr('Hello MySQL',1,5);-- 从指定下标截取指定长度的字符串select reverse('Hello MySQL');-- 反转字符串-- 日期函数select current_date();-- 获取当前日期yyyy-MM-ddselect current_time();-- 获取当前时间HH:mm:ssselect now();-- 获取当前日期时间yyyy-MM-dd HH:mm:ssselect 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();

聚合函数

-- 查询表中有多少条记录select count(studentno) from student;-- 会忽略null值select count(*) from student;-- 不会忽略null值select count(1) from student;-- 不会忽略null值-- 求总和select sum(studentresult) as '总成绩' from result;-- 求平均数select avg(studentresult) as '平均分' from result;-- 最大值select max(studentresult) as '最高分' from result;-- 最小值select min(studentresult) as '最低分' from result;

分组

-- 查询不同课程的平均分，最高分和最低分，并且平均分大于90分select subjectname '课程名称',avg(studentresult) '平均分',max(studentresult) '最高分',min(studentresult) '最低分' from result r,subject sb where r.subjectno=sb.subjectno group by sb.subjectno having 平均分>90;

MD5加密（加密）

-- 创建表create table testmd5(id int(4) not null,`name` varchar(20) not null,`password` varchar(50) not null,primary key(id))engine=innodb,default charset=utf8;-- 明文密码insert into testmd5 values(1,'zhangsan','111111'),(2,'lisi','111111'),(3,'wangwu','111111'),(4,'zhaoliu','111111'),(5,'sunqi','111111');-- 加密update testmd5 set password=md5(password);select * from testmd5;-- 插入的时候加密insert into testmd5 values(6,'xiaoming',md5('123456'));-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值select * from testmd5 where name='xiaoming' and password=md5('123456');

事务

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

Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

参考博客：事务ACID理解_dengjili的专栏-CSDN博客_acid

隔离所导致的一些问题

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

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('zhangsan',9000),('lisi',10000);-- 模拟转账：事务set autocommit=1;-- 关闭自动提交start transaction;-- 开启一个事务update account set money=money-500 where name='lisi';update account set money=money+500 where name='zhangsan';commit;-- 提交一个事务，就被持久化了rollback;-- 回滚set autocommit=1;-- 恢复自动提交

索引

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

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（primary key）
- 唯一的标识，主键不可重复，只能由一个列作为主键
唯一索引（unique key）
- 避免该字段出现重复的值，唯一索引可以有多个
常规索引（key/index)
- 使用index / key 关键字来定义
全文索引（filltext）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后，增加索引-- 显示所有的索引show index from student;-- 增加一个全文索引alter table student add fulltext(studentname);-- explain 分析sql执行状况explain select * from student;-- 非全文索引explain select * from student where match(studentname) against('te');

测试索引

-- 创建app_user表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(20) not null comment '密码',    create_time timestamp default current_timestamp,    update_time timestamp null default current_timestamp on update current_timestamp,    primary key(id))engine=innodb,default charset=utf8,comment='用户表';set global log_bin_trust_function_creators=TRUE;-- 删除自定义函数drop function if exists mock_data;-- 创建自定义函数delimiter &&create function mock_data()returns intbegindeclare num int default 1000000;declare i int default 0;while i<num doset i=i+1;insert into app_user(name,email,phone,gender,password,age) values(concat('用户',i),'2222222222@qq.com',concat('176',floor(rand()*999999999)),floor(rand()*2),concat('user',floor(rand()*99999999+1)),floor(rand()*90+10));end while;return i;end&&delimiter ;-- 调用自定义函数select mock_data();-- 测试索引select * from app_user where name='用户10000';-- 未添加索引时，耗时1s左右-- 添加索引：create index 索引名 on 表名(字段名);-- 索引命名规则：id_表名_字段名create index id_app_user_name on app_user(name);select * from app_user where name='用户10000';-- 添加索引后，耗时0.001s

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

索引原则

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

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

参考博客：CodingLabs - MySQL索引背后的数据结构及算法原理

权限管理和备份

用户管理

-- 创建用户:create user 用户名 indetify by 密码;
create user huawujian@% identified by '123456';
-- 修改密码（修改当前用户密码）:set password=password(新密码);
set password=password('111111');
-- 修改密码（修改指定用户密码）:set password for 用户名 = password(新密码);
set password for huawujian = password('111111');
-- 重命名:rename user 用户名 to 新用户名
rename user for huawujian to zhangsan;
-- 用户授权
-- 给指定用户授予全部权限
grant all privileges on *.* to zhangsan;-- *.*：所有数据库的所有表的权限授予给zhangsan（没有授权权限）
-- 查询权限
show grants for zhangsan;-- 查看指定用户权限
show grants for root@localhost;-- 查看管理员权限
-- root用户权限：grant all privileges on *.* to 'root'@'localhost' with grant option;
-- 撤销权限：revoke 哪些权限 on 哪个库.哪个表 from 用户名
revoke all privileges on *.* from zhangsan;
-- 删除用户：drop user 用户名;
drop user zhangsan;

MySQL备份

可视化软件备份这里不做讲解

使用命令行导出：mysqldump

# mysqldump -hIP地址 -u用户名 -p密码 数据库名 表名(多张表以空格间隔) >导出地址
mysqldump -hlocalhost -uroot -p123456 test student >D:a.sql
# 导入
# 登录后切换到指定的数据库
source 备份文件地址
# 未登录的情况使用 mysql -u用户名 -p密码 导入哪个库< 备份文件地址
mysql -uroot -p123456 test< D:a.sql

规范数据库设计

三大范式（了解）

第一范式（1NF）：列不可再分
- 每一列属性都是不可再分的属性值，确保每一列的原子性
- 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生数据冗余
第二范式（2NF）：属性完全依赖于主键
- 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）不需先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一的区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主键
第三范式（3NF）属性不依赖与其他非主属性，属性直接依赖于主键
- 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系

总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

规范性和性能的问题

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

考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
在规范性能的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余字段。（从多表查询中变为单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

JDBC（重点）

数据库驱动

JDBC（重点）

SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC，这些规范的实现由具体的厂商去做

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

测试程序

public Class MyJdbcDemo{
    public static void main(String[] args) throws ClassNotFoundException,SQLException{
        //加载驱动(mysql8的驱动是：com.mysql.cj.jdbc.Driver)
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
        //用户信息和url
        String url="jdbc:mysql:localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";
        //连接成功，获取Connection
        Connection connection=DriverManager.getConnection(url,username,password);
        //执行SQL的对象Statement
        Statement statement=connection.createStatement();
        //sql语句
        String sql="select * from users";
        //执行sql语句
        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"));
            System.out.println("==================================")
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

导入驱动器jar包
加载驱动Class.forName("com.mysql.jdbc.Driver");
连接数据库DriverManager.getConnection(url,username,password);
获取执行sql的对象connection.createStatement();
获取返回的结果集statement.executeQuery(sql);
释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 不建议使用上面的写法
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
Connection connection=DriverManager.getConnection(url,username,password);
// connection代表数据库
// 作用：设置自动提交、事务回滚、事务提交
connection.setAutoCommit();
connection.commit();
connection.rollback();

URL

String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql——3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3...
//oracle——1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement执行sql的对象 PrepareStatement执行sql的对象

statement.executeQuery();//查询操作返回ResultSet
Statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除，都是用这个，返回一个受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获取指定的数据类型

resultSet.getObject();//在不知道列类型的情况下使用
//如果知道列的类型可以使用指定类型的方法
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();

遍历resultSet

resultSet.beforeFirst();//移动到前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(int row);//移动到指定行

Statement对象

jdbc中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement.executeQuery()方法用于向数据库发送查询语句，executeQuery()方法返回代表查询结果的ResultSet对象

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例：

Statement statement=connection.createStatement();
String sql="insert into user(...) values(...)";
int num=statement.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！！！");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例：

Statement statement=connection.createStatement();
String sql="delete from user where id=1";
int num=statement.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！！！");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例：

Statement statement=connection.createStatement();
String sql="update user set name='' where name=''";
int num=statement.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！！！");
}

CRUD操作-query

使用executeQuery(String sql)方法完成数据查询操作，示例：

Statement statement=connection.createStatement();
String sql="select * from user where id=1";
ResultSet resultSet=statement.executeQuery(sql);
while(resultSet.next()){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

使用Statement对象执行sql语句会存在sql注入的问题

PreparedStatement对象

JdbcUtils.java

package com.example.utils;

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

public class JdbcUtils {
    static String classDriver=null;
    static String url=null;
    static String username=null;
    static String password=null;
    static {
        try {
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties=new Properties();
            properties.load(inputStream);
            classDriver=properties.getProperty("classDriver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public static Connection getConnection(){
        try {
            Class.forName(classDriver);
            Connection connection = DriverManager.getConnection(url, username, password);
            return connection;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试向数据库插入数据：

public class TestInsert{
    public static void main(String[] args){
        Connection connection=null;
        PreparedStatement ps=null;
        try{
            connection=Jdbcutils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql="insert into users values(?,?,?,?,?)";
            ps=connection.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            ps.setInt(1,4);
            ps.setString(2,'qinjiang');
            ps.setString(3,'1232112');
            ps.setString(4,'247734673@qq.com');
            //注意点：sql.Date	数据库	java.sql.Date()
            //		util.Date	Java	new Date().getTime() 获得时间戳
            ps.setDate(5,new java.sql.Date(new Date().getTime()));
            ps.executeUpdate();
        } catch(SQLException e){
            e.printStackTrace();
        } finally{
            JavaUtils.close(connection,ps,null);
        }
    }
}

测试查询：

package com.example.test;

import com.example.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 connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql="select * from grade";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("gradeno="+resultSet.getInt(1)+",gradename="+resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,preparedStatement,resultSet);
        }

    }
}

事务

要么都成功，要么都失败

ACID原则

原则性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致

package com.example.test;

import com.example.utils.JdbcUtils;
import sun.applet.AppletResourceLoader;

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 connection=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            //关闭数据库的自动提交，默认开启事务
            connection.setAutoCommit(false);//开启事务
            String sql1="update account set money=money-500 where name='zhangsan'";
            String sql2="update account set money=money+500 where name='lisi'";
            ps=connection.prepareStatement(sql1);
            ps.executeUpdate();
            int i=1/0;
            ps=connection.prepareStatement(sql2);
            ps.executeUpdate();
            //事务完毕，提交事务
            connection.commit();
            System.out.println("执行成功");
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.close(connection, ps,resultSet);
        }
    }
}

posted @ 2021-10-07 23:26 (x²+y²-1)³=x²y³ 阅读(150) 评论(0) 编辑收藏举报

刷新页面返回顶部

(x²+y²-1)³=x²y³

MySQL详解

MySQL详解

基本的命令行操作

数据库操作

操作数据库（了解）

数据库的列类型

数据库的字段属性

操作数据表（重点）

创建

修改

删除

约束

MySQL数据管理

外键

DML语言（全部记住）

添加（insert）

修改（update）

删除（delete）

DQL查询数据（最重点）

DQL

where条件子句

联表查询

分页和排序

子查询

MySQL函数

常用函数

聚合函数

分组

MD5加密（加密）

事务

索引

索引的分类

测试索引

索引原则

权限管理和备份

用户管理

MySQL备份

规范数据库设计

三大范式（了解）

JDBC（重点）

数据库驱动

JDBC（重点）

事务

公告