Guides:SQL/How do I/Add and Remove Data in a Table

From CoderGuide

Jump to: navigation, search

Return to the SQL How Do I Index

Contents


We will assume you're using the table:

CREATE TABLE my_table (
  atextfield char(50),
  my_record_ID char(50) NOT NULL,
  PRIMARY KEY (my_record_ID)
 );

Adding Data

INSERT method

The short hand method requires you know the order of the data fields in the table:

INSERT INTO my_table VALUES ("This goes into atextfield", "this goes into my_record_ID" );
INSERT INTO my_table VALUES ("another record", "id1"),  ("yet another one","id2");

We can also use this alternate method for VALUES if we want to specify the column order ourselves (or some colums). First, specify the column list BEFORE the VALUES clause, and then use VALUES to assign values to the columns in the order specified.

INSERT INTO my_table ( my_record_ID, atextfield) VALUES( 'still more', "id3");

Note: listing multiple records on one insert line only works in MySQL version 3.22.5 and later-- so, if you have a very old installation of MySQL, which you shouldn't... You can see your current version with the command SELECT VERSION();.

You can also use named fields for when you don't know, or don't want to care about, the ordering of the columns (this works in MySQL 3.23.9 and later). Using our my_table table:

INSERT INTO my_table SET atextfield="some text value",  my_record_ID="and yet another value";

From a file

You can also use data from a file to import into your database. The default file format is a tab separated file with no table headers. The column order must be the same as is in the existing table.

LOAD DATA LOCAL INFILE "filename.txt" INTO TABLE my_table;

If you have a .sql file already, and wish to import it:

SOURCE 'mysqlfile.sql';

Or you can just use the MySQL client (if you're using MySQL, of course):

# mysql -u your_username -p database_name < mysqlfile.sql

To dump an SQL file in MySQL:

# mysqldump -u your_username -p database_name my_table >mysqlfile.sql

You can leave out the table name if you wish to export the entire database.

Removing Data

Be VERY careful when using these next commands, as you can wipe out your entire table!

To delete a record in a table, you need to be specific:

SELECT * FROM my_table;
DELETE FROM my_table WHERE my_record_id="id2";
SELECT * FROM my_table;

Assuming you've been following along, you'll get:

mysql> select * from my_table;
+---------------------------+-----------------------------+
| atextfield                | my_record_ID                |
+---------------------------+-----------------------------+
| This goes into atextfield | this goes into my_record_ID | 
| some text value           | and yet another value       | 
| another record            | id1                         | 
| yet another one           | id2                         | 
| still more                | id3                         | 
+---------------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM my_table where my_record_id="id2";
Query OK, 1 row affected (0.02 sec)

mysql> select * from my_table;
+---------------------------+-----------------------------+
| atextfield                | my_record_ID                |
+---------------------------+-----------------------------+
| This goes into atextfield | this goes into my_record_ID | 
| some text value           | and yet another value       | 
| another record            | id1                         | 
| still more                | id3                         | 
+---------------------------+-----------------------------+
4 rows in set (0.00 sec)

The following command will wipe out your entire table:

DELETE FROM my_table; -- WARNING: THIS WILL WIPE OUT YOUR ENTIRE TABLE!

Without a scope to limit the command, it'll wipe out everything.

Note: You can launch mysql with the --safe-updates or --i-am-a-dummy parameters so that it won't allow you to use delete or update requests without a where clause. This basically would keep you from doing the above by accident.

If you want to be extra specific, we can add more qualifiers:

DELETE FROM my_table WHERE my_record_id="id2" AND atextfield='yet another one';

Changing existing data with UPDATE

The UPDATE clause is used to update/change records that already exist in a table. You can't use INSERT as that'll just add/insert new records into the table (which is probably not what you want). The UPDATE clause takes the following form:

UPDATE [TABLE name] SET [DATA TO UPDATE] WHERE [conditionals];

Here's an example:

UPDATE my_table SET atextfield="still more text", my_record_ID="id3.1" 
   WHERE my_record_ID="id3";

Viewing Data

You can view the contents and other information about a table with these commands:

DESCRIBE my_table;
DESCRIBE my_table\G
SELECT * FROM my_table;
SELECT * FROM my_table\G

If you're using Microsoft's SQL server, see Guides:SQL/How_do_I/Get_Database_and_Table_Information_in_a_Microsoft_SQL_Server.

Using \G (not \g) is useful if all the data won't display neatly on one screen. Of course, you can use the most pager for Linux/Unix instead. To have mysql use a pager, launch it with the with the --pager=your-pager argument. most is not installed by default on most Unix/Linux systems. mysql uses the pager environment variable if you issue the command pager; or "\P;" to turn on the pager (you can also use pager your-pager-name; to use any pager you want). To turn if off the use of the pager, use nopager; or \n;.

Personal tools