二级五六七章 匹配查询 索引 视图

原创
2017/03/19 20:34
阅读数 30
#数据库的查询 四川省办公用品按销售额降序排列
set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
ALTER TABLE 订单
MODIFY COLUMN 销售额 DOUBLE(15,10);
SELECT 省,销售额,类别,订单ID FROM 订单 GROUP BY 省,销售额,类别,订单ID HAVING 省='四川' AND 类别='办公用品' ORDER BY 销售额 DESC LIMIT 10;

#替换查询的结果
SELECT 订单ID,
CASE
WHEN 折扣!=0 THEN '有'
ELSE '无'
END FROM 订单;

#聚合函数
SELECT COUNT(*) FROM 订单;
SELECT MAX(销售额) FROM 订单 WHERE 省='上海';
SELECT SUM(销售额) FROM 订单 WHERE 省='上海';

#连接表
SELECT * FROM 退货 LEFT JOIN 订单 	ON 订单.订单ID=退货.订单ID;

#字符匹配
SELECT * FROM 订单 WHERE 客户名称 LIKE '曾%'; 

#索引
CREATE INDEX 索引
ON test.students(student_birthday ASC)
USING BTREE;
#查看已创建的索引
SHOW INDEX FROM students;
#创建复合索引
CREATE INDEX 复合索引
ON test.students(student_birthday,student_name ASC)
USING BTREE;
SHOW INDEX FROM students;
#删除索引
USE test;
DROP INDEX 索引 ON students;

#视图
CREATE VIEW 皇帝
AS 
SELECT student_name,student_address
FROM students;
WHERE student_id<3
WITH CHECK OPTION; 
select * from 皇帝;
drop view 皇帝;

 

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部