基本格式

  1. SELECT <列名> FROM <表名> WHERE <限制条件>;
  1. mysql> create database company;
  2. Query OK, 1 row affected (0.00 sec)
  4. mysql> use company;
  5. Database changed
  6. mysql> show tables;
  7. Empty set (0.00 sec)
  9. mysql> create table employee ( name varchar(20) NOT NULL, age int );
  10. Query OK, 0 rows affected (0.02 sec)
  12. mysql> show tables;
  13. +-------------------+
  14. | Tables_in_company |
  15. +-------------------+
  16. | employee          |
  17. +-------------------+
  18. 1 row in set (0.00 sec)
  20. mysql> INSERT INTO employee VALUES ('Tom', 26), ('Jack', 24), ('Rose', 22), ('Jim', 35), ('Mary', 21), ('Alex', 26), ('Ken', 27), ('Rick', 24);
  21. Query OK, 8 rows affected (0.01 sec)
  22. Records: 8  Duplicates: 0  Warnings: 0
  24. mysql> SELECT * FROM employee;
  25. +------+------+
  26. | name | age  |
  27. +------+------+
  28. | Tom  |   26 |
  29. | Jack |   24 |
  30. | Rose |   22 |
  31. | Jim  |   35 |
  32. | Mary |   21 |
  33. | Alex |   26 |
  34. | Ken  |   27 |
  35. | Rick |   24 |
  36. +------+------+
  37. 8 rows in set (0.00 sec)
  39. mysql> SELECT name FROM employee;
  40. +------+
  41. | name |
  42. +------+
  43. | Tom  |
  44. | Jack |
  45. | Rose |
  46. | Jim  |
  47. | Mary |
  48. | Alex |
  49. | Ken  |
  50. | Rick |
  51. +------+
  52. 8 rows in set (0.00 sec)
  54. mysql> SELECT name FROM employee WHERE age>25;
  55. +------+
  56. | name |
  57. +------+
  58. | Tom  |
  59. | Jim  |
  60. | Alex |
  61. | Ken  |
  62. +------+
  63. 4 rows in set (0.00 sec)
  65. mysql> SELECT name FROM employee WHERE name='Mary';
  66. +------+
  67. | name |
  68. +------+
  69. | Mary |
  70. +------+
  71. 1 row in set (0.00 sec)
  73. mysql> SELECT * FROM employee WHERE name='Mary';
  74. +------+------+
  75. | name | age  |
  76. +------+------+
  77. | Mary |   21 |
  78. +------+------+
  79. 1 row in set (0.00 sec)


Where

  • WHERE 限制条件可以有数学符号 (=,<,>,>=,<=)
  • WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 [条件一 OR 条件二] 和 [条件一 AND 条件二] 连接
  • 取范围：
    • <列名> BETWEEN a AND b; 注意BETWEEN AND是左右取的都是闭区间
    • <列名> >= a AND <列名> <= b;

IN 和 NOT IN

  • IN ()：在选项中
  • NOT IN (): 不在选项中

通配符

LIKE

  • 接 _ （一个未指定字符）
  • 接 % （不定个未指定字符）

对结果排序

ORDER BY

  • ASC：默认升序，从小到大
  • DESC: 从大到小，降序排列 ```sql mysql> SELECT * FROM employee ORDER BY age DESC; +———+———+ | name | age | +———+———+ | Jim | 35 | | Ken | 27 | | Tom | 26 | | Alex | 26 | | Jack | 24 | | Rick | 24 | | Rose | 22 | | Mary | 21 | +———+———+ 8 rows in set (0.00 sec)
  2. <a name="nIGRa"></a>
  3. ## AS
  4. 对值进行重命名
  6. <a name="hCJAu"></a>
  7. ## 计数、求和、平均、最大、最小
  8. SQL 有 5 个内置函数，这些函数都对 SELECT 的结果做操作：
  10. | 函数名： | COUNT | SUM | AVG | MAX | MIN |
  11. | --- | --- | --- | --- | --- | --- |
  12. | 作用： | 计数 | 求和 | 求平均值 | 最大值 | 最小值 |
  15. ```sql
  16. mysql> SELECT COUNT(age) FROM employee;                     
  17. +------------+
  18. | COUNT(age) |
  19. +------------+
  20. |          8 |
  21. +------------+
  22. 1 row in set (0.00 sec)
  24. mysql> SELECT MAX(age) FROM employee;
  25. +----------+
  26. | MAX(age) |
  27. +----------+
  28. |       35 |
  29. +----------+
  30. 1 row in set (0.00 sec)
  32. mysql> SELECT MAX(age) AS max_age FROM employee;
  33. +---------+
  34. | max_age |
  35. +---------+
  36. |      35 |
  37. +---------+
  38. 1 row in set (0.00 sec)
  40. mysql> SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM employee;
  41. +---------+---------+
  42. | max_age | min_age |
  43. +---------+---------+
  44. |      35 |      21 |
  45. +---------+---------+
  46. 1 row in set (0.00 sec)
  48. mysql> SELECT MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) as avg_age  FROM employee;;
  49. +---------+---------+---------+
  50. | max_age | min_age | avg_age |
  51. +---------+---------+---------+
  52. |      35 |      21 | 25.6250 |
  53. +---------+---------+---------+
  54. 1 row in set (0.00 sec)