Mysql alter table add column6/22/2023 ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop index KEY `firstname_employee_INDEX` (`firstname`) UNIQUE KEY `email_employee_UNIQUE` (`email`), So, our table will look like the following. In the following example we are adding an index to the firstname column.ĪDD INDEX `firstname_employee_INDEX` (`firstname`) We add index to columns to optimise search. To drop or delete a unique key constraint we use the DROP INDEX command.įor example, if we want to drop the UNIQUE KEY constraint from the email column by the symbol email_employee_UNIQUE we will run the following command. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop unique key UNIQUE KEY `email_employee_UNIQUE` (`email`) UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`), Now, our table will look like the following. In the following example we are adding a new column email to the employee table.Īnd now we are making it unique so that no two employee gets the same email address.ĪDD UNIQUE KEY `email_employee_UNIQUE` (`email`) We add the UNIQUE KEY constraint to ensure that the column contains distinct values. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Add unique key Now the table will look like the following. Now we will drop the temp column from the table. Right now the table looks like the following. In the following example we are dropping the temp column from the employee table. To drop or delete a column from a table we use the DROP COLUMN command. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop column Lets rename the dateofbirth column back to birthday and modify the column to NOT NULL and type from DATETIME to DATE.ĬHANGE COLUMN `dateofbirth` `birthday` DATE NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Rename and modify column Lets rename the birthday column to dateofbirth.ĬHANGE COLUMN `birthday` `dateofbirth` DATETIME DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Rename column MODIFY COLUMN `birthday` DATETIME DEFAULT NULL Lets modify the birthday column that we added to the employee table by changing its type from DATE to DATETIME. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Modify column Now our table will look like the following. Now we will add the birthday column after score column. UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`) In the following example we are adding birthday column to the employee table that we created in the CREATE Table tutorial. And in this tutorial we will cover some of the commonly used modification that we will encounter. We use the ALTER TABLE table_name command to alter tables in MySQL. RIGHT JOIN SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition 4.In this tutorial we will learn to alter tables in MySQL. LEFT JOIN SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition 3. INNER JOIN SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition 2. How to delete stored procedure DROP PROCEDURE sp_name Joins 1. How to call Stored procedure CALL sp_name 3. Create a Stored Procedure CREATE PROCEDURE sp_name(p1 datatype)ĮND 2. Drop a Trigger DROP TRIGGER trigger_name Stored Procedures 1. Create a Trigger CREATE TRIGGER trigger_name trigger_time trigger_event Deleting a View DROP VIEW View_name Triggers 1. How to call view SELECT * FROM View_name 3. DROP INDEX DROP INDEX index_name ON table_name Views 1. CREATE INDEX CREATE INDEX index_name on table_name(column_name) ĬREATE UNIQUE INDEX index_name on table_name(column_name) 2. DELETE DELETE FROM table_name where condition Example DELETE from EMPLOYEE where empId='0001' Indexes 1. WHERE condition Example UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001' 4. Example SELECT * FROM EMPLOYEE where dept ='sales' 3. Example INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales') 2. INSERT INSERT INTO table_name (column1, column2, column3. COMMENT Single-Line Comments: -Line1 Multi-Line comments: /* Line1, RENAME RENAME TABLE table_name1 to new_table_name1 6. ALTER ALTER TABLE Table_name ADD column_name datatype Example INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales') 3.
0 Comments
Leave a Reply. |