mysql--模糊、范围、空判断

创建表16并插入数据

drop table  if exists  students16;
CREATE table students16(
studentNo int(3) unsigned zerofill  PRIMARY KEY ,
name varchar (20),
sex varchar(2),
age TINYINT(3) ,
hometown varchar(50)  DEFAULT NULL, 
class varchar(10)  DEFAULT NULL,
card varchar(50) DEFAULT NULL
);

insert  into students16 VALUES
('1','王昭君','女',20,'北京','1班','31162100000910018'),
('2','诸葛亮','男',18,'上海','2班','87235958214647020'),
('3','刘玄德', '男',24,'南京','1班','98230764940128343'),
('4','白起','男',22,'安徽','4班','72898231636783201'),
('5','大乔','女',19,'河南','3班','12872396253622358'),
('6','孙尚香','女',18,'河北','1班','32276853291927386'),
('7','百里玄策','男',20,'山西','2班','69283154237972492'),
('8','小乔','女',18,'河南','3班',NULL),
('9','百里守约','男',22,'河南','2班',''),
('10','妲己','女',19,'陕西','1班','77538912786375823'),
('11','李白','男',24,'山东','3班','13467923823989730'),
('12','刘禅','男',18,'陕西','4班','13729023873820233');

1.查询姓刘的同学 

select * from students16 WHERE  name like '刘%';

‘%’代表多个字符

2.查询姓刘且名字是一个字的同学

select * from students16 WHERE  name like '刘_';

一个  ‘_’  代表一个字符 

3.查询名叫乔的同学

select * from students16 where name like '_乔';

4.查询姓名中含白的同学

select * from  students16 where name like '%白%';

5.查询城镇为河南或河北或陕西的同学

select * from students16  where hometown in ('河南','河北','陕西');

6.查询年龄18到20的同学

select * from students16 where age BETWEEN 18 and 20 ;

7.查询身份证没有的同学

select * from students16 where card  is null;

 8.查询填写身份证的同学

select * from students16 where card is not null ;