数据库(学习笔记)

数据库

• 数据库:DateBase(DB)，是存储和管理数据的仓库
• 数据库管理系统:DateBase Management System(DBMS)，操纵和管理数据库的大型软件
• SQL: Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

数据库设计

MySQL概述

安装、配置

初始化MySQL

mysqld --initialize-insecure

MySQL注册:

mysql --install

启动MySQL服务

net start mysql	//启动mysql服务
net stop mysql	//停止mysql服务

修改默认账户密码

mysqladmin -u root password 1234

登录

//直接使用密码登录:mysql -uroot -p1234

mysql -root -p

卸载MySQL

mysqld -remove mysql

MySQL-企业开发使用方式

mysql -u用户名 -p密码 [-h数据库服务器地址 -p端口号]

数据模型

关系型数据库(RDBMS)：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

SQL简介

• SQL:一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

通用语法:

• SQL语句可以单行或多行书写，以分号结尾。
• SQL语句可以使用空格/缩进来增强语句的可读性。
• MySQL数据库的SQL语句不区分大小写
• 注释
  • 单行注释：–注释内容 或 # 注释内容(MySQL特有)
  • 多行注释:/*注释内容*/

SQL分类

SQL语句通常被分为四大类:

数据库设计-DDL

数据库

DDL(数据库操作)

查询

• 查询所有数据库:

  show databases;
  

• 查询当前数据库

  select database();
  

使用

• 使用数据库

  user 数据库名;
  

创建

• 创建数据库

  create database[ if not exists] 数据库名;
  

删除

• 删除数据库

  drop database[if exists] 数据库名;
  

上述语法中的database,也可以替换成 schema

show schemas;

表

DLL(表操作)

• 创建

  create table 表名(
      字段1 字段类型 [约束][comment 字段1注释],
      字段n 字段类型 [约束][comment 字段n注释],
  )[comment 表注释];
  

  create table tb_user(
      id int comment 'ID,唯一标识',	
      username varchar(20) comment '用户名',
      name varchar(10) comment '用户名',
      age int comment '年龄',
      gender char(1) comment '性别'
  )comment  '用户表';
  

• 约束

  概念：约束时作用于表中字段上的规则，用于限制存储在表中的数据

  目的： 保证数据库中数据的正确性、有效性和完整性。

create table tb_user(
    id int comment 'ID,唯一标识',		# 唯一标识
    username varchar(20) comment '用户名',			#非空 唯一
    name varchar(10) comment '用户名',		#非空
    age int comment '年龄',			
    gender char(1) comment '性别'	#默认 男
)comment  '用户表';

# 创建:基本语法(约束)
create table tb_user(
    id int primary key comment 'ID,唯一标识',
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '用户名',
    age int comment '年龄',
    gender char(1) default'男' comment '性别'
)comment  '用户表';

• 数据类型

• 案例:

  CREATE TABLE `tb_emp` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `username` varchar(20) NOT NULL COMMENT '用户名',
    `password` varchar(32) DEFAULT '123456' COMMENT '密码',
    `name` varchar(10) NOT NULL COMMENT '姓名',
    `gender` tinyint unsigned NOT NULL COMMENT '性别 1:男 2：女',
    `image` varchar(320) DEFAULT NULL COMMENT '图像url',
    `job` tinyint unsigned DEFAULT NULL COMMENT '职位 1:班主任 2：讲师 3：学工主管 4：教工主管',
    `entrydate` date DEFAULT NULL COMMENT '入职日期',
    `create_time` datetime NOT NULL COMMENT '创建时间',
    `update_time` datetime NOT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `tb_emp_pk_2` (`username`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表'
  

DDL(表操作)

• 查询当前数据库所有表

  show tables;
  

• 查询表结构:

  desc 表名;
  

• 查询建表语句:

  show create table 表名;
  

修改：

• 添加字段:

  alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
  

• 修改字段类型

  alter table 表名 modify 字段名 新数据类型(长度);
  

• 修改字段名和字段类型

  alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
  

• 删除字段

  alter table 表名 drop column 字段名;
  

• 修改表名

  rename table 表明 to 新表名;
  

删除:

• 删除表：

  drop table[if exists] 表名;
  

  删除表时，表中的全部数据也会被删除。

数据库操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加操作(INSERT)

• 指定字段添加数据

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

  insert into tb_emp(username,name,gender,create_time,update_time) values('wuji','张无忌',1,now(),now());
  

• 全部字段添加数据:

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

  insert into tb_emp values (null,'zhiruo',123,'周芷若',2,'1.jpg',1,'2010-01-01',now(),now());
  

• 批量添加数据(指定字段)

  insert into 表名(字段名1,字段名2) values(值1,值2),(值1，值2);
  

  insert into tb_emp(username,name,gender,create_time,update_time) values ('weifuwang','韦一笑',1,now(),now()),('xieshiwang','谢逊',1,now(),now());
  

• 批量添加数据(全部字段)

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

注意事项：

• 插入数据时，指定的字段顺序需要与值得顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

修改操作(UPDATE)

• 修改数据

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

  # 将tb_emp表中ID为1的员工 的 姓名 name 更新为'张三'
  update tb_emp set name ='张三', update_time = now() where id = 1;
  
  # 将tb_emp表中所有员工的入职日期，改为 2010-01-01
  update tb_emp set entrydate = '2010-01-01' ,update_time = now();
  

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

删除数据(DELETE)

• 删除数据

  delete from 表名 [where 条件];
  

注意事项:

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为null)

数据库操作-DQL

DQL:DQL英文全程是Date Query Language(数据查询语言)，用来查询数据库表中的记录。

语法:

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

基本查询

• 查询多个字段:

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

• 查询所有字段(通配符)

  select * from 表名
  

• 设置别名

  select 字段1 [as 别名1],字段2[as 别名2] from 表名;
  

• 去除重复记录

  select distinct 字段列表 from 表名1;
  

#   =====================DQL 基本查询   ==========================
# 1,查询指定字段 name ,entrytime 并返回
select name,tb_emp.entrydate from tb_emp;

# 查询返回所有字段
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
# (不推荐使用)
select * from tb_emp;
# 查询所有员工的name,entrydate 并起别名
select name as 姓名,tb_emp.entrydate as 入职日期 from tb_emp;
select name as '姓 名',tb_emp.entrydate as 入职日期 from tb_emp;

# 查询已有的员工关联了哪几种职位(不要重复)
# distinct去除重复记录
select tb_emp.job from tb_emp;
select distinct tb_emp.job from tb_emp;

条件查询(where)

• 条件查询

  select 字段列表 from 表名 where 条件列表;
  

  比较运算符	功能
  >	大于
  >=	大于等于
  <	小于
  <=	小于等于
  =	等于
  <> 或 !=	不等于
  between…and…	在某个范围之内(含最小、最大值)
  in(…)	在in之后的列表中的值，多选一
  like 占位符	模糊匹配(_匹配单个字符,%匹配任意个字符)
  is null	是null
  and 或 &&	并且(多个条件同时成立)
  or 或 ||	或者(多个条件任意一个成立)
  not 或 !	非，不是

# ====================DQL 条件查询===========================
# 1.查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';
# 2.查询 id <= 5 的员工信息
select * from tb_emp where id <= 5;
# 3.查询没有 分配job的员工信息
select * from tb_emp where job is null;
# 4.查询 有职位 的员工信息
select * from tb_emp where job is not null ;
# 5.查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password != '123456';
# 6.查询 入职日期 在'2000-01-01' 包含 到'2010-01-01'之间的员工信息
select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01' ;
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
# 7.查询 入职时间 在2000-01-01 到 2010-01-01 之间的 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;
# 8.查询 职位是 2，3，4的员工信息
select * from tb_emp where job =2 or job = 3 or job = 4;
select * from tb_emp where job in(2,3,4);
# 9.查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__'; /*两个字*/
select * from tb_emp where name like '___'; /*三个字*/
# 10.查询 姓'张'的员工
select * from tb_emp where name like '张%';

分组查询(group by)

聚合函数:将一列数据作为一个整体，进行纵向计算。

select 聚合函数(字段列表) from 表名;

# ===================DQL 分组查询=============================
# 聚合函数
# select 聚合函数(字段列表) from 表名;
# 1.统计企业员工数量 --count
# A.count(字段)
select count(id) from  tb_emp;
select count(job) from tb_emp;
# B.count(常量)
select count('A') from tb_emp;
# C.count(*) 推荐
select count(*) from tb_emp;
# 2.统计该企业最迟入职的员工 - max
select max(tb_emp.entrydate) from tb_emp;
# 3.统计该企业最早入职的员工 - min
select min(tb_emp.entrydate) from tb_emp;
# 4.统计该企业员工ID的平均值 -avg
select avg(tb_emp.id) from tb_emp;
# 5.统计该企业员工的ID之和    -sum
select sum(tb_emp.id) from tb_emp;

注意事项:

• null值不参与所有聚合函数运算
• 统计数量可以使用: count(*) count(字段) count(常量) 推荐使用count(*)

分组查询:

select 字段列表 from 表名[where 条件] group by 分组字段名[having 分组后过滤条件];

# 分组
# 1.根据性别分组，统计 男性 和 女性员工的数量 --count(*)
select gender,count(*) from tb_emp group by gender ;
# 2.先查询入职时间'2015-01-01以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select * from tb_emp where entrydate <= '2015-01-01';   /*查询2015-01-01以前的员工*/
select job,count(*)  from tb_emp where entrydate <= '2015-01-01'group by job;       /*查询2015-01-01以前的员工，并分组*/
select job,count(*)  from tb_emp where entrydate <= '2015-01-01'group by job having count(*) >= 2;

where和having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having

排序查询(order by)

条件查询:

#语法
select 字段列表 from 表名[where 条件列表][group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2...;

# ASC 升序(默认值)
# DESC 降序

# ==================排序查询=================================
# 1.根据入职时间，对员工进行升序排序 -asc
select * from tb_emp order by entrydate; /*asc可以删去*/
# 2.根据入职时间，对员工进行降序排序
select * from tb_emp order by entrydate DESC;
# 3.根据 入职时间 对公司的员工进行 升序排序,入职时间相同，再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate,update_time desc;

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询(limit)

分页查询语法:

select 字段列表 from 表名 limit 起始索引,查询记录数;

# ==================分页查询=================
# 1.从 其实索引0 开始查询员工数据，每页展示5条记录
select * from tb_emp limit 0,5;
# 2.查询 第1页 员工数据，每页展示5条记录
select * from  tb_emp limit 0,5;
# 3.查询 第2页 员工数据，每页展示5条记录
select * from tb_emp limit 5,5;
# 4.查询 第3页 员工数据，每页展示5条记录
select * from tb_emp limit 10,5;

注意事项:

• 起始索引从0开始，其实索引 = (查询页码 - 1) * 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中式LIMIT
• 如果要查询的是第一页数据，起始索引可以省略，直接简写为limint 10

函数

• if(表达式, tvalue,fvalue) ： 当表达式为true时，取值tvalue ；当表达式为false时，取值fvalue
• case expr when value1 then result1 [when value 2 then result2] else result end

案例

# 案例1：按照需求完成员工管理的条件分页查询 - 根据输入条件，查询第一页数据，每页展示10条数据
# 输入条件 : 姓名：张   性别：男    入职时间 2000-01-01 2015-12-31
/*查询员工： 根据输入的员工姓名、员工性别、入职时间 搜索满足条件的员工信息
            其中 员工姓名 支持模糊匹配；性别 进行精准查询；入职时间 进行范围查询
            支持分页查询  并对查询的结果，根据最后修改时间进行倒序排序*/
select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2001-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;

# 案例2  根据需求，完成员工性别信息统计 -count(*)
# if(条件表达式,true取值,false取值)
select if(gender = 1, '男性员工', '女性员工') 性别 gender, count(*)
from tb_emp
group by gender;

# 案例3
# case 表达式 when 值1 then 结果1 when 值2 then 结果 2....else.....end
select (case job
            when 1 then '班主任'
            when 2 then '讲师'
            when 3 then '学工主管'
            when 4 then '教研主管'
            else '未分配职位' end) 职位,
       count(*)
from tb_emp
group by job;

多表设计

多表设计-概述：

项目开发中，再进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互联系，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

一对多：

需求：根据页面原型及需求文档，完成部门及员工模块的表结构设计

多表问题分析：

现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。

问题分析：目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

外键语法

# 创建表时指定
create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表 (字段名)
);

# 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

alter table tb_emp
    add constraint tb_emp_fk_dept_id
        foreign key (dept_id) references
            tb_dept (id);

物理外键：

• 概念：使用foreign key定义外键关联另外一张表
• 缺点
  • 影响增、删、改的效率(需要检查外键的关系)
  • 仅用于单节点数据库，不适用于分布式、集群场景。
  • 容易引发数据库的死锁问题、消耗性能

逻辑外键：

• 概念：在业务逻辑中，解决外键关联
• 通过逻辑外键，就可以很方便的解决上述问题

一对一

• 案例 ：用户 与 身份证信息 的关系
• 关系： 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多对多

实现:

建立第三张中间表，中间表指导包含两个外键，分别关联两方主键。

​