补充:如果起的别名是中文,要用 '别名'括起来
P10:条件查询
1.不是将表中所有的数据都查出来,是查询出来符合条件的。
语法格式:
select
字段一,字段二....
from
表名
where
条件;
入果要查询是字符串的话要用 '字符串名' 括起来
= 等于
查询学号为2007050202的同学信息
mysql> select sno,sname from student where sno=2007050202;
+------------+----------+
| sno | sname |
+------------+----------+
| 2007050202 | 王扬贺 |
+------------+----------+
1 row in set
<>或!=不等于
查询学号不为2007050202的同学信息
ysql> select sno,sname from student where sno!=2007050202;
+------------+----------+
| sno | sname |
+------------+----------+
| 2005020301 | 王小龙 |
| 2007010101 | 于田田 |
| 2007010103 | 王洪利 |
>大于
查询成绩大于80的同学学号
mysql> select * from sc;
+------------+------+--------+
| sno | cno | degree |
+------------+------+--------+
| 2007010101 | a01 | 67 |
| 2007010101 | a02 | 80 |
| 2007010101 | a03 | 92 |
| 2007010103 | a01 | 95 |
mysql> select sno from sc where degree > 80;
+------------+
| sno |
+------------+
| 2007010101 |
| 2007010103 |
| 2007010103 |
| 2007010103 |
between...and... 两个值之间
查询成绩在80-90之间的学生信息
1. >= and <= (and是并且的意思)
mysql> select sno from sc where degree > 80;
+------------+
| sno |
+------------+
| 2007010101 |
| 2007010103 |
| 2007010103 |
| 2007010103 |
| 2007010104 |
| 2007010104 |
2.between... and...
select
sno,cno
from
sc
where
degree between 80 and 90;
+------------+------+
| sno | cno |
+------------+------+
| 2007010101 | a02 |
| 2007010103 | a03 |
| 2007010104 | a01 |
| 2007010104 | a02 |
| 2007010105 | a02 |
| 2007010105 | a03 |
| 2007010106 | a03 |
is null为null
查询哪些同学的成绩为空?
mysql> select sno,cno,degree from sc where degree is null;
+------------+------+--------+
| sno | cno | degree |
+------------+------+--------+
| 2007010115 | c06 | NULL |
| 2007030126 | a02 | NULL |
| 2007030136 | c01 | NULL |
| 2007030421 | a03 | NULL |
| 2007030431 | c01 | NULL |
+------------+------+--------+
5 rows in set
逻辑运算符
and 并且
查询学号为2007030447并且成绩为78的同学
mysql> select sno,degree from sc where sno='2007030447' and degree=78;
+------------+--------+
| sno | degree |
+------------+--------+
| 2007030447 | 78 |
+------------+--------+
1 row in set
or 或者
查询