Web Programming MYSQL Association of Computing Activities Computer

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

Introduction �Database is a organized collection of data(in tables) �RDBMS – relationship among the data in table �MY structured query language �RDBMS providing multi user access to db’s �Popular because of scalability, flexibility, high availability, robust transactional support, protection, app development, low cost �Facebook, Google, adobe and a lot more

Creation using xampp �Go to databases �Collation as UTF-8 �Click on the database and create tables with columns required �Enter the name, type, length �Collation, attributes, null �Index, auto increment �Storage engine , collation

Creating using sql �Create database name; �Create table name_of_table{ column 1 data_type 1; }

Connecting to db �Written 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);

Insertion �INSERT into name_of_table VALUES (value_for_column 1, value_for_column 2, ……. . ) �INSERT into name_of_table (column 1, column 4, column 7) VALUES (value_for_column 1, value_for_column 4, value_for_col umn 7)

Updation �Update, set, where �UPDATE name_of_table set column 1=“some” where coulmnk = “this” �UPDATE name_of_table set column 1=“some” , column 2=“some 1” where coulmnk = “this” AND columnp=“here”;

Deletion �Delete , from , WHERE �DELETE FROM name_of_table where name_of_column=“value”; �Delete a particular row �To delete all rows use(make table empty) �DELETE from table_name or DELETE * from table_name

Querying �SELECT, FROM, WHERE �SELECT name_of_column from name_of_table WHERE name_of_other_column operator value �* instead of name_of_column �SELECT colum 1, column 2, …………. From table_name WHERE name op v 1 AND/OR name 2 op 2 v 2

Built In functions �SELECT function FROM table_name; �AVG() �MAX() �MIN() �SUM() �COUNT() �DISTINct