学习笔记033——MySQL数据库学习笔记

1、MySQL 概述

1.1 什么是数据库？

存储数据

Java SE

1、使用集合来存储数据，缺点：程序一旦重启，数据全部清空，无法做到持久化。

2、XML 文件同样可以保存数据，缺点：存取非常麻烦。

<bean>
	<id>1</id>
</bean>

3、使用 IO 流将数据存储到本地硬盘，txt 文件，word 文件，缺点：数据之间没有结构化的关联关系。

如何解决这个问题，业务数据永久保存且方便存取、结构有序？

数据库来解决这个问题

数据库一种专门的软件（服务）来管理要存储的数据，这些数据按照特定的格式进行存储，通过数据库服务可以非常方便地对数据进行增删改查的操作，提升了数据管理的效率。

这就是数据库管理系统：Database Management System 简称 DBMS。

Database 就是一个存储数据的仓库。

1.2 什么是 MySQL？

MySQL 是目前主流的数据库产品，MySQL 的优势：

• 免费
• 开源
• 跨平台
• 速度快

CRUD：Creat、Read、Update、Delete

1.3 学习数据库需要掌握两部分内容

1、掌握数据库的使用，根据业务对数据完成增删改查操作。

2、设计数据库，根据项目的需求，设计数据表之间的关联关系。

2、MySQL 的使用

1、通过命令行的方式进行操作（-u+用户，-p+密码）

mysql -uroot -p123456

MySQL 是一个服务，在服务可以创建很多数据库，在数据库中创建数据表，数据表存储数据。

类似 Excel 表格

选择数据库

use test1;

查询数据表

select * from user;

2、使用可视化管理工具操作数据库

DataGrip，更加方便地管理数据库

3、数据库存储引擎

存储引擎就是如何存储数据、如何建立索引、如何更新、查询数据等技术的具体实现，也可以称为表的类型。

MySQL 支持多种存储引擎，默认是 InnoDB。

show engines;

MySQL 默认使用 InnoDB 存储引擎，InnoDB 对于事务的处理能力非常强大，同时还支持主键自增，支持外键。

4、SQL 分类

SQL 也是一种编程语言，专门用来处理数据库的。

DML 数据操作语言，操作数据库中存储的数据（insert、update、delete）

DDL 数据定义语言，创建、删除、修改数据库、数据表（create、drop、alter）

DQL 数据查询语言，查询数据库（select）

DCL 数据控制语言，用来控制数据库组件的存取（事务 commit、rollback）

5、创建数据库

create database 数据库名称 default character set utf8 collate utf8_general_ci;

collate 设置数据库的校验字符集，是指当我们对某个字符串类型的数据进行排序查询的时候，数据排序的方式，如何比较两个字符串值的大小关系。

utf8_general_ci：不区分大小写

utf8_bin：区分大小写

B：66

a：97

6、MySQL 数据类型

MySQL 支持的数据类型

6.1 整数类型

数据类型	大小	描述
tinyint	1 byte	非常小的整数
smallint	2 byte	小的整数
mediumint	3 byte	中等大小的整数
int	4 byte	标准的整数
bigint	8 byte	大整数

6.2 小数类型

浮点型、定点型

数据类型	大小	描述
float	4 byte	单精度浮点型
double	8 byte	双精度浮点型

float(M,D) —> float(6,3) —> 总共六位，小数点后3位 —> 如存入：123.456

double(M,D)

M 表示该小数最多需要的十进制有效数字个数

D 表示该小数的小数点后的十进制数字个数

为了确保小数是精确的，MySQL 还提供了定点型数据类型，decimal

6.3 日期和时间类型

数据类型	大小	描述
year	1 byte	年份值
time	3 byte	时间值
date	3 byte	日期值
datetime	8 byte	日期+时间值
timestamp	4 byte	1970-01-01 00：00：01 到现在的毫秒数

6.4 字符串类型

数据类型	大小	描述
char(M)	M个字符	固定长度的字符串
varchar(M)	M个字符	可变长度的字符串
tinytext	2^8-1 byte	非常小的字符串
text	2^16-1 byte	小型字符串
mediumtext	2^24-1 byte	中等大小的字符串
longtext	2^32-1 byte	大型的字符串

6.5 二进制类型

数据类型	大小	描述
bit(M)	M位二进制数据	小的二进制数据
binary(M)	M byte	普通二进制数据
varbinary(M)	0~M 的变长二进制	普通二进制数据
tinyblob	255 byte	大的二进制数据
blob	2^16-1 byte	大的二进制数据
mediumblob	2^24-1 byte	大的二进制数据
longblob	2^32-1 byte	大的二进制数据

7、管理数据库

创建 见5、

删除

drop database 数据库名称;

查看数据库

show databases;

选择数据库

use 数据库名称;

在创建数据表之前，一定要先选择一个数据库，给指定的数据库中创建数据表。

8、管理数据表

服务 —>> 创建数据库 —>> 创建数据表 —>> 完成数据的 CRDU

create table 数据表名称(
	//列信息
    字段名称 数据类型 是否为主键/是否可以为空/默认值
);

8.1 创建数据表

create table student(
    id int,
    name varchar(11),
    age int
);

create table teacher(
    id integer primary key auto_increment,
    name varchar(11) not null,
    score float(2,1)
);
alter table teacher add hobby varchar(11) default '跑步';

8.2 修改数据表

修改数据表的结构，修改字段

1、新增字段

alter table 数据表名称 add 字段名称 数据类型

2、修改字段

alter table 数据表名称 change 旧字段名称 新字段名称 数据类型

3、删除字段

alter table 数据表名称 drop 字段名称

给字段设置默认值

alter table course alter column id set default 123;

8.3 删除数据表

drop table 数据表名称;

8.4 查看数据表

show tables;

8.5 查看数据表结构

desc 数据表名称;

9、数据管理

增删改查

CRUD：Create、Read、Update、Delete

9.1 增

insert into 数据表(字段列表) values(值列表)

insert into student(id, name, age) VALUES(1,'张三',20);

同时添加多条记录

insert into student(id, name, age) VALUES(1,'张三',20),(2,'李四',21),(3,'王五',22);

省略字段列表，就相当于添加了全部的字段

insert into student VALUES(2,'张三2',20);

9.2 删

delete from 数据表;

直接删除会提示警告：

因为这样操作会直接删除表中的所有数据，所以会弹出警告，提示用户操作。

条件删除

delete from teacher where id = 3;

9.3 改

update 数据表 set 字段名=字段值...;

直接修改同样会弹出警告，提示用户这样的操作会修改表中所有数据。

条件修改

update teacher set hobby = '足球' where id = 2;

9.4 查

select 字段列表 from 数据表;

select id,name,score,hobby from teacher;

通配符，* 表示所有字段

select * from teacher;

条件查询

select * from teacher where name = '张三';
select * from teacher where score >2.2;

10、SQL 函数

数学函数、日期函数、字符串函数、聚合函数

10.1 数学函数

求绝对值 abs()

select abs(-1);

向下取整 floor()，返回小于参数的最大整数

select floor(66.99);

向上取整 ceil()，返回大于参数的最小整数

select ceil(88.0001);

10.2 字符串函数

添加数据 insert(s1,index,len,s2)

在查询的结果中对字符串内容进行添加数据的修改。 —>>只针对英文字母有效

s1 是目标字符串，index 是下标，从 1 开始，len 是添加的长度，s2 是追加的内容

insert into teacher (id, name, score) values (4,'abcde',3.8);

select insert(name,2,2,'MySQL') from teacher where id = 4;

upper() 将字母转为大写。 —>>只针对英文字母有效

select upper(insert(name,2,2,'MySQL')) from teacher where id = 4;

lower() 将字母转为小写。 —>>只针对英文字母有效

select lower(insert(name,2,2,'MySQL')) from teacher where id = 4;

left(s,len) 返回 s 字符串的前 len 个字符。 —>>只针对英文字母有效

select left(name,3) from teacher where id = 4;

right(s,len) 返回 s 字符串的后 len 个字符。 —>>只针对英文字母有效

select right(name,2) from teacher where id = 4;

substring(s,index,len) 截取 s 字符串，从 index 开始，长度为 len。 —>>只针对英文字母有效

select substring(name,1,3) from teacher where id = 4;

reverse() 反序输出。 —>>只针对英文字母有效

select reverse(name) from teacher where id = 4;

日期函数

curdate() 获取当前日期

select curdate();

curtime() 获取当前时间

select curtime();

now() 获取当前日期+时间

select now();

datediff(d1,d2) 计算 d1 和 d2 之间间隔的天数

select datediff('2020-03-31','2021-05-01');

adddate(d,n) d 日期累加 n 天之后的日期

select adddate('2020-03-31',100);

subdate(d,n) d 日期之前 n 天的日期

select subdate('2020-03-31',100);

聚合函数

count() 根据某个字段统计总记录数

select count(id) from course;

sum() 计算某个字段值的总和

select sum(id) from course;

avg() 求某个字段值的平均值

select avg(id) from course;

max() 求某个字段值的最大值

select max(id) from course;

min() 求某个字段值的最小值

select min(id) from course;

11、条件查询

分组查询

select name,avg(score) from course group by name;

排序查询，默认是升序排列

降序 select * from course order by score desc;

升序 select * from course order by score asc;

12、MySQL 运算符

算术运算符

执行运算符：加减乘除

select score+60 from course;

比较运算符：大于、等于、小于、不等于，返回 1 表示 true、返回 0 表示 false

select score>80 from course;

逻辑运算符：与 或 非，将两个比较运算表达式进行逻辑运算

select score>80 && level<10 from course;

select score>80 || level<10 from course;

select !(score>80 || level<10) from course;

特殊运算符

is null 判断值是否为空

select name is null from course;

between and 判断值是否在某个区间之内

select score between 90 and 100 from course;

in 判断值是否在某个确定的集合之内

select score from course where id in (1,2,3);

like 模糊查询

select * from course where name like '%电脑%';

select * from course where name like '电脑%';

select * from course where name like '%电脑';

13、主键

表中的一个字段，该字段的值是每一行数据的唯一标识。

默认情况下，每张表都要有一个主键，也只能有一个主键。

主键生成策略：代理主键，与业务无关的字段，仅仅是用来标识一行数据，一般定义为 int 类型，因为 int 类型存储空间小，同时可以设置自增，避免主键冲突问题。

主键值必须是唯一，不能有重复。

create table user(
    id int primary key auto_increment,
    name varchar(11)
);

14、外键

表中的某个字段设置为外键，与另外一张表的主键进行关联，从而将两张表的数据建立级联关系。

创建主键

create table orders(
    id int primary key auto_increment,
    name varchar(11),
    uid int,
    foreign key(uid) references user(id)
);

外键的取值必须是主键中已经存储的值，如果是主键中没有的值，则外键无法存储。

A 表的主键和 B 表的外键建立约束关系之后，B 表外键的值就需要被 A 表的主键值所约束，只能从 A 表中获取已经存在的值存入 B 表的外键，所以 B 表就是从表，A 表就是主表。

删除主键

alter table 数据表 drop foreign key 外键名称;

15、数据表关系

1、一对一

2、一对多

3、多对多

A 表 B 表

一对一：A 中一条记录对应 B 中的一条记录，B 中一条记录对应 A 中的一条记录。

一对多：A 中一条记录对应 B 中的多条记录，B 中一条记录对应 A 中的一条记录。

多对多：A 中一条记录对应 B 中的多条记录，B 中一条记录对应 A 中的多条记录。

一对一：每个人和他的身份证号

一对多：班级和学生

多对多：学生和选课

实际开发中，一对一关系用的很少，更多是一对多和多对多。

一对多

use test2;
create table class(
    id int primary key auto_increment,
    name varchar(11)
);

create table student(
    id int primary key auto_increment,
    name varchar(11),
    cid int,
    foreign key(cid) references class(id)
);

多对多

use test2;
create table user(
    id int primary key auto_increment,
    name varchar(11)
);

create table course(
    id int primary key auto_increment,
    name varchar(11)
);

create table user_course(
    id int primary key auto_increment,
    uid int,
    cid int,
    foreign key(uid) references user(id),
    foreign key(cid) references course(id)
);

16、多表关联查询

• 嵌套查询，子查询

查询张三的信息，包括个人信息和所在班级信息

select * from class where id = (select cid from student where name = '张三');

• 连接查询

  • 内连接

    select * from student inner join class where student.name = '张三' and cid = class.id;
    

    select s.id sid,s.name sname,c.id cid,c.name cname from student s,class c where s.name = '张三' and cid = c.id;
    

  • 外连接

    • 左连接：左表所有数据和右表满足条件的数据

    select * from student s left join class c on s.cid = c.id and s.id = 1;
    

    

    • 右连接：右表所有数据和左表满足条件的数据

    select * from student s right join class c on s.cid = c.id and s.id = 1;
    

    

  多对多关系级联查询

  select u.id uid,u.name uname,c.id cid,c.name cname from user u,course c,user_course uc where u.id = uc.uid and c.id = uc.cid
  and u.id = 1;
  

17、数据库索引 Index

数据库结构，可以用来快速查询数据表中特定的记录，提高数据库查询效率的重要方式，索引是直接添加到字段上的。

索引包括：普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引

创建维护索引需要消耗时间，索引也需要占用物理空间。

主键自带索引，可以给其他字段添加索引

索引设计原则：

1、出现在 where 语句中的列，而不是 select 后面的列

2、索引的值，尽量唯一，效率更高

3、不要添加过多索引，维护成本很高

添加索引

alter table test add index in_id(id);
create index in_id on test(id);

删除索引

alter table test drop index in_id;
drop index in_id on test;

18、数据库视图 View

数据库中一张虚拟的表，允许不同用户或者应用程序以不同的方式查看同一张表中的数据。

创建视图

create view view_common as select id,name from user;

使用视图

select * from view_common;

删除视图

drop view view_common;

19、触发器 Trigger

触发器中定义了一系列操作，可以在对指定表进行插入、更新、删除的时候自动执行这些操作，完成对目标表的管理。

创建触发器

create trigger t_afterinsert_on_tab1
    after insert on tab1
    for each row
    begin
        insert into tab2(tab2_id) values(new.tab1_id);
    end;

create trigger t_afterdelete_on_tab1
    after delete on tab1
    for each row
    begin
        delete from tab2 where tab2_id = old.tab1_id;
    end;

删除触发器

drop trigger t_afterinsert_on_tab1;
drop trigger t_afterdelete_on_tab1;

20、存储过程 Procedure

存储过程是一组为了完成特定功能的 SQL 语句的集合，存储在数据库中的，用户通过指定存储过程的名字和参数进行调用。

存储过程相当于是定义在 MySQL 中的方法，开发者可以直接调用。

优点：

1、只需要创建一次，就可以任意调用。

2、执行速度更快。

3、更好的安全机制。

参数：1、输入输出类型、参数名称、参数数据类型

入参：Java 方法中的参数

出参：Java 方法中的返回值

入参 SQL

创建存储过程

create procedure add_name(in target int)
begin
    declare name varchar(11);
    if target = 1 then
        set name = 'MySQL';
        else
        set name = 'Java';
    end if;
    insert into test(name) values(name);
end;

调用存储过程

call add_name(1);

删除存储过程

drop procedure add_name;

出参 SQL

创建存储过程

create procedure count_of_student(out count_num int)
begin
    select count(*) into count_num from student;
end;

调用存储过程

call count_of_student(@count_num);
select @count_num;

删除存储过程

drop procedure count_of_student;