2019-08-21 单表查询

--单表查询

select * from student;

select Sno,Sname from Student;

--算术表达式

--查全体学生的姓名及其出生年份。

select Sno,Sname,Sage,2019-Sage from Student;

select year(now())

select Sno,Sname,Sage,year(now())-Sage from Student;

--字符串常量

select Sname,'年龄:',Sage from student;

--函数:字符串函数，日期函数，数学函数

select Sname,'年龄:',Sage ,sdept,LOWER(sdept) from student;

--列别名

select Sname,'年龄:' as 年龄,Sage from student;

select Sname as 姓名,Sage 年龄 from student;

--年龄+10

select Sname as 姓名,Sage+10 年龄 from student;

-- 刘晨  +10,other 不变

select Sname as 姓名,Sage+10 年龄 from student where sname='刘晨'

union

select Sname as 姓名,Sage 年龄 from student where sname<>'刘晨'

-- 》=19 成年，《19 未成年 ???

SELECT * from sc

select Sno from sc;

select all Sno from sc;

-- 消除取值重复的行

select DISTINCT sno from sc;

--查询满足条件的行:where

select * from student  where sage =18

select * from student  where sage >=19

select * from student  where sage !=18

select * from student  where sage <>18

--确定范围  [19,20]

select * from student  where sage  BETWEEN 19 and 20

--确定范围  [18,20)

select * from student  where sage>=18 and sage<20

确定集合 谓词：IN <值表>, NOT IN <值表>

select * from student  where sdept in ('CS','IS')

select * from student  where sdept NOT in ('CS','IS')

字符匹配  谓词：[NOT] LIKE ‘<匹配串>’

匹配串为含通配符的字符串:% _

-- SDEPT 包含 S

SELECT * from student where sdept like '%S%'

SELECT * from student where sdept like 'S%'

SELECT * from student where sno like '%23'

SELECT * from student where sname like '张__'

SELECT * from student where sname like '张_'

涉及空值的查询

is null

查询没有先修课的课程

select * from course  where cpno is null

select * from course  where cpno is not null

多重条件查询

select * from student  where ssex='男' and sage=20 and sdept='cs'

select * from student  where ssex='男' and ssex='女'

select * from student  where ssex='男' or  sdept='ma'

select * from student  where ssex='男' or  sdept='ma' and sage between 18 and 19

21,25  23

21,23,25==>

21

23

25

select * from student  where ssex='男' or  (sdept='ma' and sage between 18 and 19)

select * from student  where (ssex='男' or  sdept='ma') and sage between 18 and 19

21,23,25==>

23

25

select * from student  where ssex='男'

ORDER BY子句

select * from student order by sage desc

select * from student order by sage

select * from student order by sage asc

select * from student order by sage ,sno desc  --年龄升序，学号降序

23,22,25,21

聚集函数

SELECT Count(*) from student

SELECT avg(Sage) from student

SELECT sum(Sage) from student

SELECT max(Sage) from student

SELECT min(Sage) from student

GROUP BY子句

select ssex  男女生,count(ssex)  人数 from student group by ssex

order by count(ssex) desc

select sage  年龄,count(sage)  人数 from student group by sage

order by 人数 desc

=======================================

男女生  人数

男           2

女           2

=======================================???

HAVING

--显示 各个年龄段 人数>=2 的统计结果

select sage  年龄,count(sage)  人数 from student group by sage

having count(sage)>=2