黑马程序员---MySQL数据库02---表的增删改查

一：向表中增加数据：
整体一行插入：insert into 表名 values(值，值…)；
按列名插入：insert into 表名 (列名，列名) values(值，值)；
插入多行： insert into 表名 values(值，值…)，(值，值…)；

mysql> insert into students values(0,"小明",23,185,'男',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   23 | 185.00 | 男     |      1 |
+----+--------+------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> insert into students(name,age,high) values('小芳',24,165);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   23 | 185.00 | 男     |      1 |
|  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
2 rows in set (0.00 sec)

mysql> insert into students(name, age) values("张三", 21),("李四", 22);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   23 | 185.00 | 男     |      1 |
|  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
|  3 | 张三   |   21 |   NULL | NULL   |   NULL |
|  4 | 李四   |   22 |   NULL | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)

二：修改表中的数据：
1：按照条件修改。：update 表名 set 列1=值1，列2= 值2 where 条件；
2：对整个表全部修改。update 表名 set 列1=值1；

mysql> update students set age = 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   10 | 185.00 | 男     |      1 |
|  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
|  3 | 张三   |   21 |   NULL | NULL   |   NULL |
|  4 | 李四   |   22 |   NULL | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> update students set high = 189;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   10 | 189.00 | 男     |      1 |
|  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
|  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
|  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)

三：查找表中的数据：
1:查找所有：

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   10 | 189.00 | 男     |      1 |
|  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
|  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
|  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)

2：根据where 条件查找：

mysql> select * from students where high=189;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   10 | 189.00 | 男     |      1 |
|  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
|  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
|  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)

3：查找指定的列：并且列有顺序：
注意：顺序也是gender name,high

mysql> select gender,name,high from students;
+--------+--------+--------+
| gender | name   | high   |
+--------+--------+--------+
| 男     | 小明   | 189.00 |
| NULL   | 小芳   | 189.00 |
| NULL   | 张三   | 189.00 |
| NULL   | 李四   | 189.00 |
+--------+--------+--------+
4 rows in set (0.00 sec)

四：删除表中数据：
1:根据where条件删除：

mysql> delete  from students where id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | high   | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | 小明   |   10 | 189.00 | 男     |      1 |
|  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
|  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
+----+--------+------+--------+--------+--------+
3 rows in set (0.00 sec)