MySQL学习续篇--01

####like关键字查询 

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

实例：

runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：

SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

多表查询

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

实例：
UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）：

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;`

MySQL 排序—关键词 ORDER BY
SELECT field1, field2,…fieldN table_name1, table_name2…
ORDER BY field1, [field2…] [ASC [DESC]]
实例：
ASC —-升序 DESC—-降序

SELECT * from runoob_tbl ORDER BY submission_date DESC;

mysql分组—关键系 GROUP BY
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

实例：
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
 --------------------------------------------
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

Mysql 连接的使用
join,只展示两者相同的
left join，展示左边表的所有，右边只展示和左边相同的。
right join,展示右边表的所有，左边只展示和右边相同的。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
-------------------------------------------------
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
-------------------------------------------------
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

MySQL正则表达式

查找name字段中以'st'为开头的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '[aeiou|ok$';