一、数据库基础

1、概述

数据库是数据的组织性质，相比于文件读取方便、更安全，数据库是一种有组织的数据集合。多个程序共享一个数据库，这样使得数据库更容易被维护和更新。

java应用与数据库管理系统

2、数据库使用

• 表：关系型数据库中最基本的数据组织形式，用于存储数据
• 行：表中的每个数据实例，也称为记录或元组
• 列：表中每个数据属性，也称为字段
• 键：表中用来唯一标识每个数据实例的属性或属性组合，被称为键；主键是唯一标识一条记录的键，而外键是指向其他表中主键的键
• 视图：视图是一个虚拟表，它基于存储在数据库中的一个或多个表，可以通过查询语句访问；视图可以隐藏底层表中的某些数据，并允许用户只访问他们需要的数据
• 索引：索引是一种数据结构，用于加速对表中数据的访问；它们允许用户快速定位数据，而不必扫描整个表
• 触发器：触发器是一段在特定条件下自动执行的代码，当特定的事件发生时，触发器会自动运行并执行特定的操作
• 事务：事务是一个由一个或多个数据库操作组成的序列；事务必须满足ACID（原子性、一致性、隔离性和持久性）属性，以保证数据的完整性和一致性
• 数据类型：数据类型指定了在表中存储的数据的类型，常见的数据类型包括整数、字符串、日期和时间等

SQL分类

按照用途和功能，SQL可以分为以下几类：*（最后要记住）

DDL（Data Definition Language）：数据定义语言，用于定义数据库和表的结构，例如创建、修改和删除数据库、表、列等。常见的DDL语句包括CREATE、ALTER和DROP。

DML（Data Manipulation Language）：数据操作语言，用于对数据库中的数据进行操作，例如插入、修改、删除、查询数据等。常见的DML语句包括SELECT、INSERT、UPDATE和DELETE。

DCL（Data Control Language）：数据控制语言，用于控制数据库的访问和权限，例如授权、撤销权限等。常见的DCL语句包括GRANT和REVOKE。

TCL（Transaction Control Language）：事务控制语言，用于管理事务，例如提交、回滚、保存点等。常见的TCL语句包括COMMIT、ROLLBACK和SAVEPOINT。

二、操作数据库和表

1、DDL（数据定义语言）

数据定义语言基本语法：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET=字符集名称];

• utf8 支持1~3字节（不支持表情符号等）
• utf8mb 支持1~4字节编码（支持表情符等）

（1）查看和删除数据库

查看数据库：

SHOW CREATE DATABASE 数据库名

删除数据库：

DROP DATABASE 数据库名;

（2）表操作（DDL）

创建表的语法:

REATE TABLE 表名 (
    列名1 数据类型 约束,
    列名2 数据类型 约束,
    列名3数据类型 约束,
 ...
    PRIMARY KEY (列名…)
);

实例（在idea中编写）：

CREATE TABLE user(
    id INT,  -- 创建一个字段（列）字段名是id，类型是int
    username VARCHAR(30), -- 在mysql中varchar是可变长字符串，这列最多只能存放30个字节
    password VARCHAR(30), -- 采用utf8编码最多存10个汉字
    age INT(3) -- int也可以指定长度 指的是几位数，这个中最大的是3位置最大999
);

（3）表的重命名

-- 重命名语法 RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2,....

RENAME TABLE user to newUser;

（4）删除表

-- 删除表语法，DROP TABLE 表名1, 表名2,.....
    
DROP TABLE t_user;

（5）修改表

-- 修改表 ALTER TABLE 操作语句
-- 添加新的字段 末尾添加
ALTER TABLE hero ADD COLUMN gender VARCHAR(10);

-- 添加新的字段，在表的一开始添加字段
ALTER TABLE hero ADD COLUMN id INT FIRST;

-- 添加新的字段，在表的任意位置添加，在name字段的后面添加password字段
ALTER TABLE hero ADD COLUMN pwd VARCHAR(30) AFTER name;

-- 删除表中的字段，ALTER TABLE 表名 DROP COLUMN 字段名
-- 删除表中的pwd
ALTER TABLE hero DROP column pwd;

-- 修改表的字段
-- 修改字段的名字 ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名
-- 把name字段修改为username
ALTER  TABLE hero RENAME COLUMN name TO username;

-- 修改表中字段的长度 ALTER TABLE 表名 MODIFY COLUMN 字段名 类型(新长度)
-- 修改hero表中的username字段 长度修改为200 修改原则（长度只放大 不缩小）
ALTER TABLE hero MODIFY COLUMN username VARCHAR(200);

-- 修改表中字段类型 ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型（长度）
-- 修改表中的gender字段类型 有VARCHAR修改为CHAR（定长字符） 类型修改要慎重
ALTER TABLE hero MODIFY COLUMN gender CHAR(10);

2、DML（数据操作语言）

（1）SELECT（查询）

-- 查询表中的数据
    
SELECT * FROM person;

（2）INSERT（插入）

-- 向表中插入数据 基本语法：INSERT INTO 表名 (字段名1，字段名2，....) VALUES(值1，值2，....)
    
INSERT INTO person (name, age) VALUES ('张三',20);

INSERT INTO person (age, name) VALUES (21,'李四');

-- 插入默认值 直接使用DEFAULT关键字
    
INSERT INTO person(name, age) VALUES (DEFAULT,20);

-- 插入默认值 不指定设置了默认值的字段
    
INSERT INTO person() VALUES ();

-- 为所有字段插入数据
    
INSERT INTO person VALUES ('SmallG',21);

-- 为所有字段插入数据
    
INSERT INTO person VALUES ('SmallG',21);

INSERT INTO person VALUES ('Smally',DEFAULT);

（3）UPDATE（更新/修改）

WHERE中还可以使用如:>,>=,<,<=,<>(不等于使用"<>",而"!="不是所有数据库都支持)。

-- 修改表中的数据 基本语法：UPDATE 表名 SET 字段名1=值1，字段名2=值2，...;
-- 修改person表中的年龄 改为40 所有人都修改批量操作 不推荐
    
UPDATE person SET age=40;

-- WHERE子句 修改语句的条件
-- 修改张三的年龄 修改为36
    
UPDATE person SET age=36 WHERE name='张三';

-- 所有人的年龄加1岁
    
UPDATE person SET age=age+1;

-- 同时修改多个字段 字段之间用逗号隔开
    
UPDATE person SET name='李老四',age=55 WHERE name ='李四';

（4）DELETE（删除）

-- 删除表中的数据 基本语法：DELETE FROM 表名 WHERE 条件;
-- 删除person表中名字为李老四的人

DELETE FROM person WHERE name = '李老四';

-- 执行没有符合条件的删除 不会出现错误

DELETE FROM person WHERE age<25;

-- 清空表中的数据 执行时要慎重

DELETE FROM person;

三、字段定义

1、数据类型

• 整数类型：包括TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT等类型
• 浮点数类型：包括FLOAT、DOUBLE和DECIMAL等类型
• 字符串类型：包括CHAR、VARCHAR、TEXT、MEDIUMTEXT、LONGTEXT和ENUM等类型
• 日期和时间类型：包括DATE、TIME、DATETIME和TIMESTAMP等类型
• 二进制类型：包括BLOB、MEDIUMBLOB和LONGBLOB等类型
• 布尔类型：包括BOOLEAN或BOOL等类型

（1）整数类型

• TINYINT：存储-128到127的整数，或0到255的无符号整数。示例：TINYINT(3)。
• SMALLINT：存储-32,768到32,767的整数，或0到65,535的无符号整数。示例：SMALLINT(5) UNSIGNED。
• MEDIUMINT：存储-8,388,608到8,388,607的整数，或0到16,777,215的无符号整数。示例：MEDIUMINT(8)。
• INT或INTEGER：存储-2,147,483,648到2,147,483,647的整数，或0到4,294,967,295的无符号整数。示例：INT(11) UNSIGNED。
• BIGINT：存储-9,223,372,036,854,775,808到9,223,372,036,854,775,807的整数，或0到18,446,744,073,709,551,615的无符号整数。示例：BIGINT(20)。

（2）浮点数类型

• FLOAT：单精度浮点数，占用4个字节，用于存储比较小的小数，精度为7位。FLOAT类型的数字可以使用“f”或“F”后缀来表示。FLOAT类型的长度可以指定为FLOAT(m,n)，其中m表示总位数，n表示小数位数。如果未指定n，则默认为0。
• DOUBLE：双精度浮点数，占用8个字节，用于存储较大或较小的小数，精度为15位。DOUBLE类型的数字可以使用“d”或“D”后缀来表示。DOUBLE类型的长度可以指定为DOUBLE(m,n)，其中m表示总位数，n表示小数位数。如果未指定n，则默认为0。
• DECIMAL：用于存储精确小数的数据类型。DECIMAL类型可以存储非常大或非常小的数字，并且具有固定的精度，不会出现精度丢失的问题。DECIMAL类型的长度可以指定为DECIMAL(m,n)，其中m表示总位数，n表示小数位数。通常存储与价格金钱相关的数据。

（3）字符类型

• CHAR：这是一个固定长度的字符类型，长度参数决定了在数据库中存储的字符数，如果输入的字符串长度不够，则会被填充以满足长度要求，这个类型最大的长度参数是255。
• VARCHAR：这是一个变长的字符类型，长度参数决定了一个VARCHAR类型字符串最大可以存储的字节数量
• TINYTEXT：这是一个最大长度为255字节的字符串类型，常用于存储短文本数据。
• TEXT：这是一个最大长度为65535字节的字符串类型，常用于存储长文本数据。

（4）日期和时间类型

• DATE：用于表示年、月、日的日期，其格式为'YYYY-MM-DD'。占用3个字节的存储空间。
• TIME：用于表示时、分、秒及毫秒的时间，其格式为'HH:MM:SS[.fraction]'。占用3-6个字节的存储空间。
• YEAR：用于存储2或4位的年份，其格式为'YYYY'或'YY'。占用1个字节的存储空间。
• DATETIME：用于表示日期和时间，其格式为'YYYY-MM-DD HH:MM:SS[.fraction]'。占用8个字节的存储空间。
• TIMESTAMP：用于表示日期和时间，其格式为'YYYY-MM-DD HH:MM:SS[.fraction]'。占用4个字节的存储空间。

（5）最常用的数据类型

1、INT(m) 整数类型

• 用于存储整数数据，占用4个字节的存储空间，通常用来表示自增ID、计数器等数据
• m表示显示位数

2、CHAR(n) ：定长字符类型，n表示长度，单位是字符，最大值为255，最多可以保存255个字符

3、VARCHAR(n)：用于存储变长字符串数据，n表示长度，但是为字节，最大值为65535

4、TEXT(n)：用于存储大量文本数据，n单位是字符， 最大值为65535

5、DATETIME：用于存储日期和时间数据，精确到秒，格式为YYYY-MM-DD HH:MM:SS，通常用来记录数据创建、更新时间等信息

6、DECIMAL(m,n)：用于存储精度较高的小数数据，可以指定其精度和小数位数，通常用于存储货币、价格等与金钱相关的数据；m是总位数，n是小数位数

7、DOUBLE(m,n)：浮点数底层是8字节浮点数，有舍入误差

实例：

-- 创建数据库表 合理选择数据类型
CREATE TABLE user_info
(
    id       INT,
    username VARCHAR(30),
    mobile   CHAR(11),
    gender   CHAR(1),
    birth    DATETIME,     -- 日期时间类型
    salary   DECIMAL(7, 2) -- 7位数，小数点后两位
);

DESC user_info;

-- 插入数据
INSERT INTO user_info (id, username, mobile, gender, birth, salary)
VALUES (1,'SmallG','19223141130','男','2001-10-05 10:30:20',8888.88);

SELECT * FROM user_info;

-- 对于DATETIME类型 在保存数据的时候可以省略时分秒 不能省略年月日
INSERT INTO user_info (id, username, mobile, gender, birth, salary)
VALUES (1,'SmallY','19223141130','女','2000-11-30',8888.88);

-- 对于小数类型，在插入数据时候 如果超过定义范围，小数部分超过了两位，结果会四舍五入
-- 整数部分超过了会报错
INSERT INTO user_info (id, username, mobile, gender, birth, salary)
VALUES (1,'SmallYY','19223141130','女','2000-11-30',8888.886);

2、约束

• PRIMARY KEY：主键，用于将表中的一列或多列确定为唯一标识，防止重复数据
• FOREIGN KEY：用于将表中的一列或多列与同一表或其他表中的列进行关联，维护数据完整性
• UNIQUE KEY：确保该列（或组合列）中的所有值都是唯一的
• NOT NULL：确保该列中的所有值都非空
• CHECK：用于确认数据符合一定的条件

-- 主键约束 PRIMARY KEY
-- 创建表的时候指定主键 方式1
    
CREATE TABLE user1(
    id INT PRIMARY KEY,  -- 声明一个主键字段 每个表都有一个id字段专门设置主键
    name VARCHAR(30),
    age INT(3)
);

-- 插入数据的时候 主键不能为空 并且也不能重复
    
INSERT INTO user1(id, name, age) VALUES (1,'张三',20);

-- 创建表时指定主键 方式2
CREATE TABLE user2(
    id INT,
    name VARCHAR(30),
    age INT(3),
    PRIMARY KEY (id) -- 设置主键，这种方式可以设置联合主键
);

创建表user3 设置主键自动递增

CREATE TABLE user3(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键为自动递增，初始值为1，以后每一次插入都加1
    name VARCHAR(30),
    age INT(3)
);

创建非空约束

-- 非空约束 NOT NULL 指定一个字段 值不能为空
-- 创建user4 指定name字段不能为空(NOT NULL)
CREATE TABLE user4(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL , -- 设置name字段为非空约束
    age INT(3)
);

-- 修改表的字段 设置为非空约束
ALTER TABLE user3 CHANGE name name VARCHAR(30) NOT NULL ;

唯一性约束

（重点）唯一约束允许值为null值，并且可以有多个null

-- 创建表设置一个字段是唯一的 UNIQUE
-- 创建user5表 设置name是唯一约束
CREATE TABLE user5
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) UNIQUE,-- 设置唯一约束
    age  INT(3)
);

INSERT INTO user5 (id, name, age) VALUES (NULL,'张三',19);

-- ***唯一约束允许值为null，并且可以有多个null
INSERT INTO user5 (id, name, age) VALUES (NULL,null,19);

四、练习

1 数据库操作案例

1、MySQL 中建立数据库，建立表

• 创建数据库mydatabase
• 选择使用数据库mydatabase
• 创建一个新的表mytable, 这个表包含三个属性列：id、name 和 age；id 是自动增长的，name 是一个字符串，age 是一个整数

2、在表的属性前部，中部，尾部插入新的数据列

• 在表的属性列表的开头添加一个名为 address 的新属性列
• 在 name 属性列之后添加一个名为 phone 的新属性列
• 在 age 属性列之后添加一个名为 email 的新属性列

3、给表添加5行数据

• 向 mytable 表中插入五行数据，每行包含 name、age、address、phone 和 email 五个属性列的值

4、为表增加合理的约束

• 将 id 属性列设为主键，确保每个值都唯一
• 将 email 属性列设为唯一，确保每个值都是唯一的
• 添加一个约束来限制 name 属性不为空
• 添加一个约束来限制 age 属性不为空

5、使用UPDATE更改表中的数据

• 更新表中所有行的phone更改，在phone的头部添加"+86 "
• 更改id为1的email为'tom@highedu.cn'

6、将表中的一部分行删除，最后删除全部的行，然后删除 表和数据库

• 将表 mytable 中 id 为 1 的行删除
• 将表 mytable 中的所有行删除
• 将表 mytable 删除
• 将数据库 mydatabase删除

CREATE DATABASE mydatabase;

-- 选择使用数据库mydatabase
USE mydatabase;

-- 创建一个新的表mytable, 这个表包含三个属性列：
-- id、name 和 age；id 是自动增长的，name 是一个字符串，age 是一个整数
CREATE TABLE mytable(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    age INT(3)
);

DROP TABLE mytable;

-- 在表的属性前部，中部，尾部插入新的数据列
-- 在表的属性列表的开头添加一个名为 address 的新属性列
ALTER TABLE mytable ADD address VARCHAR(30) FIRST ;
-- 在 name 属性列之后添加一个名为 phone 的新属性列
ALTER TABLE mytable ADD phone VARCHAR(30) AFTER name;
-- 在 age 属性列之后添加一个名为 email 的新属性列
ALTER TABLE mytable ADD email VARCHAR(30);

-- 给表添加5行数据
-- 向 mytable 表中插入五行数据，
-- 每行包含 name、age、address、phone 和 email 五个属性列的值
INSERT INTO mytable (name,age,address,phone,email)
VALUES ('张三',18,'北京','110','110@gmail.com');

INSERT INTO mytable (name,age,address,phone,email)
VALUES ('李四',19,'北京','120','120@gmail.com');

INSERT INTO mytable (name,age,address,phone,email)
VALUES ('王五',28,'北京','119','119@gmail.com');

INSERT INTO mytable (name,age,address,phone,email)
VALUES ('木六',17,'北京','315','315@gmail.com');

INSERT INTO mytable (name,age,address,phone,email)
VALUES ('SmallG',18,'北京','111','111@gmail.com');

SELECT * FROM mytable;
-- 为表增加合理的约束
-- 将 email 属性列设为唯一，确保每个值都是唯一的
ALTER TABLE mytable CHANGE email email VARCHAR(30) UNIQUE ;
-- 添加一个约束来限制 name 属性不为空
ALTER TABLE mytable CHANGE name name VARCHAR(30) NOT NULL ;
-- 添加一个约束来限制 age 属性不为空
ALTER TABLE mytable CHANGE age age INT(3) not null ;

-- 使用UPDATE更改表中的数据
-- 更新表中所有行的phone更改，在phone的头部添加"+86 "
UPDATE mytable SET phone='+86 '+phone;
-- 更改id为1的email为'tom@highedu.cn'
UPDATE mytable SET email='tom@highedu.cn' WHERE id=1;

-- 将表中的一部分行删除，最后删除全部的行，然后删除表和数据库
-- 将表 mytable 中 id 为 1 的行删除
DELETE FROM mytable WHERE id = 1;
-- 将表 mytable 中的所有行删除
DELETE FROM mytable;
-- 将表 mytable 删除
DROP TABLE mytable;
-- 将数据库 mydatabase删除
DROP DATABASE mydatabase;

2 设计数据库存储商品表，并且添加4行数据

要求如下：

• 数据库名称：mall
• 商品表名称 product，包含属性：商品编号、商品名称、商品价格、商品描述、商品厂牌、库存数量、属性标签
• 设计合理的约束
• 为表插入4行数据
• 批量更新价格，整体增加100

请提交全部的SQL语句。

-- 数据库名称：mall
CREATE DATABASE mall;

-- 商品表名称 product，包含属性：
-- 商品编号、商品名称、商品价格、商品描述、商品厂牌、库存数量、属性标签
CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    price DECIMAL(7,2),
    descS VARCHAR(30),
    brand VARCHAR(30),
    num INT,
    field VARCHAR(30)
);
-- 设计合理的约束
ALTER TABLE product CHANGE name name VARCHAR(30) NOT NULL;
-- 为表插入4行数据
INSERT INTO product (name, price, descS, brand, num, field)
VALUES ('张三',12.20,'哈哈','大翔品牌',1000,'摆件');

INSERT INTO product (name, price, descS, brand, num, field)
VALUES ('李四',111.20,'哈哈','大翔品牌',1000,'摆件');

INSERT INTO product (name, price, descS, brand, num, field)
VALUES ('王五',32.20,'哈哈','大翔品牌',1000,'摆件');

INSERT INTO product (name, price, descS, brand, num, field)
VALUES ('木六',155.20,'哈哈','大翔品牌',1000,'摆件');

-- 批量更新价格，整体增加100
UPDATE product SET price=price+100;

SELECT * FROM product;

3 MySQL 表结构创建、修改、更新、删除

需求如下：

• 创建一个表来存储订单信息
• 表格包含了订单编号、顾客姓名、顾客邮箱、下单日期和总金额等属性：其中，id 是主键，使用 AUTO_INCREMENT 关键字来自动生成编号, 邮箱唯一
• 为表增加一些属性，例如订单状态和付款方式
• 为表添加合理数据
• 编写有条件更新语句，更新付款方式
• 查看表的结构和建表SQL
• 删除表

-- 创建一个表来存储订单信息
-- 表格包含了订单编号、顾客姓名、顾客邮箱、下单日期和总金额等属性：
-- 其中，id 是主键，使用 AUTO_INCREMENT 关键字来自动生成编号, 邮箱唯一
CREATE TABLE orders(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL ,
    email VARCHAR(30) UNIQUE ,
    dataTime TIMESTAMP,
    price DECIMAL
);
-- 为表增加一些属性，例如订单状态和付款方式
ALTER TABLE orders ADD status VARCHAR(30);
ALTER TABLE orders ADD payMethod VARCHAR(30);
-- 为表添加合理数据
INSERT INTO orders (name, email, dataTime, price)
VALUES ('张飒','1111@qq.com','2000-01-01 10:10:10',20.00);
-- 编写有条件更新语句，更新付款方式
ALTER TABLE orders CHANGE payMethod payMethod VARCHAR(30) NOT NULL ;
-- 查看表的结构和建表SQL
DESC orders;
SHOW CREATE TABLE orders;
-- 删除表
DROP TABLE orders;