Web Programming - MYSQL

1. Web Programming - MYSQL Association of Computing Activities Computer Science and Engineering Indian Institute of Technology Kanpur

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

3. 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

4. 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

5. Creating using sql • Create database name; • Create table name_of_table{ column1 data_type1; column1 data_type1; }

6. Connecting to db • Written in php script(server should be running) • mysql_connect(server,username,password) (=$con) • mysql_select_db(name,connection)

7. syntax for sql • $query = “my query goes here”; • $result = mysql_query($query,$con);

8. Insertion • INSERT 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)

9. 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”;

10. 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

11. 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 colum1,column2,…………. From table_name WHERE name op v1 AND/OR name2 op2 v2

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