[大数据]MySQL的简单学习

数据库(DataBase)

常见的数据库管理系统

• Oracle（全球最强大的数据库管理系统，收费，政府或者银行以及相关行业常用）
• MySQL（免费开源，互联网公司常用）
• DB2
• Sybase
• Microsoft SqlServer（支持标准sql的数据库管理系统）

MySQL的安装步骤

• 安装默认路径：C:\Program Files (x86)\MySQL

• 一直默认点到该页面

• 根据需求选择是在什么机器上进行开发

• 对事务的支持

• 默认安装位置

• 默认

• 端口设置（计算机网络知识）
  

• 选择字符集
  

• 设置Service Name（服务名称），

• 设置超管密码
• enable root access from remote machines（远程访问）

• execute

查看服务

登录MySQL

• 按住win+R 运行 cmd
• 输入mysql -u+用户名 + -p密码
  • 比如：mysql -uroot -p123
• 或者输入mysql -u+用户名 + -p
  • 这样密码会以" ***** "表示

登陆成功

退出MySQL

• exit
• quit

修改密码

方法1： 用SET PASSWORD命令

• 首先登录MySQL。

• 格式：

  • mysql> set password for 用户名@localhost = password(‘新密码’);

• 例子：

  • mysql> set password for root@localhost = password(‘123’);

方法2：用mysqladmin

• 格式：
  • mysqladmin -u用户名 -p旧密码 password 新密码
• 例子：
  • mysqladmin -uroot -p123456 password 123

方法3：用UPDATE直接编辑user表

• 首先登录MySQL。
• mysql> use mysql;
• mysql> update user set password=password(‘123’) where
• user=‘root’ and host=‘localhost’;
• mysql> flush privileges;

方法4：在忘记root密码的时候

• 以windows为例：

1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges;　。
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

参考资料：https://www.bilibili.com/video/BV1fx411X7BD?p=4

卸载mySQL

• 打开最开始的安装程序
• 选择remove
• 将C:\Program Files (x86)\MySQL 删除
• 将C:\ProgramData\MySQL（隐藏目录） 删除

MySQL目录结构

安装目录

• 配置文件 my.ini

数据目录

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

SQL

• **SQL **全称：结构化查询语言(Structured Query Language)
• SQL用来和数据库进行通信
• SQL是一套标准，大多数数据库的SQL是通用的
• 每一个数据库管理系统都有自己的一些特性，当使用这个特性的时候，SQL语句就可能不是标准了

SQL通用语法

1. SQL 语句可以单行或多行书写，以 分号结尾

2. 可使用 空格和缩进 来增强语句的可读性

3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写

4. 三种注释

   1. 单行注释：-- +空格 + 注释内容 或 # + 注释内容

      • SHOW databases; – 查询所有数据库名称

        SHOW databases; #查询所有数据库名称

   2. 多行注释：/* 注释 */

SQL分类

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

  • 用来定义数据库对象：数据库，表，列等
  • 关键字：create、drop、alter 等

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

  • 用来对数据库中的表的数据进行增删改
  • 关键字：insert、delete、update 等

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

  • 用来查询数据库中表的记录（数据）
  • 关键字：select、where 等

• DCL（Data Control Language）数据控制语言（了解）

  • 用来定义数据库的访问权限和安全级别，以及创建用户
  • 关键字：GRANT、REVOKE 等

参考资料：https://www.bilibili.com/video/BV1qv4y1o79t

DDL:操作数据库(CRUD)

• DDL（Data Definition Language），即数据定义语言，例如建数据库、建表等，都属于数据定义语言，也是今天本文的主角；

C（create）：创建

• 创建数据库

  • create database 数据库名称;
    

  • 

• 创建数据库，判断是否存在

  • create database if not exists 数据库名称;
    

  • 

• 创建数据库，并指定字符集

  • create database 数据库名称 character set 字符集名;
    

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vqKW8O7h-1646631186925)(D:\Learning\学习笔记\Java\JavaWeb\数据库\数据库.assets\image-20220119231407355.png)]

• 练习：创建db1数据库，判断是否存在，并且制定字符集为gbk

  • create database if not exists db1 character set gbk;
    

  • 

R（Retrieve）：查询

• 查询所有数据库的名称

  • show databases;
    

  • 

• 查询某个数据库的字符集：查询某个数据库的创建语句

  • show create database 数据库名称
    

  • 

U（Update）：修改

• 修改数据库的字符集

  • alter database 数据库名称 character set 字符集名;
    

  • 

D（Delete）：删除

• 删除数据库

  • drop database 数据库名称;
    

  • 

• 判断数据库存在，存在则删除

  • drop database if exists 数据库名称;
    

  • 

使用数据库

• 查询正在使用的数据库名称

  • select database();
    

  • 

• 使用数据库

  • use 数据库名称;
    

  • 

DDL:操作表

C（create）：创建

• 语法

  • create table 表名(
    	列名1 数据类型1,
    	列名2 数据类型2,
    	...
    	列名n 数据类型n
    );
    

    注意：最后一列，不需要加逗号（ , ）

• 数据类型：

  1. int：整数类型

     • age int
       

  2. double：小数类型

     • mysql score double(5,2) -- 最多有5位，保留小数点两位
       

  3. date：日期，只包含年月日，yyyy-MM-dd

  4. datetime：日期，包含年月日时分，yyyy-MM-dd HH:mm:ss

  5. timestamp：时间戳类型，包含年月日时分，yyyy-MM-dd HH:mm:ss

     • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

  6. varchar：字符串

     • name varchar(20); -- 姓名最大20个字符
       

• 复制表：

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

• 示例：创建下图对应的表

  • 

  • create table student(
    	id int,
    	name varchar(32),
    	age int,
    	score double(4,1),
    	birthday date,
    	insert_time timestamp
    );
    

  • 

R（Retrieve）：查询

• 查询某个数据库中所有表的名称

  • show tables;
    

    • 

• 查询表结构

  • desc 表名
    

    • 

U（Update）：修改

1. 修改表名

   • alter table 表名 rename to 新的表名;
     

     • 

2. 修改表的字符集

   • alter table 表名 character set 字符集名称;
     

     • 

     • 

3. 添加一列

   • alter table 表名 add 列名 数据类型
     

     • 

4. 修改列名称 类型

   • alter table 表名 change 列名 新列名 新数据类型; -- 修改列名称 + 数据类型
     

     • 

   • alter table 表名 modify 列名 新数据类型; -- 修改数据类型
     

     • 

5. 删除列

   • alter table 表名 drop 列名;
     

     • 

D（Delete）：删除

• drop table 表名;
  

  • 

• drop table if exists 表名;
  

  • 

客户端图形化工具：SQLyog

DML：增删改表中的数据

• DML（Data Manipulation Language），即数据操纵语言，最常用的增删改查就属于DML，操作对象是数据表中的记录；

添加数据

• 语法

  • insert into 表名(列名，列名，....列名n) values(值1，值2，...值n);
    

    • 

• 注意：

  1. 列名和值要一一对应

  2. 如果表名后，不定义列名，则默认给所有列添加值

     • insert into 表名 values(值1，值2，...值n);
       

       • 

  3. 除了数字类型，其他类型需要使用引号（单双都可以）引起来

删除数据

• 语法

  • delete from 表名 (where 条件);
    

    • 

  • 注意：

    1. 如果不加条件，则删除表中的所有记录
    2. 如果要删除所有记录

       • delete from 表名; – 不推荐使用。有多少记录就会执行多少次删除操作，效率低

         • 

       • truncate table 表名; – 推荐使用。先删除表，再创建一张一模一样的空表，效率高

         • 

修改数据

• 语法

  • update 表名 set 列名1 = 值1, 列名2 = 值2,...(where 条件);
    

    • 

• 注意

  • 如果不加任何条件，则会将表中所有记录 全部修改
    • 

DQL：查询表中的数据

• DQL（Data Query Language）

语法

select
	字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

基础查询

• 准备数据：

  • CREATE TABLE student (
     id int, -- 编号
     name varchar(20), -- 姓名
     age int, -- 年龄
     sex varchar(5), -- 性别
     address varchar(100), -- 地址
     math int, -- 数学
     english int -- 英语
    );
    INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
    ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
    

  
  

1. 多个字段的查询

   • select 字段名1，字段名2... from 表名;
     

     • 

   • 注意：

     • 如果查询所有字段，可以使用 ***** 来替代字段列表
       • 

2. 去除重复

   • distinct
     

     • 

3. 计算列

   • 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）

     • 

   • ifnull(表达式1，表达式2) 使用原因：null参与的运算，计算结果都为null

     • 表达式1：哪个字段需要判断是否为 null
     • 表达式2：该字段为 null 后的 替换值
     • 

4. 起别名

   • as：可省略
     

     • 

条件查询

1. select * from 表名 where 条件
   

   • 

2. 运算符

   • >、<、<=、>=、=、<>

     • 

   • BETWEEN…AND

     • BETWEEN 值 1 AND 值 2
       

       • 

   • IN(集合)

     • SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
       

       • 

   • LIKE

     • 占位符

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

     • SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
       

       • 

   • IS NULL

     • SELECT * FROM 表名 WHERE 字段名 IS NULL;
       

       • 

   • and 或 &&

     • SELECT * FROM 表名 WHERE 条件1 AND 条件2 ...;
       

       • 

   • or 或 ||

     • SELECT * FROM 表名 WHERE 条件1 OR 条件2 ...;
       

       • 

   • not 或 !

     • 

排序查询

• 语法

  • 排序方式

    • ASC：升序，默认
    • DESC：降序

  • order by 排序字段1 排序方式1, 排序字段2 排序方式2 ...
    

  • 注意：如果有多个排序条件，则只有前面的条件值相等时，才会进行第二条件判断

    • 

    • 

    • 

聚合函数

• 聚合函数：将一列数据作为一个整体，进行逆向的计算

  • count：计算个数

    • 一般选择非空的列：主键

      • 

    • count(*)

      • 

  • max：计算最大值

    • 

  • min：计算最小值

    • 

  • sum：计算和

    • 

  • avg：计算平均值

    • 

• 注意：聚合函数的计算，排除 null 值
  • 解决方案：

    1. 选择不包含空的列进行计算

       • 

    2. IFNULL函数

       • 

分组查询

1. 语法

   • group by 分组字段
     

     • 

1. 注意：
   • where 和 having 的区别？
     • where 在分组之前进行限定，如果不满足条件，则不参与分组。
     • having 在分组之后进行限定，如果不满足条件，则不会被查询出来
     • where 后不可以跟聚合函数，having 后可以进行聚合函数的判断
       • 

分页查询

1. 语法：

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

     • 

2. 公式：开始的索引 = （当前的页码 - 1）* 每页显示的条数

3. limit 是一个“方言”（不具有通用性，只能在MySQL上使用）

约束

• 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性

主键约束

• 主键约束：primary key

1. 注意
   • 含义：非空且唯一
   • 一张表 只能 有一个字段为 主键
   • 主键 就是表中记录的 唯一标识

2. 在创建表时，添加主键约束

   • create table stu(
     	需要设置主键的列名 int primary key, -- 给“需要设置主键的列” 添加主键约束
     	name varchar(20)
     );
     

     • 

     • 说明添加 主键约束 成功

3. 删除主键

   • ALTER TABLE 表名 DROP PRIMARY KEY;
     

     • 

4. 创建完表之后，添加主键

   • ALTER TABLE 表名 MODIFY 需要添加的列名 INT PRIMARY KEY;
     

     • 

5. 自动增长

   • 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长

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

     • CREATE TABLE stu(
       	id INT PRIMARY KEY AUTO_INCREMENT, -- 给 id 添加主键约束
       	NAME VARCHAR(20)
       );
       

       • 
   
   

   • 删除 自动增长

     • ALTER TABLE 表名 MODIFY 主键 INT;
       

       • 
     
     

   • 添加 自动增长

     • ALTER TABLE 表名 MODIFY 主键 INT AUTO_INCREMENT;
       

       • 

非空约束

• 非空约束：not null

1. 在创建表时添加约束

   • 

   • 实现了 name 不能为 null 的约束

2. 创建表之后，添加非空约束

   • 

   • 

唯一约束

• 唯一约束：unique，某一列的值不能重复

  • 在创建表时，添加 唯一约束

    • 

  • 删除 唯一约束

    • 

  • 在表添加完后，添加 唯一约束

    • 

• 注意：唯一约束 可以有 null 值，但是只能有一条记录为 null

外键约束

• 外键约束：foreign key，让表与表之间产生关系，从而保证数据的正确性

• 准备数据

  • CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
    );
    -- 添加数据
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
    

  
  
  1. 数据冗余
  2. 后期还会出现增删改的问题
  
  

• 解决办法：

  • -- 解决方案：分成 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 -- 外键对应主表的主键
    )
    -- 添加 2 个部门
    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);
    

• 在创建表时，添加外键

  • 语法

    • CREATE TABLE 表名(
      	...
      	外键列,
      	CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名(主表列名称);
      );
      

      • 

• 删除 外键

  • ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
    

    • 

• 添加 外键

  • ALTER TABLE 表名 ADD CONSTRAINT 外键 FOREIGN KEY (外键列名称) REFERENCES 主表名(主表列名称);
    

    • 

• 级联操作：级联操作 是存在于主表 (父表)和从表 (子表)关系之中，当 操作 了主表中的数据则要求子表中的数据也随之更新或者删除，这样的 操作 叫做 级联操作

  • 添加 级联操作

    • ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
      FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名称) ON UPDATE ON DELETE CASCADE;
      

      • 

  • 分类

    1. 级联更新：ON UPDATE CASCADE
    2. 级联删除：ON DELETE CASCADE
  
  

数据库的设计

多表之间的关系

一对一（了解）

• 例如：人和身份证
• 分析：一个人只有一个身份证，一个身份证也只能对应一个人
  • 实现方式：一对一关系实现，可以在任意一方添加 唯一外键(UNIQUE) 指向另一方的主键

一对多（多对一）

• 例如：部门和员工

• 分析：一个部门可以有多个员工，一个员工只能属于一个部门

  • 
  
  

• 实现方式：在多的一方建立外键，指向一的一方的主键

多对多

• 例如：学生和课程
• 分析：一个学生可以选择多门课程，一个课程可以被多个学生选择
  • 

案例

• -- 创建旅游线路分类表 tab_category
  -- cid 旅游线路分类主键，自动增长
  -- cname 旅游线路分类名称非空，唯一，字符串 100
  
  CREATE TABLE tab_category(
  	cid INT PRIMARY KEY AUTO_INCREMENT,
  	cname VARCHAR(100) UNIQUE NOT NULL
  );
  
  -- 添加旅游线路分类数据：
  INSERT INTO tab_category (cname) VALUES('周边游'),('出境游'),('国内游'),('澳洲游');
  
  SELECT * FROM tab_category;
  
  -- 创建旅游线路表 tab_route
  /*
  rid 旅游线路主键，自动增长
  rname 旅游线路名称非空，唯一，字符串 100
  price 价格
  rdate 上架时间，日期类型
  cid 外键，所属分类
  */
  
  CREATE TABLE tab_route(
  	rid INT PRIMARY KEY AUTO_INCREMENT,
  	rname VARCHAR(100) UNIQUE NOT NULL,
  	price DOUBLE,
  	rdate DATE,
  	cid INT,
  	FOREIGN KEY (cid) REFERENCES tab_category(cid) 
  );
  
  -- 添加旅游线路数据
  INSERT INTO tab_route VALUES
  (NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,
  '2018-01-27', 1),
  (NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-
  22', 3),
  (NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州
  往返 特价团】', 1699, '2018-01-27', 2),
  (NULL, '【经典?狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',
  2),
  (NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店
  暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
  
  SELECT * FROM tab_route;
  
  /*
  创建用户表 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)
  );
  
  -- 添加用户数据
  INSERT INTO tab_user VALUES
  (NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
  (NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
  
  SELECT * FROM tab_user;
  
  /*
  创建收藏表 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)
  );
  
  -- 增加收藏表数据
  INSERT INTO tab_favorite VALUES
  (1, '2018-01-01', 1), -- 老王选择厦门
  (2, '2018-02-11', 1), -- 老王选择桂林
  (3, '2018-03-21', 1), -- 老王选择泰国
  (2, '2018-04-21', 2), -- 小王选择桂林
  (3, '2018-05-08', 2), -- 小王选择泰国
  (5, '2018-06-02', 2); -- 小王选择迪士尼
  
  SELECT * FROM tab_favorite;
  

• 

范式

• 概念：好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
• 六种范式：
• 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF、第四范式(4NF）和第五范式（5NF，又称完美范式）

第一范式（1NF）

• 第一范式（1NF）：

  • 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。
  • 简而言之，第一范式每一列 不可再拆分，称为 原子性。

• 例如

  • 
  
  
  • 该表存在的问题：
    1. 存在非常严重的数据冗余（重复）问题：姓名、系名、系主任
    2. 数据添加存在问题：添加新开设的系和系主任时，数据不合法（原因是学号和其他列为空）
    3. 数据删除存在问题：某同学毕业了，删除数据时会将系的数据一并删除（系不会因为学生的毕业而删除）

第二范式（2NF）

• 第二范式（2NF）：在满足第一范式的前提下，表中的每一个字段都完全依赖于主键
• 简而言之，第二范式就是在第一范式的基础上 所有列 完全依赖于 主键列。

• 函数依赖：A —> B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称 B依赖于A
  • 例如：学号 —> 姓名
• 属性组：（A，C）—> B，A、C称为属性组
• 完全函数依赖：A —> B，如果A是一个 属性组，则B属性的确定需要依赖于A属性组所有的属性值
  • 例如：（学号，课程名称）—> 分数
• 部分函数依赖：A —> B，如果A是一个 属性组，则B属性的确定只需要依赖于A属性组的部分属性
  • 例如：（学号，课程名称）—> 姓名，只需要学号就可以确认姓名
• 传递依赖：A —> B，B —> C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递依赖于 A
  • 例如：学号 —> 系名，系名 —> 系主任
• 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的 码
  • 例如：上述的表中 码 为：（学号，课程名称）
  • 主属性：码属性组中的所有属性
  • 非主属性：除了码属性的属性

第三范式（3NF）

• 第三范式（3NF）：在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键
• 简而言之，第三范式就是所有列 不依赖 于其它 非主键列 ，也就是在满足 2NF 的基础上，任何非主列不得 传递依赖 于 主键

数据库的备份与还原

1. 命令行

   • 语法

     • 备份

       • mysqldump -u用户名 -u密码 数据库名称 > 保存的路径
         

         • 

     • 还原

       1. 登录数据库
       2. 创建数据库
       3. 使用数据库
       4. 执行文件（source 文件路径）
          • 

2. 图形化工具

   • 备份

     • 

     • 

   • 还原

     • 

     • 

多表查询

• 准备数据

  • # 创建部门表
    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);
    

  
  

• 笛卡尔积：

  • 笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积（Cartesian product），又称直积，表示为X × Y （即取这两个集合的所有组合情况）
  • 要完成 多表查询，需要消除无用的数据

内连接查询

1. 隐式内连接：使用 where 条件消除无用数据

   • 例子

     • -- 查询员工表的名称、性别、部门
       select
       	t1.name, -- 员工表的名称
       	t1.gender, -- 员工表的性别
       	t2.id -- 部门表的名称
       from 	
       	emp t1,
       	dept t2
       where	
       	t1.dept_id = t2.id;
       

     • 

2. 显式内连接：

   • 语法（INNER可省略）

     • SELECT 
       	字段列表
       FROM
       	表名1
       INNER JOIN
       	表名2
       ON
       	条件;
       

       • 

3. 注意：
   1. 从哪些表查询数据
   2. 条件是什么
   3. 查询哪些字段

外链接查询

1. 左外连接：

   • 语法

     • select
       	字段列表
       from
       	表1
       left outer(可省略) join
       	表2
       on
       	条件;
       

       • 

   • 查询的是左表 所有数据 以及其 交集 部分

   
   

2. 右外连接：

   • 语法

     • select
       	字段列表
       from
       	表1
       right outer(可省略) join
       	表2
       on
       	条件;
       

       • 

子查询

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

  • 例子

    • -- 查询工资最高的员工消息
      -- 1 查询最高的工资
      SELECT MAX(salary) FROM emp;
      
      -- 2 查询员工信息，并且工资为9000
      SELECT * FROM emp WHERE emp.salary = 9000;
      
      -- 子查询
      SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
      

• 子查询的不同情况

  1. 查询结果 单行单列：

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

       • 例子：

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

  2. 查询结果 多行单列：

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

       • 例子：

         • -- 查询'财务部'和'市场部'所有的员工信息
           SELECT 
           	*	 
           FROM 
           	emp
           WHERE
           	dept_id
           IN
           	(SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
           

  3. 查询结果 多行多列

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

       • 例如：

         • -- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
           SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2
           WHERE t1.id = t2.dept_id;
           
           -- 使用内连接
           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);
  
  SELECT * FROM dept;
  SELECT * FROM emp;
  SELECT * FROM job;
  SELECT * FROM salarygrade;
  
  -- 需求：
  
  -- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
  
  /*
  	分析
  		1. 员工编号，员工姓名，工资 利用emp表查询  职务名称，职务描述 利用job表
  		2. 查询条件：emp.job_id = job.id
  */
  SELECT
  	t1.id, -- 员工编号
  	t1.ename, -- 员工姓名
  	t1.salary, -- 员工工资
  	t2.jname, -- 职务名称
  	t2.description -- 职务描述
  FROM
  	emp t1,job t2
  WHERE
  	t1.job_id = t2.id;
  
  -- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
  
  /*
  	分析
  		1.员工编号，员工姓名，工资 emp表 职务名称，职务描述 job表 
  		  部门名称，部门位置 dept表
  		2.查询条件：emp.job_id = job.id and emp.dept_id = dept.id
  */
  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.查询员工姓名，工资，工资等级
  
  /*
  分析
  	1.员工姓名，工资 emp表 工资等级 salarygrade
  	2.查询条件：emp.salary between salarygrade.losalary and hisalary
  */
  SELECT 
  	t1.ename, -- 员工姓名
  	t1.salary, -- 员工工资
  	t2.grade -- 工资等级
  FROM
  	emp t1,salarygrade t2
  WHERE
  	t1.salary BETWEEN t2.losalary AND t2.hisalary;
  
  -- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
  
  /*
  分析
  	1.员工姓名，工资 emp 职务名称，职务描述 job 部门名称，部门位置 dept 工资等级 salarygrade
  	2.查询条件：emp.job_id = job.id and emp.dept_id = dept.id
  		    emp.salary between salarygrade.losalary and hisalary
  */
  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.job_id = t2.id 
  	AND t1.dept_id = t3.id 
  	AND t1.salary BETWEEN t4.losalary AND t4.hisalary; 
  
  
  -- 5.查询出部门编号、部门名称、部门位置、部门人数
  /*
  分析
  	1.部门编号、部门名称、部门位置 dept 部门人数 emp
  	2.使用分组查询，按照emp.dept_id完成分组，查询count(id)
  	3.使用子查询将第2步的查询结果和dept表进行关联查询
  */
  SELECT
  	t1.id,t1.dname,t1.loc,t2.total
  FROM
  	dept t1,
  	(SELECT 
  		dept_id,COUNT(dept_id) total
  	FROM 
  		emp
  	GROUP BY
  		dept_id) t2
  WHERE
  	t1.id = t2.dept_id
  ;
   
  -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
  /*
  分析
  	1.所有员工的姓名及其直接上级的姓名 emp
  	2.查询条件：emp.id = emp.mgr
  	3.外连接
  */
  SELECT 
  	t1.ename,
  	t1.mgr,
  	t2.id,
  	t2.ename
  FROM 
  	emp t1
  LEFT JOIN
  	emp t2
  ON
  	t1.mgr = t2.id;
  

  
  

事务

事务的基本介绍

1. 概念：

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

2. 操作：

   1. 开启事务：

      • start transaction
        

   2. 回滚：

   • rollback
     

   3. 提交：

   • commit
     

3. 例子

   • -- 创建数据表
     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;
     
     SELECT * FROM account;
     
     -- 0.开启事务
     START TRANSACTION;
     -- 张三账号-500
     UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan';
     
     -- 李四账号+500
     -- 出错了
     UPDATE account SET balance = balance + 500 WHERE NAME='lisi';
     
     -- 发现没有问题，提交事务
     COMMIT;
     
     -- 发现出问题了，回滚事务
     ROLLBACK;
     

4. MySQL数据库中 事务默认自动提交

   • 事务提交的两种方式

     1. 自动提交
        • 执行一条 DML（增删改） 语句会自动提交一次事务
     2. 手动提交
        • 需要 开启 事务，再提交
        • Oracle 数据库是 手动提交 事务

   • 修改事务的 默认提交方式

     • 查看 默认提交方式

       • SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
         

     • 修改 默认提交方式

       • SET @@autocommit = 0；
         

事务的四大特征

1. 原子性（Atomicity）：每个事务都是一个整体，不可再拆分，事务中所有的 SQL*语句要么都执行成功，要么都失败。
2. 一致性（Consistency）：事务在 执行前 数据库的状态与 执行后 数据库的 状态保持一致。如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000
3. 隔离性（Isolation）：事务与事务之间不应该 相互影响，执行时保持 隔离 的状态
4. 持久性（Durability）：一旦事务 执行成功，对数据库的修改是 持久 的。就算关机，也能保存下来

事务的隔离级别

• 概念：多个事务之间相互隔离，相互独立。如果多个事务操作同一批数据，则可能会引发一些问题，设置不同的隔离级别可以解决相关问题

• 存在问题：

  • 并发引发的问题	含义
    脏读	一个事务读取到了另一个事务中尚未提交的数据
    不可重复读（虚读）	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题
    幻读	一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题

  
  

• 隔离级别（隔离级别越高，安全性越好，性能越低）

  • 级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
    1	读未提交	read uncommitted	是	是	是
    2	读已提交	read committed	否	是	是	Oracle 和 SQL server
    3	可重复读	repeatable read	否	否	是	MySQL
    4	串行化	serializable	否	否	否

• MySQL隔离级别相关	命令
  数据库查询隔离级别	SELECT @@tx_isolation; 新版用：SELECT @@transaction_isolation
  数据库设置隔离级别	SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

演示

• 读未提交：

  • 未提交

    • 

  • 其它事务能读取

    • 
    
    

• 读已提交：

  • 未提交

    • 

  • 其它事务不能读取

    • 

  • 当前提交后其它事务可以读取

    • 

    • 

• 可重复读:

  • 当前提交后其它事务不能读取

    • 

    • 

  • 其它事务提交后可以读取

    • 

• 串行化:
  • 类似于 java中的锁
  • 如果前一个事务 未提交，后一个事务就 不执行语句

DCL

• DBA：数据库管理员
• DCL（Data Control Language），数据控制语言，如Grant、Rollback等等，常见于数据库安全管理，多数人一般很少用。

用户管理

1. 添加用户

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

2. 删除用户

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

3. 修改用户密码

   • UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
     			
     SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
     			
     

4. 查询用户

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

   • 通配符：% 表示可以在任意主机使用用户登录数据库

5. MySQL中忘记了root用户的密码

   1. cmd – > net stop mysql 停止mysql服务（需要管理员运行该cmd）
   2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
   3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
   4. use mysql;
   5. update user set password = password(‘你的新密码’) where user = ‘root’;
   6. 关闭两个窗口
   7. 打开任务管理器，手动结束mysqld.exe 的进程
   8. 启动mysql服务
   9. 使用新密码登录

权限管理

1. 查询权限

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

2. 授予权限

   • -- 授予权限
     grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
     -- 给张三用户授予所有权限，在任意数据库任意表上
     GRANT ALL ON *.* TO 'zhangsan'@'localhost';
     

3. 撤销权限

   • -- 撤销权限：
     revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
     REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
     

SQL、DB、DBMS

概念

• DB:

  • DataBase（数据库，数据库实际上在硬盘上是以文件的形式存在）

• DBMS:

  • DataBase Management System（数据库管理系统，常见的有：MySQL，Oracle，DB2，Sybase，sqlServer…）

• SQL:

  • 结构化查询语言。标准的sql适用于所有的数据库产品
  • SQL语句属于高级语言，同样在执行时，同样也是先编译再执行

关系

• DBMS负责执行sql，通过sql来操作DB里面的数据
• DBMS -> SQL -> DB

表（table）

概念

• Table是数据库的基本组成单元，所有的数据都以表格的形式组织，这样做的目的是可读性强

组成

• 一个表包括行和列
  • 行：被称为数据/记录(data)
  • 列：被称为字段(column)