insert操作

掌握insert操作和注意事项

insert 所有列

insert 指定列

注意：列与值，严格对应

数字和字符串的注意点

数字不必加单引号，字符串必须加单引号。

---------------------------------------------------------------------------------------------

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.5.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| b              |
| boy            |
| category       |
| girl           |
| goods          |
| m              |
| mian           |
| result         |
| t              |
| user           |
+----------------+
11 rows in set (0.00 sec)

mysql> desc user;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| uid   | int(11)              | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   |     |         |                |
| age   | smallint(5) unsigned | NO   |     | 0       |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into user (uid,name,age) values (1,'lisi',23);
Query OK, 1 row affected (0.00 sec)


mysql> select * from user;
+-----+------+-----+
| uid | name | age |
+-----+------+-----+
|   1 | lisi |  23 |
+-----+------+-----+
1 row in set (0.00 sec)

mysql> insert into user (uid,name) values (2,'lucy');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+-----+------+-----+
| uid | name | age |
+-----+------+-----+
|   1 | lisi |  23 |
|   2 | lucy |   0 |
+-----+------+-----+
2 rows in set (0.00 sec)

mysql> insert into user (name) values('lilei');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+-----+-------+-----+
| uid | name  | age |
+-----+-------+-----+
|   1 | lisi  |  23 |
|   2 | lucy  |   0 |
|   3 | lilei |   0 |
+-----+-------+-----+
3 rows in set (0.00 sec)

mysql> desc user;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| uid   | int(11)              | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   |     |         |                |
| age   | smallint(5) unsigned | NO   |     | 0       |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into user values (4,'white','36');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+-----+-------+-----+
| uid | name  | age |
+-----+-------+-----+
|   1 | lisi  |  23 |
|   2 | lucy  |   0 |
|   3 | lilei |   0 |
|   4 | white |  36 |
+-----+-------+-----+
4 rows in set (0.00 sec)

mysql>