从零开始学MySQL

MySQL 概述

什么是数据库 ？

存储数据

Java SE

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

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

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

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

数据库来解决这个问题

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

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

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

什么是 MySQL？

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

• 免费
• 开源
• 跨平台
• 速度快（称得上目前运行速度最快的主流数据库之一）

CRUD : Creat、Read、Update、Delete

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

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

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

MySQL 的使用

1、通过命令行的方式进行操作

mysql -uroot -proot   # 完成一个登录

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

类似 Excel 表格

选择数据库

use demo01;

查询数据表

select * from user;

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

DataGrip

数据库存储引擎

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

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

show engines;

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

SQL 分类

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

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

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

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

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

创建数据库

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

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

utf8_general_ci ：不区分大小写

不区分大小写，按照字母的顺序，a,b,c,d…

utf8_bin ：区分大小写

按照字母的 ASCII 码进行排序

MySQL 数据类型

MySQL 支持的数据类型

整数类型

数据类型	大小	描述
tinyint	1 byte(一个字节)	非常小的整数
samllint	2 byte	小的整数
mediumint	3 byte	中等大小的整数
int	4 byte	标准的整数
bigint	8 byte	大整数

小数类型

浮点型、定点型

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

float(M,D)

double(M,D)

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

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

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

比如说一个小数 3.67，它是把整数位 3 和小数位 67 分别存储起来

使用方法一样：decima(M,D)，知识存储方式不一样而已

日期和时间类型

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

timestamp 是不断变化的，所以可以应用到不重复唯一的应用上

字符串类型

数据类型	大小	描述
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	大型的字符串

二进制类型

数据类型	大小	描述
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	大的二进制数据

管理数据库

创建

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

删除

drop database 数据库名;

查看数据库

show databases;

选择数据库

use 数据库名称;

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

管理数据表

服务 —》创建数据库 —》创建数据表 —》完成数据的 CRUD

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

创建数据表

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

修改数据表

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

1、新增字段

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

2、修改字段

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

3、删除字段

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

给字段设置默认值

alter table course alter column id set default 123;

删除数据表

drop table 数据表名称;

查看数据表

show tables;

查看数据表结构

desc 数据表名称;

数据管理

增删改查

CRUD create、read、update、delte

增

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

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

同时添加多条记录

insert into student(id,name,age) values(1,'张三',20),(2,'李四',21),(3,'王二麻子',25);

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

insert into student values(1,'张三',20);

删

delete from 数据表;

直接删除会提示警告：

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

条件删除

delete from student where id = 3;

改

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

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

条件修改

update student set name = '张三', age = 23 where id = 2;

查

select 字段列表 from 数据表;

select id,name,age from student;

通配符，*表示所有字段

select * from student;

条件查询

select * from student where age > 18;

SQL 函数

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

数学函数

求绝对值 abs()

select abs(score) from course where id = 1;

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

select floor(score) from course where id = 1;

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

select ceil(score) from course where id = 1;

字符串函数

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

在查询的结果中对字符串内容进行添加数据的修改

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

select insert(name, 2, 2, 'Java') from course where id = 1;

数据库中原本保存的数据并不会变，只是在查询出来的数据上做了修改

upper() 将字母转为大写

select upper(name) from course where id = 1;

lower() 将字母转为小写

select lower(name) from course where id = 1;

left(s, len) 返回 s 字符串的前 len 个字符

select left(name, 2) from course where id = 1;

rigth(s, len) 返回 s 字符串的后 len 个字符

select right(name, 2) from course where id = 1;

substring(s,index,len) 截取 s 字符串，从 index 开始(下标从 1 开始)，长度为 len

select substring(name,1,2) from course where id = 1;

reverse() 反序输出

select reverse(name) from course where id = 1;

日期函数

curdate() 获取当前日期

select curdate();

curtime() 获取当前时间

select curtime();

获取当前 日期+时间

select now();

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

select datediff('2021-7-31','2021-8-30');

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

select adddate('2021-8-30',45);

subdate(d,n) d 日期之前 n 天的日期(相当于 adddate 的 n 传一个负数)

select subdate('2021-8-30',45);

聚合函数

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

select count(id) from course;

sum() 计算某个字段值的总和，只能加数字类型，字符串类型为 0

select sum(id) from course;

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

select avg(id) from course;

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

select max(id) from course;

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

select min(id) from course;

条件查询

分组查询

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

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

降序 : select * from course order by score desc;

升序 : select * from course order by score asc;

MySQL 运算符

算数运算符

执行运算符：加减乘除

select score+60 from course;

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

select name, score>80 from course;

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

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

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

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

特殊运算符

is null 判断值是否为空

select name is null from course;

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

select score between 83 and 100 from course;

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

select * from course where id in (4, 5, 6);

like 模糊查询

查询所有包含电脑

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

电脑开头

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

电脑结尾

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

主键

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

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

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

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

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

外键

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

创建外键

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 外键名;

alter table orders drop foreign key orders_ibfk_1;

表已经存在的情况下添加外键

alter table orders add foreign key(uid) references user(id);

数据表关系

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)
);

多对多

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)
);

多表关联查询

• 嵌套查询，子查询

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

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

• 连接查询

  • 内连接

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

    可以简化，可以给列名和表名起别名，inner join 可以省略掉，之间加一个,即可

    起别名

    select s.name as sname from student as s;
    select s.name sname from student s;
    

    简化后的内连查询

    select s.id sid,s.name sname,c.id cid,c.name cname from student s, class c where s.name='张三' and s.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 uc.cid = c.id and u.id = 1;
  

  # 数据库索引 Index
  

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

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

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

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

索引设计原则：

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

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

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

添加索引

alter table user_course add index in_uid(uid);
-- 第二种方式
create index in_id on test(id);

删除索引

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

数据库视图 View

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

创建视图

create view view_common as select id,name from user;

就会创建出视图，只有 id 和 name ，没有 money，而且当 user 表中的数据更新时视图会自动更新，往视图里面添加数据，其他表也会同步添加进来

创建所有字段的视图

create view view_all as select * from user;

使用视图

select * from view_common;

删除视图

drop view view_common;

触发器 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;

最新创建的 id 用 new.tab1_id，最新删除的 id 用 old.tab1._id

删除触发器

drop trigger t_afterinsert_on_tab1;
drop trigger t_afterdelete_on_tab1;

存储过程 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);
call add_name(2);

删除存储过程

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;