MySQL Introduction PDF

1 040103205 - Network and Internet Programming MySQL 1 What is SQL? SQL stands for Structured Query Language. SQL is a standard database sublanguage for database systems. MySQL is a relational database management system (RDBMS) developed b...

1 040103205 - Network and Internet Programming MySQL 1 What is SQL? SQL stands for Structured Query Language. SQL is a standard database sublanguage for database systems. MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). 2 The Insert statement The Insert statement is used to insert one row (one record) into an existing table. The general form of the Insert statement is: Insert Into TableName (col1, col2, col3¸… ) Values (value1, value2, value3, ….) ; Exercise 1 Consider the following table Students: Id Major Minor 12 CS 87 Stat CS 46 Math CS Write SQL statements that inserts the following two records into the table: Assume that the data type of the field Book_Id is Int  2 3 The Update statement The UPDATE statement is used to update existing record(s) in a table. The general form of the Update statement is as follows: UPDATE Table_Name SET column1=value1, column2=value2,... WHERE criteria ; Exercise 2 Consider the following table Users that exists in a database called Group: Id LastName FirstName Address City X1 Hansen Ola Timoteivn 10 Sandnes P2 Svendson Tove Borgvn 23 Sandnes Y7 Pettersen Kari Storgt 20 Stavanger F5 Nilsen Johan Bakken 2 Stavanger G8 Tjessem Jakob Timoteivn 50 Sandnes Assuming that Id is the primary key of the table, write SQL statement(s) that performs each the following: 1. Changes the value of FirstName to be 'Sam' in the fourth record. 2. Changes the value of Address to be 'Saint 22' in the first and third records. 3. Changes the value of City from 'Sandnes' to 'Paris' for all persons whose city is 'Sandnes'. 4 The Delete statement The Delete statement is used to delete record(s) from an existing table. The Delete statement has two forms as shown below: Form Effect Delete From TableName Where criteria Deletes all records that satisfy the given criteria. Delete From TableName Deletes all records in the table (leaves the table empty) Exercise 3 Consider the Users table shown in Exercise 2; write SQL statement(s) that performs each the following: 1. Delete the last record 2. Delete all users whose city is ' Stavanger'  3 5 The Select statement The SELECT statement is used to select data from table. The result is stored in a result table, called the result-set. The general forms of the Select statement is: Select * From TableName Select * From TableName Where Criteria Select col1, col2, …. From TableName Select col1, col2, …. From TableName Where Criteria Exercise 4 Consider the following table Users that exists in a database called Group: Id FName LName Address City 1 H O T B 2 S Y B Q 3 P K W T 4 N J L Q 5 T V R A Find the output of each of the following SQL statements: 1. Select * From Group.Users; 2. Select * From Group.Users Where Id =3 OR Id = 5; 3. Select LName, FName From Group.Users; 4. Select Id, FName, Address From Group.Users Where City = 'Q';