文章内容是对《MySql必知必会》一书的主要知识总结，涵盖每一章节的概念和知识点；是一篇入门级文章，适合想要快速掌握mysql常用术语、知识的同学们。不得不说作为一本入门级书籍，《MySql必知必会》的确非常靠谱实用，老少咸宜啊。
数据库术语
- 数据库（database）保存有组织的数据的容器(通常是一个文 件或一组文件)。
- 数据表（table）某种特定类型数据的结构化清单。
- 列 (column) 表中的一个字段。所有表都是由一个或多个列组成的。
- 数据类型 (datatype) 所容许的数据的类型。每个表列都有相 应的数据类型，它限制(或容许)该列中存储的数据。
- 行 (row) 表中的一个记录。表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。
- 主键 (primary key)一列(或一组列)，其值能够唯一区分表中每个行。表中每一行都应该有可以唯一标识自己的一列(或一组列)。
- SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。
mac安装数据库
- 下载MySQL Community Server；传送门：MySQL下载
- 双击安装，完成之后为了方便全局可以修改配置文件，如果终端使用zsh则修改.zshrc，使用bash则修改.bash_profile文件。以.zshrc为例：执行
vim ~/.zshrc，末尾添加export PATH=$PATH:/usr/local/mysql/bin，保存退出后执行source ~/.zshrc，然后输入mysql -u root -h localhost -p，终端会要求输入password，此时输入在安装mysql时你制定的密码即可成功登录了。
使用mysql
- 终端使用mysql
- vscode使用mysql
- 专业的可视化工具比如workbench 这里我主要使用的是前两个方式，终端使用mysql很方便，唯一的不足是操作结果展示不太友好；如果你也在使用vscode，我也推荐使用vscode操作mysql，使用方式如下：
- 首先在vscode插件商店搜索SQLTools并安装
- 之后在vscode的左侧菜单栏上会看到一个数据图图标
- 接下来你就可以创建数据库连接，新建.sql文件输入命令并点击文件顶部的run on active connection 执行sql语句
- 执行命令
create database demo;新建一个名字叫demo的数据库，之后练习的所有操作都在这个数据库里面进行了。
创建数据表
这里直接使用《MySql必知必会》书中的样例表定义。创建一个客户信息表为例：
########################
# Create customers table
########################
DROP TABLE IF EXISTS customers;
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
其他样例表的定义脚本书中也给出了。完整的定义如下：
########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################
########################
# Create customers table
########################
DROP TABLE IF EXISTS customers;
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
#########################
# Create orderitems table
#########################
DROP TABLE IF EXISTS orderitems;
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# Create orders table
#####################
DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
#######################
# Create products table
#######################
DROP TABLE IF EXISTS products;
CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
######################
# Create vendors table
######################
DROP TABLE IF EXISTS vendors;
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
###########################
# Create productnotes table
###########################
DROP TABLE IF EXISTS productnotes;
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
创建完上述样例表之后，在vscode里可以看到目前所有的数据表：
插入数据
插入数据的sql语句是
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
插入多条数据可以重复多条insert into语句，或者values之后带多条数据值，使用逗号分隔。
// 插入多条数据
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(
10001,
'Coyote Inc.',
'200 Maple Lane',
'Detroit', 'MI',
'44444',
'USA',
'Y Lee',
'ylee@coyote.com'
),
(
10001,
'Coyote Inc.',
'200 Maple Lane',
'Detroit', 'MI',
'44444',
'USA',
'Y Lee',
'ylee@coyote.com'
);
书中同样提供了插入样例数据的脚本，可以直接执行populate.sql脚本给表里插入数据。
检索数据
书中多数章节都围绕着检索数据，平常主要工作也是检索数据占多数，下面就来详细介绍数据检索。
基础检索
-
检索单个列
select prod_name from products； -
检索多个列
select prod_id, prod_name from products; -
检索所有列
select * from products; -
检索不同的值，或者说给数据去重，使用关键字
distinct；distinct不止作用于跟在其后的那一列，而是作用在所有列。select distinct prod_name from products； select distinct prod_id, prod_name from products； -
限制检索结果——LIMIT、OFFSET
select prod_name from products limit 5; // 返回前5条数据 select prod_name from products limit 5 offset 5; //limit 指定返回的行数，offset 指定从哪开始检索 -
排序检索数据——ORDER BY
-
列名排序
select cust_id from customers order by cust_id; select cust_id, cust_name from customers order by cust_id，cust_name; -
列位置排序
select cust_id, cust_name from customers order by 1，2; -
注意事项- order by 子句必须是select 语句中的最后一条子句
- order by 子句后面可以使用非检索的列
-
指定排序方式
- 升序 ASC
- 降序 DESC
select cust_id，cust_name from customers order by cust_id desc, cust_name asc;
过滤数据——WHERE
#过滤数据
#mysql 检索不区分大小写
#in操作符不是两个区间的过滤而是括号里的枚举值的联合查询
#select * from products where prod_price > 3;
#select * from products where prod_name = 'saFe';
#select * from products where prod_name = 'safe' and prod_price > 9;
#select * from products where prod_price between 10 and 50 and prod_name > 'j';
#select * from products where prod_price in (10, 20);
#select * from products where prod_price not in (10, 20, 55, 50);
- 操作符
| 符号 | 含义 |
|---|---|
| = | 等于 |
| != | 不等于 |
| <> | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| !< | 不小于 |
| 大于 | |
| >= | 大于等于 |
| !> | 不大于 |
| between and | 范围之间 |
| is null | 为空 |
高级过滤——组合where 子句条件
- AND 与
- OR 或
- IN 在范围内
- NOT 非
模糊检索
- 通配符匹配
通配符匹配的模糊检索包含以下几个元素：
- 通配符
- 搜索模式
- 谓词
常见通配符有： % 和 _
- （百分号）% ：匹配任意多个字符{0, }
- （下划线）_ ：匹配单个字符，匹配两个字符使用__两个下划线
#使用通配符做模糊匹配，配合谓词like使用
#%百分号通配符匹配任意字符任意次数
#_匹配任意字符单次
#把通配符置于搜索模式的开始处，搜索起 来是最慢的。
#select * from products where prod_name like '%anv%';
#select * from products where prod_name like '_ ton anv%';
- 通配符的使用技巧和注意事项
- 通配符搜索比一般搜索耗费更多的处理时间
- 不要过度使用通配符
- 尽量不要把通配符用在搜索模式的开始处，这样是最慢的
- 仔细注意通配符的位置，若放错地方会返回意料之外的数据
- 正则匹配搜索
#使用正则表达式做模糊匹配，配合regexp谓词使用
#select * from products where prod_name regexp '\\([0-9] sticks{0,1}\\)';
- 全文本搜索
#全文本搜索
#首先数据表需要使用fulltext关键字开启全文本搜索
#使用match(列名) against(搜索字符串)全文本搜索，该语句返回的是一个数值代表匹配等级rank
#select note_text from productnotes where match(note_text) against('rabbit');
计算字段
计算字段并不实际存在于数据库表中，而是在select 语句运行时生成的字段。通常需要给计算字段起一个别名，使用as关键字，才能在返回结果中使用计算字段。
-
拼接字段的几种方法
-
列1 + ‘自定义’ + 列2 + ‘自定义’
-
列1 || ‘自定义’ || 列2 || ‘自定义’
-
Concat(列1，‘xx’，列2，‘自定义’)
-
-
执行算术计算
- 列1 * 列2 as 别名
-
字段别名 （AS）关键字
- 计算字段必须使用别名才能返回给客户端使用
-
几个例子
select prod_name, quantity,item_price,quantity*item_price AS expanded_price from OrderItems where order_num = 20008;
select Concat(RTRIM(vend_name, '(', RTRIM(vend_country), ')') as vend_title from Vendors order by vend_name;
-
用函数处理数据
-
函数的优缺点
- 优点：处理复杂的计算
- 缺点：每个DBMS支持的相同计算能力的函数名不同，使用函数存在不能跨平台使用的风险
-
函数种类
- 系统函数
- 字符串处理函数
- 日期处理函数
- 数值处理函数
-
-
汇总数据
-
聚集函数——对某些行运行的函数，计算并返回一个值
-
常用的聚集函数
-
AVG：返回某列的平均值
-
COUNT：返回某列的行数
-
MAX：返回某列的最大值
-
MIN：返回某列的最小值
-
SUM：返回某列值之和
-
-
使用分组——GROUP BY子句
-
GROUP BY子句
- 创建分组，指使DBMS分组数据，然后对每个组而不是整个结果集进行聚集
- group by 子句中列出的列必须是检索列或检索的有效表达式。
-
HAVING子句
- 用于过滤分组
- having和where的区别
- where筛选行，having筛选分组
- having搭配group by 使用就是筛选分组，没有group by子句时和where功能一样
- where 和 having 可以同时存在于一个检索语句中，一般是用于分组检索的情况下
-
例子
select vend_id, count(*) as num_prods from Products group by vend_id;
select vend_id, count(*) as num_prods from Products group by vend_id having count(*) >= 2;
- 使用group by的注意事项
- group by 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组
- 如果在group by子句中嵌套了分组，数据将在最后指定的分组上进行汇总。
- group by 子句中列出的每一列都必须是是检索列或有效的表达式，不能使用别名
- 大多数sql实现不允许group by列带有长度可变的数据类型
- 除聚集计算语句外，select 语句中的每一列都必须在group by子句中给出
- 如果分组列中包含具有null值的行，则null将作为一个分组返回。
- group by 子句必须出现在where子句之后，order by子句之前。
检索语句子句顺序
检索语句的各个子句的顺序是：
- select
- from
- where
- group by
- having
- order by
子查询
- 在一个检索语句中嵌套另一个检索语句，被嵌套的检索语句称为子查询语句
- 当一条检索语句依赖其他数据库表中的数据时，使用子查询。也就是子查询作为查询条件使用。
select cust_id from Orders
where order_num in (select order_num from OrderItems where prod_id = 'RGANO1');
- 子查询作为计算字段使用
select cust_name, cust_state, (select count(*) from Orders where Orders.cust_id = Customers.cust_id) as orders from Customers order by cust_name;
表联结——JOIN
-
table1 inner join table2 on 条件
-
table1 outer join table2 on 条件
-
table1 left outer join table2 on 条件
-
table1 right outer join table2 on 条件
-
什么是表联结——JOIN
- 从多个关系数据表中联合查询出数据集合的操作
-
为什么使用表联结
- 想在一个select语句中检索出位于多个关系表中的数据时，表联结能够很好的解决问题
-
怎么对表进行联结查询
-
from 和 where 子句
- from 子句 指定多个表
- where 条件 指定联结的条件
-
-
例子
select vend_name, prod_name, prod_price from Vendors, Products where Vendors.vend_id = Products.vend_id;
select vend_name, prod_name, prod_price from Vendors inner join Products on vendors.vend_id = Products.vend_id;
#外联结分left outer join 和 right outer join。left 是从左边的表中选择所有行，right指的是从右边表中选择所有的行
select customers.cust_id,orders.order_num from customers left outer join orders on orders.cust_id = customers.cust_id;
select customers.cust_id,orders.order_num from customers right outer join orders on orders.cust_id = customers.cust_id;
- 相关术语
- 等值联结
- 内联结
- 外联结
组合查询——UNION
-
UNION的使用场景
- 从多个表中检索数据
- 从一个表中检索不同条件限制的数据
-
操作符
-
UNION
- 去重
-
UINION ALL
- 不去重
-
-
例子
select cust_name from Customers where cust_state in ('IL', 'IN', 'MI') union select cust_name from Customers where cust_name = 'Fun4All' -
注意事项
- 必须由两条以上的select语句组成
- 每个查询必须包含相同的列、表达式或聚集函数
- 列数据类型必须兼容：类型不必完全相同，但必须是可以隐含转换的类型。
使用视图
视图的概念：视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态 检索数据的查询。视图本身不包含数据。 使用视图的好处是可以重用sql，简化复杂sql操作。
- 视图用CREATE VIEW语句来创建。
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
- 用DROP删除视图，其语法为DROP VIEW viewname;。
- 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图; 如果要更新的视图存在，则第2条更新语句会替换原有视图。
利用视图简化复杂的联结
把复杂的联结过程用视图保存起来，可以简化后续同类型的数据检索。
// 创建一个名为productcustomers的视图
create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num=orders.order_num;
// 从视图中检索数据
select cust_name,cust_contact from productcustomers where prod_id='tnt2';
使用视图与计算字段
把相关的计算逻辑用视图保存起来，可以方便后续的数据检索
create view orderitemexpanded as
select order_num, prod_id, quantity, item_price, quantity * item_price as expanded_price
from orderitems;
使用存储过程
#存储过程
#CREATE PROCEDURE processor() BEGIN select * from products end;
使用触发器
#触发器 TRIGGER
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
#执行👆这句语句报错ERROR 1415 (0A000): Not allowed to return a result set from a trigger
MYSQL5以后，不允许触发器返回任何结果，因此使用into @变量名，将结果赋值到变量中，用select调用即可
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @order_num;
insert into orders(order_date,cust_id) values(now(),10001);
select @order_num; // 返回20011
使用事务处理
#事物处理
#不是所有引擎都支持事务，MyISAM不支持事务，而InnoDB支持事务。
#事务处理是一种机制，用来管理必须成批执行的mysql操作，以保证数据库不包含不完整的操作结果。
#事务的几个术语：事务transaction，回退rollback，提交commit，保留点savepoint
性能和安全
todo……
