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

部分,使用MYSQL

插入,删除,更新数据

插入如下数据, 可以将其保存为文件,用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;

 

 

 

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

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

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;

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

 

 

实例：

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等的描述。

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