MySQL基础笔记(4) - 联合查询&全文本搜索&列表的操作

联合查询&全文本搜索&列/表的操作

目录

• 联合查询&全文本搜索&列/表的操作
  • • 1. 联合查询
    • 2. 全文本搜索
      • 2.1 布尔文本搜索
      • 2.2 注意事项
    • 3. 插入语句
    • 4. 更新语句
    • 5. 删除语句
      • 5.1 DELETE关键字
      • 5.2 TRUNCATE关键字
      • 5.3 注意事项
    • 6. 创建和操作表
      • 6.1 库的管理
      • 6.2 表的创建
      • 6.3 表的修改
      • 6.4 表的其它操作

1. 联合查询

定义：将多条查询语句的结果合并成一个结果。适用于在单个查询中从不同的表返回类似结构的数据 或 对单个表执行多个查询，按单个查询返回数据。很多情况下联合查询可以被where替代，两者各有千秋，其中联合查询对于比较复杂的结构效果更好，逻辑更加清晰。

语法：[查询语句1] union [查询语句2] union ....

#查询价格小于等于5的所有产品和包括供应商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);

#以上语句等效于
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 
OR vend_id IN (1001,1002);

#1.要求多条查询语句的查询列数是一致的
#2.要求多条查询语句所查询的每一列的类型和顺序一致
#3.union关键字默认去重，如果需要包含重复项，使用union all

2. 全文本搜索

定义：总的来说，全文本搜索是比LIKE关键字和正则表达式功能更强大，速度更快的匹配字符方式。Mysql会创建指定列中各词的一个索引，搜索可以针对这些词进行。

#1.启用全文本搜索支持
CREATE TABLE productnotes
(
  note_id	INT        NOT NULL AUTO_INCREMENT,
  prod_id	CHAR(10)   NOT NULL,
  note_date	DATETIME   NOT NULL,
  note_text	TEXT	   NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)	#开启全文本搜索,并指定Match()中可传递的值(类似于形参)
) ENGINE=MYISAM;	#指定MYISAM引擎，InnoDB引擎不支持全文本搜索
#定义之后，Mysql自动维护该索引。在增加，更新或删除行时，索引随之自动更新

#2.使用全文本搜索
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');
#Match(note_text)指定Mysql针对指定的列进行搜索，Against('rabbit')指定rabbit为搜索文本
#看起来LIKE也能完成同样的任务，但是全文本搜索会按关键词出现的先后返回结果，而LIKE并不会。同时，如果搜索多个关键词，全文本搜索会优先返回匹配程度高的文本。这些都说明全文本搜索要比LIKE功能强大

2.1 布尔文本搜索

定义：布尔文本搜索是全文本搜索的另外一种形式。它可以通过操作符对匹配文本进行更细致的筛选

#查询所有包含heavy但不包含任意以rope开始的词的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);
#布尔文本搜索不使用FULLTEXT开启，而是使用IN BOOLEAN MODE

布尔操作符	说明
+	包含，词必须存在
-	排除，词必不能出现
>	包含，而且增加等级值
<	包含，但是减少等级值
()	把词组合成子表达式
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语

#1.不设定操作符，搜索匹配包含rabbit和bait中的至少一个词的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit bait' IN BOOLEAN MODE);

#2.搜索匹配短语rabbit bait，而不是匹配两个词rabbit和bait
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE);

#3.搜索匹配词safe和combination，降低后者的等级
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+safe +(<combination)' IN BOOLEAN MODE);

#注意:等级值只影响搜索时的优先级，并不会影响排序的先后

2.2 注意事项

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词
• Mysql带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略
• 如果一个词出现在50%以上的行中，Mysql会将它作为一个非用词忽略。这条规则不适用于IN BOOLEAN MODE
• 不具有词分隔符的语言（如汉语和日语），不能准确的返回全文本搜索结果
• 仅在MyISAM数据库引擎中支持全文本搜索

3. 插入语句

语法：insert into [表名] (列名, ...) values (值1, ...)

#1.单行插入
#插入的方式有两种。插入的值要与列的类型一致或兼容;设定不可为null的列必须插入值,可为null的列可以选择性插入值
#1.1.不明确给出列名，按次序直接插入
INSERT INTO customers
VALUES(
NULL,'Pep E.Lapew','100 Main Street','Los angeles','CA','90046','USA',NULL,NULL);
#这种插入方式高度依赖于表中列的定义次序，而且还依赖于其次序容易获得的信息。不推荐使用

#1.2.明确的给出列名，按列名依次插入
INSERT INTO customers(
cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES(
'Pep E.Lapew','100 Main Street','Los angeles','CA','90046','USA',NULL,NULL);
#VALUES完全按照指定的次序匹配列名，即便表的结构改变，该语句也可以正常运行。推荐使用
#这种语法还可以省略列，但省略的列必须满足以下两个条件之一：1.该列定义为允许NULL值 2.在表定义中给出默认值,如果不给出值，将使用默认值(不一定是default,自增长也可以)


#2.多行插入
#和单行插入差不多,这里仅介绍比较方便的多行插入
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES('Pep E.Lapew','100 Main Street','Los angeles','CA','90046','USA'),
	  ('M.Martian','42 Galaxy Way','New York','NY','11213','USA');
	  
	  
#3.插入检索出的数据
#将custnew表中的数据导入customers中
INSERT INTO customers(
cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_zip,cust_country
) 
SELECT 
cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
FROM custnew;
#Mysql并不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列将用来填充表列中指定的第一个列，以此类推

4. 更新语句

语法：update [表名] set [列]=[新值],[列]=[新值], [...] where [筛选条件];

#更新id为10005的用户的邮箱和名字
UPDATE customers
SET cust_email = 'elmer@fudd.com',
cust_name = 'The Fudds'
WHERE cust_id = 10005;

#如果要删除某个列的值，可以设置它为NULL(常规删除操作只能整行删除)
#删除id为10005的用户的邮箱
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

• 如果要更新多表的记录，可以使用联结：

  update [表1] inner|left|right join [表2] on [连接条件] set [列]=[值],[...] where [筛选条件];

5. 删除语句

5.1 DELETE关键字

单表删除：delete from [表名] where [筛选条件]

多表删除：delete [表1]，[表2] from [表1]

​ inner|left|right join [表2] on [连接条件] where [筛选条件]

#删除id为10006的用户的所有信息
DELETE FROM customers
WHERE cust_id = 10006;

#delete语句从表中删除行，甚至是删除表中所有行。但是，delete不删除表本身

5.2 TRUNCATE关键字

语法：TRUNCATE TABLE [表名];

#删除所有用户信息
TRUNCATE TABLE customers; 

#truncate会一次性删除表本身

5.3 注意事项

• delete删除和truncate删除的区别：

  1. delete可以添加筛选条件(where)，truncate不可以

  2. 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长的值从断点开始，而用truncate删除后，插入数据，自增长列的值从1开始

  3. truncate删除没有返回值，delete删除有返回值(不会显示有几行收到影响)

• 更新和删除的指导原则：

  1. 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句
  2. 保证每个表都有主键，尽可能像where子句那样使用它
  3. 在对update或delete语句使用where子句前，应该先用select进行测试，保证它过滤的是正确的记录。update和delete造成的修改是不可撤销的，要谨慎操作
  4. 可以使用强制实施引用完整性的数据库(如外部联结)，这样Mysql将不允许删除具有其他表相关联的数据的行

6. 创建和操作表

6.1 库的管理

#1.库的创建
#create database 库名;
CREATE DATABASE IF NOT EXISTS books;

#2.更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3.库的删除
DROP DATABASE IF EXISTS books;

6.2 表的创建

语法：create table [表名]([列名] [列类型] [设定条件],[...]);

CREATE TABLE vendorss
(
  vend_id        INT        NOT NULL AUTO_INCREMENT,
  vend_name      CHAR(50)   NOT NULL,
  vend_address   CHAR(50)   NULL,
  vend_city	 CHAR(50)   NULL,
  vend_state     CHAR(5)    NULL,
  vend_zip       CHAR(10)   NULL,
  vend_country   CHAR(50)   NULL,
  PRIMARY KEY (vend_id) 
) ENGINE = INNODB;
DESC vendorss;
#主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识

6.3 表的修改

#1.修改列名
ALTER TABLE vendorss CHANGE COLUMN vend_id vend_idd INT NOT NULL AUTO_INCREMENT;
#2.修改列的类型或约束
ALTER TABLE vendorss CHANGE COLUMN vend_idd vend_idd INT;
#3.添加新列
ALTER TABLE vendors ADD COLUMN vend_phone CHAR(20);
#4.删除列
ALTER TABLE vendors DROP COLUMN vend_phone;

#理想情况下，当表存储数据后，该表就不应该再被更新
#ALTER TABLE的一种常见用途是定义外键，具体请看下节'约束'部分

6.4 表的其它操作

#1.表的重命名
RENAME TABLE customers2 TO customers;

#2.表的删除
DROP TABLE IF EXISTS book_author;

#3.表的复制
#3.1.仅仅复制表的结构
CREATE TABLE copy LIKE vendors;
#3.2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM vendors;
#如果需要更多具体要求可以自行添加where

• 可以通过以下两种方式来删除主表的记录

  • 级联删除

    ALTER TABLE stuinfo 
    ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
    

  • 级联置空

    ALTER TABLE stuinfo
    ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;