Skip to main content

Posts

Showing posts with the label mysql foreign key

Numeric Expressions

Numbers can be exact-value literals or approximate-value literals.  Exact-value literals are used just as  given in SQL statements when possible and thus are not subject to the inexactness produced by rounding  error.  On the other hand, approximate-value literals are subject to rounding error and may not necessarily  be used exactly as given.   Exact-value literals are written with no exponent. Approximate-value literals are written in scientific  notation with an exponent.  For example, the numeric values -43, 368.93, and .00214 are exact values,  whereas -4.3E1, 3.6893E2, and 2.14E-3 are approximate values.  Even though the two sets of numbers look  like they have the same values, internally they are represented in different ways:   •  Exact-value numbers -                        These numbers are integer values with no fractional part after the decimal  point or decimal values with a fractional part.  They're represented internally like an integer or  DECIMAL dat

SELECT with WHERE

The WHERE clause is the mechanism for filtering out unwanted rows from the result set.  Rows can be  selected by looking for column values that satisfy various criteria, and different types of values can be  filtered out.  In the statement below, the query is selecting information from three columns,  from a given  table with a clause  where a specific city name is to be used. In other words, it is answering the  question…"What is the ID and district for the city of New York?", as follows;  mysql> SELECT ID, Name, District FROM City           -> WHERE Name = 'New York';  +-------+--------------+-------------+  | ID      | Name        | District     |  +-------+--------------+--------------+  | 3793 | New York | New York  |  +-------+--------------+--------------+   1 row in set (#.## sec)  Expressions in WHERE clauses can use the following types of operators;  ƒ                              Arithmetic -- for calculation  ƒ

Basic Uses of SELECT

            There are many clauses, and combinations thereof, that can be used with a SELECT statement to yield  particular table query results.  They range from very basic, commonly-used options to very specialized and  complex.  The following basic, optional clauses will be covered in this section:  Clauses                   Definition DISTINCT                Eliminates duplicate row data  FROM                      Specifies what table(s) to retrieve data from  WHERE                   Decides what data to show  ORDER BY             Sorts data by specified order criteria  LIMIT                        Reduces the amount of records received  Example of SELECT statement with clauses:  SELECT DISTINCT values_to_display FROM table_name WHERE expression ORDER BY how_to_sort LIMIT row_count;               The above syntax shows the correct order and usage of each of the above optional clauses.  This statement is specifically  selecting  distinct data rows  from a named ta

SELECT Statements

The  SELECT statement is primarily used to retrieve zero or more  rows from one or more tables in a  database.  In MySQL,  SELECT is the most commonly used DML (Data Manipulation Language)  command.  In specifying a SELECT query, the user specifies a description of the desired result set.  It is  built with optional clauses that specify how and what data to retrieve.  The general command syntax is  shown below;  SELECT [<clause options>] <column list> [FROM] <table>  [<clause options>];  Basic SELECT statement example using world database (and the result data):  mysql> SELECT Name FROM Country;  +-------------------------------------------------+  | Name                                                    |  +-------------------------------------------------+  | Afghanistan                                          |  | Netherlands                                          |  | Netherlands Antilles                            |  | Albania        

Foreign Key Clause

The FOREIGN KEY clause has several parts:   •  It names the column in the referring table (country_code).   •  It names the code column in the country_parent table as the referenced column. This column is  the “foreign” key.   •  It specifies what actions to take if records are modified in the referenced table. The foreign key  definition shown specifies the  CASCADE action for both  UPDATE and  DELETE operations. This  means that changes in the parent table are cascaded down to the child table. If a  code value is  changed in the  country_parent table, InnoDB changes any corresponding  city_child  records with that value in the country_code column to match. If a country_parent record is  deleted, InnoDB also deletes any  city_child records with the same country code. (InnoDB  supports actions other than CASCADE, but they are not covered here.   Instead of  CASCADE a SET NULL or RESTRICT can be specified.  In a foreign key relationship, the referring column and the