SQL语句罗列

-- 创建表
CREATE TABLE student3 (
  id INT,
  NAME VARCHAR(20),
  age INT,
  sex VARCHAR(5),
  address VARCHAR(100),
  math INT,
  english INT
);
-- 插入记录
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

# 关系运算符 mysql 特有的
-- 查询math分数大于80分的学生
SELECT * FROM student3 WHERE math > 80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student3 WHERE english <=80;
-- 查询age等于20岁的学生
SELECT * FROM student3 WHERE age =20;
-- 查询age不等于20岁的学生
SELECT * FROM student3 WHERE age != 20;
SELECT * FROM student3 WHERE age <> 20;

# 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student3 WHERE age >35 AND sex ='男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student3 WHERE age >35 OR sex ='男';
-- 查询id是1或3或5的学生
SELECT * FROM student3 WHERE id =1 OR id =3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student3 WHERE id IN(1,3,5);
-- 查询id不是1或3或5的学生
SELECT * FROM student3 WHERE id NOT IN(1,3,5);
-- 查询english成绩大于等于77，且小于等于87的学生
SELECT * FROM student3 WHERE english >=77 AND english <=87;
-- between
SELECT * FROM student3 WHERE english BETWEEN 77 AND 87;

# like模糊匹配
-- 查询姓马的学生
-- = 不可以用了
SELECT * FROM student3 WHERE NAME ='马';
-- like
SELECT * FROM student3 WHERE NAME LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student3 WHERE NAME LIKE '%德%';
-- 查询姓马，且姓名有三个字的学生
SELECT * FROM `student3` WHERE `name` LIKE '马__';

# 排序
-- 查询所有数据,使用年龄升序排序
SELECT * FROM student3 ORDER BY age ASC;
SELECT * FROM student3 ORDER BY age ;
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC;
SELECT * FROM student3 ORDER BY age DESC ,math DESC;

# 聚合函数
-- 查询学生总数（null值处理）
SELECT COUNT(id) FROM student3;
SELECT COUNT(english) FROM student3;
SELECT english FROM student3;
SELECT IFNULL(english,0) FROM student3;
SELECT COUNT(IFNULL(english,0)) FROM student3;
-- 查询年龄大于40的总数
SELECT * FROM student3 WHERE age >40;
SELECT COUNT(*) FROM student3 WHERE age >40;
-- count(1 位置) --> id
SELECT COUNT(1) FROM student3 WHERE age >40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student3;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student3;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student3;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student3;

-- 统计男生和女生人数
SELECT COUNT(*) FROM student3 WHERE sex = '男';
SELECT COUNT(*) FROM student3 WHERE sex = '女';
# 分组
-- 按性别分组
SELECT * FROM student3 GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(sex) FROM student3 GROUP BY sex;
-- 男生最高分和女生最高分别是多少
SELECT sex,MAX(math) FROM student3 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT * FROM student3 WHERE age >25;
SELECT sex,COUNT(*) FROM student3 WHERE age >25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- where 条件 
-- Invalid use of group function
SELECT sex,COUNT(*) FROM student3 WHERE age >25 AND COUNT(*)>2 GROUP BY sex;
SELECT sex,COUNT(*) FROM student3 WHERE age >25 GROUP BY sex HAVING COUNT(*) >2;

-- 分页
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
-- 查询学生表中数据，从第三条开始显示，显示6条
SELECT * FROM student3 LIMIT 2,6;
-- 查询学生表中数据，显示前6条
SELECT * FROM student3 LIMIT 0,6;
SELECT * FROM student3 LIMIT 6;
-- 模拟百度分页，一页显示5条

-- 第一页
SELECT * FROM student3 LIMIT 0,5;
-- 第二页
SELECT * FROM student3 LIMIT 5,5;
-- 第三页
SELECT * FROM student3 LIMIT 10,5;
-- 第N页 后期 java代码实现分页功能 
SELECT * FROM student3 LIMIT (n-1)*显示个数,显示个数;