insert and replace mysql query

mysql insert and replace query

INSERT  is used to add records in a table.

For columns configured with the AUTO_INCREMENT option or a TIMESTAMP data type, you can specify NULL rather than an actual value, or omit the column and value altogether. Doing so inserts the correct value into those columns. 

In addition, you can use the DEFAULT keyword in any place that you want the default value for that column inserted in the table. 

INSERT INTO CDs
VALUES (NULL, ‘Ain\’t Ever Satisfied: The Steve Earle Collection’,
1996, 2, 10, 3, NumberInStock-NumberOnReserve, ‘Country’, NULL);


The first specified value is NULL. The value is used for the CDID column, which is configured with the AUTO_INCREMENT option and is the primary key. By specifying NULL, the next incremented value is automatically inserted in that column when you add this row to the table

The backslash is used in a string value to notify MySQL that the following character is a literal value and should not be interpreted as the ending quote of the string. The backslash is useful for any characters that could be misinterpreted when executing a statement that contains a string value.

You can also use the backslash to specify other literal values, such as double quotes (\”), a backslash
(\\), a percentage sign (\%) or an underscore (\_).
you can insert the current date and time in the table is to use the NOW( ) function,
If you want to retrieve only the current date, and not the time, you can use the CURDATE( ) function.
If you want to retrieve only the current time, and not the current date, you can use the 

CURTIME ( ) function.


INSERT LOW_PRIORITY INTO CDs (CDName, Copyright, NumberDisks,
NumberInStock, NumberOnReserve, NumberAvailable, CDType)
VALUES (‘After the Rain: The Soft Sounds of Erik Satie’,
1995, DEFAULT, 13, 2, NumberInStock - NumberOnReserve, ‘Classical’);

The LOW_PRIORITY option in the INSERT clause . As a result , this statement is not processed and the client is put on hold until all other client connections have completed accessing the target table.

DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user_set_records`(in _setid bigint(20), in _queryid1 bigint(20) ,in _queryid2 bigint(20),in _queryid3 bigint(20),in _queryid4 bigint(20),in _queryid5 bigint(20))

BEGIN

INSERT INTO ys_users_set (`userid`,`cecid`,`startqueryid`,`start`) VALUES (_userid,_cecid,_startqueryid,curdate());

INSERT LOW_PRIORITY INTO ys_user_set_records
VALUES (`setid`, `queryid`),
(_setid, _queryid1),
(_setid, _queryid2),
(_setid, _queryid3),
(_setid, _queryid4),
(_setid, _queryid5);
END

type-1
INSERT INTO Formats
VALUES (‘f1’, ‘Widescreen’);
type-2
INSERT INTO Formats (FormID, FormDescrip)
VALUES (‘f2’, ‘Fullscreen’);
type-3
INSERT INTO Roles
VALUES (‘r101’, ‘Actor’),
(‘r102’, ‘Director’),
(‘r103’, ‘Producer’),
(‘r104’, ‘Executive Producer’),
(‘r105’, ‘Co-Producer’),
(‘r106’, ‘Assistant Producer’),
(‘r107’, ‘Screenwriter’),
(‘r108’, ‘Composer’);


INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
VALUES (1, 1, CURDATE(), CURDATE()+3),
(1, 4, CURDATE(), CURDATE()+3),
(1, 8, CURDATE(), CURDATE()+3),
(2, 3, CURDATE(), CURDATE()+3),



INSERT DELAYED INTO CDs
SET CDName=’Blues on the Bayou’, Copyright=1998,
NumberDisks=DEFAULT, NumberInStock=4, NumberOnReserve=1
NumberAvailable=NumberInStock-NumberOnReserve, CDType=’Blues’;

Repalce

In addition to using an INSERT statement to add data to a table, you can also use a REPLACE statement. A REPLACE statement is similar to an INSERT statement in most respects. The main difference between the two is in how values in a primary key column or a unique index are treated. In an INSERT statement, if you try to insert a row that contains a unique index or primary key value that already exists in the table, you aren’t able to add that row. A REPLACE statement, however, deletes the old row and adds the new row.

REPLACE MovieTypes
SET MTypeID=’mt17’, MTypeDescrip=’Foreign-subtitled’;