SQL实现数据过滤流程详解

数据准备student 表

CREATE TABLE `student` (
 `student_id` varchar(50) NOT NULL COMMENT '学生编号',
 `student_name` varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
 `gender` varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
 `birth_day` date NOT NULL COMMENT '生日',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `class_id` varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
 `score` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
 `teacher_id` varchar(20) DEFAULT NULL COMMENT '老师编号'
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='学生';
INSERT INTO `student` VALUES ('S20180001','方东美','女','2006-02-04',12,'G0101',80.65,'T0003'),('S20180002','方香','女','2008-09-28',10,'G0101',75.48,NULL),('S20180003','高紫菡','男','2006-07-22',12,'G0101',74.46,'T0003'),('S20180004','胡未迟','男','2007-07-25',11,'G0101',51.27,'T0003'),('S20180005','李咏颐','男','2007-03-16',11,'G0101',88.84,'T0003'),('S20180006','吴灏潇','男','2008-04-19',10,'G0101',69.93,NULL),('S20180007','吴明鸿','男','2007-11-18',11,'G0101',63.65,'T0003'),('S20180008','吴鹏宇','男','2007-08-24',11,'G0101',84.69,'T0003'),('S20180009','吴少雄','男','2007-08-04',11,'G0101',76.36,'T0003'),('S20180010','习芬飘','女','2005-01-27',13,'G0101',83.42,'T0003'),('S20180011','俞倚琳','女','2007-07-07',11,'G0101',97.38,'T0003'),('S20180012','张琼雪','女','2006-06-12',12,'G0101',81.01,'T0003'),('S20180013','陈顺军','女','2006-09-12',12,'G0102',91.13,'T0004'),('S20180014','方浩杰','男','2008-03-29',10,'G0102',79.46,NULL),('S20180015','方静雅','女','2007-01-27',11,'G0102',54.99,'T0004'),('S20180016','胡博涵','男','2008-08-11',10,'G0102',50.32,NULL),('S20180017','胡昊泽','男','2006-12-04',12,'G0102',53.58,'T0004'),('S20180018','牛思静','男','2006-11-19',12,'G0102',51.96,'T0004'),('S20180019','刘佳乐','男','2007-09-12',11,'G0102',75.01,'T0004'),('S20180020','刘笑婷','男','2006-11-07',12,'G0102',79.29,'T0004'),('S20180021','梅梦瑶','女','2007-04-01',11,'G0102',51.45,'T0004'),('S20180022','牛佳艳','女','2006-08-14',12,'G0102',75.96,'T0004'),('S20180023','牛芸','女','2006-06-07',12,'G0102',72.31,'T0004'),('S20180024','任美梦','女','2006-04-27',12,'G0102',57.18,'T0004'),('S20180025','汪涵颖','女','2006-04-22',12,'G0102',51.50,'T0004'),('S20180026','吴雨默','男','2008-07-30',10,'G0102',56.45,NULL),('S20180027','吴昭溶','男','2006-01-25',12,'G0102',72.19,'T0004'),('S20180028','叶冬星','女','2007-07-14',11,'G0102',69.39,'T0004'),('S20180029','张琼莎','女','2007-07-21',11,'G0102',63.70,'T0004'),('S20180030','赵振宇','男','2008-03-20',10,'G0102',60.70,NULL),('S20180031','方麒隆','男','2007-05-20',11,'G0103',60.61,'T0005'),('S20180032','高齐孺','男','2006-04-29',12,'G0103',72.02,'T0005'),('S20180033','黄建国','男','2006-06-24',12,'G0103',78.25,'T0005'),('S20180034','李舒飞','女','2007-06-20',11,'G0103',75.21,'T0005'),('S20180035','李佳欣','男','2007-12-01',11,'G0103',59.59,'T0005'),('S20180036','梅绵恩','女','2007-04-24',11,'G0103',80.76,'T0005'),('S20180037','牛灵雪','女','2006-09-27',12,'G0103',79.98,'T0005'),('S20180038','牛紫萍','女','2006-11-05',12,'G0103',57.61,'T0005'),('S20180039','任美灵','女','2006-02-06',12,'G0103',85.71,'T0005'),('S20180040','汪如一','女','2005-11-10',13,'G0103',67.82,'T0005'),('S20180041','汪红春','女','2008-03-02',10,'G0103',94.70,NULL),('S20180042','吴国加','男','2005-03-08',13,'G0103',70.05,'T0005'),('S20180043','习篮口','女','2008-06-04',10,'G0103',66.13,NULL),('S20180044','习馨一','女','2005-08-15',13,'G0103',33.71,'T0005'),('S20180045','汪书乔','女','2006-11-17',12,'G0103',40.24,'T0005'),('S20180046','赵舒涵','男','2008-08-16',10,'G0103',59.55,NULL),('S20180047','周浩宇','男','2005-07-01',13,'G0103',85.08,'T0005'),('S20180048','周仁浩','男','2006-08-16',12,'G0103',48.78,'T0005'),('S20180049','汪思齐','男','2007-09-05',11,'G0104',78.49,'T0006'),('S20180050','方粉','女','2006-07-25',12,'G0104',52.20,'T0006'),('S20180051','付娟丽','女','2008-10-19',10,'G0104',77.57,NULL),('S20180052','李景浩','男','2005-05-22',13,'G0104',71.09,'T0006'),('S20180053','刘晨涛','男','2007-07-09',11,'G0104',78.83,'T0006'),('S20180054','周子晨','女','2008-06-23',10,'G0104',80.89,NULL),('S20180055','汪云宗','女','2005-11-01',13,'G0104',67.94,'T0006'),('S20180056','王乐怡','男','2007-11-03',11,'G0104',97.02,'T0006'),('S20180057','王思卓','男','2006-09-27',12,'G0104',81.31,'T0006'),('S20180058','王思思','女','2006-03-24',12,'G0104',48.10,'T0006'),('S20180059','赵鹏林','男','2008-11-03',10,'G0104',82.69,NULL),('S20180060','陈希','女','2008-08-27',10,'G0105',52.04,NULL),('S20180061','方红春','女','2007-10-05',11,'G0105',37.05,'T0007'),('S20180062','方龄辕','女','2006-11-23',12,'G0105',55.12,'T0007'),('S20180063','方子嶂','男','2007-04-14',11,'G0105',65.19,'T0007'),('S20180064','高福运','男','2006-08-28',12,'G0105',60.56,'T0007'),('S20180065','王天睿','女','2005-07-10',13,'G0105',71.24,'T0007'),('S20180066','王天佑','女','2006-08-27',12,'G0105',54.57,'T0007'),('S20180067','王琳','女','2005-10-01',13,'G0105',51.11,'T0007'),('S20180068','王梦菲','女','2006-11-01',12,'G0105',91.85,'T0007'),('S20180069','习蓝尹','女','2008-01-09',10,'G0105',45.16,NULL),('S20180070','张琼莉','女','2005-06-26',13,'G0105',53.98,'T0007'),('S20180071','张芸馨','男','2007-06-13',11,'G0105',52.19,'T0007'),('S20180072','赵诗诗','男','2007-10-18',11,'G0105',98.99,'T0007'),('S20180073','周仁宇','男','2007-09-25',11,'G0105',84.38,'T0007'),('S20170001','陈慧','男','2004-04-28',14,'G0201',94.99,'T0008'),('S20170002','方松阳','男','2005-05-20',13,'G0201',59.76,'T0008'),('S20170003','方以晴','女','2004-12-31',14,'G0201',85.55,'T0008'),('S20170004','方悦倩','女','2004-10-11',14,'G0201',89.94,'T0008'),('S20170005','付朗丽','女','2004-01-24',14,'G0201',47.50,'T0008'),('S20170006','胡耀幻','男','2004-11-09',14,'G0201',70.94,'T0008'),('S20170007','胡宇诚','男','2006-04-12',12,'G0201',75.63,'T0008'),('S20170008','黄路','男','2003-10-01',15,'G0201',77.91,'T0008'),('S20170009','李姗','男','2003-01-15',15,'G0201',29.00,'T0008'),('S20170010','梅芝','女','2004-11-05',14,'G0201',92.32,'T0008'),('S20170011','牛玥薇','女','2004-04-11',14,'G0201',56.98,'T0008'),('S20170012','田家赫','男','2004-10-07',14,'G0201',56.15,'T0008'),('S20170013','王泊君','男','2005-02-21',13,'G0201',68.77,'T0008'),('S20170014','王博毅','女','2006-05-02',12,'G0201',28.73,'T0008'),('S20170015','张怡萍','男','2003-04-19',15,'G0201',93.81,'T0008'),('S20170016','周昊然','男','2004-07-01',14,'G0201',47.86,'T0008'),('S20170017','周义杰','男','2004-08-23',14,'G0201',63.41,'T0008'),('S20170018','方鸿晨','男','2006-10-09',12,'G0202',90.16,'T0009'),('S20170019','方逸','女','2005-01-10',13,'G0202',60.56,'T0009'),('S20170020','方曼','女','2003-09-13',15,'G0202',59.72,'T0009'),('S20170021','付晶灵','女','2003-07-22',15,'G0202',79.87,'T0009'),('S20170022','付君洁','女','2005-09-09',13,'G0202',36.51,'T0009'),('S20170023','付西','女','2004-10-28',14,'G0202',72.50,'T0009'),('S20170024','黄建钦','男','2006-02-21',12,'G0202',55.23,'T0009'),('S20170025','黄亦','男','2003-02-28',15,'G0202',58.64,'T0009'),('S20170026','李姗','女','2005-05-25',13,'G0202',61.32,'T0009'),('S20170027','李苑菲','女','2006-07-19',12,'G0202',61.73,'T0009'),('S20170028','任媛钰','女','2006-08-07',12,'G0202',55.37,'T0009'),('S20170029','田晨辰','男','2006-05-26',12,'G0202',56.63,'T0009'),('S20170030','田付玉','男','2005-03-18',13,'G0202',82.40,'T0009'),('S20170031','吴建逾','男','2004-10-28',14,'G0202',99.45,'T0009'),('S20170032','吴明轩','男','2003-08-14',15,'G0202',56.59,'T0009'),('S20170033','吴琦玮','男','2006-08-16',12,'G0202',84.58,'T0009'),('S20170034','习蜜原','女','2003-04-28',15,'G0202',53.14,'T0009'),('S20170035','熊静','女','2006-09-15',12,'G0202',57.31,'T0009'),('S20170036','周瑜玥','男','2006-08-26',12,'G0202',61.75,'T0009'),('S20170037','方瑶','女','2004-03-09',14,'G0203',66.24,'T0010'),('S20170038','方明旭','男','2004-11-26',14,'G0203',43.92,'T0010'),('S20170039','方慧','女','2006-02-17',12,'G0203',50.80,'T0010'),('S20170040','方悦露','女','2005-10-30',13,'G0203',64.29,'T0010'),('S20170041','黄泽','男','2005-11-21',13,'G0203',69.06,'T0010'),('S20170042','黄文瀚','男','2005-01-19',13,'G0203',52.43,'T0010'),('S20170043','刘辕','女','2005-06-24',13,'G0203',54.96,'T0010'),('S20170044','牛玥芊','女','2005-04-28',13,'G0203',43.66,'T0010'),('S20170045','任玉倩','女','2003-03-10',15,'G0203',55.25,'T0010'),('S20170046','汪苹琬','女','2005-12-09',13,'G0203',61.10,'T0010'),('S20170047','王思馨','男','2005-04-20',13,'G0203',87.06,'T0010'),('S20170048','吴锦','男','2005-08-12',13,'G0203',10.57,'T0010'),('S20170049','吴麒','男','2005-04-06',13,'G0203',26.10,'T0010'),('S20170050','叶言','女','2004-06-07',14,'G0203',58.79,'T0010'),('S20170051','陈思','男','2004-06-04',14,'G0204',74.28,'T0011'),('S20170052','陈逸轩','男','2004-11-01',14,'G0204',45.55,'T0011'),('S20170053','黄宸铭','男','2004-01-13',14,'G0204',55.82,'T0011'),('S20170054','黄嘉贤','男','2003-07-17',15,'G0204',58.06,'T0011'),('S20170055','黄锦','男','2005-09-14',13,'G0204',80.84,'T0011'),('S20170056','黄子缮','男','2004-12-09',14,'G0204',30.43,'T0011'),('S20170057','牛思静','女','2004-09-06',14,'G0204',62.54,'T0011'),('S20170058','牛烨','女','2005-08-05',13,'G0204',81.71,'T0011'),('S20170059','任盈盈','女','2003-12-09',15,'G0204',72.67,'T0011'),('S20170060','田尹迪','男','2005-01-17',13,'G0204',27.88,'T0011'),('S20170061','汪露涵','女','2006-04-17',12,'G0204',66.87,'T0011'),('S20170062','吴军','男','2005-05-18',13,'G0204',40.66,'T0011'),('S20170063','吴岳腾','男','2003-01-22',15,'G0204',28.55,'T0011'),('S20170064','熊芳','女','2005-02-16',13,'G0204',47.32,'T0011'),('S20170065','叶竹','女','2004-06-23',14,'G0204',54.90,'T0011'),('S20170066','张嘉萱','男','2003-02-03',15,'G0204',55.98,'T0011'),('S20170067','张莎欣','女','2005-12-28',13,'G0204',62.58,'T0011'),('S20170068','方瑞文','男','2003-08-27',15,'G0205',41.17,'T0012'),('S20170069','方馨','女','2005-06-05',13,'G0205',74.66,'T0012'),('S20170070','高子琦','男','2003-03-08',15,'G0205',82.08,'T0012'),('S20170071','胡颖','男','2006-08-09',12,'G0205',55.40,'T0012'),('S20170072','李欣','女','2006-07-25',12,'G0205',69.36,'T0012'),('S20170073','李亚萍','男','2005-01-21',13,'G0205',80.61,'T0012'),('S20170074','刘智','男','2004-07-06',14,'G0205',94.97,'T0012'),('S20170075','王满','女','2003-06-26',15,'G0205',74.11,'T0012'),('S20170076','王思馨','女','2005-11-23',13,'G0205',80.20,'T0012'),('S20170077','王复贤','男','2003-02-26',15,'G0205',38.50,'T0012'),('S20170078','叶芸菲','女','2004-01-07',14,'G0205',69.12,'T0012'),('S20170079','周愉迅','男','2003-07-30',15,'G0205',50.76,'T0012'),('S20170080','方俪','女','2005-12-06',13,'G0206',91.46,'T0013'),('S20170081','方彤','女','2006-01-20',12,'G0206',54.80,'T0013'),('S20170082','方欣庆','女','2004-05-15',14,'G0206',54.78,'T0013'),('S20170083','黄雨彤','男','2006-09-24',12,'G0206',64.64,'T0013'),('S20170084','李定轩','男','2004-08-23',14,'G0206',75.96,'T0013'),('S20170085','李辉','男','2005-01-26',13,'G0206',85.90,'T0013'),('S20170086','李雨桐','男','2006-05-08',12,'G0206',36.38,'T0013'),('S20170087','刘思竹','女','2003-08-01',15,'G0206',50.39,'T0013'),('S20170088','吴佳音','男','2004-12-01',14,'G0206',63.77,'T0013'),('S20170089','习芦','女','2003-12-06',15,'G0206',84.23,'T0013'),('S20170090','叶科妙','女','2003-12-30',15,'G0206',79.90,'T0013'),('S20160001','陈勃豪','男','2002-01-25',16,'G0301',82.01,'T0014'),('S20160002','方子介','男','2004-05-10',14,'G0301',94.43,'T0014'),('S20160003','胡浩轩','男','2004-08-19',14,'G0301',74.50,'T0014'),('S20160004','刘鑫源','男','2003-03-09',15,'G0301',74.99,'T0014'),('S20160005','牛芬芬','女','2003-01-01',15,'G0301',80.00,'T0014'),('S20160006','牛鹦','女','2002-06-01',16,'G0301',75.02,'T0014'),('S20160007','王雨微','男','2003-03-02',15,'G0301',53.40,'T0014'),('S20160008','吴飞酿','男','2002-07-20',16,'G0301',50.54,'T0014'),('S20160009','王傲','男','2005-05-06',13,'G0301',53.13,'T0014'),('S20160010','吴睿','男','2002-01-30',16,'G0301',85.03,'T0014'),('S20160011','吴伟婷','男','2005-05-07',13,'G0301',83.15,'T0014'),('S20160012','熊小凤','女','2003-07-14',15,'G0301',60.68,'T0014'),('S20160013','张蓝一','女','2004-09-07',14,'G0301',53.94,'T0014'),('S20160014','周睿','男','2003-11-12',15,'G0301',87.65,'T0014'),('S20160015','陈佳','男','2004-05-11',14,'G0302',76.44,'T0015'),('S20160016','方婉','女','2004-03-05',14,'G0302',35.58,'T0015'),('S20160017','方娅','女','2002-10-04',16,'G0302',66.87,'T0015'),('S20160018','方艳灵','女','2003-05-26',15,'G0302',76.63,'T0015'),('S20160019','付晶辉','女','2004-09-08',14,'G0302',82.54,'T0015'),('S20160020','刘益帆','男','2004-04-29',14,'G0302',82.83,'T0015'),('S20160021','牛思静','女','2002-07-16',16,'G0302',66.54,'T0015'),('S20160022','牛棋','女','2003-10-09',15,'G0302',84.18,'T0015'),('S20160023','牛月莺','女','2005-05-02',13,'G0302',30.74,'T0015'),('S20160024','吴妙妍','男','2002-04-26',16,'G0302',53.87,'T0015'),('S20160025','习珏旖','女','2004-08-03',14,'G0302',43.78,'T0015'),('S20160026','俞钰汝','女','2004-02-02',14,'G0302',65.85,'T0015'),('S20160027','付兰英','女','2005-08-07',13,'G0303',55.84,'T0016'),('S20160028','付乐然','女','2003-10-28',15,'G0303',66.38,'T0016'),('S20160029','刘滢','女','2003-06-02',15,'G0303',93.54,'T0016'),('S20160030','刘尚猛','男','2004-08-02',14,'G0303',68.57,'T0016'),('S20160031','牛菀','女','2004-09-20',14,'G0303',62.24,'T0016'),('S20160032','牛欣','女','2003-11-24',15,'G0303',52.06,'T0016'),('S20160033','牛萁','女','2005-06-02',13,'G0303',54.34,'T0016'),('S20160034','田峻熙','男','2003-05-16',15,'G0303',86.53,'T0016'),('S20160035','王国珍','男','2004-08-10',14,'G0303',50.58,'T0016'),('S20160036','王君','女','2003-01-06',15,'G0303',94.96,'T0016'),('S20160037','吴承眧','男','2002-03-08',16,'G0303',65.04,'T0016'),('S20160038','吴岩滨','男','2004-11-14',14,'G0303',67.60,'T0016'),('S20160039','熊诗茹','女','2003-12-09',15,'G0303',43.89,'T0016'),('S20160040','叶帆','女','2003-03-06',15,'G0303',70.68,'T0016'),('S20160041','方飘峤','女','2004-12-31',14,'G0304',79.91,'T0017'),('S20160042','方小霞','女','2005-06-05',13,'G0304',73.51,'T0017'),('S20160043','付珺娅','女','2003-03-26',15,'G0304',94.91,'T0017'),('S20160044','胡辰','男','2003-11-03',15,'G0304',71.06,'T0017'),('S20160045','黄金轩','男','2005-08-24',13,'G0304',70.57,'T0017'),('S20160046','李晨','男','2002-09-18',16,'G0304',44.40,'T0017'),('S20160047','李叶妃','女','2004-09-05',14,'G0304',86.70,'T0017'),('S20160048','牛思静','女','2005-04-22',13,'G0304',41.00,'T0017'),('S20160049','牛鹊左','女','2003-06-17',15,'G0304',50.67,'T0017'),('S20160050','田元','男','2004-06-04',14,'G0304',80.42,'T0017'),('S20160051','吴浩','男','2002-10-04',16,'G0304',51.92,'T0017'),('S20160052','吴渝','男','2005-08-09',13,'G0304',53.18,'T0017'),('S20160053','叶瑾萱','女','2003-10-07',15,'G0304',76.50,'T0017'),('S20160054','叶毛豆','女','2004-02-07',14,'G0304',63.16,'T0017'),('S20160055','俞莺','女','2003-02-17',15,'G0304',86.29,'T0017'),('S20160056','张浩楠','男','2003-05-12',15,'G0304',63.36,'T0017');

过滤数据

过滤数据：关键字WHERE

SELECT 字段列表 FROM 表名 WHERE 过滤条件; 过滤条件一般由要过滤的字段、操作符、限定值三部分组成； 如： SELECT student_id,student_name FROM student WHERE gender = '男';

常用操作符如下：

过滤单个值

SELECT student_name, age FROM student WHERE age > 14

SELECT student_id, student_name, age FROM student WHERE student_name = '黄路'

SELECT * FROM student WHERE birth_day <= '2005-12-31'

过滤null 值

SELECT * FROM student WHERE age IS NOT NULL
SELECT * FROM student WHERE class_id IS NULL
# 错误写法：
`SELECT * FROM student WHERE class_id = NULL`

过滤集合BETWEEN IN NOT IN

SELECT * FROM student WHERE age BETWEEN 10 AND 15
SELECT * FROM student WHERE birth_day BETWEEN '2005-10-01' AND '2005-12-31'

SELECT * FROM student WHERE age IN (10,11)
SELECT * FROM student WHERE student_name IN ('小王','小李')
SELECT * FROM student WHERE student_id NOT IN ('004','008')

使用通配符过滤数据LIKE

基本所有的数据库都支持百分号通配符和下划线通配符，但只有很少的数据库支持方括号通配符。

# 如何取出所有姓陈的同学
SELECT * FROM student WHERE student_name LIKE '陈％'

SELECT * FROM student WHERE student_name LIKE '陈_';
SELECT * FROM student WHERE student_name LIKE '陈％';
SELECT * FROM student WHERE student_name LIKE '陈_军';

# MySQL不支持：
SELECT * FROM student WHERE student_id LIKE 'S200[678]';
SELECT * FROM student WHERE student_id LIKE 'S200[^678]';

使用逻辑操作符组合WHERE子句

# 如何取出所有姓陈的男同学？
SELECT * FROM student WHERE gender = '男' AND student_name LIKE '陈％'
# 如何取出所有姓陈的同学或男同学？
SELECT * FROM student WHERE gender = '男' OR student_name LIKE '陈％'

SELECT * FROM t_orders
WHERE
(
pay_status='1'AND ship_status= 'o'
)
OR
(
pay_status='O'AND ( payment='-1' OR ship_status='0')
)

使用组合WHERE子句时，尽量都使用括号消除歧义

来源：https://liyunxiang.blog.csdn.net/article/details/128048036