Modifying data

INSERT

• insert one row

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);

If you specify the value of the corresponding column for all columns in the table, you can ignore the column list in the INSERT statement as follows:

INSERT INTO table
VALUES (value1,value2,...);

• insert multiple rows

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

• INSERT with SELECT clause

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

e.g. Let’s copy the tasks table to the task_1 table.
First, create a new table named tasks_1 by copying the structure of the tasks table as follows:

CREATE TABLE tasks_1 LIKE tasks;

Second, insert data from the tasks table into the tasks_1 table using the following INSERT statement:

INSERT INTO tasks_1
SELECT * FROM tasks;

• INSERT IGNORE

When you use the INSERT statement to add some rows to a table and if an error occurs during the processing, the INSERT statement is aborted and an error message is returned. As the result, no rows are inserted into the table.

However, if you use the INSERT INGORE statement, the rows that cause the errors are ignored and the remaining rows are inserted into the table.

The syntax of the INSERT INGORE statement is as follows:

INSERT IGNORE INTO table(column_list)
VALUES( value_list),
      ( value_list),
      ...

UPDATE

• The following illustrates the syntax of the MySQL UPDATE statement:

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;

!NOTE : If you omit the WHERE clause, the UPDATE statement will update all rows in the table.

• MySQL supports two modifiers in the UPDATE statement.

The LOW_PRIORITY modifier instructs the UPDATE statement to delay the update until there is no connection reading data from the table.

The IGNORE modifier enables the UPDATE statement to continue updating rows even if errors occurred. The rows that cause errors such as duplicate-key conflicts are not updated.

• UPDATE from SELECT statement
  You can supply the values for the SET clause from a SELECT statement that queries data from other tables.

• UPDATE with JOIN
  The syntax of the MySQL UPDATE JOIN is as follows:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

This UPDATE statement works the same as UPDATE JOIN with implicit INNER JOIN clause.

UPDATE T1, T2
SET T1.c2 = T2.c2,
      T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

It means you can rewrite the above statement as follows:

UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition

DELETE

The following illustrates the syntax of the DELETE statement:

DELETE FROM table_name
WHERE condition;

Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Besides deleting data from a table, the DELETE statement returns the number of rows deleted.

• with LIMIT clause

DELETE FROM table_name
ORDER BY c1, c2, ...
LIMIT row_count;

• ON DELETE CASCADE
  Using MySQL ON DELETE CASCADE referential action for a foreign key to delete data from multiple related tables.

Tips to find tables affected by MySQL ON DELETE CASCADE action

USE information_schema;
 
SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'

with JOIN

• with INNER JOIN

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omit T2 table, the DELETE statement will delete only rows in T1 table.

• with LEFT JOIN
  The following syntax illustrates how to use DELETE statement with LEFT JOIN clause to delete rows from T1 table that does not have corresponding rows in the T2 table:

DELETE T1 
FROM T1
        LEFT JOIN
    T2 ON T1.key = T2.key 
WHERE
    T2.key IS NULL;

REPLACE

• The MySQL REPLACE statement works as follows:

If the new row already does not exist, the MySQL REPLACE statement inserts a new row.

If the new row already exist, the REPLACE statement deletes the old row first and then inserts a new row. In some cases, the REPLACE statement updates the existing row only.

To determine whether the new row already exists in the table, MySQL uses PRIMARY KEY or UNIQUE KEY index. If the table does not have one of these indexes, the REPLACE statement is equivalent to the INSERT statement.

• REPLACE and INSERT

REPLACE INTO table_name(column_list)
VALUES(value_list);

• REPLACE and UPDATE

REPLACE INTO table
SET column1 = value1,
    column2 = value2;

Notice that there is no WHERE clause in the REPLACE statement.

• REPLACE INTO and SELECT

REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;

PREPARE

Using the prepared statement to execute a query.

prepare

PREPARE stmt1 FROM 'SELECT productCode, productName
                    FROM products
                    WHERE productCode = ?';
 
SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;
 
DEALLOCATE PREPARE stmt1;

Managing MySQL databases and tables

Manage Database

• Creating Database

CREATE DATABASE [IF NOT EXISTS] database_name;

The IF NOT EXISTS is an optional clause of the statement. The IF NOT EXISTS clause prevents you from an error of creating a new database that already exists in the database server. You cannot have 2 databases with the same name in a MySQL database server.

• Displaying Databases

SHOW DATABASES;

• Selecting a database to work with

USE database_name;

• Removing Databases

DROP DATABASE [IF EXISTS] database_name;

The IF EXISTS is an optional part of the statement to prevent you from removing a database that does not exist in the database server.

Manage Table

• CREATE TABLE
  The following illustrates the syntax of the CREATE TABLE statement in the simple form:

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
        ) engine=table_type

If you don’t declare the storage engine explicitly, MySQL will use InnoDB by default.

To define a column for the table in the CREATE TABLE statement, you use the following syntax:

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
[AUTO_INCREMENT]

If you want to set particular columns of the table as the primary key, you use the following syntax:

PRIMARY KEY (col1,col2,...)

AUTO_INCREMENT

In MySQL, a sequence is a list of integers generated in the ascending order i.e., 1,2,3…

To create a sequence in MySQL automatically, you set the AUTO_INCREMENT attribute to a column, which typically is a primary key column.

The following rules are applied when you use the AUTO_INCREMENT attribute:

• Each table has only one AUTO_INCREMENT column whose data type is typically the integer.
• The AUTO_INCREMENT column must be indexed, which means it can be either PRIMARY KEY or UNIQUE index.
• The AUTO_INCREMENT column must have a NOT NULL constraint. When you set the AUTO_INCREMENT attribute to a column, MySQL automatically add the NOT NULL constraint to the column implicitly.

ALTER TABLE
You use the ALTER TABLE statement to change the structure of existing tables.

ALTER TABLE table_name action1[,action2,…]

---- action:
CHANGE COLUMN
ADD COLUMN
DROP COLUMN
RENAME TO

RENAME TABLE

RENAME TABLE old_table_name TO new_table_name;

The old table ( old_table_name) must exist, and the new table ( new_table_name) must not. If the new table new_table_name does exist, the statement will fail.

• Renaming multiple tables

RENAME TABLE old_table_name_1 TO new_table_name_2,
             old_table_name_2 TO new_table_name_2,
             ...;

!NOTE: Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table.

DROP COLUMN

ALTER TABLE table
DROP COLUMN column_1,
DROP COLUMN column_2,
…;

• drop a column which is a foreign key

You must remove the foreign key constraint before dropping the column.

ADD COLUMN

ALTER TABLE table
ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
...;

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ...
[RESTRICT | CASCADE]

The TEMPORARY flag allows you to remove temporary tables only. It is very convenient to ensure that you do not accidentally remove non-temporary tables.

The RESTRICT and CASCADE flags are reserved for the future versions of MySQL.

!NOTE: The DROP TABLE statement only removes table and its data. However, it does not remove specific user privileges associated with the table. Therefore if a table with the same name is re-created after that, the existing privileges will apply to the new table, which may pose a security risk.

• DROP TABLE with LIKE

Unfortunately, MySQL does not provide the DROP TABLE LIKE statement that can remove tables based on pattern matching like the following:

DROP TABLE LIKE '%pattern%'

Temporary Table

In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session.

A MySQL temporary table has the following specialized features:

• MySQL removes the temporary table automatically when the session ends or the connection is terminated. Of course, you can use the DROP TABLE statement to remove a temporary table explicitly when you are no longer use it.

• A temporary table can have the same name as a normal table in a database. For example, if you create a temporary table named employees in the sample database, the existing employees table becomes inaccessible. Every query you issue against the employees table is now referring to the temporary employees table. When you drop the employees temporary table, the permanent employees table is available and accessible again.

Creating a MySQL temporary table

CREATE TEMPORARY TABLE t

Removing a MySQL temporary table

DROP TEMPORARY TABLE table_name;

TRUNCATE TABLE

The MySQL TRUNCATE TABLE statement allows you to delete all data in a table.

TRUNCATE TABLE table_name;

Reference

• Basic MySQL Tutorial