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');


Example:

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');


Or:

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.