数据库笔记

数据库 (DataBase)

存储数据的仓库，数据是有组织的进行存储

关系型数据库管理系统

MySQL (Structured Query Language)

SQL 通用语法

  1. SQL 语句可以单行或多行书写，以分号结尾；
  2. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写；
  3. 注释
    • 单行注释： -- 注释内容 或 # 注释内容(MySQL 特有)
    • 多行注释： /* 注释 */

SQL 分类

DDL -- 操作数据库

  1. 查询 

    1
    SHOW DATABASES;

  2. 创建

  • 创建数据库 
    1
    CREATE DATABASE 数据库名称;
  • 创建数据库(判断，如果不存在则创建) 
    1
    CREATE DATABASE IF NOT EXISTS 数据库名称;
  1. 删除
  • 删除数据库 
    1
    DROP DATABASE 数据库名称;
  • 删除数据库(判断，如果存在则删除) 
    1
    DROP DATABASE IF EXISTS 数据库名称;
  1. 使用数据库
  • 查看当前使用的数据库 
    1
    SELECT DATABASE();
  • 使用数据库 
    1
    USE 数据库名称;

DDL -- 操作表

  1. 创建(Create) 

    1
    2
    3
    4
    5
    6
    CREATE TABLE 表名 (
    		字段名1 数据类型,
    		字段名2 数据类型,
    		...
    		字段名n 数据类型 
    );
    注意：最后一行末尾，不能加逗号

  2. 查询(Retrieve)

  • 查询当前数据库下所有表名称 
    1
    SHOW TABLES;
  • 查询表结构 
    1
    DESC 表名称;
  1. 修改(Update)
  • 修改表名 
    1
    ALTER TABLE 表名 RENAME TO 新表名;
  • 添加一列 
    1
    ALTER TABLE 表名 ADD 列名 数据类型;
  • 修改数据类型 
    1
    ALTER TABLE 表名 MODIFY 列名 新数据类型;
  • 修改列名和数据类型 
    1
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
  • 删除列 
    1
    ALTER TABLE 表名 DROP 列名;
  1. 删除(Delete)
  • 删除表 
    1
    DROP TABLE 表名;
  • 删除表时判断表是否存在 
    1
    DROP TABLE IF EXISTS 表名;

数据类型

MySQL 支持多种类型，可以分为三类： - 数值 - 日期 - 字符串 

1
2
3
4
5
6
7
8
9
10
11
12
create table student (

	id int,
	name varchar(10),
	gender char(1),
	birthday date,
	score double(5, 2),
	email varchar(64),
	tel varchar(15),
	status tinyint

);

DML -- 操作数据

  1. 添加(insert)
  • 给指定列添加数据 
    1
    INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...);
  • 给全部列添加数据 
    1
    INSERT INTO 表名 VALUES(值1,值2,...);
  • 批量添加数据 
    1
    2
    3
    INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

    INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
  1. 修改(update)
  • 修改表数据 (不加条件，则将所有数据都修改) 
    1
    UPDATE 表名 SET 列名1=值1,列名2=值2,... [WHERE 条件];
  1. 删除(delete)
  • 删除数据 (不加条件，则将所有数据都删除!) 
    1
    DELETE FROM 表名 [WHERE 条件];

DQL

查询语法

基础查询

条件查询

排序查询

分组查询

  • 聚合函数

  • 分组查询

分页查询

约束

概念 & 分类

  1. 约束的概念
  • 约束是作用于表中列上的规则，用于限制加入表的数据
  • 约束的存在保证了数据库中数据的正确性、有效性和完整性
  1. 约束的分类 auto_increment 当不指定时自动增长

添加约束

  • 创建表时添加约束 
    1
    2
    3
    4
    5
    6
    CREATE TABLE 表名(
    	列名 数据类型 NOT NULL,
    	列名 数据类型 DEFAULT 默认值,
    	...
    	[CONSTRAINT] [约束名称] UNIQUE(列名)
    );
  • 建完表后添加约束 
    1
    2
    3
    4
    5
    ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

    ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
    #### 删除约束 
    1
    2
    3
    4
    5
    ALTER TABLE 表名 MODIFY 字段名 数据类型;

    ALTER TABLE 表名 DROP INDEX 字段名;

    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

外键

数据库设计

数据库设计简介

表关系

1. 多对一实现方式：在多的一方建立外键，指向一的一方的主键； 2. 多对多实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键； 3. 一对一实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE);

多表查询

  • 笛卡尔积： 取A, B集合所有组合情况
  • 多表查询：从多张表查询数据
    • 连接查询
      • 内连接：相当于查询A和B的交集数据
      • 外连接：左外连接、右外连接
    • 子查询

内连接

### 外连接 ### 子查询 1. 子查询概念：查询中嵌套查询，称嵌套查询为子查询； 2. 子查询根据查询结果不同，作用不同

事务

事务简介

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
DROP TABLE IF EXISTS ACCOUNT;

-- 创建账户表
CREATE TABLE ACCOUNT (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
money DOUBLE(10, 2)
);

-- 添加数据
INSERT INTO ACCOUNT(NAME,money) VALUES ('张三',1000), ('李四',1000);

SELECT * FROM ACCOUNT;

-- 转账操作
-- # 开启事务
BEGIN;

-- 1. 查询李四的余额

-- 2. 李四金额 -500
UPDATE ACCOUNT SET money = money - 500 WHERE NAME = '李四';

# 出异常

-- 3. 张三金额 +500
UPDATE ACCOUNT SET money = money + 500 WHERE NAME = '张三';

-- # 回滚事务
ROLLBACK;

-- # 提交事务
COMMIT;

事务操作

  • 开启事务
  • 回滚事务
  • 提交事务

事务四大特征

  • 原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
  • 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态
  • 隔离性(Isolation)：多个事务之间，操作的可见性
  • 持久性(Durability)：事务一旦提交或会回滚，它对数据库中的数据的改变是永久的

案例分析

  • 音乐专辑表名 ： Album | 字段名 | 数据类型 | 说明 | | ------------ | ------------- | ------------------------------- | | title | varchar(32) | 专辑名 | | style | varchar(8) | 流派(经典、流行、民谣、电子...) | | publish_time | date | 发行时间 | | publisher | varchar(16) | 出版者 | | number | tinyint | 唱片数 | | artist | varchar(16) | 艺术家 | | id | int | 编号(唯一) | | summary | varchar(1024) | 简介 |

  • 曲目表名：Song | 字段名 | 数据类型 | 说明 | | ---------- | ----------- | ---------- | | name | varchar(32) | 歌曲名 | | serial_num | tinyint | 歌曲序号 | | id | int | 编号(唯一) |

  • 评论表名： Review | 字段名 | 数据类型 | 说明 | | ----------- | ------------ | --------- | | content | varchar(256) | 评论内容 | | rating | tinyint | 评分(1-5) | | review_time | datetime | 评论时间 |

  • 用户表名： User | 字段名 | 数据类型 | 说明 | | --------- | ----------- | -------------- | | username | varchar(16) | 用户名(唯一) | | image | varchar(64) | 用户头像地址 | | signature | varchar(64) | 个人签名 | | nickname | varchar(16) | 用户昵称 | | id | int | 用户编号(主键) |

表关系

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- 创建数据库
 CREATE DATABASE IF NOT EXISTS MUSIC;

-- 使用数据库
 USE MUSIC;

DROP TABLE IF EXISTS Album; 
-- 创建专辑表
 CREATE TABLE Album (
  titile VARCHAR (32),
  style VARCHAR (8),
  publish_time DATE,
  publisher VARCHAR (16),
  numbers TINYINT,
  artist VARCHAR (16),
  id INT UNIQUE,
  summary VARCHAR (1024)
);

-- 创建歌曲表
 CREATE TABLE Song (
  NAME VARCHAR (32),
  serial_num TINYINT,
  id INT UNIQUE
);

-- 创建评论表
 CREATE TABLE Review (
  content VARCHAR (256),
  rating TINYINT,
  review_time DATETIME
);

-- 创建用户表
 CREATE TABLE USER (
  username VARCHAR(16),
  image VARCHAR(64),
  signature VARCHAR(16),
  id INT 
);

ALTER TABLE USER MODIFY id INT PRIMARY KEY;

ALTER TABLE Album MODIFY id INT PRIMARY KEY;

-- 展示所有表
SHOW TABLES;

-- 表关系
 -- 专辑 - 曲目 (1:n)
 -- 先添加一列
 ALTER TABLE Song ADD album_id INT;
 -- 一对多
 ALTER TABLE Song
  ADD CONSTRAINT album_id_song FOREIGN KEY (album_id) REFERENCES Album (id);
  
 -- 专辑- 短评 (1:n)
 ALTER TABLE Review ADD album_id INT;

ALTER TABLE Review
  ADD CONSTRAINT album_id_review FOREIGN KEY (album_id) REFERENCES Album (id);
  
-- 用户-短评 (1:n)
ALTER TABLE Review ADD user_id INT;
ALTER TABLE Review ADD CONSTRAINT user_id_review FOREIGN KEY (user_id) REFERENCES USER (id);

-- 专辑-用户 (m:n)
DROP TABLE AlbumUser;

CREATE TABLE AlbumUser (
	album_id INT,
	user_id INT,
	
	CONSTRAINT album_id_mid FOREIGN KEY (album_id) REFERENCES Album (id),
	CONSTRAINT user_id_mid FOREIGN KEY (user_id) REFERENCES USER (id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

SHOW TABLES;

DESC Album;

-- 添加数据
-- INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

INSERT INTO Album (
  titile,
  style,
  publish_time,
  publisher,
  numbers,
  artist,
  id,
  summary
)
VALUES (
    '范特西',
    '流行',
    '2001-09-14',
    'BMG',
    10,
    '周杰伦',
    1,
    '周杰伦的出现，让人们相信台湾创造本土R&B的可能性；周杰伦的走红，彻底地宣布音乐新声代的来临。作曲、填词、编曲、演唱样样俱精的周杰伦，首张同名专辑《Jay杰伦》推出后，销售势如破竹，不单有“音乐新人王”称号，他自成一格的R&B演绎方法，更被誉为陶吉吉的劲敌。 顶着台湾金曲奖“最佳唱片”、“最佳制作人”的光环，周杰伦的第二张专辑《Fantasy(范特西)》终于在万千歌迷的期待中推出。'
);

INSERT INTO Album VALUES (
	'叶惠美',
	'流行',
	'2003-07-31',
	'Sony Music',
	11,
	'周杰伦',
	2,
	'近日转会加盟新公司的周杰伦，同时推出了其年度国语专辑《叶惠美》，收录11首个人独唱歌曲。虽然新碟的惊喜不大，但仍保持着阿Jay一贯的个人风格(其实这正是乐迷最喜欢的)，而且多首歌曲亦较以往更有深度，故此并不算「原地踏步」。再者，当中某几首歌曲，更是惹人钟爱之作，相信这专辑依然会得到不少乐迷支持。'
); 

SELECT * FROM Album;

DESC Song;

INSERT INTO Song (NAME, serial_num, id)
VALUES
    ('爱在西元前', 1, 1),
    ('爸我回来了', 2, 2),
    ('简单爱', 3, 3),
    ('忍者', 4, 4),
    ('开不了口', 5, 5),
    ('上海一九四三', 6, 6),
    ('对不起', 7, 7),
    ('威廉古堡', 8, 8),
    ('双截棍', 9, 9),
    ('安静', 10, 10);
    
INSERT INTO Song (NAME, serial_num, id)
VALUES
    ('以父为名', 1, 11),
    ('儒夫', 2, 12),
    ('晴天', 3, 13),
    ('三年二班', 4, 14),
    ('东风破', 5, 15),
    ('你听得到', 6, 16),
    ('同一种调调', 7, 17),
    ('她的睫毛', 8, 18),
    ('爱情悬崖', 9, 19),
    ('梯田', 10, 20),
    ('双刀', 11, 21);
 
SELECT * FROM Song;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SHOW TABLES;

DESC USER;

INSERT INTO USER (username, image, signature, id)
VALUES
    (
        '张三',
        'https://demo/zhangsan.png',
        '人生无常，大肠包小肠。',
        '1'
    ),
    (
        '李四',
        'https://demo/lisi.png',
        '先打一拳，打的时候再把问题问遍。',
        2
    );
    
DESC review;

INSERT INTO Review (
    content,
    rating,
    review_time,
    album_id,
    user_id
)
VALUES
    (
        '首首经典 难以超越的高度 且不仅仅只对周杰伦而言',
        5,
        '2007-3-11 13:02:23',
        1,
        1
    ),
    (
        '题材，旋律，曲风，制作，歌词，商业度和流行度，全方位炸裂，从头高潮到尾。',
        5,
        '2017-5-17 10:30:15',
        1,
        2
    ),
    (
        '十来岁的记忆全部浓缩在这张专辑里；从它开始学会了怎样欣赏音乐，体验到了陶醉和沉浸的感觉',
        4,
        '2012-7-08 16:15:36',
        2,
        1
    ),
    (
        '周董最好的一张专辑，晴天听一百遍也不会腻，都是青春的回忆。',
        5,
        '2015-11-13 08:49:30',
        2,
        2
    );
    
SELECT * FROM Review;

SELECT * FROM USER;

SELECT * FROM Album;

DELETE FROM Album WHERE id = 1;

技术 > JavaWeb
#数据库 #MySQL
数据库笔记
https://nessaj7.github.io/2022/04/29/65dec125cf63.html
作者
kuhn
发布于
2022年4月29日
许可协议