Inserting records in MySQL

Consider below table.

 

CREATE TABLE CUSTOMER(
    CustomerId SERIAL PRIMARY KEY,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    EmailId VARCHAR(30) NOT NULL UNIQUE,
    AddressLine1 VARCHAR(30) NOT NULL,
    AddressLine2 VARCHAR(30),
    AreaName VARCHAR(30) NOT NULL,
    AreaCode INT(30) NOT NULL,
    CityName VARCHAR(30) NOT NULL,
    BirthDate DATE,
    MobileNo VARCHAR(20) NOT NULL CHECK (LEN(MobileNo) > 9),
    Sex ENUM(‘m’, ‘f’) NOT NULL
    );
    

Below query will insert new record in CUSTOMER table.

 
INSERT INTO CUSTOMER (FirstName, LastName, EmailId, AddressLine1, AreaName, AreaCode, CityName, MobileNo, Sex) VALUES (
    ‘Shaun’,’Tait’,’[email protected]’,’3/4 abc street’,’Toowong’,4066, ‘Brisbane’,’0138287170′,’m’);
    
    INSERT INTO CUSTOMER (FirstName, LastName, EmailId, AddressLine1, AreaName, AreaCode, CityName, MobileNo, Sex) VALUES (
    ‘Kathy’,’Tait’,’[email protected]’,’Ascog street’,’Taringa’,4068, ‘Brisbane’,’0138287171′,’f’);
    

Consider another table.

 
CREATE TABLE CATEGORY(
    CatId INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    CatName VARCHAR(30) NOT NULL,
    SubCatName VARCHAR(30) NOT NULL
    );

 
INSERT INTO CATEGORY(CatName, SubCatName) VALUES (‘Real Estate’,’Shared Accommodation’);

 
CREATE TABLE ADVERTS(
    AdId SERIAL PRIMARY KEY,
    CustomerId INT(20) NOT NULL REFERENCES CUSTOMER(CustomerId),
    AdTitle VARCHAR(30) NOT NULL,
    AdDescription VARCHAR(1000) NOT NULL,
    CatId INT(20) NOT NULL REFERENCES CATEGORY(CatId),
    PostedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

 

Alter table modify column AdDescription VARCHAR(1000) NOT NULL;

INSERT INTO ADVERTS(CustomerId, AdTitle, AdDescription, CatId) VALUES (1,’Room available on rent on sharing basis’, ‘
Room is available on rent for very cheap rate. only $130. Includes all bills – electricty and internet, fully furnished.’,1);

 

insert into city(Name,CountryCode,District,Population) values(‘Mumbai’,’IND’,’Maharashtra’,44000000);
insert into suburb(Name,code,city) values(‘Toowong’,4066,’Brisbane’);

If you execute the query, without specifying the column names, you will get below error.

 
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

You can use below query to get the last insert id in auto increment table.

 
select last_insert_id();

When inserting the records in the table that does not have a primary or unique key, duplicate records may be inserted. To prevent duplicate records, you must have a primary or unique key in your table. But in case you do not have created a such key, you can still prevent the duplicate records by using 2 queries.

 
insert ignore into Table values(v1,v2)
replace into table values(v1,v2)

Consider below table.

 
Create TABLE dup(
    LastName CHAR(30),
    FirstName CHAR(30)
    );
    
    insert into dup values(‘watson’,’shaun’);
    
    insert into dup values(‘watson’,’shaun’);
    insert into dup values(‘watson’,’shaun’);
    insert into dup values(‘smith’,’steve’);
    

To see if firstName is repeated, you can use below syntax.

 
select FirstName, count(*) rep from dup group by FirstName having rep > 1;

To remove duplicates, you can use below query.

 
select FirstName, LastName from dup group by FirstName, LastName;