一:单表查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女');
1.查询条件
比较
等于 SELECT * FROM score WHERE s_score=80 大于 SELECT * FROM score WHERE s_score>80 小于 SELECT * FROM score WHERE s_score<80 小于等于 SELECT * FROM score WHERE s_score<=80 大于等于 SELECT * FROM score WHERE s_score>=80 不等于 SELECT * FROM score WHERE s_score!=80 不等于 SELECT * FROM score WHERE s_score<>80 不小于 SELECT * FROM score WHERE s_score!<80 不大于 SELECT * FROM score WHERE s_score!>80
范围
在60-80之间的记录 SELECT * FROM score WHERE s_score BETWEEN 60 AND 80 不在60-80之间的记录 SELECT * FROM score WHERE s_score NOT BETWEEN 60 AND 80
in的使用
成绩只要是80的记录 SELECT * FROM score WHERE s_score IN(80) 成绩不是80的其他所有记录 SELECT * FROM score WHERE s_score NOT IN(80) 如果是中文就要用单引号,记得名字要写全不要只写一个云或李 SELECT * FROM student WHERE s_name IN('李云') 姓名不是云的所有记录(因为姓名都是两个字的) SELECT * FROM student WHERE s_name NOT IN('云') 多组的话可以这样查(成绩是80和60的会被查出来,14表中没有就没有了,sql不会出错的) SELECT * FROM score WHERE s_score IN(80,60,14)
like的使用
名字中最后一个字是云的记录 SELECT * FROM student WHERE s_name LIKE '%云' 名字中最先一个字是李的记录 SELECT * FROM student WHERE s_name LIKE '李%' 名字中包含一个字是雷的记录 SELECT * FROM student WHERE s_name LIKE '%雷%' 名字中不包含一个字是云的记录 SELECT * FROM student WHERE s_name NOT LIKE '%云%' 名字中类似第二个字是云的且只有两个字的记录 SELECT * FROM student WHERE s_name LIKE '_云'
空值
学生表中名字这个字段是null的记录 SELECT * FROM student WHERE s_name IS NULL 学生表中名字这个字段不是null的记录 SELECT * FROM student WHERE s_name IS NOT NULL
and与or
id大于3且性别是男的 SELECT * FROM student WHERE s_id>3 AND s_sex='男' 要么是id大于3的,要么性别是男,两者符合一个就行,都符合当然也行 SELECT * FROM student WHERE s_id>3 OR s_sex='男'
order by
通常是用做对查询结果按照一个或多个属性进行升序、降序
按照成绩(s_score)升序,成绩相同的按照id(s_id)升序 SELECT * FROM score ORDER BY s_score,s_id ASC 降序是 DESC
distinct 与 group by 两者都有去重的能力
去重后的成绩
SELECT DISTINCT s_score FROM score 分组(分组后是去重的)后的成绩与其对应的id SELECT s_score,s_id FROM score GROUP BY s_score
结论:distinct只适用于查询不重复记录的条数,例如select coun(distinct s_score) from score 代表不重复的成绩记录有多少条
SELECT DISTINCT s_score,s_id FROM score 这样会变成成绩与id都相同的才会去重
SELECT s_id,DISTINCT s_score FROM score 而这样mysql会报错
只有group by适合查询一整条不重复记录的数据
聚集函数
count 统计条数 select count(*) from score;select count(distinct s_score) from score; distinct不写的话默认是all
sum 计算某一列的总和(必须是数值型)select sum(s_score) from score
avg 计算某一列的平均值(必须是数值型)select avg(s_score) from score
max 求某一列的最大值 select max(s_score) from score
min 求某一列的最小值 select min(s_score) from score
值得一提的是,计算所有老师的平均工资可以这样 select avg(salary) from salary
如果要计算每个院系的老师的平均工资就要分组 select dept_name,avg(salary) from salary group by dept_name
也就是说(假设) ,把100个老师(共五个院系,每个院系20个老师)分为五组,每组20人,对每组的20人计算平均值,以不同院系区分
如果,select dept_name,id,avg(salary) from salary group by dept_name 中间加个id这样是不可以的,因为id没有被分组除非
select dept_name,id,avg(salary) from salary group by dept_name,id 这样才行
另外group by后面可以接having语句,having可以用聚集函数,where不可以
区别在于,having是分组之后才生效的,where是未分组时生效的