DML(数据的操作)
插入数据的操作
INSERT [ INTO ] tbl_name VALUES | VALUE ( 值. . . )
INSERT INTO user VALUES ( 1 , 'KING' , 'KING' , 'KING@QQ.COM' , 20 ) ;
INSERT [ INTO ] tbl_name( 字段名称1. . . ) VALUES | VALUE ( 值1. . . . )
INSERT user ( username, password) VALUES ( 'A' , 'AAA' ) ;
INSERT [ INTO ] tb_name[ ( 字段名称. . ) ] VLUES( 值. . ) ( 值. . )
INSERT user VALUES ( 6 , 'D' , 'DDD' , 'D@QQ.COM' , 35 ) ,
( 8 , 'E' , 'EEE' , 'E@QQ.COM' , 9 ) ,
( 18 , 'F' , 'FFF' , 'F@QQ.COM' , 32 ) ;
INSERT [ INTO ] tbl_name SET 字段名称= 值. . .
INSERT INTO user SET id= 98 , username= 'test' , password= 'this is a test' , email= '123@qq.com' ,
age= 48 ;
INSERT user SET username= 'maizi' , password= 'maizixueyuan' ;
INSERT [ INTO ] tbl_name[ ( 字段名称. . . ) ] SELECT 字段名称 FROM tbl_name [ WHERE 条件]
CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
username VARCHAR ( 20 ) NOT NULL UNIQUE
) ;
INSERT testUser SELECT id, username FROM user ;
字段数目不匹配
INSERT testUser SELECT * FROM user ;
TRUNCATE testUser;
INSERT testUser( username) SELECT username FROM user ;
更新数据
UPDATE tbl_name SET 字段名称= 值,… [ WHERE 条件] [ ORDER BY 字段名称] [ LIMIT 限制条数]
UPDATE user SET age= 5 ;
UPDATE user SET password= 'king123' , email= '123@qq.com' , age= 99 WHERE id= 1 ;
UPDATE user SET age= age- 5 WHERE id>= 3 ;
UPDATE user SET age= DEFAULT WHERE username= 'A' ;
删除数据
DELETE FROM tbl_ name [ WHERE 条件] [ ORDER BY 字段名称:] [ LIMIT 限制条数]
DELETE FROM testUser ;
DELETE FROM user WHERE id= 1 ;
TRUNCATE [ TABLE ] tbl name
TRUNCATE TABLE user ;
DELETE 与 TURNCATE的区别
truncate语句 删除表中的数据后,向表中添加记录时,自动增加字段的默认初始值重新从1开始,而使用delete语句,删除表中所有记录后,向表中添加记录时,自动增加字段的值,为删除时该字段的最大值加1,也就是在原来的基础上递增。 delete语句 每删除一条记录,都会在日志中记录,而使用truncate语句,不会在日志中记录删除的内容,因此,truncate语句的执行效率比delete语句高。