MYSQL 教程:§5, 插入,删除,更新数据
时间：2008-02-14 来源：oychw
第3部分,使用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,...)]
相关阅读 更多 +
