Mysql 4th Edition By Paul DuBois - Part1

Today i started reading Mysql 4TH Edition By Paul DuBois

I wanted to share few important quotations found from this book from the first 2 chapters.

1) MySQL’s roots begin in 1979, with the UNIREG database tool created by Michael "Monty" Widenius for the Swedish company TcX. In 1994,TcX began searching for an RDBMS with an SQL interface for use in developing Web applications.They tested some commercial servers, but found all too slow for TcX’s large tables.They also took a look at mSQL, but it lacked certain features that TcX required.Consequently, Monty began developing a new server.The programming interface was explicitly designed to be similar to the one used by mSQL because several free tools were available for mSQL, and by using a similar interface, those same tools could be used for MySQL with a minimum of porting effort.

2) Why Choose MySQL?

If you’re looking for a free or low-cost database management system, several are available from which to choose, such as MySQL, PostgreSQL, or SQLite.When you compare MySQL with other database systems, think about what’s most important to you.

Performance, support, features (such as SQL conformance or extensions), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations,MySQL has many attractive features to offer:

a) Speed. MySQL is fast. Its developers contend that MySQL is about the fastest database system you can get.You can investigate this claim by visiting http://www.mysql.com/why-mysql/benchmarks/, a performance-comparison page on the MySQL Web site.

b) Ease of use. MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems.

c) Query language support. MySQL understands SQL (Structured Query Language), the standard language of choice for all modern database systems.

d) Capability. The MySQL server is multi-threaded, so many clients can connect to it at the same time. Each client can use multiple databases simultaneously.You can access MySQL interactively using several interfaces that let you enter queries and view the results: command-line clients,Web browsers, or GUI clients. In addition,
programming interfaces are available for many languages, such as C, Perl, Java, PHP,Python, and Ruby.You can also access MySQL using applications that support ODBC and .NET (protocols developed by Microsoft).This gives you the choice of using prepackaged client software or writing your own for custom applications.

e) Connectivity and security. MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone,anywhere. But MySQL has access control so that one person who shouldn’t see another’s data cannot.To provide additional security, MySQL supports encrypted connections using the Secure Sockets Layer (SSL) protocol.

f) Portability. MySQL runs on many varieties of Unix and Linux, as well as on other systems such as Windows and NetWare. MySQL runs on hardware from high-end servers down to small personal computers (even palmtop devices).

g) Small size. MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain other database systems.

h) Availability and cost. MySQL is an Open Source project available under multiple licensing terms. First, it is available under the terms of the GNU General Public License (GPL).This means that MySQL is available without cost for most in-house uses. Second, for organizations that prefer or require formal arrangements or that do not want to be bound by the conditions of the GPL, commercial licenses are available.

i) Open distribution and source code. MySQL is easy to obtain; just use your Web browser. If you don’t understand how something works, are curious about an algorithm, or want to perform a security audit, you can get the source code and examine it. If you think you’ve found a bug, please report it; the developers want to know.

3) MySQL enables you to insert several rows into a table with a single INSERT statement by specifying multiple value lists:

INSERT INTO tbl_name VALUES(...),(...),... ;

Example:

mysql> INSERT INTO student VALUES('Avery','F',NULL),('Nathan','M',NULL);

This involves less typing than multiple INSERT statements, and also is more efficient for the server to execute. Note that parentheses enclose the set of column values for each row.

4) You can provide a list of column/value assignments. This syntax uses a SET clause containing col_name=value assignments rather than a VALUES() list:

INSERT INTO tbl_name SET col_name1=value1, col_name2=value2, ... ;

Example:

mysql> INSERT INTO member SET last_name='Stein',first_name='Waldo';

For any column not named in the SET clause, MySQL assigns its default value.This form of INSERT cannot be used to insert multiple rows with a single statement.

5) You can sort using multiple columns, and each column can be sorted independently in ascending or descending order.The following query retrieves rows from the president table, sorts them by reverse state of birth, and by ascending last name within each state:

mysql> SELECT last_name, first_name, state FROM president
-> ORDER BY state DESC, last_name ASC;

6) NULL values in a column sort at the beginning for ascending sorts and at the end for descending sorts. If you want to ensure that NULL values will appear at a given end of the sort order, add an extra sort column that distinguishes NULL from non-NULL values. For example,if you sort presidents by reverse death date, living presidents (those with NULL death dates) will appear at the end of the sort order.To put them at the beginning instead, use this query:

mysql> SELECT last_name, first_name, death FROM president ORDER BY IF(death IS NULL,0,1), death DESC;

The IF() function evaluates the expression given by its first argument and returns the value of its second or third argument, depending on whether the expression is true or false. For the query shown, IF() evaluates to 0 for NULL values and 1 for non-NULL values. This places all NULL values ahead of all non-NULL values.

7) LIMIT also enables you to pull a section of rows out of the middle of a result set.To do this, you must specify two values.The first value is the number of rows to skip at the beginning of the result set, and the second is the number of rows to return.The following query is similar to the previous one but returns 5 rows after skipping the first 10:

mysql> SELECT last_name, first_name, birth FROM president ORDER BY birth DESC LIMIT 10, 5;

8) To calculate one date from another, you can use DATE_ADD() or DATE_SUB().These functions take a date and an interval and produce a new date.

For example:
mysql> SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR);

9) This pattern matches last names that contain exactly four characters:

mysql> SELECT last_name, first_name FROM president WHERE last_name LIKE '____';

10) Subqueries

Subquery support is a capability that allows one SELECT statement to be written within parentheses and nested inside another. Here’s an example that looks up the IDs for grade event rows that correspond to tests ('T') and uses them to select scores for those tests:

SELECT * FROM score WHERE event_id IN (SELECT event_id FROM grade_event WHERE category = 'T');

Subqueries can return different types of information:
a) A scalar subquery returns a single value.
b) A column subquery returns a single column of one or more values.
c) A row subquery returns a single row of one or more values.
d) A table subquery returns a table of one or more rows of one or more columns.

Subquery results can be tested in different ways:

a) Scalar subquery results can be evaluated using relative comparison operators such as = or <.
b) IN and NOT IN test whether a value is present in a set of values returned by a subquery.
c) ALL, ANY, and SOME compare a value to the set of values returned by a subquery.
d) EXISTS and NOT EXISTS test whether a subquery result is empty.

11) ALL, ANY, and SOME Subqueries

The ALL and ANY operators are used in conjunction with a relative comparison operator to test the result of a column subquery.They test whether the comparison value stands in particular relationship to all or some of the values returned by the subquery. For example, <= ALL is true if the comparison value is less than or equal to every value that the subquery returns, whereas <= ANY is true if the comparison value is less than or equal to any value that the subquery returns. SOME is a synonym for ANY.

This statement determines which president was born first by selecting the row with a birth date less than or equal to all the birth dates in the president table (only the earliest date satisfies this condition):

SELECT last_name, first_name, birth FROM president WHERE birth <= ALL (SELECT birth FROM president);

12) Correlated Subqueries

Subqueries can be uncorrelated or correlated:

a) An uncorrelated subquery contains no references to values from the outer query.An uncorrelated subquery can be executed by itself as a separate statement. For example,the subquery in the following statement is uncorrelated because it refers only to the table t1 and not to t2:

SELECT j FROM t2 WHERE j IN (SELECT i FROM t1);

b) A correlated subquery does contain references to values from the outer query, and thus is dependent on it. Due to this linkage, a correlated subquery cannot be executed by itself as a separate statement. For example, the subquery in the following statement is true for each value of column j in t2 that matches a column i value in t1:

SELECT j FROM t2 WHERE (SELECT i FROM t1 WHERE i = j);

13) Transactional systems typically are characterized as providing ACID properties.ACID is an acronym for Atomic, Consistent, Isolated, and Durable, referring to four properties that transactions should have:

• Atomicity: The statements a transaction consists of form a logical unit.You can’t have just some of them execute.

• Consistency: The database is consistent before and after the transaction executes.In other words, the transaction doesn’t make a mess of your database.

• Isolation: One transaction has no effect on another.

• Durability: When a transaction executes successfully to completion, its effects are recorded permanently in the database.

About the Author

Paul DuBois is a writer, database administrator, and leader in the open source and MySQL communities. He has contributed to the online documentation for MySQL and is the author of MySQL and Perl for the Web (New Riders), MySQL Cookbook, Using csh and tcsh, and Software Portability with imake (O’Reilly). He is currently a technical writer with the MySQL documentation team at Sun Microsystems.