MySQL必知必会 -- 数据检索

SELECT语句

MariaDB [test]> select * from linux;		/检索所有列
+-------+--------+------+		
| user  | passwd | sex  |
+-------+--------+------+
| user1 | 111    | boy  |
| user2 | 222    | girl |
+-------+--------+------+
2 rows in set (0.00 sec)

MariaDB [test]> select sex from linux;	利用SELECT语句从linux表中检索一个名为sex的列
+------+
| sex  |
+------+
| boy  |
| girl |
+------+
2 rows in set (0.00 sec)

MariaDB [test]> select user,passwd from linux;		/检索多个列
+-------+--------+
| user  | passwd |
+-------+--------+
| user1 | 111    |
| user2 | 222    |
+-------+--------+
2 rows in set (0.00 sec)

MariaDB [test]> select sex from linux where user='user1';	/配合where进行条件查找
+-----+
| sex |
+-----+
| boy |
+-----+
1 row in set (0.00 sec)

DISTINCT
使用DISTINCT关键字，返回唯一值：

MariaDB [test]> select * from linux;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| user1 | 111    | boy  |
| user2 | 222    | girl |
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
+-------+--------+------+
5 rows in set (0.01 sec)

MariaDB [test]> select distinct sex from linux;
+------+
| sex  |
+------+
| boy  |
| girl |
+------+
2 rows in set (0.00 sec)
MariaDB [test]> select distinct user,sex from linux;
+-------+------+
| user  | sex  |
+-------+------+
| user1 | boy  |
| user2 | girl |
| user3 | boy  |		/在指定多行的时候会这样显示，因为把每行看做一个单位的话，他们都不一样
| user4 | boy  |
| user5 | boy  |
+-------+------+
5 rows in set (0.00 sec)

限制结果 LIMIT

MariaDB [test]> select distinct user,sex from linux;
+-------+------+
| user  | sex  |
+-------+------+
| user1 | boy  |
| user2 | girl |
| user3 | boy  |
| user4 | boy  |
| user5 | boy  |
+-------+------+
5 rows in set (0.00 sec)

MariaDB [test]> select distinct user,sex from linux limit 3;	/限制只显示前3行
+-------+------+
| user  | sex  |
+-------+------+
| user1 | boy  |
| user2 | girl |
| user3 | boy  |
+-------+------+
3 rows in set (0.00 sec)

MariaDB [test]> select distinct user,sex from linux limit 2;	/限制只显示前两行
+-------+------+
| user  | sex  |
+-------+------+
| user1 | boy  |
| user2 | girl |
+-------+------+
2 rows in set (0.00 sec)

MariaDB [test]> select distinct user,sex from linux limit 2,1;		从第2行开始，往后检索一行
+-------+-----+
| user  | sex |
+-------+-----+
| user3 | boy |
+-------+-----+
1 row in set (0.00 sec)
MariaDB [test]> select distinct user,sex from linux limit 2,3;		往后检索3行
+-------+-----+
| user  | sex |
+-------+-----+
| user3 | boy |
| user4 | boy |
| user5 | boy |
+-------+-----+
3 rows in set (0.01 sec)
在行数不够的情况下只会显示能显示的

使用完全限定的表名

MariaDB [test]> select linux.user from test.linux;
+-------+				linux 表的 user列
| user  |				来自 test 数据库的 linux 表
+-------+
| user1 |
| user2 |
| user3 |
| user4 |
| user5 |
+-------+
5 rows in set (0.00 sec)

MariaDB [test]> select user.user from mysql.user;
+------+
| user |
+------+
| root |
| root |
|      |
| aom  |
| root |
|      |
| root |
+------+
7 rows in set (0.00 sec)