Mysql学习笔记（一）学完就出师系列哈哈哈_学习mysql如何算出师-CSDN博客

本文链接：https://blog.csdn.net/qq_41721660/article/details/124273718

本篇笔记是参照书籍《SQL基础教程》撰写的，MICK著，孙淼、罗勇译

文章目录

第一章数据库和SQL
- 1.1 DBMS
- 1.2 SQL
第二章查询基础
第三章聚合与排序
第四章数据更新

基本注意事项：

;是SQL的结束符，换行不会影响语句结果，但是代码中间有空行（无任何字符的行）会造成执行错误。
数据库的名称只能使用小写字母
关键字不区分大小写，一般采用大写
字符串和日期常数需要使用单引号（'）括起来，数字常数无需加注单引号（直接书写数字即可）。
命名规则：我们只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称，名称必须以半角英文字母开头
表和列名的唯一性：同一个数据库中不能创建两个相同名称的表，在同一个表中也不能创建两个名称相同的列

第一章数据库和SQL

1.1 DBMS

概念：用来管理数据库的计算机系统称为数据库管理系统（DBMS）：(Database Management System。DBMS 可以实现多个用户同时安全简单地操作大量数据
数据库管理系统的种类：
(1) 层次数据库（HDB）：将数据用层次结构（树形结构）的方式表现出来
(2) 关系数据库(RDB)：采用由行和列组成的二维表来管理数据，且使用专门的SQL对数据进行操作
(3) 面向对象数据库(OODB:Object Oriented DataBase)：将数据以及对数据的操作集合起来以对象为单位进行管理
(4) XML数据库：对XML数据类型进行处理
(5) 键值存储系统(KVS)：单纯用来保存查询所使用的的主键和值的组合的数据库
数据库结构
RDBMS通常使用客户端/服务器(C/S)这样的系统结构

1.2 SQL

表的结构：RDBMS以二维表为主，操作的是二维表，SQL生成的也是二维表形式
- 列称为字段，行称为记录，一个单元格只能输入一个数据。
- 关系数据库以行为单位进行数据的读取
SQL摘要
(1) SQL语句：包括关键字、表明、列明等组合而成的一条语句
分类：

DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。 DDL 包含以下几种指令。
* CREATE：创建数据库和表等对象
* DROP：删除数据库和表等对象
* ALTER：修改数据库和表等对象的结构
DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。 DML 包含以下几种指令。
* SELECT：查询表中的数据
* INSERT：向表中插入新数据
* UPDATE：更新表中的数据
* DELETE：删除表中的数据
DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。 DCL 包含以下几种指令。
* COMMIT：确认对数据库中的数据进行的变更
* ROLLBACK：取消对数据库中的数据进行的变更
* GRANT：赋予用户操作权限
* REVOKE：取消用户的操作权限
表的创建
(1) 创建类
CREATE DATABASE 数据库：创建数据库
cerate table 表名：创建表

#括号里面的内容以空格隔开
CREATE TABLE <表名>
（ <列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
.
.
.
<该表的约束1>， <该表的约束2>，……）；

(2) 数据类型
integer：整数型
char(n)：定长字符串，n表示字符串长度，没有达到指定长度自动用空格补齐
varchar(n) ：可变长字符串，n为字符串最大长度，没有达到指定长度的不用补齐
date：日期型
(3) 约束设置
NOT NULL约束：该列不能为空
primary key 约束：该列为主键，主键列不能有重复值，因为需要通过主键字段来提取唯一的行

表的删除和更新
- 删除表：DROP TABLE <表名>；删除表是不可恢复的，一定要慎用该命令
- 更新表：alter table 表名
  - 添加列：ALTER TABLE <表名> ADD COLUMN <列的定义>；
  - 删除列：ALTER TABLE <表名> DROP COLUMN <列的定义>；
表中插入数据
基础语法：INSERT INTO 表名 VALUES (‘0001’, ‘T恤衫’, ‘衣服’,1000, 500, ‘2009-09-20’);
注意：插入语句的前后需要指定关键字，前面的关键字在不同的SQL软件中有所不同，后面的关键字均为commit
- postgre中：前面的语句为BEGIN TRANSACTION;
- mysql中：前面的语句为START TRANSACTION;

BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服',1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品',500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服',4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具',3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具',6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具',500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具',880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品',100, NULL,'2009-11-11');
COMMIT;

变更表名
- PostgreSQL:ALTER TABLE 原来的表名 RENAME TO 新表名;
- Mysql：RENAME TABLE 原来的表名 to 新表名;

第二章查询基础

2.1 select语句基础

查询某几列：SELECT <列名>，……FROM <表名>；多个列名之间用逗号隔开，输出结果列顺序和查询顺序一致
查询所有列：SELECT * FROM <表名>;
为列设置别名：SELECT <列名> as 新列名，……FROM <表名>；
查询常数，即将常数与表中的列结合起来，常数单独作为一列
删除重复行：SELECT DISTINCT 列名 FROM 表名; 也就是将重复的行进行合并，其中如果该列含有NULL值，结果会单独显示一个空值。
注意：distinct只能显示在第一个列名前面，否则会报错
选择满足条件的行：SELECT <列名>, ……FROM <表名> WHERE <条件表达式>;WHERE 子句必须紧跟在 FROM 子句之后，书写顺序发生改变的话会造成执行错误

2.2 算术运算符

运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，并返回结果的符号。可以在选择的列名中直接进行运算。需要注意的是：所有包含 NULL 的计算，结果肯定是 NULL

SELECT product_name, sale_price,
sale_price * 2 AS "sale_price_x2"
FROM Product;

比较运算符中，需要注意的是不等于的书写WHERE sale_price <> 500;，其次在书写大于等于或者小于等于时，一定要让不等号在左，等号在右。

日期也可以使用不等号，表示在日期前和日期后

字符串的比较运算：典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。该规则最重要的一点就是，以相同字符开头的单词比不同字符开头的单词更相近
选取NULL的数据：不能使用<>或者=NULL，只能使用特定的关键字IS NULL，选择非空数据可以使用IS NOT NULL

SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;

2.3 逻辑运算符

NOT运算符用来否定某一条件，语法格式为WHERE NOT <条件语句>;
AND 运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。
OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当于“或者”
运算符均用在WHERE子句中，且AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。

SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20');

第三章聚合与排序

3.1 对表进行聚合查询

概念：用于汇总的函数称为聚合函数或者聚集函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行

count函数——计算满足条件的行数，COUNT函数的结果根据参数的不同而不同。 COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

SELECT COUNT(*), COUNT(col_1)
FROM NullTbl;

SUM函数——求满足条件的列的和
关于NULL值：聚合函数会将NULL排除在外。但COUNT(*)例外并不会排除NULL。
AVG函数
MAX()/MIN()，满足条件的列的最大或最小值函数。SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。
count(distinct )：想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。在聚合函数的参数中使用DISTINCT，可以删除重复数据

只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。不能出现where count(*)>2的情况。

SELECT COUNT(DISTINCT product_type)
FROM Product;
/* 计算不重复的行数*/

3.2 对表进行分组

group by+类别列。在 GROUP BY 子句中指定的列称为聚合键或者分组列。聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。例如在count函数中，就会统计空值的行数
- 使用GROUP BY子句时， SELECT子句中不能出现聚合键之外的列名。
- SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是，在 GROUP BY 子句中是不能使用别名的
- GROUP BY子句结果的显示是无序的。
where...group by ...先执行where条件语句，然后在该条件下进行分组
group by和distinct都可以删除重复数据，且会将NULL值作为不确定列显示出来。但是具体用法为：在“想要删除选择结果中的重复记录”时使用 DISTINCT，在“想要计算汇总结果”时使用 GROUP BY。

SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;

3.3 为聚合结果指定条件

HAVING子句。HAVING子句要写在GROUP BY子句之后。WHERE子句用来指定数据行的条件， HAVING子句用来指定分组的条件
写在GROUP BY子句之后。得出的结果再进行条件的筛选。
聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。因为where是对行进行条件筛选，having是对分组进行条件筛选

/*聚合键写在HAVING中*/
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
/*聚合键写在WHERE中*/
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;

3.4 对查询结果进行排序

order by 子句
不论何种情况， ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。 ORDER BY子句中书写的列名称为排序键
指定升序或降序，默认升序，关键字ASC，降序DESC
ORDER BY 子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。
排序键中包含NULL时，会在开头或末尾进行汇总
Order by 子句中可以使用列的别名，这是因为order by 位于select子句的最后，是先把选择查询的结果跑出来之后再进行排序，因此是先识别了别名的。
在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

/*order by 中使用select中未使用的列*/
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;
/*order by 中使用聚合函数*/
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);

第四章数据更新

4.1 数据的插入

insert语句，负责在表中插入数据。语法格式为INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
将列名和值用逗号隔开，分别括在（）内，这种形式称为清单，包括列清单和值清单，需要注意的是清单里面的个数应该保持一致。
插入多行insert，values之间以逗号隔开，当插入的值为所有列时，可以省略列清单

/*多行INSERT （ Oracle以外）*/
INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

插入NULL值。直接在values中对应的列写入NULL。但是注意插入NULL的那一列不能有NOT NULL 约束
插入默认值。
- 显示默认值：直接在values中某一列写入default，表示该列使用默认值
- 隐式默认值：在列表清单和值清单中不写入该列。但是存在一个问题：未设置默认值的列不写入值的话默认是NULL值，但如果恰好该列又使用了NOT NULL约束的话，该语句就会报错。
从其他表中复制数据。INSERT … SELECT 语句。INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDER BY子句并不会产生任何效果）

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price) 
FROM Product
GROUP BY product_type;

4.2 数据的删除

delete 语句。DROP TABLE 语句可以将表完全删除。DELETE 语句会留下表（容器），而删除表中的全部数据
语法格式：DELETE FROM <表名>;
指定删除对象的delete语句。想要删除部分数据行时，可以像 SELECT 语句那样使用 WHERE子句指定删除条件。这种指定了删除对象的 DELETE 语句称为搜索型DELETE

DELETE FROM Product
WHERE sale_price >= 4000;

注意：DELETE 语句中不能使用 GROUP BY、HAVING 和ORDER BY 三类子句，而只能使用WHERE 子句。原因很简单，GROUP BY 和 HAVING 是从表中选取数据时用来改变抽取数据形式的，而 ORDER BY 是用来指定取得结果显示顺序的。因此，在删除表中数据时它们都起不到什么作用
truncate语句：只能删除表中全部数据的TRUNCATE语句

4.3 数据的更新

更新某一列：
搜索型update语句：在update…set语句后加入where条件

UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;

NULL清空：使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同

UPDATE Product
SET regist_date = NULL
WHERE product_id = '0008';

多列更新：set后面的子句更新列用逗号隔开

/*使用逗号对列进行分隔排列*/
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

4.4 事务

概念：事务transaction就是需要在同一个处理单元中执行的一系列更新处理的集合
语法格式：

事务开始语句;
DML语句①;
DML语句②;
DML语句③;
.
.
.
事务结束语句（ COMMIT或者ROLLBACK） ;

事务开始语句：

START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT;

事务结束语句：COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。因此，在提交之前一定要确认是否真的需要进行这些更新。
rollback：ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态
DBMS必须遵守的规则：ACID特性
- 原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。例如，在之前的例子中，在事务结束时，绝对不可能出现运动 T 恤的价格下降了，而 T 恤衫的价格却没有上涨的情况。该事务的结束状态，要么是两者都执行了（COMMIT），要么是两者都未执行（ROLLBACK）。
- 一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
- 隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。
- 持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后， DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。