MySQL数据操作语句

数据操作语句主要包括INSERT ,UPDATE ,DELETE 

INSERT语法
1、insert 语句中不指定具体的字段名

mysql> insert into testa values('user1',2,'2016-01-31');
Query OK, 1 row affected (0.00 sec)

 

2、insert 语句中列出所有字段

mysql> insert into testa(user_id,ud_id,user_creation_date) values('user2',3,'2016-02-1 01:28:22'); 
Query OK, 1 row affected (0.00 sec)

 

3、insert为表的指定字段插入数据

mysql> insert into testa(user_id,ud_id) values('user3',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testa where user_id='user3' ; 
+---------+-------+--------------------+
| user_id | ud_id | user_creation_date |
+---------+-------+--------------------+
| user3 | 3 | NULL |
+---------+-------+--------------------+
1 row in set (0.00 sec)

 

4、同时插入多条记录

mysql> insert into testa(user_id,ud_id) values('user3',3),
-> ('user4',4),
-> ('user5',5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from testa where user_id IN ('user3','user4','user5'); 
+---------+-------+--------------------+
| user_id | ud_id | user_creation_date |
+---------+-------+--------------------+
| user3 | 3 | NULL |
| user3 | 3 | NULL |
| user4 | 4 | NULL |
| user5 | 5 | NULL |
+---------+-------+--------------------+
4 rows in set (0.00 sec)

 

5、将查询结果插入到表中

mysql> insert into testa(user_id,ud_id,user_creation_date) select * from test where user_id like 'test9%';
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0

 

 

更新数据：
UPDATE 表名
SET 属性名1=取值1，属性名2 =取值2，... 属性名n=取值n
WHERE 条件表达式

mysql> update testa set user_id='a1' where user_id='test1'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 

删除数据

mysql> delete from testa where user_id='a1';
Query OK, 1 row affected (0.00 sec)

 

删除一个表中所有数据

mysql> delete from testa;
Query OK, 33 rows affected (0.00 sec)

 

参考书籍：《MySQL入门很简单》

转载于:https://www.cnblogs.com/xiangshui/p/5211466.html