select 字段名1,字段名2,字段名3···· from 表名;
eg:select name,grade,gender from student;
3.查询指定的字段
select 字段名1,字段名2,字段名3···· from 表名;(字段指的是部分字段)
eg:select name from student;
4.关系运算符的查询
指定查询条件对数据进行过滤 select *|字段名1,字段名2·· from 表名 where 条件表达式 eg:select * from student where id=1; eg:select * from student where id=1; eg:select * from student where id>5; eg:select * from student where id!=2; eg:select * from student where name=’刘备’; eg:select * from student where grade=98;
5.带in关键字的查询
判断某个字段是否在指定集合中 select *|字段名1,字段名2·· from 表名 where 字段名 [not] in(元素1,元素2···); eg:select * from student where id in(1,2,3); eg:select * from student where gender in(‘男’); eg:select * from student where grade in(100,95,96); eg:select * from student where id not in(1,2,3); eg:select * from student where grade not in(100,95);
6.带between and关键字的查询
判断某个字段的值是否在指定的范围内 select *|字段名1,字段名2···· from 表名 where 字段名 [not] between 值1 and 值2; eg:select * from student where id between 1 and 4; eg:select * from student where grade between 90 and 100; eg:select * from student where grade between 10 and 30; eg:select * from student where id not between 1 and 4; eg:select * from student where grade not between 100 and 90;
7.空值查询
判断字符串是否为空值 select *|字段名1,字段名2,··· from 表名 where 字段名 is [not] null; eg:select * from student where id is not null; eg:select * from student where id is null;
8.带distinct关键字的查询
去掉查询记录中重复的数据 select distinct 字段名 from 表名; eg:select distinct grade from student; eg:select distinct name from student; eg:select distinct id from student;
9.distinct关键字可以作用于多个字段
select distinct 字段名1,字段名2··· from 表名;
eg:select distinct gender,name from student;
10. 带like关键字的查询
对字符串进行模糊查询 select *|字段名1,字段名2··· from 表名 where 字段名 [not] like ‘匹配字符串’; eg:select * from student where name like ‘小明’; eg:select * from student where name not like ‘%小%’;
百分号%通配符 可以匹配任意长度的字符串,包括空字符串 eg:select * from student where name like ‘%备’ eg:select * from student where name like ‘刘%’ eg:select * from student where name like ‘%小%’;
下划线_通配符 可以匹配任意的长度的字符串,包括空字符串 eg:select * from student where name like ‘小_’;
添加一条数据 insert into student(name,grade,gender) values(‘sun%er’,95,’男’); eg:select * from student where name like ‘%\%%’;
11.带and关键字的查询
select *|字段名1,字段名2··· from 表名 where 条件表达式1 and 条件表达式2[···and 条件表达式n];
eg:select * from student where id=1 and id=2
eg:select * from student where id>1 and grade<60;
12.带or关键字的多条件查询
select *|字段名1,字段名2··· from 表名 where 条件表达式1 or 条件表达式2[···or 条件表达式n]
eg:select * from student where id>1 or grade <60;