MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 3: INSERT

2009-04-16 21:13:57 | 161 views | insert into sql mysql table command

Normal insert:

Synthax 1:

INSERT INTO table_name VALUE('column', 'columns', 'columns');

Syntax 2:

INSERT INTO table_name (COL1, COL2, COL3) VALUES ('columns', 'columns', 'columns');


Assuming this is our table:

|Field |Type |Null |
|Name varchar(150) NO |
|Age int(3) NO |
|Gender varchar(25) YES |

INSERT INTO person VALUES ('Bob', 34, 'Male');


INSERT INTO person (Name, Age, Gender) VALUES ('Bob', 34, 'Male');

Now you have inserted a person. Bob is 34 years old and Male. Cool guy.

Advanced insert:

Well, it's not that advanced. But you can, of course, insert data into a table while not including all columns. This method, however, has a limitation. If a column is set to NOT NULL, this is not possible; that columns HAS to be set. If it's however set to NULL, we don't have to set it while inserting a record. If a NULL column is not set it will get a default value coherent with the column datatype.
Our table was created with NOT NULL set columns, but if it wasn't, this is how we would add two columns to a table consisting of tree columns:

INSERT INTO person (Name, Age) VALUES ('Bob', 34);

Gender would be set to "".

Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.