【个人笔记】MySQL 数据库基础学习笔记

最新推荐文章于 2023-07-29 18:45:45 发布

idiot5liev

最新推荐文章于 2023-07-29 18:45:45 发布

阅读量871

点赞数 2

分类专栏： MySQL 个人笔记文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/idiot5lie/article/details/117793661

版权

个人笔记同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

前言

MySQL 数据库基础 0606 完成 0610

出发点是先有体系，再理解底层。0606 搭建环境，熟悉 MySQL 语法及规范，本地连接云服务器，解决 DataGrip 关键字默认 upper、console 无法中文输入等问题。紧接着 down 例子，练手过基础内容，最终同步的整理笔记完成。

除数据库以外，知识体系完整，较夯实。包括接触数据库锁概念，发现其等同进程间通信 IPC cond 与 mutex 对数据加锁，故死锁问题考虑，初步判断，底层实现逻辑相同（理解相同点、不同点）。进度较快，但计划时间有限，提醒自己务必注重学习效率。

计划——下一阶段 MySQL 进阶（包括 MySQL 架构、引擎&事务&索引、锁、锁&分库分表、集群、高可用），深入探索底层原理，以及之后 Redis、MongoDB 等择其一 Redis，理解非关系型数据库，与 MySQL 关系型数据库对比。数据库整体目标，首先具备实战能力，其次重点理解、掌握知识点：

数据库基础知识
MySQL 简介
表基本操作
SQL 约束
基本查询
函数
分组查询
子查询
组合查询
多表操作（重点）
表关联查询（重点）
视图
事务（重点）
索引（重点）
触发器
存储过程

第一章数据库基础知识

两种连接方式

mysql -u root -p

mysql -h 127.0.0.1 -P 3306 -u root -p

数据库管理系统

我们常说的“数据库”软件，确切的说应该叫：数据库管理系统。

数据库管理系统（DataBase Management System，DBMS）:指一种操作数据库、管理数据库的大型软件

作用：用于数据库的建立、使用和维护数据库

数据库

数据库（database）：就是存储数据的容器，其本质是一个文件系统，按照特定的格式将数据存储起来。用户可以对数据库中的数据进行增加、修改、删除及查询操作。

表

表具有一些特性，这些特性定义了数据在表中如何存储。比如：可以存储什么样的数据，数据如何拆分等等。

列

列（column）表中的一个字段。

行

行（row）表中的一个记录。

主键

区分两个人是不是同一个人，可以使用身份证号；区分两台汽车，可以用汽车牌号；区分两行数据，可以用主键。

主键（primary key）：一列（或多列），其值能够唯一区分表中的每个行。

SQL

SQL（发音为字母 S-Q-L 或 sequel）是结构化查询语言（Structured Query Language）的缩写。SQL 是一种专门用来与数据库通信的语言。

SQL 的特点：

SQL不是某种特定数据库供应商专有的语言。几乎所有重要的 DBMS 都支持 SQL，所以，学习此语言使你几乎能与所有数据库打交道。

SQL 简单易学。它的语句全都是由描述性很强的英语单词组成，而且这些单词的树木不多。

SQL 尽管看上去很简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL 的分类：

数据定义语言：简称 DDL（Data Definition Language ），用来定义数据库对象：数据库，表，列等。关键词：create，alter，drop 等。

数据控制语言：简称 DCL（Data Control Language），用来定义数据库的访问权限和安全级别，及创建用户。

数据操作语言：简称 DML（Data Manipulation Language），用来对数据中表的记录进行更新。关键字：insert，delete，update 等。

数据查询语言：简称 DQL（Data Query Language），用来查询数据库中表的记录。关键字：select，from，where 等。

第二章 MySQL 简介

题外话：专业数据建模的工具 PowerDesigner

MySQL 数据库历史简介

1995 年 5 月 23 日，MySQL 的第一个内部版本发行了。
2008 年 1 月，MySQL AB 公司被 Sun 公司以 10 亿美金收购，MySQL 数据库进入 Sun 时代。在 Sun 时代，Sun 公司对其进行了大量的推广、优化、Bug 修复等工作。
2008 年11月，MySQL 5.1 发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的 NDB 集群系统,，同时修复了大量的 BUG。
2009 年 4 月 20 日，Oracle 公司以 74 亿美元收购 Sun 公司，自此 MySQL 数据库进入Oracle 时代，而其第三方的存储引擎 InnoDB 早在 2005 年就被 Oracle 公司收购。
2010 年 12 月，MySQL 5.5 发布，最重要的是 InnoDB 存储引擎终于变为当前 MySQL 的默认存储引擎。MySQL 5.5 不是时隔两年后的一次简单的版本更新，而是加强了 MySQL 各个方面在企业级的特性。
2013 年 2 月，MySQL 5.6 发布。Oracle 最近宣布将于 2021 年 2 月停止 5.6 版本的更新，结束其生命周期。
2015 年12月，MySQL 5.7 发布，其性能、新特性、性能分析带来了质的改变。
2016 年 9 月，MySQL 开始了 8.0 版本，Oracle 宣称该版本速度是 5.7 的两倍，性能更好。

可视化工具

两种常用的数据库连接工具：Navicat 和 DataGrip

Navicat 是一款连接 MySQL 的经典工具，直到现在还有很多程序员都在使用。

DataGrip 是 JetBrains 公司的产品，旗下有很多著名的开发工具，比如：IntelliJ IDEA、PyCharm、CLine等。由于其人性化的提示、补全等功能，越来越多的程序员开始使用。（推荐）

数据库操作

# database tutorial
SHOW DATABASES;
USE mysql;
SELECT database();
SHOW TABLES;

# create
CREATE DATABASE mydb CHARACTER SET utf8mb4;
CREATE DATABASE mydb2;
CREATE DATABASE IF NOT EXISTS mydb2;

# show
SHOW DATABASES;
SHOW CREATE DATABASE mydb;
SHOW CREATE DATABASE mydb2;

# drop
DROP DATABASE mydb2;
DROP DATABASE IF EXISTS m;

SELECT database();
USE mydb;

# alter
ALTER DATABASE mydb2 CHARACTER SET gbk;

第三章表基本操作

MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写，以分号结尾。

SQL 中数据的常用数据类型

类型名称	说明
int	整数类型
double	小数类型
decimal（m, d）	指定整数位与小数位长度的小数类型
date	日期类型，格式为 yyyy-MM-dd，包括年月日，不包含时分秒
datetime（推荐）	日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒
timestamp	日期类型，时间戳 1970-01-01 00:00:00 - 2038-12-31 23:59:59
varchar（M）（推荐）	文本类型，M为0～65535之间的整数（可变长度）
char（M）	文本类型，M为0～65535之间的整数（不可变长度）

表操作

# table tutorial1
# create
CREATE TABLE student (
    id int,
    name varchar(5),
    age int
);
CREATE TABLE users (
    id int,
    username varchar(50),
    password varchar(50)
);
# CREATE TABLE `users2` (
#   `id` int(11) DEFAULT NULL,
#   `username` varchar(50) DEFAULT NULL,
#   `password` varchar(50) DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; # CHARSET same as database

# show
SHOW TABLES;
SHOW CREATE TABLE users;

# drop
DROP TABLE users;
   
# alter
# keywords to variable use ``
ALTER TABLE student ADD `desc` varchar(50);
ALTER TABLE student MODIFY name varchar(3);
ALTER TABLE student CHANGE `desc` description varchar(50);
ALTER TABLE student DROP description;

# rename
RENAME TABLE student TO stu;
ALTER TABLE stu CHARACTER SET utf8mb4;
SHOW CREATE TABLE stu;
# CREATE TABLE `stu` (
#   `id` int(11) DEFAULT NULL,
#   `name` varchar(3) CHARACTER SET utf8mb4 DEFAULT NULL,
#   `age` int(11) DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表中记录操作

# table tutorial2
# insert into
INSERT INTO stu(id, name, age) VALUES (1,'张三', 20);
INSERT INTO stu(id, name) VALUES (2, 'lis');
INSERT INTO stu (id, name, age) VALUES (3, '彭于晏', 30), (4, '吴彦祖', 59);

# update
UPDATE stu SET age = 40, name = '鹏鹏' WHERE id = 3;
# UPDATE stu SET age = 40, name = '鹏鹏'; # no where, all update

# delete
DELETE FROM stu WHERE id = 4;

# select
SELECT * FROM stu;

第四章 SQL 约束

主键约束

主键必须包含唯一的值，不能重复

主键列不能包含 NULL 值

每个表都应该有一个主键，并且每个表只能有一个主键

添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键

CREATE TABLE persons (
pid int PRIMARY KEY, -- 添加了主键约束
lastname varchar(255),
firstname varchar(255),
address varchar(255)
);
INSERT INTO persons VALUES(1, '星驰', '周', '香港');
INSERT INTO persons VALUES(1, '德华', '刘', '香港'); -- 设置主键后, 插入失败, 值重复
INSERT INTO persons VALUES(2, '德华', '刘', NULL);
INSERT INTO persons VALUES(NULL, '润发', '周', '香港'); -- 设置主键后, 插入失败, 值不能为 NULL

方式二：创建表时，在 constraint 约束区域，声明指定字段为主键

# 关键字 CONSTRAINT 可以省略，如果需要为主键命名，CONSTRAINT 不能省略，主键名称一般没用
# 字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔，声明两个以上字段为主键,我们称为联合主键
CONSTRAINT pk_persons PRIMARY KEY (pid) -- 添加主键约束, 单一字段
CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname) -- 添加主键约束, 多个字段, 我们称为联合主键。

方式三：创建表之后,通过修改表结构，声明指定字段为主键

ALTER TABLE persons ADD CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname); -- 添加联合主键

删除主键约束

ALTER TABLE persons DROP PRIMARY KEY;

自动增长列

向 persons 添加数据时，可以不为pid字段设置值，也可以设置成null，数据库将自动维护主键值

CREATE TABLE persons (
	pid INT PRIMARY KEY AUTO_INCREMENT,
	lastname VARCHAR(255),
	firstname VARCHAR(255),
	address VARCHAR(255)
);
# 扩展:默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，使用下列 SQL语法
ALTER TABLE persons AUTO_INCREMENT = 100;

面试题

问：针对 AUTO_INCREAMENT，删除表中的所有记录使用 DELETE FROM 表名或使用 TRUNCATE TABLE 表名，二者有什么区别？
删除过程:
DELETE：表中记录一条一条删除，AUTO_INCREMENT 计数不会重置为 1，新记录添加时在原有计数基础上 +1
TRUNCATE：直接将表删除,重新创建新表，AUTO_INCREMENT 计数重置为1

非空约束

添加非空约束

方式一：创建表时，在字段描述处，声明指定字段为非空约束

CREATE TABLE persons (
	pid INT PRIMARY KEY AUTO_INCREMENT,
	lastname VARCHAR(255) NOT NULL,
	firstname VARCHAR(255) NOT NULL,
	address VARCHAR(255)
);

方式二：修改表时

ALTER TABLE persons MODIFY firstname varchar(255) NOT NULL;

删除非空约束

ALTER TABLE persons MODIFY lastname VARCHAR(255);
ALTER TABLE persons MODIFY firstname VARCHAR(255);

唯一约束

UNIQUE 约束：指定列的值不能重复
注意：

UNIQUE 和 PRIMARY KEY 约束均为列提供了唯一性的保证，PRIMARY KEY 是自动定义
的 UNIQUE 约束
每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
UNIQUE 不限制 null 值出现的次数

添加唯一约束：三种方式参考主键约束，UNIQUE 替换 PRIMARY KEY

删除唯一约束：如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名

ALTER TABLE persons DROP INDEX uni_address; -- 有唯一约束名称，使用约束名称删除
ALTER TABLE persons DROP INDEX address; -- 没有唯一约束名称，使用字段名删除

默认约束

DEFAULT 约束：

用于指定字段默认值
当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值

添加默认约束：

CREATE TABLE persons (
   pid INT,
   lastname VARCHAR(255),
   firstname VARCHAR(255),
   address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
);
INSERT INTO persons(pid,lastname, firstname, address) VALUES(1, '富成', '郭', '北京');
INSERT INTO persons(pid,lastname, firstname) VALUES(2, '龙', '成'); -- 使用address的默认值
INSERT INTO persons(pid,lastname, firstname, address) VALUES(3, '润发', '周', NULL);

删除默认约束：

ALTER TABLE persons MODIFY address VARCHAR(255);

第五章基本查询

SQL 数据准备——SQL 数据脚本下载

语法

SELECT [distinct]
* | 列名, 列名
FROM 表
WHERE 条件;

简单查询 -> 条数限制查询 -> 排序

SELECT * FROM products;
SELECT prod_id, vend_id, prod_name, prod_price, prod_desc FROM products;
SELECT prod_name FROM products;
SELECT prod_id, prod_name, prod_price, vend_id FROM products;
SELECT DISTINCT vend_id, prod_id FROM products;
SELECT vend_id FROM products;
SELECT prod_name FROM products LIMIT 3;
SELECT * FROM products  LIMIT 1, 5; # first item index is 0, and the latter parameter is amount
SELECT * FROM products ORDER BY prod_price DESC;
SELECT * FROM products ORDER BY convert(prod_name USING gbk) DESC;
SELECT prod_name, vend_id, prod_price FROM products ORDER BY vend_id ASC, prod_price DESC;
SELECT prod_name, vend_id, prod_price FROM products ORDER BY vend_id DESC, prod_price ASC;
SELECT prod_name, vend_id, prod_price FROM products ORDER BY prod_price DESC LIMIT 3;

条件查询

SELECT prod_name, prod_price FROM products WHERE prod_price <> 55.00;
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ORDER BY prod_price;
SELECT * FROM products WHERE prod_desc IS NULL;
SELECT * FROM products WHERE prod_desc IS  NOT NULL; # 区分与 `NULL` 、空字串的区别
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id = 1001 OR vend_id = 1002;
SELECT prod_name, vend_id, prod_price FROM products WHERE (vend_id = 1001 OR vend_id = 1002) AND prod_price > 10;
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id IN(1001, 1002); # in can include subSELECT, so better than OR

模糊查询

# LIKE 语句中，% 代表零个或多个任意字符，_ 代表一个字符
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id LIKE '1%';
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id LIKE '1001%';
SELECT prod_name, vend_id, prod_price FROM products WHERE prod_name LIKE 'i%';
SELECT prod_name FROM products WHERE BINARY prod_name LIKE 'i%'; # BINARY distinguish by upper lower
SELECT prod_name FROM products WHERE prod_name LIKE '__去%'; # 尽量不要把 % 放在最前面

正则表达式

正则表达式（regular expression）描述了一种字符串匹配的模式（pattern），可以用来检查一个串是否含有某种子串、将匹配的子串替换或者从某个串中取出符合某个条件的子串等。
比如判断用户输入的邮箱格式是否合法，可以使用正则表达式。判断用户密码是否包含特殊字符，可以使用正则表达式。想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。
SELECT prod_name FROM products WHERE prod_name REGEXP '水|40';
SELECT prod_name FROM products WHERE prod_name REGEXP '[a-z]';

第六章函数

快捷键

快速复制：ctrl + D

剪切：ctrl + X

移动：ctrl + shift（长按）

字符拼接

SELECT concat(prod_name, ' (', prod_price, ')') FROM products;
SELECT concat(prod_name, ' (', prod_price, ')') AS prod_name_price FROM products;

加减乘除、别名

SELECT prod_id, item_price, quantity, quantity * item_price AS total_price FROM orderitems WHERE order_num = 20006;

文本处理

SELECT upper(prod_name) FROM products;
SELECT lower(prod_name) FROM products;
SELECT left(prod_name, 3) FROM products;
SELECT right(prod_name, 3) FROM products;
SELECT substr(prod_name, 2) AS substr FROM products;
SELECT substr(prod_name, 2, 4) FROM products;
SELECT concat(substring_index('www.google.baidu.com', '.', 2), '.com') AS strind;
SELECT prod_name, length(prod_name) AS length FROM products;
# ltrim(string)：去掉字符串左边的空格
# rtrim(string)：去掉字符串右边的空格
# trim(string)：去掉字符串两边的空格

日期函数

首先需要注意的是 MySQL 使用的日期格式，日期格式推荐设置：yyyy-mm-dd。这是首选的日期格式，因为它排除了多义性。

SELECT curdate();
SELECT curtime();
SELECT now();
SELECT date(order_date) FROM orders;
SELECT datediff('2021-01-02', '2021-01-01') AS datediff;
SELECT * FROM orders;
SELECT date_format(order_date, '%Y/%m/%d') AS date FROM orders;
SELECT dayofweek(now()); # the first day is Sunday
SELECT dayname(now());
SELECT adddate(now(), 5); # same as function date_add()

数学函数

SELECT prod_price, round(prod_price) AS round FROM products;
SELECT prod_price, truncate(prod_price, 1) AS truncate FROM products; # decimal, int
# ceil()：向上取整
# floor()：向下取整

聚合函数

我们经常需要汇总数据而不用把它们实际检索出来，为此 MySQL 提供了专门的函数。聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数。

SELECT max(prod_price) FROM products;
SELECT min(prod_price) FROM products;
SELECT avg(prod_price) FROM products;
SELECT count(*) FROM products;
SELECT count(DISTINCT vend_id) FROM products;
SELECT sum(quantity) FROM orderitems;
SELECT sum(quantity * item_price) FROM orderitems where order_num = 20005;
SELECT count(*), min(prod_price), max(prod_price), avg(prod_price) from products;

其他函数

md5 加密函数
SELECT md5('123');
查询当前用户
SELECT user();
查询当前数据库的版本号
SELECT version();

第七章分组查询

快捷键

SQL 格式化：ctrl + a|t + L （语句过长格式化）

分组查询

分组查询是指使用 GROUP BY 字句对查询信息进行分组

分组操作中的 HAVING 子语句，是用于在分组后对数据进行过滤的，作用类似于 WHERE 条件，区别于 WHERE

HAVAING 是在分组操作执行后，对分组后的数据进行过滤，WHERE 是在分组操作执行前，对分组的数据，只能使用表原始列进行条件过滤

HAVING 后面可以使用聚合函数，WHERE 后面不可以使用聚合函数

故 SQL 语句，既有 WHERE 又有 GROUP BY \ HAVING 时，先执行 WHERE，再执行 GROUP BY，最后执行 HAVING，有ORDER BY 对最终查询结果进行排序

SELECT vend_id, count(*) FROM products WHERE vend_id = 1003;
SELECT vend_id, count(*) FROM products GROUP BY vend_id;
SELECT vend_id, count(*) FROM products GROUP BY vend_id HAVING count(*) > 2;
SELECT vend_id, count(*), max(prod_price) FROM products GROUP BY vend_id;
SELECT vend_id, count(*), max(prod_price) FROM products WHERE vend_id > 1001 GROUP BY vend_id;
SELECT vend_id, count(*), max(prod_price) FROM products GROUP BY vend_id HAVING vend_id > 1001;
SELECT vend_id, count(*), max(prod_price) FROM products WHERE prod_price > 10 GROUP BY vend_id HAVING vend_id > 1001;
SELECT vend_id, count(*), max(prod_price) FROM products WHERE prod_price > 10 AND vend_id > 1001 GROUP BY vend_id;
SELECT vend_id, count(*), max(prod_price) FROM products WHERE prod_price > 10 AND vend_id > 1001 GROUP BY vend_id HAVING max(prod_price) > 10 ORDER BY DESC;
# 作业题：从订单明细表中，查询每笔订单的订单编号和订单总额，并且得到订单总额大于 100 的数据，将最终结果按照订单总额从大到小排序，取前两条数据
SELECT order_num, sum(quantity * item_price) AS sum_price FROM orderitems GROUP BY order_num HAVING sum_price > 100 ORDER BY sum_price DESC LIMIT 2;

注意事项：

GROUP BY 常和聚合函数一起使用，来统计数据。
GROUP BY 后面可以跟多个列。例如:：GROUP BY 年级列，班级列。就会先按年级分组，再按班级进行分组。
分组后所有的数据统计都将在分组上进行汇总，也就是说不能从个别的列取数据。
在分组查询中，SELECT 后的列名，要么是函数，要么是 GROUP BY 后的分组列。
如果分组中有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。
我们可以发现用 GROUP BY 分组的数据确实是以分组顺序输出的。但这样的顺序并不是一定的，因为它并不是 SQL 规范所要求的。所以，需要以分组顺序输出时，ORDER BY 必须要写。
ORDER BY 是对最终的查询结果进行排序。

第八章子查询

到现在为止，我们所看到的 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。
SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询。

# 练习题：假设现在需要列出订购物品 id 为 60005 的所有客户 id、客户名称，应该怎样检索
SELECT order_num FROM orderitems WHERE prod_id = 60005;
SELECT cust_id FROM orders WHERE order_num IN (20005, 20005, 20007, 20009);
SELECT cust_name FROM customers WHERE cust_id IN (10001, 10004, 10001);
SELECT cust_name FROM customers WHERE cust_id IN (
    SELECT cust_id FROM orders WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id = 60005
    )
);

# 作业题 1：使用子查询，返回购买价格为 10 元或以上产品的顾客 id、顾客名称
SELECT cust_id, cust_name FROM customers WHERE cust_id IN (
    SELECT cust_id FROM orders WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE item_price >= 10
    )
);

# 作业题 2：假设现在你需要查询购买 id 为 60005 的产品的所有下单日期以及下单的顾客 id，SQL 该如何写
SELECT cust_id, order_date FROM orders WHERE order_num IN (
    SELECT order_num FROM orderitems WHERE prod_id = 60005
) ORDER BY order_date;

# 作业题 3：查询购买 id 为 60005 的产品的所有顾客的邮件，该如何写 SQL
SELECT cust_email FROM customers WHERE cust_id IN (
    SELECT cust_id FROM orders WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id = 60005
    )
);

注意事项：

在 WHERE 子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。
对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
提示：子查询一般与 IN 操作符结合使用，但也可以用于等于、不等于等。

第九章组合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。默认：多个 SELECT 语句会删除重复的数据。

CREATE TABLE customers2 SELECT * FROM customers; # 复制表结构 + 不含约束
CREATE TABLE cus LIKE customers; # 复制表结构 + 含约束
SELECT * FROM customers2;
UPDATE customers2 SET cust_name = '杨过' WHERE cust_id = 10001;
UPDATE customers2 SET cust_name = '小龙女' WHERE cust_id = 10002;
UPDATE customers2 SET cust_name = '金庸' WHERE cust_id = 10003;
SELECT cust_name FROM customers;
SELECT cust_name FROM customers2;
SELECT cust_name FROM customers
UNION DISTINCT # UNION 默认 DISTINCT, 故多此一举
SELECT cust_name FROM customers2;
SELECT cust_name FROM customers
UNION ALL
SELECT cust_name FROM customers2;

注意事项：

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合 4 条 SELECT 语句，将要使用 3 个 UNION 关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（各个列不需要以相同的次序列出，但出于业务需求，通常会以相同的顺序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是数据库可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

第十章多表操作

现在我们有两张表“顾客表”和“订单表”，为了表明订单属于哪个顾客，通常情况下，我们将在订单表上添加一列，用于存放 cust_id 的信息，此列称为——外键。

外键（FOREIGN KEY）

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

主表（父表）：对于两个具有关联关系的表而言，相关联字段中的主键所在的那个表即是主表

从表（子表）：对于两个具有关联关系的表而言，相关联字段中的外键所在的那个表即是从表

外键特点

从表外键的值是对主表主键的引用。

从表外键类型，必须与主表主键类型一致。

外键的作用：保证数据引用的完整性、一致性。

要考虑向从表添加数据时，外键字段必须是主表主键的引用，否则添加失败。

要考虑从主表中删除数据时，主键并未被从表引用，否则删除失败。

总结以上两点：添加先主表，删除先从表。

# 简单示例
ALTER TABLE orders ADD CONSTRAINT orders_customers_fk FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
INSERT INTO customers (cust_id, cust_name) VALUES (666, '王老五');
INSERT INTO orders (order_date, cust_id) VALUES (now(), 666);
INSERT INTO orders (order_date, cust_id) VALUES (now(), 111); # 失败
DELETE FROM customers WHERE cust_id = 666; # 失败
DELETE FROM orders WHERE cust_id = 666;
DELETE FROM customers WHERE cust_id = 666; # 成功
ALTER TABLE orders DROP FOREIGN KEY orders_customers_fk;

# 多对多示例：用户和角色
# 用户表
CREATE TABLE `user` (
 uid varchar(32) PRIMARY KEY,
 uname varchar(32),
 `password` varchar(32)
);
# 角色表
CREATE TABLE role (
 rid varchar(32) PRIMARY KEY,
 rname varchar(32)
);
# 中间表
CREATE TABLE user_role (
 user_id varchar(32),
 role_id varchar(32),
 CONSTRAINT user_role_pk PRIMARY KEY (user_id,role_id),
 CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES `user`(uid),
 CONSTRAINT role_id_fk FOREIGN KEY (role_id) REFERENCES role(rid)
);

注意事项：

现在这种创建外键的方式已经不提倡，甚至被禁止了，因为在维护数据时，限制条件太多，效率较低。关联关系通过 SQL 语句来实现。但是，理解表关系可以通过外键的操作，并且必要。

第十一章表关联查询

表别名：当表的名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，以替代表原来的名称。

在为表取别名时，要保证不能与数据库中的其他表的名称冲突。
对单表做简单的别名查询通常是无意义的。一般是对一个表要当作多个表来操作，或者是对多个表进行操作时，才设置表别名。
当为表取别名后，列名前面最好都加上表的别名，做以区分。

内连接

# 练习题 1：使用 INNER JOIN 编写 SQL，查询每个顾客的名称、所有订单号
# 方式一：WHERE
SELECT cust_name, order_num
FROM customers v,
     orders o
WHERE v.cust_id = o.cust_id
ORDER BY cust_name, order_num;
# 方式二：JOIN ON 连接
SELECT cust_name, order_num
FROM customers c
         INNER JOIN orders o ON c.cust_id = o.cust_id
ORDER BY cust_name, order_num;

多表内连接

SQL 对一条 SELECT 语句中可以连接的表的数目没有限制。创建联结的基本规则也相同。

# 作业题——多表内联：假设现在要查询订单编号为 20005 的产品名称、产品价格、产品数量、供应商名称，该如何查询
# 个人思考：子查询也可以实现这个功能，所以思考区分 IN 子查询（子查询更适合用来显示单表的各字段，如自连接可用子查询实现）
# 方式一：WHERE
SELECT prod_name, prod_price, quantity, vend_name
FROM products p,
     orderitems o,
     vendors v
WHERE p.vend_id = v.vend_id
  AND p.prod_id = o.prod_id
  AND order_num = 20005;
# 方式二：JOIN ON 连接
SELECT prod_name, prod_price, quantity, vend_name
FROM products p
         JOIN orderitems o ON p.prod_id = o.prod_id
         JOIN vendors v ON p.vend_id = v.vend_id
WHERE order_num = 20005;

注意事项：

阿里手册禁止三表以上连接

笛卡尔积

在数据库表的定义中不存在能指示 MySQL 如何对表进行联结的东西。我们必须自己通过SQL 做这件事情。

在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有 WHERE 子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT v.vend_name, p.prod_name, p.prod_price
FROM products p,
     vendors v
ORDER BY vend_name, prod_name;

从上面的输出中可以看到，相应的笛卡儿积不是我们所想要的。这里返回的数据用每个供应商匹配了每个产品，它包括了供应商不正确的产品。实际上有的供应商根本就没有产品。所以两表联查时，一定要注意 WHERE 条件。

外连接

左外连接

# 查询所有客户，的订单情况，包括没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers
         LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
# OUTER 可以省略

# 练习题 2：修改练习题 1 的 SQL 语句，列出所有顾客，即使他们没有下过订单
SELECT cust_name, order_num
FROM customers c
         LEFT JOIN orders o ON c.cust_id = o.cust_id
ORDER BY order_num DESC;

# 练习题 3：编写 SQL 语句，列出供应商 id（vend_id）及其可供产品的数量，包括没有产品的供应商
# 提示：使用外连接和分组
SELECT vendors.vend_id, COUNT(prod_id)
FROM vendors
         LEFT OUTER JOIN products p ON vendors.vend_id = p.vend_id
GROUP BY vendors.vend_id;

右外连接

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。

上面的例子使用 LEFT OUTER JOIN 从 FROM 子句的左边表（customers 表）中选择所有行。为了从右边的表中选择所有行，应该使用 RIGHT OUTER JOIN，如下例所示：

SELECT customers.cust_id, orders.order_num
FROM customers
         RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

自连接

使用表别名查询，可以使 SQL 变得简洁而更易书写和阅读，尤其在 SQL 比较复杂的情况下。除了使用别名来简化 SQL 外，有些时候例如一个表做自身关联时，必须要使用别名来当作两个表进行关联操作。

# 假设你发现商品 id 为 60001 的商品存在质量缺陷，现在需要查找，60001 供应商所提供的所有商品的名称和商品 id。请问如何编写 SQL 语句？
# 方式一：子查询
SELECT vend_id
FROM products
WHERE prod_id = 60001;
SELECT prod_id, prod_name
FROM products
WHERE vend_id = 1001;

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 60001);

# 方式二：自连接
SELECT p1.prod_id, p1.prod_name, p1.vend_id
FROM products p1,
     products p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 60001;

第十二章视图

视图概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条 SELECT 语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条 SQL 查询语句上。

视图相对于普通的表的优势主要包括以下几项：

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

注意事项

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY，那么该视图中的ORDER BY 将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的 SELECT 语句。

视图操作

在理解什么是视图（以及管理它们的规则及约束）后，我们来看一下视图的创建。

视图用 CREATE VIEW 语句来创建。
使用 SHOW CREATE VIEW viewname；来查看创建视图的语句。
用 DROP 删除视图，其语法为 DROP VIEW viewname。
更新视图时，可以先用 DROP 再用 CREATE，也可以直接用 CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第 1 条更新语句会创建一个视图；如果要更新的视图存在，则第 2 条更新语句会替换原有视图。

# 查询订单编号为 20005 的：产品名称，供应商名称，产品价格，购买数量
SELECT order_num, prod_name, vend_name, prod_price, quantity
FROM orderitems o
         JOIN products p ON o.prod_id = p.prod_id
         JOIN vendors v ON p.vend_id = v.vend_id
WHERE o.order_num = 20005;

# 创建视图，包含所有订单的产品名称，供应商名称，产品价格，购买数量
CREATE VIEW order_detail AS
SELECT order_num, prod_name, vend_name, prod_price, quantity
FROM orderitems o
         JOIN products p ON o.prod_id = p.prod_id
         JOIN vendors v ON p.vend_id = v.vend_id;

# 查看视图的所有数据
SELECT *
FROM order_detail;

# 从视图中过滤数据
SELECT *
FROM order_detail
WHERE order_num = 20005;

# 删除视图
DROP VIEW order_detail;

第十三章事务

事务：指的是逻辑上的⼀组操作，组成这组操作的各个单元要么全都成功，要么全都失败。

事务作用：保证在⼀个事务中多次 SQL 操作要么全都成功，要么全都失败。

例子

比如：银行转账，小明有 50 元，小红有 10 元，小明向小红转账 10 元，在数据库操作中相当于执行了两条 SQL 语句

# 创建账号表
CREATE TABLE account (
 id int PRIMARY KEY AUTO_INCREMENT  COMMENT '主键',
 name varchar(20) COMMENT '姓名',
 money double COMMENT '余额'
);
# 初始化数据
INSERT INTO account VALUES (1, '小明', 50);
INSERT INTO account VALUES (2, '小红', 10);

# 终端（保证在一个会话）切换数据库并 BEGIN 后运行以下两行，最后打入 ROLLBACK 或者 COMMIT
UPDATE account SET money = money - 10 WHERE id = 1; # 小明账户减 10 元
UPDATE account SET money = money + 10 WHERE id = 2; # 小红账户加 10 元

# 题外小知识
# MySQL 有一个系统变量 autocommit，用来自动提交事务。
SHOW VARIABLES LIKE 'autocommit';
# 默认是开启的，也就意味着在默认情况下，如果不显示地执行  START TRANSACTION 或者 BEGIN 开启一个事务，那么每条 SQL 语句都算是一个独立的事务，这种特性被称作事务的自动提交；
# 如果想关闭这种自动提交的功能，可以使用两种办法
# 1) 显式使用START TRANSACTION 或者 BEGIN 开启一个事务
# 这样在本次事务中提交或者回滚前会暂时关闭自动提交
# 2) 把系统变量 autocommit 设置为 OFF
SET AUTOCOMMIT = off;

事务四大特性（ACID）

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A 和 B 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 5000，这就是事务的一致性。
隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

一个事务的执行不能被其他事务干扰。一个事务内部的操作及使用的数据，对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

关于事务的隔离性数据库提供了多种隔离级别。
持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

隐式提交

当我们使用 START TRANSACTION 或者 BEGIN 语句时开启了一个事务。或者把系统变量的值设置为 off 时，事物就不会进行自动提交。

如果我们输入了某些语句，且这些语句会导致之前的事物，悄悄的提交（就像输入了 COMMIT 命令一样），那么因为某种特殊的语句而导致，事务提交的情况称为隐式提交。

会导致隐式提交的语句有：

数据库定义语言 DDL：CREATE、ALTER、DROP
事务控制或关于锁定的语句：前一个事务未提交，又开启了一个新的事务
加载数据的语句：LOAD DATA
关于 MySQL 复制的一些语句：SLAVE

保存点

如果你已经开启了一个事物，并且输入了很多语句，这是忽然发现前面已经执行完的某个语句。参数写错了，只好使用 ROLLBACK 语句，让数据库状态恢复到事务执行之前的样子，然后一切从头再来。

这种感觉很不爽，因此就有了保存点的概念。

# 定义保存点
SAVEPOINT  保存点名称;
# 回滚到某个保存点，如果 ROLLBACK 后面不跟随保存点名称，则直接回滚到事务之前的状态
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称 ; # 通常 ROLLBACK TO savepoint_name;
# 删除保存点
RELEASE SAVEPOINT 保存点名称;

第十四章索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL 必须从第 1 条记录开始读完整个表，直到找出相关的行。

表越大，查询数据所花费的时间越多，如果表中查询的列有一个索引，MySQL 能快速到达。某个位置去搜寻数据文件，而不必查看所有的数据，这节课我们来介绍与索引相关的内容。

索引是对数据库中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

索引是一个单独的存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中的某个值。所有 MySQL 列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此每一种存储引擎的索引，都不一定完全相同。

索引分类

普通索引和唯一索引

普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合，必须唯一。主键索引是一种特殊的索引，唯一，不允许有空值。

单列索引和组合索引

单列索引及一个索引只包含单个列，一个表可以有多个单列索引。

组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀原则。

创建索引

创建表时创建

# 普通索引
CREATE TABLE book (
    id int AUTO_INCREMENT PRIMARY KEY ,
    bookname varchar(255) NOT NULL ,
    INDEX (bookname)
);
# 唯一索引
CREATE TABLE book2 (
    id int AUTO_INCREMENT PRIMARY KEY ,
    bookname varchar(255) NOT NULL ,
   UNIQUE INDEX (bookname)
); 
# 给索引起名字
CREATE TABLE book3(
    id int AUTO_INCREMENT PRIMARY KEY ,
    bookname varchar(255) NOT NULL ,
   UNIQUE INDEX  name_idx (bookname)
);

注意：index 和key是等价的

单独创建索引

普通索引

CREATE INDEX index_name
    ON table_name (column_name);

唯一索引

CREATE UNIQUE INDEX index_name
   ON table_name (column_name);

组合索引

CREATE INDEX index_name
    ON table_name (column_name1, column_name2);

修改表结构创建

ALTER TABLE table_name
   ADD INDEX name_idx (column_name);

删除索引

ALTER TABLE table_name
    DROP INDEX index_name;
DROP INDEX index_name ON table_name;

第十五章触发器

MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 的一段程序。触发器是由事件来触发某个操作，这些事件包括 INSERT，UPDATE 和 DELETE 语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发器是个特殊的存储过程，不同的是，执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被 MySQL 自动调用。

创建只有一个执行语句的触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tab_name FOR EACH ROW trigger_stmt

# trigger_name：触发器名称，用户自行指定；  
# trigger_time ：触发时机，可以指定为 before 或 after；  
# trigger_event：触发事件，包括 INSERT，UPDATE 和 DELETE；  
# tab_name：要建立触发器的表名；  
# trigger_stmt：触发器执行语句。

 ```SQL
 CREATE TABLE book2 (
     id int AUTO_INCREMENT PRIMARY KEY ,
     bookname varchar(255) NOT NULL
 );
 ALTER TABLE book  ADD INDEX name_idx (bookname);
 # 创建触发器，在book表中插入一条数据时自动在book2中插入一条
 CREATE TRIGGER book_insert_select AFTER INSERT ON book FOR EACH ROW INSERT INTO book2(bookname) SELECT bookname FROM book;
 INSERT INTO book(bookname)VALUES ('《平凡的世界2》');
 # 查看触发器
 SHOW TRIGGERS ;
 # 也可以在triggers表中查看触发器
 SELECT * FROM information_schema.triggers;
 # 删除触发器
 DROP TRIGGER book_insert_select;

创建有多个执行语句的触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tab_name FOR EACH ROW 
BEGIN    
语句执行列表
END

# trigger_name：触发器名称，用户自行指定；  
# trigger_time ：触发时机，可以指定为 before 或 after；  
# trigger_event：触发事件，包括 INSERT，UPDATE 和 DELETE；  
# tab_name：要建立触发器的表名；  
# 这个可以在 BEGIN，END 之间包含多条语句。

CREATE TRIGGER book_insert_select AFTER INSERT ON book FOR EACH ROW
BEGIN
INSERT INTO book2(bookname) SELECT bookname FROM book;
INSERT INTO book3(bookname) SELECT bookname FROM book;
END

第十六章存储过程

简单的说存储过程就是一条或者多条语句的集合，可视为批文件，但是其作用不仅限于批处理。

MySQL中，创建存储过程的基本形式如下：

CREATE PROCEDURE  存储过程名 (参数列表)
BEGIN    
  SQL 语句代码块
END

其中参数列表的形式如下：

[IN|OUT|INOUT] param_name type

其中 in 表示输入参数，out 表示输出参数，inout 表示既可以输入也可以输出；param_name 表示参数名称；type 表示参数的类型，该类型可以是 MYSQL 数据库中的任意类型。

# 创建存储过程
CREATE PROCEDURE my_test()
BEGIN
    SELECT * FROM book;
END;
# 调用存储过程
CALL my_test();
# 删除存储过程
DROP PROCEDURE my_test;

# 创建存储过程
CREATE PROCEDURE my_test()
BEGIN
# DECLARE 定义变量 i
    DECLARE i int DEFAULT 0;
    WHILE i < 10
        DO
            INSERT INTO book (bookname) VALUES (CONCAT('悲惨的世界', i));
            SET i = i + 1;
        END WHILE;
END

注意事项：

阿里手册禁止使用存储过程

小结

学习笔记，定期回顾，有问题留言。

idiot5liev

关注

2
点赞
踩
15

收藏

觉得还不错? 一键收藏
打赏
0
评论
【个人笔记】MySQL 数据库基础学习笔记

TOC前言MySQL 数据库基础 0606 完成 0610当前除数据库以外，知识体系完整，较夯实。比方说包括，初次接触数据库锁概念，发现其等同进程间通信 IPC cond 与 mutex 对数据加锁，故死锁问题同需要考虑，所以初步判断，底层实现逻辑相同（待确认，接着理解相同点、不同点）。进度较快，但计划时间有限，提醒自己务必注重学习效率。0606 搭建环境，熟悉 MySQL 语法及规范，本地连接云服务器，解决 DataGrip 关键字默认 upper、console 无法中文输入等问题。紧接着 d
复制链接

扫一扫