MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
INSERT INTO - Statement to Insert Records to Tables
A tutorial example is provided on how to use INSERT INTO statements to insert records into tables in different ways.
An insert statement allows you to insert new rows of data into an existing table. It has a number of syntax formats:
1. To insert a single row of all columns with values resulting from the specified expressions:
INSERT INTO tbl_name VALUES (expression, expression, ...);
When executed, all expressions will be evaluated, and the resulting values will form the new row, which will be inserted into the specified table. Of course, the number of expressions must be equal to the number of columns in table.
2. To insert a single row with some columns having specified values, and others having default values:
INSERT INTO tbl_name (column, column, ...) VALUES (expression, expression, ...);
Notes:
3. To insert one or more rows of all columns with a select sub-statement:
INSERT INTO tbl_name select_statement;
When executed, the output rows of the select sub-statement will be inserted into the specified table. Of course, the number of select expressions in the select statement must be equal to the number of columns of the specified table.
4. To insert one or more rows with some column having values from the specified select sub-statement, and other columns having default values:
INSERT INTO tbl_name (column, column, ...) select_statement;
5. To insert multiple rows with multiple VALUES clauses:
INSERT INTO tbl_name (c_list) VALUES (e_list), (e_list), ..., (e_list);
Notes:
Here is an example SQL code, InsertRows.sql, showing you how to insert rows into an existing table:
-- InsertRows.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- DROP TABLE IF EXISTS User; CREATE TABLE User (Login VARCHAR(8), Password CHAR(8)); INSERT INTO User VALUES ('herong','8IS3KOXW'); INSERT INTO User (Login) VALUES ('mike'); INSERT INTO User SELECT Login, Password FROM User; INSERT INTO User (Password) SELECT CONCAT('__',Login) FROM User; SELECT 'User table:' AS '---'; SELECT * FROM User;
If you run the code, you will get:
--- User table: Login Password herong 8IS3KOXW mike NULL herong 8IS3KOXW mike NULL NULL __herong NULL __mike NULL __herong NULL __mike
The output looks alright to me.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
►Using DML to Insert, Update and Delete Records
►INSERT INTO - Statement to Insert Records to Tables
UPDATE - Statement to Update Records in Tables
DELETE FROM - Statement to Delete Records from Tables
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux