华清远见-重庆中心-数据库阶段技术总结

华清远见-重庆中心-数据库阶段技术总结

数据和信息

Data数据

任何描述事物的文字和符号的都可以称为数据。

软件开发就是为了收集数据，从中筛选出有用的信息。

Infomation信息

数据经过处理之后得到的内容称为信息。

数据需要保存，保存的介质有内存和硬盘等。

内存中的数据是临时的，随着系统的关闭，数据也会消失；

硬盘中的数据是永久的，就算系统关闭，数据依然保留。

excel等文件保存数据是一种保存到硬盘中的途径，

但是如果大量数据要保存，文件系统就不再方便。

使用一个系统化的数据仓库才能更高效地处理数据。

数据库

DataBase，简称为DB

运行在操作系统上，按一定的数据结构，保存数据的仓库。是一个电子化的文件柜。

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System，简称为DBMS。

通常所说的数据库，是指数据库管理系统，如MySQL、Oracle等。

是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

注意

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

常见的数据库管理系统

关系型数据库

关系型数据库是主流的数据库类型。

数据通过行row和列column的形式(表格)保存。

每行称为一条记录。

每列称为一个字段。

字段通常为Java中某个类的属性，通过这个类创建的对象，就是一条记录。

如class Employee，有员工编号、姓名、部门、工资等属性，

对应数据库中有一个Employee表，这个表中有员工编号、姓名、部门、工资等字段。

关系型数据库，数据表直接有关联，能快速地查询出想要的数据。

关系型数据库的特点

• 优点

  • 易于维护：都是使用表结构存储数据，格式一致
  • 使用方便：SQL语句通用，可用于不同关系型数据库
  • 复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

• 缺点

  • 读写性能差，尤其是还是数据的高效读写
  • 固定的表结构，灵活度少欠
  • 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

数据通过对象的形式保存，对象可以是一个键值对、文档、图片等。

非关系型数据库的特点

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

MySQL的使用

MySQL控制台常用命令

• 查看所有数据库

  show databases;
  

• 切换数据库

  use 数据库名;
  

• 查看当前数据库下的所有表

  show tables;
  

• 创建一个数据库

  create database 数据库名;
  

• 删除数据库

  drop database 数据库名;
  

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

如果只是用控制台操作数据库系统不方便，所以有很多图形化的管理工具。

如navicat、datagrip、sqlyog等。

使用

连接指定的MySQL。

由于现在是自己的电脑链接自己，所以主机名是localhost，之后会连接真实的ip地址。

进入主界面后，点击左上角的连接，选择mysql，只需输入密码即可。

创建数据库

在连接上点击右键，新建数据库，只需写数据库名。

切换数据库

只需双击对应的数据库即可。

创建数据表

在展开后的数据库中，在表的选项上右键新建表。

保存时会提示输入表名。

创建表时的注意实现

• 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录。整个主键列中的数据不能重复，通常还需要设置为自增

• 数据类型和所占长度根据实际情况选择

• 通过"不是null"设置能否允许该字段为空

• 如果某个字段有默认值，可以在设计表的时候设置，字符串用单引号引起来

• 最好写上注释

修改数据表

如果要对某张已存在的表进行修改，在对应的表上右键，设计表。

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

SQL

Structrued Query Language 结构化查询语言

用于操作关系型数据库的一门语言。可以用来创建、维护数据库和数据。

-- mysql中的注释

操作数据库

1.创建数据库

create database 数据库名;

2.切换数据库

use 数据库名;

3.删除数据库

drop database 数据库名;

操作数据表

1.创建数据表

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

2.删除数据表

drop table 表名;

3.修改数据表

• 对表重命名

  alter table 旧表名 rename to 新表名;
  

• 添加新字段

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

• 修改字段

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

• 删除字段

  alter table 表名 drop 字段名;
  

添加约束

1.添加非空约束

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

2.添加主键约束

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

3.添加唯一约束

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

4.添加默认值约束

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

5.添加外键约束

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

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

建表的同时添加约束

-- 创建数据库gamedb
create database gamedb;
-- 切换数据库
use gamedb;
-- 创建游戏角色表hero
create table hero(
-- 编号 整型 非空     主键        自增 
	id    int  not null primary key auto_increment comment '编号',
-- 姓名 字符串     非空 		唯一	
	name varchar(20) not null unique comment '姓名',
-- 定位	   字符串			 非空 	
	position varchar(20) not null comment '定位',
-- 性别 字符串 非空	 默认男
	sex char(1) not null default '男' comment '性别',
-- 价格 整型 非空		 默认4800 
	price int not null default '4800' comment '价格',
-- 上架日期 	
	shelf_date date  comment '上架日期'
)

-- 创建战斗表battle
create table battle(
	hero_id int not null ,
	position varchar(20),
    -- 外键  hero_id参考hero表中的id字段
	foreign key (hero_id) references hero(id)
)

数据完整性

数据完整性是指数据精确可靠。不能保存无意义或无效的数据。

如不合理的年龄、全为空的记录、重复记录等。

为了保证保存在数据库中的数据是完整数据，就要在设计数据表时添加一些约束或特征来保证数据完整性。

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度,精度)	指定保留的小数位数和整体宽度	如decimal(4,2) 3.1415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，旧占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss，8表示yyyyMMdd

约束

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key)。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段;如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用于设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值只能来自于主表中的某个主键字段	foreign key references

操作数据

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

简称为CURD。

数据添加insert

数据添加时，都是整行(一条记录)添加。不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

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

• 表名后无需添加字段名，添加时保证值的顺序和字段的顺序一致
• 遇到自增字段，不能省略不写，要使用0、null或default让其自动填充
• 遇到有默认值的字段，不能省略不写，要使用default让其自动填充默认值
• 遇到允许为空的字段，不能省略不写，要使用null让其设置为空

给指定字段赋值

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

• 没有默认值的非空字段必须要写出来
• 表名后的字段顺序要和值的顺序一致

批量添加

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

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

• 可以省略表名后的字段名
• 值的顺序和字段的顺序一致
• 如果一次添加多条记录时，优先使用批量添加，效率更高

数据修改update

修改单个字段的所有值

update 表名 set 字段 = 值;

修改多个字段的所有值

update 表名 set 字段1 = '值',字段2 = '值'...

根据条件修改(where子句)

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

指定值

update 表名 set 字段 = '值' where 字段 = '值'

指定范围

• 使用>、<、>=、<=表示范围，使用and、or、&&、||将多个条件关联

  update 表名 set 字段 = '值' where 字段
  

• 使用"字段 between 值1 and 值2"表示字段在闭区间[值1，值2]

  update 表名 set 字段='值' where 字段 between 值1 and 值2
  

• 使用!=或<>表示不等于

  update 表名 set 字段='值' where 字段<>值
  

指定集合

• 在某个集合中 in

  update 表名 set 字段='值' where 字段 in ('值1','值2'...)
  

• 不在某个集合中 not in

  update 表名 set 字段='值' where 字段 not in ('值1','值2'...)
  

空值

• 使用is null表示空

  update 表名 set 字段 = '值' where 字段 is null
  

• 使用is not null表示非空

  update 表名 set 字段 = '值' where 字段 is not null
  

模糊查询

-- 字段 like '%娜%'
-- 带有'娜'字

-- 字段 like '张%'
-- ‘张’字开头

-- 字段 like '%儿'
-- ‘儿’字结尾

-- 字段 like '%瑞_'
-- 倒数第二个字为‘瑞’

-- 字段 like '___'
-- 3个字

update 表名 set 字段 = '值' where 字段 like ‘%文字%’

数据删除delete

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

删除所有

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

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

条件删除

delete from 表 where 条件

删除时的条件同修饰时的条件语句

数据查询select

查询所有字段

select * from 表名;

查询指定字段

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

字段重命名

select 字段1 as '重命名',字段2 '重命名'... from 表名;

查询指定条数

-- 查询前N条记录
select * from 表名 limit N;

-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;

-- 每页显示size条，第page页
select * from 表名 limit (page-1)*size,size

去重复

select distinct 字段名 from 表名;

条件查询

where子句，语法同修改、删除时的where

select * from 表名 where 条件;

排序

select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

• 排序可以是升序或降序
  • 默认不写是升序asc
  • 降序需要写desc
• 排序时如果有条件，where条件写在表名之后，排序之前
• 多字段排序时，在order by之后写多个字段及排序规则，用逗号隔开
  • 按字段顺序优先排序

统计函数(聚合函数)

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

函数名
count(字段名)	统计数量
sum(字段名)	求和
avg(字段名)	平均
max(字段名)	最大
min(字段名)	最小

数学相关函数

函数	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入
ceil(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

函数
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去除字符串首尾/首/尾空格
left(字符串或字段,n)/right(字符串或字段,n)	从字符串左/右开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	得到字符串2在字符串1中出现的顺序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2…)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符串

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

分组

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

按指定的字段进行分组，会将该字段值相同的记录归纳到同一组中。

分组通常配合统计函数使用。

如果统计函数作为条件，不能写在where之后，要写在having之后，

having子句放在分组之后。

-- 按图书作者分组，查询平均价格大于50的信息
select book_author,avg(book_price) from book_info 
group by book_author
having avg(book_price)>50

group_concat()函数

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

group_concat(字段1或字符串,字段2或字符串...)

select group_concat(字段1,字段2...) from 表名 group by 分组字段
-- 根据图书类型分组，查看每组下的图书名和作者
select group_concat(book_name,'--',book_author) from book_info group