mysql常用查询（1）

Day2  

查询-1

#1.查询所有数据
SELECT * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 张一   | 男   | 1999-10-01 00:00:00 | 95033 |
| 102 | 张二   | 男   | 1999-10-02 00:00:00 | 95033 |
| 103 | 张三   | 女   | 1999-10-03 00:00:00 | 95031 |
| 104 | 张四   | 女   | 1999-10-04 00:00:00 | 95031 |
| 105 | 张五   | 男   | 1999-10-05 00:00:00 | 95032 |
| 106 | 张六   | 女   | 1999-10-07 00:00:00 | 95032 |
| 107 | 张七   | 男   | 1999-10-07 00:00:00 | 95034 |
| 108 | 张八   | 男   | 1999-10-08 00:00:00 | 95034 |
+-----+--------+------+---------------------+-------+

#2.查询某些数据,如sex,name,class
SELECT ssex,sname,class from student;
+------+--------+-------+
| ssex | sname  | class |
+------+--------+-------+
| 男   | 张一   | 95033 |
| 男   | 张二   | 95033 |
| 女   | 张三   | 95031 |
| 女   | 张四   | 95031 |
| 男   | 张五   | 95032 |
| 女   | 张六   | 95032 |
| 男   | 张七   | 95034 |
| 男   | 张八   | 95034 |
+------+--------+-------+

#3.查询不重复的数据，使用distinct关键字
SELECT DISTINCT class from student;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
| 95032 |
| 95034 |
+-------+

#4.查询区间
SELECT * from score where dgree BETWEEN 60 AND 80;
+-----+-------+-------+
| sno | cno   | dgree |
+-----+-------+-------+
| 101 | 3-102 |    75 |
| 102 | 3-102 |    65 |
| 102 | 3-103 |    76 |
| 103 | 3-101 |    66 |
| 104 | 3-104 |    63 |
| 104 | 3-101 |    79 |
| 105 | 3-101 |    79 |
| 105 | 3-102 |    76 |
| 107 | 3-104 |    76 |
+-----+-------+-------+
也可以写成degree>60 and degree<80

#5.查询某值记录，使用in关键字
SELECT * from score where dgree in (85,86,87,88,89,90);
+-----+-------+-------+
| sno | cno   | dgree |
+-----+-------+-------+
| 101 | 3-101 |    86 |
| 101 | 3-103 |    88 |
| 102 | 3-104 |    90 |
| 103 | 3-104 |    88 |
| 104 | 3-103 |    87 |
| 106 | 3-104 |    90 |
| 108 | 3-103 |    88 |
+-----+-------+-------+

#查询student表中‘95031’班或者性别为'女'的同学记录
SELECT * FROM student where class ='95031' or ssex='女';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 张三   | 女   | 1999-10-03 00:00:00 | 95031 |
| 104 | 张四   | 女   | 1999-10-04 00:00:00 | 95031 |
| 106 | 张六   | 女   | 1999-10-07 00:00:00 | 95032 |
+-----+--------+------+---------------------+-------+

#7.以class降序查询student表中所有记录,DESC(降序),ASC（升序）
SELECT * from student order by class DESC;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 107 | 张七   | 男   | 1999-10-07 00:00:00 | 95034 |
| 108 | 张八   | 男   | 1999-10-08 00:00:00 | 95034 |
| 101 | 张一   | 男   | 1999-10-01 00:00:00 | 95033 |
| 102 | 张二   | 男   | 1999-10-02 00:00:00 | 95033 |
| 105 | 张五   | 男   | 1999-10-05 00:00:00 | 95032 |
| 106 | 张六   | 女   | 1999-10-07 00:00:00 | 95032 |
| 103 | 张三   | 女   | 1999-10-03 00:00:00 | 95031 |
| 104 | 张四   | 女   | 1999-10-04 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
#8.以cno升序，degree降序查询score表
SELECT * from score order by sno ASC,dgree DESC;
+-----+-------+-------+
| sno | cno   | dgree |
+-----+-------+-------+
| 101 | 3-103 |    88 |
| 101 | 3-101 |    86 |
| 101 | 3-102 |    75 |
| 102 | 3-104 |    90 |
| 102 | 3-103 |    76 |
| 102 | 3-102 |    65 |
| 103 | 3-103 |    99 |
| 103 | 3-104 |    88 |
| 103 | 3-101 |    66 |
| 104 | 3-103 |    87 |
| 104 | 3-101 |    79 |
| 104 | 3-104 |    63 |
| 105 | 3-103 |    99 |
| 105 | 3-101 |    79 |
| 105 | 3-102 |    76 |
| 106 | 3-104 |    90 |
| 107 | 3-104 |    76 |
| 108 | 3-103 |    88 |
+-----+-------+-------+