SQL

客户端工具

DBeaver


MySQL必知必会

MySQL必知必会 Ben Forta, 配套数据库数据

章节 内容 备注
1. 了解SQL
主键 (primary key) 的定义 能够唯一标识每一行的列或列的组合。不可为 NULL 值。
主键可以由多个列构成
SQL / DBMS的关系 Structrued Query Language
Database Management System
2. MySQL简介
MySQL是一种DBMS
MySQL的重要版本4.1 / 5引入的功能 V4. InnoDB引擎、增加事务处理、并、改进全文搜索
V4.1 函数库、子查询、集成帮助
V5. 存储过程、触发器、游标、视图
mysql --help
help
help select		 获取帮助
3. 使用MySQL
MySQL服务端口3306
使用数据库 use crash;
查看数据库 show databases;
查看当前数据库的包含的表 show tables;
查看表的字段 show columns from customers;
describe customers;
desc customers;		 field: 列名 (字段名)
type: 字段类型
null: 是否允许 NULL 值
key: 标识主键 (PRI)
default: 默认值
extra: 其他信息如 auto_increment
自动增量 auto_increment 每添加一行，自动分配下一个可用编号
显示服务器状态信息 show status;
显示状态信息 status; 显示当前数据库，当前用户，
服务端版本，协议版本，连接信息，
服务端客户端字符集等信息
显示创建数据库的语句 show create database crash;
显示创建列表的语句 show create table customers;
显示授予用户的安全权限 show grants;
显示服务器错误或警告消息 show errors;
show warnings;
help show;
显示用户 select user from mysql.user;
显示当前用户 select user();
查询全局设置 show variables like ‘%auto%’; 列出变量名含有 ‘auto’ 的变量的当前值。例如 autocommit 。
4. 检索数据
select select prod_name
from products;		 检索一列
select prod_id, prod_name, prod_price
from products;		 检索多列
select *
from products;		 检索所有列
distinct select distinct vend_id
from products;		 检索不同行
limit select prod_name
from products
limit 5;		 限制返回不多于五行
limit M offset N select prod_name
from products
limit 4 offset 3;		 从行3开始取4行（行号从0开始）
limit N, M select prod_name
from products
limit 3, 4;		 从行3开始取4行（行号从0开始）
完全限定名 select products.prod_name
from products;		 完全限定表名
select products.prod_name
from crash.products;		 完全限定表名/数据库名
5. 排序检索数据
子句的概念 之前的 from 即为 select 子句
order by select prod_name
from products
order by prod_name;		 默认排序
select prod_id, prod_price, prod_name
from products
order by prod_price, prod_name;		 多列排序，前一列相同的按后一列排序
asc / desc select prod_id, prod_price, prod_name
from products
order by prod_price desc, prod_name desc;		 默认为asc
一个关键字只作用一列
order by + limit select prod_price
from products
order by prod_price desc
limit 1;		 找最值
6. 过滤数据
where select prod_name, prod_price
from products
where prod_price = 2.50;		 where子句操作符
=,<>,!=,<,<=,>,>=,between…and…
between…and… select prod_name, prod_price
from products
where prod_price between 5 and 10;		 包含指定的值
除数值外还可检索日期等
is null select cust_id
from customers
where cust_email is null;		 空值检查
7. 数据过滤 where子句可以组合
and select prod_id, prod_price, prod_name
from products
where vend_id = 1003 and prod_price <= 10;
or select prod_name, prod_price
from products
where vend_id = 1002 or vend_id = 1003;
select prod_name, prod_price
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price <= 10;		 使用多个逻辑符时注意优先级
in select prod_name, prod_price
from products
where vend_id in (1002, 1003)
order by prod_name;		 in结合括号，更清晰
in可以包含其他select语句
not select prod_name, prod_price
from products
where vend_id not in (1002, 1003)
order by prod_name;		 not可以对in, between和exists取反
8. 通配符过滤
通配符 用来匹配值的一部分的特殊字符。
搜索模式 由字面值、通配符或两者组合构成的搜索条件。
like select prod_name, prod_price
from products
where prod_name like “jet%”;		 % 表示任何字符出现任意次
默认不区分大小写
%不能匹配 null 字段
通配符"%" select prod_id, prod_name
from products
where prod_name like “%anvil%”;		 可在搜索模式中的任意位置
select prod_id, prod_name
from products
where prod_name like “s%e”;		 以s开头以e结尾的prod_name
通配符"_" select prod_id, prod_name
from products
where prod_name like “_ ton anvil”;		 匹配一个字符，不多也不少
通配符使用原则:
其他操作符可达到目的时使用其他操作符
尽量不在搜索模式开头使用通配符
9. 正则表达式
regexp select prod_name
from products
where prod_name regexp “1000”
order by prod_name;		 基本字符匹配
正则字符"." select prod_name
from products
where prod_name regexp “.000”
order by prod_name;		 "."匹配任意一个字符
like 和 regexp 的重要区别：
like 匹配整个字段，而 regexp 匹配字段的部分		 like “100”
regexp “100”
对于1000，like不匹配而regexp匹配
binary regexp 不区分大小写
使用 binary 可以区分大小写
正则字符"|" select prod_name
from products
where prod_name regexp ‘1000|2000’
order by prod_name;		 匹配两个串之一
正则字符"[]" select prod_name
from products
where prod_name regexp ‘[123] ton’
order by prod_name;		 匹配指定字符之一
正则字符"()" select prod_name
from products
where prod_name regexp ‘(1|2|3) ton’
order by prod_name;		 (1|2|3)作用等同于[123]
正则字符"[^]" select prod_name
from products
where prod_name regexp ‘[^123] ton’
order by prod_name;		 匹配除指定字符之外的字符
正则字符"[0-9]" select prod_name
from products
where prod_name regexp ‘[0-9] ton’;		 [0-9]匹配数字0到9
[1-5]匹配数字1到5
转义"\\" select prod_name
from products
where prod_name regexp ‘\\.’;		 两个反斜杠表示转义
其他转义: \\f, \\n, \\r, \\t, \\v
\\\ 匹配反斜杠本身
预定义字符集 select prod_name
from products
where prod_name regexp ‘[:alnum:]’;		 [:alnum:] 同 [a-zA-Z0-9]
[:alpha:] 同 [a-zA-Z]
[:digit:] 同 [0-9]
[:lower:] 同 [a-z]
[:upper:] 同 [A-Z]
重复元字符 * 0个或多个
+ 1个或多个，同{1,}
? 0个或1个，同{0,1}
{n} 指定匹配n个
{n,} 指定匹配不少于n个
{n,m} 指定匹配数据不少于n个不多于m个(m<=255)
select prod_name
from products
where prod_name regexp ‘\\([0-9] sticks?\\)’;
定位符 ^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾		 ^ 在 [^] 中用于指定该集合元素之外的元素，否则用来指串的开始处。
select prod_name
from products
where prod_name regexp ‘^[0-9\\.]’;		 以小数点或数字开始
测试 select ‘hello’ regexp ‘[a-z]’; 匹配返回1，不匹配返回0
不读取表即可直接测试正则表达式
10. 创建计算字段
对检索结果的转换、计算、格式化等
concat() select concat(vend_name, ’ (', vend_country, ‘)’)
from vendors
order by vend_name;		 concat指定一个或多个串，以逗号隔开
rtrim()
ltrim()
trim()		 select concat(vend_name, ’ (', rtrim(vend_country), ‘)’)
from vendors
order by vend_name;		 去掉字段右边/左边/两边的空格
as select concat(vend_name, ’ (', trim(vend_country), ‘)’) as vend_title
from vendors
order by vend_name;		 也可以省略as
算术计算 select prod_id, quantity, item_price, quantity * item_price as extended_price
from orderitems
where order_num = 20005;		 MySQL 算术操作符: +, -, *, /
测试计算 select 3 * 2; select 1;
select now();
11. 函数
upper() select vend_name, upper(vend_name)
from vendors
order by vend_name;		 常用文本处理函数:
left() / right()
length()
lower() / upper()
ltrim() / rtrim() / trim()
locate() / substring()
soundex()
soundex() select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex(‘Y. Lie’);
日期和时间 select cust_id, order_num
from orders
where order_date = ‘2005-09-01’;		 涉及日期时间应使用相关函数
date() select cust_id, order_num
from orders
where date(order_date) = ‘2005-09-01’;		 日期和时间相关函数
adddate/addtime
curdate/curtime
now/date/year/month/day
time/hour/minute/second
datediff/date_add/date_format
dayofweek
select cust_id, order_num, order_date
from orders
where date(order_date) between ‘2005-09-01’ and ‘2005-09-30’;
select cust_id, order_num
from orders
where year(order_date) = 2005 and month(order_date) = 9;
数值处理 作用于数值数据的代数、三角函数或几何运算等 abs/sqrt/exp/mod
pi/sin/cos/tan/rand
12. 汇总数据
聚集函数 作用于一列，返回一个计算值 MySQL 提供五种聚集函数: avg/count/max/min/sum
avg() select avg(prod_price) as avg_price
from products;		 avg()只作用于一列，
获取多列时使用多个avg()
avg()忽略null
select avg(prod_price) as avg_price
from products
where vend_id = 1003;		 结合where子句
count() select count(*)
from customers;		 count(*)包括null
select count(cust_email) as valid_cust
from customers;		 count(<指定列名>)不包括null
max() select max(prod_price) as max_price
from products;
min() select min(prod_price) as min_price
from products;
sum() select sum(quantity) as total
from orderitems;
select sum(quantity) as 20005_total
from orderitems
where order_num = 20005;		 结合where子句
select sum(item_price * quantity) as 20005_total_price
from orderitems
where order_num = 20005;		 结合计算
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;		 结合其他关键字
组合聚集函数 select
count(*) as num_items,
min(prod_price) as min_price,
max(prod_price) as max_price,
avg(prod_price) as avg_price
from products;
13. 分组数据
group by select vend_id, count(*) as num_prods
from products
group by vend_id;		 按逻辑组返回
null 会被分为一组
group by 在 where 后 order by 前
聚合函数 + group by 参考1
group by 与聚合函数使用时，后者作用在每一个分组之上。
group by 的分组字段可以有多个，例如 group by vend_id, prod_id;		 group by x 将表按 x 分为 n 个组（每个组至少有一行），然后对每个组的多个值求「聚合函数f(y)」的值，y是「聚合」列的列名。
可想象 group by 的过程有个「临时表」，该表有 n 行，每一行的格子里放入了 x 在原表中对应的多个值。之后聚合函数施加于这个格子中所有值之上。
select 列，同时也适用聚集函数时，必须使用group by，如下语句将报错
select vend_id, count(*)
from products;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘crash.products.vend_id’; this is incompatible with sql_mode=only_full_group_by
with rollup select vend_id,count(*) as num_prods
from products
group by vend_id with rollup;		 得到每个分组以及汇总值
包括 null
having select cust_id, count(*)
from orders
group by cust_id
having count(*) >= 2;		 过滤分组
where过滤行，
having过滤组
having 支持所有 where 操作符
select vend_id, count(*) as num_prods
from products
where prod_price >= 10
group by vend_id
having count(*) >= 2;		 where 与 group by - having 结合
select order_num, sum(item_price * quantity) as ordertotal
from orderitems
group by order_num
having ordertotal >= 50
order by ordertotal;		 group by 与 order by 结合
select子句顺序 select
from
where
group by
having
order by
limit
14. 使用子查询 由4.1引入
select cust_id
from orders
where order_num in
(
select order_num
from orderitems
where prod_id = ‘TNT2’
);		 从内向外执行
select cust_name, cust_contact
from customers
where cust_id in
(
select cust_id
from orders
where order_num in
(
select order_num
from orderitems
where prod_id = ‘TNT2’
)
);		 子查询多重嵌套
select cust_name, cust_state,
(
select count(*)
from orders
where orders.cust_id = customers.cust_id
) as orders
from customers
order by cust_name;		 计算字段子查询
涉及到完全限定名
逐渐增加子查询来建立查询：
先建立和测试内层查询，再用直接数据测试外层查询
15. 联结表
foreign key 外键的概念 当前表的外键为另一表的主键
分表 类似程序中的「解耦」，一张表应当只存放一类信息，例如 vendors 表存放供应商信息，products 存放产品信息。两张表的外键分别是对方的主键。 分表的好处:
各自信息解耦，节省空间，逻辑清晰。
若其中一个表有改动，只需修改该表即可而不需改动其他表。
分表增强了数据库的可伸缩性。
from 两张表
等值联结		 select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;		 等值联结是内联结 (inner join on) 的另一种写法
where 子句使用完全限定列名
select vend_name, prod_name, prod_price
from vendors as v, products as p
where v.vend_id = p.vend_id
order by vend_name, prod_name;		 简化表名 (as可省略)
笛卡尔积 select vend_name, prod_name, prod_price
from vendors, products
order by vend_name, prod_name;		 不加 where 限定条件时得到笛卡尔积
inner join on select v.vend_id, vend_name, prod_name
from
vendors v
inner join
products p
on v.vend_id = p.vend_id
order by vend_name, prod_name;		 内联结
如果 on 后用于连接的键相同，
也可以使用 using (vend_id)
多表联结 select prod_name, vend_name, prod_price, quantity
from orderitems oi, products p, vendors v
where p.vend_id = v.vend_id
and oi.prod_id = p.prod_id
and order_num = 20005;
select cust_name, cust_contact
from customers c, orders o, orderitems oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and oi.prod_id = ‘TNT2’;		 优于多重子查询的内联结写法
16. 高级联结
select prod_id, prod_name
from products
where vend_id =
(
select vend_id
from products
where prod_id = ‘DTNTR’
);
自联结 上述查询的自联结做法：
select p1.prod_id, p1.prod_name
from products p1, products p2
where p1.vend_id = p2.vend_id
and p2.prod_id = ‘DTNTR’;		 表与自身连接
自然联结 select c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
from customers c, orders o, orderitems oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = ‘FB’;
外部联结 select c.cust_id, o.order_num
from
customers c
left outer join
orders o
on c.cust_id = o.cust_id;		 left outer join (即 left join)
right outer join (即 right join)
select c.cust_id, o.order_num
from
customers c
inner join
orders o
on c.cust_id = o.cust_id;		 内部联结比较
联结+聚集函数 select c.cust_id, c.cust_name, count(o.order_num) as num_order
from
customers c
inner join
orders o
on c.cust_id = o.cust_id
group by c.cust_id;
select c.cust_id, c.cust_name, count(o.order_num) as num_order
from
customers c
left outer join
orders o
on c.cust_id = o.cust_id
group by c.cust_id;
17. 组合查询
union select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);		 通过union组合多条select语句
union all 与 where 通过多个条件过滤相同，也会去除重复的行，利用 union all 可包含重复的行
union + order by select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002)
order by vend_id, prod_price;		 order by 只能用于最后，对整个结果集排序
18. 全文本搜索
数据库引擎支持 MyISAM 支持，InnoDB 不支持 由于数据是索引的，全文本搜索相比 LIKE 或正则表达式方式要快
启用全文搜索 CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text,
PRIMARY KEY (note_id),
FULLTEXT KEY note_text (note_text)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;		 在 CREATE 语句中使用 FULLTEXT KEY() 子句
match / against select note_text
from productnotes
where match(note_text) against(‘rabbit’);		 结果以「匹配」的良好程度排序返回
select note_text, match(note_text) against(‘rabbit’) as score
from productnotes;		 列出匹配程度得分
19. 插入数据
依据次序插入 insert into customers
values (null, ‘yukiyama’, ‘liangzhu wenhuacun’, ‘hangzhou’, ‘ZJ’, ‘00000’, ‘China’, null, null);		 需严格按照次序完整给出所有列
依据列名插入 insert into customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values (‘duoduo’, ‘Koiwa’, ‘Tokyo’, ‘Japan’, ‘22222’, ‘Japan’, null, null);		 对于可以为 NULL 值的列，且默认值为 NULL，则插入 NULL 值时，可省略不写。
合并表 insert into customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values (‘duoduo’, ‘Koiwa’, ‘Tokyo’, ‘Japan’, ‘22222’, ‘Japan’, null, null)
select cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email
from custnew;		 select 中的列名不必与 insert into 中的相同。select 中声明的列讲依次填充 insert into 中指定的列。
20. 更新和删除数据
更新一行一个字段 update customers
set cust_email = ‘efudd@gmail.com’
where cust_id = 10005;
更新一行多个字段 update customers
set cust_email = ‘efudd@gmail.com’,
cust_name = ‘The Fudds’
where cust_id = 10005;
更新多行一个字段 update customers
set cust_email = null;		 不使用 where 子句过滤时将更新所有行的指定字段为指定值
删除行 delete from customers
where cust_id = 10006;		 以 where 子句过滤要删除的行
删除所有行 delete from customers; 不使用 where 子句过滤时将删除所有行
更新和删除的原则 在更新和删除前应先使用 select 列出目标，确保不误更新活误删除
21. 创建和操纵表
22. 使用视图
23. 使用存储过程
24. 使用游标
25. 使用触发器