When supplying the data values to be inserted into the new table, the following should be considered: Show
Example: Suppose that we have the following list of new library members that need to be added to the database. Let’s INSERT data one by one. We will start with Leonard Hofstadter. We will treat the contact number as a numeric data type and not enclose the number in single quotes. INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Leonard Hofstadter','Male','Woodcrest',0845738767); Executing the above script drops the 0 from Leonard’s contact number. This is because the value will be treated as a numeric value, and the zero (0) at the beginning is dropped since it’s not significant. To avoid such problems, the value must be enclosed in single quotes as shown below – INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Sheldon Cooper','Male','Woodcrest', '0976736763'); In the above case, zero(0) will not be dropped Changing the order of the columns has no effect on the INSERT query in MySQL as long as the correct values have been mapped to the correct columns. The query shown below demonstrates the above point. INSERT INTO `members` (`contact_number`,`gender`,`full_names`,`physical_address`) VALUES ('0938867763','Male','Rajesh Koothrappali','Woodcrest'); The above queries skipped the date of birth column. By default, MySQL will insert NULL values in columns that are omitted in the INSERT query. Let’s now insert the record for Leslie, which has the date of birth supplied. The date value should be enclosed in single quotes using the format ‘YYYY-MM-DD’. INSERT INTO `members` (`full_names`,`date_of_birth`,`gender`,`physical_address`,`contact_number`) VALUES ('Leslie Winkle','1984-02-14','Male','Woodcrest', '0987636553'); All of the above queries specified the columns and mapped them to values in the MySQL insert statement. If we are supplying values for ALL the columns in the table, then we can omit the columns from the MySQL insert query. Example:- INSERT INTO `members` VALUES (9,'Howard Wolowitz','Male','1981-08-24', 'SouthPark','P.O. Box 4563', '0987786553', 'lwolowitz[at]email.me'); Let’s now use the SELECT statement to view all the rows in the member’s table. SELECT * FROM `members`;membership_ numberfull_ namesgenderdate_of_ birthphysical_addresspostal_ addresscontct_ numberemail1Janet JonesFemale21-07-1980First Street Plot No 4Private Bag0759 253 [email protected] Smith JonesFemale23-06-1980Melrose [email protected] PhilMale12-07-19893rd Street [email protected] WilliamsFemale14-02-19842nd Street 23NULLNULLNULL5Leonard HofstadterMaleNULLWoodcrestNULL845738767NULL6Sheldon CooperMaleNULLWoodcrestNULL0976736763NULL7Rajesh KoothrappaliMaleNULLWoodcrestNULL0938867763NULL8Leslie WinkleMale14-02-1984WoodcrestNULL0987636553NULL9Howard WolowitzMale24-08-1981SouthParkP.O. Box [email protected] Notice the contact number for Leonard Hofstadter has dropped the zero (0) from the contact number. The other contact numbers have not dropped the zero (0) at the beginning. Inserting into a Table from another TableThe INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below. INSERT INTO table_1 SELECT * FROM table_2; Let’s now look at a practical example. We will create a dummy table for movie categories for demonstration purposes. We will call the new categories table categories_archive. The script shown below creates the table. CREATE TABLE `categories_archive` ( `category_id` int(11) AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) ) Execute the above script to create the table. Let’s now insert all the rows from the categories table into the categories archive table. The script shown below helps us to achieve that. INSERT INTO `categories_archive` SELECT * FROM `categories`; Executing the above script inserts all the rows from the categories table into the categories archive table. Note the table structures will have to be the same for the script to work. A more robust script is one that maps the column names in the insert table to the ones in the table containing the data. INSERT into MySQL untuk apa?Perintah INSERT INTO dapat di gunakan untuk menambahkan record baru ke dalam tabel.
Langkah langkah membuat database MySQL di cmd?MySQL Membuat Database dan Table. Buka command prompt dengan cara tekan ctrl + R keudian ketik cmd lalu enter.. Buka MySQL dengan cara mengetikan cd AppServ\MySQL\bin\MySQL.. Bila meminta password, masukkan password yang kalian buat (tapi biasanya password defaultnya “root”). Apa itu query INSERT?query insert bertujuan untuk memasukkan data dari PHP ke PhpMyAdmin tanpa harus bekerja 2x dimana sebelumnya kita membuat database setelah itu membuat program agar data tersimpan dengan baik.
Bagaimana cara menambahkan data ke dalam tabel MySQL?Cara Menambah data dalam tabel di database mysql (INSERT).
INSERT INTO nama_tabel VALUES (data_field1, data_field2, data_field3,...); INSERT INTO perintah awal yang digunakan untuk menginput data ke tabel.
|