开源中文网

您的位置: 首页 > MySQL > 正文

MYSQL 教程: 插入,删除,更新数据

来源:  作者:

§5, 插入,删除,更新数据 
§5.1 INSERT 

插入如下数据, 可以将其保存为文件,用mysql <文件名来操作 

use employee; 



delete from department; 

insert into department values 

(42, 'Finance'), 

(128, 'Research and Development'), 

(NULL, 'Human Resources'), 

(NULL, 'Marketing'); 



delete from employee; 

insert into employee values 

(7513,'Nora Edwards','Programmer',128), 

(9842, 'Ben Smith', 'DBA', 42), 

(6651, 'Ajay Patel', 'Programmer', 128), 

(9006, 'Candy Burnett', 'Systems Administrator', 128); 



delete from employeeSkills; 

insert into employeeSkills values 

(7513, 'C'), 

(7513, 'Perl'), 

(7513, 'Java'), 

(9842, 'DB2'), 

(6651, 'VB'), 

(6651, 'Java'), 

(9006, 'NT'), 

(9006, 'Linux'); 



delete from client; 

insert into client values 

(NULL, 'Telco Inc', '1 Collins St Melbourne', 'Fred Smith', '95551234'), 

(NULL, 'The Bank', '100 Bourke St Melbourne', 'Jan Tristan', '95559876'); 



delete from assignment; 

insert into assignment values 

(1, 7513, '2003-01-20', 8.5); 

插入后情况如下: 

mysql> show table status; 

+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | 

+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 

| assignment | MyISAM | 10 | Fixed | 1 | 16 | 16 | 4503599627370495 | 2048 | 0 | NULL | 2006-09-14 14:28:02 | 2006-09-30 09:44:36 | NULL | latin1_swedish_ci | NULL | | | 

| client | MyISAM | 10 | Dynamic | 2 | 64 | 128 | 281474976710655 | 2048 | 0 | 3 | 2006-09-14 14:27:02 | 2006-09-30 09:44:36 | NULL | latin1_swedish_ci | NULL | | | 

| department | MyISAM | 10 | Dynamic | 4 | 26 | 104 | 281474976710655 | 2048 | 0 | 133 | 2006-09-14 14:20:02 | 2006-09-30 09:44:36 | NULL | latin1_swedish_ci | NULL | | | 

| employee | MyISAM | 10 | Dynamic | 4 | 39 | 156 | 281474976710655 | 3072 | 0 | 9843 | 2006-09-30 09:10:31 | 2006-09-30 09:44:36 | 2006-09-30 09:10:31 | latin1_swedish_ci | NULL | | | 

| employeeSkills | MyISAM | 10 | Dynamic | 8 | 20 | 160 | 281474976710655 | 2048 | 0 | NULL | 2006-09-14 14:22:37 | 2006-09-30 09:44:36 | NULL | latin1_swedish_ci | NULL | | | 

+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 

5 rows in set (0.00 sec) 

估计其中的动态是因为有VARCHAR 



格式如下: 

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

VALUES ((expression | DEFAULT),...),(...),... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

SELECT ... 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name 

SET col_name=(expression | DEFAULT), ... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 

INTO可选, 

关于引号的使用:string or date需要使用引号,数值类型不需使用。单引号可以通过转义实现:'O\'Leary'. 

auto_increment可以自动分配ID。自动分配的结果如下: 

mysql> select * from department; 

+--------------+--------------------------+ 

| departmentID | name | 

+--------------+--------------------------+ 

| 42 | Finance | 

| 128 | Research and Development | 

| 129 | Human Resources | 

| 130 | Marketing | 

+--------------+--------------------------+ 

4 rows in set (0.00 sec) 



语法如下: 



INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

VALUES ((expression | DEFAULT),...),(...),... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

SELECT ... 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name 

SET col_name=(expression | DEFAULT), ... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 

第一种格式是我们刚才所使用的。 

第2种格式用于从表中获取数据。 





insert into department 

以下例子没有指定外键也照样可以插入。而且外键还是要求为非空的。自动取了默认值0。这个默认值好像也是系统给的,我们在定义表的时候并没有设置默认值。而且只有一个值起作用。 

mysql> insert into employee set name = 'Meil'; 

Query OK, 1 row affected, 1 warning (0.05 sec) 



mysql> insert into employee set name = 'Glen' and departmentID = 42; 

Query OK, 1 row affected, 2 warnings (0.04 sec) 



mysql> select * from employee; 

+------------+---------------+-----------------------+--------------+ 

| employeeID | name | job | departmentID | 

+------------+---------------+-----------------------+--------------+ 

| 6651 | Ajay Patel | Programmer | 128 | 

| 7513 | Nora Edwards | Programmer | 128 | 

| 9006 | Candy Burnett | Systems Administrator | 128 | 

| 9842 | Ben Smith | DBA | 42 | 

| 9843 | Meil | NULL | 0 | 

| 9844 | 0 | NULL | 0 | 

+------------+---------------+-----------------------+--------------+ 

6 rows in set (0.00 sec) 



mysql> 

mysql> 

set name='Asset Management'; 

只能插入一行,但是可以不填其他的值. 

更多的选项参见教材. 



插入有重复的处理方法: 

· create table warning 

· ( 

· employeeID int primary key not null references employee(employeeID), 

· count int default 1 

· ) type =InnoDB; 

· 

· insert into warning (employeeID) 

· values (6651) 

· on duplicate key update count=count+1; 






§5.2 REPLACE 

更新和插入类似,就不详细描述了。 

REPLACE [LOW_PRIORITY | DELAYED] 

[INTO] tbl_name [(col_name,...)] 

VALUES (expression,...),(...),... 



or REPLACE [LOW_PRIORITY | DELAYED] 

[INTO] tbl_name [(col_name,...)] 

SELECT ... 



or REPLACE [LOW_PRIORITY | DELAYED] 

[INTO] tbl_name 

SET col_name=expression, col_name=expression,... 



§5.3 DELETE 

delete from department; 

delete from department where name='Asset Management'; 

为了避免误删,可以用-–safe-updates or –-i-am-a-dummy启动, 这样不允许不带where子句的删除. 

语法如下: 

DELETE [LOW_PRIORITY] [QUICK] FROM table_name 

[WHERE where_definition] 

[ORDER BY ...] 

[LIMIT rows] 



or 



DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...] 

FROM table-references 

[WHERE where_definition] 



or 



DELETE [LOW_PRIORITY] [QUICK] 

FROM table_name[.*] [, table_name[.*] ...] 

USING table-references 

[WHERE where_definition] 



第一种格式,刚才的例子就是 

第二种格式: 



delete employee, employeeSkills 

from employee, employeeSkills, department 

where employee.employeeID = employeeSkills.employeeID 

and employee.departmentID = department.departmentID 

and department.name='Finance'; 



执行结果如下: 



mysql> delete employee ,employeeSkills from employee , employeeSkills, department where employee.employeeID= employeeSkills.employeeID and employee.departmentID = department.departmentID and department.name= 'Finance'; 

Query OK, 2 rows affected (0.05 sec) 



本例删除了财务部工作的所有员工信息和相关的技能信息。Department中的信息并不删除。Delete后面接要删除的表,from后面是要查询的表。 



第三种格式和第二种很类似。 



delete from employee, employeeSkills 

using employee, employeeSkills, department 

where employee.employeeID = employeeSkills.employeeID 

and employee.departmentID = department.departmentID 

and department.name='Finance'; 



其他参数请参考教材 



TRUNCATE TABLE employee; 

是通过删除表然后重建实现,更快,但是对事物来说不是很安全. 




§5.4 UPDATE 

实例: 

update employee 

set job='DBA' 

where employeeID='6651'; 





语法: 



UPDATE [LOW_PRIORITY] [IGNORE] tbl_name 

SET col_name1=expr1 [, col_name2=expr2 ...] 

[WHERE where_definition] 

[ORDER BY ...] 

[LIMIT rows] 



or 



UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] 

SET col_name1=expr1 [, col_name2=expr2 ...] 

[WHERE where_definition] 



请参考insert,DELETE等的描述。 

§5.5 用LOAD DATA INFILE上载数据 

LOAD DATA INFILE可以不实用insert而批量录入数据。一般用在another database format, spreadsheet, or CSV (comma-separated values) file。比如文件department_infile.txt。 

42 Finance 

128 Research and Development 

NULL Human Resources 

NULL Marketing 



load data local infile 'department_infile.txt' 

into table department; 



语法 



LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fileName.txt' 

[REPLACE | IGNORE] 

INTO TABLE tbl_name 

[FIELDS 

[TERMINATED BY '\t'] 

[[OPTIONALLY] ENCLOSED BY ''] 

[ESCAPED BY '\\' ] 



[LINES TERMINATED BY '\n'] 

[IGNORE number LINES] 

[(col_name,...)] 





详细的参见教材 

LOAD DATA INFILE需要FILE privilege 



Listing 5.3 new_programmers.csv 

Name,Job,DepartmentID 



Julia Lenin,Programmer,128 

Douglas Smith,Programmer,128 

Tim O'Leary,Programmer,128 



加载方法: 

load data infile 'e:\\new_programmers.csv' 

into table employee 

fields terminated by ',' 

lines terminated by '\n' 

ignore 2 lines 

(name, job, departmentID); 

实例: 

在LSDB中插入用户: 

用户绑定: 











insert into terminal set terminal_id ='000000000000000000010314a00371f3',provider_code='1',terminal_type='66324',status='0'; 



insert into auth_relationship_binding values('000000000000000000010314a00371f3',0,'UTDRM_R260_CLI_V_00000000000000000000000000000000000001182',1,0,0);



update terminal set status='1' where terminal_id ='000000000000000000010314a00371f3'; 



update drm_client set status ='1' where drm_client_id ='UTDRM_R260_CLI_V_00000000000000000000000000000000000001182'; 
§5.6 小结 

Inserting Data 

String values should be in quotes. Single quotes or backslashes within a string need to be escaped with a backslash. 



Add data to tables with the INSERT statement: 







INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

VALUES ((expression | DEFAULT),...),(...),... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name [(col_name,...)] 

SELECT ... 



or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] 

[INTO] tbl_name 

SET col_name=(expression | DEFAULT), ... 

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 



The REPLACE statement is just like INSERT, but it overwrites rows where a key clash occurs. INSERT fails or triggers the ON DUPLICATE KEY UPDATE clause when a key clash occurs. 



Deleting Data 

Avoid disasters with --i-am-a-dummy. 



Delete data from tables with the DELETE statement: 







DELETE [LOW_PRIORITY] [QUICK] FROM table_name 

[WHERE where_definition] 

[ORDER BY ...] 

[LIMIT rows] 

or DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...] 

FROM table-references 

[WHERE where_definition] 

or DELETE [LOW_PRIORITY] [QUICK] 

FROM table_name[.*] [, table_name[.*] ...] 

USING table-references 

[WHERE where_definition] 



The TRUNCATE TABLE statement deletes all rows from a table. 



Updating Data 

Update data in tables with the UPDATE TABLE statement: 







UPDATE [LOW_PRIORITY] [IGNORE] tbl_name 

SET col_name1=expr1 [, col_name2=expr2 ...] 

[WHERE where_definition] 

[ORDER BY ...] 

[LIMIT rows] 

or UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] 

SET col_name1=expr1 [, col_name2=expr2 ...] 

[WHERE where_definition] 



LOAD DATA INFILE 

Use LOAD DATA INFILE to load the contents of a text file into a table: 







LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' 

[REPLACE | IGNORE] 

INTO TABLE tbl_name 

[FIELDS 

[TERMINATED BY '\t'] 

[[OPTIONALLY] ENCLOSED BY ''] 

[ESCAPED BY '\\' ] 



[LINES TERMINATED BY '\n'] 

[IGNORE number LINES] 

[(col_name,...)] 


Tags:MYSQL 教程 插入
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明