MySQL
SQL --- Structured Query Language
What is a DATABASE ??
A database is an organized collection of data, generally stored and
accessed electronically from a computer system.
What is a DBMS ??
Database management system is an application that ineracts with the
user ,other application and the database itself to capture and analyse data.
It is designed to allow the definition ,creation, querying, update, and
administration of database.
RELATIONAL DATABASE MANAGEMENT SYSTEM
Data is stored in the form of rows and column in RDBMS
The relation among the tables are also stored in the form of the table.
What is MySQL ?
MySQL is a fast ,easy to use RDBMS being used for used for many small
and big business.
SQL statements
DDL Data definition language
DML Data manipulation language
DCL Data control Language
TCL Transaction control language
DDL DML DCL TCL
create select grant commit
alter insert revoke rollback
drop update savepoint
truncate delete set
comment merge transaction
rename call
Create
Database
Create database <database_name>
Table
Create table <table_name> (<table_argument> <datatype> list)
Example
Create table IF NOT EXISTS products( productid int,productname
varchar(30) ,productprice int )
To create table with primary key and autocrement and default
variables
Create table IF NOT EXISTS products( productid int
AUTO_INCREMENT, productname varchar(30) DEFAULT=” ” ,
productprice int, primary key(productid) )
Insert
insert into <table_name> values(values)
Insert into products values(1001,”keyboard”,2450)
Insert into product (productname,productprice) values (“chair”,900)
Update
Update <table_name> set <column_name> =value
Update product set productid=1000 where productname=”violin”
Delete
Delete from <table_name> where condition
Truncate table <tablename>
Select
select * from <tablename>
Where
select * from <tablename> where condition
conditions : And, Or, between, like, in, is null
Limit
Select * from table limit offset,count
Order
select * from <tablename> order by <columnname> asc/dsc
Create table using like operator
Create table <new_tablename> like <old_tablename> @without copying
the rows
Create table <new_tablename> select * from <old_tablename> @by
copying the rows
Create table <new_tablename> select productname ,productprice from
product @copying the specified columns
Create table <newtablename> select * from <oldtablename> limit 0 @with
all columns and no rows will be copied
Alter
Adding columns
Alter table <tablename> add <newcolumn> <columndefinition>
FIRST | AFTER <columnname>
Adding multiple column
Alter table <tablename>
add <newcolumn> <columndefinition> FIRST | AFTER
<columnname>
add <newcolumn> <columndefinition> FIRST | AFTER
<columnname>
modify column in table
Alter table <tablename>
Modify <newcolumn> <columndefinition> FIRST | AFTER
<columnname>
modify multiple column in table
Alter table <tablename>
Modify <newcolumn> <columndefinition> FIRST | AFTER
<columnname>
Modify <newcolumn> <columndefinition> FIRST | AFTER
<columnname>
Drop column in a table
Alter table <tablename> drop column <columnname>