|
Custom Search
| |
|
Basic insert statement This tutorial demonstrates how insert statement works in MySQL with syntax and examples explained in details. MySQL insert statement allows you to insert new rows into an existing table, based on explicitly specified values in the statement, or values in another table or tables. The simplest INSERT statement takes the following syntax format, where explicitly specified values are inserted.
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...); To learn how to insert multiple new rows into an existing table, based on values in another table or tables, read Create MySQL table by using another table. When creating the INSERT statement, consider the following guidelines.
Practice #1: Basic insert statement The following insert statement adds a value to products table. Both column names and values are explicitly provided. Note that the ProductID column is not listed in the columns list and no corresponding value is provided because ProductID is an AUTO_INCREMENT primary key. For this type of column, the next available sequence number will be generated automatically when the record is inserted. INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, To see this just inserted product, run the following query. LAST_INSERT_ID() function returns the last auto generated integer number which was assigned to ProductID column. SELECT * FROM products
Query result set - 1 row returned: Practice #2a: Insert where AUTO_INCREMENT is hardcoded as primary key. This insert statement is very similar to the one above. The only difference is that ProductID is listed and a value is provide. Before insert, the maximum ProductID in the table is 78, so we assign 79 to the record as it leaves no gap and is considered a good practice to follow. INSERT INTO products(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, Below is the records in products table. ProductID 78 was auto generated in Practice #1, but 79 was hardcoded and inserted by our query above.
Practice #2b: use keyword NULL for the AUTO_INCREMENT column. The difference here to the query in Practice #2a above is the use of keyword NULL. This way, ProductID will be assigned to the next available sequence number. INSERT INTO products(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, Products table looks like below after the insert.
Practice #3: Insert where column names are omitted. This insert query omits the column names. Values for every column must be provided in the VALUES list. However, the best practice is to include the column names because the query wouldn't break if table structure is changed laster, e.g. number of columns or column order is changed. You need to make sure the values are in the same order as the columns. INSERT INTO products Below is the records in products table.
To find out the order of the columns in the table, use DESCRIBE keyword. The following query finds out the table structure - the columns and order of them are presented clearly. DESCRIBE products Products table looks like below after the insert.
Practice #4: Insert where default value is defined. This insert statement does not list the Discontinued column and it will use the default value 'n' for the inserted row. INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, Products table looks like below after the insert.
For a given table, you can use the SHOW CREATE TABLE statement to see which columns have an explicit DEFAULT clause. SHOW CREATE TABLE products Practice #5: Insert a default value by using keyword DEFAULT In the insert statement below, it explicitly specifies by DEFAULT(col_name) that the default value for Discontinued column is to be used. INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
Practice #6: Insert a column value by using expression. The following insert query uses UnitsInStock column in the expression to provide a value for ReorderLevel. An expression can refer to any column that was set earlier in a value list. The caveat is that the column that contains an expression must refer to column(s) defined before it. Here UnitsInStock is defined before ReorderLevel. INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, Products table looks like below after the insert.
Practice #7: Insert a single quote or other special characters The following query inserts a string data type which contains a single quote. Single quote is a special character that must begin with a backslash (also known as Escape Character). Refer to this MySQL page about special character escape sequences. Please note that the single quote ' can also be escaped by two single quotes ''. INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
Below is the Special Character Escape Sequences listed on MySQL website. Each of these sequences begins with a backslash.
Practice #8: Insert into a table with SET keyword This insert statement is nearly identical to the example above, but instead of using VALUES list, is assigns each column value by using SET keyword. INSERT INTO products Products table looks like below after the insert.
Happy coding! Other tutorials in this category 1. Update Statement in MySQL |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |