学习mysql（教科书：书籍《mysql必知必会》）

jasminegegege

已于 2023-12-05 10:21:14 修改

阅读量543

点赞数 5

文章标签： mysql 学习数据库

于 2023-02-27 18:24:44 首次发布

本文链接：https://blog.csdn.net/jasminegegege/article/details/129048513

版权

一、检索
二、排序
三、过滤
- 1.distinct 函数
四、创建字段
- 1. 合并列 select concat()函数
五、函数
- 1. 文本处理函数
- 2. 时间处理函数
- 3. 数值处理函数
六、汇总数据
- 1.聚集函数
- 2.聚集不同值
- 3.组合聚集函数
七、分组数据
- 1. 分组最常用的两个：COUNT(),GROUP BY
- 2. 分组后进行过滤
- 3. 与order by语句的搭配
- 4. SELECT子句顺序
七、子查询
- 1. 相关子查询（correlated subquery）: 跨数据集的子查询
- 2.子查询的嵌套（与后期所学的多表联结功能相同）
八、联结
- 1.创建联结
- 2.内部联结
- 3.多表联结
九、创建高级联结
- 1.使用表别名
- 2.使用不同类型的联结
- 3.使用带聚集函数的联结
十、组合查询
- 1.使用UNION将两个SELECT语句联合起来
十一、文本搜索
- 1.全文本搜索
- 2.正则表达式搜索(REGEXP)
- 3. rank
- 4.搜索扩展
- 5.布尔文本搜索
十二、插入数据
- 1.插入完整的行
- 2.插入多个行
- 3.插入检索出的数据
十三、更新和删除数据
- 1.更新数据
- 2.删除数据
- 3.注意
十四、创建表
- 1.创建表
- 2.更新表
- 3.删除表
- 3.重命名表
十五、视图
- 1.何为视图
- 2.使用视图
- 3.更新
十六、存储过程
- 1. 使用存储过程
补充

一、检索

二、排序

三、过滤

1.distinct 函数

distinct(col_a) 取col_a 的unique，保留null
distinct(col_a),b 取(col_a,b)集合的unique

四、创建字段

1. 合并列 select concat()函数

SELECT Concat(col_1, '符号', RTrim(col_2), '符号') AS col_new
FROM data
ORDER BY col_3;

Concat函数中，列名与符号之间用逗号隔开，符号需用引号扩起
LTrim()函数，去掉列值的左括号，RTrim()，去掉列值的右括号。Trim()函数，去掉串左右两边的空格。
AS 定义新列名或新值。AS可与SELECT 单独搭配使用

五、函数

1. 文本处理函数

函数名	功能
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Upper()	将文本转换为大写
Lower()	将文本转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

#Soundex()函数的应用
SELECT col_1,col_2
FROM data
WHERE Soundex(col_2) = Soundex('XXX')

2. 时间处理函数

函数名	函数功能
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分（可能列值存在时分秒）
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

SELECT first_name,last_name
FROM customers
WHERE date(birth_date) >= '1986-01-01'
Order BY first_name;

3. 数值处理函数

函数名	函数功能
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

六、汇总数据

1.聚集函数

聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数。

函数	函数功能
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

（1）AVG()函数

（1）忽略列值为NULL的行
（2）只用于单列

（2）COUNT()函数

（1）COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
（2）使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
（3）count(distinct a,b) 取(a,b)组合的唯一情况计数

（3）MIN() MAX()函数

（1）对非数值数据：包括返回文本列中的最值。在用于文本数据时，如果数据按相应的列排序，则返回最前面的行。
（2）忽略列值为NULL的行

（4）SUM()函数

（1）在多个列上进行计算：利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

SELECT SUM(col_1*col_2) AS XXX
FROM data

（2）忽略列值为NULL的行

2.聚集不同值

DISTINCT 参数

SELECT SUM(DISTINCT col_1) AS XXX
FROM data

3.组合聚集函数

SELECT COUNT(*)As num_items,
	MIN(prod_price) As price_min，
	MAX(prod_price) AS price_max,
	AVG(prod_price) As price_avg
FROM products;

七、分组数据

1. 分组最常用的两个：COUNT(),GROUP BY

SELECT id,COUNT(*) AS num_prods
FROM products
GROUP BY id

*对应SELECT前面的列

2. 分组后进行过滤

与having语句的搭配

SELECT id,COUNT(*) AS num_prods
FROM products
GROUP BY id
HAVING COUNT(*) >=2

可以引用列别名

3. 与order by语句的搭配

4. SELECT子句顺序

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

七、子查询

1. 相关子查询（correlated subquery）: 跨数据集的子查询

WHERE order.id = cust.id

2.子查询的嵌套（与后期所学的多表联结功能相同）

SELECT cust_name，cust_contactFROMcustomers
wHERE cust_id IN (SELECT cust_id
					FROM orders
					WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id = 'TNT2 '));

八、联结

个人理解是跨数据集的联系与比较

1.创建联结

“WHERE =” 等值联结

SELECT col_1,col_2
FROM data_1,data_2
WHERE data_1.col_3 = data_2.col_4
ORDER BY col_1

WHERE语句将data_1与data_2联结

若没有联结，用SELECT语句从两个数据集匹配，会得到笛卡尔积。即行数会是第一个行的数目乘以第二个行的数目。

2.内部联结

区别于等值联结用 WHERE = 语句，内部联结用 FROM INNER JOIN ON = 语句，返回与等值联结相同

SELECT col_1,col_2
FROM data_1 
INNER JOIN data_2
ON data_1.col_3 = data_2.col_4
ORDER BY col_1

如果存在两个表存在同列名的情况，最好用data.col区分

3.多表联结

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;

九、创建高级联结

1.使用表别名

SELECT d1.col_1,d2.col_2
FROM data_1 AS d1,data_2 AS d2
WHERE d1.col_3 = d2.col_4;

如上述代码所示，表别名可以用于WHERE语句，也可以用于SELECT列表以及ORDER BY 语句。

2.使用不同类型的联结

（1）自联结

SELECT d1.col_1,d1.col_2
FROM dataset AS d1, dataset AS d2
WHERE d1.col_3 = d2.col_4
  AND d2.col_4 = 'XXX'

对data集取两个别名，以此实现WHERE语句的表内子查询

（2）自然联结

自然联结排除多次出现，使每个列只返回一次。

SELECT c.*,o.order_num，o.order_date,
       oi.prod_id，oi.quantity，o1.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';

（3）外部联结

OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。使用LEFT OUTER JOIN从FROM子句的左边表中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN
left join on里面相当于对右表进行过滤后再链接，右表没有的字段筛选无法生效。即无法在on语句单独对左集进行筛选。

SELECT data1.cola,data2.colb
FROM data1 LEFT OUTER JOIN data2
ON data1.colc = data2.cold;

3.使用带聚集函数的联结

SELECT data1.cola,
       data2.colb,
       count(data2.cole) AS 2e
FROM data1 LEFT OUTER JOIN data2
ON data1.colc = data2.cold;

十、组合查询

1.使用UNION将两个SELECT语句联合起来

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

这种组合会将重复行只展示一次。若要重复展示要用到UNION ALL

十一、文本搜索

1.全文本搜索

SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('xxx') #xxx是要搜索的内容
#等同于 WHERE TXTCOL LIKE '%xxx%'

WHERE Match(列名）Against(‘搜索文本内容’)
全文本搜索不区分大小写
类似于like操作

2.正则表达式搜索(REGEXP)

（1）基本字符匹配

SELECT *
FROM data_1
WHERE col_1 RGERXP '100|200'

（2）or匹配

SELECT *
FROM data_1
WHERE col_1 RGERXP '[123] ton'

SELECT *
FROM data_1
WHERE col_1 RGERXP '1|2|3 ton'

SELECT *
FROM data_1
WHERE col_1 RGERXP '[1-3] ton'

（3）特殊字符匹配

SELECT *
FROM data_1
WHERE col_1 RGERXP '\\.'

元字符	说明
\\f	换页
\\n	换行
\r	回车
\\t	制表
\\v	纵向制表

特殊地，如果要匹配’\'，需要用‘\\\’

（4）匹配字符类

3. rank

SELECT TXTCOL,Match(TXTCOL) Against('xxx') AS rank
FROM data_1

SELECT Match(TXTCOL) Against(‘xxx’) AS rank。意为对Match的列的所有行，进行文本搜索，返回一个rank。rank越高意为着搜索的文本内容在每一行出现的位置越靠前。当然，如果该行并没有出现搜索的文本内容，那么rank值会是0。

4.搜索扩展

SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('xxx' WITH QUERY EXPANSION) #xxx是要搜索的内容,WITH QUERY EXPANSION 扩展查询

区别于普通的文本搜索，文本扩展搜索，WITH QUERY EXPANSION允许在搜索完指定文本内容后，将该内容存在的行中内容，作为新的文本搜索内容，再次搜索。

5.布尔文本搜索

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('AAA - BBB*' IN BOOLEAN MODE) #包含AAA,但不包含BBB以及以BBB开头的词是要搜索的内容,IN BOOLEAN MODE指示以布尔文本进行搜索

在这里插入图片描述
注意：
1.如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）
2.忽略词中的单引号。例如，don’t索引为dont。

十二、插入数据

1.插入完整的行

INSERT INTO data_1
VALUES(NULL,
       value_1
       ...)

插入新的一行，采用这种简洁的写法时应注意顺序以及个数问题。如果某列为空值，就设为NULL。

INSERT INTO data_1(col_2,
                   col_3,
                   ...)
VALUES(value_1,
       value_2
       ...)

采用这种采繁琐的写法时，注意列名与数值的对应。

2.插入多个行

如果要插入的列名相同，可以采取简洁写法。否者，要重复写入insert语句
简洁形：

INSERT INTO data_1(col_2,
                   col_3,
                   ...)
VALUES(
       value_1,
       value_2
       ...),
       (
       value_1,
       value_2
       ...);

复杂形：

INSERT INTO data_1(col_2,
                   col_3,
                   ...)
VALUES(value_1,
       value_2
       ...);
INSERT INTO data_1(col_2,
                   col_3,
                   ...)
VALUES(value_1,
       value_2
       ...);

3.插入检索出的数据

INSERT INTO data_1(col_2,
                   col_3,
                   ...)
SELECT col_4,col_5,...,
FROM data_2

检索data_2的特定列导入data_1中

十三、更新和删除数据

1.更新数据

可以理解为添加某个格的数据
用set语句可以一次性添加多条信息
where 索引

UPDATE data_1
SET col_2 = 'xxx'
    col_3 = 123
    col_4 = NULL
WHERE col_1 = 'yyy'

对col_1=yyy的行的col_2,col_3添加信息。其中col_4列值为空

2.删除数据

用DELETE FROM语句

DELETE FROM data_1
WHERE col_1= 'yyy'

从data_1删除col_1=yyy的行

如果要删除整个数据集，应该用TRUNCATE TABLE语句

3.注意

1.如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不WHERE子句，表的所有数据都将被删除。
2.MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

十四、创建表

1.创建表

CREATE TABLE data_1
(
	col_1	int	        NOT NULL AUTO_INCREMENT,
    col_2	int   	    NOT NULL DEFAULT 1,
    col_3	char(10)	NULL,
    PRIMARY KEY (col_1,col_2)
) ENGINE = InnoDB

设定	解释
data_1	表名
col_1	列名
int、char()	数据类型。char(10),10为数据长度
NULL	定义数据值可以为空
NOT NULL	不能为空
NOT NULL AUTO_INCREMENT	本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。
PRIMARY KEY()	定义某列为主键，可以多列
DEFAULT 1	定义当插入值为空时，默认插入1
ENGINE	引擎。InnoDB,事务处理引擎，不支持全文本搜索。MEMORY,速度很快（特别适合于临时表）。MyISAM，支持全文本搜索，但不支持事务处理

2.更新表

（1）插入列

插入：ADD 列名数据类型

ALTER TABLE data_1
ADD col_4 char(20);

（2）删除列

删除：DROP COLUMN 列名

ALTER TABLE data_1
DROP COLUMN col_4;

（3）定义外键：

这部分不理解，暂时放个图
在这里插入图片描述

3.删除表

DROP TABLE data_1;

3.重命名表

RENAME TABLE data_1 TO data_rename1,
             data_2 TO data_rename2，
             data_3 TO data_rename3;

十五、视图

1.何为视图

在这里插入图片描述

这就是视图的作用。productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。

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

2.使用视图

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

（1）利用视图简化复杂的联结

上面的SELECT语句可以简化为，利用等值联结

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;

创建了一个视图productcustomers，在视图中用WHERE 语句

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_od = 'TNT2'

（2）concat函数与视图

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
       AS vend_title
FROM vendors
ORDER BY vend_name

将列concat后成为视图

（3）WHERE函数与视图

CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email is NOT NULL

用where语句筛选后成为视图

（4）计算字段与视图

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

3.更新

一般，应该将视图用于检索（SELECT语句），而不用于更新（INSERT、UPDATE和DELETE）

十六、存储过程

存储过程定义：存储特定语句以供批量使用

1. 使用存储过程

（1）创建

delimiter $$
CREATE PROCEDURE productpricing()
BEGIN 
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END $$

（2）执行

delimiter $$
CALL productpricing();

（3）删除

DROP PROCEDURE productpricing();

（4）插入变量

delimiter $$
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 $$

使用

CALL productpricing(@pricelow,@pricehigh,@priceaverage);
SELECT @pricelow, @pricehigh, @priceaverage;

补充

update与replace的联合使用，检索后替换

UPDATE data_1
SET REPLACE (col_1,'xxx','yyy')
WHERE col_1 LIKE '%xxx%'

UPDATE data_1
SET col_1 = 'yyy'
WHERE col_1 = 'xxx'