一定有你不知道的MySQL易错点-基础篇-必知必会-CSDN博客

本文链接：https://blog.csdn.net/weixin_40539952/article/details/106373540

MySQL易错点总结-基础篇-必知必会

数据库和数据库管理系统的概念

答：数据库是保存有组织的数据的容器，DBMS是我们通过其对数据库进行操作的软件。
什么是模式

答：模式是关于数据库和表的布局及特性的信息。
SQL语句不区分大小写，但表名，列名有可能区分（mysql版本，5版本以后不区分）。同时，MySQL在执行匹配时默认不区分大小写，所以在select fuses中，fuses与Fuses同样匹配。
不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。
检索出来的第一行为行0而不是行1。
关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
ORDER BY用非检索的列排序数据是完全合法的。如果不指定DESC，则该命令默认按照升序排列。
对文本数据进行排序时，A与a是否相同取决于数据库如何设置，在dictionary排序中，A与a被视为相同。
NULL ，无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。可以使用圆括号消除歧义。
IN操作符一般比OR操作符清单执行更快。
根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，like 'jet%‘与JetPack 1000将不匹配。注意’%'不能匹配NULL。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。即REGEXP是包含即可。
MySQL中的正则表达式匹配（自版本 3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’。
除非把字符|括在一个集合中，否则它将应用于整个串。
^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。例如 [^0-9]和正则中使用。
可以在不使用数据库表的情况下用 SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。下个语句返回0。select ‘hello’ regexp ‘[0-9]’;。
如何测试计算，SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2;将返回6，SELECT Trim(‘abc’);将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。
多数DBMS使用+或||来实现拼接， MySQL则使用Concat()函数来实现。当把SQL语句转换成 MySQL语句时一定要把这个区别铭记在心。
WHERE子句使用Soundex()函数来转换cust_ contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和 Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。
```
select cust_name ,cust_contact
from customers
where soundex(cust_name) = soundex('Y Lie')
```
不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性。
如果要的是日期，请使用Date() 如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。
```
select cust_id,order_num
from orders
where Date(order_date) ='2005-09-01'
```
聚集函数AVG()忽略列值为NULL的行。 COUNT()函数有两种使用方式。
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。使用COUNT(column)对特定列中具有值的行进行计数，忽略 NULL值。MAX()函数忽略列值为NULL的行。对于文本数据MAX()返回最后一行。
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT 不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
```
select AVG(DISTINCT pro_price) AS avg_price
from products
where vend_id = 100
```
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。 HAVING中聚集函数必须指出，不要使用别名。

SQL执行顺序

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组聚集计算时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

任何时候只要列名可能有多义性，就必须使用相关子查询（表名和列名由一个句点分隔）而非直接使用列名。
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
在使用INNER JOIN时，联结条件用特定的ON子句而不是WHERE 子句给出。传递给ON的实际条件与传递给WHERE的相同。 SQL规范首选INNER JOIN而不是简单的JOIN。
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。（需要指定不同的表别名）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。且列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
不要在导入数据时使用FULLTEXT 。FULLTEXT在创建表使用或者导入完成后对表进行修改。
文本中词靠前的行的等级值比词靠后的行的等级值高。而且全文搜索比LIKE快。例如下面返回的rabbit如果出现在第二个词比出现在第20个词的排序高，先返回。
```
select note_text
from productnotes
where Match(note_text) Against('rabbit')
```
一般不要使用没有明确给出列的列表的 INSERT语句。插入数据最好指定列名。
如果数据检索是最重要的（通常是这样），则你可以通过在 INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级。且同样适用于UPDATE和DELETE操作。
```
insert low_priority into
```
如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。即使是发错误，也继续进行更新，可使IGNORE关键字.
```
UPDATE IGNORE customers
SET customers.email=1000@163.com
where customers.id = 10001
```
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快。

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS

create table vendors if not exists(
    vend_id int not null auto_increment,
    vend_name char null,
    quantity int not null default 1
    primary key(vend_id)
)engine=InnoDB;

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。SELECT last_insert_id()函数可以获得最后一个auto_increment的值
不同引擎有不同的特性。
- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
注意：不用引擎的表不能够互相引用。
ALTER TBALE一般用于定义外键，因为表一旦固定最好不要更替。ALTER语句DROP掉一列后不可撤销。
```
alter table orderitems
add constraint fk_order_orders
foreign key (order_num) references orders (order_num);
```
为什么要使用视图？（因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。所以有时候性能会下降。）（如果你对视图增加或删除行，实际上是对其基表增加或删除行。）
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
```
create view orderview as
select order_num
from orderitems;
```
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
```
create view orderview as
select order_num
from orderitems;
```