Types of SQL Commands:
=======================
5 types of sql commands
1) DDL
create, alter, drop, truncate, rename
2) DML
insert, update, delete
3) DCL
grant, revoke
4) TCL
commit, rollback, savepoint
5) DQL
select
1) DDL (Data Definition Language):
----------------------------------
DDL changing the structure of the table : create a table, alter a table, delete a table, drop table, rename
- all DDL commands a committed automatically once their TX completes.
create: Create a new table or create a new database
alter: altering command used for adding new column, change size, datatype
drop: drop table
truncate : removes/deletes the data from table
rename: rename a table
- Create a database:
------------------
- Creating a database using create DDL command
Syntax:
create database [databaseName];
Ex:
Create database EnggcollegeJJM;
1)If not exists:
create database if not exists jjm;
if exists:
- create: Create a new table or create a new database
-------------------------------
syntax:
create table tableName(columnName1 Datatype,columnName2 Datatype,......);
create table if not exists tableName(columnName1 Datatype ,columnName2 Datatype,......);
create table tableName(columnName1 Datatype [attributes],columnName2 Datatype,......);
create table student(id int, name varchar(25), email varchar(25),dob date);
create table if not exists student(id int, name varchar(25), email varchar(25),dob date);
Create a table with all datatypes supported by mysql:
----------------------------------------------------
create table employee(eid int autoincrement,name varchar(20), gender varchar(6),dob date, salary float(10,2));
create table dummy(vc varchar(20), tInt tinyint, textual text, dob date, sInt smallInt, mInt Medium
Int, bInt bigInt, salary float(6,2), proValue double(10,3),dt datetime,time12 time, gender char(1), photog
raph blob);
Create a new table using existing table:(copy table struture)
create table newStudent like student;
alter: altering command used for adding new column, change size, datatype
-------------------------------------------------
syntax:
alter table tableName add columnName datatype;
3) truncate : delete the rows/data from the table
-------------------------------------------------
truncate table tablename;
truncate table newStudent;
4) drop: drop table
-------------------
drops the table defintion as well as contents also.
drop table tablename;
drop table newStudent;
5) rename: rename a table
--------------------------
rename table oldTableName to newTableName;
2) DML (Data Manipulation Langugage):
-------------------------------------
- manipulating data stored in a table
- dml are not auto saved/autocommited, dont saved permanantly,
even programmer can rollback also
insert : add new records/rows to the table
update : updating existing records of the table
delete : remove/deleting specific/all the rows from the table;
insert : add new records/rows to the table
------------------------------------------
insert into tablename(columnames,.......) values(val1,.....);
Or
insert into tablename values(val1,.....); need to provide all column values compulsory
Rules:
Date : yyyy-mm-dd : '2019-08-30'
text,string : single quotes : 'ganesh shankar lord'
numbers : without single quotes
5 сен 2024
