读MySQL必知必会笔记2

如何数据处理函数

文本处理函数：

​ Trim()：去除首位空格函数

​ Upper()：转大写函数 SELECT vend_name,UPPER(vend_name) FROM vendors;

LEFT()	返回串左边的字符
LENGTH()	返回串的长度
LOCATE()	找出串的一个子串所在字符串的位置
LOWER()	将串转为小写
RIGHT()	返回串右边的字符
SUBSTRING()	返回子串的字符
UPPER()	将串转为大写
SOUNDEX()	返回串的SOUNDEX值

SOUNDEX()解释：SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

日期处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7zJaotOw-1621569612574)(E:\积累\MySQL笔记\日期处理函数.PNG)]

数值处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tv0RqI4b-1621569612576)(E:\积累\MySQL笔记\数值处理函数.PNG)]

汇总数据

​ 聚集函数以及如何利用聚集函数去汇总表中的数据

​ 聚集函数：

​ 常用的聚集函数：

AVG（）	计算某一列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的和

例：返回表中所有商品的平均价格

​ SELECT AVG(prod_price) as avg_price FROM products;

当需要计算多个列的平均值就需要多个AVG函数，并且会忽略值为NULL的行（当某一行为NULL时，就直接忽略，也不会加载求平均数的总和里面去）

COUNT()：求行的总数，存在两种情况，当参数为*时，不会去判断NULL的情形

​ 当参数为某一个列时，就会忽略值为NULL的行

MAX()和MIN()函数类似，都会忽略NULL值的行

SUM()求和计算，忽略NULL值的行

**聚集不同值：**ALL和DISTINCT的用法：默认是ALL，使用DISTINCT会去除数值相同的列

​ 例：SELECT AVG(prod_price) as avg_price FROM products；

​ SELECT AVG(DISTINCT prod_price) as avg_price FROM products;

​ 下面求出的平均价格就要比上面高，因为去除了一些较低的相同价格；

​ 使用注意事项：DISTINCT必须使用与列名，不能用于计算或表达式

组合聚合函数：与写需要显示的列名一样，多个聚合函数之间使用逗号隔开，最好取一个便于识别的别名，而且最好别使用存在的列名，不然容易混淆。

分组数据

分组会用到两个新的字句：GROUP BY 和HAVING

​ 创建分组GROUP BY注意事项：

​ GROUP BY 字句可以使用任意数目的列

​ 如果在字句中嵌套了分组，数据将在最后规定的分组上进行汇总

​ GROUP BY字句中的列都必须是检索列或有效表达式（聚集函数不可以），如果SELECT语句中使用了表达式则字句中必须使用表达式，不能用别名

​ 除聚集函数外，SELECT中的每个列，在字句中都必须给出

​ 如果分组具有NULL值，则NULL会作为一个分组，如果有多个NULL，则他们会分为一个组

​ 使用位置，在where之后，order by 之前

​ 例：SELECT vend_id,SUM(prod_price) FROM products GROUP BY vend_id WITH ROLLUP

​ 使用WITH ROLLUP 关键字可以获得每个分组总值

​ 过滤分组：

​ HAVING约等于WHERE ，只是关键字不同，WHERE 过滤行，HAVING过滤分组

​ 例：SELECT cust_id,COUNT() FROM orders GROUP BY cust_id HAVING COUNT()>1;

​ 当然WHERE和HAVING可以同时使用

​ 例如：先过滤掉10月份之前的订单，在选出过滤出订单超过2个的顾客

​ SELECT cust_id,COUNT() FROM orders WHERE order_date >= ‘2020-10-01’ GROUP BY cust_id HAVING COUNT()>1;

​ 分组排序：ORDER BY 和 GROUP BY 的区别

ORDER BY	GROUP BY
排序产生的输出	分组行，可能不是按一定的顺序输出
任意列或非显示的列	只能使用选择列或表达式列，而且必须使用每个选择列和表达式列
不一定需要	如果和聚集函数一起使用列或表达式，则必须使用

例：检索总计订单价格大于等于50的订单的订单号和总计订单价格

SELECT order_num,SUM(quantityitem_price) as tolalPrice FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price) > 50.0 ORDER BY tolalPrice;

子查询

即：查询中嵌套查询

例：SELECT * 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,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) FROM customers;

​ 这里的where条件不太一样，使用到了全限定类名，这种涉及外部查询的子查询也叫做相关子查询

表连接

**外键（foreign key）：**某表中的一列，一另一张表的主键值相关联

**笛卡尔积：**当两个表联结，如果没有指定过滤条件，那么两个表就会进行笛卡尔积，查询结果为第一个表的行数乘以第二个表的行数

**内部连接：**使用INNER JOIN …ON,又称等值连接，因为连接都是基于两个表中相等测试

​ 例：SELECT * FROM orders INNER JOIN orderitems ON orders.order_num=orderitems.order_num;

**多表连接：**SELECT * FROM vendors,products,productnotes WHERE vendors.vend_id=products.vend_id AND products.prod_id=productnotes.prod_id ORDER BY vendors.vend_id;

SELECT * FROM vendors INNER JOIN products INNER JOIN productnotes ON vendors.vend_id=products.vend_id AND products.prod_id=productnotes.prod_id ORDER BY vendors.vend_id;

高级表联结

自联结：

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=‘TNT1’

**自然联结：**每个内部联结都是自然联结，无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

**外部联结：**关键字OUTER JOIN 在使用的时候需要RIGHT和LEFT一起使用，左联结和右联结，则是以左边或右边的表为基准进行联结

组合查询

**关键字：**UNION

可以用于相同表之间，因为不同WEHE字句获得的不同结果相联结，当然也可以用于不同表，但是，他们查询的结果数据类型得类似，就是说MYSQL可以隐式转换的数据类型，在使用的时候UNION会自动消除重复的行，当然如果需要重复的行可以使用UNION ALL。对结果集进行排序，ORDER BY 只能使用与最后一条SELECT语句，意味着ORDER BY只能出现一次，而且必须在最后

全文本搜索

并非所有数据库引擎都支持全文本搜索：两种最常见的搜索引擎MyISAM(支持)和InnoDB（不支持）,

**开启支持全文本索引：**FULLTEXT，在创建表时，可以直接定义，可以是一列，也可以是多列

例：CREATE TABEL 表名(

​ 定义列，

​ FULLTEXT(列名)

)ENGINE=MyISAM;

注意：不要在导入数据的时候开启支持这会使得数据导入非常缓慢，可以先导入数据，在开启支持

进行全文本搜索：

​ 会使用到两个关键词MATCH(),AGAINST(),

​ MATCH中定义需要进行全文本搜索的列

​ AGAINST中定义需要搜索的文本

​ 注意：搜索不区分大小写，区分大小写需要使用BINARY指定和正则表达式时类似

​ SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘RABBIT’)

​ 演示排序如何工作的：SELECT note_text,MATCH(note_text) AGAINST(‘rabbit’) FROM productnotes;

​ 原理：查询的结果如果包含有查询字符串则会返回一个值，如果不包含则返回0，后根据返回值的大小进行降序排序

​ 全文本搜索不仅实现了LIKE的一些简单匹配功能，还实现一下复杂搜索，执行效率上也更快

查询扩展搜索：

​ 解释：MySQL会进行两次全文本搜索，第一次用指定的词去搜索，返回结果，饭后根据返回的结果MySQL去判断那些有用，那些无用，再次根据判断出的词和原来的词进行第二次全文本搜索，这样得出的结果行就增加了。

​ 特点：行越多越好

​ 使用例：在原有的基础上使用了WITH QUERY EXPANSION三个关键字

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘anvils’ WITH QUERY EXPANSION);

布尔文本搜索：

​ 解释：全文本搜索的另一种形式，这种形式可以实现要匹配的词，不匹配的词，排列提示，表达式分组，另外的一些内容，特别注意：即使没有设置FULLTEXT索引也可以使用，但是操作会非常缓慢，随着数据增多，性能就会越低

​ 使用例：关键词 IN BOOLEAN MODE

​ 全文本布尔操作符解释：

+	包含，且必须出现
-	排除，且必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成一个表达式，可以包含或者排除
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语

匹配heavy，排除repo开头的

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘heavy -repo*’ IN BOOLEAN MODE);

注意：排列而不排序，在布尔排序中不按等级值排列而返回的行

1. 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。

2. MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。

3. 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。

4. 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。

5. 忽略词中的单引号。例如，don’t索引为dont。

6. 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。

7. 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

数据插入

关键字：INSERT

1. 数据插入可以插入一行完整的数据

2. 插入一行的部分数据

3. 插入多行

4. 插入某些查询的结果

   插入及系统安全：在MYSQL中可以针对每个表或每个用户利用安全机制禁止使用INSERT语句

   插入单行：

   ​ 基本语法：INSERT INTO 表名(列名,列名,…) VALUES(值1,值2,…);

   当然表名后的列名也可以不写，但是这样并不安全，不推荐

   注意：在实际中，会有多个用户在操作，这时如果检索的重要性比较高则可以降低插入的优先级，当然也适用与update和delete

   INSERT LOW PRIORITY INTO

   插入多行：

   ​ 可以使用多个INSERT语句一次提交，也可以如下使用（前提他们所用的列一样）

   ​ INSERT INTO 表名(列名,列名,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);

   ​ 这样使用可以提高性能：因为MySQL执行一个INSERT语句比执行多个INSERT语句快

   ​ 例：INSERT INTO customers( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES (‘Coyote Inc.2’, ‘200 Maple Lane2’, ‘Detroit2’, ‘MI’, ‘444442’, ‘USA’, ‘Y Lee2’, ‘ylee@coyote2.com’),
   (‘Coyote Inc.3’, ‘200 Maple Lane3’, ‘Detroit3’, ‘MI’, ‘444443’, ‘USA’, ‘Y Lee3’, ‘ylee@coyote3.com’);

   插入检索出的数据：

   利用到INSERT SELECT语句

   例：INSERT INTO customers(cust_id,cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
   SELECT cust_id,cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email FROM newcustomers;

   当然在查询语句中可以使用where等语句进行过滤数据，在插入时需要注意不能是重复的主键值，或者如果表支持主键的自增长，就不可以不插入主键列

   例：INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
   SELECT cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email FROM newcustomers;

   更新和删除数据

   更新：关键字UPDATE

   ​ 基本语法：UPDATE 表名 SET 列名=新值 WHERE 条件表达式

   ​ 在使用过程中确保不要忘了WHERE 字句，否则会更新所有行，这并不是我们所希望的

   ​ 例：UPDATE customers SET cust_name=‘tom’ WHERE cust_id=10010;

   ​ 更新中使用子查询：

   ​ 例：UPDATE customers SET cust_name=(SELECT cust_name FROM newcustomers WHERE cust_id=10010) WHERE cust_id=10010;

   ​ 在更新多行时，如果发生错误，那么之前更新的行也会恢复为原来的数据，此时，如果希望继续更新，则可以使用IGNORE，例：UPDATE IGNORE 表名…

   **删除：**关键字DELETE

   ​ 基本语法：DELETE FROM 表名 WHERE 条件

   ​ 例：DELETE FROM customers WHERE cust_id=10010;

   同样MySQL在安全控制里面可以去控制那些用户和那些表可以执行DELETE语句

   注意：DELETE删除的是表的内容而不是表

   要想更快的删除所有行可以使用TRUNCATE TABLE，因为TRUNCATE实际是将原来的表删除后新建的一张表，而不是逐行删除表的数据

   **小心使用：**MySQL中没有撤销（undo）操作，所以删除了就删除了

   创建和操纵表

   关键字：CREATE

   基本语法：CREATE 表名(

   ​ 列名 数据类型 其他约束

   ​ …

   )

   #例：IF NOT EXISTS 可以在创建表时先判断库中是否有此表，表名一样，AUTO_INCREMENT自增长的意思,ENGINE=Innodb数据库引擎
   CREATE TABLE IF NOT EXISTS student (
   	stu_id INT NOT NULL AUTO_INCREMENT ,
   	stu_name VARCHAR(100) NOT NULL ,
   	stu_age VARCHAR(10) NULL ,
   	PRIMARY KEY (stu_id)
   )ENGINE=Innodb;
   

   NULL值和空串的区别：

   NULL值是没有值，而不是说‘’两个单引号引上的空字符串

   确定AUTO_INCREMENT的值：

   ​ SELECT last_insert_id()此语句会返回最后一个AUTO_INCREMENT

   **指定默认值：**关键字DEFAULT

   ​

   CREATE TABLE IF NOT EXISTS student (
   	stu_id INT NOT NULL AUTO_INCREMENT ,
   	stu_name VARCHAR(100) NOT NULL ,
   	stu_age VARCHAR(10) NULL DEFAULT 1 ,
   	PRIMARY KEY (stu_id)
   )ENGINE=Innodb;
   

   这里的默认值在MySQL中只能是常量，不过其他数据库好像可以支持函数

   引擎类型：

   ​ MySQL中需要知道的引擎类型Innodb,MEMORY,MyISAM

   他们的区别：

   Innodb	一个可靠的事务处理引擎，不支持全文本搜索
   MEMORY	功能等同于MyISAM,但是数据都是存储在内存中，速度快，适用于临时表
   MyISAM	不支持事务，支持全文本搜索，性能极高

   更新表：

   ​ 基本语法：ALTER TABLE 表名 …

   #给表添加一列
   ALTER TABLE student
   ADD stu_birthday DATE;
   #给表删除一列
   ALTER TABLE student
   DROP COLUMN stu_birthday;
   

   ​ ALTER TABLE 一种常见的用途就是用来创建外键

   ALTER TABLE 表名
   ADD CONSTRAINT 外键名
   FOREIGN KEY (指定列) REFERENCES 另一个表名(列名)
   

   在实际开发中要小心使用ALTER TABLE 语句

   删除表：

   ​ 基本语法：DROP TABLE 表名，删除表而不是只删除表的内容

   重命名表：

   ​ 基本语法：RENAME TABLE 旧表名 TO 新表名

   视图

   ​