查询语句
--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;