Auto Increment.
This is a function that is operated on numeric data types.
*This automatically generates sequential numeric values every time that a record is inserted into a table for a field defined as auto increment.
Command:
CREATE TABLE STUDENT
(sno int(5) primary_key AUTO_INCREMENT, //this should have unique and not null values.
sname varchar(),
marks int(5));
ALTER TABLE student AUTO_INCREMENT=100;
Insert into student(sname,marks) values (‘x’,60);
Insert into student(sname,marks) values (‘y’,40);
Insert into student(sname,marks) values (‘z,105); //We have not mentiones sno value anywhere in columns.
Select * from student;// When we execute this the sno value will be displayed in the output with an increment.
Output:
sna sname marks
1 x 60
2 y 40
3 z 105
Delete from student WHERE sno=2;//record will be deleted we have only 2 records
Select * from student; // the deleted record will be deleted and a record with new number will be displayed.
Output:
sno sname marks
1 x 60
3 z 105
4 y 45 //next number is generated when old reocrd 2 is deleted.
Note:
We need to use primary key in that column when ever AUTO_INCREMENT is used.
*Bi default the sno would start from 1 and the increment is done.
*If we want to give a diff number then we can give the command as
Command :
SET_SAFE_UPDATES=0;
Delete from student; //Empty records
CREATE TABLE STUDENT
(sno int(5) primary_key AUTO_INCREMENT, //this should have unique and not null values.
sname varchar(),
marks int(5));
ALTER TABLE student AUTO_INCREMENT=100;
Insert into student(sname,marks) values (‘x’,60);
Insert into student(sname,marks) values (‘y’,40);
Insert into student(sname,marks) values (‘z,105);
Select * from student
Output :
sno sname marks
100 x 60
102 y 45
103 z 105 //next number is generated when old reocrd 2 is deleted.
This is not a constriction, it is a class to increase values.
LIMIT CLASS
This is used to display limited rows from a table.
Command:
Select * from employees limit 10;
Select * from employees limit 5,10;//5th row to 10th row records
When we retrieve data from table we get the complete data bi default.
If we want to view only few records like first 5 or 10 then we can execute the above commands.
Note: There is an option in MySQL Workbench however we can do this through command as well.
VIEWS:
A view is a virtual table based on a result set of an SQL statement.
*It contains rows and columns just like a real tables.
*The fields in a view are from one or more real tables in the database.
*Also, we can add SQL functions WHERE AND JOIN statements to a view and present the data as if the data were coming from one single table.
*View is a logical structure of a table which have a logical data and this is a mirror image of a table.
*This is used to maintain security of the data in the tables.
Note:
If we make any changes to the table, the changes will be made on view as well, but the changes made in view will not effect anything in the table.
Example:
Let us consider a table with 3 columns and multiple rows.
If we perform some action on the data in the table there will be some results.
We will store the result in a view and provide the access of the view to the user.
*Now, instead of providing the complete access of the table to the user we can create a view and provide the access of the view so that any changes made on the view will not effect the actual data in the table.
Example:
use hr;
SELECT * from EMPLOYEES;
Creating a view:
Syntax:
CREATE VIEW viewname AS SQL query. //what ever records the SQL query returns those will be present in the view.
Command:
CREATE VIEW employees_V1 AS SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES; //in the employees table views is created with view name.
SELECT * from employees_v1; //data from the view will be displayed.
Dropping view:
Syntax:
DROP VIEW viewname
command:
DROP VIEW WMPLOYEE_V1;
INDEX:
These are used to retrieve data from the database very fast.
Users cannot see the indexes they ate just used to speed up searches/queries.
Insex are usually created on the columns with huge and complex data.
creating index
Syntax:
CREATE INDEX name of the index On table name( columnname ); //when we refresh the databases under tables and under particulate table the index will be created with the specified name.
CREATE INDEX idx_employees ON Employess(First_Name);
Dropping Index:
Syntax:
DROP INDEX INDEXNAME ON TABLENAME
drop index index_employees on EMPLOYEES;
That is all about final part 11.
Thank you for referring the entire article series.
