MySQL必知必会3

最新推荐文章于 2023-03-09 09:43:46 发布

奋斗的龙猫

最新推荐文章于 2023-03-09 09:43:46 发布

阅读量1.9k

点赞数 1

分类专栏： mysql学习文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_46544385/article/details/120402664

版权

mysql学习专栏收录该内容

13 篇文章 10 订阅

订阅专栏

文章目录

组合查询
全文搜索
插入数据
更新和删除数据
创建和操作表

组合查询

多数的SQL查询都只包含一个或多个表中的返回数据的单条SELECT语句。MySQL也允许多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为“并union或者复合查询”。
有两种基本情况，其中需要使用组合查询：

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

其中在组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单挑查询完成的工作相同。

利用UNION操作符来组合数条SQL查询。利用UNION，可以给多条SELECT语句，将他们的结果组合程单个结果集。

在这里插入图片描述
通过上面我们可以知道了，对相同的表的多条SELECT语句，可以使用WHERE进行获取，也可以利用UNION来得到。但是如果需要对不相同的表时，只能用UNION来获取。

并且在上面的例子中，我们也将看到这样的现象：
在这里插入图片描述
表明了使用UNION的时候，他会自动删除重复的行，但是我们需要将所有的行都输出，也即需要包括重复地行怎么办呢？这时候我们使用的是UNION ALL操作符即可。

但是如果我们检索的两个SELECT的列不同，又会怎样呢？此时就会发生报错，提示含有不同的列数。
在这里插入图片描述
基于上面的示范，我们来讲解UNION的规则：

UNION必须由两条或两条一行的SELECT语句组成，语句之间用关键字UNION分隔。（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）
UNION中的每个查询必须包含相同的列数。在上面的例子中就可以体现出来，如果每个查询的列数不相同，那么就会发生报错。但是书上对于这一条的描述是这样的:

但是下面的例子中确实不同的聚集函数也可以实现：

所以我个人认为，这一条应该保证每个SELECT都有相同的列数即可，因为虽然聚集表达式不同，但是最后由于列的数据类型可以兼容，也即隐含的进行类型转换，所以虽然对应的列的类型不同，聚集函数不同，但也可以正常运行。但也只是我的个人意见，如果有大佬有不同意见，请指教哈。
列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含的转换的类型。

在进行UNION之后，输出的结果没有顺序的，那么我们可以利用ORDER BY进行排序。然而，利用ORDER BY这个子句时，需要注意的一点是，在用UNION组合查询的时候，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后，表示对UNION之后的形成的整体进行排序，因此并不存在用一种方式排序一部分，而又用另一种方式排序另一种部分的情况。所以在UNION子句中不允许使用多条ORDER BY 子句。
在这里插入图片描述
所以利用ORDER BY的时候，必须保证跟在ORDER BY后面的列是UNION之后的列名,也就是第一个SELECT后面的列名，否则就会出现上面的错误情况.

全文搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当的设计后，MySQL会自动进行所有的索引和重新索引。
在索引后，SELECT可与Match()Against()一起使用以实际执行搜索。
其中，一般在创建表的时候启用全文本搜索，CREATE TABLE语句接受FULLTEXT子句，他给出被索引列的一个逗号分隔的列表。
在这里插入图片描述

如果我们需要使用查询扩展，那么只需要诸如Agains(‘xxx’ WITH QUERY EXPANSION)的形式，即在Against里面添加WITH QUERY EXPANSION即可。

MySQL支持全文本搜索的另一种方式，称为布尔方式，也就是在Agains()中的字符串后面使用IN BOOLEAN MODE即可。例如:
在这里插入图片描述
此时我们来讲解一下全文本布尔操作符：

插入数据

利用SQL的INSERT语句将数据插入表中。
INSERT是用来插入（或添加）行到数据库表的。插入可以使用几种方式使用：

插入完整的行
对应的语法有：
①INSERT INTO table_name VALUES (xxx1,xxx2,xxx3,xxx4,…xxxn); 通过这样的语句，从而实现插入完整的行，但是存在着弊端，就是需要保证VALUES括号里面的值需要和table_name的列一样多，并且数据类型要和table_name的列的类型是一一对应的。
```
 ②**INSERT INTO table_name(xxx1,xxx2,xxx3.....xxxn) VALUES (yyy1,yyy2,yyy3......yyyn)；**需要注意的是VALUES括号里面的参数需要和table_name后面的参数的数据类型一一对应的，即yyy1的类型需要和xxx1相同，这样才会将yyy1的值赋给xxx1，其他同理。
```

在这里插入图片描述

因此考虑到两者语法的安全性问题，一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化,即插入整行的时候建议使用第二种语法.
而在上面第二种语法的例子种，我们发现了值给出了3列的赋值，剩余的自动添加了NULL，这是因为我们在创建表的时候指定这一列的默认值为NULL了，所以没有对这个列赋值的时候，对应的值为NULL，但是需要一定的条件才可以实现的，否则就会出现错误。
在这里插入图片描述

插入行的一部分
就是上面提到的第二种语法格式.
插入多行
如果需要插入多行，我们虽然可以写多个INSERT INTO table_name,但是这样会耗费大量的时间，所以就有了下面的语法格式:
**INSERT INTO table_name(xxx1,xxx2,xxx3,…xxxn) VALUES (yyy1,yyy2,yyy3…yyyn),(zzz1,zzz2,zzz3,…zzzn);**所以只需要在VALUES后面写多个行即可，每个新行用逗号分隔即可。
插入某些查询的结果
因为插入一些查询的结果，所以是将VALUES改成了SELECT，对应的语法为:
INSERT INTO table_name(xxx1,xxx2,xxx3…xxxn)
SELECT yyy1,yyy2,yyy3…yyyn FROM table_name2 (WHERE);
其中这里同样需要保证yyy1和xxx1的类型一样，从而可以将yyy1的值赋值给xxx1，其他的同理，因此需要保证table_name后面的参数个数、类型和SELECT后面的参数个数、类型是一一对应的。如果没有WHERE，那么会将table_name2中的全部数据插入到table_name这个表中，如果利用WHERE进行过滤时，那么就会将table_name2中的符合WHERE子句的数据插入到table_name中。

更新和删除数据

为了更新修改表的数据，使用的是UPDATE语句，对应的语法格式为:UPDATE table_name SET xxx = newValue (WHERE);
这里通过使用WHERE，从而使得更新修改的是某一行的，而不是整个表的所有行，否则，如果没有WHERE进行判断，那么修改的就是所有行。
在这里插入图片描述
如果需要修改某一行的多个列，那么只需要写一个SET，并且在SET的后面写多个列，每列需要用逗号分开，最后一列不需要.

而进行删除的时候，对应的语法更加简单了，只需要使用DELETE FROM table_name WHERE xxx，即可实现删除某特定的行。
在这里插入图片描述

而在上面说到了，如果要删除某一列的值，需要通过UPDATE将其设置设置为NULL即可，但是没有删除这一列，那么应该怎样写呢？这时候就是修改表了，对应语法是下一讲提到的ALTER TABLE table_name DROP column，从而删除了table_name中column这一列。所以说，DELETE删除的是表中的内容，即表格中的行，并不是表格，如果要修改表格，则需要利用ALTER。
在这里插入图片描述

将某一列的值删除，通过UPDATE将其值设置为NULL：
UPDATE table_name SET column = NULL WHERE xxxx; 从而将table_name中对应的行的column的值删除
将某一列删除，这时候已经涉及到了表的修改，所以需要使用ALTER TABLE,语法格式为:
ALTER TABLE table_name DROP column; 从而将column从table_name这个表中删除

如果要删除全部的行，那么不需要写WHERE即可。但是通过DELETE FROM table_name来删除所有的行执行效率较低，所以出现了TRUNCATE table_name来删除table_name这张表的所有行，因为TRUNCATE table_name是将这个表删除，然后直接新建一个同名的空表，而DELET FROM table_name则是通过逐行删除来实现的，所以删除所有行的时候TRUNCATE table_name效率会高些。
在这里插入图片描述

因为UPDATE和DELETE中都具有WHERE子句，这样做的理由很充分，如果省略了WHERE子句，那么UPDATE或者DELETE将应用到表的所有行中，后果不堪设想。
在这里插入图片描述

所以这里需要提一下更新和删除的指导原则：

除非确实打算更新和删除所有行，否则绝对不要使用不带WHERE的UPDATE或者DELETE语句。
保证每个表都有主键（表的唯一标识），尽可能在WHERE中使用主键，从而可以明确得到要删除或者更新的那一行
在对UPDATE或者DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证他过滤的是正确的记录，以防编写的WHERE子句不正确。
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

进行UPDATE或者DELETE操作的时候，却会发生错误:You can’t specify target table ‘employee3’ for update in FROM clause,例如下面的例子：

# 目的：根据dept_id进行分组，保留每个分组中salary最小的数据，其余数据需要删除
DELETE FROM employee3 
WHERE salary NOT IN (SELECT MIN(salary) FROM employee3 GROUP BY dept_id);

# 目的：将salary最小的人的名字修改为"小吴"
UPDATE employee3 SET name = "小吴" WHERE salary = (SELECT MIN(salary) FROM employee3);

看上去上面的语句并没有什么异常，只要salary不在我们检索的MIN(salary)范围中就将其删除，思路的却是正确，但是为什么会发生报错，提示You can’t specify target table ‘employee3’ for update in FROM clause呢？这段提示的中文意思是：无法在FROM子句中给update指定目标表Employee3.从而表明了不可以在一个表中检索到的数据用于同一个表的UPDATE或者DELETE，即类似上面的employee3表，不可以在employee3中检索到的数据，用于employee3的UPDATE或者DELETE。所以解决办法是利用一个中间表，使得WHERE子句中的SELECT语句成为一个中间表，即：

# 目的：根据dept_id进行分组，保留每个分组中salary最小的数据，其余数据需要删除
DELETE FROM employee3
# (SELECT MIN(salary) FROM employee3) AS a构建中间表，里面的数据就是每个分组的最小数据，当salary不在这些数据中就进行
# 删除操作，从而实现只保留每个分组中salary最小的数据，其余数据都删除的目的
WHERE salary NOT IN (SELECT * FROM (SELECT MIN(salary) FROM employee3) AS a);
                     
# 目的：将salary最小的人的名字修改为"小吴"
UPDATE employee3 SET name = "小吴" 
# 这时候a这个中间表中的数据就是MIN(salary),然后再次检索，就可以得到MIN(salary)了.    
WHERE salary = (SELECT * FROM (SELECT MIN(salary) FROM employee3) AS a);

例如这一道题：删除emp_no重复的记录，只保留最小的id对应的记录，就是上面所说的，如果一不小心，就会用同一个表中检索到的数据用于同一个表的UPDATE/DELETE操作，然后就会发生报错。

创建和操作表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表的创建和处理。

创建表的基本语法：
CREATE TABLE  (IF NOT EXISTS) table_name(
    列名1 数据类型,
    列名2 数据类型,
    .
    .
    . 
    PRIMARY KEY(xxx1,xxx2) -- 说明主键是xxx1,xxx2这些列组合的值，必须保证他们组合的值是唯一的才可以作为主键
    ); 
创建表的时候，table_name后面的括号中每一行需要用逗号分开，最后一行不需要，然后右圆括号记得用分号结束。

利用IF NOT EXISTS来确保创建的新表并没有存在于数据库中，如果存在与数据库中，如果创建表的时候，没有IF NOT EXISTS，就会报错，而有IF NOT EXISTS，那么只有新表没有存在于数据库中才会创立，否则不会

在这里插入图片描述

在上面创建新表的例子中，我们可以发现在表列的数据类型后面存在着NOT NULL,DEFALUT之类的词语，这些是用来干啥的咧？

NOT NULL，用来表示当前这列不可以为空，所以插入的时候，如果没有默认值，那么这一列是不可以省略的,如果省略了就会发生报错，而如果有默认值，那么虽然插入的时候省略了，但是它的值就是默认值了。例如下面例子中的age和score.
NULL，和NOT NULL刚好相反，表示这一列为空，也就是没有值，他和空串' '还是有去别的，空串' '就是一个值，而NULL就是没有值。
DEFAULT:用来指定默认值的，在插入的时候省略这一列，那么对应的值就是它的默认值。
AUTO_INCREMENT: 用来实现自增的。每个表只允许一个AUTO_INCREMENT列，而且它必须被索引，（如，通过使它成为主键）。而它实现自增，主要时通过查询上一次插入的last_insert_id()函数获得这个值，从而返回最后一个AUTO_INCREMENT值，然后将这个值用于后续的MySQL语句中。
PRIMARY KEY:表示主键,主键是表的唯一标识，所以单个列作为主键的时候，对应的语法为PRIMARY KEY(XXX1),此时xxx1这一列的值必须是唯一的，否则如果多列作为主键的时候，那么对应的语法为PRIMARY KEY(XXX1,XXX2,…XXXN),此时需要确保这些列的组合的值必须唯一。

更新表的时候则需要使用ALTER TABLE。这时候我们看一下就可以通过ALTER TABLE进行删除表的某一列、增加列或者其他操作。
ALTER TABLE table_name ADD column yyy; – 向table_name这张表添加数据类型为yyy的列名为column列。
ALTER TABLE table_name DROP (column) value; – 删除table_name这张表中列名为value的列

在这里插入图片描述

但是倘若我需要添加或者删除多行，难道每次都需要写ALTER TABLE 吗？不是的，我们可以单单写一个ALTER TABLE即可，对应形式可以看下面的例子：
在这里插入图片描述
我们将利用RENAME TABLE oldValue TO newValue来对oldValue这张表进行重命名，尽管语法简单，但是还是有一些问题需要注意的，就是我们都知道MySQL是不区分大小写的，所以必须保证newValue的大写或者小写形式并不存在于数据库中的，否则，如果newValue的大写形式或者小写形式已经存在于数据库中，那么就会发生报错。
在这里插入图片描述
那么我们应该怎样实现删除表呢？利用DROP TABLE IF EXISTS table_name;就可以实现删除table_name这张表了。

奋斗的龙猫

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL必知必会3

文章目录组合查询组合查询多数的SQL查询都只包含一个或多个表中的返回数据的单条SELECT语句。MySQL也允许多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为“并union或者复合查询”。有两种基本情况，其中需要使用组合查询：在单个查询中从不同的表中返回类似结果的数据；对单个表执行多个查询，按单个查询返回数据。其中在组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单挑查询完成的工作相同。利用UNION操作符来组合数条SQL查询。利用UNI
复制链接

扫一扫

专栏目录