Web Programming - MYSQL

Association of Computing Activities Computer Science and Engineering

Indian Institute of Technology Kanpur

Outline of Lecture•Introduction•Creating•Connecting •Insertion•Updating•Deletion•Querying

IntroductionDatabase is a organized collection of data(in

tables)RDBMS – relationship among the data in

tableMY structured query languageRDBMS providing multi user access to db’sPopular because of scalability, flexibility, high

availability, robust transactional support, protection, app development, low cost

Facebook, Google, adobe and a lot more

Creation using xamppGo to databasesCollation as UTF-8Click on the database and create tables with

columns requiredEnter the name, type, length Collation, attributes, nullIndex, auto incrementStorage engine , collation

Creating using sqlCreate database name;

Create table name_of_table{ column1 data_type1; column1 data_type1; }

Connecting to dbWritten in php script(server should be

running)

mysql_connect(server,username,password) (=$con)

mysql_select_db(name,connection)

syntax for sql

$query = “my query goes here”;

$result = mysql_query($query,$con);

InsertionINSERT into name_of_table VALUES

(value_for_column1,value_for_column2,……..)

INSERT into name_of_table (column1,column4,column7) VALUES (value_for_column1,value_for_column4,value_for_column7)

Updation

Update, set, where

UPDATE name_of_table set column1=“some” where

coulmnk = “this”

UPDATE name_of_table set column1=“some” ,column2=“some1” where

coulmnk = “this” AND columnp=“here”;

Deletion

Delete ,from ,WHEREDELETE FROM name_of_table where

name_of_column=“value”;Delete a particular rowTo delete all rows use(make table empty)DELETE from table_name or DELETE * from

table_name

QueryingSELECT, FROM, WHERE

SELECT name_of_column from name_of_table WHERE name_of_other_column operator value

* instead of name_of_column

SELECT colum1,column2,…………. From table_name

WHERE name op v1 AND/OR name2 op2 v2

Built In functionsSELECT function FROM table_name;AVG()MAX()MIN()SUM()COUNT()DISTINct