day44-数据库（补充）

一，数据库

1.1 概念

数据库就是存储数据的仓库

数据库可以实现数据的持久化存储

数据库的本质是文件系统

1.2 DBMS

Database Management System 数据库管理系统

常见的DBMS：

MySQL：Oracle公司的中小型数据库，从6版本开始收费

Oracle：Oracle公司的大型数据库

DB2：IBM公司的大型数据库

SQLServer：Mircosoft微软公司的大型数据库

SQLite：移动端的嵌入式的小型数据库

Java中主要使用的是MySQL和Oracle

1.3 数据库的结构

一个DBMS中可以维护管理多个数据库

一个数据库由若干张表组成

一张表包含若干条记录

一条记录包含若干字段

数据库中的表相当于代码中的实体类：

例如：要研究护士这个实体类，在对护士对象进行数据管理时就应该有一张对应的护士表

数据库中的字段（列）相当于代码中实体类的属性：

例如：宠物具有寿命属性，在宠物表中就应该定义寿命字段

根据类创建出的对象相当于数据库表中的一行（一条记录）：

例如：当我们获取了一个卡车对象时，我们就能够知道该卡车所具有的所有信息；同样当我们查询到一条指定的卡车记录时也就可以明确该卡车的所有信息

1.4 SQL语言

结构化查询语言 Structured Query Language

分类：

DDL：数据定义语言（Data Definition Language）

针对数据库、表进行创建、修改、删除等操作

关键字：create、alter、drop等

DML：数据操作语言（Data Manipulation Language）

针对表中记录、字段进行操作

关键字：insert、delete、update等

DQL：数据查询语言（Data Query Language）

对数据库的表、记录、字段进行查询

关键字：select、where、in、order by、limit、group by、having等

DCL：数据库控制语言（Data Control Language）

对数据库的安全级别和访问权限进行管理的

关键字：revoke、grant、commit、rollback等

1.5 mysql软件

1.5.1 卸载

在控制面板进行软件卸载

删除mysql安装路径下的mysql文件夹

找到C盘下的ProgramData文件夹，删除其中的mysql文件夹

1.5.2 安装

根据安装图解进行安装

注：安装目录必须是纯英文路径的

1.5.3 登录数据库

方式一：

1. 打开cmd

输入mysql -uroot -p1234

方式二：

打开cmd

输入mysql -uroot -p

再输入密码（此时密码会以星号显示）

方式三：

找到安装mysql后，软件所提供的的mysql command line client

输入密码

二，SQL

C（create）R（read）U（update）D（delete）

2.1 库的操作

建库：
create database 库名;
判断数据库是否存在，不存在才创建：
create database if not exists 库名;
查看已有的数据库：
show databases;
删库：
drop database 库名;
判断数据库是否存在，存在才删除：
drop database if exists 库名;
创建数据库的同时指定编码格式：
create database 库名 character set 编码格式;
修改数据库的编码格式：
alter database 库名 character set 编码格式;
查询数据库的创建语句及编码格式：
show create database 库名;
指定要使用的数据库：
use 库名;

2.2 表的操作

查看数据库中的表：
show tables;
建表：
create table 表名(字段名 字段类型 [约束], 字段名 字段类型 [约束], ...);
判断表是否已存在，不存在才创建：
create table if not exists  表名(字段名 字段类型 [约束], 字段名 字段类型 [约束], ...);
删表：
drop table 表名;
判断表是否已存在，存在才删除：
drop table if exists 表名;
复制表结构：
create table 新表名 like 被复制的表名;
修改表名：
alter table 旧表名 rename to 新表名;
查看表结构：
desc 表名;

修改表结构：

添加新字段

alter table 表名 add 新字段 字段类型 [约束];

删除字段
```
alter table 表名 drop 字段;
```

修改字段类型

alter table 表名 modify 字段 类型;

修改字段名

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

2.3 记录、字段的操作

查询表中所有记录：
select * from 表名;
*表示所有字段（列）
添加记录：
向所有字段添加数据
insert into 表名 values(值1, 值2, 值3, ...);
向指定字段添加数据
insert into 表名 (字段1,字段2,...) values (值1,值2,...);
批量添加（所有字段）
insert into 表名 values (值1, 值2, 值3, ...),(值1, 值2, 值3, ...),...;
批量添加（指定字段）
insert into 表名 (字段1,字段2,...) values (值1,值2,...),(值1,值2,...),...;
删除记录：

删除表中所有记录
delete from 表名;
删除表中所有记录
truncate table 表名;
根据条件删除指定记录
delete from 表名 where 条件;
修改记录：

根据条件修改指定记录
update 表名 set 字段名1 = 字段值1,字段名2 = 字段值2,... where 条件;
修改所有记录
update 表名 set 字段名1 = 字段值1,字段名2 = 字段值2,...;

注：

添加记录时，值的列数必须与字段列数必须一致

添加记录时，值的类型必须与字段类型一致

除了数值类型，其他类型的值必须使用单/双引号括起来

"delete from 表名" 该语句在删除时是将表中数据逐行删除，指的是表中有多少条记录，就会执行所少次delete动作；"truncate table 表名" 该语句在删除时是先将整个表删除，再构建一个结构一样的空表。

2.4 数据类型

2.4.1 数值类型

int：整数类型

double：小数类型

例如：salary double(n,m)

n：表示该字段值整数部分小数部分最多有n位

m：表示该字段小数部分最多保留m位

salary double(6 , 2)：此时该salary的最大值是9999.99

2.4.2 日期和时间类型

datetime：包含年月日时分秒格式：YYYY-MM-DD HH:MM:SS

timestamp：时间戳类型包含年月日时分秒格式：YYYY-MM-DD HH:MM:SS

注：

如果某字段的类型是timestamp，而我们不给它赋值或者给它赋值为null，那么系统会给它一个默认值（当前时间）

timestamp不能为空

设置时分秒前必须先设置年月日

时间值需要根据指定格式进行设置

2.4.3 字符串类型

varchar：

例如：name varchar(20)：表示姓名字段的值最大20个字符

注：数字、字母、汉字都占1个字符

2.5 查询

2.5.1 基础查询

查询全部
select * from 表名;
根据指定字段查询
select 字段名1,字段名2,... from 表名;
去重查询
select distinct 字段名 from 表名;
例如：对班级去重
select distinct class from student;

2.5.2 条件查询

在where关键字后跟上条件，查询时根据条件进行筛选

逻辑运算符

and、or、not

&&、||、!

关系运算符

大于、大于等于、小于、小于等于、等于(=)、不等于(!=、<>)

例如：查询"帅哥班"以外的班级中成绩超过80的同学
select * from student where score > 80 and class != '帅哥班'
指定范围之内

between ... and ...

注：含头含尾

例如：查询成绩在80~100之间的同学
select * from student where score between 80 and 100;
在指定列表中

in (值1，值2，值3，...)

不在指定列表中 not in

例如：查询成绩不是58,68,78,88的同学
select * from student where score not in (58,68,78,88);
空和非空

判断为空 is null

判断不为空 is not null

例如：查询成绩栏为空的同学
select * from student where score is null;
模糊查询

like

占位符：

_：单个任意字符

%：任意个任意字符

例如：查询姓“卢”，单名一个字的同学
select * from student where name like '卢_';
例如：查询名字中含“猪”的同学
select * from student where name like '%猪%';

2.5.3 排序查询

使用关键字order by

排序方式：

升序：默认，asc

降序：desc

例如：对员工的销售额进行降序排列
select * from emp order by sales desc;
例如：升序查询所有女性员工的工资
select * from emp where sex = "女" order by money asc;
注：如果多个条件进行排序，只有当前一个条件的值相同时，才会比较第二个条件

格式：order by 排序字段1 排序方式，排序字段2 排序方式，...

例如：以工资降序的方式查询所有员工，如果工资相同则按销售额升序排列
select * from emp order by money desc, sales;

2.5.4 聚合函数

概念：将一列数据作为一个整体，进行纵向的计算。

avg()：计算平均值

sum()：求和

max()：求最大值

min()：求最小值

count()：求个数

count()中一般使用非空字段->主键或者 *
计算平均工资
select avg(money) from emp;
计算总销售额
select sum(sales) from emp;
查询工资最高的员工
select * from emp where money = (select max(money) from emp)
统计部门字段为空的人数
select count(*) from emp where department is null;
注：聚合函数的计算排除null值

2.5.5 分页查询

语法：limit 开始索引，每页查询的记录数

注：索引从0开始

公式：开始索引 = （当前页码 - 1） * 每页查询的记录数
每页查询4条记录，查询第3页
select * from emp limit 8,4;
降序查询员工的销售额，每页显示2条记录，查询第3页
select * from emp order by sales desc limit 4,2;

2.5.6 分组查询

语法：group by 分组字段

注：

  1.  分组后查询的字段应该是：分组字段、聚合函数
  2.  where和having的区别：
          1.  where在分组前进行筛选，如果不满足where条件则不进入分组。having在分组后进行筛选，如果不满足则不被查询到
          2.  where后不能跟聚合函数，having后可以使用聚合函数进行筛选

查询每个部门的平均销售额
select avg(sales),department  from emp group by department;

查询男员工和女员工的最高收入
select sex,max(money)  from emp group by sex;

查询每个部门女性员工的平均销售额，以平均销售额降序方式排列
select avg(sales),department  from emp where sex = "女" group by department order by avg(sales) desc;

查询平均销售额超过9000的部门
select avg(sales),department from emp group by department having avg(sales) > 9000;

查询每个部门男性员工平均销售额超过9000的部门
select avg(sales),department from emp where sex = '男' group by department having avg(sales) > 9000;

2.5.7 查询语句的结构

select

字段列表

from

表名

where

条件列表

group by

分组字段

having

分组后的条件

order by

排序字段排序方式

limit

分页

2.5.8 起别名

语法：
as 别名
可以给表、字段起别名，为了区分，为了简化

注：as可以省略

2.6 约束

概念：对表中数据进行限定

分类：

非空约束：not null

唯一约束：unique

主键约束：primary key

外键约束：foreign key

2.6.1 非空约束 not null

限定字段值不能为空

创建表时，添加非空约束
create table 表名(字段名 字段类型 not null, 字段名 字段类型 [约束], ...);
创建表后，添加非空约束
alter table 表名 modify 字段名 字段类型 not null ;
注：已存在空值的字段不能被设置成非空的

删除非空约束（设置字段允许有空值）
alter table 表名 modify 字段名 字段类型;

2.6.2 唯一约束 unique

限定某一字段的字段值不能有重复

创建表时，添加唯一约束
create table 表名(字段名 字段类型 unique, 字段名 字段类型 [约束], ...);
创建表后，添加唯一约束
alter table 表名 modify 字段名 字段类型 unique;
删除唯一约束（设置字段允许有重复值）
alter table 表名 drop index 字段名;
注：

唯一约束的值可以是空值，可以存在多个空值，但是只能有唯一的“null”值

一个字段可以同时设置成非空唯一，非空和唯一约束的顺序是任意的

已存在重复值的字段不能被设置成唯一的

2.6.3 主键约束 primary key

特点：

非空且唯一

一张表只能有一个字段是主键

主键是表中记录的唯一标识

创建表时，添加主键约束

create table 表名(字段名 字段类型 primary key, 字段名 字段类型 [约束], ...);
create table 表名(字段名 字段类型 [约束], 字段名 字段类型 [约束], ... , primary key(主键字段));

创建表后，添加主键约束

alter table 表名 modify 字段名 字段类型 primary key;

删除主键

alter table 表名 drop primary key;
注意：删除主键约束后，主键仍有非空约束

主键自增长：

概念：如果某个字段是数值类型的主键字段，可以使用 auto_increment 来实现主键自增长

注：

自增长的主键必须是数值类型的（整数或者小数）

当添加记录时，不为该字段赋值或者赋值为null，该字段就会自增长

第一条自增长的字段值是 1

从已存在的最大值开始 + 1

从已存在的最大值是小数时，自增长的值是向上取整

只有主键可以自增长

创建表时，添加主键自增长
create table 表名(字段名 字段类型 primary key auto_increment, 字段名 字段类型 [约束], ...);
创建表后，添加主键自增长
alter table 表名 modify 主键字段 字段类型 auto_increment;
创建表后，添加主键约束的同时，设置自增长
alter table 表名 modify 字段名 字段类型 primary key auto_increment
删除自增长
alter table 表名 modify 字段名 字段类型;

2.6.4 外键约束 foreign key

外键约束可以使表与表之间产生关系

创建表时，添加外键约束

create table 表名 (字段名 字段类型 primary key, 字段名 字段类型 [约束], ... , 外键字段 外键类型 ,[constraint 外键名] foreign key (外键字段) references 主表名 (主表主键字段));

创建表后，添加外键约束

alter table 表名 add [constraint 外键名] foreign key (外键字段) references 主表名 (主表主键字段));

删除外键约束

alter table 表名 drop foreign key 外键名;

注：

从表的外键类型必须与主表的主键类型一致

外键只能指向主键

外键保证了数据的正确性和完整性

中间表的创建以及联合主键

create table 表名 (
  外键字段1 外键类型, 外键字段2 外键类型, primary key (外键字段1,外键字段2), 
  foreign key (外键字段1) references 主表1(主键字段),
  foreign key (外键字段2) references 主表2(主键字段),
);

三，多表

3.1 表与表之间的关系

一对一

例如：人和身份证、公司与注册地址

一对多（多对一）

例如：部门和员工、商品和分类

多对多

例如：学生和课程、订单和商品

3.2 多表关系的实现

一对一

在任何一方添加外键指向另一方法的主键

一对多

在多的一方建立外键，指向一的一方的主键

多对多

需要借助第三张中间表。
中间表至少要包含两个字段，这两个字段都是外键，
分别指向两张主表的主键。

四，多表查询

4.1 交叉查询

语法：
select 字段列表 from 表1, 表2;
交叉查询的结果是笛卡尔积，是两张表所有记录的组成情况。

我们需要使用条件来去除掉无用的数据。

4.2 内连接查询

在交叉查询的基础上使用where条件消除无用的数据

语法：

1. 隐式内连接
select 字段列表 from 表1, 表2 where 条件;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;

4.3 外连接查询

语法：

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件;

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件;

内连接查询的结果是两表的交集

左外连接查询的结果是左表的全部和两表的交集

右外连接查询的结果是右表的全部和两表的交集

五，子查询

概念：查询中嵌套查询

例题：

查询工资最高的员工信息

（1）.查询最高工资
select max(salary) from emp; 结果是10000
（2）.查询员工信息，条件是工资值是10000
select * from emp where salary = 10000;
整合
select * from emp where salary = (select max(salary) from emp);