Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 22-12-2010
Member
 
Join Date: Jul 2010
Posts: 75
MySQL: Entering data in tables

Data entry in a table is via the INSERT statement. Obviously we need to have permission to INSERT on the table. Let us see what we can use different syntax:
Code:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name [(col_name ,...)] 
VALUES ({expression | DEFAULT },...),(...),... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]
With this syntax we can use one or more rows in the table. Before the VALUES clause can specify the column names affected by the INSERT statement: these names will correspond to the values included in parentheses after each VALUES. To insert multiple rows will use more than one pair of parentheses after VALUES. If you do not specify a column list, we provide a value for each column in the order they are defined.
Code:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name 
December column_name = {expression | DEFAULT}, ... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]
In this case we use the SET clause to explicitly assign a value to each column indicated.
Reply With Quote
  #2  
Old 22-12-2010
Member
 
Join Date: Jul 2010
Posts: 75
Re: MySQL: Entering data in tables

Code:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name [(col_name ,...)] 
SELECT ... 
[ON DUPLICATE KEY UPDATE col_name = expression, ... ]
Here we use a SELECT to directly supply values to the new table. With this system you can insert multiple rows. As in the first case, you can list the columns involved, otherwise the SELECT must provide values for all columns. Now let's see the function of various terms used:
  • LOW PRIORITY: The inclusion is not made until there are customers who read on the table involved, this can also lead to long waits.
  • DELAYED: in this case the insertion is delayed until the table is not free. The difference with the previous case is that the client is given the go ahead immediately, and the lines are kept to be included in a buffer managed by the server until the time of actual writing. Of course the lines will not be visible until a SELECT will not be placed on the table.
  • HIGH PRIORITY: cancel the effect of a possible option - low-priority-updates that were active on the server.
  • IGNORE: can handle errors that occur during insertion (duplicate keys or invalid values) instead of throwing fatal error, the records with double keys are simply discarded as invalid values are "adjusted" to the maximum value next.
  • ON DUPLICATE KEY UPDATE: If you detect a key double the specified instruction is executed on the existing line. With this option you can not use DELAYED.
These values in the table with the first two syntax can be indicated by constants or expressions, or by calling explicitly DEFAULT. The default is also used for columns not specified. However, in strict mode, you must specify values for all columns that have no explicit default, otherwise you will get an error.
Reply With Quote
  #3  
Old 22-12-2010
Member
 
Join Date: Jul 2010
Posts: 75
Re: MySQL: Entering data in tables

AUTO_INCREMENT columns are measured automatically, showing NULL (or omitted). To know the value generated may be used after the insertion, LAST_INSERT_ID () which returns the last value created during the current connection. In addition to the INSERT, REPLACE, MySQL offers education, which is an extension to the SQL standard and can replace existing rows with the rows inserted if there is a situation of dual key. In practice, using REPLACE, if you can not insert a row because a PRIMARY KEY or a UNIQUE index already exists on the table, MySQL deletes the old row and insert the new one. This behavior is opposite to that of INSERT IGNORE, with which the new line to be discarded.

To make a REPLACE we have permission to INSERT and DELETE statements, the syntax is almost identical to those of the INSERT, Let's see:

Code:
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name ,...)] 
VALUES ({expression | DEFAULT },...),(...),... 
or 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name 
December column_name = {expression | DEFAULT}, ... 
or 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name ,...)] 
SELECT ...
Another way to insert data into a table that allows us to import a text file: LOAD DATA INFILE. Let's see the syntax:
Code:
LOAD DATA [LOW_PRIORITY | Concurrent] [LOCAL] INFILE 'file_name. Txt' 
[REPLACE | IGNORE] 
INTO TABLE table_name 
[FIELDS 
[TERMINATED BY 'string'] 
[[Optionally] ENCLOSED BY 'char'] 
[ESCAPED BY 'char'] 
] 
[LINES 
[STARTING BY 'string'] 
[TERMINATED BY 'string'] 
] 
[IGNORE number LINES] 
[(Name_column_o_variable ,...)] 
[SET col_name = expression ,...)]
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "MySQL: Entering data in tables"
Thread Thread Starter Forum Replies Last Post
How to manage databases and tables in MySQL? Aahlaadith Software Development 5 17-12-2010 07:18 AM
How to detect failure of MySQL database tables Agneya Software Development 5 27-02-2010 04:40 AM
How to create MySQL Database and Tables in PHP? Orton Software Development 4 23-02-2010 06:45 AM
Mysql select from multiple tables for php. afidelino Software Development 4 09-07-2009 06:13 PM
Restore corrupted MyISAM/InnoDB tables on MySQL 5.0 jean-paul martell Software Development 5 14-04-2009 09:03 AM


All times are GMT +5.5. The time now is 03:03 AM.