JavaWeb--MySQL数据库

JavaWeb--MySQL数据库

数据库的基本概念

  • Database 简称：DB
  • 用于存储和管理数据的仓库

数据库的特点

  • 持久化存储数据，其实数据库就是一个文件系统
  • 方便储存和管理数据
  • 使用了统一的方式操作数据库---SQL

常见的数据库软件

MySQL数据库软件

  • 完成安装
  • 学会卸载

启动和关闭SQL服务

  • cmd（管理员模式）--services.msc可以快捷打开服务窗口
  • 或者cmd（管理员模式）--输入 net start mysql80 / net stop mysql80 开关SQL服务（我的服务名字是mysql80）

  • 连接客户端
    • 由于安装出现starting the server失败问题无法使用网络服务，更改为本地系统账户后安装成功，因此没有root帐号的密码，开启MySQL提供的客户端提示需要密码时直接回车即可连接客户端
    • 或者cmd（管理员）：mysql -u root -p 后直接回车
  • 3种标准的登录代码
    • mysql -uroot -p密码
    • mysql -hip -p连接目标的密码
    • mysql --host=ip --user=root --password=密码

  • 离开客户端
    • 输入exit或者quit命令即可

MySQL的目录结构

MySQL安装目录

  • C:\ProgramData\MySQL\MySQL Server 8.0配置文件my.ini

MySQL数据目录

  • 几个概念
    • 数据库：文件夹
    • 表：文件
    • 数据

SQL基本概念

  • 全称Structured Query Language：结构化查询语言，定义了一种操作关系型数据库的统一规则
  • 每一种数据库操作的方式存在不一样的地方，成为“方言”

SQL通用语法

  • 可以单行或者多行书写，以分号“ ; ”结尾。
  • 可以使用空格/缩进来增强语句的可读性。
  • 数据库的SQL语句不区分大小写，但关键字建议使用大写。

注释

  • 单行注释
    • --(空格)注释内容 或 #(不需要空格)注释内容
  • 多行注释
    • /* 注释内容 */

SQL分类

DDL：操作数据库，表

DDL操作数据库

  • CRUD，对应增删改查

    • C(Create):创建

      • create database [if not exists] 数据库名称 [character set(可简写为charset)] 字符集 ；创建新数据库同时可以判断是否存在，同时可自定义字符集,[]内可省略，字符集默认为utf-8

    • R(Retrieve):查询

      • show databases;查询所有数据库的名称
      • show create database 数据库的名字；查询某个数据库的字符集，创建语句

    • U(Update):修改

      • alter database 数据库名称 charset 字符集; 修改数据库字符集

    • D(Delete):删除

      • drop database [if exists] 数据库名称；如果存在，删除数据库，防止报错

    • 使用数据库

      • use 数据库名称；
      • select database(); 查询正在使用的数据库

DDL操作表

  • C(Create):创建

    • create table 表名(

      列名1 数据类型1，

      列名2 数据类型2，

      .....

      列名n 数据类型n（最后不要写逗号

      );

    • 数据类型

    • int :整数类型（age int

    • double：小数类型（5，2）括号里左边为一共有几位小数，右边为保留几位

    • timestamp:时间戳类型，包含年月日时分秒（如果将来不给这个字段赋值，或者赋值为null，则默认使用当前系统时间赋值

    • varchar:变长字符串（name varchar(20)，姓名最大20个字符

  • 创建表

​ create table student(

​ id int，

​ name varchar(32),

​ age int,

​ score double(4,1),

​ birthday date,

​ insert_time timestamp);

复制表

​ create table 表名 like 被复制的表名

  • R(Retrieve):查询

    • 查询某个数据库中所有表名称，use使用后直接用代码
      • show tables; 查看数据库中所有的表
    • 查询表结构
      • desc 表名；

  • U(Update):修改

    • 修改表名
      • alter table 表名 rename to 新的表名；
    • 修改表的字符集
      • alter table 表名 charset 字符集
    • 添加一列
      • alter table 表名 add 列名 数据类型；
    • 修改列的名称 类型
      • alter table 表名 change 某一个列名 更改后的新列名 更改后的新类型；
      • alter table 表名 modify 列名 更改后的新类型；（只改类型
    • 删除列
      • alter table 表名 drop 列名；

  • D(Delete):删除

    • drop table [if exists] 表名;

图形化界面工具SQLyog

此处使用的SQL可视化工具为SQLyog

DML：增删改表中数据

DML操作数据库

添加数据

  • insert into 表名(列名1，列名2，......列名n) value(值1，值2，......值n)
  • 列名和值要一一对应
  • 如果表名后不定义列名则默认给所有列添加值
  • timestamp没生效在设置timestamp类型列表时如下操作
    • 例：ALTER TABLE 表名 ADD 列名 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

删除数据

  • delete from 表名 [where 条件]
    • 比如：DELETE FROM student WHERE id=3;
    • 如果不加条件，则会删除表内全部数据（不推荐）
  • truncate table 表名；删除表，然后创建一个一模一样的空表

修改数据

  • update 表名 set 列名1 = 值1，列名2 = 值2，......[where 条件]；
    • 例：UPDATE stu SET age =16，score=100 WHERE id = 3;
    • 如果不加任何条件，则会将表中所有数据一起修改

DQL：查询表中的记录

  • select * from 表名；查询表内所有数据

  • 语法

    • select

      ​ 字段列表

      from

      ​ 表名列表

      where

      ​ 条件列表

      group by

      ​ 分组字段

      having

      ​ 分组之后的条件

      order by

      ​ 排序

      limit

      ​ 分页限定

基础查询

  • -- 创建stu表
CREATE TABLE stu (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math DOUBLE(5,2), -- 数学成绩
english DOUBLE(5,2), -- 英语成绩
hire_date DATE -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
    • 多个字段的查询
      • -- 查询所有字段
        SELECT * FROM stu;
        -- 查询指定的字段
        SELECT
        NAME,
        age
        FROM
        stu;
    • 去除重复
      • -- 去除重复的结果集
        SELECT DISTINCT address FROM stu;
        -- 只有两个数据完全一样才会去除结果集
        SELECT DISTINCT NAME,address FROM stu;
    • 计算列
      • 一般可以使用四则运算计算一些列的值（一般只会进行数值型的计算）
      • ifnull(表达式1，表达式2)：null参与的运算，计算结果都为null所以需要这个命令
        • 表达式1：哪个字段需要判断是否为null
        • 表达式2：如果该字段为null后的替换值
        • -- 计算数学和英语的分数之和
          SELECT NAME,math,english,math+english FROM stu;
          -- 如果有null参与了计算，计算结果都为null,所以使用ifnull，如果这个数据为null，则为0
          SELECT NAME,math,english,math+IFNULL(english,0) FROM stu;
    • 起别名
      • -- 起别名 AS，省略后空格也可
        SELECT NAME,math,english,math+IFNULL(english,0) AS 总分 FROM stu;
        SELECT NAME 姓名,math 数学,english 英语,math+IFNULL(english,0) 总分 FROM stu;

条件查询

  • where子句后跟条件
  • 运算符

  • -- 查询年龄大于20岁
    select * from stu where age > 20;
    -- 查询年龄大于等于20岁
    SELECT * FROM stu WHERE age >= 20;
    -- 查询年龄等于20岁
    SELECT * FROM stu WHERE age = 20;
    -- 查询年龄不等于20岁
    SELECT * FROM stu WHERE age != 20;
    SELECT * FROM stu WHERE age <> 20;
    -- 查询年龄大于等于20岁 小于等于30
    SELECT * FROM stu WHERE age >= 20 && age <=30;(不推荐)
    SELECT * FROM stu WHERE age >= 20 and age <=30;
    SELECT * FROM stu WHERE age between 20 and 30;
    -- 查询年龄22岁，18岁，25岁的信息
    SELECT * FROM stu WHERE age = 22 or age = 18 or age = 25;
    SELECT * FROM stu WHERE age in (22,18,25);

    -- 查询英语成绩为null，不能用 =null
    SELECT * FROM stu WHERE english is null;
    -- 查询英语成绩不为null,不能用!=null
    SELECT * FROM stu WHERE english IS not NULL;

模糊查询

  • like：模糊查询

    • 占位符
      • _：单个字符
      • %：多个任意字符

  • -- 查询姓马的有哪些？like
    SELECT * FROM stu WHERE NAME LIKE '马%';

    -- 查询第二个字是花的人
    SELECT * FROM stu WHERE NAME LIKE '_花%';

    -- 查询姓名是3个字的人
    SELECT * FROM stu WHERE NAME LIKE '___';

    -- 查询姓名中包含马的人
    SELECT * FROM stu WHERE NAME LIKE '%马%'

排序查询

  • 语法
    • order by 子句
    • order by 排序字段1 排序方法1，排序字段2 排序方式2，.......
  • 排序方式
    • ASC：升序，默认
    • DESC：降序
      • select * from stu order by math DESC;
    • 有多个排序条件，则当前面的条件值一样的时候，才会判断第二条件
      • -- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
        select * from stu order by math DESC,english desc;

聚合函数

  • 将一列数据作为一个整体，进行纵向的计算

    • count：计算个数
      • 一般选择非空的列：主键
      • count(*)：计算所有个数，只要这一列有一个不为null就算一条记录
    • max：计算最大值
      • SELECT MAX(math) FROM stu;
    • min：计算最小值
      • SELECT MIN(math) FROM stu;
    • sum：求和
      • SELECT MIN(math) FROM stu;
    • avg：计算平均值
      • SELECT AVG(math) FROM stu;

  • 注意：所有的聚合函数会排除null

    • 解决方法1
      • 选择不包含非空的列进行计算
    • 解决方法2
      • 计算个数时可以使用 IFNULL() 将null纳入计数
      • SELECT COUNT(IFNULL(english,0)) FROM stu;

分组查询

  • 语法

    • group by 分组的字段；

      • -- 按照性别分组然后分别查询男、女同学的人数和数学的平均分

        例：SELECT sex,AVG(math),COUNT(id) FROM stu GROUP BY sex;

      • -- 按照性别分组然后分别查询男、女同学的人数和数学的平均分,分数低与70分不参与分组

        例：SELECT sex,AVG(math),COUNT(id) FROM stu WHERE math >=70 GROUP BY sex;

      • -- 按照性别分组然后分别查询男、女同学的人数和数学的平均分,分数低与70分不参与分组,分组之后人数大于2个的

        SELECT sex,AVG(math),COUNT(id) FROM stu WHERE math >=70 GROUP BY sex HAVING COUNT(id)>2;

        可在判断时起别名方便书写

  • 注意

    • 分组之后查询的字段只有：分组字段，聚合函数，不要加别的字段
    • SELECT sex,AVG(math) FROM stu GROUP BY sex;
    • where 和 having的区别（面试重点）
      • where在分组前进行限定，如果不满足条件，则不参与分组
      • having再分组后进行限定，如果不满足限定，则不会被查询出来
      • where后不可以接聚合函数的判定
      • having可以接聚合函数计算

分页查询

  • 语法

    • limit 开始的索引，每页查询的条数

      • -- 每页显示3条记录

        SELECT * FROM stu LIMIT 0 ,3;-- 第一页

        SELECT * FROM stu LIMIT 3 ,3;-- 第二页

        -- 公式：开始的索引=（开始的页码-1）*每条显示的条数 ←重要

    • limit分页操作是MySQL数据库软件的一个“方言”

DCL: 管理用户，授权

  • DBA：数据库管理员

添加用户

  • CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 添加完用户如果发生无法登录的情况的话
delete from user where User='';-- 删除匿名用户即可

给用户授权

  • -- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 授予权限
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

-- GRANT…ON…TO 授权关键字
-- 权限    -- 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授
予所有的权限则使用 ALL
-- 数据库名.表名  --该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
-- '用户名'@'主机名'  -- 给哪个用户授权，注：有 2 对单引号

撤销用户权限

REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from '用户名'@'主机名';

删除用户

  • DROP USER '用户名'@'主机名';

修改用户密码

  • -- 修改管理员密码
mysqladmin -uroot -p password 新密码

-- 修改普通用户的密码(2种方式)
update user set password = password('新密码') where use = '用户名';
set password for '用户名'@'主机名' = password('新密码');     ←DCL特有方式

  • 忘记了root用户的密码？

    • cmd -----> net stop mysql 停止mysql的服务（需要管理员权限
    • 使用无验证方式启动mysql服务：mysqld --skip-grant-tables 回车后再开个窗口直接mysql回车就可以进入数据库了，然后改密码，改完后在任务管理器关掉mysqld服务，再cmd启动mysql启动后就可以正常输入密码进入数据库了

查询用户

  • -- 切换到mqsql数据库
USE mysql;
-- 查询user表
SELECT * FROM USER;

约束

约束的概念

  • 对表中的数据进行限定，保证数据的正确性，有效性和完整性
  • 分类
    • 主键约束：primary key
    • 非空约束：not null
    • 唯一约束：unique
    • 外键约束：foreign key

约束--非空约束

  • 非空约束：not null，某一列的值不能为null
    • CREATE TABLE per(
      id INT,
      NAME VARCHAR(20) NOT NULL -- name为非空
      );
    • -- 去除name的非空约束
      ALTER TABLE per MODIFY NAME VARCHAR(20);

约束--唯一约束

  • unique：某一列的值不能重复
    • create table per(
      id int,
      phone_number varchar(20) unique -- 手机号唯一
      );
    • -- 删除唯一约束
      • alter table per drop index phone_number; （创建唯一约束时会自动创建唯一索引，需要删除索引
    • 唯一约束可以有null值，但是只能有一条记录为null
    • 创建表后再添加唯一约束的时候本身表的列数据如果有重复元素是无法添加唯一约束的，需要检查

约束--主键约束

  • 含义：非空且唯一，综合了非空和唯一

    • 一张表只能有一个字段为主键

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

  • 在创建表的时候，添加主键约束

    • create table per(
      id int primary key,-- 给id添加主键约束
      name varchar(30)
      );

  • 删除主键

    • -- 删除主键
      ALTER TABLE per DROP PRIMARY KEY;
    • 不需要指定列是因为主键有且只有一个

  • -- 创建完表后，添加主键
    ALTER TABLE per MODIFY id INT PRIMARY KEY

主键约束--自动增长

  • 概念：如果某一列时数值类型的，使用auto_increment 可以来完成值的自动增长，一般会和主键一块用

    • -- 在创建表的时候，添加主键约束，并且完成主键自增长

      create table per(
      id int primary key auto_increment,-- 给id添加主键约束和自增长
      name varchar(30)
      );

    • 添加数据时，主键的值设为null，或者不设置主键值，的时候会根据上一个值来自动增长

      • insert into per value(null,'a');

    • -- 删除自动增长（主键这样是删不掉的，所以可以直接删掉自动增长）

      • ALTER TABLE per MODIFY id INT;

    • -- 创建完表后，添加主键自动增长

      • ALTER TABLE per MODIFY id INT AUTO_INCREMENT

约束--默认约束

  • 保存数据时，未指定值则采用默认值。 例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。

  • -- 创建表时添加默认约束

    • CREATE TABLE 表名(
 列名 数据类型 DEFAULT 默认值, 
… 
);

  • 默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。

约束--外键约束

  • 单表的缺陷

  • 数据有冗(rong)余，增删改麻烦

通过多一张表将部门和地点整理通过id连接在一起就方便整理

-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT -- 外键对应主表的主键
);

INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
SELECT * FROM employee;

  • 外键约束

    • 在创建表的时候，可以添加外键，让表和表产生关系，从而保证数据的正确性

      • 语法

      • create table 表名(
	...
    外键列
    constraint 外键自定义名称 foreign key (选定外键列名称) references 主表名称(主表列名称)
)

        例：constraint emp_dep_id foreign key (dep_id) references department(id)

  • 有外键约束，就没法删除，输入数据是需要按外界约束的范围内输入

  • 删除外键

    • alter table [外键所在的表] drop foreign key [外键列名]；

  • -- 在表存在的情况下添加外键

    • alter table [需要添加的表名] add constraint [自定义外键名] foreign key (外键列名) references 主表(主表列名);

级联操作

  • 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

    • ON UPDATE CASCADE 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新
    • ON DELETE CASCADE 级联删除

  • -- 创建外键约束 ,添加级联更新和级联删除

    constraint [自定义外键名] foreign key (外键列名) references 主表(主表列名) on update cascade on delete cascade

  • 级联删除存在的风险导致在开发中极为谨慎

多表关系

概念

  • 现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们 在设计表的时候，就应该体现出表与表之间的这种关系
  • 一对一(了解)
    • 如人和身份证
  • 一对多(多对一)
    • 比如一个部门有多个员工，一个员工只能对应一个部门
  • 多对多
    • 如学生和课程，一个学生可以选很多课，一个课程也可以被很多学生选择

一对一关系实现

  • 一对一关系的实现，可以在任意的一方添加唯一外键指向另一方的主键
  • 实际开发中应用不多因为一对一可以创建成一张表

一对多(多对一)关系实现

  • 一对多(多对一)
    • 一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键（如外键约束的例子）

多对多关系实现

  • 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。

多表关系案例

  • 做一个旅游网站

    • 需求1：一个旅游线路分类中有多个旅游线路
    • 需求2：一个用户收藏多个线路，一个线路被多个用户收藏

  • 分析

    • 旅游分类和旅游线路是一对多的关系，一个分类有多个旅游线路
    • 用户和旅游线路是多对多的关系，一个用户可以选择多个旅游线路，一个旅游线路可以被多个用户选择
    • 所以需要有第三个表：收藏，来展现多对多关系，收藏表至少两个字段分别作为各自一方主键

  • 数据库表的框架创建

    -- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(100) NOT NULL UNIQUE
)



/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/

CREATE TABLE tab_route(
 rid INT PRIMARY KEY AUTO_INCREMENT,
 rname VARCHAR(100) NOT NULL UNIQUE,
 price DOUBLE,
 rdate DATE,
 cid INT,
 FOREIGN KEY (cid) REFERENCES tab_category(cid)-- 省略了constraint 外键自定义名称，所以系统会默认给名称
)



/*
创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/

CREATE TABLE tab_user (
 uid INT PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) UNIQUE NOT NULL,
 PASSWORD VARCHAR(30) NOT NULL,
 NAME VARCHAR(100),
 birthday DATE,
 sex CHAR(1) DEFAULT '男',
 telephone VARCHAR(11),
 email VARCHAR(100)
)


/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/

CREATE TABLE tab_favorite (
 rid INT,
 DATE DATETIME,
 uid INT,
 -- 创建复合主键
 PRIMARY KEY(rid,uid),
 FOREIGN KEY (rid) REFERENCES tab_route(rid),
 FOREIGN KEY(uid) REFERENCES tab_user(uid)
)

范式

范式概述

  • 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需 要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

三大范式

  • 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、 第四范式(4NF）和第五范式（5NF，又称完美范式）。 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF）， 其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式

  • 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值 时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。
  • 存在的问题
    • 存在非常严重的冗余，重复的太多：姓名，系名，主任
    • 在数据添加存在问题：添加新设的系和系主任时，没有学生的话数据不合法
    • 数据删除也会存在问题，比如这个系只有这一个同学，这位同学毕业了的话会连系一起删除

第二范式

  • 在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有 两个列，此时不能存在一个属性，它只依赖于其中一个列，这就是不符合第二范式。

  • 几个概念

    • 函数依赖：

      • A-->B，如果通过A属性(属性组)的值，可以确定唯一B属性的值，则B依赖于A

      • 如学号-->姓名，(学号，课程名称)---> 分数

  • 完全函数依赖

    • A--->B, 如果A是一个属性组，则B属性值的确定确定需要依赖于A属性组中所有的属性值

      • (学号，课程名称)---> 分数

    • 部分函数依赖

      • A--->B,如果A是一个属性组，则B属性值的确定只需要依赖于A属性数组中的某一些值即可

    • (学号，课程名称)---> 姓名，只需要学号就能知道姓名

    • 传递函数依赖

    • A--->B , B--->C，如果通过A属性(属性组)的值，可以确定唯一B属性的值，再通过B属性(属性组)的值可以确定唯一的C的属性的值，则称C传递函数依赖于A

      • 学号--->系名，系名-->系主任

  • 码：

    • 如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码

  • 小结

    • 在第一范式(1NF)的基础上，非码属性必须完全依赖于候选码(在1NF的基础上消除非主属性对主码的部分函数依赖)
    • 例子：分数完全依赖于(学号+课程)的属性组，但是姓名，系名，系主任则部分依赖于这个属性组，所以将姓名，系名和系主任，以及复制学号一起分离出来即可完成第二范式的需求

第三范式

  • 在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。 简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递 依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范 式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
  • 也就是消除每个表的传递依赖，比如上面的学号，姓名系名和系主任分离出来的表，这个表内，学号决定系名，系名决定系主任，所以系主任传递依赖于学号，需要消除这样的传递依赖

数据库的备份和还原

命令行的方法

  • 语法：
    • 备份：mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径
    • 还原：
      • 登录数据库
      • 创建数据库
      • 使用数据库
      • 执行文件：source 文件路径

图形化工具的方式

  • SQLyog右键数据库备份/导入

多表查询

概述

  • 语法

    • select
	列名列表
from
	表名列表
where....

  • 准备数据库

    # 创建部门表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表

create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性别
 salary double, -- 工资
 join_date date, -- 入职日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)

insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

  • 笛卡尔积现象

多表查询需要消除笛卡尔积现象的影响，消除无用数据

  • 多表查询的分类

内连接查询

  • 隐式内连接

    • 使用where条件消除无用的数据

    • -- 查询所有员工信息和对应部门信息
SELECT * FROM dept,emp where emp.`dept_id` = dept.`id`;
-- 查询员工表的名称，性别，部门表的名称
SELECT emp.`name`,emp.`gender`,dept.`name`FROM dept,emp WHERE emp.`dept_id`=dept.`id`;

-- 使用别名简化输入
SELECT
	t1.`name`, -- 员工表的姓名
	t1.`gender`, -- 员工表的性别
	t2.`name` -- 部门表的名字
FROM
	emp t1,dept t2
WHERE
	t1.`dept_id`=t2.`id`;

  • 显式内连接

    • 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件

    • SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
-- inner 可省略
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

  • 注意事项

    • 从哪些表中查询数据
    • 条件是什么
    • 查询哪些字段

外连接查询

  • 左外连接

    • 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件

    • SELECT * FROM emp LEFT JOIN dept ON emp.`dept_id` = dept.`id`;

    • 查询的是左表主表所有数据，以及右表与其交集部分，如果符合条件的则显示；否则，显示 NULL

  • 右外连接

    • 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件

    • SELECT * FROM emp RIGHT JOIN dept ON emp.`dept_id` = dept.`id`;

    • 查询的是右表主表所有数据，以及左表与其交集部分，如果符合条件的则显示；否则，显示 NULL

子查询

  • 概念：查询中嵌套查询，称嵌套查询为子查询

    • 基础例子

      • -- 查询工资最高是多少
select max(salary) from emp;

-- 查询员工信息，并且工资等于9000的
select * from emp where emp.`salary` = 9000;

-- 使用子查询结合成一条语句
SELECT * FROM emp WHERE emp.`salary` = (select max(salary) from emp);

    • 子查询的结果是单行单列的

      • 子查询可以作为条件，使用运算符< > =等等判断

      • -- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE salary < (SELECT AVG(salary)FROM emp);

    • 子查询的结果是多行单列的

      • 子查询可以作为条件，使用运算符 in 判断

      • -- 子查询财务部的所有员工信息
select * from emp where emp.`dept_id`=(select id from dept where name = '财务部');

-- 子查询市场部和财务部所有员工信息
select * from emp where emp.`dept_id` in (select id from dept where name in ('财务部','市场部'));

    • 子查询的结果是多行多列的

      • 子查询可以作为一张虚拟表来进行查询

      • -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT * FROM dept t1,(SELECT * FROM emp WHERE join_date >'2011-11-11') t2 WHERE t1.`id` = t2.`dept_id`;


-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息(使用普通内连接）
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11';

      • 使用普通内连接也可以做到

多表查询练习

  • -- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');



-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');



-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);



-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

-- 需求：

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT 
	t1.`job_id`, -- 员工编号
	t1.`ename`, -- 员工姓名
	t1.`salary`,-- 工资
	t2.`jname`,-- 职务名称
	t2.`description`-- 职务描述
FROM 
	emp t1,
	job t2 
WHERE 
	t1.`job_id` = t2.`id`;
 
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
	t1.`id`, -- 员工编号
	t1.`ename`, -- 员工姓名
	t1.`salary`,-- 工资
	t2.`jname`,-- 职务名称
	t2.`description`,-- 职务描述
	t3.`dname`,-- 部门名称
	t3.`loc`-- 部门位置
FROM
	emp t1,job t2,dept t3
WHERE
	t1.`job_id`=t2.`id` AND t1.`dept_id`=t3.`id`;
   
-- 3.查询员工姓名，工资，工资等级
SELECT
	t1.`ename`,-- 员工姓名
	t1.`salary`, -- 工资
	t2.`grade` -- 工资等级
FROM
	emp t1, 
	salarygrade t2
WHERE
	t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`; 
	

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
	t1.`ename`,-- 员工姓名
	t1.`salary`,-- 工资
	t2.`jname`,-- 职务名称
	t2.`description`,-- 职务描述
	t3.`dname`,-- 部门名称
	t3.`loc`,-- 部门位置
	t4.`grade`-- 工资等级
FROM
	emp t1,job t2, dept t3,salarygrade t4
WHERE
	t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary` 
	AND t1.`job_id` = t2.`id`
	AND t1.`dept_id` = t3.`id`;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT
	t1.`id`,-- 部门编号
	t1.`dname`,-- 部门名称
	t1.`loc`,-- 部门位置
	t2.total-- 部门人数
FROM
	dept t1,
	(SELECT
		dept_id,COUNT(id) total -- 部门人数
	FROM
		emp
	GROUP BY dept_id) t2
WHERE
	t1.`id` = t2.dept_id;
 
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT
	t1.`ename` '员工姓名',
	t1.`mgr`,
	t2.`id`,
	t2.`ename` '上级领导姓名'
FROM
	emp t1
LEFT JOIN 
	emp t2
ON 
	t1.`mgr` = t2.`id`;

事务

概念

  • 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

  • 一个业务操作如：转账，往往是要多次访问数据库才能完成的。转 账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

  • 操作

    • 开启事务：start transaction;
    • 回滚:rollback
    • 提交:commit

转账例子

-- 创建数据表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
UPDATE account SET balance = 1000;
-- 张三给李四转账


-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2.李四账户+500

UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

-- 发现出问题，回滚
ROLLBACK;

-- 发现执行没有问题，提交
COMMIT;

默认自动提交和手动提交

  • MYSQL 中可以有两种方式进行事务的操作：

    • 手动提交事务

      • 需要先开启事务，再提交

    • 自动提交事务

      • mysql就是自动提交的

      • 每一条DML（增删改）语句会自动提交一次事务

  • 修改事务的默认提交（MySQL数据库是默认自动提交，Oracle数据库是默认手动提交

    • 查看事务的默认提交方式： SELECT @@autocommit; -- 1代表自动提交，0代表手动提交
    • 修改默认事务提交方式： set @@autocommit = 0;
      • 修改后如果不手动提交是不会更新实际数据的，不更新提交退出后自动回滚
      • 原理：事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表 中，其他任何情况都会清空事务日志(rollback，断开连接

回滚点

  • 概念

    • 在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

  • 语法

    • 设置回滚点 savepoint 名字;
    • 回到回滚点 rollback to 名字;

事务额四大特征

  • 1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
  • 2.持久性：当事务提交或者回滚后，数据库会持久化的保存数据
  • 3.隔离性：多个事务之间，相互独立
  • 4.一致性：事务操作前后，数据总量不变

事务的隔离级别

  • 概念： 多个事务之间隔离的，相互都立的，因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题，设置不同的隔离级别就可以解决这些问题

  • 引发的问题

    • 脏读：一个事务，读取到另一个事务没有提交的数据
    • 不可重复读（虚读）：在同一个事物中，两次读取到的数据不一样
    • 幻读：一个事务操作（DML）中两次读取的数量不一致，一个事务操作的时候另一个事务修改了一条数据，则第一个事务查询不到自己的修改

  • MySQL数据库的4种隔离级别：

    • 读未提交 read uncommitted
      • 产生的问题：脏读，不可重复读，幻读
    • 读已提交 read committed （Oracle和SQL server的默认隔离级别
      • 产生的问题：不可重复读，幻读
    • 可重复读 repeatable read （MySQL的默认隔离级别
      • 产生的问题：幻读
    • 串行化：serializable
      • 可以解决所有问题
    • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低

  • 数据库查询隔离级别

    • select @@tx_isolation;

  • 设置隔离级别

    • set global transaction isolation level 级别字符串;
posted @ 2022-09-30 21:59  Joe_ximu  阅读(130)  评论(0)  编辑  收藏  举报