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

Your Ad Here

Your Ad Here

§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可选,

 

insert into department

set name='Asset Management';

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

更多的选项参见教材.

 

§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';

 

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]

§5.5  用LOAD DATA INFILE上载数据

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,...)]

 

 

详细的参见教材

 

MYSQL 教程:§6, 查询：【上一篇】
MYSQL 教程:§4, 创建数据库,表,素引：【下一篇】