Loading Data into a Table:

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your records, then load the contents of the file into the table with a single statement.

You could create a text file `student.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values, you can use NULL values. To represent these in your text file, use \N.

To load the text file `student.txt' into the Student_info table, use this command:

mysql> LOAD DATA LOCAL INFILE "student.txt" INTO TABLE Student_info;

Retrieving Information from a Table

The SELECT statement is used to pull information from a table. The general form of the statement is:
 

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

Selecting All Data

The simplest form of SELECT retrieves everything from a table:

mysql> SELECT * FROM table_name;

Selecting Particular Rows:

You can select only particular rows from your table. For example, if you want to view information about  a particular student (ID=123456) in Student_info, select student's record like this:

mysql> SELECT * FROM Student_info WHERE ID = 123456;

You can combine conditions, for example, assume that the pet table contains information about pets.

To locate female dogs:

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
        -> OR (species = "dog" AND sex = "f");

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:

mysql> SELECT name FROM pet;   (single column)

mysql> SELECT name, birth FROM pet;  (multiple columns)

The following command may result in multiple entries:

mysql> SELECT owner FROM pet;

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:

mysql> 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:

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = "dog" OR species = "cat";

Updating data:

To update information in a table, use UPDATE command. For example, to change the phone number of a particular student, use the following:

mysql> UPDATE Student_info SET phone = "262-472-0000" WHERE ID = 123456 ;

Deleting data:

To delete a particular record form a table, use delete command:

mysql>DELETE FROM Table_name WHERE condition_to_satisfy;

mysql>DELETE FROM Student_info WHERE ID=123456;

To delete a table ot database, use DROP command:

mysql>DROP database_name;

Sorting Rows:

It is 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:

mysql> SELECT name, birth FROM pet ORDER BY birth;

To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

You can sort on multiple columns. For example, to sort by type of animal, then by birth date within animal type with youngest animals first, use the following query:

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

Pattern Matching:

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi..

SQL pattern matching allows you to use `_' to match any single character and `%' to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case insensitive by default. Some examples are shown below. Note that you do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with `a':

mysql> SELECT * FROM pet WHERE name LIKE "a%";

To find names ending with `m':

mysql> SELECT * FROM pet WHERE name LIKE "%m";

To find names containing a `w':

mysql> SELECT * FROM pet WHERE name LIKE "%w%";

To find names containing exactly five characters, use the `_' pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_____";

Databases are often used to answer the question, ``How often does a certain type of data occur in a table?'' For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of censuses on your animals.

Counting the total number of animals you have is the same question as ``How many rows are in the pet table?'' because there is one record per pet. The COUNT() function counts the number of non- NULL results, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;

You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

The Student_info table keeps track of students. If you want to record other information about them, such as GPA, Major, Number of credits taken, you could create another table (Academic_info). You  can create a text file, enter information, and then load the information into the table.

Load the records like this:

mysql> LOAD DATA LOCAL INFILE "academic_info.txt" INTO TABLE Academic_info;

Suppose you want to find out the GPA of each student .In order to display name and GPA, you need both tables for the query:

mysql> SELECT Student_info.first_name, Student_info.last_name, Academic_info.GPA
         > FROM Student_info, Academic_info
         > WHERE Student_info.ID = Academic_info.ID;

If you want to find out about the structure of a table, the DESCRIBE command is useful; it displays information about each of a
table's columns:

mysql> DESCRIBE Student_info;

Back