My SQL | Short Notes | Class 12 | CS | Computer Science

               MY SQL

____________________

 

#DATABASE

To create database

Create database xyz:

 

#USE COMMAND

To use database

Use xyz:

#COMMAND TO DISPLAY EXISTING DATA

• Show databases:

• Show Tables:

 

# USE OF TABLE COMMAND

To create table

Create table abc(slno int, stdnm varchar(30), fathernm varchar(30), class char(3), sec char(1), marks int):

 

#USE OF DESC

To describe the table

desc abc:

 

#USE OF INSERT

To insert value inside table

insert into abc values(1, "aditya","alok","XI", "A",330):

 

insert into abc values(2,"abhay","aman","XII","B",410):

 

#USE OF   *  as ALL

To show the full table

Select* from abc:

 

#USE OF SELECT 

To show any selected of coulmn

Select stdnm, marks from abc:

 

#USE OF UPDATE

To update any data in table

update abc set marks=390 where slno=2:

 

#USE OF PRIMARY KEY

To set a primary key

create table abc(slno int primary key, stdnm varchar (30), fathernm varchar(30), class char(3),sec char(3), marks int):

 

#USE OF UNIQUE

Create table pqr(roll_no int primary key,reg_no. char(8) unique.....){can be used more than one}

 

#USE OF OPERATER(<,>,=,+,-,*,/)

Use of operaters

Select * from abc where marks>350:

 

#USE OF ALTER

To Change structure of table

Alter table abc add (admission_no char(12)):

 

#USE OF LIKE

To get a row of specific type

Select * from abc where stdnm like '_%':

 

#USE OF ORDER BY

To arrange data in order

select * from abc order by marks: {desc=descending/ by default ascending}

 

#SUM FUNCTION

To sum up integer

Select sum(marks) from abc:

 

#AVERAGE FUNCTION

To find average of integer

Select avg(marks) from abc:

 

#MIN FUNCTION

To find minimum

Select min(marks) from abc:

 

#MAX FUNCTION

To find maximum

Select max(marks) from abc:

 

#USE OF COUNT

To count the number of data

Select count(*) from abc:

 

#USE OF DROP

To delete any coulmn

alter table abc drop fathernm: (column name)

 

#USE OF ALTER

To make changes in coulmn

alter table abc modify class=std char(3):

 

#USE OF FOREIGN KEY

To connect two tables

1) create table abc (roll_no int primary key, stdnm varchar(30), fathernm varchar(30), std char(3), sec char(2), marks char(3)):

 

2) create table pqr (roll_no int, Adhar_no char(16), Address, foreign key(roll_no) references abc(roll_no)):

 

·    select roll_no, stdnm, Adhar_no, std, marks, address from abc, pqr where abc.roll_no=pqr.roll_no:

 

#DISTINCT COMMAND

To show common data once

Select distinct (marks) from abc:

 

--------------------------------------------------

MY SQL TERMS:-

Tuple ---- row record

Attribute ---- column record

Degree ---- no. of column

Cardinality ---- no. of rows

Relation ---- table

--------------------------------------------------

 

 

 

_______________________________

DDL: Data Definition Language

             eg:- create, alter, drop

            

DML: Data Manipulation Language

             eg:- insert, update, delete, etc.

 

Difference between primary key and unique key

Primary 

•can be used once.

• helps in use of primary key. 

 

 Unique

• can be used more than once

•it allows to enter null value

Click here for pdf Notes