MySQL学习笔记之二

作者: 8ba7c349861d | 来源:发表于2019-01-17 12:11 被阅读1次

    数据库的操作总结就是:增删改查(CURD),今天记录一下基础的检索查询工作。

    检索MySQL

    1.查询表中所有的记录

    mysql> select * from apps;
    +----+------------+-----------------------+---------+
    | id | app_name   | url                   | country |
    +----+------------+-----------------------+---------+
    |  1 | QQ APP     | http://im.qq.com      | CN      |
    |  2 | 微博 APP   | http://weibo.com      | CN      |
    |  3 | 淘宝 APP   | http://www.taobao.com | CN      |
    +----+------------+-----------------------+---------+
    3 rows in set (0.00 sec)
    

    2. 查询表中某列(单列)的记录

    mysql> select app_name from apps;
    +------------+
    | app_name   |
    +------------+
    | QQ APP     |
    | 微博 APP   |
    | 淘宝 APP   |
    +------------+
    3 rows in set (0.00 sec)
    

    3.检索表中多列的记录,列名之间用逗号分开

    mysql> select id, app_name from apps;
    +----+------------+
    | id | app_name   |
    +----+------------+
    |  1 | QQ APP     |
    |  2 | 微博 APP   |
    |  3 | 淘宝 APP   |
    +----+------------+
    3 rows in set (0.00 sec)
    

    4.检索不重复的记录,distinct关键字

    mysql> select * from apps;
    +----+------------+-----------------------+---------+
    | id | app_name   | url                   | country |
    +----+------------+-----------------------+---------+
    |  1 | QQ APP     | http://im.qq.com      | CN      |
    |  2 | 微博 APP   | http://weibo.com      | CN      |
    |  3 | 淘宝 APP   | http://www.taobao.com | CN      |
    |  4 | QQ APP     | http://im.qq.com      | CN      |
    +----+------------+-----------------------+---------+
    4 rows in set (0.00 sec)
    上面表中是所有的数据,可以看到第1条和第4条数据是一样的,如果想要检索时不想出现重复的数据,可以使用distinct关键字,并且需要放在所有列的前面
    mysql> select distinct app_name from apps;
    +------------+
    | app_name   |
    +------------+
    | QQ APP     |
    | 微博 APP   |
    | 淘宝 APP   |
    +------------+
    3 rows in set (0.00 sec)
    

    5.限制检索记录的数量, limit关键字

    mysql> select * from apps limit 2;
    +----+------------+------------------+---------+
    | id | app_name   | url              | country |
    +----+------------+------------------+---------+
    |  1 | QQ APP     | http://im.qq.com | CN      |
    |  2 | 微博 APP   | http://weibo.com | CN      |
    +----+------------+------------------+---------+
    2 rows in set (0.00 sec)
    
    limit后面可以跟两个值, 第一个为起始位置, 第二个是要检索的行数
    mysql> select * from apps limit 1, 2;
    +----+------------+-----------------------+---------+
    | id | app_name   | url                   | country |
    +----+------------+-----------------------+---------+
    |  2 | 微博 APP   | http://weibo.com      | CN      |
    |  3 | 淘宝 APP   | http://www.taobao.com | CN      |
    +----+------------+-----------------------+---------+
    2 rows in set (0.00 sec)
    
    

    5.limit关键字和offset配合使用

    limit可以配合offset使用, 如limit 2 offset 1(从行1开始后的2行,默认行数的角标为0)
    mysql> select * from apps limit 2 offset 1;
    +----+------------+-----------------------+---------+
    | id | app_name   | url                   | country |
    +----+------------+-----------------------+---------+
    |  2 | 微博 APP   | http://weibo.com      | CN      |
    |  3 | 淘宝 APP   | http://www.taobao.com | CN      |
    +----+------------+-----------------------+---------+
    2 rows in set (0.00 sec)
    

    我的网站:https://wayne214.github.io

    相关文章

      网友评论

        本文标题:MySQL学习笔记之二

        本文链接:https://www.haomeiwen.com/subject/zhtudqtx.html