MySQL数据过滤语句
▼▼▼
/*创建表(学生表,字段:学号、姓名、班级、分数)*/
CREATE TABLE stu(
number int not null primary key auto_increment,
name varchar(20) not null,
class varchar(20) not null,
score int not null
);
/*插入数据*/
insert into stu values (1,"张三","1班",60);
insert into stu values (2,"赵四","2班",70);
insert into stu values (3,"王五","1班",75);
insert into stu values (4,"田七","1班",72);
insert into stu values (5,"李狗蛋","3班",88);
insert into stu values (6,"大毛","2班",92);
insert into stu values (7,"二毛","3班",53);
/*查询表内数据信息*/
select * from stu
/*单条件查询*/
/*查询张三的成绩*/
select score from stu where name="张三";
/*查询成绩及格的学生信息*/
select * from stu where score >= 60;
/*查询成绩不为60分的学生人数*/
select count(*) from stu where score <> 60;
/*查询1班学生的信息*/
select * from stu where class = "1班";
/*查询成绩在75-90之间的学生信息*/
select * from stu where score between 75 and 90;
/*多条件查询and、or、in、not*/
/*查询一班成绩大于60的学生信息*/
select * from stu where class = "1班" and score > 60;
/*查询二班和三班的学生人数*/
select count(*) from stu where class = "2班" or class="3班";
/*查询一班和二班的所有学生成绩(两种写法)*/
select name,score from stu where class in('1班','2班');
select name,score from stu where class not in('3班');
/*查询一班和二班成绩大于70分的学生信息*/
select * from stu where class in('1班','2班') and score > 70;
-
% :表示任何字符出现任意次数 -
_:只能匹配单个任意字符
/*通配符的使用*/
/*查询所有姓李的同学的信息*/
select * from stu where name like '李%';
/*查询名字第二个字为“毛”的同学的信息*/
select * from stu where name like '_毛';