简单查询
-- 显示所有列(字段)数据
SELECT * FROM student;
SELECT id,`name`,age,gender FROM student;
-- 查询指定的列
SELECT id,`name` FROM student;
-- 查询的时候可以添加常量列,通过AS可以起别名
-- AS 可以给某一列起别名,这个AS可以省略
SELECT id,`name`,age,gender,'Java210702' AS '班级' FROM student;
-- 查询的时候可以将字段当成Java里面的变量进行运算
SELECT id,`name`,(php+java) '总成绩' FROM student;
-- 查询的时候去掉重复的记录
-- 查询这些学生来自于哪几个城市
SELECT DISTINCT address FROM student;
条件查询
-- 条件查询 WHERE
SELECT * FROM student WHERE `name`='zhangsan';
-- 逻辑条件 AND(同时成立) OR(只要有一个成立)
SELECT * FROM student WHERE `name`='王五' AND address='青岛';
SELECT * FROM student WHERE `name`='王五' OR address='青岛';
-- 比较逻辑符:> < >= <= !=
SELECT * FROM student WHERE java>=60 AND java<=90;
-- BETWEEN AND [60,90](必须包含等号)
SELECT * FROM student WHERE java BETWEEN 60 AND 90;
SELECT * FROM student WHERE address!='青岛';
聚合函数的使用
-- 聚合查询函数:sum()、avg()、max()、min()、count()
-- 统计学生php的总成绩(sum求和)
SELECT SUM(php) AS 'php总成绩' FROM student;
-- 统计学生php成绩的平均值
SELECT AVG(php) AS 'php平均值' FROM student;
-- 统计学生php成绩的最大值
SELECT MAX(php) AS 'php最大值' FROM student;
-- 统计学生php成绩的最小值
SELECT MIN(php) AS 'php最小值' FROM student;
-- 统计学生表里面一共有多少学生
SELECT COUNT(*) AS '总人数' FROM student;
SELECT COUNT(id) AS '总人数' FROM student;
SELECT COUNT(address) AS '总人数' FROM student;
注意:count 函数统计的是指定列不包含 NULL 的数据的个数
查询并排序
-- ORDER BY 字段 ASC/DESC
-- 默认是ASC升序,可以不写
SELECT * FROM student ORDER BY id DESC;
SELECT * FROM student ORDER BY php;
SELECT * FROM student ORDER BY php ASC;
-- 默认php相同的是按照id排的
SELECT * FROM student ORDER BY php DESC;
-- 多个条件排序
-- 先按照php降序,java升序(整体是按照php降序排列,如果php成绩相同按照java升序)
SELECT * FROM student ORDER BY php DESC,java ASC;