Date:29/Dec/2022
----------------
SQL Concepts / Database Concepts
--------------------------------
- SQL stands for Structured Query Language
- SQL is set of commands which are used to operate a database
- A database is a collection of related tables
- A table is a collection of data in the form of rows and columns
Ex:
Books table
---------------------
bno bname price } columns or fields
---------------------
111 Java 500 } row or record
222 XML 800 } row or record
333 HTML 400 } row or record
---------------------
SQL Command Types
-----------------
- DDL - Data Definition Language - create, drop, alter
- DML - Data Manipulation Language - insert, update, delete
- DQL - Data Query Language - select
- TCL - Transaction Control Language - commit, rollback
Database Servers
----------------
- MySQL (Oracle Corp)
- Oracle (Oracle Corp)
- DB/2 (IBM)
- SQL Server (Microsoft)
- Sybase (Microsoft)
Download and Install MySQL Database Server v8
---------------------------------------------
Refer document Download and Install MySQL 8 version.pdf
port number : 3306
username : root
password : root
Errors during Installation
--------------------------
Need to install Microsoft Visual C++ Redistributable
-----------------------------------------------------
Google: Microsoft Visual C++ Redistributable for Visual Studio 2019
Youtube link
------------
https://www.youtube.com/watch?v=m9UKmUK9BbA
Download and Install MySQL Server 8 on macOS
---------------------------------------------
https://www.youtube.com/watch?v=-BDbOOY9jsc
Creating a database
-------------------
syntax
------
mysql>create database database-name;
Ex:
mysql>create database java16;
Use/Change Database
-------------------
mysql>use java16;
Drop database
-------------
used to delete the database
mysql>drop database java16;
Datatypes
---------
- int
- float
- char - used to store fixed size data Ex: empids, rollnos, itemnos etc
- varchar - used to store variable size data Ex: empnames, studnames, itemnames etc
- date
create table command
--------------------
used to create table with the specified columns
syntax
------
create table table-name (column-1 datatype(size),
column-2 datatype(size),
....
column-n datatype(size));
Ex:
create table books (bno int(3), bname varchar(10), price float(4));
desc command
-------------
used to display the structure of the table
Ex:
desc books;
drop table command
------------------
used to delete the table
syntax
------
drop table table-name;
EX:
drop table books;
Insert command
--------------
used to insert the records into the table
syntax
------
insert into table-name values (value1,value2,...,valuen);
- the number of values should be equal to the number of columns of table
insert into table-name (column-1,column-2,..,column-n) values (value1,value2,..,valuen);
- the number of values should be equal to the number of columns given in the command
Ex:
insert into books values (111,'java',500);
insert into books (bno,bname) values (222,'xml');
update command
--------------
used to modify/update the existing records
syntax
-------
update table-name set column=new-value,column=new-value,...column=new-value [where condition];
[] => optional
Ex:
update books set price=600;//for all records price will be updated to 600
update books set price=800 where bno=222;
TCL Commands
------------
commit
------
used to save the transactions (insert, update, delete)
Ex:
mysql>commit;
rollback
--------
used to undo the uncommitted transactions
Ex:
mysql>rollback;
Note
----
In MySQL, by default the transactions are autocommitted
Setting autocommit mode to false/off
-------------------------------------
mysql>set autocommit = 0;
Setting autocommit mode to true/on
-------------------------------------
mysql>set autocommit = 1;
Delete command
---------------
used to delete the records of the table
syntax
-------
delete from table-name [where condition];
Ex:
delete from books;//all records will be deleted
delete from books where bno=222;
delete from books where price>=500 and price<=700;
select command
--------------
used to retrieve the records of the table
Ex:
select * from books;//* indicates all columns of table
select bno,bname from books;
select * from books where bno=222;
select * from books where price>=500 and price<=700;
Alter table command
-------------------
used to modify the structure of the table like the following
- rename the table
- add new columns
- modify existing columns
- rename columns
- drop columns
mysql>alter table books rename to tempbooks;
mysql>alter table tempbooks rename to books;
mysql>alter table books add author varchar(10);
mysql>alter table books modify bname varchar(15);
mysql>alter table books change bname bookname varchar(15);
mysql>alter table books drop column author;
