玩命加载中 . . .

MySQL数据库的学习


学习视频链接:B站 动力节点

一、概述

1、SQL、DB、DBMS分别是:

SQL:结构化查询语言,是一门标准通用的语言。
DB:DataBase(数据库,数据库实际上在硬盘上以文件的形式存在)。
DBMS:数据库管理系统。
DBMS负责执行SQL语句,通过执行SQL语句来操作DB当中的数据。

2、表

    表:table是数据库的基本组成单元。
    一个表包括行和列:
        行:被称为数据/记录(data)
        列:被称为字段(column)
            每一个字段应该包括哪些属性?
                字段名,数据类型,相关的约束

3、SQL语句划分:

    DQL(数据查询语言):查询语句,凡是select语句都是DQL。
    DML(数据操作语言):insert delete update,对表中的数据进行增删改。
    DDL(数据定义语言):create drop alter,对表结构的增删改。
    TCL(事务控制语言):commit提交事务,rollback回滚事务。
    DCL(数据控制语言):grant授权,revoke撤销权限等。

4、MySQL安装

window下使用压缩包安装:

下载地址:https://dev.mysql.com/downloads/mysql/

1、下载完成后,进行解压(不要解压放到含有中文和空格的目录下)。

2、在解压后与bin同目录下新建my.ini文件和data文件夹(8版本以后初始化后会自动生成)。

3、编辑my.ini:

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\mysql-5.7.30-winx64\mysql-5.7.30-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-5.7.30-winx64\mysql-5.7.30-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

#跳过登录密码验证
skip-grant-tables

my.ini必须保存为ANSI格式(配置文件默认就是是ANSI编码格式)

4、设置环境变量。

变量
MYSQL_HOME MySQL解压的文件路径(不含bin)
Path %MYSQL_HOME%\bin

5、以管理员身份打开cmd命令窗口,并将路径切换至mysql下的bin目录,然后输入mysqld -install(安装mysql)。

#安装mysql服务,mysqld --install [服务名称,默认是MySQL]
mysqld install

#卸载mysql服务
sc delete mysql(需要管理员权限)

#移除mysql服务(需要停止mysql)
mysqld -remove

执行安装命令成功后一般会出现Service successfully installed

6、输入mysqld --initialize-insecure --user=mysql初始化数据库。

mysqld  --initialize-insecure --user=mysql

7、开启MySQL服务。

net start mysql

启动成功:MySQL服务已经启动成功。

8、登录MySQL。

mysql -uroot -p

不用输密码,直接回车。

9、登录成功后,修改root密码。

mysql>update mysql.user set authentication_string=password('root') where user='root' and Host='localhost';

修改成功后,输入

flush privileges;

刷新权限。

10、修改my.ini文件。

删除或注释掉最后一句skip-grant-tables

11、重启mysql即可正常使用。

net stop mysql
net start mysql

二、常用命令

1、登录mysql数据库管理系统
    mysql -u<user name> -p<user password>
        (-p后可填写密码,也可不写【回车后输入密码,密码不可见】)
2、查看有哪些数据库
    show databases;
3、创建数据库
    create database <database name>;
4、使用数据库
    use <database name>;
5、查看当前使用的数据库中有哪些表
    show tables;
        查看其它库中的表:
            show tables from <database name>;
6 、初始化数据
    source 文件绝对路径
7、文件以sql结尾,这样的文件称为“sql脚本”。
    当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句,故称这样的文件为sql脚本。
    直接使用source命令可以执行sql脚本。sql脚本中的数据量大的时候,无法打开,需使用source命令初始化。
8、删除数据库
    drop database <database name>;
9、查看表结构
    desc <table name>;
10、查看表中数据
    select * from<table name>;
11、查看当前使用的数据库
    select database();
12、、查看数据库版本
    select version();
13、\c命令,结束一条语句
14、exit或quit命令,退出MySQL
15、查看创建表的语句
    show create table <table name>;

三、简单的查询语句(DQL)

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

字段可以参与数学运算。

1、

任何一条sql语句以`;`结尾。
sql语句不区分大小写。

2、给查询结果重命名

select 字段名1,字段名2,字段名3,... as ### from 表名;
标准sql语句中要求`字符串`使用`单引号`括起来,虽然MySQL支持双引号,建议尽量少使用。
`as`关键字可以省略。

3、查询所有字段

select * from 表名;

四、条件查询

select
    字段,字段,...
from
    表名    
where
    条件;

条件查询需要用到where语句,where必须放到from语句表的后面。支持如下运算符:

运算符 说明
= 等于
<>或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between…and… [左小右大]两个值之间,等同于 >= and <=
is null 为null(is not null 不为空)
and 并且
or 或者
in 包含,相当于多个or(not in不在这个范围中)
not not可以取非,主要用在is或in中
like like称为模糊查询,支持%或下划线匹配
% 匹配任意个字符
_ 下划线,一个下划线只匹配一个字符

1、模糊查询like

%代表任意多个字符,_代表任意一个字符
若查询的数据中存在_,需要转译\_

2、排序(升序、降序)

select
    字段,字段,...
from
    表名    

order by
    需排序的字段 ;

默认是升序,asc表示升序,desc表示降序。执行顺序from->where->select->order by
越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序时,才会启用后面的字段。

五、处理函数

1、单行处理函数

输入一行,输出一行。
函数 作用
lower(字段名) 转换小写
upper(字段名) 转换大写
substr(字段名,起始下标,截叏长度) 取子串
length(字段名) 取长度
trim(‘字符串’) 去除首尾空格,不会去除中间空格
str_to_date(‘日期字符串’,’日期格式’) 将字符串转换成日期
date_format(日期类型数据,’日期格式’) 格式化日期
format 设置千分位
round(数字,保留小数位数) 四舍五入,默认保留整数位
rand() 生成随机数
ifnull(字段名,替换值) 可以将null转换成一个具体的值

在数据库中,只要有null参与数学运算,其结果一定为null。为了防止计算结果出现null,建议先使用ifnull()函数预先处理。

MySql日期格式:
格式符 功能
%Y 代表四位的年份
%y 代表两位的年份
%m 代表月,格式(01 …12)
%c 代表月,格式(1 … 12)
%d 代表日
%H 代表24小时制
%h 代表12小时制
%i 代表分种,格式(00 … 59)
%S或%s 代表秒,格式(00..59)

2、分组函数【多行处理函数】

多行输入,一行输出

所有的分组函数都是对“某一组”数据进行操作,分组函数会自动忽略空值,不需要手劢增加where条件排除空值。

函数 作用
sum(字段名) 求和
avg(字段名) 取平均
max(字段名) 取最大值
min(字段名) 取最小值
count(字段名) 表示统计某个字段中不为null的数据总数量
count(*) 表示取得当前查询表所有记录(统计总记录条数)

3、group by 和 having

select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...

执行顺序:from->where->group by->having->select->order by。

group by 字段名称:按照某个字段或者某些字段进行分组。
having:having是对分组之后的数据进行再次过滤。

分组函数一般都会和group by联合使用,这也是为什么它会被称为分数函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束后才会执行。
当一条SQL语句没有group by的话,整张表的数据会自成一组。

where ...中的...中不能`直接`有分组函数(count sum avg max min);
有 group by 的SQL询句中,select 语句后面只能跟 分组函数 和 参与分组的字段。

若在有group by的SQL询句中,select语句后跟有 除分组相关 的字段,查询结果是有的,但毫无意义。

4、distinct 去除重复记录

作用:将查询结果中某一字段的的重复记录去除掉
用法:distinct 字段名或 distinct 字段名1, 字段名2 … …
distinct 字段名A:去除不字段名A相同的记录
distinct 字段名A,字段名B:去除不字段名A和字段名B同时相同的记录

注意:distinct只能出现在所有字段最前面,后面如果有多个字段则为多字段联合去重。

六、连接查询

1、连接查询的分类

SQL92
SQL99
    内连接
        等值连接
        非等值连接
        自连接
    外连接
        左外连接(左连接)
        右外连接(右连接)
    全连接

2、笛卡尔积现象

若两张表迚行连接查询的时候没有任何条件限制,最终的查询结果总数是两张表记录的乘积,该现象称为笛卡尔积现象(笛卡尔乘积现象)。

增加条件进行过滤,避免笛卡尔积现象。(避免笛卡尔积并不会减少匹配次数,只不过显示有效的记录,故也不会提高效率)

<1>执行效率高;
<2>可读性好。

3、内连接

(1)、等值连接

最大特点:条件是等量关系。

#SQL92
    select
        e.ename,d.dname
    from
        emp e,dept d
    where
        e.deptno = d.deptno;

#SQL99
    select
        e.ename,d.dname
    from
        emp e
    inner join
        dept d
    on
        e.deptno = d.deptno;
//inner可省略

SQL99语法(等值连接):

...
    A
join
    B
on
    连接条件
...

(2)、非等值连接

最大特点:连接条件中的关系是非等量关系。

(3)、自连接

最大的特点:一张表看作两张表。自己连自己。

4、外连接

其最主要的特点:主表的数据无条件全部查询出来。

select
    ...
from
    A
left\right <outer> join
    B
on
    连接条件
where
    ...

(1)、左连接

表示左边的表是主表。

(2)、右连接

表示右边的便是主表。

5、内连接与外连接的区别

假如有两张表,A表和B表。
    <1>内连接:只查询两张表匹配的记录,两张表是平等的。
    <2>外连接:两张表有主副之分,不匹配的记录用NULL填充。
因为内连接与外连接的`inner`与`outer`可以省略,所以区分是内还是外主要看是否有`left\right`,有则为外连接,反之内连接。

6、全连接

包含左、右两个表的全部行,不管另外一边的表中是否存在不它们匹配的行。

七、子查询

select语句当中嵌套select语句,被嵌套的select语句是子查询。
select子句可出现在selectfromwhere关键字后面。

select
    ...<select>
from
    ...<select>
where
    ...<select>

八、union

将查询的结果集合并。
合并结果集的时候,查询字段的个数必须相同。

九、limit(MySQL特有)

获取一表前几条戒中间某几行数据。【分页查询使用】

    limit startIndex, length 表示
        startIndex表示起始位置,从0开始,0表示第一条数据。
        length表示从startIndex+1开始,取length个。

limit在SQL语句中最后执行。

通用的标准分页sql
假设每页显示pageSize条记录:

第pageNo页:(pageNo - 1) * pageSize, pageSize

pageSize表示每页显示多少条记录
pageNo表示显示第几页

十、表【概述中提及】

表(table):是数据库最基本的组成单元,数据库是用来存储数据的,数据库中有很多表,每一个表都是一个独立的单元,表也是一个结构化的文件,由行和列组成,行称为数据库记录,列称为字段。

字段包含:字段名称、字段类型、长度、约束。

数据类型 对应Java类型 描述
char String 定长字符串,存储空间大小固定
varchar StringBuffer/StringBuilder 变长字符串,存储空间等与实际的数据空间
int int 表示整型
bigint long 表示长整型
float(有效数字位数,小数位) float 数值型
double(有效数字位数,小数位) double 数值型
date java.sql.Date 表示日期和时间
BLOB Object Binary Large Object(二进制大对象,存储图片、视频等流媒体信息)
CLOB Object Character Large Obje(字符大对象,存储较大文本)

顺序要求:

        删除数据的时候,先删除子表,再删除父表。
        添加数据的时候,先添加父表,在添加子表。
        创建表的时候,先创建父表,再创建子表。
        删除表的时候,先删除子表,在删除父表。

1、创建表

语法格式:

create table 表名称(字段名 类型(长度) 约束);

表名称建议t_开头。

2、插入数据

语法格式:

insert into 表名(字段1,字段2.字段3,...) value(1,2,3,...);

字段的数量和值的数量相同,并且数据类型要对应相同。
字段可以省略不写,但是value值的顺序、数量必须与表中字段对应。

3、表的复制

语法格式:

create table 表名 as select语句;

将查询结果当作表创建出来。

4、修改数据

语法格式:

update 表名 set 字段1=1,字段2=2,... where 条件;

没有条件整张表数据全部更新。

5、删除数据

语法格式:

delete from 表名 where 条件;

没有条件则将表中的数据全部删除。

删除大表中的数据:

truncate table 表名; // 表被截断,不可回滚。永久丢失。

增删改查有一个术语:CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)

6、增/删/改 表结构

新增:ALTER TABLE 表名 ADD 字段名 字段类型(长度);
修改:ALTER TABLE 表名 MODIFY 字段名 字段类型(长度);
删除:ALTER TABLE 表名 DROP 字段名; 

7、删除表

drop table 表名;
drop table if exists 表名; 

8、约束 Constraint

在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性有效性完整性

常见的约束:

    非空约束(not null):约束的字段不能为NULL。
    唯一约束(unique):约束的字段不能重复。
    主键约束(primary key):约束的字段既不能为NULL,也不能重复。
    外键约束(foreign key):用于在两个表之间建立关系,需要指定引用主表的哪一列。
    检查约束(check):目前MySQL不支持该约束。

(1)非空约束(not null)

not null约束的字段不能为NULL值,必须赋具体数据。
not null约束只有列级约束。没有表级约束。

(2)唯一约束(unique)

唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。
列级约束:字段后使用unique。

create table t_user(
            id int,
            name varchar(255) unique  // 列级约束
        );

表级约束:多个字段联合起来使用unique。

create table t_user(
            id int, 
            username varchar(255),
            sex char(2),
            unique(name,sex) // 多个字段联合起来添加1个约束unique 【表级约束】
        );

(3)主键约束PK(primary key)

主键:

表中某个字段添加主键约束之后,该字段被称为主键字段,主键字段中出现的每一个数据都被称为主键值。

主键的作用:

<1>添加主键primary key的字段即不能重复也不能为空,主键值是这行记录在这张表当中的唯一标识。
<2>表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。

主键的分类
根据主键字段的字段数量划分:
    单一主键
    复合主键(多个字段联合起来添加一个主键,违背三范式)
根据主键性质划分:
    自然主键
    业务主键:主键值和系统的业务挂钩。最好不要拿和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。

一张表的主键只能有一个。
MySQL提供主键值自增:

create table t_user(
        id int primary key auto_increment, //id字段自动维护一个自增的数字,从1开始,以1递增。
        username varchar(255)
    );

insert into t_user(username) values('张三');
insert into t_user(username) values('李四');
select * from t_user;

列级约束

create table t_user(
            id int primary key,
            username varchar(255),
            email varchar(255)
        );

表级约束

create table t_user(
            id int,
            username varchar(255),
            primary key(id)
        );

复合主键(了解即可):
create table t_user(
                id int,
                username varchar(255),
                eamil varchar(255),
                primary key(id,username)
            );

外键约束FK(foreign key)

给某个字段添加外键约束之后,该字段称为外键字段,外键字段中的数据称为外键值。
外键可以为NULL。外键字段引用其他表的某个字段的时候,被引用的字段不一定是主键,但至少具有unique约束。

create table t_class(
        cno int,
        cname varchar(255),
        primary key(cno)
        );

create table t_stu(
        sno int,
        sname varchar(255),
        classno int,
        primary key(sno),
        foreign key(classno) references t_class
        );
t_stu中的classno字段引用t_class表中的cno字段,此时t_stu表叫做子表。t_class表叫做父表。

十一、事务(Transaction)

一个事务是一个完整的业务逻辑单元,不可再分。事务的存在是为了保证数据的完整性,安全性。
和事务相关的语句只有DML语句。[insert delete update]因为这三个语句都是和数据库表当中的“数据”相关的。

事务中存在一些概念:
    事务(Transaction):一批操作(一组DML)
    开启事务(Start Transaction)[关闭自动提交机制。]
    回滚事务(rollback)
    提交事务(commit)
    SET AUTOCOMMIT:禁用或启用事务的自动提交模式。mysql事务默认情况下是自动提交的。

例如银行转账,要么同时成功,要么同时失败。
要想保证同时成功或者同时失败,那么就需要使用数据库的“事务机制”。

1、事务的特性:ACID

A:原子性,事务是最小的工作单元,不可再分。
C:一致性,事务必须保证多条DML语句同时成功或者同时失败。
I:隔离性,事务A与事务B之间具有隔离。
D:持久性,在事务完成以后,该事务对数据库所作的更改将持久地保存在数据库之中,并不会被回滚。

2、事务的隔离级别

事务隔离性存在隔离级别。

理论上隔离级别包括4个:

            第一级别:读未提交(read uncommitted)
                对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
                读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。
            第二级别:读已提交(read committed)
                对方事务提交之后的数据我方可以读取到。
                这种隔离级别解决了: 脏读现象没有了。
                读已提交存在的问题是:不可重复读。
            第三级别:可重复读(repeatable read)
                这种隔离级别解决了:不可重复读问题。
                这种隔离级别存在的问题是:读取到的数据是幻象。
            第四级别:序列化读/串行化读(serializable) 
                解决了所有问题。
                效率低。需要事务排队。

            oracle数据库默认的隔离级别是:读已提交。
            mysql数据库默认的隔离级别是:可重复读。

–脏读取(Dirty Read)
一个事务开始读取了某行数据,但是另外一个事务已经更新了此数据但没有能够及时提交,这就出现了脏读取。
–不可重复读(Non-repeatable Read)
在同一个事务中,同一个读操作对同一个数据的前后两次读取产生了不同的结果,这就是不可重复读。
–幻像读(Phantom Read)
幻像读是指在同一个事务中以前没有的行,由于其他事务的提交而出现的新行。

十二、索引

索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。

创建索引:
create index 索引名称 on 表名(字段名);

删除索引:
drop index 索引名称 on 表名;

A、主键和具有unique约束的字段自动会添加索引。
B、根据主键查询效率较高。尽量根据主键检索。
C、模糊查询时,第一个通配符使用的是%,这时索引是失效的。
D、索引底层采用的数据结构是:B + Tree。

1、添加索引的条件

a、表中该字段中的数据量庞大
b、经常被检索,经常出现在where子句中的字段
c、经常被DML操作的字段不建议添加索引

2、索引的实现原理

通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。

3、索引的分类

单一索引:给单个字段添加索引。
复合索引:给多个字段联合起来添加一个索引。
主键索引:主键上会自动添加索引。
唯一索引:有unique约束的字段上会自动添加索引。
......

4、查看索引

show index from 表名;

查看sql语句的执行情况:
explain select 字段 from 表名 where 条件;

十三、视图(index)

站在不同的角度去看数据。(同一张表的数据,通过不同的角度去看待)

创建视图:
create view 视图名 as select语句;

修改视图:
alter view 视图名 as select语句;

删除视图:
drop view 视图名;

对视图进行增删改查【可以对视图进行CRUD操作】,会影响到原表的数据。

视图的作用:
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。

十四、数据库设计三范式

设计范式:设计表的依据,按照这个三范式设计的表不会出现数据冗余。

1、第一范式

数据库表中不能出现重复记录,每个字段是原子性的不能再分。

2、第二范式

第二范式是建立在第一范式基础上的,另外要求所有非主键字段完全依赖主键,不能产生部分依赖。

多对多?三张表,关系表两个外键。

3、第三范式

建立在第二范式基础上的,非主键字段不能传递依赖于主键字段,不要产生传递依赖。

一对多?两张表,多的表加外键。

4、总结

第一范式:有主键,具有原子性,字段不可分割。
第二范式:完全依赖,没有部分依赖。
第三范式:没有传递依赖。

一对一设计:
    主键共享;
    外键唯一。

在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度,最终用目的要满足客户需求。

十五、DBA命令

1、新建用户

语法格式:

create user 用户名 identified by '密码';
密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

2、授权

语法格式:

grant all 权限 on 数据库名.表名 to '用户名'@'登录ip' identified by '密码' with grant option;

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

1)dbname=*表示所有数据库
2)tbname=*表示所有表
3)login ip=%表示任何ip
4)password为空,表示不需要密码即可登录
5)with grant option;  表示该用户还可以授权给其他用户


 privileges包括:
1)    alter:修改数据库的表
2)    create:创建新的数据库或表
3)    delete:删除表数据
4)    drop:删除数据库/表
5)    index:创建/删除索引
6)    insert:添加表数据
7)    select:查询表数据
8)    update:更新表数据
9)    all:允许任何操作
10)    usage:只允许登录

3、回收权限

语法格式:

revoke privileges on dbname[.tbname] from username;

4、将数据库当中的数据导出

无需登录MySQL。

导出整个库:
mysqldump 数据库名>路径 -u用户名 -p密码

导出指定库中的表:
mysqldump 数据库名.表名>路径 -u用户名 -p密码

5、导入数据

语法格式:

source .sql文件绝对路径

  目录