[大数据]Mysql 学习3

Mysql 学习3

语法学习3

子查询

SELECT order_num
FROM orderitems
WHERE prod_id='TNT2';

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

使用子查询连接两个查询

首先一层一层确定自己查询的数据，通过共有的数据将其一层一层链接到最外层所需的数据。

SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
					FROM orderitems
					WHERE prod_id='TNT2');

SELECT cust_name,cust_state,
				(SELECT COUNT(*)
					FROM orders
					WHERE orders.cust_id=customers.cust_id) AS orders#涉及外部查询
FROM customers
ORDER BY orders;

联结表

外键：两个表中，一个表中的一列包含另一个表的主键值，定义了两个表的关系。

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;

没有连接条件的表关系返回的结果为笛卡儿积。

所有的联结都应保证有WHERE子句。

也可以使用 INNER JOIN 指定，联结条件用特定的ON子句而不是WHERE子句给出。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id;

使用连结表方法达到和子查询一样的效果

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
	AND orderitems.order_num=orders.order_num
	AND prod_id='TNT2';#将三个表联结起来并过滤TNT2的数据

表别名

SELECT cust_name,cust_contact
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='TNT2';

表别名只在查询执行中使用，与列别名不一样。

自联结

#查看生产DTNTR的生产商所生产的其他商品
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='DINTR';

自联结有时性能会比子查询好

自然联结

对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成。

外部联结包括没有关联的行

#检索所有客户，包括那些没有订单的客户
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id;

左外部联结

以左边表单为基准，将右边的表单加入左边得到

组合查询

需要使用组合查询

在单个查询中从不同的表返回类似结构的数据；

对单个表执行多个查询，按单个查询返回数据。

UNION操作符，可以给出多条SELECT语句，将他们的结果组合成单个结果集。

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5#价格小于等于5的所有物品的价格
UNION#并集
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);#供应商1001，1002生产的所有物品

#结果含有其他厂商生产的价格小于等于5的物品供应信息

UNION使用规则：

1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔

2、UNION中的每个查询必须包含相同的列，表达式或聚集函数

3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型。

UNION从查询结果集中自动去除了重复的行，如果不去除，可使用UNION ALL。

只能使用一条ORDER BY子句，出现在最后一条SELECT语句之后。

全文本搜索

在创建表时启用全文本搜索，给出被索引列的一个逗号分隔的列表。根据FULLTEXT索引单个列，如果需要也可以指定多个列。不要在导入数据时使用FULLTEXT！（本质来讲就是专门创造一个索引列）

创建

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(notetext)#创建索引列
)ENGINE=MyISAM;

使用

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');#全文本搜索

MATCH()指定被搜索的列，AGAINST()指定要使用的搜索表达式。

可以使用MATCH()和AGAINST()用来建立一个计算列,此列包含全文本搜索计算出的等级值。

SELECT MATCH(note_text) AGAINST('rabbit') AS rank

查询扩展 MySQL对数据和索引进行两遍扫描来完成搜索：

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；

其次，MySQL检查这些匹配行并选择所有有用的词；

再其次，MySQL再次进行全文本搜索，使用原有的条件和所有有用的词。

WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

布尔文本搜索

WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);

匹配包含heavy但不包含任意以rope为开始的词的行。

在布尔方式中，不按等级制降序排序返回的行。

全文本搜索的使用说明：

1、短词被忽略且从索引中排除，短词为少于3个字符。（可更改）

2、MySQL有一个内建的非用词列表（可更改）

3、表中的行数少于3行，全文本搜索不返回结果。

4、许多次出现的频率很高（MySQL规定50%原则）

5、忽略词中的单引号

6、不具有词分隔符

7、仅在MyISAM数据库引擎中支持全文本搜索。

对数据操作

插入数据

使用 INSERT INTO 语法，可以省略列（前提是该列定义为允许的NULL值，或者给出默认值），也可添加关键字如 INSERT LOW_PRIORITY INTO 降低插入语句的优先级。

#插入数据更安全的做法
INSERT INTO customers(cust_name,cust_email,cust_address,cust_state,cust_zip)
VALUES('HHARRY POTTER',NULL,NULL,'CA','90046'),
('RYRY POTTER','HEIHEI@CUC.EDU.CN','CUCUC','DFZ','10110');

插入检索出的数据

INSERT INTO tables (rowsname) SELECT rowsname FROM tables;

注意括号

更新数据

UPDATE customers
SET cust_email=NULL
WHERE cust_id=10005;

UPDATE IGNORE 即使发生错误也继续更新

删除数据

使用DELETE关键字，删除表的内容而不是表。

TRUNCATE TABLE删除原来的标兵重新创建一个表。

DELETE FROM customers
WHERE cust_id=10006;

对表操作

创建表

CREATE TABLE########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

关键字CREATE TABLE,圆括号之中列名，数据类型，默认值，主键字指定

记住创建时先删除后创建或者在表明后给出 IF NOT EXISTS

NOT NULL阻止插入没有值的列。

区分NULL与“”空串。

主键：唯一标识表中每个行的列，允许NULL值的列不能作为主键。

AUTO_INCREMENT：本列每当增加一行是自动增量。每个表只允许一个这个列。

SELECT last_insert_id():此语句返回最后一个自动增量值。

指定默认值：DEFAULT ___

CREATE TABLE orderitems(quantity int NOT NULL DEFAULT 1,......)

引擎

InnpDB:可靠的事务处理引擎，不支持全文本搜索

MEMORY:功能等同于MyISAM,数据存储在内存中，速度快，适合临时表

MyISAM:性能极高的引擎，支持全文本搜索，不支持事务处理

外键不能跨引擎

更新表

ALTER TABLE 更改表的结构

ALTER TABLE vendors
ADD vend_phone CHAR(20);#增加列，明确数据类型

ALTER TABLE vendors
DROP COLUMN vend_phone;#删除列

ALTER TABLE orderitems #向这个表里
ADD CONSTRAINT fk_orderitems_orders #添加约束，约束名为fk_ _
FOREIGN KEY (order_num) #引入外键约束 （列名）
REFERENCES orders (order_num);#被引用对象 表名（列名）

删除表

DROP TABLE customers;

重命名

RENAME TABLE customers2 to customers

ALTER TABLE vendors
DROP COLUMN vend_phone;#删除列

ALTER TABLE orderitems #向这个表里
ADD CONSTRAINT fk_orderitems_orders #添加约束，约束名为fk_ _
FOREIGN KEY (order_num) #引入外键约束 （列名）
REFERENCES orders (order_num);#被引用对象 表名（列名）

#### 删除表

```mysql
DROP TABLE customers;

重命名

RENAME TABLE customers2 to customers