学习视频链接: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()函数预先处理。
格式符 | 功能 |
---|---|
%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子句可出现在select
、from
、where
关键字后面。
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文件绝对路径