目录
第四章
检索单列
select prod_name from products;
结果顺序可能不同,确保行数正确即可
检索多列
select prod_id,prod_name,prod_price from products;
检索所有列
select * from products;
distinct
检索不同的行**(distinct必须直接放在列前面)**
select distinct vend_id from products;
当distinct修饰两列
select distinct vend_id,prod_price from products;
select vend_id,prod_price from products;
可见两列内容完全相同才会删去
limit
限制显示结果
显示不多于5行
select prod_name from products limit 5;
指定要检索的开始行和行数
select prod_name from products limit 3,5;
从行3开始取五行,注意第一行是0,也就是行3其实是第四行
如果超过最大行数,就只显示能返回的行数
SELECT prod_name FROM products LIMIT 13,5;
第五章 排序检索数据
order by
通常检索的数据是按照添加到表中的顺序显示,ORDER BY进行排序
select prod_name from products order by prod_name;
按照一个列的字母顺序排序
按照多个列排序,优先第一个列的顺序,前一个列相同时才按照下一个列排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
desc排序方向
默认升序=ASC,DESC设置为降序,只应用于直接位于其前面的列名,需要多个列降序需要每个列指定desc
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;
找最贵
SELECT * FROM products ORDER BY prod_price DESC LIMIT 1;
ORDER BY子句必须是select语句中最后一条子句
第六章
过滤条件WHERE
where在order by前
SELECT prod_name,prod_price FROM products WHERE prod_price=2.5;
where支持的条件操作符
执行匹配时不区分大小写,aaa=Aaa可以匹配
值与串进行比较时,需要引号,与数值比较则不需要
where prod_price=10;
where prod_name=‘fuses’;
between
需要配合and,且两边均为闭区间
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
NULL匹配要用IS NULL
SELECT prod_name FROM products WHERE prod_name IS NULL;
第七章 数据过滤
组合WHERE子句
AND
SELECT prod_id,prod_name,prod_price FROM products WHERE vend_id=1003 AND prod_price<=10;
OR
SELECT prod_id,prod_name,prod_price FROM products WHERE vend_id=1003 OR vend_id=1002;
AND比OR优先级高,混合使用要用括号分隔
SELECT prod_id,prod_name,prod_price FROM products WHERE prod_price>=10 AND (vend_id=1003 OR vend_id=1002);
IN
OR可以替换成IN
SELECT prod_id,prod_name,prod_price FROM products WHERE prod_price>=10 AND vend_id IN (1002,1003);
NOT
NOT否定它之后所有条件
SELECT prod_id,prod_name,prod_price FROM products WHERE prod_price>=10 AND vend_id NOT IN (1002,1003);
第八章 通配符过滤
LIKE
LIKE表示后面是通配符匹配而不是直接相等匹配
%:表示任何字符出现任意次数(0-n次),尾空格会干扰匹配,故可在LIKE通配符尾加上%如’anvil%’
%不匹配NULL
寻找prod_name列以jet开头的产品
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvi%';
** _:单个字符**
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
比较%
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '% ton anvil';
优化技巧:通配符慢,尽量别用,非要用放在最后,放在开头会慢
第九章 正则表达式搜索
REGEXP
其后作为正则表达式
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '1000';
正则表达式中.代表任意一个字符,故下两句功能相同
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%000%';
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '.000';
BINARY关键字:区分大小写
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP BINARY 'Jetpack .000';
返回为空
OR匹配 |
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000';
OR匹配几个字符之一 []
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '[123] ton';
相当于[1|2|3] ton
不使用[]会导致|应用于整个串
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '1|2|3 ton';
否定一个字符集^
匹配123之外
[^123]
匹配范围
0-9,1-5,任意字母
[0-9] [1-5] [a-z]
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '[2-5] ton';
转义
匹配特殊字符:如. - | []等
使用\作为前导进行转义
如 REGEXP ‘\.’
\也可用于引用元字符
匹配多个实例
例子1:
匹配prod_name中的(数字 stick)或(数字 sticks)
?指匹配它之前的字符的0次或1次出现,s后的问号使s可选
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '\\([0-9] stick?\\)';
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)';
例子2:匹配连在一起的四个数字
三种方法
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '[0-9]{4}';
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]';
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';
定位符
例:prod_name列所有以小数点或数字开头的
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';
不使用数据库表进行正则表达式测试,匹配返回1,不匹配返回0
SELECT 'hello' REGEXP '[0-9]';
返回0
第十章 创建计算字段
concat()函数
拼接字段
例:将vendors表中的供应商名和供应商位置按照name(location)的格式输出报表
注意:是逗号连接而非加号
SELECT CONCAT(vend_name ,'(',vend_city,')') FROM vendors;
RTrim()函数与LTrim()函数
去掉值右边所有空格/去掉值左边所有空格
SELECT CONCAT(RTRIM(vend_name) ,'(',RTRIM(vend_city),')') FROM vendors;
as别名
指示SQL创建一个包含指定计算的名为别名的计算字段,可以按名引用该列
SELECT CONCAT(RTRIM(vend_name) ,'(',RTRIM(vend_city),')') AS 'vend_title' FROM vendors;
算术计算
例子:计算orderitems表order_num=20005中每一个货物总价
SELECT prod_id,item_price*quantity AS total_price FROM orderitems WHERE order_num=20005;
第十一章 使用数据处理函数
文本处理函数
upper():全部转为大写
SELECT vend_name,UPPER(vend_name) AS vend_name_upper FROM vendors;
日期和时间处理函数
例题:求orders表中05你9月的所有订单
两种方法
SELECT *FROM orders WHERE YEAR(order_date)=2005 MONTH(order_date)=9;
SELECT *FROM orders WHERE order_date BETWEEN "2005-09-01" AND "2005-09-30";
数值处理函数
第十二章 汇总数据
聚集函数
AVG()求平均
例子:products表中商品价格平均值
SELECT AVG(prod_price) AS pingjun FROM products;
例子:特定供应商商品价格平均值
SELECT AVG(prod_price) AS pingjun FROM products WHERE vend_id=1003;
COUNT() 累计
count(*)统计行数,无论是否null
count(column) 统计特定列中有值的行数,忽略null
例子:统计customers表中的用户数
SELECT COUNT(*) FROM customers;
统计有邮箱的用户数
SELECT COUNT(cust_email) FROM customers;
max()求最大值,min()求最小值
max()和min()函数忽略列值为null的行
sum()返回指定列值的和(总计)
sum()也自动忽略列值为null的行
例子:统计orderitems表中order_num为20005的order_item总数
SELECT SUM(quantity) FROM orderitems WHERE order_num=20005;
例子:计算order_num=20005的订单总额(item_price)*quantity
SELECT SUM(item_price*quantity) AS total_money FROM orderitems WHERE order_num=20005;
不同值的聚集
统计products表中vend_id=1003的各个不同价格平均值
SELECT AVG(DISTINCT prod_price) AS dist_avg_price FROM products WHERE vend_id=1003;
distinct不能用于count(*),即不能用count(distinc),但可用count(distinct column)
组合聚集函数
例子:统计products表中num_items总数,price的最高最低平均值
SELECT COUNT(*) AS total,AVG(prod_price) AS average,MIN(prod_price) AS minimum,MAX(prod_price) AS maxium FROM products;
第十三章 分组数据
GROUP BY
创建分组
例子:products表中按vend_id分
SELECT COUNT(*) AS tongji FROM products GROUP BY vend_id;
group by在where之后,order by之前
where——group by——order by
HAVING
where和having的区别:where过滤行,having过滤分组
例子:products表中按vend_id分,显示tongji>2的
用where会报错,因为过滤是基于分组聚集值而不是特定行值
SELECT vend_id,COUNT(*) AS tongji FROM products GROUP BY vend_id HAVING tongji>2 ORDER BY vend_id;
SELECT vend_id,COUNT(*) AS tongji FROM products GROUP BY vend_id HAVING COUNT(*)>2 ORDER BY vend_id;
where和having混合使用
例子:统计products表中按vend_id分组,数量超过两个且价格大于等于10的
SELECT vend_id,COUNT(*) AS tongji FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2 ORDER BY vend_id;
例子:统计orderitems表中订单价格大于等于50的订单的订单号和总计订单价格
SELECT order_num,SUM(quantity*item_price) AS total_price FROM orderitems GROUP BY order_num HAVING SUM(item_price*quantity)>=50 ORDER BY total_price;
中间报错,原因搞错了关键词顺序,having在group by后面
select子句顺序
select from where group by having order by limit
第十四章 子查询
没搞懂,感觉是鸡肋,需要了回头再看
第十五章 联结表
例子:orderitems表中有prod_id为TNT2的商品,查询买该商品dustomers表中的cust_id
SELECT cust_name FROM customers,ORDERS,orderitems WHERE orderitems.prod_id='TNT2' AND (customers.cust_id=orders.cust_id) AND(orders.order_num=orderitems.order_num);
select的列名如果不是唯一,需要用表名.列名限定否则报错
内连结
就是等值连接
内连接:指连接结果仅包含符合连接条件的行,参与连接的两个表都应该符合连接条件。
外连接:连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;