IT Session 3 & 4 - MySQL Basics

Introduction to Web Applications – IT Session 4 and 5 7 Alternatively, you can click on the Query Window button and type those several insert commands into the editor. After typing all the INSERT statements above, press the button Go in your Query Window. Have a look at the output query, with highlighted colours. Verify that there is a message for each INSERT statement showing that 1 row was affected. There is another and easier way to populate your table. If you click on your table and click on the link Insert, on the frame on the right side of your session page, you can populate your table simply by filling in the text boxes under the Value column. Remember that you need to verify which fields are NULL or NOT NULL, which can be done by simply ticking the field under the column Null. 4.1.3 Deleting Table Content Deleting the rows of a table is very simple. In case you want to delete all the rows, just type the following: DELETE FROM petYOUR ASSIGNED NUMBER; Now, all the data in your table is deleted! But if you followed steps 1 to 8 on previous section, you execute the file Insert.sql following steps 5 to 8 from the previous section. Alternatively, you can populate your table again, by copying the content of the table pet, which was populated with the same values you inserted into your table. In order to do this, you just need to type the following command in your Query Window (but remember to replace petYOUR ASSIGNED NUMBER by your own table name): INSERT INTO petYOUR ASSIGNED NUMBER (select * from pet) Let’s now make a delete command with a particular criterion. For example, suppose you want to delete all "hamster" records: delete from petYOUR ASSIGNED NUMBER where species 'hamster';

Introduction to Web Applications – IT Session 4 and 5 8 4.1.4 Selecting Data from a Table And Updating The SELECT statement is used to pull information from a table. The general form of the statement is: SELECT what to select FROM which table WHERE conditions to satisfy; * what to select indicates what you want to see. This can be a list of columns, or to indicate “all columns”. which table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it’s present, conditions to satisfy specifies conditions that rows must satisfy to qualify for retrieval. Check it out in the MySQL documentation. Hint 2: use backslash ‘\’ to delimit special characters, for example: select * from table where field “c:\\Dir\\subDir\\File.ext” or insert into table value (‘Someone\’s belonging’), where the ‘s is the special character you want to insert into the table. 4.1.4.1 Selecting All Data and Updating Records Let’s then use the command SELECT to query all the data stored in your table: SELECT * FROM petYOUR ASSIGNED NUMBER; This form of SELECT is useful if you want to review your entire table, for example, after you’ve just loaded it with your initial dataset. For example, you may happen to think that the birth date for Puffball doesn’t seem quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989, not 1999. One way to do is to fix only the erroneous record with an UPDATE statement: UPDATE petYOUR ASSIGNED NUMBER SET birth '1989-03-30' WHERE name 'Puffball'; The UPDATE changes only the record in question and does not require you to reload the table. (Suggestion: Try to make multiple UPDATES, similarly to the INSERT step on page 6). 4.1.4.2 Selecting Particular Rows As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the SELECT statement. But typically you don’t want to see the entire table. Normally you have a particular question, in which case you specify some constraints on the information you want. Let’s look at some possible selection queries about your pets.

Introduction to Web Applications – IT Session 4 and 5 9 You can select only particular rows from your table. For example, if you want to verify the change that you made to Puffball’s birth date, select Puffball’s record like this: SELECT * FROM pet WHERE name 'Puffball'; And you will have the following result: The result confirms that the year is correctly recorded now as 1989, not 1999. String comparisons normally are case-insensitive, so you can specify the name as 'puffball', 'PUFFBALL', etc. The query result will be the same. You can specify conditions on any column, not just the name. For example, if you want to know which animals were born after 1998, test the birth column: SELECT * FROM pet where birth '1999-01-01'; You can combine conditions, for example, to locate female dogs: SELECT * FROM pet WHERE species 'dog' AND sex 'f'; The preceding query uses the AND logical operator. There is also an OR operator: SELECT * FROM pet WHERE species 'cat' OR species 'dog'; AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it’s a good idea to use parentheses to indicate explicitly how conditions should be grouped: SELECT * FROM pet WHERE (species 'cat' AND sex 'm');

Introduction to Web Applications – IT Session 4 and 5 10 4.1.4.3 Selecting Particular Columns If you don’t want to see entire rows from your table, just name the columns in which you’re interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns: SELECT name, birth FROM pet; To find out who owns pets, make the following query: SELECT owner FROM pet; However, notice that the query simply retrieves the owner field from each record, and some of them appear more than once. To minimize the output, retrieve each unique output record just once by adding the keyword DISTINCT: SELECT DISTINCT owner FROM pet; You can use a WHERE clause to combine row selection with column selection. For example, to get birth dates for dogs and cats only, use this query: SELECT name, species, birth FROM petYOUR ASSIGNED NUMBER WHERE species 'dog' OR species 'cat';

Introduction to Web Applications – IT Session 4 and 5 11 4.1.4.4 Ordered Selection You may have noticed in the preceding examples that the result rows are displayed in no particular order. It’s often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause. Here are animal birthdays, sorted by date: SELECT name, birth FROM pet ORDER BY birth; On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order will be undefined for columns using the BINARY cast: ORDER BY BINARY col name. The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by: SELECT name, birth FROM pet ORDER BY birth DESC; You can sort on multiple columns, and you can sort columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query: SELECT name, species, birth FROM pet ORDER BY species, birth DESC; Note that the DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order.

Introduction to Web Applications – IT Session 4 and 5 12 4.1.5 Date Calculation MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates. To determine how old each of your pets is, compute the difference in the year part of the current date and the birth date, then subtract one if the current date occurs earlier in the calendar year than the birth date. The following query shows, for each pet, the birth date, the current date, and the age in years. SELECT name, birth, CURDATE(), (YEAR(CURDATE())-YEAR(birth))(RIGHT(CURDATE(),5) RIGHT(birth,5)) AS age FROM pet; Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if CURDATE() occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful. The query works, but the result could be scanned more easily if the rows were presented in some order. This can be done by adding an ORDER BY name clause to sort the output by name: SELECT name, birth, CURDATE(), (YEAR(CURDATE())-YEAR(birth)) AS age FROM pet ORDER BY name; (RIGHT(CURDATE(),5) RIGHT(birth,5)) To sort the output by age rather than name, just use a different ORDER BY clause: SELECT name, birth, CURDATE(), (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) RIGHT(birth,5)) AS age FROM pet ORDER BY age;

Introduction to Web Applications – IT Session 4 and 5 13 A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values: SELECT name, birth, death, (YEAR(death)-YEAR(birth)) - (RIGHT(CURDATE(),5) RIGHT(birth,5)) AS age FROM pet WHERE death IS NOT NULL ORDER BY age; And as an output message, you will be prompted: The query is supposed to be empty indeed. However, it shouldn’t be the same result if the term NOT was removed. Try it! Note that the query above uses death IS NOT NULL rather than death NULL because NULL is a special value that cannot be compared using the usual comparison operators. What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month of the birth. MySQL provides several date-part extraction functions, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth): SELECT name, birth, MONTH(birth) FROM pet;

Introduction to Web Applications – IT Session 4 and 5 14 Finding animals with birthdays in the upcoming month is easy, too. Suppose that the current month is January. Then the month value is 1 and you look for animals born in February (month 2) like this: SELECT name, birth, MONTH(birth) FROM pet WHERE MONTH(birth) 2; There is a small complication if the current month is December. You don’t just add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1). You can even write the query so that it works no matter what the current month is. That way you don’t have to use a particular month number in the query. DATE ADD() allows you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays: SELECT name, birth FROM pet WHERE MONTH(birth) MONTH(DATE ADD(CURDATE(),INTERVAL 6 MONTH)); 5 Is That All? For our course, hopefully that’s what you need to know. But there is whole world out there. MySQL can be used as a proper Database Management System with administration and multi-users support. You can download it on your PC to play around and develop your skills as a database administrator, which is quite a promising career. If you want to know more about this nice and free database server, go to the official website www.mysql.com. Explore some of the features of a database server that is widely used by developers. For the next steps of the course, we are going to use MySQL in a more interesting and practical way where we will play around with both PHP and MySQL. But this is for our next meeting ;-)

3 Inside MySQL Inside the MySQL server, you can create databases where you want the MySQL server to store your set of tables. Inside the database we are going to put all the tables containing information we want to store. 3.1 A MySQL Table A table in MySQL has 6 structures: Field, Type, Null, Key, Default and Extra. There is a table