The TRUNCATE statement allows us to delete all the rows from a table. For example:
TRUNCATE TABLE employee;
This query would delete all the
We can use the
SQL statement to change rows already stored in the database. For example, imagine that one of our
update employee set job='DBA' where employeeID='6651';
This statement changes the value of the job column for employee number 6651.
The general form of the
statement from the MySQL manual is as
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 ]
The UPDATE statement is similar in many respects to the DELETE statement.
We can use an optional
clause to update particular rows or leave it off to update all rows. Again, you can fall into the trap of
update user set password='test';
This again highlights the
The second version of the
statement listed previously is a multi-table update. This works similarly to the multi-table deletes we
We have seen all the other clauses of the UPDATE statement before. The LOW_PRIORITY and IGNORE clauses work the same way as they do in INSERT . The ORDER BY and LIMIT clauses work the same way they do in DELETE .
Uploading Data with LOAD DATA INFILE
LOAD DATA INFILE
command allows you to bulk insert data from a text file into a single table without having to write
statements. For example, we could have loaded the data in the department table using this technique as
Listing 5.2 department_infile.txt
42 Finance 128 Research and Development NULL Human Resources NULL Marketing
This file is in the default LOAD DATA INFILE format, with each row listed on a separate line with tabs between column values. (This is configurable and we will see how in a minute.)
We can load this information into the department table with the following LOAD DATA INFILE statement:
load data local infile 'department_infile.txt' into table department;
This facility is particularly useful for converting data from another database format, spreadsheet, or CSV (comma-separated values) file.
LOAD DATA INFILE
statement requires the
privilege ”see Chapter 11, "Managing User Privileges," for further information,
The general form of the LOAD DATA INFILE statement is as follows:
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 ,...)]
The optional clauses are as listed here:
A common format to receive data in is CSV or comma-separated values. Many programs can read and write files of this type, but one notable example is Microsoft Excel. Listing 5.3 shows a small CSV file saved from Excel.
Listing 5.3 new_programmers.csv
Name,Job,DepartmentID Julia Lenin,Programmer,128 Douglas Smith,Programmer,128 Tim O'Leary,Programmer,128
We can load this data into the employee table with the following query:
load data infile 'e:\new_programmers.csv' into table employee fields terminated by ',' lines terminated by '\n' ignore 2 lines (name, job, departmentID);
You can see that we have used more options to load this data than we did when the data was in the default format. A few points are worth noting: