15.查询语句

15.1 查询表的所有数据行

1、命令语法：select <字段 1，字段 2，..> from <表名> where <表达式>
其中，select，from，where 是不能随便改的，是关键字，支持大小写
2、查看表 test 中所有数据
a.进入指定库后查询
mysql> use oldboy
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
| test |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from test;;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | zhangxuan |
| 4 | engchao |
| 5 | geili |
+----+-----------+
5 rows in set (0.00 sec)
ERROR:
No query specified​

 

15.2 查看 mysql 库的用户

mysql> select user,host from mysql.user;
+-----------+--------------------------+
| user | host |
+-----------+--------------------------+
| root | 127.0.0.1 |
| blog | 172.16.1.% |
| oldgirl | 172.16.1.% |
| wordpress | 172.16.1.% |
| oldgirl | 172.16.1.0/255.255.255.0 |
| oldboy | localhost |
| oldgirl | localhost |
| root | localhost |
+-----------+--------------------------+
rows in set (0.00 sec)​

 

15.3 根据指定条件查询表的部分数据

1、例:查看表 test 中前两行数据
执行命令：
mysql> select * from test limit 2;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)
2、例：查看后两行数据
执行命令：
mysql> select * from test limit 1,2;
+----+-----------+
| id | name |
+----+-----------+
| 2 | oldgirl |
| 3 | zhangxuan |
+----+-----------+
2 rows in set (0.00 sec)​

 

15.4 根据固定条件查数据

执行命令：

mysql> select * from test where id=1;
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.05 sec)
mysql> select * from test where name='oldboy'; <-查询字符中要加引号
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)​

 

15.5 指定固定条件范围查数据

执行命令：
mysql> select * from test where id>2 and id<5;
+----+-----------+
| id | name |
+----+-----------+
| 3 | zhangxuan |
| 4 | engchao |
+----+-----------+
2 rows in set (0.00 sec)​

 

15.6 根据顺序查看列数据

正序
mysql> select * from test order by id asc;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | zhangxuan |
| 4 | engchao |
| 5 | geili |
+----+-----------+
5 rows in set (0.00 sec)
倒序
mysql> select * from test order by id desc;
+----+-----------+
| id | name |
+----+-----------+
| 5 | geili |
| 4 | engchao |
| 3 | zhangxuan |
| 2 | oldgirl |
| 1 | oldboy |
+----+-----------+
5 rows in set (0.00 sec)​

 

15.7 在表中根据条件导出数据至文件中

mysql> select * from test where id>2 and id<5 order by id desc into outfile '/tmp/id.txt';
Query OK, 2 rows affected (0.06 sec)
mysql> system cat /tmp/id.txt
4 engchao
3 zhangxuan