mysql introduction · mysql introduction by prof. b.a.khivsara note: the material to prepare this...
TRANSCRIPT
![Page 1: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/1.jpg)
MySQL Introduction
By Prof. B.A.Khivsara
Note: The material to prepare this presentation has been taken from internet and are generated only for students reference and not for commercial use.
![Page 2: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/2.jpg)
Outline
Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as
• Table,
• View,
• Index,
• Sequence,
• Synonym
![Page 3: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/3.jpg)
Table
RollNo Name Address
1 Bhavana Nashik
2 Jiya Pune
Table Name: Student
![Page 4: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/4.jpg)
View
RollNo Name
1 Bhavana
2 Jiya
View Name: St_View created on table Students
View is a logical table. It is a physical object which stores data logically. View just refers to data that is tored in base tables.
A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.
![Page 5: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/5.jpg)
Index
Database index, or just index, helps speed up the retrieval of data from tables. When you query data from a table, first MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the whole table.
![Page 6: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/6.jpg)
Index of Book- an Example
![Page 7: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/7.jpg)
Sequence
Bill No Date Particulars Amount
101 2/3/2017 Mixer 2000
102 2/3/2017 T.V. 4000
103 3/3/2017 Speaker 1600
Sequence
you can create a column that contains a sequence of numbers (1, 2, 3, and so on) by using the
AUTO_INCREMENT attribute.
![Page 8: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/8.jpg)
Synonym • A synonym is an alternative name for objects such as tables,
views, sequences, stored procedures, and other database objects.
• Eg. Nickname or short name of any person
• Note: Synonyms are not possible in MySQL but possible with oracle.
![Page 9: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/9.jpg)
Commands Types
• Data Definition Language DDL
• Data Manipulation Language DML
• Data Control Language DCL
• Transaction Control Language TCL
![Page 10: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/10.jpg)
Commands Types
![Page 11: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/11.jpg)
DDL- Data Definition Language
• DDL statements or commands are used to define and modify the database structure of your tables or schema.
Some commands of DDL are:
• CREATE – to create table (objects) in the database
• ALTER – alters the structure of the database
• DROP – delete table from the database
• TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
• COMMENT – add comments to the data dictionary
• RENAME – rename a table
![Page 12: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/12.jpg)
DDL Commands for Table
•Create table
•Show tables
•Describe table
•Alter table
•Truncate table
•Drop table
•Rename
![Page 13: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/13.jpg)
DDL Commands for Table
•Create table • Syntax Create table table_name (Col_name1
data_type(Size) , Col_name2 data_type(Size),…))
• Example Create table Stud(rno number(4),name
varchar2(20), DOB date));
![Page 14: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/14.jpg)
DDL Commands for Table
•Describe table
•Syntax
desc table_name;
•Example
desc Stud;
![Page 15: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/15.jpg)
DDL Commands for Table
•Show tables This will list the tables in the current database:
• Syntax
show tables;
• Example
mysql> show tables;
customers
orders
2 rows in set (0.00 sec)
![Page 16: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/16.jpg)
DDL Commands for Table
•Alter table (add or modify)
• Syntax
alter table table_name add/modify/drop
column_name data_type(size)
• Example
• Alter table stud add age int(3);
• Alter table stud modify name varchar2(30);
• Alter table stud modify name char(20);
• Alter table stud drop column age;
![Page 17: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/17.jpg)
DDL Commands for Table
• Truncate Table used to delete complete data from an existing table.
• Syntax • TRUNCATE TABLE table_name;
• Example mysql> TRUNCATE TABLE stud;
![Page 18: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/18.jpg)
DDL Commands for Table
•Rename Table used to rename table.
• Syntax • RENAME TABLE tbl_name TO new_tbl_name
• Example mysql> RENAME TABLE stud TO student ;
![Page 19: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/19.jpg)
Constraints
• Constraints are the set of rules defined on tables to ensure data integrity.
• Primary key
• Not null
• Default
• Unique
• Check
• Foreign key/reference key
![Page 20: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/20.jpg)
Constraints- Unique
• The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column.
• Syntax CREATE TABLE table_name (col_name data_type (size) Unique);
• Example • CREATE TABLE Stud (Rno number(4) Unique);
![Page 21: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/21.jpg)
Constraints- Not Null
• A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL.
• Syntax CREATE TABLE table_name
(Col_name Data_type(size)not null, ... );
• Example Create table stud
(rollno number(4) ,name varchar2(20)not null);
![Page 22: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/22.jpg)
Constraints- Primary key
• Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table. This column or set of columns is referred to as the primary key.
• A table can have only one primary key.
• Multiple columns can be clubbed under a composite primary key.
• Primary key columns is combination of NOT NULL and UNIQUE.
• Syntax
CREATE TABLE table_name ( Col_name Data_type(size)CONSTRAINT constraint_name PRIMARY KEY, ... );
• Example
• Create table stud (rollno number(4)constraint pk1 primary key,name…)
• Create table stud (rollno number(4) primary key, name ….)
![Page 23: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/23.jpg)
Constraints- Default
• In a MySQL table, while inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.
• Syntax • CREATE TABLE table_name (col_name
data_type(size) DEFAULT ‘default_value’ );
• Example • CREATE TABLE Stud (rno number(4) ,name varchar2(20), addr
varchar(30) DEFAULT ‘Chandwad’ );
![Page 24: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/24.jpg)
Constraints- Check
• In a MySQL table, A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not.
• Syntax • CREATE TABLE table_name (col_name data_type(size) Check
(condition) );
• Example • CREATE TABLE Stud (rno number(4) CHECK (rollno BETWEEN 1
AND 60));
• CREATE TABLE Stud (age number(4) CHECK (age>18));
![Page 25: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/25.jpg)
Constraints- FOREIGN KEY
• A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
• Syntax • Create table table_name(col_name data_type(size)references
table_name(col_name));
• Example • Createtable stud1 (rollno number(4) references stud(rno));
![Page 26: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/26.jpg)
Constraints- after table creation and drop constraint
• You can also add constraint after table creation using alter table option
• Example • Alter table stud add constraint prk1 primary key(rollno);
• You can also drop constraint using Drop command & name of constraint
• Example • Drop constraint prk1;
![Page 27: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/27.jpg)
DDL Commands for Table
•Drop table
•Syntax
Drop table table_name;
•Example
Drop table stud;
![Page 28: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/28.jpg)
DDL Commands for Index
•Create Index
•Show Index
•Drop Index
![Page 29: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/29.jpg)
DDL Commands for Index
•Create Index •Syntax 1> Create Index index_name on
table_name(column_name)
2> Alter table table_name add index index_name (column_name)
•Example
1> Create Index n1 on Stud(Name)
2> Alter table Stud add Index n1 (name)
![Page 30: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/30.jpg)
DDL Commands for Index
•Show Index • Syntax
Show Index from table_name
• Example
Show Index from Stud;
![Page 31: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/31.jpg)
DDL Commands for Index
•Drop Index
•Syntax
Alter table table_name drop Index index_name
•Example
• Alter table Stud drop Index n1;
![Page 32: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/32.jpg)
DDL Commands for View
•Create View
•Show View
•Drop View
![Page 33: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/33.jpg)
DDL Commands for View
•Create View • Syntax
Create View view_name as select col_name1,col_name2 from table_name [where <condition>]
• Example 1>Create view v1 as select name from stud;
2>Create view v2 as select name from stud where addr=‘Nashik’;
![Page 34: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/34.jpg)
DDL Commands for View
•Show View
•Syntax
Select col_name1,.. from View_name
[where condition]
•Example
Select * from v1;
![Page 35: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/35.jpg)
DDL Commands for View
•Drop View
•Syntax
Drop View view_name
•Example
Drop View v1;
![Page 36: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/36.jpg)
Sequence- Using AUTO_INCREMENT Column
AUTO_INCREMENT
Syntax • CREATE TABLE table_name ( column1 datatype NOT NULL
AUTO_INCREMENT,….);
• ALTER TABLE table_name AUTO_INCREMENT = start_value;
Example • CREATE TABLE Bills ( Bill_No INT(11) NOT NULL
AUTO_INCREMENT, name varchar2(20));
• ALTER TABLE Bills AUTO_INCREMENT = 1001;
![Page 37: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/37.jpg)
Assignment
• Create one table employee with fields • Eno –primary key and apply sequence starts with 101 • Ename –not null • Address –default ‘Nashik’ • Joindate • Post • Salary –check > 5000
• Create another table emp_proj with fields • Eno- forign key • Project_name • Loc
• Create Index on Ename field of employee table
• Create View on employee table to show only Employee name, it’s
post and salary.
![Page 38: MySQL Introduction · MySQL Introduction By Prof. B.A.Khivsara Note: The material to prepare this presentation has been taken from internet and are ... It is a SQL statement stored](https://reader034.vdocuments.us/reader034/viewer/2022042806/5f6e3cb49e709667005bc3dc/html5/thumbnails/38.jpg)
References
• https://www.nextstep4it.com/ddl-statements-in-mysql/
• http://www.w3schools.in/mysql/ddl-dml-dcl/
• https://www.tutorialspoint.com/mysql/