mysql-- 根据条件查询

创建表15并插入数据

drop table if exists students15;
create table students15(
studentNo int(3) unsigned zerofill  PRIMARY KEY ,
name varchar (20),
sex varchar(2),
age int ,
hometown varchar(50),
class varchar(10),
card varchar(50),
isdelect int default 0
);

insert into students15 value 
('1','王昭君',  '女',20,'北京','1班','31162100000910018',0),
('2','诸葛亮',  '男',18,'上海','2班','87235958214647020',0),
('3','刘备',    '男',24,'南京','1班','98230764940128343',0),
('4','关羽',    '男',22,'安徽','4班','72898231636783201',0),
('5','张飞',    '男',19,'河南','3班','12872396253622590',0),
('6','孙尚香',  '女',18,'河北','1班','32276853291927386',0),
('7','百里玄策','男',20,'山西','2班','69283154237972492',0),
('8','小乔',    '女',18,'河南','3班','12872396253622359',0),
('9','大乔',    '女',19,'河南','3班','12872396253622358',0),
('10','妲己',   '女',19,'陕西','1班','77538912786375823',0),
('11','李白',   '男',24,'山东','3班','13467923823989730',0),
('12','孙膑',   '男',22,'陕西','4班','13729023873820233',0);

 1.查询所有字段

select * from students15;

2.查询指定字段

select name,age from students15;

 

3.查询指定字段(多个表情况)

select students15.studentNo,students15.name from students15;

4.查询指定字段  用as给表名起别名 单表可省略

select st15.name, st15.card from students15 as st15;

5.查询指定字段  用as给字段名起别名 

select studentNo as 序号, name as 姓名, hometown as 城镇 from  students15;

6.消除重复行

select distinct class from students15;

7.消除重复行 用as给表名起别名

select distinct  st15.sex, st15.class from students15 as st15;

8.消除重复行 用as给表名起别名

select distinct  class as 班级 from students15;

 where 比较运算符

9.查询关羽年龄

select age  from students15 where name='关羽';

10.查询小于20岁以下的学生

select * from students15 where age<20;

 11.查询城镇不在河南的学生

select * from students15 where hometown<>'河南';

 逻辑运算符 

12.查询小于20岁的女同学

select * from students15 where age<20 and sex='女';

13.查询女同学或者1班的同学

select * from students15 where sex='女' or class='1班';

14.查询非1班的同学 

select * from students15 where not class='1班';