Example 14.30.
Code!View:!
mysql> DESCRIBE categories;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra
|
+ + + + + + +
1 | CategoryID | int(11) | | PRI | NULL |
auto_increment |
| CategoryName | varchar(15) | | MUL | |
|
| Description | longtext | YES | | NULL |
|
| Picture | longblob | YES | | NULL |
|
+ + + + + + +
4 rows in set (0.00 sec)
mysql> DESCRIBE products;
+ + + + + + +
| Field | Type | Null| Key | Default| Extra
|
+ + + + + +
| ProductID | int(11) | | PRI | NULL |
auto_increment|
| ProductName | varchar(40) | | MUL | |
|
| SupplierID | int(11) | YES | MUL | NULL |
|
2 | CategoryID | int(11) | YES | MUL | NULL |
|
| QuantityPerUnit| varchar(20) | YES | | NULL |
|
| UnitPrice | decimal(19,4)| YES | | NULL |
|
| UnitsInStock | smallint(6) | YES | | NULL |
|
| UnitsOnOrder | smallint(6) | YES | | NULL |
|
| ReorderLevel | smallint(6) | YES | | NULL |
|
| Discontinued | tinyint(4) | | | 0 |
|
+ + + + + + +
10 rows in set (0.00 sec)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Explanation
1
The!“categories”!table!has!a!primary!key!field!called!“CategoryID”.
2
The!“products”!table!has!its!own!primary!key!(“ProductID”)!in!addition!to!a!
foreign!key!called!“CategoryID”.!If!a!primary!key!is!referenced!in!another!table,!
it!is!called!a!foreign!key.
14.3.5. Relations
A major advantage of the relational database systems is the ability to create relations between tables. Simply put, a
relation is a connection between a field of one table and a field of another. This relation allows you to look up related
records in the database.
The operation of matching rows from one table to another using one or more column values is called a join. There are
several types of join statements, such as full joins, cross joins, left joins, and so on, but let’s start with a simple joining
of two tables, called an inner join.
Tables can be related to each other with keys. As we discussed earlier, a primary key is a column with a unique value
for each row. A matching key in a second table is called a foreign key. With these keys, you can bind data together
across tables without repeating all of the data in every table where a certain condition is met.
Consider the the previous Example 14.30, in which two tables from the “northwind” database are described. One table
is called “categories” and the other called “products”. “CategoryId” is a primary key field in the “categories” table, and
it is a foreign key in the “products” table. The “CategoryId” key is used to create a relationship between the two tables.
Two Tables with a Common Key
As discussed previously, both the “categories” table and the “products” table have a “CategoryID” key with the same
values, making it possible to create a relation between the two tables.
Let’s create a relation in which all the product names are listed if they are in the “Seafood” category. Because every
product in the “products” table falls into one of the eight categories in the “categories” table, the two tables can be
bound by their common “CategoryID”.
Example 14.31.
Code!View:!
mysql> SELECT CategoryID, CategoryName FROM categories;
+ + +
| categoryID | categoryName |
+ + +
| 1 | Beverages |
| 2 | Condiments |
| 3 | Confections |
| 4 | Dairy Products |
| 5 | Grains/Cereals |
| 6 | Meat/Poultry |
| 7 | Produce |
| 8 | Seafood |
+ + +
8 rows in set (0.00 sec)
mysql> SELECT CategoryID, ProductName FROM products;
(Partial Output)
+ + +
| CategoryID | ProductName |
+ + +
| 1 | Chai |
| 1 | Chang |
| 2 | Aniseed Syrup |
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
| 2 | Chef Anton's Cajun Seasoning |
| 2 | Chef Anton's Gumbo Mix |
| 2 | Grandma's Boysenberry Spread |
| 7 | Uncle Bob's Organic Dried Pears |
| 2 | Northwoods Cranberry Sauce |
| 6 | Mishi Kobe Niku |
| 8 | Ikura |
| 4 | Queso Cabrales |
| 4 | Queso Manchego La Pastora |
| 8 | Konbu |
| 7 | Tofu |
| 2 | Genen Shouyu |
Explanation
This example displays columns from both the “categories” table and the “products” table. In the
“categories” table the “CategoryID” is the primary field and uniquely identifies all other fields in the table.
In the “products” table, the “CategoryID” is a foreign key and is repeated many times for all the products.
Using a Fully Qualified Name and a Dot to Join the Tables
When querying more than one table, a dot is used to fully qualify the columns by their table name to avoid potential
ambiguity if two tables have a field with the same name, as shown in Example 14.32.
Example 14.32.
mysql> SELECT CategoryName, ProductName FROM categories, products
-> WHERE products.CategoryID = 8 AND categories.CategoryID =
8;
+ + +
| CategoryName | ProductName |
+ + +
| Seafood | Ikura |
| Seafood | Konbu |
| Seafood | Carnarvon Tigers |
| Seafood | Nord-Ost Matjeshering |
| Seafood | Inlagd Sill |
| Seafood | Gravad lax |
| Seafood | Boston Crab Meat |
| Seafood | Jack's New England Clam Chowder |
| Seafood | Rogede sild |
| Seafood | Spegesild |
| Seafood | Escargots de Bourgogne |
| Seafood | Röd Kaviar |
+ + +
12 rows in set (0.00 sec)
Explanation
In the SELECT, two tables (separated by commas) will be joined by the “CategoryID” field. Because the
field name is the same in both tables, the table name is prepended to the field name with a dot, as
products.CategoryId and categories.CategoryId. In the WHERE clause, the two tables are
connected if the both tables have a “CategoryID” equal to 8.
Aliases
To make things a little easier by typing less with complicated queries, SQL provides an aliasing mechanism that allows
you to use symbolic names for columns and tables. The alias is defined with the AS keyword and the alias consists of a
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
single character or an abbreviated string. When the alias is used in the WHERE clause to represent a table name, it is
appended with a dot and the name of the field being selected from that table.
Format
(Column Alias)
SELECT column_name AS column_alias_name FROM table_name
!
(Table Alias)
SELECT column_name FROM table_name AS table_alias_name
Example 14.33.
mysql> SELECT CategoryName as Foods FROM categories;
+ +
| Foods |
+ +
| Beverages |
| Condiments |
| Confections |
| Dairy Products |
| Grains/Cereals |
| Meat/Poultry |
| Produce |
| Seafood |
+ +
8 rows in set (0.00 sec)
Explanation
The column name from table “categories” was named “CategoryName”. An alias called “Foods” is created
by using the AS keyword after “CategoryName”. Now when the SELECT returns a result-set, the output
will show “Foods” as the name of the column.
Example 14.34.
mysql> SELECT ProductName FROM products AS p, categories AS c
WHERE
-> p.CategoryID = c.CategoryID AND c.CategoryName="SeaFood";
+ +
| ProductName |
+ +
| Ikura |
| Konbu |
| Carnarvon Tigers |
| Nord-Ost Matjeshering |
| Inlagd Sill |
| Gravad lax |
| Boston Crab Meat |
| Jack's New England Clam Chowder |
| Rogede sild |
| Spegesild |
| Escargots de Bourgogne |
| Röd Kaviar |
+ +
12 rows in set (0.00 sec)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Explanation
This example might look a little tricky at first. The table named “products” is given an alias called “p” and
the table name “categories” is given the alias “c”. These aliases are short names, making it easier to type
the query when more than one table is involved; for example, instead of typing
products.CategoryID, we can type p.CategoryID, and categories.CategoryName can be
referenced as c.CategoryName.
14.3.6. Altering a Table
When you alter a table, you redefine its structure by adding or dropping a new columns, keys, indexes, and tables. You
can also use the ALTER command to change column names, types, and the table name.
Format
ALTER TABLE tablename ADD column datatype
!
Example:
alter table artist add column ArtDate date; alter table artist drop column
"Address";
Example 14.35.
Code!View:!
use pets;
1 mysql> ALTER TABLE dog ADD pet_id int(11);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
2 mysql> ALTER TABLE dog MODIFY column pet_id int(11)
> auto_increment primary key;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
3 mysql> describe dog;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra
|
+ + + + + + +
| name | varchar(20) | YES | | NULL |
|
| owner | varchar(20) | YES | | NULL |
|
| breed | varchar(20) | YES | | NULL |
|
| sex | char(1) | YES | | NULL |
|
| birth | date | YES | | NULL |
|
| death | date | YES | | NULL |
|
| pet_id | int(11) | | PRI | NULL | auto_increment
|
+ + + + + + +
7 rows in set (0.00 sec)
mysql> select * from dog;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
+ + + + + + + +
| name | owner | breed | sex | birth | death |
pet_id |
+ + + + + + +
| Fido | Mr. Jones | Mutt | M | 2004-11-12| 2006-04-02|
1 |
| Lassie| Tommy Rettig| Collie| F | 2006-01-10| NULL |
2 |
+ + + + + + + +
2 rows in set (0.00 sec)
Explanation
1
The!ALTER!command!will!change!the!table!by!adding!a!new!field,!called!
“pet_id”,!an!integer!of!11!digits.
2
Once!the!“pet_id”!field!has!been!created,!the!ALTER!command!is!used!again!to!
make!this!a!primary!key!that!will!automatically!be!incremented!each!time!a!
record!is!added.
3
The!DESCRIBE!command!shows!the!structure!of!the!table!after!it!was!changed.!
A!primary!key!has!been!added.
14.3.7. Dropping a Table
To drop a table is relatively simple. Just use the drop command and the name of the table:
mysql> drop table dog; Query OK, 20 rows affected (0.11 sec)
14.3.8. Dropping a Database
To drop a database, use the drop database command:
mysql> drop database pets; Query OK, 1 row affected (0.45 sec)
14.4. SQL Functions
The following functions are used to alter or format the output of a SQL query. Functions are provided for strings,
numbers, dates, server and information, and so on. They return a result-set. Functions are vendor specific, meaning
functions supported by MySQL might not be supported by Microsoft SQL Server. See the MySQL documenation for a
list of all functions supported.
When using SELECT with a function, the function, as it was called, is displayed as the name of the column in the
result-set as shown in Example 14.36.
Example 14.36.
1 mysql> SELECT avg(UnitPrice)
FROM order_details;
+ +
| avg(UnitPrice) |
+ +
| 26.21851972 |
+ +
1 row in set (0.01 sec)
2 mysql> SELECT avg(UnitPrice) as 'Average Price'
FROM order_details;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
+ +
| Average Price |
+ +
| 26.21851972 |
+ +
1 row in set (0.00 sec)
Explanation
1
The!function!is!displayed!as!the!name!of!the!column.
2
You!can!use!the!AS!keyword!to!create!an!alias!or!another!name!for!the!column!
where!the!function!displays!the!result8set.
14.4.1. Numeric Functions
Suppose you want to get the sum of all the orders, or the average cost of a set of items, or to count all the rows in a table
based on a certain condition. The aggragate functions will return a single value based on a set of other values. If used
among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause.
No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The
functions and their syntax are listed in Table 14.4.
Table 14.4. Aggregate Functions
:;,*&+',
<=%&1>&1('#)
AVG()
Computes!and!returns!the!average!value!of!a!column.
COUNT(expression)
Counts!the!rows!defined!by!the!expression.
COUNT()
Counts!all!rows!in!a!table.
MIN()
Returns!the!minimum!value!in!a!column.
MAX()
Returns!the!maximum!value!in!a!column!by!the!expression.
SUM()
Returns!the!sum!of!all!the!values!in!a!column.
!
Example 14.37.
1 mysql> select count(*) from products;
+ +
| count(*) |
+ +
| 81 |
+ +
1 row in set (0.00 sec)
mysql> SELECT count(*) as 'Number of Rows' FROM products;
+ +
| Number of Rows |
+ +
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
| 81 |
+ +
1 row in set (0.00 sec)
Explanation
1
The!COUNT()!function!counts!all!rows!in!a!table.
Example 14.38.
1 mysql> SELECT avg(UnitPrice)
FROM order_details;
+ +
| avg(UnitPrice) |
+ +
| 26.21851972 |
+ +
1 row in set (0.01 sec)
2 mysql> SELECT FORMAT(avg(UnitPrice),2) as 'Average Price'
FROM order_details;
+ +
| Average Price |
+ +
| 26.22 |
+ +
1 row in set (0.00 sec)
Explanation
1
The!AVG()!function!computes!and!returns!the!average!value!of!a!column,!
called!“UnitPrice”.
2
The!FORMAT!function!returns!the!result!of!the!AVG()!function!with!a!precision!
of!two!decimal!places.
Using GROUP BY
The GROUP BY clause can be used with a SELECT to collect all the data across multiple records and group the results
by one or more columns. This is useful with the aggregate functions such as SUM, COUNT, MIN, or MAX. See the
following two examples.
Example 14.39.
mysql> select CategoryID, SUM(UnitsInStock) as 'Total Units in
Stock'
-> FROM products
-> GROUP BY CategoryID;
+ + +
| CategoryID | Total Units in Stock |
+ + +
| NULL| 0 |
| 1 | 559 |
| 2 | 507 |
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
| 3 | 386 |
| 4 | 393 |
| 5 | 308 |
| 6 | 165 |
| 7 | 100 |
| 8 | 701 |
+ + +
9 rows in set (0.00 sec)
Example 14.40.
mysql> select C.CategoryName,
-> SUM(P.unitsInsStock) AS Units
-> FROM products as P
-> join categories AS C ON C.CategoryID=
-> P.CategoryID Group By C.CategoryName;
+ + +
| CategoryName | Units |
+ + +
| Beverages | 559 |
| Condiments | 507 |
| Confections | 386 |
| Dairy Products | 393 |
| Grains/Cereals | 308 |
| Meat/Poultry | 165 |
| Produce | 100 |
| Seafood | 701 |
+ + +
8 rows in set (0.00 sec)
14.4.2. String Functions
SQL provides a number of basic string functions, as listed in Table 14.5.
Table 14.5. MySQL String Functions
:;,*&+',
<=%&1>&1('#)
CONCAT(string1,string2, )
[a]
Combines!column!values,!or!variables!together!into!one!
string.
LOWER(string)
Converts!a!string!to!all!lowercase!chara cters.
SUBSTRING(string, position)
Extracts!a!portion!of!a!string!(see!Example!14.41).
TRANSLATE
Converts!a!string!from!one!character!set!to!another.
TRIM(' string ');
Removes!leading!characters,!trailing!characters,!or!both!
from!a!character!string.
UPPER(string)
Converts!a!string!to!all!uppercase!characters!(see!Example!
14.41).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
!
[a]
SQL99 defines a concatenation operator (||) to use with the CONCATENATE() function. MySQL uses the
concat() function shown in Table 14.5.
Example 14.41.
mysql> select upper(CompanyName) as 'Company' from shippers;
+ +
| Company |
+ +
| SPEEDY EXPRESS |
| UNITED PACKAGE |
| FEDERAL SHIPPING |
+ +
3 rows in set (0.00 sec)
mysql> select lower(CompanyName) as 'Company' FROM shippers;
+ +
| Company |
+ +
| speedy express |
| united package |
| federal shipping |
+ +
3 rows in set (0.00 sec)
14.4.3. Date and Time Functions
To get the date and time, MySQL provides the functions shown in Table 14.6.
Table 14.6. MySQL Date and Time Functions
:;,*&+',
%/"0#
NOW()
select NOW() > 2006-03-23 20:52:58
!
(See!Example!14.42.)
CURDATE()
select CURDATE(); > '2006-12-15'
!
(See!Example!14.42.)
CURTIME()
select CURTIME(); > '23:50:26'
!
(See!Example!14.42.)
DAYOFYEAR(date)
select DAYOFYEAR('2006-12-15'); > 349
DAYOFMONTH(date)
select DAYOFMONTH('2006-12-15'); > 15
DAYOFWEEK(date)
select DAYOFWEEK('2006-12-15'); > 6
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét