PHP and MySQL by Example- P13

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
45
lượt xem
12
download

PHP and MySQL by Example- P13

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'php and mysql by example- p13', công nghệ thông tin, kỹ thuật lập trình phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: PHP and MySQL by Example- P13

  1. Table 14.3. Most Common SQL Data Types Data  Type Description maximum  size  is  specified  in  the  parentheses. TINYTEXT A  string  with  a  maximum  length  of  255  characters. TEXT A  variable-­‐length  text  string  with  a  maximum  length  of  65,535  characters,   used  for  storing  large  text  files,  documents,  text  areas,  etc. BLOB Binary  large  object.  A  binary  string  with  a  maximum  length  of  65,535   characters,  used  for  storing  binary  files,  images,  sounds,  etc. Date  and  Time DATE (yyyy-mm-dd)  year,  month,  day;  e.g.,  2006-10-30  (Note:  MySQL  also  allows   you  to  store  0000-00-00  as  a  “dummy  date.”) DATETIME (yyyy-mm-dd hh:mm:ss)  date  and  time;  e.g.,  2006-10-30 22:59:59 TIMESTAMP (yyyy-mm-dd hh:mm:ss)  date  and  time;  e.g.,  1970-01-01  (date  and  time  of   last  transaction  on  a  row) TIME (hh:mm:ss)  time;  e.g.,  10:30:58 YEAR (yyyy | yy)  year  in  four  or  two  digits;  e.g.,  1978  or  78   14.3.3. Creating a Table Creating a table is a little more complicated than creating the database. The CREATE TABLE statement is used to create a new table in the database. First you must name the new table and then specify all the fields that will be included in the table as well as the data types and any other attributes. A data type can be an integer, a floating-point (real) number such as 5.4, a string of characters, a date, a time, and so on. Not all databases will specify data types in the same way. To see what data types and attributes are available for MySQL, see Table 14.3 or the MySQL documentation. Designing your tables correctly is important and a subject that merits further research if you have not worked with databases before. See http://databases.about.com/od/specificproducts/a/normalization.htm for an excellent beginner’s tutorial on database design. For now, here are some rules to keep in mind when designing the table: 1.   Choose  the  right  data  type  for  your  fields;  for  example,  use  integer  types  for  primary  keys,   use  float  and  double  types  for  large  numbers,  use  decimal  or  numeric  types  for  currency,   use  the  correct  date  format  for  times  and  dates,  and  give  yourself  ample  field  width  for   strings  containing  variable  numbers  of  characters,  such  as  names  and  addresses.  If  you  are   saving  binary  data  such  as  images  and  sounds,  use  a  data  type  that  supports  such  large   amounts  of  data  such  as  blob  and  text  types.  See  Table  14.3. 2.   Give  columns  sensible  and  concise  names.  Make  them  unique  within  the  table.  Do  not  have   duplicate  columns  in  the  same  table,  as  shown  below.  These  should  not  be  three  columns   all  headed  with  phone.   Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. 3.   Store  only  one  value  under  each  column  heading  in  each  row;  for  example,  if  you  have  a   “Phone”  field,  you  should  not  have  “cell,  home,  business”  all  in  one  table  cell,  as  shown   here:   4.   Create  separate  tables  for  each  group  of  related  items  and  give  each  row  a  unique  column   or  primary  key,  as  shown  here:     User  Table:   Phone  Table:   5.   If  you  still  have  redundant  data,  put  it  in  its  own  table  and  establish  a  relation  between  the   tables  with  foreign  keys. Format CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type CREATE DATABASE pets; Query OK, 1 row affected (0.24 sec) 2 mysql> USE pets; 3 mysql> CREATE TABLE dog -> ( name varchar(20), -> owner varchar(20), -> breed varchar(20), -> sex char(1), -> birth date, -> death date -> ); Query OK, 0 rows affected (0.16 sec) 4 mysql> describe dog; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. +-------+-------------+------+-----+---------+-------+ | 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 | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) Explanation 1 A  database  called  “pets”  is  created. 2 The  “pets”  database  is  selected  and  entered. 3 A  table  called  “dogs”  is  created  with  fields  and  their  data  types.  The  “name”,   “owner”,  and  “breed”  will  consist  of  a  varying  number  of  up  to  20  characters.   The  “sex”  is  one  character,  either  “f”  or  “m”  for  female  or  male.  The  “birth”  and   “death”  columns  are  assigned  date  type. 4 The  DESCRIBE  command  is  like  the  SHOW  command.  It  displays  the  layout  of  the   new  table. Now we can insert some data into the new table. Example 14.27. mysql> INSERT INTO dog(name,owner,breed, sex, birth, death) -> VALUES('Fido','Mr. Jones', 'Mutt', 'M', '2004-11-12', '2006-04-02'); Query OK, 1 row affected (0.09 sec) 14.3.4. Creating a Key In real life, people can be identified by Social Security numbers, driver’s license numbers, and employee numbers; books can be identified by ISBN numbers; and a Web store order can be identified by a purchase order number. These identification numbers must be unique so that no two people have the same Social Security number, no two books have the same ISBN number, and so on. Keys are used to uniqely identify a record in a table. There are two types of keys: primary keys and foreign keys. Primary Keys Each table typically has a primary key. Primary keys are used to uniquely identify a record in the database. They must be unique, never change, occur only once per table, and are normally numeric types. You can choose to manually generate this unique number for each record or let the database do it for you. If you let the database generate the primary key, it will generate a unique number, given a starting value (e.g., 1) and then for each new record increment that number by one. Even if a record is deleted, that number is never recycled. The database increments its internal counter, guaranteeing that each record will be given a unique “key.” To set a field as a primay key, use the attribute PRIMARY KEY (field_name) and to tell the database to automatically create the unique number, use the AUTO_INCREMENT attribute following the field definition. The primary key cannot be null. The following two examples describe a table called “categories” where the primary key is called “CategoryID”. It will automatically be incremented each time a new category is added to the table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Example 14.28. mysql> USE northwind; Database changed mysql> DESCRIBE categories; +--------------+-------------+------+-----+---------+------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+------------+ | CategoryID | int(11) | | PRI | NULL |auto_increment | | CategoryName | varchar(15) | | MUL | | | | Description | longtext | YES | | NULL | | | Picture | longblob | YES | | NULL | | +--------------+-------------+------+-----+---------+------------+ 4 rows in set (0.09 sec) Explanation The “CategoryID” is the primary key, an integer of up to 11 digits, which will be incremented by 1, initially set to NULL (no value). The first time a record is inserted into the database, the value will be 1. Example 14.29. 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.16 sec) Explanation The primary key is called “CategoryID”. It is used to uniquely identify the different categories in this table from the “northwind” database. When a new category is added to the table, the “CategoryID” will be automatically incremented by 1. Foreign Keys If a primary key is referenced in another table, it is called a foreign key. Foreign keys are used to create relation between tables. In the following example, two tables are described, which both reference the “CategoryID” key, although it is primary in one and foreign in the other. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 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.
  6. 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.
  7. | 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.
  8. 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.
  9. 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.
  10. +-------+-------------+-------+-----+-----------+-----------+----+ | 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.
  11. +---------------+ | 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  result-­‐set. 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 Function What  It  Does 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.
  12. | 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.
  13. | 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 Function What  It  Does CONCAT(string1,string2,...)[a] Combines  column  values,  or  variables  together  into  one   string. LOWER(string) Converts  a  string  to  all  lowercase  characters. 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.
  14.   [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 Function Example 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.
  15. Table 14.6. MySQL Date and Time Functions Function Example WEEKDAY(date) select WEEKDAY('2006-12-15'); --> 4 MONTHNAME(date) select MONTHNAME('2006-12-15'); --> December DAYNAME(date) select DAYNAME('2006-12-15'); --> Friday YEAR(date) select YEAR('2006-12-15'); --> 2006 QUARTER(date) select QUARTER('2006-12-15'); --> 4   Example 14.42. mysql> select NOW() ; +---------------------+ | NOW() | +---------------------+ | 2006-03-21 00:32:37 | +---------------------+ 1 row in set (0.00 sec) mysql> select CURDATE(); +----------------+ | CURDATE() | +----------------+ | 2006-03-21 | +----------------+ 1 row in set (0.03 sec) mysql> select CURTIME(); +----------------+ | CURTIME() | +----------------+ | 00:12:46 | +----------------+ 1 row in set (0.01 sec) Formatting the Date and Time When retrieving dates and times from a table, you might find you want to format the output. For example, when selecting the dates of the orders from the orders table in the “northwind” database, the result-set is not user friendly. Date values in SQL are always saved in MM/DD/YY(YY) format. The DATE_FORMAT() and TIME_FORMAT() functions (see Example 14.43) are provided with a list of parameters (see Table 14.7) used to specify how the the output should be displayed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Example 14.43. mysql> select DATE_FORMAT('2006-03-23', '%W %M %d, %Y') as Today; +-------------------------+ | Today | +-------------------------+ | Thursday March 23, 2006 | +-------------------------+ 1 row in set (0.00 sec) mysql> select DATE_FORMAT(OrderDate,'%M %e, %Y - %l:%i %p') FROM orders LIMIT 5; +-----------------------------------------------+ | DATE_FORMAT(OrderDate,'%M %e, %Y - %l:%i %p') | +-----------------------------------------------+ | July 4, 1996 - 12:00 AM | | July 5, 1996 - 12:00 AM | | July 8, 1996 - 12:00 AM | | July 8, 1996 - 12:00 AM | | July 9, 1996 - 12:00 AM | +-----------------------------------------------+ 5 rows in set (0.00 sec) Table 14.7. DATE_FORMAT() and TIME_FORMAT() Paramater What  It  Means %a Weekday  abbreviation  (Sun,  Mon,  Tues,  etc.) %b Month  name  abbreviation  (Jan,  Feb,  Mar,  etc.) %c Month  (1–12) %d Two-­‐digit  day  of  the  month  (01–31) %D Day  with  a  suffix  (30th,  31st) %e Day  of  the  month  (1–31) %f Microseconds  (000000..999999) %H Hour  (00..23) %h Hour  (01..12) %i Minutes,  numeric  (00..59) %I Hour  (01–12) %j Day  of  year  (001–366) %k Hour  (0..23) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. Table 14.7. DATE_FORMAT() and TIME_FORMAT() Paramater What  It  Means %l Hour  (1–12) %m Month  with  a  leading  0  (01,  06,  etc.) %M Month  name  (March,  April,  May,  etc.) %p AM/PM %r Time,  12-­‐hour  (hh:mm:ss  followed  by  AM  or  PM) %S Seconds  (00..59) %s Seconds  (00..59) %T Time,  24-­‐hour  (hh:mm:ss) %U Week  (00..53)  starting  with  Sunday %u Week  (00..53)  starting  with  Monday %v Week  (01..53)  starting  with  Monday %V Week  (01..53)  starting  with  Sunday %W Weekday  (Sunday,  Monday,  etc.) %w Day  of  the  week  (0  =  Sunday..6  =  Saturday) %Y Year  (1999,  2007) %y Two-­‐digit  year  (99,  07) %% A  literal  %  character   The MySQL EXTRACT Command The EXTRACT command is an example of a MySQL extension, not described in the SQL standard. It allows you to extract different parts of a date or time, as shown in Table 14.8. Table 14.8. Date and Time Parts Type Format SECOND SECONDS MINUTE MINUTES HOUR HOURS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. Table 14.8. Date and Time Parts Type Format DAY DAYS MONTH MONTHS YEAR YEARS  (see  Example  14.44) MINUTE SECOND “MINUTES:SECONDS” HOUR_MINUTE “HOURS:MINUTES” DAY_HOUR “DAYS  HOURS” YEAR_MONTH “YEARS-­‐MONTHS” HOUR_SECOND “HOURS:MINUTES:SECONDS” DAY_MINUTE “DAYS  HOURS:MINUTES” DAY_SECOND “DAYS  HOURS:MINUTES:SECONDS”   Example 14.44. mysql> select EXTRACT(YEAR FROM NOW()); +--------------------------+ | EXTRACT(YEAR FROM NOW()) | +--------------------------+ | 2006 | +--------------------------+ 1 row in set (0.03 sec) 14.5. Chapter Summary In this chapter you learned how to use the SQL language to create database schemas as well as how to insert, update, retrieve, alter, and delete records from a database. 14.5.1. What You Should Know Now that you have finished this chapter you should be able to answer the following questions: 1. How  do  you  retrieve  all  the  records  from  a  database  table? 2. How  do  you  retrieve  a  select  set  of  records  or  a  single  record  from  a  table  based   on  specific  criteria? 3. How  do  you  select  and  sort  records  in  a  database? Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. 4. How  do  you  select  a  range  of  rows  from  a  database? 5. How  do  you  create  a  database? 6. How  do  you  create  database  tables? 7. How  do  you  assign  a  primary  key  to  a  field? 8. How  are  records  inserted  into  the  database  table? 9. How  are  records  updated  in  a  table? 10. How  do  you  delete  a  record? 14.5.2. What’s Next? Now that you have learned how to use the SQL language, you can talk to your database. In the next chapter, we learn how to use PHP functions to connect to the MySQL server and retrieve and display data from a database using the SQL statements. Chapter 14 Lab 1. Go  to  the  MySQL  console  and  use  the  show  command  to  list  all  the  databases.  Use   the  mysql  database.  Now  display  all  of  its  tables. 2. Create  a  new  database  called  school.  Once  you  create  the  database,  you  need  to   be  able  to  use  it:     use school; 3. Create  a  table  called  student.  The  table  will  consist  of  the  following  fields:     FirstName LastName Email CellPhone Major GPA StartDate StudentId (the primary key) The following information is the type of data you will use to define your table. Go to the Web and look for a table similar to this to use as your guide. Data  Type Description integer(size)   int(size)   Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. smallint(size)   tinyint(size) Holds  integers  only The  maximum  number  of  digits  are  specified  by  size  in  parentheses. decimal(size,d)   numeric(size,d) Holds  numbers  with  fractions. The  maximum  number  of  digits  are  specified  in  size.  The  maximum  number  of   digits  to  the  right  of  the  decimal  is  specified  in  d. char(size) Holds  a  fixed-­‐length  string  (can  contain  letters,  numbers,   and  special  characters).  The  fixed  size  is  specified  by  size   in  parentheses. varchar(size) Holds  a  variable-­‐length  string  (can  contain  letters,   numbers,  and  special  characters).  The  maximum  size  is   specified  by  size  in  parentheses. date(yyyymmdd) Holds  a  date. 4. Use  the  SQL  describe  statement  to  display  the  information  you  used  to  create   the  school  database. 5. Insert  three  rows  into  the  table:     Row  1: FirstName:  John     LastName: Doe Email: johndoe@smileyface.edu CellPhone: 408-333-3456 Major: CIS GPA: 2.8 StartDate: 09/22/2004 (use the correct date format!) StudentId:  1 Row  2: FirstName:  Mary     LastName: Chin Email: mchin@qmail.com CellPhone: 408-204-1234 Major: Biology GPA: 3.3 StartDate: 06/22/2003 StudentId:  2 Row  3: FirstName:  Sadish     LastName: Pamel Email: sadi@univ_ab.edu CellPhone: 415-204-1234 Major: CIS GPA: 3.9 StartDate: 06/22/2003 StudentId:  2 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản