Insert, delete and update data in tables.

The syntax

INSERT INTO table_name [(column, ...)]
VALUES (value, ...)[, (...), ...];
        

Column list is optional!

If not present, you should list in VALUES values for all columns, following the order in table definition.

You can insert more than 1 row at a time, by using a list of values arrays.

Examples

Without specifying columns

INSERT INTO artist
VALUES (0,'Robert', 'Smith');
        
Query OK, 1 row affected
        

But:

INSERT INTO artist
VALUES ('Madonna');
        
(1136, u"Column count doesn't match value count at row 1")
        

With explicit column specification

INSERT INTO artist(lname)
VALUES ('Piazzolla');
        
Query OK, 1 row affected
        

Examples

Insert more than 1 row at a time

INSERT INTO artist( lname, fname )
VALUES ( 'Schnittke', 'Alfred' ),
       ( 'Pärt', 'Arvo' ),
       ( 'Shostakovich', 'Dmitri' );
        
Query OK, 3 rows affected
        

The Syntax

DELETE FROM table_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
        

The conditions in the optional WHERE clause identify which rows to delete. With no WHERE clause, all rows are deleted.

The LIMIT clause places a limit on the number of rows that can be deleted.

If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

Examples

delete from artist WHERE
    artist_id BETWEEN 9 AND 13;
        
Query OK, 5 rows affected
        

The Syntax

UPDATE table_name
SET column1=value, column2=value2,...
[WHERE where_clause]
[ORDER BY ...]
[LIMIT row_count]
        

The WHERE clause, if given, specifies the conditions that identify which rows to update

With no WHERE clause, all rows are updated!

If the ORDER BY clause is specified, the rows are updated in the order that is specified.

The LIMIT clause places a limit on the number of rows that can be updated.

Examples

UPDATE artist
SET fname='Test', lname='Test'
WHERE artist_id >= 14;
    
Query OK, 2 rows affected
    

Exercises

1. CONNECT TO MySQL as ROOT
2. CRETE DB: books_db
3. CREATE books_db_admin
4. GRANT admin privileges to books_db_admin on books_db
5. CONNECT to books_db as books_db_admin

6. Create next tables, using the appropriate data types for columns:
   table author:

   Columns:
   --------------------------------
   id (primary key, auto_increment)
   fname,
   lname,
   birth_year,
   death_year;
               

   table book:

   Columns:
   --------------------------------
   id (primary key, auto_increment)
   author_id,
   book_name,
   pub_year;
               

If you need to store years less than 1900, you have to use some integer type for that - SMALLINT(4) should be enough for most cases.

You can examine the sample DataBase books_db:

Download the books_db_schema.sql

Download the books_db_sample_data.sql

Execute the SQL statements by using one of the approaches described in MySQL_Users_and_DB_admin.html (load_external_sql)

Use the 'INSERT', 'UPDATE' and 'DELETE' statements to modify the data in the example (or in your) data base.