Download IT Session 3 & 4 – MySQL Basics and more Summaries Design in PDF only on Docsity!
Assigned Number: _________
- 1 The MySQL Database IT Session 3 & 4 – MySQL Basics
- 2 Making your database connection
- 3 Inside MySQL............................................................................................................................
- 1 A MySQL Table..................................................................................................................
- 4 MySQL Basics........................................................................................................................... - 4.1.1 Creating Tables - 4.1.2 Inserting Data into Tables - 4.1.3 Deleting Table Content............................................................................................... - 4.1.4 Selecting Data from a Table And Updating............................................................... - 4.1.4.1 Selecting All Data and Updating Records - 4.1.4.2 Selecting Particular Rows - 4.1.4.3 Selecting Particular Columns - 4.1.4.4 Ordered Selection - 4.1.5 Date Calculation........................................................................................................
- 5 Is That All?...............................................................................................................................
1 The MySQL Database
MySQL is a relational database management system. A relational database is a special kind of database that organizes data into tables and represents relationships among those tables. MySQL has many features that makes is widely used. The first feature is that it a multithreading server, which means that it suits for performance design goals. Besides that, MySQL supports openness in every sense of the term. It runs on countless platforms, including Windows, Mac OS, Linux, FreeBSD and Solaris. If no binary code is available for another platform, you have access to the source to compile to that platform. Some of the other features, we are going to explore in the following two weeks. The content of this worksheet will be also used for the next week, so remember to bring it back with you next week.
2 Making your database connection
The database we are going to use is installed in a remote server. In this course we going to use a graphical interface for MySQL database server, supported by PhPmyAdmin available at the UCL web site. So open the web browser and go to http://www.ucl.ac.uk/is/mysql/admin/ Fill in the login fields as follows: Username: uczlw3e_user Password: student_DBEFREI* Then, click on the button Login. Once you logged in successfully, you will see a welcome message on the right frame of the page:
Welcome to phpMyAdmin 2.6.4-pl
On the top of the left frame, there are four icons, that can be very useful to know: If you pass the mouse over them, you will find out what they stand for. For the purpose of this course, the button we are going to use more frequently is the Query Window which will be used to write our SQL queries. Click on each button to find out their purpose, but beware that if you press the Logout button you will have to login again. You don’t have to worry about the phpMyAdmin Documentation button , but the MySQL Documentation button can be quite useful for you to know detailed information about the SQL commands we are going to run and much more. All free for you! Check it out. Below those buttons, on the left frame of your session page, there are the list of the tables stored on the database uczlw3e , where you will show the tables you will create and manipulate throughout this course.
- UPDATING TABLES. We are going to explain these functions briefly below.
4.1.1 Creating Tables
The first step is to decide what the structure of your database should be: what tables you will need and what columns will be in each of them. You’ll want a table that contains a record for each of your pets. This can be called the pet table, and it should contain, as a bare minimum, each animal’s name. Because the name by itself is not very interesting, the table should contain other information. For example, if more than one person in your family keeps pets, you might want to list each animal’s owner. You might also want to record some basic descriptive information such as species and sex. You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient for now: name, owner, species, sex, birth, and death. Use a CREATE TABLE statement to specify the layout of your table (Note that YOUR_ASSIGNED_NUMBER has to be filled with your folder number). Before going through the table creation, let’s open an editor to edit the sql commands. To do this, we are going to use the editor that comes with MySQL. Click on the Query Window button and type the following commands, where you should substitute YOUR_ASSIGNED_NUMBER for the number on the top right corner on the first page of this document: CREATE TABLE petYOUR_ASSIGNED_NUMBER (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); Now press the button Go. If successful, you will see on the right frame of the session page the following screen: And the SQL create statement underneath the message will be presented to you with different syntax colours. If you have an error message, press the button Back and you can re-edit the command above. VARCHAR is a good choice of type for the name , owner , and species columns because the column values will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to
you. (If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.) Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or perhaps 'male' and 'female'. It’s simplest to use the single characters 'm' and 'f'. The use of the DATE data type for the birth and death columns is a fairly obvious choice. To verify that your table was created the way you expected, look for it among the list of tables and click on it. Your table should look like the following screenshot: If we want to create a more complex table with keys, we need to put other commands in our CREATE TABLE statement. Have a look at the script below: CREATE TABLE USER ( USER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, USER_NAME CHAR(10) NOT NULL, LAST_NAME VARCHAR(30), FIRST_NAME VARCHAR(30), OFFICE CHAR(2) NOT NULL DEFAULT 'NY'); We need to observe some features in the SQL command above:
- The USER_ID field is required to be NOT NULL. This means that whatever data someone inserts into the table USER, the field USER_ID must be assigned a value.
- The USER_ID is of type INT. As a key field, it’s frequently useful to make them as integer type so you can have a wide range of values to attribute to it.
- The USER_ID is also described as a PRIMARY KEY field. This means that this field has unique value. By calling a column a primary key at creation, you are naming it as unique index that guarantees no clashes of data when you want to query a specific data or when you want to make table joins (For table join, refer to sub-section Error! Reference source not found. ). You can use MySQL to help you to insert unique values into your table using the AUTO_INCREMENT function, as described above.
- The OFFICE field has a DEFAULT value. If a row is inserted into the table without a value for this field, this value will be inserted. If a default is not defined, a null value is inserted, unless the field is defined as NOT NULL (like field USER_NAME), in which case MySQL picks a value based on the type of the field.
4.1.2 Inserting Data into Tables
After creating your table, you need to insert data into it, which is usually referred as populating a table. The INSERT statement is useful for this. Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in 'YYYY-MM-DD' format). You could add a new record using an INSERT statement like this: INSERT INTO petYOUR_ASSIGNED_NUMBER VALUES ('Puffball','Diane','hamster','f','1999- 03 - 30',NULL);
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';
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.
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. 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.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';
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.
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;
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 ;-)
