1.where条件:
逻辑运算符:
and/or/xor(^)/not(!):
and:SELECT * FROM result WHERE studentresult>=80 AND studentresult<=90;
//查询所有大于等于80并且小于等于90的学生信息。
or:SELECT * FROM result WHERE studentresult>=80 or studentresult<=90;
//查询所有大于等于80或者小于等于90的学生信息。
not:SELECT * FROM result WHERE NOT(studentresult>=80);
//查询所有不大于80的学生信息。
xor:SELECT * FROM result WHERE (studentresult>=80) XOR (studentresult<=90); //查询所有>90或者<80的学生成绩
等价于:
(studentresult>=80) AND !(studentresult<=90)
OR
!(studentresult>=80) AND (studentresult<=90)
2.IS NULL/IS NOT NULL:判断字段值是否为null。(前提是此字段允许为null)
select * from student where Address is not NULL;
select * from student where Address is NULL;
3.in:使用in进行范围选择
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
等价于:
SELECT * FROM subject where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
4.LIKE模糊查询:
“%”:表示匹配0或任意多个字符
“_”:表示匹配单个字符
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '%s';
//最后一个字母为s的SubjectName
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '_s_';
//一共三个字母,中间字母为s的subject1信息
5.课堂演示:查询所有姓“李”的学生所有成绩
SELECT StudentNo '李同学学号',studentresult '李同学成绩' FROM result WHERE StudentNo IN (SELECT StudentNo FROM student WHERE StudentName LIKE '郭%');
逻辑运算符:
and/or/xor(^)/not(!):
and:SELECT * FROM result WHERE studentresult>=80 AND studentresult<=90;
//查询所有大于等于80并且小于等于90的学生信息。
or:SELECT * FROM result WHERE studentresult>=80 or studentresult<=90;
//查询所有大于等于80或者小于等于90的学生信息。
not:SELECT * FROM result WHERE NOT(studentresult>=80);
//查询所有不大于80的学生信息。
xor:SELECT * FROM result WHERE (studentresult>=80) XOR (studentresult<=90); //查询所有>90或者<80的学生成绩
等价于:
(studentresult>=80) AND !(studentresult<=90)
OR
!(studentresult>=80) AND (studentresult<=90)
2.IS NULL/IS NOT NULL:判断字段值是否为null。(前提是此字段允许为null)
select * from student where Address is not NULL;
select * from student where Address is NULL;
3.in:使用in进行范围选择
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
等价于:
SELECT * FROM subject where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
4.LIKE模糊查询:
“%”:表示匹配0或任意多个字符
“_”:表示匹配单个字符
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '%s';
//最后一个字母为s的SubjectName
SELECT * FROM SUBJECT1 WHERE SubjectNmae LIKE '_s_';
//一共三个字母,中间字母为s的subject1信息
5.课堂演示:查询所有姓“李”的学生所有成绩
SELECT StudentNo '李同学学号',studentresult '李同学成绩' FROM result WHERE StudentNo IN (SELECT StudentNo FROM student WHERE StudentName LIKE '郭%');