MySql（3）-select

SELECT * FROM result

--去重 DISTINCT
SELECT `studentno` FROM result
--DISTINCT  去重，去除SELECT查询出来的重复的数据
SELECT DISTINCT `studentno` FROM result

SELECT VERSION()  --查询系统版本
SELECT 100*2-1 AS 结果  --用来计算
SELECT @@auto_increment_increment  --查询自增的步长(变量)
--学员考试成绩全部加一
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result

--=====================  WHERE ========================
--查询考试成绩在90到95之间的学生的学号和成绩
SELECT `studentno`,`studentresult` FROM result 
    WHERE studentresult>=95 AND studentresult<=100
    
SELECT `studentno`,`studentresult` FROM result 
    WHERE studentresult BETWEEN 95 AND 100 

--查询除了1000号的学生的成绩
SELECT `studentno`,`studentresult` FROM result
    WHERE studentno!=1000 AND studentno!=1001
    
SELECT `studentno`,`studentresult` FROM result
    WHERE NOT studentno=1000

 

--=====================  模糊查询 ========================
--查询名字以夜开头的     %(任意字符)   _(一个字符)  与LIKE匹配使用
SELECT `studentno`,`studentname` FROM `student`
    WHERE `studentname` LIKE '夜%' 
    
SELECT `studentno`,`studentname` FROM `student`
    WHERE `studentname` LIKE '张_'
    
SELECT `studentno`,`studentname` FROM `student`
    WHERE `studentname` LIKE '%雨%'
    
--===============IN 具体的一个或多个值==================
SELECT `studentno`,`studentname` FROM `student`
    WHERE `studentno` IN (1000,1001,1002)
    
--查询地址为空的学生  IS NULL     IS NOT NULL 
SELECT `studentno`,`studentname` FROM `student`
    WHERE address='' OR address IS NULL 

 

--===============连表查询==============
--查询参加了考试的学生的学号、姓名、科目编号、分数
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
JOIN result AS r
ON s.studentno = r.studentno

--RIGHT JOIN(右外连接)    LEFT JOIN（左外连接）
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
RIGHT JOIN result AS r
ON s.studentno = r.studentno

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
LEFT JOIN result AS r
ON s.studentno = r.studentno

--同时连接三个表  学号、学生姓名、科目名、分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s 
JOIN result AS r 
JOIN `subject` AS sub
WHERE s.studentno = r.studentno 
  AND r.subjectno = sub.subjectno