# create table
create table student1(
st_id varchar(20) primary key not null,
st_name varchar(30),
st_age DATE);
# insert records in table (student1)
insert into student1 (st_id, st_name, st_age)values
(01,"anand","1990-12-12");
or
insert into student1 values(02,"amit","1991-01-12");
# add new column in table
alter table student1 add column st_phone integer (10);
# to display the all records from table
select * from student1;
# update the data items in table
update student1 set st_phone=258255 where st_id =01;
update student1 set st_phone=98285255 where st_id =02;
#drop any table in database
drop table student1;
# rename table
alter table student1 rename studentnew;
#truncate (remove all records from table )
truncate table studentnew;
# remove any column from table
alter table studentnew drop st_phone;
# display all records/rows
select * from studentnew;
# display perticular column/field
select st_age from studentnew;
select st_age , st_name from studentnew group by st_id;
# display one record/ row
select * from studentnew where st_id=01;
# delete the record from table
Delete from studentnew where st_age = "1990-01-15" ;
# update /changing the column datatype
Alter table studentnew change st_name st_fathername char (20);
# using aggrigate function in tables columns
# To display count records value
Select count(*) from studentnew ;
# aggregate function
Select count(st_age) from studentnew;
Select max(st_age) from studentnew;
Select count(st_age) , min(st_age)from studentnew;
Alter table studentnew change st_name st_lastname varchar (20);
# foreign key constraints
# Parents table(person)
CREATE TABLE Persons (
PersonID int NOT NULL primary key,
person_name varchar(20)
);
# Child table (Orders)
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
Insert into persons values(01,”amit”);
Insert into Orders values (11,98,01);
# fetch (display ) column from two tables
select person_name, OrderNumber from Orders , Persons where PersonID = 01 ;
# like operator uses
select * from persons where person_name like="a%";
select * from persons where person_name like "a_";
# join two or more tables
1. inner joining:
select * from persons inner join orders on persons.PersonID=orders.PersonID;
#using and / or operator in my sql
select column1 , column2 from table name where condition1 or condition2 or condition3 ;
select * from table name where condition1 and condition2 ;
#using not operator
select * from tablename where not condition;
#having clause
select column name from tablename groupby column name having condition ;
select person name , sum(person salary) from persons group by person_id having person_id=01;
