五、条件查询
1、where 子句
where 后面跟一个条件,实现有选择的查询
句式 select * from 表名 where 条件;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wlEVJEuX-1649318547418)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649045268782.png)]
select * from students where studentsNO = '001';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tkY6PWpV-1649318547419)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649049313489.png)]
select name, class from students where age=30 ;
select *或select 字段名 控制了查询返回什样的字段(列)
where 条件 控制了查询返回什么样的记录(行)
2、比较运算符
小于 <
小于等于 <=
大于 >
大于等于 >=
不等于 !=或<>
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6V4dULT2-1649318547422)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649051109959.png)]
select age from students where name = '小乔';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5S7oNFLY-1649318547423)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649051222243.png)]
select * from students where age <=30;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TRkYnCqu-1649318547425)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649051300345.png)]
select * from students where hometown != '北京';
练习
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ykok9edj-1649318547427)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649051739336.png)]
1、 select card from students where studentNo = '007';
2、select * from students where class !='1班';
3、select name , sex from students where age > 25;
3、逻辑运算符
and(与)
有两个条件,条件1 +and+ 条件2,两个条件同时满足
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e4crM4zb-1649318547428)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649052114298.png)]
select * from students where age <30 and sex = '女';
or(或)
有两个条件,条件1 + or + 条件2,两个条件 只要一个满足即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-773fReuo-1649318547430)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649052296801.png)]
select * from students where sex = '女' or class = '1班' ;
not(非)
not 只有一个条件,not 条件,如果条件为满足,not后变为不满足。如果条件为不满足,not后变为满足。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCe9wIbb-1649318547431)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649053137922.png)]
select * from students where not hometown != '天津';
练习
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rIjP7mFC-1649318547432)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649053710038.png)]
1、select * from students where hometown = '河南' or hometown = '河北';
2、select * from students where class ='1班' and hometown ='北京';
3、select * from students where not age = '30岁';
select * from students where age != '30岁';
4、模糊查询 like
like 实现模糊查询
% 表示任意多个任意字符;
_ 表示一个任意字符。
字段名 like '字符%' : 指定字符开始,后面任意多个字符
字段名 like '字符_' : 指定字符开始,后面只有一个字符,几个 _ 就有几个字符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oiHWZPp7-1649318547433)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649063109040.png)]
select * from students where name like '孙%'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VZySQWRL-1649318547434)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649066986869.png)]
select * from students where name like '孙_'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iNl5CeRA-1649318547435)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649067504880.png)]
select * from students where name like '%乔';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pLuz0mFf-1649318547437)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649070919822.png)]
select *from students where name like '%白%';
练习
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7drgJho8-1649318547439)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1649071941761.png)]
1、select * from students where name like '__';
2、select * from students where name like '白' and age > '30岁' ;