SELECT 查询
1.整表查询
SELECT [查询内容] FROM [查询的表名] ;
2.条件查询
SELECT [查询内容] FROM [查询的表名] WHERE [查询的条件] ;
3.多条件查询
SELECT [查询内容] FROM [查询的表名] WHERE [查询的条件] AND [查询条件];
4.关键字BETWEEN AND 在什么之间
SELECT [查询内容] FROM [查询的表名] WHERE [列名] BETWEEN [条件] AND [条件];
例：查询TabName表里“id” 50 到 100 的列
SELECT * FROM TabName WHERE id BETWEEN 50 AND 100;
5.关键字IN集合查询
SELECT [查询内容] FROM [查询的表名] WHERE [列名] IN (x,x,x)
查询TabName表里年龄Age 可能是18 ， 20 ，22的人的信息
SELECT * FROM TabName WHERE Age IN (18,20,22);
6.关键字IS NULL
SELECT [查询内容] FROM [查询的表名] WHERE [列名] IS NULL
SELECT [查询内容] FROM [查询的表名] WHERE [列名] IS NOT NULL
例： 查询年龄没有填写的和已填写的 表TanName 年龄Age
SELECT * FROM TabName WHERE Age IS NULL; Age 为空
SELECT * FROM TabName WHERE Age IS NOT NULL; Age 不为空
7.关键字LIKE模糊查询
SELECT [查询内容] FROM [查询的表名] WHERE [列名] LIKE ‘大概的值’
“%”匹配所有 “_”匹配一个
8.查询排序order by
SELECT [查询内容] FROM [查询的表名] WHERE [查询的条件] ORDER BY;
9.限制查询的记录数LIMIT
SELECT [查询内容] FROM [查询的表名] WHERE [查询的条件] LIMIT (行数int)/(m,n)
10.使用集合函数查询MAX()
SELECT COUNT() FROM [查询的表名]； 计数
SELECT MAX() FROM [查询的表名]； 最大值
SELECT MIN() FROM [查询的表名]； 最小值
SELECT AVG() FROM [查询的表名]； 平均值
SELECT SUM(*) FROM [查询的表名]； 求和
SELECT GROUP_CONCAT(name) FROM [查询的表名]； 一格显示所有列
SELECT sex FROM employee5 GROUP BY sex; 去除重复
11.使用正则表达式查询REGEXP
SELECT * FROM [查询的表名] WHERE [列名] REGEXP ‘正则表达式’ ;
12.MySQL多表查询
a.交叉连接 不需要条件
SELECT [查询内容] FROM [查询的表名1],[查询的表名2] ;
b.内连接
SELECT [查询内容] FROM [查询的表名1],[查询的表名2] WHERE 表1.字段 = 表2.字段;
c.外连接 A表 left/right join B表 on 条件是
SELECT [查询内容] FROM [查询的表名1] LEFT JOIN [查询的表名2] ON 表1.字段 = 表2.字段 ;
