**********Table Queries********

UPDATE TABLES:

 
TO UPDATE EXISTING ROWS

SYNTAX:

UPDATE table_name

SET col1=val1,col2=val2

WHERE condition;


UPDATE users3

SET  followers=500 

WHERE age=21;


















DELETE:

TO DELETE EXISTING ROWS

SYNTAX

DELETE from table_name
WHERE condition;


DELETE from users3
WHERE mails="123@gmail.com" OR followers=40;



ALTER

TO CHANGE THE SCHEMA

ADD COLUMN:

ALTER TABLE table_name
ADD COLUMN  column_name datatype constraint;


DROP COLUMN:

TO DELETE A COLUMN

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE users3
DROP COLUMN city;


RENAME TABLE:

ALTER TABLE table_name
RENAME TO new table_name;

ALTER TABLE users3
RENAME TO userinfo;
SHOW tables;


CHANGE COLUMN:

rename

ALTER TABLE table_name
CHANGE COLUMN old_name new_name new_datatype new_dataconstraint;

ALTER TABLE userinfo
CHANGE COLUMN followers subs INT DEFAULT 100;

SELECT * FROM userinfo;


MODIFY COLUMN

TO MODIFY DATATYPE OR CONSTRAINT

ALTER TABLE userinfo
MODIFY subs INT DEFAULT 200;

TRUNCATE



TRUNCATE userinfo;
SELECT * FROM userinfo;

Comments

Post a Comment

Popular posts from this blog

DATABASE RELATIONSHIPS

Image
 RELATIONSHIPS INCLUDE  ONE - TO -ONE (1x 1)   ONE ENTITY OF A TABLE IS IN RELATION WITH ONLY ONE ENTITY OF ANOTHER. ONE - TO -MANY (1x N)   ONE ENTITY IS IN RELATION WITH MANY MANY - TO - MANY(NxN)   MANY ENTITIES ARE IN RELATION WITH MANY OTHER. ONE- TO-MANY IS MORE HARDER TO BE REPRESENTED THAN ONE - TO - ONE AND MANY - TO - MANY ONE- TO -MANY IT IS CATEGORIZED INTO 3 BASED ON MANY MANY CAN BE IN 3 TYPES 1. ONE -TO-FEW:  SMALL NUMBER (2,5 ,100,500) STORE THE CHILD DOCUMENT INSIDE PARENT TAKE ZOMATO OR AMAZON FOR EXAMPLE, A SINGLE USER CAN HAVE MULTIPLE ADDRESSES. HERE WE DONOT CREATE TWO MODELS FOR USER AND ADDRESS, AS ADDRESS IS ASSOCIATED WITH USER DIRECTILY, WE STORE THE CHILD DOCUMENT INSIDE PARENT. const mongoose = require ( "mongoose" ); const { Schema } = mongoose ; main ()   . then (() => {     console . log ( "connection successful" );   })   . catch (( err ) => {     console . log ( err );...
Read more

ROUTING (GET /) home route

Image
  app . get ( "/" , ( req , res ) => {   let q = 'SELECT COUNT(*) FROM user' ;   try {   connection . query ( q , ( err , result ) => {     if ( err ) throw err ;     let count = result [ 0 ][ 'COUNT(*)' ];     console . log ( count );     res . render ( "home.ejs" ,{ count });   }); } catch ( err ) {   console . log ( err );   res . send ( "error occured" ); } }); app . listen ( port , () => {   console . log ( `listening to port ${ port } ` ); }); HOME.EJS <! DOCTYPE html > < html lang = "en" > < head >     < meta charset = "UTF-8" >     < meta name = "viewport" content = "width=device-width, initial-scale=1.0" >     < title > home </ title > </ head > < body >     < h2 > Total count of users: <%= count %> </ h2 > </ body > </ html >
Read more

Query Strings

Image
 req.query() - used to get the query string(searched) app . get ( "/search" ,( req , res ) => {   let { q } = req . query ;   // console.log(q);   if ( ! q ){     res . send ( "<h1>nothing searched</h1>" );   }   res . send ( `<h1>search results for ${ q } </h1>` ); });
Read more