Data Manipulation Language 由插入insert into、修改update、删除delete from三个命令组成。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| learning |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database learning3;
Query OK, 1 row affected (0.01 sec)
mysql> use learning3;
Database changed
mysql> create table houses (name varchar(100) not null,house_location varchar(10
0) not null,purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> select name,house_location,purchasing_year from houses;
Empty set (0.00 sec)
mysql> insert into houses(name,house_location,purchasing_year) values ('甲','天
河',1997);
Query OK, 1 row affected (0.00 sec)
mysql> select name,house_location,purchasing_year from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> create table houses2 (name varchar(100)not null,house_location varchar(10
0) not null, purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | varchar(100) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc houses2;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | varchar(100) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from houses2;
Empty set (0.00 sec)
mysql> insert into houses2 select name,house_location,purchasing_year from house
s;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> update houses2 set name='乙';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> delete from houses2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from houses2;
Empty set (0.00 sec)
mysql> insert into houses values('乙','番禺',1998);
Query OK, 1 row affected (0.00 sec)
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 乙 | 番禺 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> insert into houses2 select * from houses where name='乙';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙 | 番禺 | 1998 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> insert into houses2 select name,house_location,purchasing_year from house
s where house_location='天河';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙 | 番禺 | 1998 |
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> delete from houses2;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from houses2;
Empty set (0.00 sec)
mysql> desc houses2;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | varchar(100) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> drop table houses2;
Query OK, 0 rows affected (0.02 sec)
mysql> desc houses2;
ERROR 1146 (42S02): Table 'learning3.houses2' doesn't exist
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| houses |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 乙 | 番禺 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> update houses set name='yi',house_location='panyu',purchasing_year=1996 w
here name='乙';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| yi | panyu | 1996 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)删除操作基本语法：
DELETE FROM table_name (WHERE Clause);
修改基本语法：
UPDATE table_name SET field1=new_value1, field2=new_value2 (WHERE Clause);