MySQL CREATE TABLE Statement
To create a new table in a database the CREATE TABLE statement is used.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Note: The column field specifies the names of the table’s columns.
The datatype field specifies the type of data the column can have. For example varchar, integer, date, etc.
CREATE TABLE Example:
Below is an example of a table called “Mytable” that has the column names UserID, FirstName, LastName, and JobPosition.
Example
CREATE TABLE Mytable (
UserID int,
FirstName varchar(255),
LastName varchar(255),
JobPosition varchar(255),
);
Wish to crack SQL interviews? Intellipaat’s Top DB2 interview questions are meant only for you!
MySQL ALTER TABLE Statement
To add, delete, or modify columns in an existing table the ALTER TABLE statement is used.
To ADD any column in the table, you need to use the syntax which is given below.
Syntax:
ALTER TABLE table_name
ADD column_name datatype;
Alter table- ADD Column Example
Below is an example to ADD a column named “Address” in the table “Mytable”.
Example
ALTER TABLE Mytable
ADD Address varchar(255);
To DELETE any column in the table, you need to use the syntax which is given below.
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Alter table- DROP Column Example
Below is an example to DROP a column named “Address” in the table “Mytable”.
Example
ALTER TABLE Mytable
DROP COLUMN Address;
- ALTER TABLE- Modify Column
To MODIFY any data table of a column in the table, you need to use the following syntax which is given below.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
ALTER TABLE- Modify Column Example
Below is an example to Change or Modify the data type of a column named “DOB” in the table “Mytable”.
Example
ALTER TABLE Mytable
MODIFY COLUMN DOB year;
Want to get certified in SQL? Go through our blog on SQL Server Certification and be informed!
MySQL RENAME TABLE Statement
In MySQL, this RENAME TABLE statement is used to change or rename the name of the table that has been created by mistake or needs to change the table name as per the requirement.
Syntax:
mysql> RENAME OLD_Tablename to NEW_Tablename;
RENAME TABLE Example
Below is an example to RENAME an old table named “Mytable” to a new table named “MyProfile”.
Example
mysql> RENAME Mytable to MyProfile;
MySQL TRUNCATE TABLE Statement
With the MySQL TRUNCATE statement, all the data get deleted, leaving the structure unchanged. When we wish to remove all of the data from a database without deleting the table structure, we use this command.
Syntax:
TRUNCATE TABLE table_name;
TRUNCATE TABLE Example
Below is an example to TRUNCATE the data in the table named “MyProfile”.
Example
mysql> TRUNCATE TABLE MyProfile;
MySQL DROP TABLE Statement
An existing table in MySQL can be deleted using the DROP TABLE statement. This statement removes all the specific data from the database.
Syntax:
mysql> DROP TABLE table_name;
DROP TABLE Example
Below is an example to DROP a table named “MyProfile”.
Example
mysql> DROP TABLE MyProfile;
Basic MySQL Queries
Following are some of the basic MySQL Query Commands. These are listed here:
- SELECT: SELECT statement is used to retrieve the data from the tables.
Syntax: SELECT * FROM [Table name];
Example: SELECT * FROM Comp;
- SELECT DISTINCT: This statement is used to retrieve the distinct data from the table.
Syntax: SELECT DISTINCT [Column name] FROM [Table name];
Example: SELECT DISTINCT Stu_name FROM Comp;
- WHERE: This command is used to filter the data for finding a specific value.
Syntax: SELECT * FROM [TABLE NAME] WHERE [CONDITION];
Example: SELECT * FROM Comp WHERE c_id= 100;
- AND: This condition is used to filter the data based on a particular condition.
Syntax: SELECT [COLUMN NAMES] FROM [TABLE NAME] WHERE [CONDITION] AND [CONDITON];
Example: SELECT Stu_NAME, FROM Comp WHERE c_id=100 AND c_COUNTRY="INDONESIA";
- OR: This statement combines the data from the table for the specific condition.
Syntax: SELECT [COLUMN NAMES] FROM [TABLE NAME] WHERE TRUE OR FALSE
Example: SELECT * FROM Comp WHERE c_COUNTRY="INDONESIA" OR c_COUNTRY = ”MALAYSIA";
- IN: This command helps in filtering the data based on a value match.
Syntax: SELECT COLUMN1, COLUMN2… FROM [TABLE NAME] WHERE [COLUMN NAME] IN (‘val1’,’val2’);
Example: SELECT c_NAME, c_SALARY FROM Students WHERE c_COUNTRY IN (‘INDONESIA’, ‘MALAYSIA’, ‘SWEDEN’);
Take a look at the SQL Commands cheat sheet.
- ORDER BY: It is used to sort the data in a particular order for a particular column in ascending or descending order.
Syntax: SELECT COLUMN1, COLUMN2, FROM [TABLE NAME] ORDER BY Column1 desc, Column2 asc;
Example: SELECT c_NAME, c_id FROM Comp ORDER BY c_NAME desc, c_id asc;
- LIKE: This command is used to retrieve the data from the table for the specific pattern.
Syntax: SELECT COLUMN1, COLUMN2 FROM [TABLE NAME] WHERE COLUMN1 Like’’;
Example: SELECT c_id, c_NAME, c_SALARY FROM Comp WHERE c_NAME like ‘IN%’;
- BETWEEN: This is used to range the data between the two conditions.
Syntax: SELECT Column1, Column2 FROM Table name WHERE Column3 BETWEEN val1 AND val2;
Example: SELECT c_id, c_NAME FROM Comp WHERE c_SAL BETWEEN 500 AND 1000;
- IS NULL: It is used for checking the value or retrieving the data for the particular column that is null.
Syntax: SELECT Column1, Column2 FROM [TABLE NAME] Column3 IS NULL;
Example: SELECT c_id, Stu_NAME FROM Comp WHERE c_SAL IS NULL;
If you have any doubts or queries related to SQL, get them clarified by the SQL experts in our SQL Community!