DAY1:
******
Create DataBase:
CREATE DATABASE db_name;
Select DataBase:
USE db_name;
Displays list of schemmas:
SHOW DATABASES;
Drop Databases;
DROP DATABASE db_name;
Practice:
>CREATE DATABASE sampledb;
>USE sampledb;
>SHOW DATABASES;
>DROP DATABASE sampledb;
Create Table:
CREATE TABLE tb_name (col_name datatype, .................);
Displays list of Tables:
SHOW TABLES;
RENAME:
RENAME TABLE tb_name TO new_tb_name;
Or
ALTER TABLE tb_name RENAME TO new_tb_name;
Drop Databases;
DROP DATABASE db_name;
Practice:
>CREATE TABLE students(id int, name varchar(100), email varchar(50));
>CREATE TABLE marks(id int, subject1 int, subject2 int, subject3 int);
>SHOW TABLES;
>RENAME TABLE marks TO memo_card;
>CREATE TABLE s1(id int, name varchar(100));
>CREATE TABLE s2(id int, name varchar(100));
>DROP TABLE students;
* Drop multiple tables at once
- Drop table tb1, tb2, ......;
>Drop table s1, s2:
DAY2:
******
INSERT Data:
INSERT INTO tb_name(col_1,col_2, ...) VALUES(val_1, val_2, ......);
or
INSERT INTO tb_name VALUES(val_1, val_2, ......);
UPDATE Data:
UPDATE tb_name SET col = value, col2 = value2 ....... WHERE <condition>;
Updating all rows
UPDATE tb_name SET col = value;
DELETE Data:
DELETE FROM tb_name WHERE <condition>;
SELECT:
SELECT col1, col2.......coln FROM tb_name;
Or
SELECT * FROM tb_name; [ ‘ * ‘ means all columns]
WHERE:
SELECT * FROM tb_name WHERE <condition>;
Or
SELECT col1, col2.......coln FROM tb_name WHERE <condition>;
Practice:
>INSERT INTO students(id, name,email) VALUES(101, ‘siri’, ‘siri@gmail.com’);
or
>INSERT INTO students VALUES(102, ‘arun’, ‘arun@gmail.com’);
>UPDATE studentd SET name = “sirisha”, email = “sirisha” WHERE id = 101;
>DELETE FROM students where name = “arun”;
>SELECT * FROM students;
Or
>SELECT name, email FROM students;
>SELECT * FROM students WHERE id = 101;
Or
>SELECT name, email FROM students WHERE id = 101;
* Delete all records
- Delete from students;
DAY3:
******
Alter Table:
Alter Table clause is used to add, delete, or modify columns and also used to add
and drop various constraints on an existing table.
ADD Column:
ALTER TABLE tb_name ADD COLUMN col_name datatype;
Or
ALTER TABLE tb_name ADD col_name datatype;
ALTER TABLE tb_name ADD COLUMN col_name INT NOT NULL AFTER
existing_column;
MODIFY Column: -- modify type column
ALTER TABLE tb_name MODIFY col_name constraints;
CHANGE:-- change type and name of column
ALTER TABLE tb_name CHANGE col_name rename_col datatype constraints;
DROP:
ALTER TABLE tb_name DROP COLUMN col_name;
ALTER TABLE tb_name DROP COLUMN col_name,
DROP COLUMN col_name,
DROP COLUMN col_name;
RENAME:
ALTER TABLE tb_name RENAME TO new_tb_name;
ALTER TABLE tb_name RENAME COLUMN col_name TO new_col_name
INDEX:
ALTER TABLE tb_name ADD INDEX “index_name”(‘col_name’)
ALTER TABLE tb_name ADD INDEX “index_name” (‘col_name’, ‘col_name’)
DROP Constraint:
ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT
NULL PRIMARY KEY;
CHANGE column definition:
To change the type of age column from char to int, we use the query below:
ALTER TABLE students CHANGE age age tinyint UNSIGNED NOT NULL;
Practice:
> ALTER TABLE students ADD COLUMN DateOfBirth date;
> ALTER TABLE students ADD Address varchar(100);
> ALTER TABLE students ADD Joiningdate date AFTER DateOfBirth;
> ALTER TABLE students CHANGE Joiningdate to Admit_Date NOTNULL;
> ALTER TABLE students MODIFY Admit_Date NOT NULL
> ALTER TABLE students DROP COLUMN DateOfBirth date;
> ALTER TABLE students DROP COLUMN Address,
DROP COLUMN Admit_Date;
> ALTER TABLE students RENAME COLUMN Joiningdate to Admit_Date;
DAY4:
******
JOINS:
Mysql support joins are 1)Inner join, 2) left join, 3) right join, 4) crossjoin 5) self join
Inner Join:
SELECT a.col_name, a.col_name, ......, b.col_name, b.col_name, ...
FROM tb_name AS a
INNER JOIN tb_name AS b
ON a.col_name = b.col_name
WHERE <condition>
ORDER BY <condition>;
Left Join:
SELECT a.col_name, a.col_name, ......, b.col_name, b.col_name, ...
FROM tb_name AS a
LEFT JOIN tb_name AS b
ON a.col_name = b.col_name
WHERE <condition>
ORDER BY <condition>;
Right Join:
SELECT a.col_name, a.col_name, ......, b.col_name, b.col_name, ...
FROM tb_name AS a
LEFT JOIN tb_name AS b
ON a.col_name = b.col_name
WHERE <condition>
ORDER BY <condition>;
Cross Join:
SELECT a.col_name, a.col_name, ......, b.col_name, b.col_name, ...
FROM tb_name AS a
LEFT JOIN tb_name AS b
ON a.col_name = b.col_name
WHERE <condition>
ORDER BY <condition>;
Self Join:
SELECT a.col_name, a.col_name, ......, b.col_name, b.col_name, ...
FROM tb_name AS a
SELF JOIN tb_name AS b
ON a.col_name = b.col_name
WHERE <condition>
ORDER BY <condition>;
DAY5:
******
