条件查询

条件查询分类

• 关系运算：>、<、>=、<=、<>/!=、=
• 逻辑运算：and, or, not
• 其它运算：betwen and, like, in, not in, is null, is not null 等等

用法示例

#查询成绩大于90分的记录 mysql> SELECT * FROM score where degree > 90; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 4 | 103 | 3-105 | 92 | | 8 | 105 | 3-105 | 91 |   #查询3-245班级的成绩 mysql> SELECT * FROM score where cno='3-245'; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 2 | 105 | 3-245 | 75 | | 3 | 109 | 3-245 | 68 |   #查询成绩在80~90之间的记录（包含80 90） mysql> SELECT * FROM score where degree between 80 and 90; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 5 | 105 | 3-105 | 88 |   #查询成绩在80~90之间的记录（不含80 90） mysql> SELECT * FROM score where degree > 80 and degree < 90; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 5 | 105 | 3-105 | 88 |   #查询3-245班级后者成绩大于90分的记录 mysql> SELECT * FROM score where cno='3-245' or degree > 90; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 2 | 105 | 3-245 | 75 | | 3 | 109 | 3-245 | 68 | | 4 | 103 | 3-105 | 92 | | 8 | 105 | 3-105 | 91 |   #查询成绩是86或者91分的记录 mysql> SELECT * FROM score where degree in (86, 91); +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 8 | 105 | 3-105 | 91 |   #查询不是3-245班的成绩记录 mysql> SELECT * FROM score where cno not in ('3-245');  mysql> SELECT * FROM score where cno<>'3-245';  mysql> SELECT * FROM score where cno!='3-245'; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 4 | 103 | 3-105 | 92 | | 5 | 105 | 3-105 | 88 |   #查询成绩非空的记录 mysql> SELECT * FROM score where degree is not null; +----+-----+-------+--------+ | id | sno | cno | degree | +----+-----+-------+--------+ | 1 | 103 | 3-245 | 86 | | 2 | 105 | 3-245 | 75 |   #查询学生姓名以“李”开头的学生记录 mysql> SELECT * FROM student where sname like '李%'; +-----+--------+------+---------------------+-------+----------+ | sno | sname | ssex | sbirthday | class | romemate | +-----+--------+------+---------------------+-------+----------+ | 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 |   #查询学生姓名为两个字且第二个字是“芳”的学生记录 mysql> SELECT * FROM student where sname like '_芳'; +-----+--------+------+---------------------+-------+----------+ | sno | sname | ssex | sbirthday | class | romemate | +-----+--------+------+---------------------+-------+----------+ | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 |   #查询学生姓名中带“丽”的学生记录 mysql> SELECT * FROM student where sname like '%丽%'; +-----+--------+------+---------------------+-------+----------+ | sno | sname | ssex | sbirthday | class | romemate | +-----+--------+------+---------------------+-------+----------+ | 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 108 |  

排序查询

排序查询的关键词为order by 列名1,列名2... desc/asc        desc:按照指定的列降序排列，asc:按照指定列升序排列（默认动作)，order by通常在查询语句最末尾。 排序查询一般和限定行数关键词limit同时使用，当同时使用时在sql语句中的先后顺序为where 限定条件  order by 排序  limit n限定输出行数。 语法：select * from table_name order by column_name1,column_name2 desc/asc;

mysql> select * from score where cno='3-105' order by degree,sno limit 3;
+----+-----+-------+--------+
| id | sno | cno | degree |
+----+-----+-------+--------+
| 7 | 103 | 3-105 | 64 |
| 6 | 109 | 3-105 | 76 |
| 9 | 109 | 3-105 | 78 |