创建info表
|
1 2 3 4 5 6 7 |
create table info( ID int not null auto_increment primary key, NAME CHAR(8) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(20) NOT NULL, SALARY decimal(10,2) not null ) ENGINE=innodb; |
INSERT（插入数据）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 第一种,在info表里插入数据的方法，为表指定字段插入数据; mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('ZHANG',32,'Beijing',2000.00); # 第二种,在info表里插入数据的方法，不指定字段插入数据，前提VALUES的值必须要跟表的字段一一对应; mysql> INSERT INTO info VALUES(2,'LIN',31,'Beijing',3000.00); # 第三种,在info表里插入数据的方法，同时插入多条数据; mysql> INSERT INTO info VALUES(3,'XIN',33,'Hangzhou',2000.00), (4,'XIN',33,'Beijing',2000.00); # 第四种,在info表里插入数据的方法，字段跟值一一对应插入表中; mysql> INSERT INTO info set NAME='LI',AGE=25,ADDRESS='Shanghai',SALARY=1500.00; # 第五种,利用查询数据插入表，将查询结果插入到表中（需要注意，为了保证数据一致性，对查询表默认加了读锁）; mysql> INSERT INTO db_name.table_name SELECT * FROM table_name WHERE …; |
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。
例如，info表的列ID为主键，如下操作：
|
1 2 |
mysql> INSERT INTO info(ID,NAME,AGE,ADDRESS,SALARY) VALUES(100,'ZHANG',32,'Beijing',2000.00); mysql> INSERT INTO info(ID,NAME,AGE,ADDRESS,SALARY) VALUES(100,'ZHANG',32,'Beijing',2000.00) ON DUPLICATE KEY UPDATE ID=ID+1; |
我先插入了一行，ID为100，然后使用ON DUPLICATE KEY UPDATE语法，此时INSERT语句判断有ID为100的记录，所以就会更新此语句。如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2。
这个语法还可以这样用：INSERT多行记录
|
1 2 |
INSERT INTO info(ID,NAME,AGE,ADDRESS,SALARY) VALUES(101,'ZHANG',32,'Beijing',2000.00), (102,'ZHANG',32,'Beeijing',2000.00) ON DUPLICATE KEY UPDATE ID=ID+1; |
执行后，ID的值会变为103（只会插入一条记录），因为第二条与第一条重复，所以ID在原值上+1。
如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行呢？其实使用VALUES()函数一切问题都解决了。
|
1 2 |
INSERT INTO info(ID,NAME,AGE,ADDRESS,SALARY) VALUES(103,'test',103,'test',2000.00), (200,'ZHANG',200,'Beeijing',2000.00) ON DUPLICATE KEY UPDATE AGE=values(AGE); |
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！
REPLACE（替换数据）
REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。
注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。
|
1 2 3 4 5 |
# 插入一条新纪录,有主键; mysql> INSERT INTO info VALUES(6,'WANG',25,'Shanghai',2000.00); # 把新插入的记录替换; mysql> REPLACE INTO info VALUES(6,'WANG',25,'HUNAN',2000.00); |
为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。
REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。
受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。
插入数据避免使用replace语句，可能会造成唯一键数据重复，增加死锁，且replace锁复杂。详情：唯一键约束失效
SELECT（查询数据）
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM blog.info; +----+-------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 2 | LIN | 31 | Beijing | 3000.00 | | 3 | XIN | 33 | Hangzhou | 2000.00 | | 4 | XIN | 33 | Beijing | 2000.00 | | 5 | LI | 25 | Shanghai | 1500.00 | +----+-------+-----+----------+---------+ 5 rows in set (0.00 sec) |
PS:后面详细介绍SQL查询语句的使用。
UPDATE（更新数据）
|
1 2 3 4 5 6 7 8 |
# 更新info表中AGE字段，把AGE字段设置为25，条件是NAME='ZHANG'的记录; mysql> UPDATE info set AGE=25 WHERE NAME='ZHANG'; # 更新info表中SALARY字段，把SALARY字段的值全部加100; mysql> UPDATE info set AGE=25 WHERE NAME='ZHANG'; # 利用replace函数更新单个字段内的特定内容; mysql> UPDATE info set NAME=replace(NAME,'old','new'); |
DELETE（删除数据）
|
1 2 3 4 5 |
# 删除info表中NAME='ZHANG'的记录; mysql> DELETE FROM blog.info WHERE NAME='ZHANG'; # 删除info表中所有记录，但AUTO_INCREMENT计数器不会清空; mysql> DELETE FROM blog.info; |
|
1 2 |
mysql> SELECT * FROM blog.info; Empty set (0.00 sec) |
查询info表，已经没有数据了，但是AUTO_INCREMENT计数器并不会重置，如下代码：
|
1 2 3 4 5 6 7 8 |
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',25,'Shanghai',2000.00); mysql> SELECT * FROM blog.info; +----+------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+----------+---------+ | 7 | WANG | 25 | Shanghai | 2000.00 | +----+------+-----+----------+---------+ 1 row in set (0.00 sec) |
TRUNCATE（清除表）
|
1 2 |
# 清除表中的所有内容，并且重置AUTO_INCREMENT计数器为0; mysql> TRUNCATE blog.info; |
|
1 2 3 4 5 6 7 8 9 10 |
mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',25,'Shanghai',2000.00); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM blog.info; +----+------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+----------+---------+ | 1 | WANG | 25 | Shanghai | 2000.00 | +----+------+-----+----------+---------+ 1 row in set (0.00 sec) |
可以看到ID字段的值已经变为1了。
