MYSQL 初级入门 (SELECT 查询)

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.字段 ;