- Creating tables ๐
CREATE TABLE BookHistory (
Auther VARCHAR(129),
title VARCHAR(129),
btype VARCHAR(129),
year CHAR(4)
);
- To drop table column
ALTER TABLE tableName DROP column-Name;
ex.
ALTER TABLE BookHistory DROP Auther;
- How to delete Data from a MySQL table ?
// Delete Statement is used to delete data,
DELETE FROM table_name WHERE column_name = VALUE
EX.
DELETE FROM BookHistory WHERE title = 'JungleBook'
- Inserting Value into Table.
INSERT INTO table_name (Column1, Column2, Column3 ) VALUES (value1, value3, value3);
EX.
INSERT INTO BookHistory (Author, title, btype, year) VALUES ("James Camron", "Avatar", "Adventure", 2006);
- Update column name.
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
EX.
ALTER TABLE BookHistory RENAME COLUMN btype TO Book_type;
- Add new column in table
ALTER TABLE table_name ADD column_name DATATYPE;
EX.
ALTER TABLE BookHistory ADD price INT(4);
-
The Select Command : DQL- DATA QUERY LANGUAGE Command.
Used to visualize the table content.
SELECT * FROM table_name;
EX.
SELECT * FROM BookHistory;
// It will show all data present inside the BookHistory table
We can use SELECT command to retrieve specific data from the table using WHERE clause . Like below
SELECT * FROM table_name WHERE condition;
EX.
SELECT * FROM BookHistory WHERE Book_type = "hacking";
-
MySQL numeric Data types
MySQL has numeric data types for Integer, Fixed-point, Floating-Point and bit etc.
*Numeric can be singed or unsinged
1. TINYINT 6. FLOAT 2. SMALLINT 7. DOUBLE 3. MEDIUMINT 8. BIT 4. INT 5. BIGINT -
String Data type.
1. CHAR 5. TINYBLOB 2. VARCHAR 6. MEDIUMBLOB 3. BINARY 7. LONGBLOB 4. VARBINARY -
Temporal Data Types in MySQL
- DATE - A date value in 'CCYY-MM-DD'
- TIME- Time in 'HH:MM:SS'
- DATETIME - Date-Time - 'CCVV-MM-DD HH:MM:SS'
- TIMESTAMP - 'CCVV-MM-DD' HH:MM:SS
- YEAR - CCYY or YY
Create user in MySQL
CREATE USE 'user-name' IDENTIFIED BY 'sample-password';
- What are the "VIEWS" ?
โ In MySQL , A view consists of a set of rows that returned if particular query is executed.
โ It also known as "Virtual Table"
โ Advantages : Simplicity, security, not consume any memory, maintainability.
-
How to you create & Execute VIEWS in MySQL ?
โ We can create views using the CREATE VIEW Statement;
-> A view is table in database that has no values, The views are created by joining one or more tables.
-> Syntax for creating Views
CREATE [or REPLACE] VIEW view_name AS SELECT columns FROM TABLES [ WHERE CONDITION ]
SELECT AND command.
SELECT * FROM cust_tbl WHERE f_name = "shubham" AND cust_id > 3;
- Truncate : It removes complete data without removing itโs structure. It is a DDL Command
TRUNCATE TABLE table_name;
EX.
TRUNCATE TABLE BookHistory;
- Update Command in MySQL.
UPDATE 'table_name' SET 'column_name' = 'new_value' [WHERE CONDITION];
EX.
UPDATE BookHistory SET 'Auther' = 'James Bond' WHERE Auther = "JB";
- BETWEEN : Get values between particular condition.
SELECT * FROM cus_tbl WHERE ID = 8 AND 11;
-
Find version of installed MySQL.
Type Following Command.
SHOW VARIABLES LIKE "%version%";
-
ENUM and SET.
ENUM data type is used in the MySQL datatypes to select any one value from the predefined list. Ex
CREATE DATABASE newEnum;
CREATE TABLE Clients (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
memberShip ENUM('silver', 'gold', 'Diamond'),
interested SET('Movie', 'Music', 'concert');
)
Cannot set multiple in ENUM but we can set multiple values in SET.
- What is different between Primary key and Foreign Key ?
-> The database table uses Primary key to identify each row uniquely, It is necessary to declare a primary key on those tables that require tp create a relationship among them. - One or More field of table can be declared as primary key
-> When primary key of any table is used in another table as the primary key or anther field for making a database relation then it is called Foreign Key.
-> Primary Key : Identified a record whereas foreign key refers the primary key of another tables. Primary key never accepts not null value. But Foreign key accepts null value.
-
Filter duplicate values.
-> A DISTINCT keyword is used to identify the duplicate data from table while retrieving the records. EX.
SELECT * FROM items
output:
id | name | type | brand | man_id |
---|---|---|---|---|
1 | samsung | mobile | samsung | 1 |
2 | iPhone | mobile | apple | 2 |
3 | Sony | TV | Sony | 3 |
- Using DISTINCT Keyword
SELECT DISTINCT type FROM items;
output:
type |
---|
mobile |
TV |
- Which statement is used in a select query statement for partial matching ?
โ REGEXP and LIKE statement can be used in a select query for partial matching.
- REGEXP : used to Search records bases on the pattern matching
- LIKE : Is used to search any record by matching string at beginning or end or middle of particular filed value.
Ex. 1. REGEXP (Search records start with โSโ);
SELECT * FROM BookHistory WHERE name REGEXP "^s";
Ex. 2. LIKE
SELECT * FROM BookHistory WHERE name LIKE "A%";
- Rename Table
RENAME TABLE table_name TO new_table_name;
EX.
RENAME TABLE items TO Products;
- Retrieve a portion of any Column value by using select Query ?
โ * SUBSTR() Function is used to retrieve the portion of any column. EX
OR we can we โSUBSTRINGโ keyword
SELECT SUBSTR(name, 1, 5) FROM Products;
output:
Samsu |
---|
iPhon |
Sony |
- Calculate Sum of any Column of table ?
โ SUM() Function is used to calculate the sum of any column. EX.
SUM(DISTINCT expression)
EX.
SELECT SUM(Price) as total FROM Products;
output:
total |
---|
2109.00 |
The Practical Approach
- Fetch FIRST_NAME from worker table.
SELECT First_name as worker_name from Woker;
//will return all first_name 's
- Fetch FIRST_NAME as Upper Case
SELECT upper(First_name) FROM Worker
// Will return all name in upper case
- Fetch unique Values from Department
SELECT DISTINCT department FROM Worker;
output :
department |
---|
HR |
ADMIN |
ACCOUNT |
- Find position of alphabets (โaโ) in first_name column โAmitabhโ from worker.
SELECT INSTR(first_name, BINARY'a') FROM worker WHERE first_name = "Amitabh";
output :
INSTR( first_name, BINARY โaโ ) |
---|
5 |
-
Remove white Spaces
- RTRIM : To remove white spaces from right side. EX
SELECT RTRIM(first_name) FROM Worker;
b. LTRIM : To remove white spaces from left side. EX
SELECT LTRIM(Department) FROM Worker;
Query to print
first_name
andsalary
from worker table into a single column NAME_SALARY
โ We use CONCAT() keyword to get combined result from two or more tables. EX
SELECT CONCAT(first_name, "=" , Salary) AS 'NAME_SALARY' FROM Worker;
- Query to print all worker details from worker table order by First_Name Ascending.
SELECT * FROM Worker ORDER BY First_Name ASC;
- Print details for worker with First_name as โShubhamโ and โNICKโ from worker table.
SELECT * FROM Worker WHERE First_Name In('Shubham', 'NICK');
- Query to fetch the count of employee working in the department โadminโ.
โ The count function return cunt of given queries : EX.
SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';
output :
count(4) |
---|
4 |
AGGREGATE FUNCTIONS : -
SQL Aggregate functions is used to perform calculations on multiple row of a single column of a table it return single value.
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
- COUNT()
โ Count the number of rows in database; It uses function COUNT(*) that return all rows
SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';
count(4) |
---|
4 |
- SUM()
โ SUM() Function is used to calculate the sum of all selected columns. it only work on numeric values. Syntax: sum();
SELECT SUM(salary) FROM Worker;
or
SELECT
- AVG()
โ Used to calculate average value of the numeric type. AVG function return the average of all non-null values. Syntax: AVG();
SELECT Avg(salary) FROM Workder;
- MAX():
โ Max function used to find the maximum value of a certain column . This function determines the largest value of all selected values of column. Syntax: MAX();
SELECT MAX(SALARY) FROM Worker;
- MIN();
โ MIN used to find minimum value of a certain column. this function determines the smallest value of all selected of a column.
SELECT MIN(SALARY) FROM Worker
Joins
Two relations
- Personal
- Professional
select * from personal;
+----+----------+---------+-----------+
| id | name | address | contact |
+----+----------+---------+-----------+
| 10 | ankit | kapali | 838405939 |
| 20 | ankush | kapali | 835403953 |
| 30 | akhilesh | mango | 335502953 |
| 40 | altaf | sakchi | 985402953 |
+----+----------+---------+-----------+
select * from professional;
+------------+-------+------+
| work | dep | id |
+------------+-------+------+
| teacher | bca | 20 |
| docktor | mbbs | 40 |
| teacher | mba | 50 |
| programmer | java | 60 |
| cashier | icici | 10 |
+------------+-------+------+
1. Natural Join
select * from personal natural join professional;
Output:
+----+--------+---------+-----------+---------+-------+
| id | name | address | contact | work | dep |
+----+--------+---------+-----------+---------+-------+
| 10 | ankit | kapali | 838405939 | cashier | icici |
| 20 | ankush | kapali | 835403953 | teacher | bca |
| 40 | altaf | sakchi | 985402953 | docktor | mbbs |
+----+--------+---------+-----------+---------+-------+
2. Inner Join
select name,contact,dep from personal inner join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);
output:
+--------+-----------+-------+
| name | contact | dep |
+--------+-----------+-------+
| ankit | 838405939 | icici |
| ankush | 835403953 | bca |
| altaf | 985402953 | mbbs |
+--------+-----------+-------+
3. Cross Join
select * from personal cross join professional;
output:
+----+----------+---------+-----------+------------+-------+------+
| id | name | address | contact | work | dep | id |
+----+----------+---------+-----------+------------+-------+------+
| 10 | ankit | kapali | 838405939 | teacher | bca | 20 |
| 20 | ankush | kapali | 835403953 | teacher | bca | 20 |
| 30 | akhilesh | mango | 335502953 | teacher | bca | 20 |
| 40 | altaf | sakchi | 985402953 | teacher | bca | 20 |
| 10 | ankit | kapali | 838405939 | docktor | mbbs | 40 |
| 20 | ankush | kapali | 835403953 | docktor | mbbs | 40 |
| 30 | akhilesh | mango | 335502953 | docktor | mbbs | 40 |
| 40 | altaf | sakchi | 985402953 | docktor | mbbs | 40 |
| 10 | ankit | kapali | 838405939 | teacher | mba | 50 |
| 20 | ankush | kapali | 835403953 | teacher | mba | 50 |
| 30 | akhilesh | mango | 335502953 | teacher | mba | 50 |
| 40 | altaf | sakchi | 985402953 | teacher | mba | 50 |
| 10 | ankit | kapali | 838405939 | programmer | java | 60 |
| 20 | ankush | kapali | 835403953 | programmer | java | 60 |
| 30 | akhilesh | mango | 335502953 | programmer | java | 60 |
| 40 | altaf | sakchi | 985402953 | programmer | java | 60 |
| 10 | ankit | kapali | 838405939 | cashier | icici | 10 |
| 20 | ankush | kapali | 835403953 | cashier | icici | 10 |
| 30 | akhilesh | mango | 335502953 | cashier | icici | 10 |
| 40 | altaf | sakchi | 985402953 | cashier | icici | 10 |
+----+----------+---------+-----------+------------+-------+------+
4. Left outer join
select name, address, dep, work from personal left outer join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);
output:
+----------+---------+-------+---------+
| name | address | dep | work |
+----------+---------+-------+---------+
| ankush | kapali | bca | teacher |
| altaf | sakchi | mbbs | docktor |
| ankit | kapali | icici | cashier |
| akhilesh | mango | NULL | NULL |
+----------+---------+-------+---------+
5. Right outer join
select name, address, dep, work from personal right outer join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);
Outupt:
+--------+---------+-------+------------+
| name | address | dep | work |
+--------+---------+-------+------------+
| ankit | kapali | icici | cashier |
| ankush | kapali | bca | teacher |
| altaf | sakchi | mbbs | docktor |
| NULL | NULL | mba | teacher |
| NULL | NULL | java | programmer |
+--------+---------+-------+------------+
Top comments (0)