• 【一】【SQL】表的增删查改（部分）

    表之“增”操作

    建表的操作

     

    1. mysql> create table students(
    2.     -> id int unsigned primary key auto_increment,
    3.     -> sn int unsigned unique key,
    4.     -> name varchar(20) not null,
    5.     -> qq varchar(32) unique key
    6.     -> );
    7. Query OK, 0 rows affected (0.03 sec)
    8.  
    9. mysql> desc students;
    10. +-------+------------------+------+-----+---------+----------------+
    11. | Field | Type             | Null | Key | Default | Extra          |
    12. +-------+------------------+------+-----+---------+----------------+
    13. | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    14. | sn    | int(10) unsigned | YES  | UNI | NULL    |                |
    15. | name  | varchar(20)      | NO   |     | NULL    |                |
    16. | qq    | varchar(32)      | YES  | UNI | NULL    |                |
    17. +-------+------------------+------+-----+---------+----------------+
    18. 4 rows in set (0.01 sec)
     

    指定列插入
     

     

    1. mysql> insert into students (sn,name,qq) values (123,'张飞','4567890');
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. mysql> select * from students;
    5. +----+------+--------+---------+
    6. | id | sn   | name   | qq      |
    7. +----+------+--------+---------+
    8. |  1 |  123 | 张飞   | 4567890 |
    9. +----+------+--------+---------+
    10. 1 row in set (0.00 sec)
    11.  
     

    全列插入
     

     

    1. mysql> insert into students values (10,124,'关羽','123456');
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. mysql> select * from students;
    5. +----+------+--------+---------+
    6. | id | sn   | name   | qq      |
    7. +----+------+--------+---------+
    8. |  1 |  123 | 张飞   | 4567890 |
    9. | 10 |  124 | 关羽   | 123456  |
    10. +----+------+--------+---------+
    11. 2 rows in set (0.00 sec)
     

    主键约束展示
     

     

    1. mysql> insert into students values (10,124,'关羽','123456');
    2. ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
    3. mysql> insert into students values (11,125,'刘备','123459990');
    4. Query OK, 1 row affected (0.01 sec)
    5.  
    6. mysql> select * from students;
    7. +----+------+--------+-----------+
    8. | id | sn   | name   | qq        |
    9. +----+------+--------+-----------+
    10. |  1 |  123 | 张飞   | 4567890   |
    11. | 10 |  124 | 关羽   | 123456    |
    12. | 11 |  125 | 刘备   | 123459990 |
    13. +----+------+--------+-----------+
    14. 3 rows in set (0.00 sec)
    15.  
    16. mysql> 
     

    多列插入---全列
     

     

    1. mysql> insert into students values (13,127,'许攸','1234545656'),(14,128,'许褚','1123334455'),(15,129,'诸葛亮','32111234343');
    2. Query OK, 3 rows affected (0.00 sec)
    3. Records: 3  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> select * from students;
    6. +----+------+-----------+-------------+
    7. | id | sn   | name      | qq          |
    8. +----+------+-----------+-------------+
    9. |  1 |  123 | 张飞      | 4567890     |
    10. | 10 |  124 | 关羽      | 123456      |
    11. | 11 |  125 | 刘备      | 123459990   |
    12. | 13 |  127 | 许攸      | 1234545656  |
    13. | 14 |  128 | 许褚      | 1123334455  |
    14. | 15 |  129 | 诸葛亮    | 32111234343 |
    15. +----+------+-----------+-------------+
    16. 6 rows in set (0.00 sec)
    17.  
    18. mysql> 
     

    多列插入---指定列
     

     

    1. mysql> insert into students (sn,name,qq) values (130,'孙权','64533764'),(131,'吕布','4232455');
    2. Query OK, 2 rows affected (0.01 sec)
    3. Records: 2  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> select * from students;
    6. +----+------+-----------+-------------+
    7. | id | sn   | name      | qq          |
    8. +----+------+-----------+-------------+
    9. |  1 |  123 | 张飞      | 4567890     |
    10. | 10 |  124 | 关羽      | 123456      |
    11. | 11 |  125 | 刘备      | 123459990   |
    12. | 13 |  127 | 许攸      | 1234545656  |
    13. | 14 |  128 | 许褚      | 1123334455  |
    14. | 15 |  129 | 诸葛亮    | 32111234343 |
    15. | 16 |  130 | 孙权      | 64533764    |
    16. | 17 |  131 | 吕布      | 4232455     |
    17. +----+------+-----------+-------------+
    18. 8 rows in set (0.00 sec)
    19.  
    20. mysql> 
     

    表之“改”操作
     

    键冲突时修改数据（冲突键不变）
     

     

    1. mysql> select * from students;
    2. +----+------+-----------+-------------+
    3. | id | sn   | name      | qq          |
    4. +----+------+-----------+-------------+
    5. |  1 |  123 | 张飞      | 4567890     |
    6. | 10 |  124 | 关羽      | 123456      |
    7. | 11 |  125 | 刘备      | 123459990   |
    8. | 13 |  127 | 许攸      | 1234545656  |
    9. | 14 |  128 | 许褚      | 1123334455  |
    10. | 15 |  129 | 诸葛亮    | 32111234343 |
    11. | 16 |  130 | 孙权      | 64533764    |
    12. | 17 |  131 | 吕布      | 4232455     |
    13. +----+------+-----------+-------------+
    14. 8 rows in set (0.00 sec)
    15. mysql> insert into students values (13,128,'xuyou','11111111');
    16. ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
    17. mysql> insert into students values (13,132,'xuyou','11111111') on duplicate key update sn=132,name='xuyou',qq='11111111';
    18. Query OK, 2 rows affected (0.00 sec)
    19.  
    20. mysql> select * from students;
    21. +----+------+-----------+-------------+
    22. | id | sn   | name      | qq          |
    23. +----+------+-----------+-------------+
    24. |  1 |  123 | 张飞      | 4567890     |
    25. | 10 |  124 | 关羽      | 123456      |
    26. | 11 |  125 | 刘备      | 123459990   |
    27. | 13 |  132 | xuyou     | 11111111    |
    28. | 14 |  128 | 许褚      | 1123334455  |
    29. | 15 |  129 | 诸葛亮    | 32111234343 |
    30. | 16 |  130 | 孙权      | 64533764    |
    31. | 17 |  131 | 吕布      | 4232455     |
    32. +----+------+-----------+-------------+
    33. 8 rows in set (0.00 sec)
    34. mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='111223234';
    35. Query OK, 1 row affected (0.00 sec)
    36.  
    37. mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='111223234';
    38. Query OK, 0 rows affected (0.00 sec)
    39.  
    40. mysql> select * from students;
    41. +----+------+-----------+-------------+
    42. | id | sn   | name      | qq          |
    43. +----+------+-----------+-------------+
    44. |  1 |  123 | 张飞      | 4567890     |
    45. | 10 |  124 | 关羽      | 123456      |
    46. | 11 |  125 | 刘备      | 123459990   |
    47. | 13 |  132 | xuyou     | 11111111    |
    48. | 14 |  128 | 许褚      | 1123334455  |
    49. | 15 |  129 | 诸葛亮    | 32111234343 |
    50. | 16 |  130 | 孙权      | 64533764    |
    51. | 17 |  131 | 吕布      | 4232455     |
    52. | 18 |  134 | 貂蝉      | 111223234   |
    53. +----+------+-----------+-------------+
    54. 9 rows in set (0.00 sec)
    55.  
    56. mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='1112235554';
    57. Query OK, 2 rows affected (0.00 sec)
    58.  
    59. mysql> select * from students;
    60. +----+------+-----------+-------------+
    61. | id | sn   | name      | qq          |
    62. +----+------+-----------+-------------+
    63. |  1 |  123 | 张飞      | 4567890     |
    64. | 10 |  124 | 关羽      | 123456      |
    65. | 11 |  125 | 刘备      | 123459990   |
    66. | 13 |  132 | xuyou     | 11111111    |
    67. | 14 |  128 | 许褚      | 1123334455  |
    68. | 15 |  129 | 诸葛亮    | 32111234343 |
    69. | 16 |  130 | 孙权      | 64533764    |
    70. | 17 |  131 | 吕布      | 4232455     |
    71. | 18 |  134 | 貂蝉      | 1112235554  |
    72. +----+------+-----------+-------------+
    73. 9 rows in set (0.00 sec)
    74.  
    75. mysql> 
     

    -- 0 row affected :表中有冲突数据，但冲突数据的值和update的值相等
     

    -- 1 row affected:表中没有冲突数据，数据被插入
     

    -- 2 row affected:表中有冲突数据，并且数据已经被更新
     

    键冲突时修改数据（冲突键会变）
     

     

    1. mysql> select * from students;
    2. +----+------+-----------+-------------+
    3. | id | sn   | name      | qq          |
    4. +----+------+-----------+-------------+
    5. |  1 |  123 | 张飞      | 4567890     |
    6. | 10 |  124 | 关羽      | 123456      |
    7. | 11 |  125 | 刘备      | 123459990   |
    8. | 13 |  132 | xuyou     | 11111111    |
    9. | 14 |  128 | 许褚      | 1123334455  |
    10. | 15 |  129 | 诸葛亮    | 32111234343 |
    11. | 16 |  130 | 孙权      | 64533764    |
    12. | 17 |  131 | 吕布      | 4232455     |
    13. | 18 |  134 | 貂蝉      | 1112235554  |
    14. +----+------+-----------+-------------+
    15. 9 rows in set (0.00 sec)
    16.  
    17. mysql> desc students;
    18. +-------+------------------+------+-----+---------+----------------+
    19. | Field | Type             | Null | Key | Default | Extra          |
    20. +-------+------------------+------+-----+---------+----------------+
    21. | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    22. | sn    | int(10) unsigned | YES  | UNI | NULL    |                |
    23. | name  | varchar(20)      | NO   |     | NULL    |                |
    24. | qq    | varchar(32)      | YES  | UNI | NULL    |                |
    25. +-------+------------------+------+-----+---------+----------------+
    26. 4 rows in set (0.00 sec)
    27.  
    28. mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
    29. Query OK, 1 row affected (0.00 sec)
    30.  
    31. mysql> select * from students;
    32. +----+------+-----------+-------------+
    33. | id | sn   | name      | qq          |
    34. +----+------+-----------+-------------+
    35. |  1 |  123 | 张飞      | 4567890     |
    36. | 10 |  124 | 关羽      | 123456      |
    37. | 11 |  125 | 刘备      | 123459990   |
    38. | 13 |  132 | xuyou     | 11111111    |
    39. | 14 |  128 | 许褚      | 1123334455  |
    40. | 15 |  129 | 诸葛亮    | 32111234343 |
    41. | 16 |  130 | 孙权      | 64533764    |
    42. | 17 |  131 | 吕布      | 4232455     |
    43. | 18 |  134 | 貂蝉      | 1112235554  |
    44. | 19 |  140 | 许攸      | 31213554    |
    45. +----+------+-----------+-------------+
    46. 10 rows in set (0.00 sec)
    47.  
    48. mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
    49. Query OK, 2 rows affected (0.00 sec)
    50.  
    51. mysql> select * from students;
    52. +----+------+-----------+-------------+
    53. | id | sn   | name      | qq          |
    54. +----+------+-----------+-------------+
    55. |  1 |  123 | 张飞      | 4567890     |
    56. | 10 |  124 | 关羽      | 123456      |
    57. | 11 |  125 | 刘备      | 123459990   |
    58. | 13 |  132 | xuyou     | 11111111    |
    59. | 14 |  128 | 许褚      | 1123334455  |
    60. | 15 |  129 | 诸葛亮    | 32111234343 |
    61. | 16 |  130 | 孙权      | 64533764    |
    62. | 17 |  131 | 吕布      | 4232455     |
    63. | 18 |  134 | 貂蝉      | 1112235554  |
    64. | 20 |  140 | 许攸      | 31213554    |
    65. +----+------+-----------+-------------+
    66. 10 rows in set (0.00 sec)
    67.  
    68. mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
    69. Query OK, 2 rows affected (0.00 sec)
    70.  
    71. mysql> select * from students;
    72. +----+------+-----------+-------------+
    73. | id | sn   | name      | qq          |
    74. +----+------+-----------+-------------+
    75. |  1 |  123 | 张飞      | 4567890     |
    76. | 10 |  124 | 关羽      | 123456      |
    77. | 11 |  125 | 刘备      | 123459990   |
    78. | 13 |  132 | xuyou     | 11111111    |
    79. | 14 |  128 | 许褚      | 1123334455  |
    80. | 15 |  129 | 诸葛亮    | 32111234343 |
    81. | 16 |  130 | 孙权      | 64533764    |
    82. | 17 |  131 | 吕布      | 4232455     |
    83. | 18 |  134 | 貂蝉      | 1112235554  |
    84. | 21 |  140 | 许攸      | 31213554    |
    85. +----+------+-----------+-------------+
    86. 10 rows in set (0.00 sec)
    87.  
    88. mysql> 
     

    主键或唯一键没有冲突，则直接插入。
     

    主键或唯一键有冲突，则删除后插入。
     

    1 row affected：表中没有冲突数据，数据被插入。
     

    2 row affectede：表中有冲突数据，删除后重新插入。
     

    update
     

    将孙悟空同学的数学成绩变更为80分
     

     

    1. mysql> select name ,math from exam_result ;
    2. +-----------+------+
    3. | name      | math |
    4. +-----------+------+
    5. | 唐三藏    |   98 |
    6. | 孙悟空    |   78 |
    7. | 猪悟能    |   98 |
    8. | 曹孟德    |   84 |
    9. | 刘玄德    |   85 |
    10. | 孙权      |   73 |
    11. | 宋公明    |   65 |
    12. +-----------+------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> update exam_result set math=80 where name='孙悟空';
    16. Query OK, 1 row affected (0.00 sec)
    17. Rows matched: 1  Changed: 1  Warnings: 0
    18.  
    19. mysql> select name ,math from exam_result ;
    20. +-----------+------+
    21. | name      | math |
    22. +-----------+------+
    23. | 唐三藏    |   98 |
    24. | 孙悟空    |   80 |
    25. | 猪悟能    |   98 |
    26. | 曹孟德    |   84 |
    27. | 刘玄德    |   85 |
    28. | 孙权      |   73 |
    29. | 宋公明    |   65 |
    30. +-----------+------+
    31. 7 rows in set (0.00 sec)
    32.  
    33. mysql> 
     

    将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分
     

     

    1. mysql> select * from exam_result ;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   80 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      82 |   84 |      67 |
    9. |  5 | 刘玄德    |      55 |   85 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   65 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.01 sec)
    14.  
    15. mysql> update exam_result set math=60 ,chinese=70 where name='曹孟德';
    16. Query OK, 1 row affected (0.00 sec)
    17. Rows matched: 1  Changed: 1  Warnings: 0
    18.  
    19. mysql> select * from exam_result ;
    20. +----+-----------+---------+------+---------+
    21. | id | name      | chinese | math | english |
    22. +----+-----------+---------+------+---------+
    23. |  1 | 唐三藏    |      67 |   98 |      56 |
    24. |  2 | 孙悟空    |      87 |   80 |      77 |
    25. |  3 | 猪悟能    |      88 |   98 |      90 |
    26. |  4 | 曹孟德    |      70 |   60 |      67 |
    27. |  5 | 刘玄德    |      55 |   85 |      45 |
    28. |  6 | 孙权      |      70 |   73 |      78 |
    29. |  7 | 宋公明    |      75 |   65 |      30 |
    30. +----+-----------+---------+------+---------+
    31. 7 rows in set (0.00 sec)
    32.  
    33. mysql> 
     

    将总成绩倒数前三的3位同学的数学成绩加上30分
     

     

    1. mysql> select name , math+english+chinese total from exam_result ;
    2. +-----------+-------+
    3. | name      | total |
    4. +-----------+-------+
    5. | 唐三藏    |   221 |
    6. | 孙悟空    |   244 |
    7. | 猪悟能    |   276 |
    8. | 曹孟德    |   197 |
    9. | 刘玄德    |   185 |
    10. | 孙权      |   221 |
    11. | 宋公明    |   170 |
    12. +-----------+-------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name , math+english+chinese total from exam_result order by total ;
    16. +-----------+-------+
    17. | name      | total |
    18. +-----------+-------+
    19. | 宋公明    |   170 |
    20. | 刘玄德    |   185 |
    21. | 曹孟德    |   197 |
    22. | 唐三藏    |   221 |
    23. | 孙权      |   221 |
    24. | 孙悟空    |   244 |
    25. | 猪悟能    |   276 |
    26. +-----------+-------+
    27. 7 rows in set (0.00 sec)
    28.  
    29. mysql> select name , math+english+chinese total from exam_result order by total desc;
    30. +-----------+-------+
    31. | name      | total |
    32. +-----------+-------+
    33. | 猪悟能    |   276 |
    34. | 孙悟空    |   244 |
    35. | 唐三藏    |   221 |
    36. | 孙权      |   221 |
    37. | 曹孟德    |   197 |
    38. | 刘玄德    |   185 |
    39. | 宋公明    |   170 |
    40. +-----------+-------+
    41. 7 rows in set (0.00 sec)
    42.  
    43. mysql> select name , math+english+chinese total from exam_result order by total desc limit 0,3;
    44. +-----------+-------+
    45. | name      | total |
    46. +-----------+-------+
    47. | 猪悟能    |   276 |
    48. | 孙悟空    |   244 |
    49. | 唐三藏    |   221 |
    50. +-----------+-------+
    51. 3 rows in set (0.00 sec)
    52.  
    53. mysql> select name , math+english+chinese total from exam_result order by total limit 3;
    54. +-----------+-------+
    55. | name      | total |
    56. +-----------+-------+
    57. | 宋公明    |   170 |
    58. | 刘玄德    |   185 |
    59. | 曹孟德    |   197 |
    60. +-----------+-------+
    61. 3 rows in set (0.00 sec)
    62.  
    63. mysql> update exam_result set math=math+30 order by chinese+english+math asc limit 3;
    64. Query OK, 3 rows affected (0.00 sec)
    65. Rows matched: 3  Changed: 3  Warnings: 0
    66.  
    67. mysql> select name , math+english+chinese total from exam_result order by total limit 3;
    68. +-----------+-------+
    69. | name      | total |
    70. +-----------+-------+
    71. | 宋公明    |   200 |
    72. | 刘玄德    |   215 |
    73. | 唐三藏    |   221 |
    74. +-----------+-------+
    75. 3 rows in set (0.00 sec)
    76.  
    77. mysql> select name ,math+chinese+english total from exam_result order by total;
    78. +-----------+-------+
    79. | name      | total |
    80. +-----------+-------+
    81. | 宋公明    |   200 |
    82. | 刘玄德    |   215 |
    83. | 唐三藏    |   221 |
    84. | 孙权      |   221 |
    85. | 曹孟德    |   227 |
    86. | 孙悟空    |   244 |
    87. | 猪悟能    |   276 |
    88. +-----------+-------+
    89. 7 rows in set (0.00 sec)
    90.  
    91. mysql> 
     

    将所有同学的语文成绩更新为原来的2倍
     

     

    1. mysql> select *from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   80 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      70 |   90 |      67 |
    9. |  5 | 刘玄德    |      55 |  115 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   95 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> update exam_result set chinese=chinese*2 ;
    16. Query OK, 7 rows affected (0.01 sec)
    17. Rows matched: 7  Changed: 7  Warnings: 0
    18.  
    19. mysql> select * from exam_result;
    20. +----+-----------+---------+------+---------+
    21. | id | name      | chinese | math | english |
    22. +----+-----------+---------+------+---------+
    23. |  1 | 唐三藏    |     134 |   98 |      56 |
    24. |  2 | 孙悟空    |     174 |   80 |      77 |
    25. |  3 | 猪悟能    |     176 |   98 |      90 |
    26. |  4 | 曹孟德    |     140 |   90 |      67 |
    27. |  5 | 刘玄德    |     110 |  115 |      45 |
    28. |  6 | 孙权      |     140 |   73 |      78 |
    29. |  7 | 宋公明    |     150 |   95 |      30 |
    30. +----+-----------+---------+------+---------+
    31. 7 rows in set (0.00 sec)
    32.  
    33. mysql> 
     

    表之“查”操作（部分）
     

    全列查询
     

     

    1. mysql> select * from students;
    2. +----+------+-----------+-------------+
    3. | id | sn   | name      | qq          |
    4. +----+------+-----------+-------------+
    5. |  1 |  123 | 张飞      | 4567890     |
    6. | 10 |  124 | 关羽      | 123456      |
    7. | 11 |  125 | 刘备      | 123459990   |
    8. | 13 |  132 | xuyou     | 11111111    |
    9. | 14 |  128 | 许褚      | 1123334455  |
    10. | 15 |  129 | 诸葛亮    | 32111234343 |
    11. | 16 |  130 | 孙权      | 64533764    |
    12. | 17 |  131 | 吕布      | 4232455     |
    13. | 18 |  134 | 貂蝉      | 1112235554  |
    14. | 21 |  140 | 许攸      | 31213554    |
    15. +----+------+-----------+-------------+
    16. 10 rows in set (0.00 sec)
     

    指定列查询
     

     

    1. mysql> select id from students;
    2. +----+
    3. | id |
    4. +----+
    5. |  1 |
    6. | 10 |
    7. | 11 |
    8. | 14 |
    9. | 15 |
    10. | 16 |
    11. | 17 |
    12. | 13 |
    13. | 18 |
    14. | 21 |
    15. +----+
    16. 10 rows in set (0.00 sec)
    17.  
    18. mysql> select id,sn from students;
    19. +----+------+
    20. | id | sn   |
    21. +----+------+
    22. |  1 |  123 |
    23. | 10 |  124 |
    24. | 11 |  125 |
    25. | 14 |  128 |
    26. | 15 |  129 |
    27. | 16 |  130 |
    28. | 17 |  131 |
    29. | 13 |  132 |
    30. | 18 |  134 |
    31. | 21 |  140 |
    32. +----+------+
    33. 10 rows in set (0.00 sec)
    34.  
    35. mysql> select id,sn,name from students;
    36. +----+------+-----------+
    37. | id | sn   | name      |
    38. +----+------+-----------+
    39. |  1 |  123 | 张飞      |
    40. | 10 |  124 | 关羽      |
    41. | 11 |  125 | 刘备      |
    42. | 13 |  132 | xuyou     |
    43. | 14 |  128 | 许褚      |
    44. | 15 |  129 | 诸葛亮    |
    45. | 16 |  130 | 孙权      |
    46. | 17 |  131 | 吕布      |
    47. | 18 |  134 | 貂蝉      |
    48. | 21 |  140 | 许攸      |
    49. +----+------+-----------+
    50. 10 rows in set (0.00 sec)
    51.  
    52. mysql> select id,sn,name,qq from students;
    53. +----+------+-----------+-------------+
    54. | id | sn   | name      | qq          |
    55. +----+------+-----------+-------------+
    56. |  1 |  123 | 张飞      | 4567890     |
    57. | 10 |  124 | 关羽      | 123456      |
    58. | 11 |  125 | 刘备      | 123459990   |
    59. | 13 |  132 | xuyou     | 11111111    |
    60. | 14 |  128 | 许褚      | 1123334455  |
    61. | 15 |  129 | 诸葛亮    | 32111234343 |
    62. | 16 |  130 | 孙权      | 64533764    |
    63. | 17 |  131 | 吕布      | 4232455     |
    64. | 18 |  134 | 貂蝉      | 1112235554  |
    65. | 21 |  140 | 许攸      | 31213554    |
    66. +----+------+-----------+-------------+
    67. 10 rows in set (0.00 sec)
    68.  
    69. mysql> 
     

    表达式查询
     

    示例展示
     

     

    1. mysql>  create table exam_result(
    2.     -> id int unsigned primary key auto_increment,
    3.     -> name varchar(20) not null comment '同学姓名',
    4.     -> chinese float default 0.0 comment '语文成绩',
    5.     -> math float default 0.0 comment '数学成绩',
    6.     -> english float default 0.0 comment '英语乘积'
    7.     -> );
    8. Query OK, 0 rows affected (0.05 sec)
    9.  
    10. mysql> desc exam_result;
    11. +---------+------------------+------+-----+---------+----------------+
    12. | Field   | Type             | Null | Key | Default | Extra          |
    13. +---------+------------------+------+-----+---------+----------------+
    14. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    15. | name    | varchar(20)      | NO   |     | NULL    |                |
    16. | chinese | float            | YES  |     | 0       |                |
    17. | math    | float            | YES  |     | 0       |                |
    18. | english | float            | YES  |     | 0       |                |
    19. +---------+------------------+------+-----+---------+----------------+
    20. 5 rows in set (0.00 sec)
    21. mysql> insert into exam_result (name ,chinese,math,english)values
    22.     -> ('唐三藏',67,98,56),
    23.     -> ('孙悟空',87,78,77),
    24.     -> ('猪悟能',88,98,90),
    25.     -> ('曹孟德',82,84,67),
    26.     -> ('刘玄德',55,85,45),
    27.     -> ('孙权',70,73,78),
    28.     -> ('宋公明',75,65,30);
    29. Query OK, 7 rows affected (0.00 sec)
    30. Records: 7  Duplicates: 0  Warnings: 0
    31.  
    32. mysql> select * from exam_result;
    33. +----+-----------+---------+------+---------+
    34. | id | name      | chinese | math | english |
    35. +----+-----------+---------+------+---------+
    36. |  1 | 唐三藏    |      67 |   98 |      56 |
    37. |  2 | 孙悟空    |      87 |   78 |      77 |
    38. |  3 | 猪悟能    |      88 |   98 |      90 |
    39. |  4 | 曹孟德    |      82 |   84 |      67 |
    40. |  5 | 刘玄德    |      55 |   85 |      45 |
    41. |  6 | 孙权      |      70 |   73 |      78 |
    42. |  7 | 宋公明    |      75 |   65 |      30 |
    43. +----+-----------+---------+------+---------+
    44. 7 rows in set (0.00 sec)
    45.  
    46. mysql> 
     

    表达式查询
     

     

    1. mysql> select 1+1;
    2. +-----+
    3. | 1+1 |
    4. +-----+
    5. |   2 |
    6. +-----+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select 7+8;
    10. +-----+
    11. | 7+8 |
    12. +-----+
    13. |  15 |
    14. +-----+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select name,math,10 from exam_result;
    18. +-----------+------+----+
    19. | name      | math | 10 |
    20. +-----------+------+----+
    21. | 唐三藏    |   98 | 10 |
    22. | 孙悟空    |   78 | 10 |
    23. | 猪悟能    |   98 | 10 |
    24. | 曹孟德    |   84 | 10 |
    25. | 刘玄德    |   85 | 10 |
    26. | 孙权      |   73 | 10 |
    27. | 宋公明    |   65 | 10 |
    28. +-----------+------+----+
    29. 7 rows in set (0.00 sec)
    30.  
    31. mysql> select name,math,1+1 from exam_result;
    32. +-----------+------+-----+
    33. | name      | math | 1+1 |
    34. +-----------+------+-----+
    35. | 唐三藏    |   98 |   2 |
    36. | 孙悟空    |   78 |   2 |
    37. | 猪悟能    |   98 |   2 |
    38. | 曹孟德    |   84 |   2 |
    39. | 刘玄德    |   85 |   2 |
    40. | 孙权      |   73 |   2 |
    41. | 宋公明    |   65 |   2 |
    42. +-----------+------+-----+
    43. 7 rows in set (0.00 sec)
    44.  
    45. mysql> select name,math,math+chinese+english from exam_result;
    46. +-----------+------+----------------------+
    47. | name      | math | math+chinese+english |
    48. +-----------+------+----------------------+
    49. | 唐三藏    |   98 |                  221 |
    50. | 孙悟空    |   78 |                  242 |
    51. | 猪悟能    |   98 |                  276 |
    52. | 曹孟德    |   84 |                  233 |
    53. | 刘玄德    |   85 |                  185 |
    54. | 孙权      |   73 |                  221 |
    55. | 宋公明    |   65 |                  170 |
    56. +-----------+------+----------------------+
    57. 7 rows in set (0.00 sec)
    58.  
    59. mysql> select name,math,math+chinese+english as total from exam_result;
    60. +-----------+------+-------+
    61. | name      | math | total |
    62. +-----------+------+-------+
    63. | 唐三藏    |   98 |   221 |
    64. | 孙悟空    |   78 |   242 |
    65. | 猪悟能    |   98 |   276 |
    66. | 曹孟德    |   84 |   233 |
    67. | 刘玄德    |   85 |   185 |
    68. | 孙权      |   73 |   221 |
    69. | 宋公明    |   65 |   170 |
    70. +-----------+------+-------+
    71. 7 rows in set (0.00 sec)
    72.  
    73. mysql> select name,math,math+chinese+english  total from exam_result;
    74. +-----------+------+-------+
    75. | name      | math | total |
    76. +-----------+------+-------+
    77. | 唐三藏    |   98 |   221 |
    78. | 孙悟空    |   78 |   242 |
    79. | 猪悟能    |   98 |   276 |
    80. | 曹孟德    |   84 |   233 |
    81. | 刘玄德    |   85 |   185 |
    82. | 孙权      |   73 |   221 |
    83. | 宋公明    |   65 |   170 |
    84. +-----------+------+-------+
    85. 7 rows in set (0.00 sec)
    86.  
    87. mysql> select name 姓名 ,math 数学,math+chinese+english  总分 from exam_result;
    88. +-----------+--------+--------+
    89. | 姓名      | 数学   | 总分   |
    90. +-----------+--------+--------+
    91. | 唐三藏    |     98 |    221 |
    92. | 孙悟空    |     78 |    242 |
    93. | 猪悟能    |     98 |    276 |
    94. | 曹孟德    |     84 |    233 |
    95. | 刘玄德    |     85 |    185 |
    96. | 孙权      |     73 |    221 |
    97. | 宋公明    |     65 |    170 |
    98. +-----------+--------+--------+
    99. 7 rows in set (0.00 sec)
    100.  
    101. mysql> 
     

    去重查询（distinct）
     

     

    1. mysql> select math from exam_result;
    2. +------+
    3. | math |
    4. +------+
    5. |   98 |
    6. |   78 |
    7. |   98 |
    8. |   84 |
    9. |   85 |
    10. |   73 |
    11. |   65 |
    12. +------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select distinct math from exam_result;
    16. +------+
    17. | math |
    18. +------+
    19. |   98 |
    20. |   78 |
    21. |   84 |
    22. |   85 |
    23. |   73 |
    24. |   65 |
    25. +------+
    26. 6 rows in set (0.00 sec)
    27.  
    28. mysql> 
     

    WHERE条件
     

     
    运算符
         		 
    说明
     
     
    >,>=,<,<=
         		 
    大于，大于等于，小于，小于等于
     
     
    =
         		 
    等于，NULL不安全，例如NULL=NULL的结果是NULL
     
     
    <=>
         		 
    等于，NULL安全，例如NULL<=>NULL的结果是TRUE（1）
     
     
    !=,<>
         		 
    不等于
     
     
    BETWEEN a0 AND a1
         		 
    范围匹配，[a0,a1],如果a0<=value<=a1,返回TRUE（1）
     
     
    IN（option,...）
         		 
    如果是option中的任意一个，返回TRUE（1）
     
     
    IS NULL
         		 
    是NULL
     
     
    IS NOT NULL
         		 
    不是NULL
     
     
    LIKE
         		 
    模糊匹配。%表示匹配多个（包括0个）任意字符；_表示任意一个字符
     
     
    AND
         		 
    多个条件必须为TRUE（1），结果为TRUE（1）
     
     
    OR
         		 
    任意一个条件为TRUE（1），结果为TRUE（1）
     
     
    NOT
         		 
    条件为TRUE（1），结果为FALSE(0)
     
     

     

    1. mysql> select NULL;
    2. +------+
    3. | NULL |
    4. +------+
    5. | NULL |
    6. +------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select 0;
    10. +---+
    11. | 0 |
    12. +---+
    13. | 0 |
    14. +---+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select NULL=NULL;
    18. +-----------+
    19. | NULL=NULL |
    20. +-----------+
    21. |      NULL |
    22. +-----------+
    23. 1 row in set (0.00 sec)
    24.  
    25. mysql> select null<=>null
    26.     -> ;
    27. +-------------+
    28. | null<=>null |
    29. +-------------+
    30. |           1 |
    31. +-------------+
    32. 1 row in set (0.00 sec)
    33.  
    34. mysql> select 1=1
    35.     -> ;
    36. +-----+
    37. | 1=1 |
    38. +-----+
    39. |   1 |
    40. +-----+
    41. 1 row in set (0.00 sec)
    42.  
    43. mysql> select 2=2;
    44. +-----+
    45. | 2=2 |
    46. +-----+
    47. |   1 |
    48. +-----+
    49. 1 row in set (0.00 sec)
    50.  
    51. mysql> select 2=1;
    52. +-----+
    53. | 2=1 |
    54. +-----+
    55. |   0 |
    56. +-----+
    57. 1 row in set (0.00 sec)
    58.  
    59. mysql> select 2=null;
    60. +--------+
    61. | 2=null |
    62. +--------+
    63. |   NULL |
    64. +--------+
    65. 1 row in set (0.00 sec)
    66.  
    67. mysql> select 1<=>1;
    68. +-------+
    69. | 1<=>1 |
    70. +-------+
    71. |     1 |
    72. +-------+
    73. 1 row in set (0.00 sec)
    74.  
    75. mysql> select 1<=>0;
    76. +-------+
    77. | 1<=>0 |
    78. +-------+
    79. |     0 |
    80. +-------+
    81. 1 row in set (0.00 sec)
    82.  
    83. mysql> select 1!=1;
    84. +------+
    85. | 1!=1 |
    86. +------+
    87. |    0 |
    88. +------+
    89. 1 row in set (0.00 sec)
    90.  
    91. mysql> select 1!=2;
    92. +------+
    93. | 1!=2 |
    94. +------+
    95. |    1 |
    96. +------+
    97. 1 row in set (0.00 sec)
    98.  
    99. mysql> select null is null;
    100. +--------------+
    101. | null is null |
    102. +--------------+
    103. |            1 |
    104. +--------------+
    105. 1 row in set (0.00 sec)
    106.  
    107. mysql> select null is not null;
    108. +------------------+
    109. | null is not null |
    110. +------------------+
    111. |                0 |
    112. +------------------+
    113. 1 row in set (0.00 sec)
    114.  
    115. mysql> 
     

    条件查询
     

     

    1. mysql> select name , english from exam_result ;
    2. +-----------+---------+
    3. | name      | english |
    4. +-----------+---------+
    5. | 唐三藏    |      56 |
    6. | 孙悟空    |      77 |
    7. | 猪悟能    |      90 |
    8. | 曹孟德    |      67 |
    9. | 刘玄德    |      45 |
    10. | 孙权      |      78 |
    11. | 宋公明    |      30 |
    12. +-----------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name , english from exam_result where english<60;
    16. +-----------+---------+
    17. | name      | english |
    18. +-----------+---------+
    19. | 唐三藏    |      56 |
    20. | 刘玄德    |      45 |
    21. | 宋公明    |      30 |
    22. +-----------+---------+
    23. 3 rows in set (0.00 sec)
    24.  
    25. mysql> 
     

    where条件查询案例
     

    语文成绩在[80,90]分的同学及语文成绩
     

     

    1. mysql> select * from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   78 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      82 |   84 |      67 |
    9. |  5 | 刘玄德    |      55 |   85 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   65 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name,chinese from exam_result;
    16. +-----------+---------+
    17. | name      | chinese |
    18. +-----------+---------+
    19. | 唐三藏    |      67 |
    20. | 孙悟空    |      87 |
    21. | 猪悟能    |      88 |
    22. | 曹孟德    |      82 |
    23. | 刘玄德    |      55 |
    24. | 孙权      |      70 |
    25. | 宋公明    |      75 |
    26. +-----------+---------+
    27. 7 rows in set (0.00 sec)
    28.  
    29. mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
    30. +-----------+---------+
    31. | name      | chinese |
    32. +-----------+---------+
    33. | 孙悟空    |      87 |
    34. | 猪悟能    |      88 |
    35. | 曹孟德    |      82 |
    36. +-----------+---------+
    37. 3 rows in set (0.00 sec)
    38.  
    39. mysql> select name,chinese from exam_result where chinese between 80 and 90;
    40. +-----------+---------+
    41. | name      | chinese |
    42. +-----------+---------+
    43. | 孙悟空    |      87 |
    44. | 猪悟能    |      88 |
    45. | 曹孟德    |      82 |
    46. +-----------+---------+
    47. 3 rows in set (0.00 sec)
    48.  
    49. mysql> 
     

    数学成绩是58或59或98或99的同学及数学成绩
     

     

    1. mysql> select name ,math from exam_result;
    2. +-----------+------+
    3. | name      | math |
    4. +-----------+------+
    5. | 唐三藏    |   98 |
    6. | 孙悟空    |   78 |
    7. | 猪悟能    |   98 |
    8. | 曹孟德    |   84 |
    9. | 刘玄德    |   85 |
    10. | 孙权      |   73 |
    11. | 宋公明    |   65 |
    12. +-----------+------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,math from exam_result where math=58 or math =59 or math =98 or math =99;
    16. +-----------+------+
    17. | name      | math |
    18. +-----------+------+
    19. | 唐三藏    |   98 |
    20. | 猪悟能    |   98 |
    21. +-----------+------+
    22. 2 rows in set (0.00 sec)
    23.  
    24. mysql> select name ,math from exam_result where math in(58,59,98,99);
    25. +-----------+------+
    26. | name      | math |
    27. +-----------+------+
    28. | 唐三藏    |   98 |
    29. | 猪悟能    |   98 |
    30. +-----------+------+
    31. 2 rows in set (0.00 sec)
    32.  
    33. mysql> 
     

    姓孙的同学及孙某同学
     

     

    1. mysql> select name from exam_result where name like '孙%';
    2. +-----------+
    3. | name      |
    4. +-----------+
    5. | 孙悟空    |
    6. | 孙权      |
    7. +-----------+
    8. 2 rows in set (0.00 sec)
    9.  
    10. mysql> select name from exam_result where name like '孙_';
    11. +--------+
    12. | name   |
    13. +--------+
    14. | 孙权   |
    15. +--------+
    16. 1 row in set (0.00 sec)
    17.  
    18. mysql> 
     

    语文成绩好于英语成绩的同学
     

     

    1. mysql> select name ,chinese,english from exam_result where chinese>english;
    2. +-----------+---------+---------+
    3. | name      | chinese | english |
    4. +-----------+---------+---------+
    5. | 唐三藏    |      67 |      56 |
    6. | 孙悟空    |      87 |      77 |
    7. | 曹孟德    |      82 |      67 |
    8. | 刘玄德    |      55 |      45 |
    9. | 宋公明    |      75 |      30 |
    10. +-----------+---------+---------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> 
     

    总分在200分以下的同学
     

     

    1. mysql> select name ,chinese+math+english from exam_result;
    2. +-----------+----------------------+
    3. | name      | chinese+math+english |
    4. +-----------+----------------------+
    5. | 唐三藏    |                  221 |
    6. | 孙悟空    |                  242 |
    7. | 猪悟能    |                  276 |
    8. | 曹孟德    |                  233 |
    9. | 刘玄德    |                  185 |
    10. | 孙权      |                  221 |
    11. | 宋公明    |                  170 |
    12. +-----------+----------------------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,chinese+math+english from exam_result where chinese+math+english<200;
    16. +-----------+----------------------+
    17. | name      | chinese+math+english |
    18. +-----------+----------------------+
    19. | 刘玄德    |                  185 |
    20. | 宋公明    |                  170 |
    21. +-----------+----------------------+
    22. 2 rows in set (0.00 sec)
    23.  
    24. mysql> select name ,chinese+math+english total from exam_result ;
    25. +-----------+-------+
    26. | name      | total |
    27. +-----------+-------+
    28. | 唐三藏    |   221 |
    29. | 孙悟空    |   242 |
    30. | 猪悟能    |   276 |
    31. | 曹孟德    |   233 |
    32. | 刘玄德    |   185 |
    33. | 孙权      |   221 |
    34. | 宋公明    |   170 |
    35. +-----------+-------+
    36. 7 rows in set (0.00 sec)
    37.  
    38. mysql> 
     

    mysql语句执行顺序
     

     

    1. mysql> select name ,chinese+math+english total from exam_result where total<200;
    2. ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
    3. mysql> select name ,chinese+math+english total from exam_result where chinese+math+english<200;
    4. +-----------+-------+
    5. | name      | total |
    6. +-----------+-------+
    7. | 刘玄德    |   185 |
    8. | 宋公明    |   170 |
    9. +-----------+-------+
    10. 2 rows in set (0.00 sec)
    11.  
    12. mysql> select name ,chinese+math+english as total from exam_result where chinese+math+english<200;
    13. +-----------+-------+
    14. | name      | total |
    15. +-----------+-------+
    16. | 刘玄德    |   185 |
    17. | 宋公明    |   170 |
    18. +-----------+-------+
    19. 2 rows in set (0.00 sec)
    20.  
    21. mysql> 
     

     

    首先要知道在哪一个表中做操作，第二步是条件判断，前两步得出的是符合条件的表，第三步在得到的符合条件的表中选择列数据。在执行第二步的时候，并不知道total是什么。
     

    语文成绩>80并且不姓孙的同学
     

     

    1. mysql> select name ,chinese from exam_result where chinese>80;
    2. +-----------+---------+
    3. | name      | chinese |
    4. +-----------+---------+
    5. | 孙悟空    |      87 |
    6. | 猪悟能    |      88 |
    7. | 曹孟德    |      82 |
    8. +-----------+---------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> select name ,chinese from exam_result where name like '孙%';
    12. +-----------+---------+
    13. | name      | chinese |
    14. +-----------+---------+
    15. | 孙悟空    |      87 |
    16. | 孙权      |      70 |
    17. +-----------+---------+
    18. 2 rows in set (0.00 sec)
    19.  
    20. mysql> select name ,chinese from exam_result where name not like '孙%';
    21. +-----------+---------+
    22. | name      | chinese |
    23. +-----------+---------+
    24. | 唐三藏    |      67 |
    25. | 猪悟能    |      88 |
    26. | 曹孟德    |      82 |
    27. | 刘玄德    |      55 |
    28. | 宋公明    |      75 |
    29. +-----------+---------+
    30. 5 rows in set (0.00 sec)
    31.  
    32. mysql> select name ,chinese from exam_result where  chinese>80 and  name not like '孙%';
    33. +-----------+---------+
    34. | name      | chinese |
    35. +-----------+---------+
    36. | 猪悟能    |      88 |
    37. | 曹孟德    |      82 |
    38. +-----------+---------+
    39. 2 rows in set (0.00 sec)
    40.  
    41. mysql> 
     

    孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80
     

     

    1. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result;
    2. +-----------+---------+------+---------+--------+
    3. | name      | chinese | math | english | 总分   |
    4. +-----------+---------+------+---------+--------+
    5. | 唐三藏    |      67 |   98 |      56 |    221 |
    6. | 孙悟空    |      87 |   78 |      77 |    242 |
    7. | 猪悟能    |      88 |   98 |      90 |    276 |
    8. | 曹孟德    |      82 |   84 |      67 |    233 |
    9. | 刘玄德    |      55 |   85 |      45 |    185 |
    10. | 孙权      |      70 |   73 |      78 |    221 |
    11. | 宋公明    |      75 |   65 |      30 |    170 |
    12. +-----------+---------+------+---------+--------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200;
    16. +-----------+---------+------+---------+--------+
    17. | name      | chinese | math | english | 总分   |
    18. +-----------+---------+------+---------+--------+
    19. | 唐三藏    |      67 |   98 |      56 |    221 |
    20. | 孙悟空    |      87 |   78 |      77 |    242 |
    21. | 猪悟能    |      88 |   98 |      90 |    276 |
    22. | 曹孟德    |      82 |   84 |      67 |    233 |
    23. | 孙权      |      70 |   73 |      78 |    221 |
    24. +-----------+---------+------+---------+--------+
    25. 5 rows in set (0.00 sec)
    26.  
    27. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200 and chinese <math;
    28. +-----------+---------+------+---------+--------+
    29. | name      | chinese | math | english | 总分   |
    30. +-----------+---------+------+---------+--------+
    31. | 唐三藏    |      67 |   98 |      56 |    221 |
    32. | 猪悟能    |      88 |   98 |      90 |    276 |
    33. | 曹孟德    |      82 |   84 |      67 |    233 |
    34. | 孙权      |      70 |   73 |      78 |    221 |
    35. +-----------+---------+------+---------+--------+
    36. 4 rows in set (0.00 sec)
    37.  
    38. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200 and chinese <math and english>80;
    39. +-----------+---------+------+---------+--------+
    40. | name      | chinese | math | english | 总分   |
    41. +-----------+---------+------+---------+--------+
    42. | 猪悟能    |      88 |   98 |      90 |    276 |
    43. +-----------+---------+------+---------+--------+
    44. 1 row in set (0.00 sec)
    45.  
    46. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where name like '孙_';
    47. +--------+---------+------+---------+--------+
    48. | name   | chinese | math | english | 总分   |
    49. +--------+---------+------+---------+--------+
    50. | 孙权   |      70 |   73 |      78 |    221 |
    51. +--------+---------+------+---------+--------+
    52. 1 row in set (0.00 sec)
    53.  
    54. mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where name like '孙_'  or  chinese +math+english >200 and chinese <math and english>80
    55. 0;
    56. +-----------+---------+------+---------+--------+
    57. | name      | chinese | math | english | 总分   |
    58. +-----------+---------+------+---------+--------+
    59. | 猪悟能    |      88 |   98 |      90 |    276 |
    60. | 孙权      |      70 |   73 |      78 |    221 |
    61. +-----------+---------+------+---------+--------+
    62. 2 rows in set (0.00 sec)
    63.  
    64. mysql> 
     

    NULL和''的区别
     

     

    1. mysql> create table test_null(
    2.     -> id int,
    3.     -> name varchar (20)
    4.     -> );
    5. Query OK, 0 rows affected (0.03 sec)
    6.  
    7. mysql> 
    8. mysql> insert into test_null (id,name) values (1,'张三');
    9. Query OK, 1 row affected (0.01 sec)
    10.  
    11. mysql> insert into test_null (id,name) values (null,'张三');
    12. Query OK, 1 row affected (0.00 sec)
    13.  
    14. mysql> insert into test_null (id,name) values (1,null);
    15. Query OK, 1 row affected (0.01 sec)
    16.  
    17. mysql> insert into test_null (id,name) values (null,null);
    18. Query OK, 1 row affected (0.01 sec)
    19.  
    20. mysql> insert into test_null (id,name) values (1,'');
    21. Query OK, 1 row affected (0.01 sec)
    22.  
    23. mysql> select * from test_null;
    24. +------+--------+
    25. | id   | name   |
    26. +------+--------+
    27. |    1 | 张三   |
    28. | NULL | 张三   |
    29. |    1 | NULL   |
    30. | NULL | NULL   |
    31. |    1 |        |
    32. +------+--------+
    33. 5 rows in set (0.00 sec)
    34.  
    35. mysql> select * from test_null where name is null;
    36. +------+------+
    37. | id   | name |
    38. +------+------+
    39. |    1 | NULL |
    40. | NULL | NULL |
    41. +------+------+
    42. 2 rows in set (0.00 sec)
    43.  
    44. mysql> select * from test_null where name = '';
    45. +------+------+
    46. | id   | name |
    47. +------+------+
    48. |    1 |      |
    49. +------+------+
    50. 1 row in set (0.00 sec)
    51.  
    52. mysql> select * from test_null where name is not null;
    53. +------+--------+
    54. | id   | name   |
    55. +------+--------+
    56. |    1 | 张三   |
    57. | NULL | 张三   |
    58. |    1 |        |
    59. +------+--------+
    60. 3 rows in set (0.00 sec)
    61.  
    62. mysql> 
     

    Order by
     

    asc升序
     

    desc降序
     

    默认升序
     

    同学及数学成绩，按照数学成绩升序显示
     

     

    1. mysql> select * from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   78 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      82 |   84 |      67 |
    9. |  5 | 刘玄德    |      55 |   85 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   65 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,math from exam_result order by math asc;
    16. +-----------+------+
    17. | name      | math |
    18. +-----------+------+
    19. | 宋公明    |   65 |
    20. | 孙权      |   73 |
    21. | 孙悟空    |   78 |
    22. | 曹孟德    |   84 |
    23. | 刘玄德    |   85 |
    24. | 唐三藏    |   98 |
    25. | 猪悟能    |   98 |
    26. +-----------+------+
    27. 7 rows in set (0.00 sec)
    28.  
    29. mysql> 
     

    NULL的排序
     

     

    1. mysql> select * from test_null;
    2. +------+--------+
    3. | id   | name   |
    4. +------+--------+
    5. |    1 | 张三   |
    6. | NULL | 张三   |
    7. |    1 | NULL   |
    8. | NULL | NULL   |
    9. |    1 |        |
    10. +------+--------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select * from test_null order by name asc;
    14. +------+--------+
    15. | id   | name   |
    16. +------+--------+
    17. |    1 | NULL   |
    18. | NULL | NULL   |
    19. |    1 |        |
    20. |    1 | 张三   |
    21. | NULL | 张三   |
    22. +------+--------+
    23. 5 rows in set (0.00 sec)
    24.  
    25. mysql> select * from test_null order by name desc;
    26. +------+--------+
    27. | id   | name   |
    28. +------+--------+
    29. |    1 | 张三   |
    30. | NULL | 张三   |
    31. |    1 |        |
    32. |    1 | NULL   |
    33. | NULL | NULL   |
    34. +------+--------+
    35. 5 rows in set (0.00 sec)
    36.  
    37. mysql> 
     

    NULL在排序中视作为比任何值都要小。
     

    查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
     

     

    1. mysql> select name ,math,english,chinese from exam_result;
    2. +-----------+------+---------+---------+
    3. | name      | math | english | chinese |
    4. +-----------+------+---------+---------+
    5. | 唐三藏    |   98 |      56 |      67 |
    6. | 孙悟空    |   78 |      77 |      87 |
    7. | 猪悟能    |   98 |      90 |      88 |
    8. | 曹孟德    |   84 |      67 |      82 |
    9. | 刘玄德    |   85 |      45 |      55 |
    10. | 孙权      |   73 |      78 |      70 |
    11. | 宋公明    |   65 |      30 |      75 |
    12. +-----------+------+---------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,math,english,chinese from exam_result order by math;
    16. +-----------+------+---------+---------+
    17. | name      | math | english | chinese |
    18. +-----------+------+---------+---------+
    19. | 宋公明    |   65 |      30 |      75 |
    20. | 孙权      |   73 |      78 |      70 |
    21. | 孙悟空    |   78 |      77 |      87 |
    22. | 曹孟德    |   84 |      67 |      82 |
    23. | 刘玄德    |   85 |      45 |      55 |
    24. | 唐三藏    |   98 |      56 |      67 |
    25. | 猪悟能    |   98 |      90 |      88 |
    26. +-----------+------+---------+---------+
    27. 7 rows in set (0.00 sec)
    28.  
    29. mysql> select name ,math,english,chinese from exam_result order by math desc;
    30. +-----------+------+---------+---------+
    31. | name      | math | english | chinese |
    32. +-----------+------+---------+---------+
    33. | 唐三藏    |   98 |      56 |      67 |
    34. | 猪悟能    |   98 |      90 |      88 |
    35. | 刘玄德    |   85 |      45 |      55 |
    36. | 曹孟德    |   84 |      67 |      82 |
    37. | 孙悟空    |   78 |      77 |      87 |
    38. | 孙权      |   73 |      78 |      70 |
    39. | 宋公明    |   65 |      30 |      75 |
    40. +-----------+------+---------+---------+
    41. 7 rows in set (0.00 sec)
    42.  
    43. mysql> select name ,math,english,chinese from exam_result order by math desc,english desc;
    44. +-----------+------+---------+---------+
    45. | name      | math | english | chinese |
    46. +-----------+------+---------+---------+
    47. | 猪悟能    |   98 |      90 |      88 |
    48. | 唐三藏    |   98 |      56 |      67 |
    49. | 刘玄德    |   85 |      45 |      55 |
    50. | 曹孟德    |   84 |      67 |      82 |
    51. | 孙悟空    |   78 |      77 |      87 |
    52. | 孙权      |   73 |      78 |      70 |
    53. | 宋公明    |   65 |      30 |      75 |
    54. +-----------+------+---------+---------+
    55. 7 rows in set (0.00 sec)
    56.  
    57. mysql> select name ,math,english,chinese from exam_result order by math desc,english desc,chinese;
    58. +-----------+------+---------+---------+
    59. | name      | math | english | chinese |
    60. +-----------+------+---------+---------+
    61. | 猪悟能    |   98 |      90 |      88 |
    62. | 唐三藏    |   98 |      56 |      67 |
    63. | 刘玄德    |   85 |      45 |      55 |
    64. | 曹孟德    |   84 |      67 |      82 |
    65. | 孙悟空    |   78 |      77 |      87 |
    66. | 孙权      |   73 |      78 |      70 |
    67. | 宋公明    |   65 |      30 |      75 |
    68. +-----------+------+---------+---------+
    69. 7 rows in set (0.00 sec)
    70.  
    71. mysql> select name ,math,english,chinese from exam_result order by math desc,english desc,chinese asc;
    72. +-----------+------+---------+---------+
    73. | name      | math | english | chinese |
    74. +-----------+------+---------+---------+
    75. | 猪悟能    |   98 |      90 |      88 |
    76. | 唐三藏    |   98 |      56 |      67 |
    77. | 刘玄德    |   85 |      45 |      55 |
    78. | 曹孟德    |   84 |      67 |      82 |
    79. | 孙悟空    |   78 |      77 |      87 |
    80. | 孙权      |   73 |      78 |      70 |
    81. | 宋公明    |   65 |      30 |      75 |
    82. +-----------+------+---------+---------+
    83. 7 rows in set (0.00 sec)
    84.  
    85. mysql> 
     

    默认的排序
     

    默认升序
     

     

    1. mysql> select name,math from exam_result;
    2. +-----------+------+
    3. | name      | math |
    4. +-----------+------+
    5. | 唐三藏    |   98 |
    6. | 孙悟空    |   78 |
    7. | 猪悟能    |   98 |
    8. | 曹孟德    |   84 |
    9. | 刘玄德    |   85 |
    10. | 孙权      |   73 |
    11. | 宋公明    |   65 |
    12. +-----------+------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name,math from exam_result order by math desc;
    16. +-----------+------+
    17. | name      | math |
    18. +-----------+------+
    19. | 唐三藏    |   98 |
    20. | 猪悟能    |   98 |
    21. | 刘玄德    |   85 |
    22. | 曹孟德    |   84 |
    23. | 孙悟空    |   78 |
    24. | 孙权      |   73 |
    25. | 宋公明    |   65 |
    26. +-----------+------+
    27. 7 rows in set (0.00 sec)
    28.  
    29. mysql> select name,math from exam_result order by math asc;
    30. +-----------+------+
    31. | name      | math |
    32. +-----------+------+
    33. | 宋公明    |   65 |
    34. | 孙权      |   73 |
    35. | 孙悟空    |   78 |
    36. | 曹孟德    |   84 |
    37. | 刘玄德    |   85 |
    38. | 唐三藏    |   98 |
    39. | 猪悟能    |   98 |
    40. +-----------+------+
    41. 7 rows in set (0.00 sec)
    42.  
    43. mysql> select name,math from exam_result order by math ;
    44. +-----------+------+
    45. | name      | math |
    46. +-----------+------+
    47. | 宋公明    |   65 |
    48. | 孙权      |   73 |
    49. | 孙悟空    |   78 |
    50. | 曹孟德    |   84 |
    51. | 刘玄德    |   85 |
    52. | 唐三藏    |   98 |
    53. | 猪悟能    |   98 |
    54. +-----------+------+
    55. 7 rows in set (0.00 sec)
    56.  
    57. mysql> 
     

    查询同学及总分，由高到低（mysql语句执行顺序）
     

     

    1. mysql> select name ,math+english+chinese from exam_result;
    2. +-----------+----------------------+
    3. | name      | math+english+chinese |
    4. +-----------+----------------------+
    5. | 唐三藏    |                  221 |
    6. | 孙悟空    |                  242 |
    7. | 猪悟能    |                  276 |
    8. | 曹孟德    |                  233 |
    9. | 刘玄德    |                  185 |
    10. | 孙权      |                  221 |
    11. | 宋公明    |                  170 |
    12. +-----------+----------------------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select name ,math+english+chinese as total from exam_result;
    16. +-----------+-------+
    17. | name      | total |
    18. +-----------+-------+
    19. | 唐三藏    |   221 |
    20. | 孙悟空    |   242 |
    21. | 猪悟能    |   276 |
    22. | 曹孟德    |   233 |
    23. | 刘玄德    |   185 |
    24. | 孙权      |   221 |
    25. | 宋公明    |   170 |
    26. +-----------+-------+
    27. 7 rows in set (0.01 sec)
    28.  
    29. mysql> select name ,math+english+chinese as total from exam_result order by total;
    30. +-----------+-------+
    31. | name      | total |
    32. +-----------+-------+
    33. | 宋公明    |   170 |
    34. | 刘玄德    |   185 |
    35. | 唐三藏    |   221 |
    36. | 孙权      |   221 |
    37. | 曹孟德    |   233 |
    38. | 孙悟空    |   242 |
    39. | 猪悟能    |   276 |
    40. +-----------+-------+
    41. 7 rows in set (0.00 sec)
    42.  
    43. mysql> select name ,math+english+chinese as total from exam_result order by total desc;
    44. +-----------+-------+
    45. | name      | total |
    46. +-----------+-------+
    47. | 猪悟能    |   276 |
    48. | 孙悟空    |   242 |
    49. | 曹孟德    |   233 |
    50. | 唐三藏    |   221 |
    51. | 孙权      |   221 |
    52. | 刘玄德    |   185 |
    53. | 宋公明    |   170 |
    54. +-----------+-------+
    55. 7 rows in set (0.00 sec)
    56.  
    57. mysql> 
     

     

     

    查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
     

     

     

    1. mysql> select name ,math from exam_result where name like '孙%' or name like '曹%';
    2. +-----------+------+
    3. | name      | math |
    4. +-----------+------+
    5. | 孙悟空    |   78 |
    6. | 曹孟德    |   84 |
    7. | 孙权      |   73 |
    8. +-----------+------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> select name ,math from exam_result where name like '孙%' or name like '曹%' order by math;
    12. +-----------+------+
    13. | name      | math |
    14. +-----------+------+
    15. | 孙权      |   73 |
    16. | 孙悟空    |   78 |
    17. | 曹孟德    |   84 |
    18. +-----------+------+
    19. 3 rows in set (0.00 sec)
    20.  
    21. mysql> select name ,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
    22. +-----------+------+
    23. | name      | math |
    24. +-----------+------+
    25. | 曹孟德    |   84 |
    26. | 孙悟空    |   78 |
    27. | 孙权      |   73 |
    28. +-----------+------+
    29. 3 rows in set (0.00 sec)
    30.  
    31. mysql> select * from exam_result limit 3 offset 1;
    32. +----+-----------+---------+------+---------+
    33. | id | name      | chinese | math | english |
    34. +----+-----------+---------+------+---------+
    35. |  2 | 孙悟空    |      87 |   78 |      77 |
    36. |  3 | 猪悟能    |      88 |   98 |      90 |
    37. |  4 | 曹孟德    |      82 |   84 |      67 |
    38. +----+-----------+---------+------+---------+
    39. 3 rows in set (0.00 sec)
    40.  
    41. mysql> select * from exam_result limit 5 offset 0;
    42. +----+-----------+---------+------+---------+
    43. | id | name      | chinese | math | english |
    44. +----+-----------+---------+------+---------+
    45. |  1 | 唐三藏    |      67 |   98 |      56 |
    46. |  2 | 孙悟空    |      87 |   78 |      77 |
    47. |  3 | 猪悟能    |      88 |   98 |      90 |
    48. |  4 | 曹孟德    |      82 |   84 |      67 |
    49. |  5 | 刘玄德    |      55 |   85 |      45 |
    50. +----+-----------+---------+------+---------+
    51. 5 rows in set (0.00 sec)
    52.  
    53. mysql> 
     

     

    筛选分页
     

    limit的使用
     

     

     

    1. mysql> select * from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   78 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      82 |   84 |      67 |
    9. |  5 | 刘玄德    |      55 |   85 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   65 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select * from exam_result limit 5;
    16. +----+-----------+---------+------+---------+
    17. | id | name      | chinese | math | english |
    18. +----+-----------+---------+------+---------+
    19. |  1 | 唐三藏    |      67 |   98 |      56 |
    20. |  2 | 孙悟空    |      87 |   78 |      77 |
    21. |  3 | 猪悟能    |      88 |   98 |      90 |
    22. |  4 | 曹孟德    |      82 |   84 |      67 |
    23. |  5 | 刘玄德    |      55 |   85 |      45 |
    24. +----+-----------+---------+------+---------+
    25. 5 rows in set (0.00 sec)
    26.  
    27. mysql> select * from exam_result limit 1;
    28. +----+-----------+---------+------+---------+
    29. | id | name      | chinese | math | english |
    30. +----+-----------+---------+------+---------+
    31. |  1 | 唐三藏    |      67 |   98 |      56 |
    32. +----+-----------+---------+------+---------+
    33. 1 row in set (0.00 sec)
    34.  
    35. mysql> select * from exam_result limit 2;
    36. +----+-----------+---------+------+---------+
    37. | id | name      | chinese | math | english |
    38. +----+-----------+---------+------+---------+
    39. |  1 | 唐三藏    |      67 |   98 |      56 |
    40. |  2 | 孙悟空    |      87 |   78 |      77 |
    41. +----+-----------+---------+------+---------+
    42. 2 rows in set (0.00 sec)
    43.  
    44. mysql> select * from exam_result limit 3;
    45. +----+-----------+---------+------+---------+
    46. | id | name      | chinese | math | english |
    47. +----+-----------+---------+------+---------+
    48. |  1 | 唐三藏    |      67 |   98 |      56 |
    49. |  2 | 孙悟空    |      87 |   78 |      77 |
    50. |  3 | 猪悟能    |      88 |   98 |      90 |
    51. +----+-----------+---------+------+---------+
    52. 3 rows in set (0.00 sec)
    53.  
    54. mysql> select * from exam_result limit 1,3;
    55. +----+-----------+---------+------+---------+
    56. | id | name      | chinese | math | english |
    57. +----+-----------+---------+------+---------+
    58. |  2 | 孙悟空    |      87 |   78 |      77 |
    59. |  3 | 猪悟能    |      88 |   98 |      90 |
    60. |  4 | 曹孟德    |      82 |   84 |      67 |
    61. +----+-----------+---------+------+---------+
    62. 3 rows in set (0.00 sec)
    63.  
    64. mysql> select * from exam_result limit 2,4;
    65. +----+-----------+---------+------+---------+
    66. | id | name      | chinese | math | english |
    67. +----+-----------+---------+------+---------+
    68. |  3 | 猪悟能    |      88 |   98 |      90 |
    69. |  4 | 曹孟德    |      82 |   84 |      67 |
    70. |  5 | 刘玄德    |      55 |   85 |      45 |
    71. |  6 | 孙权      |      70 |   73 |      78 |
    72. +----+-----------+---------+------+---------+
    73. 4 rows in set (0.00 sec)
    74.  
    75. mysql> select * from exam_result limit 2,1;
    76. +----+-----------+---------+------+---------+
    77. | id | name      | chinese | math | english |
    78. +----+-----------+---------+------+---------+
    79. |  3 | 猪悟能    |      88 |   98 |      90 |
    80. +----+-----------+---------+------+---------+
    81. 1 row in set (0.00 sec)
    82.  
    83. mysql> select * from exam_result limit 2,2;
    84. +----+-----------+---------+------+---------+
    85. | id | name      | chinese | math | english |
    86. +----+-----------+---------+------+---------+
    87. |  3 | 猪悟能    |      88 |   98 |      90 |
    88. |  4 | 曹孟德    |      82 |   84 |      67 |
    89. +----+-----------+---------+------+---------+
    90. 2 rows in set (0.00 sec)
    91.  
    92. mysql> select * from exam_result limit 2,3;
    93. +----+-----------+---------+------+---------+
    94. | id | name      | chinese | math | english |
    95. +----+-----------+---------+------+---------+
    96. |  3 | 猪悟能    |      88 |   98 |      90 |
    97. |  4 | 曹孟德    |      82 |   84 |      67 |
    98. |  5 | 刘玄德    |      55 |   85 |      45 |
    99. +----+-----------+---------+------+---------+
    100. 3 rows in set (0.00 sec)
    101.  
    102. mysql> select * from exam_result limit 2,4;
    103. +----+-----------+---------+------+---------+
    104. | id | name      | chinese | math | english |
    105. +----+-----------+---------+------+---------+
    106. |  3 | 猪悟能    |      88 |   98 |      90 |
    107. |  4 | 曹孟德    |      82 |   84 |      67 |
    108. |  5 | 刘玄德    |      55 |   85 |      45 |
    109. |  6 | 孙权      |      70 |   73 |      78 |
    110. +----+-----------+---------+------+---------+
    111. 4 rows in set (0.00 sec)
    112.  
    113. mysql> select * from exam_result limit 0,4;
    114. +----+-----------+---------+------+---------+
    115. | id | name      | chinese | math | english |
    116. +----+-----------+---------+------+---------+
    117. |  1 | 唐三藏    |      67 |   98 |      56 |
    118. |  2 | 孙悟空    |      87 |   78 |      77 |
    119. |  3 | 猪悟能    |      88 |   98 |      90 |
    120. |  4 | 曹孟德    |      82 |   84 |      67 |
    121. +----+-----------+---------+------+---------+
    122. 4 rows in set (0.00 sec)
    123.  
    124. mysql>
     

    分页的具体操作
     

     

    1. mysql> select * from exam_result;
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  1 | 唐三藏    |      67 |   98 |      56 |
    6. |  2 | 孙悟空    |      87 |   78 |      77 |
    7. |  3 | 猪悟能    |      88 |   98 |      90 |
    8. |  4 | 曹孟德    |      82 |   84 |      67 |
    9. |  5 | 刘玄德    |      55 |   85 |      45 |
    10. |  6 | 孙权      |      70 |   73 |      78 |
    11. |  7 | 宋公明    |      75 |   65 |      30 |
    12. +----+-----------+---------+------+---------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. mysql> select * from exam_result limit 0,3;
    16. +----+-----------+---------+------+---------+
    17. | id | name      | chinese | math | english |
    18. +----+-----------+---------+------+---------+
    19. |  1 | 唐三藏    |      67 |   98 |      56 |
    20. |  2 | 孙悟空    |      87 |   78 |      77 |
    21. |  3 | 猪悟能    |      88 |   98 |      90 |
    22. +----+-----------+---------+------+---------+
    23. 3 rows in set (0.00 sec)
    24.  
    25. mysql> select * from exam_result limit 3,3;
    26. +----+-----------+---------+------+---------+
    27. | id | name      | chinese | math | english |
    28. +----+-----------+---------+------+---------+
    29. |  4 | 曹孟德    |      82 |   84 |      67 |
    30. |  5 | 刘玄德    |      55 |   85 |      45 |
    31. |  6 | 孙权      |      70 |   73 |      78 |
    32. +----+-----------+---------+------+---------+
    33. 3 rows in set (0.00 sec)
    34.  
    35. mysql> select * from exam_result limit 6,3;
    36. +----+-----------+---------+------+---------+
    37. | id | name      | chinese | math | english |
    38. +----+-----------+---------+------+---------+
    39. |  7 | 宋公明    |      75 |   65 |      30 |
    40. +----+-----------+---------+------+---------+
    41. 1 row in set (0.00 sec)
    42.  
    43. mysql> select * from exam_result limit 3 offset 0;
    44. +----+-----------+---------+------+---------+
    45. | id | name      | chinese | math | english |
    46. +----+-----------+---------+------+---------+
    47. |  1 | 唐三藏    |      67 |   98 |      56 |
    48. |  2 | 孙悟空    |      87 |   78 |      77 |
    49. |  3 | 猪悟能    |      88 |   98 |      90 |
    50. +----+-----------+---------+------+---------+
    51. 3 rows in set (0.00 sec)
    52.  
    53. mysql> select * from exam_result limit 3 offset 3;
    54. +----+-----------+---------+------+---------+
    55. | id | name      | chinese | math | english |
    56. +----+-----------+---------+------+---------+
    57. |  4 | 曹孟德    |      82 |   84 |      67 |
    58. |  5 | 刘玄德    |      55 |   85 |      45 |
    59. |  6 | 孙权      |      70 |   73 |      78 |
    60. +----+-----------+---------+------+---------+
    61. 3 rows in set (0.00 sec)
    62.  
    63. mysql> select * from exam_result limit 3 offset 6;
    64. +----+-----------+---------+------+---------+
    65. | id | name      | chinese | math | english |
    66. +----+-----------+---------+------+---------+
    67. |  7 | 宋公明    |      75 |   65 |      30 |
    68. +----+-----------+---------+------+---------+
    69. 1 row in set (0.00 sec)
    70.  
    71. mysql> 
     

    同学名字及总分，总分大于200分，总分降序排序，分页操作
     

     

    1. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total;
    2. +-----------+-------+
    3. | name      | total |
    4. +-----------+-------+
    5. | 唐三藏    |   221 |
    6. | 孙权      |   221 |
    7. | 曹孟德    |   233 |
    8. | 孙悟空    |   242 |
    9. | 猪悟能    |   276 |
    10. +-----------+-------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc;
    14. +-----------+-------+
    15. | name      | total |
    16. +-----------+-------+
    17. | 猪悟能    |   276 |
    18. | 孙悟空    |   242 |
    19. | 曹孟德    |   233 |
    20. | 唐三藏    |   221 |
    21. | 孙权      |   221 |
    22. +-----------+-------+
    23. 5 rows in set (0.01 sec)
    24.  
    25. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 1;
    26. +-----------+-------+
    27. | name      | total |
    28. +-----------+-------+
    29. | 猪悟能    |   276 |
    30. +-----------+-------+
    31. 1 row in set (0.00 sec)
    32.  
    33. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 0;
    34. +-----------+-------+
    35. | name      | total |
    36. +-----------+-------+
    37. | 猪悟能    |   276 |
    38. | 孙悟空    |   242 |
    39. +-----------+-------+
    40. 2 rows in set (0.00 sec)
    41.  
    42. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 2;
    43. +-----------+-------+
    44. | name      | total |
    45. +-----------+-------+
    46. | 曹孟德    |   233 |
    47. | 唐三藏    |   221 |
    48. +-----------+-------+
    49. 2 rows in set (0.00 sec)
    50.  
    51. mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 4;
    52. +--------+-------+
    53. | name   | total |
    54. +--------+-------+
    55. | 孙权   |   221 |
    56. +--------+-------+
    57. 1 row in set (0.00 sec)
    58.  
    59. mysql> 
     

     

    表之“删”操作
     

    删表
     

     

    1. mysql> create table t1( id int, name varchar(20) );
    2. Query OK, 0 rows affected (0.02 sec)
    3.  
    4. mysql> show tables;
    5. +--------------+
    6. | Tables_in_d1 |
    7. +--------------+
    8. | exam_result  |
    9. | students     |
    10. | t1           |
    11. +--------------+
    12. 3 rows in set (0.00 sec)
    13.  
    14. mysql> drop table if exists t1;
    15. Query OK, 0 rows affected (0.01 sec)
    16.  
    17. mysql> show tables;
    18. +--------------+
    19. | Tables_in_d1 |
    20. +--------------+
    21. | exam_result  |
    22. | students     |
    23. +--------------+
    24. 2 rows in set (0.00 sec)
    25.  
    26. mysql> 
     

    Delete
     

    删除孙悟空同学的考试成绩
     

     

    1. mysql> select *from exam_result where name='孙悟空';
    2. +----+-----------+---------+------+---------+
    3. | id | name      | chinese | math | english |
    4. +----+-----------+---------+------+---------+
    5. |  2 | 孙悟空    |     174 |   80 |      77 |
    6. +----+-----------+---------+------+---------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> delete from exam_result where name='孙悟空';
    10. Query OK, 1 row affected (0.00 sec)
    11.  
    12. mysql> select *from exam_result;
    13. +----+-----------+---------+------+---------+
    14. | id | name      | chinese | math | english |
    15. +----+-----------+---------+------+---------+
    16. |  1 | 唐三藏    |     134 |   98 |      56 |
    17. |  3 | 猪悟能    |     176 |   98 |      90 |
    18. |  4 | 曹孟德    |     140 |   90 |      67 |
    19. |  5 | 刘玄德    |     110 |  115 |      45 |
    20. |  6 | 孙权      |     140 |   73 |      78 |
    21. |  7 | 宋公明    |     150 |   95 |      30 |
    22. +----+-----------+---------+------+---------+
    23. 6 rows in set (0.00 sec)
    24.  
    25. mysql> select name ,chinese +math+english total from exam_result ;
    26. +-----------+-------+
    27. | name      | total |
    28. +-----------+-------+
    29. | 唐三藏    |   288 |
    30. | 猪悟能    |   364 |
    31. | 曹孟德    |   297 |
    32. | 刘玄德    |   270 |
    33. | 孙权      |   291 |
    34. | 宋公明    |   275 |
    35. +-----------+-------+
    36. 6 rows in set (0.00 sec)
    37.  
    38. mysql> select name ,chinese +math+english total from exam_result order by total;
    39. +-----------+-------+
    40. | name      | total |
    41. +-----------+-------+
    42. | 刘玄德    |   270 |
    43. | 宋公明    |   275 |
    44. | 唐三藏    |   288 |
    45. | 孙权      |   291 |
    46. | 曹孟德    |   297 |
    47. | 猪悟能    |   364 |
    48. +-----------+-------+
    49. 6 rows in set (0.00 sec)
    50.  
    51. mysql> select name ,chinese +math+english total from exam_result order by total desc;
    52. +-----------+-------+
    53. | name      | total |
    54. +-----------+-------+
    55. | 猪悟能    |   364 |
    56. | 曹孟德    |   297 |
    57. | 孙权      |   291 |
    58. | 唐三藏    |   288 |
    59. | 宋公明    |   275 |
    60. | 刘玄德    |   270 |
    61. +-----------+-------+
    62. 6 rows in set (0.01 sec)
    63.  
    64. mysql> select name ,chinese +math+english total from exam_result order by total asc;
    65. +-----------+-------+
    66. | name      | total |
    67. +-----------+-------+
    68. | 刘玄德    |   270 |
    69. | 宋公明    |   275 |
    70. | 唐三藏    |   288 |
    71. | 孙权      |   291 |
    72. | 曹孟德    |   297 |
    73. | 猪悟能    |   364 |
    74. +-----------+-------+
    75. 6 rows in set (0.00 sec)
    76.  
    77. mysql> select name ,chinese +math+english total from exam_result order by total asc limit 1;
    78. +-----------+-------+
    79. | name      | total |
    80. +-----------+-------+
    81. | 刘玄德    |   270 |
    82. +-----------+-------+
    83. 1 row in set (0.00 sec)
    84.  
    85. mysql> delete from exam_result order by english +math+chinese asc limit 1;
    86. Query OK, 1 row affected (0.00 sec)
    87.  
    88. mysql> select name ,chinese+math+english total from exam_result order by total asc limit 1;
    89. +-----------+-------+
    90. | name      | total |
    91. +-----------+-------+
    92. | 宋公明    |   275 |
    93. +-----------+-------+
    94. 1 row in set (0.00 sec)
    95.  
    96. mysql> select * from exam_result;
    97. +----+-----------+---------+------+---------+
    98. | id | name      | chinese | math | english |
    99. +----+-----------+---------+------+---------+
    100. |  1 | 唐三藏    |     134 |   98 |      56 |
    101. |  3 | 猪悟能    |     176 |   98 |      90 |
    102. |  4 | 曹孟德    |     140 |   90 |      67 |
    103. |  6 | 孙权      |     140 |   73 |      78 |
    104. |  7 | 宋公明    |     150 |   95 |      30 |
    105. +----+-----------+---------+------+---------+
    106. 5 rows in set (0.00 sec)
    107.  
    108. mysql> 
     

    删除整个表
     

     

    1. mysql> create table for_delete(
    2.     -> id int primary key auto_increment,
    3.     -> name varchar(20)
    4.     -> );
    5. Query OK, 0 rows affected (0.02 sec)
    6.  
    7. mysql> insert into for_delete (name) value ('A'),('B'),('C');
    8. Query OK, 3 rows affected (0.01 sec)
    9. Records: 3  Duplicates: 0  Warnings: 0
    10.  
    11. mysql> show CREATE table for_delete\G
    12. *************************** 1. row ***************************
    13.        Table: for_delete
    14. Create Table: CREATE TABLE `for_delete` (
    15.   `id` int(11) NOT NULL AUTO_INCREMENT,
    16.   `name` varchar(20) DEFAULT NULL,
    17.   PRIMARY KEY (`id`)
    18. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
    19. 1 row in set (0.02 sec)
    20.  
    21. mysql> delete from for_delete;
    22. Query OK, 3 rows affected (0.01 sec)
    23.  
    24. mysql> select *from for_delete;
    25. Empty set (0.00 sec)
    26.  
    27. mysql> show create table for_delete\G
    28. *************************** 1. row ***************************
    29.        Table: for_delete
    30. Create Table: CREATE TABLE `for_delete` (
    31.   `id` int(11) NOT NULL AUTO_INCREMENT,
    32.   `name` varchar(20) DEFAULT NULL,
    33.   PRIMARY KEY (`id`)
    34. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
    35. 1 row in set (0.00 sec)
    36.  
    37. mysql> insert into for_delete (name) value ('E');
    38. Query OK, 1 row affected (0.00 sec)
    39.  
    40. mysql> select * from for_delete;
    41. +----+------+
    42. | id | name |
    43. +----+------+
    44. |  4 | E    |
    45. +----+------+
    46. 1 row in set (0.00 sec)
    47.  
    48. mysql> 
     

    截断表
     

    无法回滚，会重置auto_increment项。
     

     

    1. mysql> create table for_truncate(
    2.     -> id int primary key auto_increment,
    3.     -> name varchar(20)
    4.     -> );
    5. Query OK, 0 rows affected (0.04 sec)
    6.  
    7. mysql> desc for_truncate;
    8. +-------+-------------+------+-----+---------+----------------+
    9. | Field | Type        | Null | Key | Default | Extra          |
    10. +-------+-------------+------+-----+---------+----------------+
    11. | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    12. | name  | varchar(20) | YES  |     | NULL    |                |
    13. +-------+-------------+------+-----+---------+----------------+
    14. 2 rows in set (0.00 sec)
    15.  
    16. mysql> insert into for_truncate (name) value('A'),('B'),('C');
    17. Query OK, 3 rows affected (0.01 sec)
    18. Records: 3  Duplicates: 0  Warnings: 0
    19.  
    20. mysql> select * from for_truncate;
    21. +----+------+
    22. | id | name |
    23. +----+------+
    24. |  1 | A    |
    25. |  2 | B    |
    26. |  3 | C    |
    27. +----+------+
    28. 3 rows in set (0.00 sec)
    29.  
    30. mysql> show create table for_truncate\G
    31. *************************** 1. row ***************************
    32.        Table: for_truncate
    33. Create Table: CREATE TABLE `for_truncate` (
    34.   `id` int(11) NOT NULL AUTO_INCREMENT,
    35.   `name` varchar(20) DEFAULT NULL,
    36.   PRIMARY KEY (`id`)
    37. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
    38. 1 row in set (0.00 sec)
    39.  
    40. mysql> truncate for_truncate;
    41. Query OK, 0 rows affected (0.02 sec)
    42.  
    43. mysql> show tables;
    44. +--------------+
    45. | Tables_in_d1 |
    46. +--------------+
    47. | exam_result  |
    48. | for_delete   |
    49. | for_truncate |
    50. | students     |
    51. | test_null    |
    52. +--------------+
    53. 5 rows in set (0.00 sec)
    54.  
    55. mysql> select * from for_truncate;
    56. Empty set (0.00 sec)
    57.  
    58. mysql> show create table for_truncate\G
    59. *************************** 1. row ***************************
    60.        Table: for_truncate
    61. Create Table: CREATE TABLE `for_truncate` (
    62.   `id` int(11) NOT NULL AUTO_INCREMENT,
    63.   `name` varchar(20) DEFAULT NULL,
    64.   PRIMARY KEY (`id`)
    65. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    66. 1 row in set (0.00 sec)
    67.  
    68. mysql> insert into for_truncate (name) values ('E');
    69. Query OK, 1 row affected (0.01 sec)
    70.  
    71. mysql> select *from for_truncate;
    72. +----+------+
    73. | id | name |
    74. +----+------+
    75. |  1 | E    |
    76. +----+------+
    77. 1 row in set (0.01 sec)
    78.  
    79. mysql> show create table for_truncate\G
    80. *************************** 1. row ***************************
    81.        Table: for_truncate
    82. Create Table: CREATE TABLE `for_truncate` (
    83.   `id` int(11) NOT NULL AUTO_INCREMENT,
    84.   `name` varchar(20) DEFAULT NULL,
    85.   PRIMARY KEY (`id`)
    86. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
    87. 1 row in set (0.00 sec)
    88.  
    89. mysql> 
     

    结尾
     

    最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
     

     

    同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
     

     

    谢谢您的支持，期待与您在下一篇文章中再次相遇！
     

    
                
    
                    
    
                
    • 

                
  • 

                    相关阅读:
    
                    
PCB layout有DRC为什么还要用CAM和DFM检查？                            
    
echarts文档解读                            
    
centos启动停留在started GNOME display manager                            
    
Pandas时序数据Time Series                            
    
ram和rom的区别                            
    
Python函数                            
    
Java volatile功能简介说明                            
    
动手学深度学习（pytorch）2                            
    
什么是单链表？                            
    
如何通过7个步骤编写出色的在线用户手册                            
    
                        
                
    • 
                
  • 
                    原文地址：https://blog.csdn.net/m0_74163972/article/details/136310628
                
    •