YogeshChauhan.com
Essential SQL Commands We Need to Know
December 20, 2019

In this blog post, I'll list out the basic and essential commands we all need to know before moving to SQL advanced level.

  1. CREATE TABLE: used to create table.

Syntax:


CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype,
   ....
    column_n datatype,
);

CREATE TABLE is the keyword to start the query of creating table. Then you enter the name you want to give to the table. Inside the brackets you enter names of the columns you want to give and indicate their data types. The most used data types are INT, VARCHAR, BINARY, TEXT, MEDIUMTEXT, LONGTEXT, FLOAT etc.

For example we can create users tables by following command.


CREATE TABLE Users(
    UserID int,
    LastName varchar(255),
    FirstName varchar(255),
    EmailAddress varchar(255),
);
  1. DROP TABLE: used to drop an existing table

Syntax:


DROP TABLE table_name;

For example, this following code will drop the table we just created.


DROP TABLE users;

If you just want to delete the data inside the table use, TRUNCATE TABLE command instead.
Like TRUNCATE TABLE table_name;

  1. ALTER TABLE: used to add, delete or change columns an existing table

Syntax: To Add Column in an existing table


ALTER TABLE table_name
ADD column_name datatype;

Example: This query will add a new column to existing users table (create the table again as we just deleted it in the previous example)


ALTER TABLE users
ADD City varchar(255);

Syntax: To Drop Column in an existing table


ALTER TABLE table_name
DROP COLUMN column_name;

Example: The following query will drop the column City 


ALTER TABLE users
DROP COLUMN City;

Syntax: Change a column in a table (My SQL only)


ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Syntax: Change a column in a table (SQL Server / MS Access only)


ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example, if you want to change datatype of EmailAddress then use this


ALTER TABLE users
ALTER COLUMN EmailAddress MEDIUMTEXT;
  1. SELECT: used to select data from the existing table(s) in the database

Syntax:


SELECT column_1, column_2, ....., column_n
FROM table_name;

Example: If you want to select all the data from table use this-


SELECT * FROM table_name;

Example: If you just want a specific column, use this


SELECT EmailAddress FROM users;

Example: If you want a specific row then you need to add WHERE clause in the query which I will cover in next blog post.

  1. UPDATE: used to change data/records from the existing table(s) 

Syntax:


UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ....., column_n = value_n
WHERE condition;

REMEMBER: DO NOT FORGET TO USE WHERE IN THE UPDATE QUERY. IT WILL UPDATE ALL THE RECORDS IF YOU MISS IT.

Example: Add Email address for an user with UserID=1


UPDATE users
SET EmailAddress='[email protected]'
WHERE UserID=1;
  1. DELETE: used to delete data/records from the existing table(s) 

Syntax:


DELETE FROM table_name WHERE condition;

REMEMBER: DO NOT FORGET TO USE WHERE IN THE DELETE QUERY AS WELL. IT WILL DELETE ALL THE RECORDS IF YOU MISS IT.

Example: Delete the records for UserID=1


DELETE FROM users WHERE UserID=1;
  1. INSERT INTO: used to insert new data/records in the existing table(s) 

Syntax:


INSERT INTO table_name (column_1, column_2, column_3, ....., column_n)
VALUES (value_1, value_2, value_3, ....., value_ n);

Example: This query will add a new row with the following values in each column.


INSERT INTO users (UserID, LastName, FirstName, EmailAddress) VALUES (10,'Chauhan', 'Yogesh', '[email protected]');
  1. CREATE DATABASE: used to create new database. 

Syntax:


CREATE DATABASE databasename;

Example: Let's create employee's master database to save all records of employees.


CREATE DATABASE employees_master_database;
  1. DROP DATABASE: used to drop an existing database.

Syntax:


DROP DATABASE databasename;

Example: Lets drop the database we just created!


DROP DATABASE employees_master_database
dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 5 How to create flickering text using CSS? Mar 4 How to use data-* Attributes in HTML? Mar 4 The substr() method in JavaScript and how it’s different from substring() Mar 4 A complete guide to add responsive YouTube videos using HTML and CSS Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress?

You might also like these

bin2hex() and chr() String Functions in PHPPHPHow to create a new HTML element programmatically using JavaScript?HTMLHow to create Glowing Text using CSS?CSSCheck If a String Contains a Specific Word in PHPPHPSafe Practice to add Links to cross-origin destinationUI/UXaddcslashes() and addslashes() String Functions in PHPPHP