Yogesh Chauhan's Blog

Essential SQL Commands We Need to Know

in SQL/MySQL on 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

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to check if the page is the home page in WordPress?WordPressObject destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptHow to change value of a span tag using a reference from another div using jQuery?jQueryWhat is the difference between let and var in Swift?SwiftHow to create a simple tab interaction using CSS?CSSAngular: Templates, directives, data binding, Services and dependency injectionAngular