前言: 数据库

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

数据库最基本的组成单元就是一条一条数据记录, 这个就是数据行
为了让数据更加方便管理, 通常我们会使用表格来描述, 这个就是数据表
很多数据表放在一起就形成数据库

数据库的基本操作: 添加操作, 更新操作, 删除操作, 查询操作

一. MySQL概述

1.什么是MySQL

定义

MySQL是一种关系型数据库软件, 基于C/S架构

• 数据库: 存储数据的仓库
• 关系型: 数据与数据之间存在关系
• C/S架构: Client(客户端)和Server(服务端) MySQL57 版本

关系型:

关系型数据库有明确的库/表/行的关系 数据库由数据表组成 数据表由数据行(记录)组成

2.MySQL的安装

1) 安装MySQL服务端

2) 安装MySQL客户端

MySQL的客户端有很多种. 这里, 主要使用的是Navicate

3.如何理解C/S架构

MySQL分为MySQL客户端和MySQL服务端
不管是客户端还是服务端, 本质上依然是一个程序

服务端: 提供服务的程序
客户端: 连接服务端执行操作的程序

二. MySQL基本操作

一般MySQL操作都是在操作MySQL客户端
这里, 我们可以使用一些图形化的软件, 如Navicate/MySQL-Front.
如果需要操作MySQL, 需要先使用MySQL客户端连接到服务端

1.客户端连接与基本操作

1) 连接数据库

点击后弹出如下窗口

2) 创建数据库

3) 选择数据库

两种操作

• 双击

• 右键->打开数据库

  3) 建库建表

  2.SQL概念

  1) 什么是SQL

  Structured Query Language 结构化查询语言

  2) SQL的作用

• 是一种所有关系型数据库的查询规范, 不同的数据库都支持

• 通用的数据库操作语言, 可以用在不同的数据库中

  3) SQL语句分类

• Data Definition Language(DDL数据定义语言) 如: 建库, 建表

• Data Manipulation Language(DML 数据操纵语言) 如: 对表记录的增/删/改
• Data Query Language(DQL 数据查询语言) 如: 对表的查询操作

• Data Control Language(DCL 数据控制语言) 如: 对用户权限的设置

  4) MySQL的语法

• 每条语句以分号结尾

• SQL中不区分大小写, 推荐关键字大写以区分

  三. 数据类型

  在 MySQL中, 将数据分为三大类

• 数值型

• 字符串
• 日期与时间

1.数值—整型

CREATE TABLE `t_int` (
    age tinyint unsigned,
    stu_sn smallint unsigned,
    goods_sn int unsigned,
    mayun bigint
);

2.数值—小数

小数分为

• 浮点
  • 单精度
  • 双精度
• 定点

浮点型的数据不精确, 所以一般表示价格, 我们使用定点型

CREATE TABLE `t_float` (
    distance float(10,2),
    weight float(5,2),
    price decimal(10,2)
);

3.字符串

char: 拿时间换空间, 追求时间,浪费空间
varchar: 拿空间换时间, 追求空间,浪费时间

CREATE TABLE `t_char` (
    phone char(11),
    password char(32),
    name varchar(16),
    email varchar(255),
    content text,
    sex enum('男','女')
);

说明

char与varchar的区别

• char是固定的长度 char(5) 保存 abc, 实际占用的空间5个
• varchar最大长度 varchar(5)保存abc, 实际占用的空间3个

varchar用多少就占多少

4.日期与时间

CREATE TABLE `t_time` (
    birthday date,
    start_time datetime,
    end_time datetime,
    created_time timestamp
);

六. 字段属性

字段属性又叫字段约束, 通常用来限定(约束)当前列

• 能否为空
• 默认值

  1.能否为空

  如果需要当前字段不能为空, 默认情况下是可以为空的

  关键词

not null

语法

CREATE TABLE `t_null` (
    id int,
    name varchar(16) not null
);

2.默认值

有些情况下，我们希望某个字段拥有默认值，比如

• 性别的字段，拥有默认值为“男”
• 籍贯的字段，拥有默认值为“汉”

  关键词

default

语法

CREATE TABLE `t_default` (
    name varchar(16),
    sex enum('男','女') default '男'
);

3.主键

如果我们可以通过某一列进行唯一的标识每一条记录，我们就可以把这个字段当做主键

关键词

primary key

语法

CREATE TABLE `表名` (
    字段 字段类型 primary key
);

4.自增

如果数据表中的某个字段，需要进行自动增长，我们可以将其定义为自动增长, 一般自增跟主键连用

关键词

auto_increment

语法

CREATE TABLE `表名` (
    字段 字段类型 primary key auto_increment
);

示例

CREATE TABLE `t_primary` (
    id int primary key auto_increment
);

5.备注

在创建字段时, 一般需要给一定的说明

关键词

comment

语法

CREATE TABLE `表名` (
    字段 字段类型 字段属性 comment '备注'
);

示例

CREATE TABLE `t_comment` (
    id int primary key auto_increment comment '主键',
    name varchar(16) not null comment '姓名'
);

七. 数据的写操作(重点)

MySQL最主要的功能是管理数据, 也就是增/删/改/查, 也叫CURD, 从大的方面划分就是写操作和读操作

1.添加操作

1) 基本用法

把握3点

1. 插入哪张表?
2. 插入表的哪些列(字段)?
3. 这些列的值?

   语法

INSERT INTO `表名`
(`字段1`, ... `字段n`)
VALUES
(值1, 值2, 值n)

示例一. 插入单条记录

# 先选择db1数据库
USE `db1`;
# 向t1表中添加一条数据
INSERT INTO `t1`
(`id`, `name`, `age`)
VALUES
(1, 'xiaoming', 20);

示例二. 一次插入多条记录

# 向t1表中一次添加多条记录
INSERT INTO `t1`
(`id`, `name`, `age`)
VALUES
(2, 'xiaomei', 18),
(3, 'xiaoqiang', 22);

一个( ) : 代表一条记录
多个( )使用, 隔开

注意:

• 插入数据的类型最好与要求的类型一致
• 插入的数据必须在有效范围内
• 插入的数据为null时给默认值
• 自增会在原来的基础上+1

  2.修改操作

  4点

1. 修改哪张表?
2. 修改表的哪些列(字段)?
3. 这些列的值?
4. 条件

   语法

UPDATE `表名`
SET
`字段1`=值1,
`字段2`=值2,
...
WHERE 条件

示例

UPDATE `student`
SET
`name`='xiaoming-new'
WHERE `id`=1;

3.删除操作

2点

1. 删除哪张表?
2. 删除哪些行?

   语法

DELETE FROM `表名` WHERE 条件

示例

DELETE FROM `student` WHERE `id`=3;

八. 查询—基本查询(单表)

0.单表约束

主键约束
primary key auto_increment
非空约束
not null
唯一约束
unique

1.基本用法

1) 基本查询

3点:

1. 查哪张表的数据?
2. 查哪些列的数据?
3. 条件

   语法

SELECT 字段1, 字段2, ...字段n 
FROM 表名
[WHERE 条件]

示例1—查询指定的列

# 查询t1表中的name和age字段
SELECT `name`, `age` FROM `t1`;

示例2—查询所有的列

# 查询t1表中的所有数据
SELECT * FROM `t1`;

• * : 表示所有字段

  示例3—根据条件查询

# 查询xiaomei的年龄
SELECT `age` FROM `t1` WHERE `name`='xiaomei';

2) 别名

可以给指定的列取一个别名, 一般用于多表查询中

语法

SELECT 字段1 as 别名1, 字段2 as 别名2 FROM 表名 as 表别名

其中, as关键字可以省略

示例

SELECT `name` as `username` FROM `t1` as stu;

2.查询模型

1) 基本点

3个点

1. 列当作变量
2. 列可以参与计算
3. where条件判断真假

   如何理解

逐行比较, 当where条件为真的时候, 把列(变量)的值取出来

示例

SELECT name, age FROM `student`;

示例2

# 查询本店价比市场价低多少(计算差价)
SELECT market_price - shop_price FROM `goods`;

2) where条件

MySQL中常用的运算符如下表:

3) 模糊查询

模糊查询规则
%代表 零个,一个或者多个字符
_代表 匹配单个字符

• like ‘yy%’ 匹配以yy开头的字符串
• like ‘%yy’ 匹配以yy结尾的字符串
• like ‘%yy%’ 匹配包含yy的字符串

1. like ‘yy_’ 匹配以yy开头,后面只有一个字符的字符串
2. like ‘_yy’ 匹配以yy结尾,前面只有一个字符的字符串

   3.查询案例

   以商品表为例, 做如下查询练习
   1.1 查询主键id为32的商品

   SELECT * FROM `goods` WHERE `id`=32;

   1.2 不属第3栏目的所有商品id和名称

   SELECT `id`, `goods_name`, `cate_id`
   FROM `goods`
   WHERE `cate_id` != 3;

   1.3 本店价格高于3000元的商品

   SELECT `goods_name`, `shop_price`
   FROM `goods`
   WHERE `shop_price` > 3000;

   1.4 本店价格低于或等于100元的商品

   SELECT `goods_name`, `shop_price`
   FROM `goods`
   WHERE `shop_price` <= 100;

   1.5 取出第4栏目或第11栏目的商品(使用or或者使用in) ```sql

   使用or

   SELECT goods_name, cate_id FROM goods WHERE cate_id=4 or cate_id=11;

不使用or

SELECT goods_name, cate_id FROM goods WHERE cate_id in (4, 11);

1.6 取出100<=本店价格<=500的商品(使用and或者between)
```sql
# 使用and
SELECT `goods_name`, `shop_price`
FROM `goods`
WHERE `shop_price` >= 100 and `shop_price` <= 500;
# 不使用and
SELECT `goods_name`, `shop_price`
FROM `goods`
WHERE `shop_price` between 100 and 500;

1.7 取出不属于第3栏目且不属于第11栏目的商品(使用and或使用not in分别实现)

# 使用and
SELECT `goods_name`, `cate_id`
FROM `goods`
WHERE `cate_id` != 3 and `cate_id` != 11;
# 使用 not in
SELECT `goods_name`, `cate_id`
FROM `goods`
WHERE `cate_id` not in (3, 11);

1.8 取出第3个栏目下面价格<1000或>3000,并且点击量>5的商品

SELECT `goods_name`, `cate_id`, `shop_price`, `click_count`
FROM `goods`
WHERE `cate_id`=3
and (`shop_price` < 1000 or `shop_price` > 3000)
and `click_count` > 5;

1.9 取出名字以”诺基亚”开头的商品

SELECT `goods_name`
FROM `goods`
WHERE `goods_name` LIKE '诺基亚%';

1.10 取出名字为”诺基亚Nxx”的手机

SELECT `goods_name`
FROM `goods`
WHERE `goods_name` LIKE '诺基亚N__';

九. 进阶查询操作(select子句)(单表)

1.聚合函数查询

聚合函数

• count: 计算总数
• sum: 求和
• max: 最大值
• min: 最小值
• avg: 平均值 ```plsql — 计算product表中总记录条数 SELECT COUNT(*) FROM product

— 获取最高价格 SELECT MAX(price) FROM product

— 获取最低库存 SELECT MIN(IFNULL(stock,0)) FROM product

— 获取总库存数量 SELECT sum(IFNULL(stock,0)) FROM product

— 获取品牌为苹果的总库存数量 SELECT SUM(stock) FROM product WHERE brand =’苹果’

— 获取品牌为小米的平均商品价格 SELECT avg(price) FROM product WHERE brand =’小米’

<a name="WGGhB"></a>
## 2.group by分组查询
标准语法：<br />    SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
```plsql
-- 按照品牌分组，获取每组商品的总金额
SELECT brand, SUM(price) FROM product GROUP BY brand
-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand, SUM(price) FROM product WHERE price>4000 GROUP BY brand
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand, SUM(price) FROM product WHERE price>4000 GROUP BY brand HAVING SUM(price)>7000
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand, SUM(price) FROM product WHERE price>4000 GROUP BY brand  HAVING SUM(price)>7000 ORDER BY SUM(price) DESC

3.having子句

having跟where一样, 用来做条件的判断和数据的筛选过滤
where和having的区别

• where中的条件是表字段
• having中的条件是结果集 (对分组后的结果再进行判断)

  -- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
  SELECT brand, SUM(price) FROM product WHERE price>4000 GROUP BY brand  HAVING SUM(price)>7000 ORDER BY SUM(price) DESC

  4.order by排序查询

  标准语法：
  SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2; ```plsql — 按照库存升序排序 SELECT * FROM product ORDER BY stock ASC

— 查询名称中包含手机的商品信息。按照金额降序排序 SELECT * FROM product WHERE NAME LIKE (‘%手机%’) ORDER BY price DESC

— 按照金额升序排序，如果金额相同，按照库存降序排列 SELECT * FROM product ORDER BY price ASC , stock DESC

<a name="BCzqK"></a>
## 5.limit分页查询
LIMIT 起始索引,每页显示的条数;  (起始索引是从0开始)<br />    公式：当前页数 = (当前页数-1) * 每页显示的条数<br /> 或    分页offset = (当前的页码-1) *每页要显示的记录数
```plsql
-- 每页显示3条数据
SELECT * FROM product LIMIT 2,3
-- 第1页  起始索引=(1-1) * 3
SELECT * FROM product LIMIT 0,3
-- 第2页  起始索引=(2-1) * 3
SELECT * FROM product LIMIT 3,3
-- 第3页  起始索引=(3-1) * 3
SELECT * FROM product LIMIT 6,3

6.书写顺序及注意

书写的顺序(更像一段话)
select—from—where—group by—having—order by—limit

执行的顺序
from—where—group by—having—select—order by—limit

form:需要从哪些表中查询数据
where:根据条件过滤表中的数据
—过滤的是表中所有的数据
group by;将where过滤之后的数据进行分组
having:对上面已经分组的每一组数据进行过滤
—过滤的是分组之后的每一组数据
select:查看结果集的哪一列
order by:把结果集按照什么顺序展示
limit:取最终结果集的前几页

注意:
分组之后的查询字段,要么是分组字段,要么是聚合函数,其他字段没有任何意义

十. E-R实体关系模型

1) 实体

实体的定义 客观存在并相互区别的一个事物称为实体（Entity） E: entity(实体) R: relation(关系)

2) 实体属性

属性的定义 实体的某些特性称为实体的属性（Attribute），一个实体可以拥有多个属性

举例: 如学生有学号、姓名、性别、出生日期等, 相当于字段

3) 实体集

定义 实体的集合, 相当于一张表

2.关系

表与表之间的关系一共有4种

• 一对一
• 一对多
• 多对一
• 多对多

  1) 一对一关系

  定义

一张表的一条记录对应另一张表的一条记录

关联条件(建表原则)

在任意一方添加外键指向另外一方的主键,并且让外键唯一

2) 一对多关系

定义

一张表的一条记录对应另一张表的多条记录

关联条件(建表原则)

在多的一方添加外键,指向一的一方的主键

3) 多对一关系

定义

一张表的多条记录对应另一张表的一条记录

关联条件(建表原则)

在多的一方添加外键,指向一的一方的主键

4) 多对多关系

定义

表A的一条记录对应表B的多条记录
同时, 表B的一条记录也对应表A的多条记录

关联条件(建表原则)

需要借助第三张中间表,中间表至少有两个字段,这两个字段作为第三张表的外键,分别指向另外两张表的主键

十一 . 多表查询(多表)

0.多表约束

外键约束
格式 alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主键列名)

1.交叉查询

交叉查询(笛卡尔值:查询的是两张或多张表所有组成情况)
所有表的全部组合情况,是所有查询的基础

SELECT * FROM department,employee

2,内连接查询

内连接查询 :查询两张或多张表的交集

隐式内连接

没有使用inner join关键字

SELECT * FROM department,employee WHERE department.id = employee.dep_id

显示内连接

使用inner join关键字

SELECT * FROM department INNER JOIN employee ON department.id = employee.dep_id

3,外连接查询

左外连接(left join)

查询左边表的全部和两张表的交集

SELECT * FROM department LEFT OUTER JOIN employee ON department.id = employee.dep_id

右外连接(right join)

查询右边表的全部和两张表的交集

SELECT * FROM department RIGHT OUTER JOIN employee ON department.id = employee.dep_id

4,子查询

子查询: sql语句嵌套sql语句
1.结果是单行单列(结果只有一个),子查询可以作为条件用运算符去连接

-- 查询年龄最大的员工的信息
--  第一步,现在员工表中查询最大年龄
SELECT MAX(age) FROM employee
--  第二步:在员工表中查询年龄和最大年龄相等的员工
SELECT * FROM employee WHERE age = (SELECT MAX(age) FROM employee)

2,结果是多行单列,子查询可以作为条件,用in, not in, any, all, exist来连接

--  查询研发部和学工部所有的员工信息
SELECT * FROM department,employee WHERE department.id = employee.dep_id AND department.id in(1,3)
SELECT id FROM department WHERE dep_name='研发部' OR dep_name= '学工部'
SELECT* FROM employee WHERE dep_id in(SELECT id FROM department WHERE dep_name='研发部' OR dep_name= '学工部')

-- ANY:任意一个 (只要有一个满足就成立)
-- 查询大于任意一个(员工所在部门的遍号dep_id)的部门信息
-- 第一步:查询员工所在部门的编号dep_id
SELECT dep_id FROM employee
-- 第二步:在部门表中查询大于上述结果中的任意一个
SELECT * FROM department WHERE id>ANY(SELECT dep_id FROM employee)
-- ALL:所有(所有满足才能成立)
-- 查询大于所有(员工所在部门的遍号dep_id)的部门信息
-- 第一步:查询员工所在部门的编号dep_id
SELECT dep_id FROM employee
-- 第二步:在部门表中查询大于上述结果中的所有
SELECT * FROM department WHERE id > ALL(SELECT dep_id FROM employee)
-- EXISTS: 只要查询的有结果,前面就会执行
-- 查询所有的部门信息(前提员工表中有'大王'这个人)
-- 第一步:查询员工表中有没有大王
SELECT * FROM employee WHERE `NAME`='大王'
-- 第二步:根据上述结果来执行查询部门信息的语句
SELECT * FROM department WHERE EXISTS (SELECT * FROM employee WHERE `NAME`='大王')

3,查询多行多列,子查询的结果可以作为一张虚拟的表来查询

-- 查询员工年龄大于20岁的员工信息和他所在的部门信息
SELECT * FROM employee INNER JOIN department ON employee.dep_id=department.id AND employee.age >20
-- 方式一 子查询
-- 第一步:查询年龄大于20岁的员工信息
SELECT * FROM employee WHERE age>20
-- 第二步:将第一步的结果作为一张虚拟的表和部门表一起查询
SELECT * FROM department t1,(SELECT * FROM employee WHERE age>20) t2 WHERE t1.id = t2.dep_id

5,自关联查询

概念: 是一张特殊的表,可以将它当做两张表,起不同的别名,用条件关联上

/*
分析
    员工信息 employee表
    条件：employee.mgr = employee.id
    查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
-- 自关联查询
SELECT
e1.id,e1.name,e1.mgr,e2.name
FROM
employee_1 e1
LEFT JOIN
employee_1 e2
ON 
e1.mgr = e2.id -- e1的上级编号 = e2的id值

6,多表查询技巧

99%都是内连接查询
1,需要查什么字段
2,查这些字段,需要动用哪几张表
3,这些表之间的关联关系(外键)+其它条件