javaweb

数据库

关系型数据库：

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库

如下图，订单信息表 和 客户信息表 都是有行有列二维表我们将这样的称为关系型数据库。

image-20210721205130231

接下来看关系型数据库的优点：

  • 都是使用表结构，格式一致，易于维护。
  • 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
    • 关系型数据库都可以通过SQL进行操作，所以使用方便。
    • 复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。
  • 数据存储在磁盘中，安全。

数据模型：

image-20210721212754568

如上图，我们通过客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹。比如可以通过SQL语句创建一个数据库（数据库名称为db1），语句如下。该语句咱们后面会学习。

image-20210721213349761

我们可以在数据库安装目录下的data目录下看到多了一个 db1 的文件夹。所以，在MySQL中一个数据库对应到磁盘上的一个文件夹。

而一个数据库下可以创建多张表，我们到MySQL中自带的mysql数据库的文件夹目录下：

image-20210721214029913

而上图中右边的 db.frm 是表文件，db.MYD 是数据文件，通过这两个文件就可以查询到数据展示成二维表的效果。

小结：

  • MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
  • 在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件
  • 每张表可以存储多条数据，数据会被存储到磁盘中 MYD 文件中

DDL:操作数据库

1
2
3
4
net start mysql // 启动mysql服务
net stop mysql // 停止mysql服务
mysql -uroot -p1234 //登录
exit //退出

  • 查询所有的数据库

    1
    Show Database;

  • 创建数据库

    1
    2
    3
    Create Database 数据库名称;
    Create Database if not exists 数据库名称;
    # 创建数据库(判断，如果不存在则创建)

  • 删除数据库

    1
    2
    Drop Database 数据库名称;
    Drop Database if exists 数据库名称;

  • 使用数据库

    1
    2
    3
    USE 数据库名称;
    SELECT DATABASE(); 
    -- 查看当前使用的数据库

  • 查询当前数据库下所有表名称

    1
    Show Tables;

  • 查询表结构

    1
    Desc 表名称;

  • 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TABLE 表名 (
    字段名1 数据类型1,
    字段名2 数据类型2,
    …
    字段名n 数据类型n
    );
    -- 最后一行末尾，不能加逗号
    create table tb_user (
    id int,
    username varchar(20),
    password varchar(32)
    );

    image-20221227223328878

  • 删除表

    1
    2
    3
    DROP TABLE 表名;
    DROP TABLE IF EXISTS 表名;
    -- 删除表时判断表是否存在

  • 修改表名

    1
    2
    3
    ALTER TABLE 表名 RENAME TO 新的表名;
    -- 将表名student修改为stu
    alter table student rename to stu;

  • 添加一列

    1
    2
    3
    ALTER TABLE 表名 ADD 列名 数据类型;
    -- 给stu表添加一列address，该字段类型是varchar(50)
    alter table stu add address varchar(50);

  • 修改数据类型

    1
    2
    3
    ALTER TABLE 表名 MODIFY 列名 新数据类型;
    -- 将stu表中的address字段的类型改为 char(50)
    alter table stu modify address char(50);

  • 修改列名和数据类型

    1
    2
    3
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
    -- 将stu表中的address字段名改为 addr，类型改为varchar(50)
    alter table stu change address addr varchar(50);

  • 删除列

    1
    2
    3
    ALTER TABLE 表名 DROP 列名;
    -- 将stu表中的addr字段 删除
    alter table stu drop addr;

数据类型

MySQL 支持多种类型，可以分为三类：

  • 数值

    tinyint : 小整数型，占一个字节
    int ： 大整数类型，占四个字节 eg ： age int
    double ： 浮点类型
    使用格式： 字段名 double(总长度,小数点后保留的位数)
    eg ： score double(5,2)

  • 日期

    date ： 日期值。只包含年月日 eg ：birthday date ：
    datetime ： 混合日期和时间值。包含年月日时分秒

  • 字符串

    char ： 定长字符串。
    优点：存储性能高
    缺点：浪费空间
    eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个空 间
    varchar ： 变长字符串。
    优点：节约空间
    缺点：存储性能底
    eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

DML：增删改操作

  • 添加数据

    1
    2
    3
    4
    5
    6
    7
    INSERT INTO 表名(列名1,列名2,…) 1 VALUES(值1,值2,…);
    -- 给指定列添加数据
    INSERT INTO 表名 VALUES(值1,值2,…);
    -- 给全部列添加数据
    INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
    INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
    -- 批量添加数据

  • 修改数据

    1
    2
    3
    UPDATE 表名 SET 列名1=值1,列1 名2=值2,… [WHERE 条件] ;
    -- 1. 修改语句中如果不加条件，则将所有数据都修改！
    -- 2. 像上面的语句中的中括号，表示在写sql语句中可以省略这部分

    image-20221227231703916

  • 删除数据

    1
    2
    3
    DELETE FROM 表名 [WHERE 条件] ;
    delete from stu;
    -- 删除stu表中所有的数据

DQL：查询数据库

  • 基础查询
1
2
3
4
5
6
7
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名;
-- 查询所有数据
SELECT DISTINCT 字段列表 FROM 表名;
-- 去除重复记录
AS:也可以省略
-- 起别名

image-20221227233429936

  • 条件查询

    1
    SELECT 字段列表 FROM 表名 WHERE 条件列表;

    image-20221227233625713

  • 模糊查询

    模糊查询使用like关键字，可以使用通配符进行占位:
    （1）_ : 代表单个任意字符
    （2）% : 代表任意个数字符

    1
    select * from stu where name like '%德%';

  • 排序查询

    1
    SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

    ASC ： 升序排列 （默认值）
    DESC ： 降序排列
    注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

  • 分组查询

    1
    2
    3
    4
    5
    6
    SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
    /*where和having区别：
    执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过
    滤。
    可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。*/
    -- where > 聚合函数 > having

    image-20221227234552672

    // null 值不参与所有聚合函数运算

  • 分页查询

    1
    2
    SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
    -- 上述语句中的起始索引是从0开始

约束

约束是作用于表中列上的规则，用于限制加入表的数据，保证了数据库中数据的正确性、有效性和完整性

非空约束

用于保证列中所有数据不能有NULL值

1
2
3
4
5
6
7
8
9
10
11
-- 创建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
…
);

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

-- 删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;

唯一约束

唯一约束用于保证列中所有数据各不相同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
-- AUTO_INCREMENT: 当不指定值时自动增长
…
);
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [约束名称] UNIQUE(列名)
);

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

-- 删除约束
ALTER TABLE 表名 DROP INDEX 字段名;

主键约束

主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表时添加主键约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
…
);
CREATE TABLE 表名(
列名 数据类型,
[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

-- 删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;

默认约束

保存数据时，未指定值则采用默认值

1
2
3
4
5
6
7
8
9
10
11
-- 创建表时添加默认约束
CREATE TABLE 表名(
列名 数据类型 DEFAULT 默认值,
…
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

-- 删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

外键约束

让两个表的数据之间建立链接，保证数据的一致性和完整性。

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
CONSTRAINT [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

-- 删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

多表查询

1
2
select * from emp , dept;
select * from emp , dept where emp.dep_id = dept.did;

image-20221228233317994

内连接查询

1
2
3
4
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接	
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;