Mysql 笔记2

补充：如果起的别名是中文，要用 '别名'括起来

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 或者
查询