MySQL笔记3

字段操作

CONCAT拼接字段

#以格式vend_name(vend_country)格式显示供应商信息
SELECT CONCAT(vend_name,'(',vend_country,')') FROM vendors;

#RTrim去除右侧空格
#上面语句输出的结果和要求有细微差别，要求是（）前后没有空格
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') FROM vendors;

#使用别名
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors;

字段算术计算

#算术操作符支持+-*/
SELECT prod_id, quantity, item_price, quantity*item_price AS price FROM orderitems;

数据处理函数

文本处理函数

函数	说明
left()	返回串左边的字符
right()	返回串右边的字符
RTrim()	去除串右边的空格
LTrim()	去除串左边的空格
Lower()	将串全部小写
Upper()	将串全部大写
length()	返回串的长度
substring()	截取一部分字符返回
locate()	找出串的一个子串的位置
soundex()	返回串的soundex值

需要特别说明soundex()函数的功能：类似于模糊音匹配。

#例如有个消费者coyote Inc.，联系名为Y. Lee，但是检索式拼写成了Y. Lie，肯定检索不到信息
SELECT cust_name,cust_contact FROM customers WHERE cust_contact='Y .Lie';
#使用soundex()函数将字符转化为发音音节，可以进行模糊音匹配，从而检索到信息
SELECT cust_name,cust_contact FROM customers WHERE SOUNDEX(cust_contact)=SOUNDEX('Y .Lie');

日期处理函数

日期处理函数	说明
now()	返回当前日期和时间
curdate()	返回当前日期
curtime()	返回当前时间
date()	返回当前日期时间的日期部分
time()	返回当前日期时间的时间部分
year()	返回一个日期的年
month()	返回一个日期的月
day()	返回一个日期的天
hour()	返回一个时间的小时
minute()	返回一个时间的分钟
second()	返回一个时间的秒
adddate()	增加一个日期
addtime()	增加一个时间
dayofweek()	返回一个日期对应的星期几
datediff()	计算两个日期之差
date_add()	高度灵活的日期运算函数
date_formate()	将日期格式化为想要的格式

#基本的日期比较
SELECT * FROM orders WHERE order_date='2005-09-01';
但是这样并不保险，因为order_date里面不只有日期，还有时间，这里order_date里面的时间为00:00:00所以恰巧匹配，
更保险的做法是使用DATE()函数提取order_date的日期
SELECT * FROM orders WHERE DATE(order_date)='2005-09-01';

#比较复杂的日期操作
#例如检索位于9月份内的订单
SELECT * FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

#更灵活的方法
SELECT * FROM orders WHERE YEAR(order_date)=2005 AND MONTH(order_date)=9

数值处理函数

abs();cos();exp();pi();rand();sin();sqrt();tan();mod();

汇总数据

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列有多少行
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的总和

#avg()只能用于特定列（数值列）求平均值，且将列名作为函数的参数输入，想要获得多个列的平均值，必须使用多个avg()函数
SELECT AVG(prod_price) AS avg_price FROM products;

#count()函数的两种用法
#count(*)对表中的行数进行计数，不管是否有null值
SELECT COUNT(*) FROM customers;
#count(字段名)对特定列的行数进行计数，null值不计算在内
SELECT COUNT(cust_email) FROM customers;

#max()和min()虽然一般用来找出最大\最小的数值或者日期，但mysql允许用他们返回任意列（包括文本列）的最大\最小值
#用于文本列时，如果数据按照相应的列排序，则min() 返回第一行，max()返回最后一行数据
#l两个函数都忽略null值
SELECT MIN(prod_price) FROM products;
SELECT MAX(prod_price) FROM products;

#sum()函数也忽略null行
SELECT SUM(prod_price) FROM products;

聚集函数中使用DISTINCT

#上面五个聚集函数都可以类似如下使用distinct去除重复内容
SELECT AVG(DISTINCT prod_price) FROM products;
#注意
#如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT)，否则产生错误。
#类似的，DISTINCT必须使用列明，不能用于计算或表达式

多个聚合函数同时使用

SELECT COUNT(*) AS 数据条数,MIN(prod_price) AS  min_price, MAX(prod_price)  AS max_price, SUM(prod_price) AS sum_price FROM products;

分组数据

下面的例子返回供应商1003提供的产品数目

SELECT COUNT(*) FROM products WHERE vend_id=1003

但是当我们需要返回每个供应商提供的产品数目时，以前的知识不能做到，或者能做到但是很复杂。这个时候就需要用到数据分组的概念。

GROUP BY数据分组

SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id;

HAVING过滤分组

#where过滤行，having过滤分组，即having负责分组后再过滤
SELECT vend_id, COUNT(*)  FROM products GROUP BY vend_id HAVING COUNT(*)>2;

#当然，where和having是可以同时使用的，比如查询供应产品价格大于等于10，且供应产品数目大于等于2的供应商
SELECT vend_id, COUNT(*) FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

分组并排序

#检索总计订单价格大于50的订单的订单号和总计订单价格
SELECT 
  order_num,
  SUM(quantity * item_price) 
FROM
  orderitems 
GROUP BY order_num 
HAVING SUM(quantity * item_price) > 50 ;

#在上述查询的基础上，再按照总订单价格排序输出
SELECT 
  order_num,
  SUM(quantity * item_price) 
FROM
  orderitems 
GROUP BY order_num 
HAVING SUM(quantity * item_price) > 50 
ORDER BY SUM(quantity * item_price) ;

SELECT子句顺序

使用子查询(略难)

子查询：即嵌套在查询中的查询，或者说嵌套在select中的select

利用子查询进行过滤

#举例说明：需要列出订购物品 TNT2的所有客户，因为信息分别存在多个表中，所以需要以下步骤
#检索包含TNT2的所有订单号
#检索上一步输出的订单号涉及的客户ID
#检索上一步输出的ID对应的客户信息
SELECT order_num FROM orderitems WHERE prod_id='TNT2';#结果为20005，20007
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);#结果为10001，10004
SELECT * FROM customers WHERE cust_id IN (10001,10004);

#现在使用子查询将所有语句组合成一个语句
SELECT * FROM customers WHERE cust_id IN (
	SELECT cust_id FROM orders WHERE order_num IN (
		SELECT order_num FROM orderitems WHERE prod_id='TNT2'
	)
);

作为计算字段使用子查询

#加入需要显示customers表中每个客户的信息和他对应的订单总数
SELECT cust_id,cust_name,(
    SELECT COUNT(*) FROM orders WHERE orders.`cust_id` = customers.`cust_id`
    ) 
FROM customers ;
#这里使用了完全限定的表明，这里如果不加限定会出现多义性。

算字段使用子查询

#加入需要显示customers表中每个客户的信息和他对应的订单总数
SELECT cust_id,cust_name,(
    SELECT COUNT(*) FROM orders WHERE orders.`cust_id` = customers.`cust_id`
    ) 
FROM customers ;
#这里使用了完全限定的表明，这里如果不加限定会出现多义性。