DATA DEFENITION LANGUAGE (DDL)
create database databasename;
use databasename;
show tables;
create table student( rno numeric(2),name varchar(20));
datatype constraints:int-max3 dgits;
varchar variable lwnght;
char fixed lenght;
float no with decinal points;
date:
desc student(tablenameonly);
ALTER
alter table student add primarykey(rno);
alter table name student1 add foreignkey(rno) REFERENCES student (rno);
alter table student add UNIQUE(rno);
alter table student add class varchar (5);
alter table student MODIFY class varchar(10);
alter table student modify class varchar(10) NOT NULL;
alter table student modify dob date DEFAULT "2000-12-01";
alter table student drop class;
alter table student drop primarykey;
* DROP TABLE STUDENT*
*DROP DATABASE DATABASENAME*
DATA MANIPULATION LANGUAGE (DML)
INSERT
insert into tablename values ( val1,val2..);(same no of values as the tot no of
attributes)
inserT into tablename (col1, col2) values ( val 1 , val2);
-----------------------------------------------------------------
RENAMING COLUMNS : SELECT clas as class from student;
DISTINCT : select distinct class from student;
ORDER BY:-
SELECT * FROM STUDENT
ORDER BY CLASS(OR) ORDER BY CLASS DESC(OR) ORDER BY RNO,CLASS DESC;
TO HANDLE NULL: SELECT* FROM STUDENT WHERE MARK IS NULL;
SELECT* FROM STUDENT WHERE MARK IS NOT NULL;
SUBSTRING PATTERN : SLECT * FROM EMPLOYEE WHERE NAME LIKE "A%";
SELECT*FROM EMPLOYEE WHERE NAME LIKE _IA;
SELECT*FROM EMPLOYEE WHERE NAME LIKE %A%;
-------------------------------------------------------------------
UPDATE
UPDATE TABLENAME SET COL1=VAL1,....(IF WHERE CLAUSE IS NOT USED THEN THE VAL IS
USED FOR THE ENTIRE TABLE)
update student set rno=101 where name = george;
AGREEGATE FUNCTIONS: AVG,MIN,MAX,COUNT(*), COUNT(COL)
SELECT AVG(MRK),MAX(MRK),MIN(MRK),COUNT(STU),STUS FROM STUDENT GROUP BY STUS;
ALSO USES HAVING
SELECT STU, AVG(MRK) FROM STUDENT GROUP BY STUDENT HAVING AVG(MRK)>80;