一、INSERT插入语句
语法：
INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
# 在表名后给出要插入的列名，其他没有指定的列等同与插入null值。在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应
INTERT INTO 表名 VALUES(列值1, 列值2)
# 没有给出要插入的列，那么表示插入所有列。值的个数必须是该表列的个数。值的顺序，必须与表创建时给出的列的顺序相同。
示例：
# 查看表结构 mysql> desc t1; +--------+---------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | NAME | char(16) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | | gender | enum('m','f') | NO | | m | | | bir | datetime | YES | | CURRENT_TIMESTAMP | | | telnum | varchar(12) | NO | UNI | NULL | | +--------+---------------+------+-----+-------------------+----------------+ # 插入指定的值 mysql> insert into t1(id, NAME, gender, telnum) values(1,'tfj','f','13666669999'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+------+------+--------+---------------------+-------------+ | 1 | tfj | NULL | f | 2019-09-18 10:07:06 | 13666669999 | +----+------+------+--------+---------------------+-------------+ 1 row in set (0.00 sec) # 插入所有的值 mysql> insert into t1 values(2,'ella',30,'f',19990902,'13245678987'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +----+------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+------+------+--------+---------------------+-------------+ | 1 | tfj | NULL | f | 2019-09-18 10:07:06 | 13666669999 | | 2 | ella | 30 | f | 1999-09-02 00:00:00 | 13245678987 | +----+------+------+--------+---------------------+-------------+ 2 rows in set (0.00 sec) # 一次性插入多条记录 mysql> insert into t1 values(3,'jolin',32,'f',19850303,'18789989232'),(4,'andy',8,'m',20121212,'13721230990'),(5,'leon',34,'m',19861225,'19900009999');
二、UPDATE更新数据
语法：
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]
# WHERE 条件(条件可选的)： # 条件必须是一个boolean类型的值或表达式：UPDATE t1 SET gender='男', age=age+1 WHERE id='1'; # 运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND
示例：
mysql> select * from t1; +----+-------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+-------+------+--------+---------------------+-------------+ | 1 | tfj | NULL | f | 2019-09-18 10:07:06 | 13666669999 | | 2 | ella | 30 | f | 1999-09-02 00:00:00 | 13245678987 | | 3 | jolin | 32 | f | 1985-03-03 00:00:00 | 18789989232 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | | 5 | leon | 34 | m | 1986-12-25 00:00:00 | 19900009999 | +----+-------+------+--------+---------------------+-------------+ 5 rows in set (0.00 sec) mysql> update t1 set age=18 where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +----+-------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+-------+------+--------+---------------------+-------------+ | 1 | tfj | 18 | f | 2019-09-18 10:07:06 | 13666669999 | | 2 | ella | 30 | f | 1999-09-02 00:00:00 | 13245678987 | | 3 | jolin | 32 | f | 1985-03-03 00:00:00 | 18789989232 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | | 5 | leon | 34 | m | 1986-12-25 00:00:00 | 19900009999 | +----+-------+------+--------+---------------------+-------------+ 5 rows in set (0.00 sec)
三、DELETE 删除数据
语法：
DELETE FROM 表名 [WHERE 条件];
# TRUNCATE TABLE 表名：TRUNCATE是DDL语句，它是先删除drop该表，再create该表。而且无法回滚！！！
示例：
mysql> select * from t1; +----+-------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+-------+------+--------+---------------------+-------------+ | 1 | tfj | 18 | f | 2019-09-18 10:07:06 | 13666669999 | | 2 | ella | 30 | f | 1999-09-02 00:00:00 | 13245678987 | | 3 | jolin | 32 | f | 1985-03-03 00:00:00 | 18789989232 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | | 5 | leon | 34 | m | 1986-12-25 00:00:00 | 19900009999 | +----+-------+------+--------+---------------------+-------------+ 5 rows in set (0.00 sec) mysql> delete from t1 where age>18; Query OK, 3 rows affected (0.01 sec) mysql> select * from t1; +----+------+------+--------+---------------------+-------------+ | id | NAME | age | gender | bir | telnum | +----+------+------+--------+---------------------+-------------+ | 1 | tfj | 18 | f | 2019-09-18 10:07:06 | 13666669999 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | +----+------+------+--------+---------------------+-------------+ 2 rows in set (0.00 sec)
一般不直接删除，而是使用update替换delete命令，实现伪删除。
# 添加一个状态列，1表示数据存在，0表示数据被删除。
mysql> ALTER TABLE t1 ADD state ENUM('1','0') DEFAULT '1'; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +----+------+------+--------+---------------------+-------------+-------+ | id | NAME | age | gender | bir | telnum | state | +----+------+------+--------+---------------------+-------------+-------+ | 1 | tfj | 18 | f | 2019-09-18 10:07:06 | 13666669999 | 1 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | 1 | +----+------+------+--------+---------------------+-------------+-------+ 2 rows in set (0.00 sec) mysql> UPDATE t1 SET state='0' WHERE id='1'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
# 实现伪删除（将1改为0） mysql> SELECT * FROM t1; +----+------+------+--------+---------------------+-------------+-------+ | id | NAME | age | gender | bir | telnum | state | +----+------+------+--------+---------------------+-------------+-------+ | 1 | tfj | 18 | f | 2019-09-18 10:07:06 | 13666669999 | 0 | | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | 1 | +----+------+------+--------+---------------------+-------------+-------+ 2 rows in set (0.00 sec)
# 让应用看不到state=0的数据 mysql> select * from t1 WHERE state='1'; +----+------+------+--------+---------------------+-------------+-------+ | id | NAME | age | gender | bir | telnum | state | +----+------+------+--------+---------------------+-------------+-------+ | 4 | andy | 8 | m | 2012-12-12 00:00:00 | 13721230990 | 1 | +----+------+------+--------+---------------------+-------------+-------+ 1 row in set (0.00 sec)
补充：update操作也是有风险的，一般会在数据库设计中，加入trigger一但有update操作，会触发将修改前和修改后的数据存放到自己定义的一张表中，但是会有性能耗损。