3.select，in，between 控制查询

CREATE TABLE teacher(
id INT,
name VARCHAR (20),
age INT,
code INT,
score INT
);
INSERT INTO teacher VALUES(1,'李四',25,001,59);
INSERT INTO teacher VALUES(2,'王五',25,002,60);
INSERT INTO teacher VALUES(3,'六',25,003,70);
INSERT INTO teacher VALUES(4,'七',25,004,80);
INSERT INTO teacher VALUES(5,'八',25,005,60);
INSERT INTO teacher VALUES(6,'九',25,006,70);
INSERT INTO teacher VALUES(7,'十',25,007,80);

1.select

#查询所有的数据，显示指定的列
/*
  * : 通配符，表示显示有的列
*/
SELECT *FROM teacher;
SELECT id,name FROM teacher;

#条件查询

#查询 id为4 的学生姓名
SELECT name FROM teacher WHERE id = 4;

#查询名称为李四的教师信息
SELECT *FROM teacher WHERE name = '李四';

2.in

#IN
#SELECT *FROM teacher WHERE id IN(条件1，条件2）；
#查询id值为1,2,3的数据
SELECT *FROM teacher WHERE id IN(1,2,3);


#查询不在指定范围内的记录，去除id为1,2,3的记录
SELECT *FROM teacher WHERE id NOT IN(1,2,3);

3.BETWEEN

#查询id为2~5之间的学生姓名
#BETWEEN
SELECT *FROM teacher WHERE id BETWEEN 2 AND 5;
#查询id 不在 2~5之间的学生姓名
SELECT *FROM teacher WHERE id NOT BETWEEN 2 AND 5;

#空值查询
#查询 scode 为空的记录
SELECT *FROM teacher WHERE score IS NULL;

#查询 scode 不为空的记录
SELECT *FROM teacher WHERE score IS NOT NULL;