插入数据

插入表的值得顺序按insert into TABLE 后面得匹配项顺序来写得。匹配项可以省略，省略后按表得列顺序插入值（desc TABLE_NAME可以查看列顺序）可以一次插入多条值，值与值之间用，隔开。

  1. mysql> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | mysql              |
  7. | performance_schema |
  8. | stu                |
  9. | sys                |
  10. +--------------------+
  11. 5 rows in set (0.02 sec)
  13. mysql> use stu;
  14. Database changed
  15. mysql> show tables;
  16. +---------------+
  17. | Tables_in_stu |
  18. +---------------+
  19. | student       |
  20. | students      |
  21. +---------------+
  22. 2 rows in set (0.00 sec)
  24. mysql> desc students;
  25. +-----------+-------------+------+-----+---------+----------------+
  26. | Field     | Type        | Null | Key | Default | Extra          |
  27. +-----------+-------------+------+-----+---------+----------------+
  28. | id        | int(20)     | NO   | PRI | NULL    | auto_increment |
  29. | name      | varchar(10) | NO   |     | NULL    |                |
  30. | tel_phone | varchar(13) | YES  |     | NULL    |                |
  31. | address   | varchar(20) | YES  |     | NULL    |                |
  32. +-----------+-------------+------+-----+---------+----------------+
  33. 4 rows in set (0.03 sec)
  34. mysql> --插入数据
  35. mysql> insert into students (id, name, tel_phone,address)
  36.     -> values(1, 'frank', '123455','shanghai');
  37. Query OK, 1 row affected (0.00 sec)
  39. mysql> -- 第一个id已经插入，id自动增长
  40. mysql> insert into students (name, tel_phone,address)
  41.     -> values( 'jerry', '123455','shanghai');
  42. Query OK, 1 row affected (0.00 sec)
  44. mysql> -- 如果数据不知道，则填NULL或default
  45. mysql> insert into students (name, tel_phone,address)
  46.     -> values( 'roll', NULL, default);
  47. Query OK, 1 row affected (0.00 sec)
  49. mysql> select * from students;
  50. +----+-------+-----------+----------+
  51. | id | name  | tel_phone | address  |
  52. +----+-------+-----------+----------+
  53. |  1 | frank | 123455    | shanghai |
  54. |  2 | jerry | 123455    | shanghai |
  55. |  3 | roll  | NULL      | NULL     |
  56. +----+-------+-----------+----------+
  57. 3 rows in set (0.00 sec)

修改数据

  1. mysql> select * from students;
  2. +----+------+-----------+---------+
  3. | id | name | tel_phone | address |
  4. +----+------+-----------+---------+
  5. |  1 | tom  | 1234566   | NULL    |
  6. +----+------+-----------+---------+
  7. 1 row in set (0.00 sec)
  9. mysql> update students set name='frank' where id=1;
  10. Query OK, 1 row affected (0.00 sec)
  11. Rows matched: 1  Changed: 1  Warnings: 0

删除数据

  1. mysql> delete from students where id=3;
  2. Query OK, 1 row affected (0.01 sec)

清空表

delete 是遍历表，比较慢。trancate 是直接创建删除整张表，再克隆一张新的一模一样表。
delect不会删除主键的计数器，而trancate会。举例说就是，假设表中id为主键，假设id最大为 id=100.当使用delete删除这100条数据后，再插入一条数据，id的计数是从101开始，而trancate是从id=1开始

  1. mysql> select * from students;
  2. +----+-------+-----------+----------+
  3. | id | name  | tel_phone | address  |
  4. +----+-------+-----------+----------+
  5. |  1 | frank | 123455    | shanghai |
  6. |  2 | jerry | 123455    | shanghai |
  7. +----+-------+-----------+----------+
  8. 2 rows in set (0.00 sec)
  10. mysql> truncate table students;
  11. Query OK, 0 rows affected (0.01 sec)
  13. mysql> select * from students;
  14. Empty set (0.00 sec)