连接

mysql -uroot -p
password

显示所有数据库

show databases;

选择数据库

use database_name

显示当前数据库中所有表

show tables;

显示某个表的数据列

show columns from table_name;
desc table_name;

显示创建特定数据库或表的MySQL语句

show create database database_name;
show create table table_name;

显示授予用户的安全权限

show grants;

显示服务器错误或警告

show errors;
show warnings;

检索数据

检索一个列

SELECT field_name FROM table_name;

检索多个列

SELECT field_name,field_name,... FROM table_name;

检索所有列

SELECT * FROM table_name;

检索不同的列,使用 DISTINCT 关键字

SELECT DISTINCT  field_name FROM table_name

限制结果

SELECT field_name FROM table_name limit 5;

使用全限定的表名

SELECT products.prod_name FROM study.products;
SELECT 表名.字段名 FROM 数据库名.表名；

1、排序检索数据

子句

SQL语句是由子句构成，有些子句是必需的，而有的是可选的，子句的列子有select语句的FROM子句

排序可使用ORDER BY子句，ORDER BY子句取一个或多个列的名字,这个子句必须是SELECT中的最后一条子句

SELECT field_name FROM table_name ORDER BY field_name;

可以使用DESC指定排序的方向（降序排序），ASC指定升序（默认排序方式）

SELECT field_name FROM table_name ORDER BY field_name DESC,field_name ASC;

2、过滤数据

在 SELECT 语句中，根据 WHERE 子句给出搜索条件

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEW	在指定的两个值之间

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEW 5 AND 10;

空值检查

SELECT 语句有一个特殊的WHERE子句，可用来检查具有 Null 值的列，这个 WHERE 子句就是 ==IS NULL==

组合WHERE子句

WHERE 子句在过滤数据时使用的都是单一的条件，为了进行更强的过滤控制，MySQL允许给出多个WHERE子句，以AND子句或OR子句的方式使用

AND操作符

SELECT *
FROM products
WHERE vend_id = 1003 AND prod_price <=10

OR操作符

SELECT *
FROM products
WHERE vend_id = 1003 OR prod_price <=10

3、计算次序

在使用AND和OR时，AND在计算次序中优先级更高，如果想OR子句先执行可使用小括号

IN操作符

小括号在WHERE子句中还有另一种用法，IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，IN取合法值的由逗号分隔的清单, 在 WHERE 子句中规定多个值


SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

NOT操作符

NOT对跟在它之后的条件进行取反，支持对 IN BETWEEN EXISTS 子句取反

SELECT prod_name,prod_price,vend_id
FROM products
WHERE vend_id NOT IN (1002,1003) #不包含1002，1003的
ORDER BY prod_name;

4、通配符过滤

LIKE操作符

百分号（%）通配符

匹配0个或多个字符

SELECT prod_name,prod_id
FROM products
WHERE prod_name LIKE 'jet%'

下划线（__）通配符

用途与百分号一样，但是下划线只==匹配单个字符==

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil'

5、正则表达式

基本字符匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name

6、聚集函数

函数	说明
AVG()	返回某列的平均值
count()	返回某列的行数
MAX()	返回某列的最大值
MIN()	列的最小值
SUM()	返回某列的值之和

7、数据分组

分组允许把数据分为多个逻辑组，以便对每个组进行聚集计算

select vend_id,count(*)
from products
group by vend_id;

在具体使用group by子句前，需要知道一些规定

GROUP BY 子句可以包含任意数目的列，这使得能对分组进行嵌套为数据分组提供更细致的控制
如果GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总，换名话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，（但不能是聚集函数）如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT 语句中的每个列都必须在GROUP BY 子句中给出
如果分组列中具有NULL值，则NULL将作为一个分组返回，如果列中有多行NULL值，它们将分为一组
GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 子句之前 。

过滤分组

使用==HAVING== 子句，语法与 WHERE 相同，关键字有差别。

SELECT vend_id, count(*) as num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

8、子查询

利用子查询进行过滤

查询orders时使用的条件 order_num 来自子查询得到的结果

SELECT * FROM orders
WHERE order_num in ( SELECT order_num 
					FROM orderitems WHERE prod_id = 'TNT2')

作为计算字段使用子查询

使用子查询的另一个方法是创建计算字段，假如需要显示customers表中每个用户的订单总数。订单与相应的客户id存储在orders表中。

SELECT 
	cust_name,
	cust_state,
	(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customer.cust_id) as orders
FROM customers
ORDER BY cust_name

9、联结表

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结

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

使用WHERE 子句建立联结关系

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

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== 结出联系条件

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors 
where products.vend_id = vendors.vend_id 
and orderitems.prod_id = products.prod_id 
and order_num=20005;

自联结

假如你发现某物品（id为DTNTR)存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

此查询要求首先找到该id物品的供应商，然后找出这个供应商生产的其他物品

以下是使用子查询查出的结果

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')

以下是使用自联结，此查询需要的两个表实际上是相同的表，因此products 在FROM子句中出现两次

SELECT p1.prod_id, p1.prod_name
from products as p1, products as 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 AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB'

在上面这个例子中，通配符只对第一个表使用。所有其他列明确列出，所有没有重复的列被检索出来

事实上，目前为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部

外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如

对第个客户下了多少个订单进行计数，包含那些至今尚未下订单的客户
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括那些至今尚未下订单的客户

在上述中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结

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

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。

存在两种外部联结

-- 左外连接 left join
如果数据不存在，左表记录会出现，而右表为null填充
-- 右外连接 right join
如果数据不存在，右表记录会出现，而左表为null填充

10、组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句，MySQL也允许执行多个查询（多条 SELECT），并将结果作为单个查询结果集返回，这个组合查询通常称为并（union）或复合查询

有两种基本情况，其中需要使用组合查询

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据

使用UNION

假如需要价格小于等于5的所有物品列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）

select vend_id,prod_id,prod_price
from products
where prod_price <=5

select vend_id,prod_id,prod_price
from products
where vend_id in (1001,1002)

为了组合这两条语句，按如下进行

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规则

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

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行

如果需要重复的行，可以改变它。可使用UNION ALL而不是UNION

对组合查询结果排序

SELECT 语句的输出用ORDER BY子句排序，在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

select vend_id,prod_id,prod_price,prod_desc
from products 
where prod_price <=5 
union ALL 
select vend_id,prod_id,prod_price,prod_desc 
from products 
where vend_id in (1001,1002) 
order by prod_price;

11、全文搜索

数据库引擎 MyISAM 和 InnoDB , 前者支持全文搜索

全文搜索需要使用子句 FULLTEXT(note_text) 的指示对它进行索引

使用 Match() 和 Against()执行全文搜索，Match() 指定被搜索的列，Against()指定要使用的搜索表达式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit')

插入数据

插入完整的行

INSER INTO customers
VALUES(NULL,NULL)
-- 各个列都该给出

更新和删除数据

更新数据

更新表中特定行

update customers set cust_email = 'affaf' where cust_id = 2004

删除数据

DELETE FROM customers where cust_id = 32323;

删除和更新习惯

UPDATE 和 DELETE 子句要带上WHERE
保证每个表都有主键
在更新和删除前，应该先用SELECT 进行测试，再删除

创建和操纵表

表的创建

CREATE TABLE customers(
	cust_id int NOT NULL, AUTO_INCREMENT,
	cust_name char(50) NOT NULL,
	cust_address char(50) NULL
	PRIMARY KEY (cust_id)
)ENGINE=INnoDB

数据库存储引擎

INnoDB 是一个可靠的事务处理引擎，它不支持全文搜索
MEMORY 在功能等同于MyISAM,但是由于数据存储在内存，速度很快，（适合临时表）
MyISAM 是一个性能级高的引擎，它支持全文搜索

更新表

使用ALTER TABLE语句

ALTER TABLE vendors
ADD vend_phone char(20);

ALTER TABLE vendors
DROP COLUMN vend_phone

删除表

DROM TABLE customers;

视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。做为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询

为什么使用视图

重用SQL语句
简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

使用视图的规则和限制

与表一样，视图必须唯一命名（不能与别的视图或者表同名）
对于可以创建的视图数据没有限制
为了创建视图，必须具有足够的访问权限
视图可以嵌套
order by 可以用在视图中，如果SELECT 子句中有order by 子句，视图中的order by将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。如：编写一条将联结表和视图的SELECT语句

使用视图

图片用CREATE VIEW 语句创建
使用 SHOW CREATE VIEW viewname; 查看创建视图语句
用DROP删除视图，DROP VIEW view_name;
更新视图时，可以先用DROP再用CREATE

CREATE VIEW productcustomers as 
select cust_name, cust_contact, prod_id
from customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

为什么要使用存储过程

通过把处理封闭在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
简化对变动的管理
提高性能
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

创建存储过程

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT AVG(prod_price) as priceaverage
	FROM products;
END //
DELIMITER

使用存储过程

call productpricing()
#存储过程是一个函数

删除存储过程

drop procedure productpricing;

使用参数

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT MIN(prod_price)
	INTO pl
	FROM products;
	SELECT MAX(prod_price)
	INTO ph
	FROM products;
	SELECT AVG(prod_price)
	INTO pa
	FROM products;
end;

调用此存储过程，必须指定3个变量名，调用后些语句并不显示任何数据，它返回以后可以显示

call productpricing(@pricelow, @pricehigh, @priceaverage);

可以使用以下命令显示

SELECT @pricelow, @pricehigh;

使用游标

mysql游标只能用于存储过程（和函数）

有时，需要在检索出来行中前进或后退一行或多行，这就是使用游标的原因

使用游标的几个步骤

在能够使用游标前，必须声明定义它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来
对于填有数据游标，根据需要取出（检索）各行
在结束游标使用时，必须关闭游标

游标使用DECLARE语句创建,以下定义游标为ordernumbrs的游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

打开游标，当一个游标被打开后，可以使用FETCH语句分别访问它的每一行

游标用open ordernumbers

关闭游标

close ordernumbers

CREATE PROCEDURE processorders()
BEGIN
	DECLARE o INT;
	DECLARE ordernumbers CURSOR
	FOR 
	SELECT orders FROM orders;
	
	open ordernumbers;
	
	
	fetch ordernumbers into o;
	
	close ordernumbers;
end

create PROCEDURE processorders1()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	
	DECLARE o INT;
	
	DECLARE ordernumbers cursor
	for 
	select order_num from orders;
	
	declare continue HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	open ordernumbers;
	
	repeat
	
		fetch ordernumbers into o;
		
	until done end repeat;
	
	close ordernumbers;
	
end;

create PROCEDURE processorders2()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	
	DECLARE o int;
	
	DECLARE t decimal (8, 2);
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num from orders;
	
	DECLARE continue handler for sqlstate '02000' set done= 1;
	
	create table if not exists ordertotals(order_num int, total decimal(8,2));
	
	open ordernumbers;
	
	repeat
		fetch ordernumbers into o;
		
		call ordertotal(o,1,t);
		
		insert into ordertotals(order_num, total)
		values(o, t);
		
	until done end repeat;
	
	close ordernumbers;
END;

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)

)
BEGIN
	DECLARE total DECIMAL(8,2);
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT SUM(item_price*quantity)
	FROM orderitems
	where order_num = onumber
	into total;
	
	if taxable THEN
		select total+(total/100*taxrate) into total;
	end if;
	
	select total into ototal;
END;

触发器

触发器是MySQL响应以下任意语句而自动执行的MySQL 语句（或位于BEGIN 和 END 语句之间的语句）

DELETE
INSERT
UPDATE

创建触发器

在创建触发器，需要给出4条信息：

唯一的触发器名
触发器关联的表
触发器应该响应的活动（DELETE 、INSERT或UPDATE)
触发器何时执行（处理之前或之后）

触发器用 CREATE TRIGGER语句创建

CREATE TRIGGER newproduct AFTER INSERT on product
FOR EACH ROW SELECT 'Product added';

删除触发器

DROP TRIGGER newProduct;

INSERT 触发器

insert 触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

在 INSERT触发器代码内，可引用一个名为NEW的虚拟表，该问被插入的行
在BEFFORE INSERT 触发器中，NEW中的值也可以被更新（允许更改被插入的值）
对于AUTO_INCREMENT列，NEW 在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

CREATE TRIGGER newOrder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @val;

先次插入新行，都会触发

BEFFORE用于数据验证和净化，目的是保证插入表中的数据确实是需要的数据

DELETE 触发器

在DELETE 触发器代码内，你可以引用一名为OLD的虚拟表，该被删除的行
OLD中的值都是只读的，不能更新

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_order(order_num, order_date,cust_id)
    value(old.order_num,OLD.order_date,OLD.cust_id);
END;

多语言触发器，触发器使用BIGIN和END语句标记触发器体

UPDATE 触发器

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问旧的数据，引用一个名为NEW虚拟表访问新更新的值
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）
OLD中的值全是只读的，不能更新

CREATE TRIGGER updatetevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_stat=Upper(NEW.vend_state)

事务处理

事务（transaction) 指一组SQL语句

回退（rollback) 指撤销指定SQL语句的过程

提交（commit)指将未存储的SQL语句结果写入数据库表

保留点（savepoint) 指事务处理中设置的临时占位符，你可以对它发布回退

开启事务

START TRANSACTION

回滚事务

ROLLBACK

提交事务

COMMIT

文档信息

本文作者：clsnull
本文链接：https://clsnull.github.io/2021/04/23/MySQL%E7%AC%94%E8%AE%B0/
版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）

clsnull

Mysql笔记