MySQL-常用命令(二)
函数
表结构如下
select * from vendors;+---------+-----------+---------------------------+-----------+------------+----------+--------------+| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |+---------+-----------+---------------------------+-----------+------------+----------+--------------+| 1 | vend_1 | shanghai_baoshan_jinqiulu | shanghai | shanghai | 200000 | CN || 2 | vend_2 | hefei_gaoxin_huangshanlu | hefei | anhui | 300000 | CN || 3 | vend_3 | hangzhou_binjiang_binjian | hangzhou | zhejiang | 400000 | CN || 4 | vend_4 | ningbo_gaoxin_shijilu | ningbo | zhejiang | 500000 | CN |+---------+-----------+---------------------------+-----------+------------+----------+--------------+
CONCAT：用于连接字段的值或者字符串，以行的形式显示（注意和GROUP_CONCAT的区别）
SELECT CONCAT(vend_name,'!',vend_country) FROM Vendors;//输出+------------------------------------+| concat(vend_city,'!',vend_country) |+------------------------------------+| shanghai!CN || hefei!CN || hangzhou!CN || ningbo!CN |+------------------------------------+
GROUP_CONCAT：用于连接字段的值或者字符串，以“，”分割显示在一行（注意和CONCAT的区别）
SELECT GROUP_CONCAT(vend_city,vend_country) FROM vendors;//输出+----------------------------------------+| group_concat(vend_city,vend_country) |+----------------------------------------+| shanghaiCN,hefeiCN,hangzhouCN,ningboCN |+----------------------------------------+
GROUP_BY: 对行分组，但输出的值未必是原数据的同一行。选择没有被GROUP_BY的字段值具有不确定性，特别是和MAX()，MIN()函数结合使用时。
select vend_state, count(*) as state_num from vendors group by vend_state;//输出+------------+-----------+| vend_state | state_num |+------------+-----------+| anhui | 1 || shanghai | 1 || zhejiang | 2 |+------------+-----------+#条件分组 HAVING条件select vend_state, count(*) as state_num from vendors group by vend_state having state_num > 1;//输出+------------+-----------+| vend_state | state_num |+------------+-----------+| zhejiang | 2 |+------------+-----------+