2020年4月28日

    1. SELECT * FROM users WHERE id=1
    2. SELECT * FROM users WHERE id=1 OR password='12345'；
    3. SELECT * FROM users ORDER BY id ASC;
    4. SELECT * FROM users ORDER BY id DESC;
    5. SELECT * FROM users WHERE id>5 ORDER BY password ASC；
    6. SELECT * FROM users ORDER BY id DESC LIMIT 1;
    7. SELECT * FROM users LIMIT 0,2;
    8. SELECT * FROM users LIMIT 2,2;
    9. SELECT * FROM users LIMIT 4,2;
    11. SELECT * FROM users WHERE name LIKE '%狠%'；
    12. SELECT * FROM users WHERE name LIKE '__';
    13. SELECT * FROM users WHERE name LIKE '___';
    14. SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
    15. SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
    17. SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
    18. SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
    19. SELECT * FROM birds LIMIT 0,10;
    21. SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';
    24. SELECT * FROM users WHERE id=1
    25. SELECT * FROM users WHERE id=1 OR password='12345'；
    26. SELECT * FROM users ORDER BY id ASC;
    27. SELECT * FROM users ORDER BY id DESC;
    28. SELECT * FROM users WHERE id>5 ORDER BY password ASC；
    29. SELECT * FROM users ORDER BY id DESC LIMIT 1;
    30. SELECT * FROM users LIMIT 0,2;
    31. SELECT * FROM users LIMIT 2,2;
    32. SELECT * FROM users LIMIT 4,2;
    34. SELECT * FROM users WHERE name LIKE '%狠%'；
    35. SELECT * FROM users WHERE name LIKE '__';
    36. SELECT * FROM users WHERE name LIKE '___';
    37. SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
    38. SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
    40. SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
    41. SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
    42. SELECT * FROM birds LIMIT 0,10;
    44. SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';
    46. SELECT * FROM users WHERE id=1
    47. SELECT * FROM users WHERE id=1 OR password='12345'；
    48. SELECT * FROM users ORDER BY id ASC;
    49. SELECT * FROM users ORDER BY id DESC;
    50. SELECT * FROM users WHERE id>5 ORDER BY password ASC；
    51. SELECT * FROM users ORDER BY id DESC LIMIT 1;
    52. SELECT * FROM users LIMIT 0,2;
    53. SELECT * FROM users LIMIT 2,2;
    54. SELECT * FROM users LIMIT 4,2;
    56. SELECT * FROM users WHERE name LIKE '%狠%'；
    57. SELECT * FROM users WHERE name LIKE '__';
    58. SELECT * FROM users WHERE name LIKE '___';
    59. SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
    60. SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
    62. SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
    63. SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
    64. SELECT * FROM birds LIMIT 0,10;
    66. SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';

    笔记：
    查询语句语法：
    ：所有字段
    查询处users表的所有字段：
    SELECT 字段 FROM 表名；
    SELECT     FROM users；

    查询出users表的指定字段：
    获取一个字段的数据：SELECT id FROM users；
    获取多个字段的数据：SELECT name,id,phone FROM users；

    WHERE 字句
    给查询添加筛选条件
    =：表示的是运算符
    还可以使用AND和OR来设置多个筛选条件
    语法：SELECT 字段 FROM 表名 WHERE 字段=值；
    例如：查询处id为1或密码为12345的用户信息
    SELECT FROM users WHERE id=1 OR password=’12345’；
    ORDER BY 字句
    添加排序功能；要写在WHERE后面且WHERE字句需要存在；
    ORDER BY 字段排序方式：
    DESC:降序
    ASC(默认)：升序
    例如：数据通过id进行降序排序：
    SELECT     FROM users ORDER BY id DESC;
    与WHERE搭配一起用
    SELECT * FROM users WHERE id>5 ORDER BY password ASC；

    LIMIT 字句
    限定结果集合:限制获取数据的数量
    查询出最后添加到表中的数据
    SELECT FROM users ORDER BY id DESC LIMIT 1;
    分页 规定每页显示两条数据
    第一页：SELECT     FROM users LIMIT 0,2;
    第二页：SELECT FROM users LIMIT 2,2;
    第三页：SELECT     FROM users LIMIT 4,2;
    LIKE 运算符
    模糊查找 属于WHERE下面的运算符
    %表示匹配任意内容，不限值长度
    _表示匹配任意内容，长度限制一个字符
    搜索用户名中带有“狠”字的用户
    SELECT * FROM users WHERE name LIKE ‘%狠%’；
    搜索用户名为两个字的时候匹配

    SELECT * FROM users WHERE name LIKE ‘ ‘;

    REGEXP运算符
    WHERE 字段 REGEXP [BINARY] 值
    NOT REGEXP [BINARY]匹配上的内容丢弃，没匹配上作为结果
    默认查找时不分大小斜；让它区分大小写 加上BINARY
    查找用户名结尾为137的用户
    SELECT FROM users WHERE name REGEXP ‘[1|3|7]$’;
    SELECT     FROM users WHERE name NOT REGEXP ‘[1|3|7]$’;

    对查询结果进行计数
    COUNT()计数函数
    SELECT count(id) FROM users;
    SELECT count(*) FROM users;
    SELECT count(id) FROM users WHERE id>5;

    对查询数据进行分组
    GROUP BY 字段
    SELECT * FROM users GROUP BY password；