数据库学习记录

数据和信息

Data数据
任何描述事物的文字或符号都可以称为数据。
软件开发就是为了收集数据，从中筛选出有用的信息。
信息就是进过分析筛选后的数据。
数据需要保存，保存的介质有内存或硬盘。
内存中的数据是临时的，随着系统的关闭，数据也会消失。
硬盘中的数据是永久，就算系统关闭，数据依然保留。
excel等文件保存数据是一种保存到硬盘中的途径，
如果需要大量数据保存，文件系统就不再方便。
使用一个系统化的数据仓库才能高效地管理数据。

数据库

DataBase ,称为数据库，简称DB
运行在操作系统上，按照一定的数据结构，保存数据的仓库。是一个电子化的文件柜。
数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System，简称DBMS
通常所说的数据库，其实是指数据库管理系统，如MySQL、SQLServer、Oracle等。
是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

总结

数据Data需要永久保存在数据库中
数据库db是运行在操作系统上的一个软件
数据库管理系统DBMS是管理数据库的一个软件
学习数据库就是学习如何使用DBMS创建、使用数据仓库来管理数据

常见的数据库管理系统

关系型数据库

关系型数据库是主流的数据库类型。
数据通过行row和列column的形式(表格)保存。
每行称为一条记录。
每列称为一个字段。
|学号|姓名 |专业
|-1001-|小王–|电子
| 1002| 小李 |电子
字段通常为Java中某个实体类的属性，通过这个类创建的对象，就是一条记录。
如class Employee，有员工编号、部门、工资等属性，
对应数据库中有一张Employee员工表，这张表中有员工编号、姓名、部门等字段。
关系型数据库中，数据表中间进行关联，能快速地查询想要的数据
优点
易于维护：数据都是以表的结构存储，格式一致
使用方便：SQL语句通用，可用于不同的关系型数据库
支持复杂查询：可以通过SQL语句在多个表之间查询出复杂数据
缺点
读写性能差，在处理海量数据时， 频繁读写效率略低
表结构不易改动，灵活度稍欠佳
高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

非关系型数据库的特点

保存数据的格式多样
对于海量数据的读写性能高
不支持复杂查询

常用指令

show databases; 查看所有数据库
use 数据库名; 切换到指定数据库
create database 数据库名; 创建指定数据库
drop database 数据库名; 删除指定数据库
show tables; 在某个数据库中，查看其中的所有表

数据库管理系统图形化管理工具

如果只是使用控制台操作数据库系统很不方便，所以在windows下有很多图形化的管理工具。
如navicat、datagrip、sqlyog等。

SQL语句

Structrued Query Language 结构化查询语言
用于操作关系型数据库的一门语言。可用来创建、维护数据库和数据。

-- mysql中的注释

操作数据库

创建数据库

create database 数据库名;

切换数据库

use 数据库名;

删除数据库

drop database 数据库名;

操作数据库

基础数据表

create table 表名(
字段名1 数据类型 [字段特征],
字段名2 数据类型 [字段特征],
...
字段名n 数据类型 [字段特征]
)

修改数据表

修改表时，要保证不影响现有数据

对表重名

alter table 旧表名 rename to 新表名;

添加新字段

alter table 表名 add column 字段名1 数据类型 [字段特征];

修改字段

alter table 表名 change 旧字段名 新字段名 数据类型 [字段特征];

删除字段

alter table 表名 drop 字段名;

添加约束

添加唯一约束

alter table 表名 add unique(字段名);

添加主键约束

alter table 表名 add primary key(字段名);

添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

添加外键约束

alert table 从表表名 add foreign key(从表外键字段) references 主表表名(主表主键字
段)

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候就设计好约束。

建表的同时添加约束

-- 创建游戏数据库gamedb
create database gamedb;
-- 切换到gamedb
use gamedb;
-- 创建游戏人物表hero
create table hero(
-- 编号id主键 非空 自增
id int not null primary key auto_increment comment '人物编号',
-- 姓名 唯一 非空
name varchar(20) unique not null,
-- 性别 默认男 非空
sex char(1) not null default '男',
-- 价格 非空
price int not null ,
-- 发布时间 可以为空
make_time date
)

删除数据表

drop table 

表名如果要删除有外键关联的表，要先删除从表，再删除主表。

数据完整性

数据完整性是指数据精确可靠。不能保存无意义或无效的数据。
如不合理的年龄性别、全为空的记录、重复记录等。
为了保证保存在数据库中的数据是完整数据，就要在设计数据表的时候添加一些约束或字段征来保证数据的完整性

MySQL中常见的数据类型

约束

操作数据

数据的操作，是指数据的增加create，修改update，查询read和删除delete。简称CURD。

数据添加insert

数据添加时，都是整行添加。不能只给一个字段添加数据。
如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values('值1','值2'...)

1）表名后无需添加字段名，添加时保证值的顺序和数量与表中字段的顺序和数量一致
2）遇到自增字段，不能省略不写，可以使用0、null或default让其填充自增值
3）遇到有默认值的字段，不能省略不写，使用default让其填充默认值
4）遇到允许为空的字段，不能省略不写，使用null让其设置为空

给指定指定赋值

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

1）至少需要将所有非空字段写出来
2）可以不用写出自增字段、有默认值的字段和允许为空的字段
3）表名后的字段顺序要和值的顺序一致

批量添加

可以用一个insert into语句添加多条记录

insert into 表名[(字段1,字段2...)] values
('值1','值2'...),
('值1','值2'...),
...
('值1','值2'...)

数据修改update

修改单个字段的所有值

update 表名 set 字段 = '值';

修改多个字段所有值

update 表名 set 字段 = '值',字段 = '值'... where 条件;

指定值

使用=指定某个字段的值

update 表名 set 字段 = '值',字段 = '值'... where 字段 = '值';

指定范围

1）使用>、<、>=、<=表示范围，使用and、&&、or、||将多个条件关联
2）使用"字段 between A and B"表示字段在[A,B]范围内
3）使用!=或<>表示不等于

指定集合

某个字段的值在某个集合中时: 字段 in (‘值1’,‘值2’…)
某个字段的值不在某个集合中时: 字段 not in (‘值1’,‘值2’…)

空间匹配

某个字段为空: 字段 is null
某个字段不为空: 字段 is not null

模糊查询

%表示未知长度字符串
_表示一个字符

-- 字段 like '%琳%'
-- 带有'琳'字
-- 字段 like '张%'
-- 姓'张'
-- 字段 like '%儿'
-- 以'儿'结尾
-- 字段 like '__'
-- 两个字
-- 字段 like '%的_'
-- 倒数第二个字为'的'

数据删除delete

删除数据是删除一条或多条记录。

删除所有

delete from 表名;
-- 或
truncate table 表名;

1）delete会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增
2）truncate会重置自增列的值。删除所有数据时效率更高
3）如果要删除主从关系且设置了外键的表中的数据。从表中如果有数据，不能直接删除主表中相关联的数据，先删除从表数据，再删除主表数据。

条件删除

delete from 表名 where 条件;

删除时的条件同修改时的条件语句。

数据查询select

查询所有字段

select *from 表名字

查询指定字段

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

字段重命名

select 字段1 as '自定义名称',字段1 '自定义名称'... from 表名;

查询字段条件

-- 查询前N条记录
select * from 表名 limit N;
-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;
-- 每页显示size条记录，第page页
select * from 表名 limit (page-1)*size,size;

条件查询

select * from 表名 where 条件;

排序

select * from 表名 where 条件 order by 排序字段 排序规则,排序字段 排序规则...

1）排序默认是升序
排序规则不写或写asc表示升序
排序规则写desc表示降序
2)排序时如果有条件，排序写在条件语句之后
3）多字段排序时，在order by 之后写多个字段及排序规则，用逗号隔开，按字段顺序排序

统计函数

select 统计函数(字段) from 表名;

数学相关函数

字符串相关函数

时间相关函数

分组

select 分组字段,统计函数 from 表名 group by 分组字段 having 统计函数条件

1）按指定的字段进行分组，会将该字段值相同的记录归纳中同一组中。
2）分组通常配合统计函数使用。
3）如果统计函数作为条件，不能使用where，而要使用having子句，将整个having子句放在分组之后。

group_concat()函数

将分组后的数据拼接成字符串

group_concat(字段或字符串