CREATE
TABLE student(ID INT PRIMARY KEY ,NAME
TEXT,SALARY REAL CHECK(SALARY > 0));
1. //Creating a Database
Create
database college;
2. //Changing a Database
Use
college;
3.//using where condition for selecting a particular column
Select
name from student where id=’2’;
4.//showing all tables in a particular database
Show
tables;
5.//adding a column
to an exciting table;
Alter
table student add address varchar(255);
6.//dropping a table
Drop
table student;
7.//IS NULL attribute
Select
* from Student where class IS NULL;
8.//concatenation of two column in a tables(CONCAT)
Select concat(name,’-‘,class) as ‘product_desc’ from
student;
9.//COUNT(*) function for counting the number of rows in
table
Select
count(*) as ‘count’ from student;
10.//MAX(column_name) function for selecting the max value
of a particular column
Select
MAX(rollno) from student;
11.//MIN(column_name) function for selecting the minimum
value of a particular column
Select
MIN(rollno) from student;
12.//AVG(column_name) function for selecting the
average value of a particular column
Select
AVG(rollno) from student;
13.//SUM(column_name) function for selecting the SUM of a
particular column
Select SUM(rollno)
from student;
14.//dropping a particular column of a table
Alter table student drop class;
15.//AUTO_INCREMENT (must be defined for key values only)
Alter
table student add pass_no int key auto_increment;
16.//joining data of two tables
Select
student.id,student.name,student9.rollno from student,student9 where
student.name=student9.name;
17.//joining of two tables using LEFT JOIN
Select student.id,student.name,student9.rollno
from student LEFT JOIN student9 on student.name=student9.name;
18.//joining of two tables using RIGHT JOIN
Select
student.id,student.name,student9.rollno from student RIGHT JOIN student9 on student.name=student9.name;
19.//describing the complete table;
Describe
student;
20.//updating a column of a table using MODIFY
Alter
table student modify column name char(20);
21.//renaming a column
Alter
table student rename as all_student;
22.//use of HAVING
Select student.id,student.name,student9.rollno
from student RIGHT JOIN student9 on
student.name=student9.name group by name having count(student.rollno)>5;
23.//use of UCASE() for converting a column from lowercase
to uppercase
Select ucase(name) from student;
24.//use of LCASE() for converting a column from uppercase
to lowercase
Select Lcase(name) from student;
25.//selecting
substrings from a table column using SUBSTRING
Select substring(name,1,3) from
student;
1
defines start of substring
2
defines it’s length
26.//foreign key
Let us consider that ‘id’ is the
primary key of student table and it is referenced to a second table called
‘account’
Create table account(account_no int
not null,account_id int primary
Key, id int foreign key references
student(id));
27.//Procedures in
mysql
Procedures in mysql are
sequence of a group of statements, that are defined together for later use. In
simple words procedures in mysql are almost similar to functions of c++.which
can be called from anywhere in a program whenever they required. Below shown a
simple example :
Delimiter
//
Create
procedure mynameis( s VARCHAR(255))
Begin
Set
@x=s;
End
//
Using this procedure
Delimiter ;
Call
mynameis(‘smile Mittal’);
Select
@x;
This will display the value stored in x in the form table. Here
delimiters are used for separating the
executable statement followed by ‘;’ to define a group of statements.
28.//dropping a
procedure
Drop
procedure mynameis;
29.//creating function in mysql
Create
function mydata(s char(20))
Returns
char(50) deterministic
Return concat(‘amandeep’,’ ‘, s);
Calling this function
Select mydata(‘singh’);
30.//Dropping a function
Drop
function mydata(‘singh’);
31.//showing list of all tables in a database
Show tables;
32.//showing all tables with full information
Show
full tables;
Views in MySql
A view is a virtual table,
which looks like a table .views can restrict a user to specific rows or column
and hence it will result in providing security. These can be used to provide
aggregate of multiple tables or multiple columns from multiple tables. Views
are not the part of physical schema. Views provide security in such a way that, if we want only a few no of column
accessible to a user ,but remaining are not, then we declare these columns as a part of
view.
33.//creating a view for a single table
Create view
student_view as select name, rollno from student where rollno=646;
34.//creating views using multiple UNION oprerator
create view my_view as select * from student
where id=3
union
select * from student where name='aman'
union
select * from Student where rollno=646;
35.//showing the created view
Select
* from my_view;
Index in mysql
Use of indexes in mysql
improves the performance for searching in database. For example if we have a
table student, which consists of a no. of fields like name,class,rollno,id. If
we declare index for field ‘class’, then it will become easier to search for
student based on their class. As compared to searching the whole table, if
index is not defined.
Practically index can be considered
as a type of tables, which keeps primary key or index field and a pointer to
each record in the actual table.
INDEX speed up SELECT but speed
down INSERT. But speed up effect is greater than speed down. Delicacy is not
allowed while we define index for a particular column.
36.//use of UNIQUE INDEX
Create
unique index my_name on student (name);
37.//displaying index information
Show
index from student;
38.//use of SIMPLE INDEX
Create
index my_data on student(name);
//delicacy is allowed in simpleindex
39.//ALTER command to add and drop UNIQUE index
Alter
table student ADD UNIQUE my_name(student_roll);
40.//ALTER command to add and drop SIMPLE INDEX
Alter table student ADD INDEX my_name(student_roll);
41.//adding FULLTEXT Index
Alter
table student ADD FULLTEXT my_name(name);
TEMPORARY TABLES
Temporary tables are used to
store temporary data. Temporary tables would got destroyed if present session
for a user is destroyed. Temporary tables remains alive till the user’s session
is alive.
42.//creating temporary tables
Create
temporary table allstudent(id int,name varchar(255));
Insert
into allstudent values (1,’smile mittal’);
Select * from
allstudent;
When SHOW TABLES command is used
than ,it will not display the temporary tables in the shown list of tables. And
if the current mysql session is closed then SELECT command for will not display
any data in this temporary tables.
43.// getting information for complete table structure.
Show
create table student;
This will display all the
information related to the table student. This
information will include the following :
UNIQUE KEY,PRIMARY KEY,FULLTEXT
KEY,DAFAULT CHARSET.
TRIGGERS IN MYSQL
A
trigger is a set of code that is being executed is response to an event. It is
of two type BEFORE and AFTER trigger. Triggers can only be associated with real
tables not with temporary or views. Triggers provide interactivity to the
tables ,in response to a particular event. These also provide the facility of
linking of various operations like INSERT,DELETE,UPDATE together to be executed
on separate tables at occurrence of an particular event.
Below
is an example shown which gives you a simple understanding of operation of
triggers. In this example we have taken two table named as STUD_TRIGGER1 and
STUD_TRIGGER2,having columns COL1_TAB1 and COL1_TAB2 respectivily.
44.//Creating a TRIGGER named my_trigger
Delimiter
//
Create
trigger my_trigger before insert on stud_trigger1
For
each row begin
Insert
into stud_trigger2 set col1_tab2=new.col1_tab1;
End;
//
45.// using TRIGGERS ,following statement will insert data
in table STUD_TRIGGER1,and hence as defined in the MY_TRIGGER statements, this
will insert the same data in STUD_TRIGGER2 table.
Insert
into stud_trigger1
values(1),(3),(1),(7),(6);
Select
* from stud_trigger1; //this will
display the content of stud_trigger1 table.
Select * from stud_trigger2; //This will display the same content as
per statement defined in MY_TRIGGER
.
Cursors in MYSQL
MySQl cursor is a read only (means
you can not update table, for which cursor is created),non scrollable(means you
can not change the order of fetching, which is being defined in the select
statement).and in addition to these cursor is asensitive (means it works on
actual data). Cursors are used when more than one rows are expected to be
retrieved. Cursors must be declared and its definition contains a query.
Cursors are well supported inside procedures and functions. It must be opened
before processing and closed after processing. A number of cursors can be
inside a procedure and function ,but should be of different names.
46.//Defining cursor
in procedure
delimiter
//
create procedure my_cursor3()
begin
declare d int default 0;
declare id,rollno,student_roll int;
declare name,class varchar(255);
declare curs cursor for select name
from student
declare continue handler for not
found set d=1;
open curs;
lbl:loop
if d=1 then
leave lbl;
end if;
if not d=1 then
fetch curs into id;
insert into account values(id);
end if;
end loop;
close curs;
end;
//
Query OK, 0 rows affected (0.00
sec)
Using the defined cursor
delimiter ;
call my_cursor3();
Query OK, 1 row affected (0.01 sec)
Showing data of selected table
select * from account;
