This post is continuation of the most useful unix commands ever used but in this i will focus on the most used SQL commands that we use on day to day basis and should be aware about there general syntax and usage. So here it goes :-
CRUD: Create, read, update and delete
NOTE: The SQL Keywords are case-insensitive ( SELECT , FROM , WHERE , etc), but are often written in all caps. However in some setups table and column names are case-sensitive.
--------STARTING WITH SELECT FIRST AS THIS IS MOST USED------------------------------------------------------------------
SELECT Queries with WHERE, AND, OR, IN, and LIKE
SELECT * (COLUMN NAMES,NAME2,NAME3) FROM TABLENAME WHERE (CONDITION(COLUMNAME='COLUMNVALUE') ; ORDER BY ; CONDITION CAN BE AND , OR , IN , LIKE
LIKE EXAMPLE
SELECT * FROM student WHERE name LIKE ‘d%K’;
NAME=DEEPAK (D%K)
ORDER BY GENERIC SYNTAX
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
AND & OR GENERIC SYNTAX
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');
WHERE CLAUSE GENERIC SYNTAX AND OPERATORS
SELECT * FROM USERS WHERE USERID=1;
Operators in The WHERE Clause
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
---------------------------------------------------------------------------------------------------
STRUCTURE OF A TABLE
Describe TABLENAME
DESC TABLENAME
Checking performance of query
EXPLAIN QUERY PLAN SELECT * FROM student;
---------------------CREATE EXAMPLE--------------------------------
CREATE TABLE student
(
id INTEGER PRIMARY KEY,
name varchar(20),
address varchar(50),
age INTEGER
);
ONE MORE EXAMPLE
CREATE TABLE STUDENT (
id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NULL,
password VARCHAR(255) NULL,
email VARCHAR(255) NULL,
status int(10) NOT NULL DEFAULT 0,
PRIMARY KEY(id)
);
CREATE TABLE GENERIC SYNTAX
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
CONSTRAINTS
NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value for a column
------------------INSERT INTO -----------------------
GENERIC SYNTAX
INSERT INTO table_name
VALUES (value1,value2,value3,...);
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
NUMBER GENERALLY Don't NEED SINGLE QUOTES BUT STRINGS DO NEED
-------------UPDATE ----------------------------------
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Be careful when updating records. If YOU OMIT WHERE clause, ALL COLUMNS GETS UPDATED
---------DELETE----------------------------------------------------------
GENERIC SYNTAX
DELETE FROM table_name
WHERE some_column=some_value;
ALL DATA DELETE
DELETE FROM table_name;
OR
DELETE * FROM table_name;
-----------------SOME OTHER TO REMEMBER------------------------------
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
DROP/DELETE DATABASE
--------------------Directory Tree Structure--------------------------
vdir -R
