Database
A database is a collection of data that is organized in a manner that facilitates ease of access, as well as efficient management and updating.SQL
Once you understand what a database is, understanding SQL is easy. SQL stands for Structured Query Language.Primary Keys
It is uniquely idetifies row record.SQL is used to access and manipulate a database.
MySQL is a program that understands SQL.
SQL can:
- insert, update, or delete records in a database.
- create new databases, table, stored procedures, views.
- retrieve data from a database, etc
SQL commands
- SHOW DATABASES
display information about all databse content.
- SHOW TABLES
show list of tables for currently selected database
-SHOW COLUMNS FROM table_name
SELECT statement
SELECT colunm-list,..... FROM table_name
The DISTINCT Keyword
use to return only unique records, instead of fetching the duplicates.
SELECT DISTINCT col_name1, col_name2 FROM table_name
Where
where [condition]
Order By
- sorting key word
order by col_name1 [ACES/DECS] , col_name2 [DECS]
The LIMIT Keyword
syntax : LIMIT [limit_integer], [offset_integer]
SELECT DISTINCT col_name1, col_name2 FROM table_name LIMIT 5, 8; show 5 record start from 8
Operator
Conditional
=, != ,< , >, <=, >=, BETWEEN.. AND.. ,
Logical
AND, OR, IN, NOT, [ NOT IN]
Arithmetic Operators
+,- *, /
The CONCAT Function
- CONCAT(FirstName, ', ' , Lastname)
The AS Keyword
- we can use as for column alias
SELECT CONCAT(FirstName, ', ' , Lastname) AS name ....
The LIKE keyword
Like pattern
'_' single character
'%' any character including digit
Function
UPPER, LOWER, SQRT, AVG, SUM, MIN
SELECT UPPER(FirstName) AS name
Table Join
-LEFTJOIN / LEFT OUTER JOIN [outer is optional]-RIGHTJOIN-INNERJOIN
Select column... ROM table1 as t1 LEFT OUTER JOIN table2 as t2 ON t1.name=t2.t1_name
UNION
UNION combines multiple datasets into a single dataset, and removes any existing duplicates.
UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows. UNIONALL is faster than UNION
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);
INT -A normal-sized integer that can be signed or unsigned.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
Date and Time
DATE - A date in YYYY-MM-DD format.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
TIME - Stores the time in HH:MM:SS format.
String Type
CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.
BLOB - "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files.
TEXT - Large amount of text data.
CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);
Data type:
NumericINT -A normal-sized integer that can be signed or unsigned.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
Date and Time
DATE - A date in YYYY-MM-DD format.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
TIME - Stores the time in HH:MM:SS format.
String Type
CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.
BLOB - "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files.
TEXT - Large amount of text data.
INSERT,UPDATE,DELETE
UPDATE table_name
SET cloumn_name= value, col_name2=val2
where condition
DELETE FROM table_name where condition.
NOT NULL, PRIMARY KEY, DEFAULT, UNIQUE, AUTO_INCREMENT
SQL Constraints
SQL constraints are used to specify rules for table data.NOT NULL, PRIMARY KEY, DEFAULT, UNIQUE, AUTO_INCREMENT
ALTER TABLE
The ALTER TABLE command is used to add, delete, or modify columns in an existing table.
ALTER TABLE table_name ADD column_name type(size) CONSTRAINTS
Dropping
Drop is used to delete column or entire table
DROP Table_name;
ALTER TABLE table_name
DROP COLUMN column_name ;
Renaming
Remane column or table. We use keyword
CHANGE , RENAME
ALTER TABLE table_name
CHANGE field_name oldname newname type(size);
RENAME TABLE oldname TO newname; //rename tablename
VIEWS
Views are virtual table same like real one. Generally result fetched from commands data are displayed in view. They are used for reports.
Creating view
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
Updating views
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
Delete view
DROP VIEW view_name;
Sub Query
A subquery is a query within another query.