數(shù)據(jù)庫增刪改查
1.創(chuàng)建表
樣式表.png
mysql> create table class
-> id int primary key auto_increment,
-> sname varchar(10) not null default '',
-> gender char(1) not null default '',
-> company varchar(20) not null default '',
-> salary decimal(6,2) not null default 0.00,
-> fanbu smallint not null default 0
-> ) engine=myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)
2.查詢表結(jié)構(gòu)
mysql> desc class;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | char(1) | NO | | | |
| company | varchar(20) | NO | | | |
| salary | decimal(6,2) | NO | | 0.00 | |
| fanbu | smallint(6) | NO | | 0 | |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
3.添加數(shù)據(jù)
往那張表 添加行 class
添加哪幾列（id ，sname，gender，compangy，salary，fanbu）
分別添加什么值 ？ (1,'張三','男','百度','8888','500')
- 插入所有數(shù)據(jù)的情況：
mysql> insert into class
-> (id,sname,gender,company,salary,fanbu)
-> values
-> (1,'張三','男','百度',8888.88,234);
Query OK, 1 row affected (0.01 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 張三 | 男 | 百度 | 8888.88 | 234 |
+----+-------+--------+---------+---------+-------+
1 row in set (0.00 sec)
- 也可以插入部分?jǐn)?shù)據(jù)：
（id 為自增型 ）
mysql> insert into class
-> (sname,gender,salary)
-> values
-> ('刀鋒','男',8767.98);
Query OK, 1 row affected (0.01 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 張三 | 男 | 百度 | 8888.88 | 234 |
| 2 | 刀鋒 | 男 | | 8767.98 | 0 |
+----+-------+--------+---------+---------+-------+
2 rows in set (0.00 sec)
- 再來看插入所有數(shù)據(jù)：
如果插入所有列，則可以不聲明待插入的列，即，如果不聲明插入的列，則理解為依次插入所有的列
mysql> insert into class
-> values
-> (3,'李思思','女','新浪',9000.50,500);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+--------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+--------+--------+---------+---------+-------+
| 1 | 張三 | 男 | 百度 | 8888.88 | 234 |
| 2 | 刀鋒 | 男 | | 8767.98 | 0 |
| 3 | 李思思 | 女 | 新浪 | 9000.50 | 500 |
+----+--------+--------+---------+---------+-------+
3 rows in set (0.00 sec)
-
不要犯如下錯(cuò)誤
如果你認(rèn)為id 是自增型的，插入時(shí)不必要為其賦值；
這是一種常見的錯(cuò)誤，列與值必須按順序一一對(duì)應(yīng)；
