MySQL中的查询语法

条件查询

-- 条件查询  select 结果列  from 表名  where 条件
SELECT * FROM student WHERE height>=1.80;

-- and 所有条件都要满足
SELECT * FROM student WHERE height<1.80 AND gender ='女';


-- or 满足一个条件即可
SELECT * FROM student WHERE height=1.80 OR gender='女'; 

模糊查询

-- 模糊查询  like  %字符%  通配符
SELECT NAME FROM student WHERE NAME LIKE '%杰';



-- between 1.70 and 2.0== height>=1.70 and height<=2.0
SELECT NAME FROM student WHERE height BETWEEN 1.70 AND 2.0;

in/not in语句

-- 查询身高1.80,1,57,1,55
SELECT NAME FROM student WHERE height IN(1.80,1.57,1.55);


SELECT NAME FROM student WHERE height NOT IN(1.80,1.57,1.55);

空值查询&联合查询

-- 查询值为空的数据
SELECT NAME FROM student WHERE height IS NULL;



-- 查询值不为空的数据
SELECT NAME FROM student WHERE height IS NOT NULL;


-- union 合并多个查询的结果  重复的会自动去除
SELECT NAME FROM student WHERE height=1.80
UNION
SELECT NAME FROM student WHERE gender='女'


-- union all 合并多个查询的结果  重复的不会去除
SELECT NAME FROM student WHERE height=1.80
UNION ALL
SELECT NAME FROM student WHERE gender='男'

排序

-- 排序
-- asc 升序(默认)  desc降序
SELECT * FROM student ORDER BY height;

SELECT * FROM student ORDER BY height DESC;

有数量限制的查询

-- 数量限制  limit 开始位置,查询的数量
-- 实际使用场景,数据分页显示   一次只查询一部分数据,提高查询效率
SELECT * FROM student LIMIT 0,1;
SELECT * FROM student LIMIT 1,3;

分组查询

-- 分组查询
-- 将某类数据分到一个组中进行处理 
-- group by 分组条件(列名)
SELECT COUNT(*),gender FROM student GROUP BY gender;
SELECT SUM(height) FROM student GROUP BY gender;
SELECT AVG(height) FROM student GROUP BY gender;