第03章 单表检索

--查询全部的行和列
SELECT * FROM SALES


SELECT * FROM tbl_studentinfo 


SELECT stuname FROM tbl_studentinfo 


SELECT DISTINCT(stuname) FROM tbl_studentinfo 


select UNIQUE(stuname) from tbl_studentinfo
WHERE stuno ='1001'


select distinct stuname, stuno from  tbl_studentinfo


select distinct stusex from (select distinct stuname,stusex from tbl_studentinfo)


SELECT   stuname,stusex,stuaddr 
FROM tbl_studentinfo
WHERE stusex = 0 
ORDER BY stuname
--别名的使用
select a.stuno from tbl_studentinfo a;


SELECT a.stuno ,a.stuname name  FROM tbl_studentinfo A;
--使用AS来命名列
SELECT a.stuno  学员编号,stuname as 学员姓名,stuaddr as 学员地址  
FROM tbl_studentinfo  a
WHERE stuname <> '张三'


SELECT stu.stuno  学员编号,stuname as 学员姓名,stuaddr as 学员地址  
FROM tbl_studentinfo stu




--别名的使用,使用AS来命名列
SELECT a.stuno as 学员编号,stuname as 学员姓名,stuaddr as 学员地址  
FROM tbl_studentinfo a


--查询部分行
SELECT stuname,stusex,stuaddr FROM tbl_studentinfo
WHERE stusex = 0;
SELECT stuname,stusex,stuaddr FROM tbl_studentinfo
WHERE stusex <> 0;
--语句中的运算  
--1.查询所有学生成绩加20分以后的结果
select * from Tbl_ScoreInfo;
SELECT score + 20 FROM Tbl_ScoreInfo ;
--2.从学生表中把姓名和学号用’-’连接起来 
SELECT Stuno || ' - ' ||  stuname FROM tbl_studentinfo A;
--限制行
--1.查询所有北京的男生
 SELECT * FROM tbl_studentinfo WHERE stuAddr = '南京市玄武区' AND stusex = 0
--2.查询日语成绩优秀和不及格的学生学号和成绩
select * from tbl_scoreinfo
SELECT STUNO,SCORE FROM tbl_scoreinfo WHERE classno = '1' AND (score >= 80 OR score < 60);
--查询空行
update tbl_studentinfo set stusex =1 where stuno=1001
update tbl_studentinfo set stusex = null where stusex is not null
SELECT * FROM tbl_studentinfo where stutel is not null
SELECT * FROM tbl_studentinfo WHERE stusex IS NULL


--模糊查询—BETWEEN
SELECT stuno, score FROM tbl_scoreinfo WHERE score BETWEEN 65 AND 95;
SELECT stuno, score FROM tbl_scoreinfo WHERE score>=65 and score <=95;
--IN条件关键字
--查询日语和英语成绩不及格的学生学号
SELECT * FROM tbl_scoreinfo WHERE score < 70 and classno IN ('1','2');


SELECT * FROM tbl_scoreinfo WHERE score < 70 and (classno=1 or classno=2) ;
--通配符 “ like ” 任意长度的字符串
--查询地址是江苏的学生信息
SELECT * FROM tbl_studentinfo WHERE stuAddr LIKE '%南京%';


update tbl_studentinfo set stuaddr='南京市玄武区' where stuno=05001;
--通配符” _ “一个字符
SELECT * FROM tbl_StudentInfo 
WHERE stuAddr  LIKE '南京市玄武_';


--IN条件关键字
SELECT stuname AS 学员姓名,stuaddr As 地址 FROM tbl_studentinfo WHERE stuaddr IN ('南京市玄武区','广州','上海');


--查询地址带下划线的学生名和学号
UPDATE tbl_studentinfo 
SET stuAddr = '江苏_南京'
WHERE stuno = '05001';
--查询内容中带有通配符或者数据库特殊字符 使用“ ESCAPE ”
SELECT * FROM tbl_StudentInfo 
WHERE stuAddr  LIKE '%/_%' ESCAPE '/';


select * from tbl_studentinfo where stuaddr like'%_%';--错误
--排序
--检索所有学生信息按照学号降序排序 。
 SELECT * FROM tbl_studentinfo ORDER BY stuno desc;
 SELECT * FROM tbl_studentinfo ORDER BY stusex ASC, stuno DESC;
--升序排列
SELECT stuno As 学员编号,(Score*0.9+5) As 综合成绩
FROM tbl_scoreinfo
WHERE (Score*0.9+5)>60
ORDER BY Score desc


delete from tbl_studentinfo where stuno=1002


--降序排列
SELECT stuno As 学员编号,(Score*0.9+5) As 综合成绩
FROM tbl_scoreinfo
WHERE (Score*0.9+5)>60
ORDER BY Score DESC
--按多列排序
SELECT stuno As 学员编号, Score As 成绩
FROM tbl_scoreinfo
WHERE Score>60
ORDER BY Score,stuno