高级联结
使用表别名 AS
自联结
查询DTNTR的供应商的生产的其他物品
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = '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 = 'DTNTR';
自然联结
对表联结,应该至少有一个列出现在不止一个表中(被联结的列)
自然联结排除相同的列多次出现的问题,使每个列只返回一次
自己通过通配符*实现,其他列明确列出
外部联结
外部联结包含了在相关表中没有关联行的行
用RIGHT或LEFT指定包括其他所有行的表
RIGHT指包含OUTER JOIN右边的表
LEFT指包含OUTER JOIN左边的表
检索有订单的客户,用内连接。
检索所有客户,包括哪些没有订单的客户,用外连接
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
带聚集函数的联结
检索所有客户及每个客户下的订单数(想包含那些没有订单的客户用LEFT JOIN)
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
组合查询
使用情况:
在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询,按单个查询返回数据
创建:UNION(放在2或2个以上SELECT语句之间,MySQL执行两条SELECT语句,并把输出组合成单个查询结果集)
UNION中的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容
UNION从查询结果集中自动去除重复的行(UNION ALL不去重)
多个SELECT语句最后使用ORDER BY
全文本搜索
MyISAM 支持 解决LIKE、正则表达式的限制
InnoDB 不支持
FULLTEXT(列) mysql自动维护该索引,在增删改时索引自动更新
不要在导入数据时使用FULLTEXT,更新索引慢,数据导入之后修改表
Match(列) Against(要搜索的表达式)
不区分大小写,除非用BINARY
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
插入数据库
INSERT 插入完整的行
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state)
VALUES('XXX','XX','XXXX','X');
插入多行
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state)
VALUES('XXX','XX','XXXX','X'),
('XXX','XX','XXXX','X');
插入检索出的数据
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state)
SELECT cust_name, cust_address, cust_city, cust_state
FROM custnew;
提高整体性能
如果数据检索是最重要的,可以通过在INSERT LOW_PRIORITY INTO指示mysql降低insert语句的优先级
插入多行,比用多条insert处理插入更快
删除和更新数据
更新表中特定行\更新表中所有行
UPDATE customers
SET cust_email = 'xxxx'
WHERE cust_id = 10200;
UPDATE customers
SET cust_email = 'xxxx',
cust_address = 'xx'
WHERE cust_id = 10200;
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10200;
删除特定的行\删除所有行
DELETE FROM customers
WHERE cust_id = 10200;
更快的删除表中所有行
用TRUNCATE TABLE:删除原来的表并重新创建一个表
创建和操纵表
创建表 CREATE TABLE
仅在表不存在时创建表 IF NOT EXISTS
NULL 没有值,不是空串,空串是一个有效的值
主键 PRIMARY KEY(列1,列2),主键不允许NULL,允许NULL的列不能作为唯一标识
AUTO_INCREMENT
DEFAULT 默认值
ENGINE 引擎类型
InnoDB 可靠的事务处理引擎,不支持全文本搜索
MEMORY 数据存储在内存(不是磁盘),速度很快,适合于临时表
MyISAM 性能极高,支持全文本搜索,不支持事务处理
外键不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键
更新表 ALTER TABLE
理想状态下 当表中存储数据以后,表就不应该在被更新
添加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP COLUME vend_phone;
定义外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY(prod_id)
REFERENCES products (prod_id);
复杂表结构的更改一般需要手动删除
用新的列布局创建一个新表
使用INSERT SELECT从旧表复制数据到新表
检验包含所需数据的新表
重命名酒标
用旧表原有名字命名新表
根据需要,重新创建触发器、存储过程、索引和外键
删除表 DROP TABLE
重命名表 RENAME TABLE XX TO XXX