一、DML语句
CRUD-----增删改查
1、增加(insert)
mysql> insert into sanguoyanyi values (null,“魏延”,“男”,34,“湖南长沙”);
mysql> insert into sanguoyanyi (name,sex,age,address) values (“黄忠”,“男”,62,“湖南长沙”);
注:(1)当不写字段名时,values 后面 (null,“魏延”,“男”,34,“湖南长沙”)全写,
(2)自增条目或未知条目可用null代替。另在MySQL中values与value具有相同效果。
(3)当需要填入默认值时可以用default
2、删除(delete)—经过数据字典会有保留
(1)删除表中所有数据
delete from t_student; ### 该语句删除表中所有数据,但保留表格式,慎用!!!
eg:
mysql> select * from t_student;###查看t_student表中数据
+----+-----------+
| id | name |
+----+-----------+
| 1 | 刘勇杰 |
| 2 | 李珂瑶 |
| 3 | 胡星 |
| 4 | 马博宇 |
| 5 | 李奇 |
| 6 | 马海涛 |
+----+-----------+
6 rows in set (0.00 sec)
mysql> delete from t_student;###删除表中所有数据
Query OK, 6 rows affected (0.20 sec)
mysql> select * from t_student;###查看表中数据
Empty set (0.00 sec)
mysql> desc t_student;###查看删除后表的格式
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.26 sec)
(2)条件删除
delete from t_student where id=1; ###删除id=1的行(建议通过主键删除数据因为主键唯一防止误删数据)
mysql> select * from t_student;
+----+-----------+
| id | name |
+----+-----------+
| 7 | 李珂瑶 |
| 8 | 李奇 |
| 9 | 胡星 |
+----+-----------+
3 rows in set (0.37 sec)
mysql> delete from t_student where id = 7;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+----+--------+
| id | name |
+----+--------+
| 8 | 李奇 |
| 9 | 胡星 |
+----+--------+
2 rows in set (0.10 sec)
truncate删除
truncate 表名称 ###truncate删除数据(清空表中数据),非常具有风险的操作,不经过数据字典,不可恢复
3、更新(update)
update shuihuzhuan(表名) set byname=“武大郎”(修改条目) where id=2;
mysql> update shuihuzhuan set byname="武大郎" where id=2;
Query OK, 1 row affected (0.46 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from shuihuzhuan;
+----+-----------+------+-----------+--------------+
| id | name | age | byname | address |
+----+-----------+------+-----------+--------------+
| 1 | 潘金莲 | 23 | 大美妞 | 阳谷县 |
| 2 | 李珂瑶 | 18 | 西门庆 | 陕西渭南 |
| 3 | 李奇 | 18 | 武松 | 宁夏吴忠 |
| 4 | 宋江 | 32 | 及时雨 | 梁山 |
+----+-----------+------+-----------+--------------+
4 rows in set (0.16 sec)
mysql> select * from shuihuzhuan;
+----+-----------+------+-----------+--------------+
| id | name | age | byname | address |
+----+-----------+------+-----------+--------------+
| 1 | 潘金莲 | 23 | 大美妞 | 阳谷县 |
| 2 | 李珂瑶 | 18 | 武大郎 | 陕西渭南 |
| 3 | 李奇 | 18 | 武松 | 宁夏吴忠 |
| 4 | 宋江 | 32 | 及时雨 | 梁山 |
+----+-----------+------+-----------+--------------+
4 rows in set (0.00 sec)
update shuihuzhuan set name=“马博宇”, byname=“西门庆” where id=4;