DML语言详细梳理--select

查询语句

--union
select * from sunny union all select * from deptno;   --无法执行。
select stid,name from sunny union select stid,name from sunny2;
select stid,name from sunny union all select stid,name from sunny2;

--between and
select * from sunny where grade between 50 and 75;
select * from sunny where age between 18 and 26;
select * from sunny where age not between 18 and 20;

--in
select * from sunny where grade in ('73','59','88');
select * from sunny where grade ='73' or grade ='59' or grade ='88';
select * from sunny where grade not in ('73','59','88');

--\通配符
select * from sunny where name like 'Joke\_sun' escape '\';

--is null
select * from sunny where age is null;
select * from sunny where age is not null;

--order by desc asc

--distinct 去重
select count(distinct age) from sunny;
select count(age) from sunny;

--count(),avg(),sum(),max(),min()
select count(*),max(age),min(age),avg(grade),sum(grade),max(grade),min(grade) from sunny;

--求分数大于平均分数的人的信息
select * from sunny where grade > (select avg(grade) from sunny );
select * from sunny where grade > avg(grade);   --无法执行

--求分数最高的人的信息
select * from sunny where grade = (select max(grade) from sunny );

--求分数最低的人的信息
select * from sunny where grade = (select min(grade) from sunny );

-- 连接字符串
select stid ||' grade is '|| grade from sunny;

--使用列的别名
select age as "年龄" ,grade, grade*10  "年总分" from sunny;

--连接字符串（||）
SELECT stid  || ' grade is  ' || grade ||' 哈~ '||  name FROM sunny;

--like
select * from sunny where name like 's%';

--or
select * from sunny where name ='Manny' or grade >70;