Last Updated:

MySQL DBMS | Safety system | SQL Language

Above, we discussed why a Web programmer needs to provide access to databases. Next, you need to find out what types of databases and by what means this access can be provided.

The simplest databases of all possible are flat text files. You can access them by using DHTML tools or file operations that are included with server-side scripting languages. To interact with popular desktop databases, such as the Well-known Microsoft Access DBMS, they use their own technologies. Since the most popular desktop operating systems are still members of the Microsoft Windows family, the tools for accessing desktop databases will be discussed in the topic on the server-side scripting language Active Server Pages (ASP). The most advanced means of access are database servers, such as MS SQL or MySQL, the features of which are devoted to this topic.

MySQL DBMS.

MySQL is arguably the most striking software project since the release of Linux. Now it is a serious competitor to large DBMSs in the field of small and medium-sized database development. The specific goals of MySQL design were speed, reliability, and ease of use. To achieve such performance, its developer, the Swedish firm TSX, decided to multi-threaded the internal mechanism of MySQL. A multithreaded application performs multiple tasks at the same time, as if multiple instances of the application were running at the same time.

By making MySQL multithreaded, TSX has given users many benefits. Each incoming connection is handled by a separate thread, and another always-running thread manages the connections, so clients do not have to wait for requests from other clients to complete. Any number of requests can be executed at the same time. While a thread writes data to a table, all other queries that require access to that table simply wait for it to become available. The client can perform all valid operations without paying attention to other concurrent connections. The control thread prevents two threads from writing to the same table at the same time. Such an architecture is more complex than a single-threaded one. However, the gain in speed due to the simultaneous execution of several requests significantly exceeds the speed losses caused by the increase in complexity.

Another benefit of multithreaded processing is common to all multithreaded applications. Although threads share process memory, they run separately. With this separation, thread execution on multiprocessor machines can be distributed across multiple CPUs. In Fig. Figure 3-1 shows this multithreaded nature of the MySQL server.

In addition to the performance gains derived from multithreading, MySQL supports a large subset of the SQL query language. MySQL supports more than a dozen data types as well as SQL functions. Your app can access these features through ANSI SQL commands. MySQL actually extends ANSI SQL with several new features. Among them are new functions (ENCRYPT, WEEKDAY, IF and others), the ability to increment fields (AUTO_INCREMENT and LAST_INSERT ID), as well as the ability to distinguish between upper and lower cases.

TSX intentionally omitted some of the SQL features found in large databases. The most noticeable is the lack of transactions and built-in procedures. TX decided that the realization of these opportunities would cause too much of a blow to performance. However, TX continues to work in this direction, but in such a way that only those users who really need such capabilities suffer from loss of performance.

Since 1996, TSX has been using MySQL in an environment where there are more than 40 databases containing 10,000 tables. Of these 10,000, more than 500 tables have, in turn, more than 7 million records – about 100 GB of data.

MySQL Security

Not only do you need to have reliable access to your data, but you also need to be sure that others don't have any access to it. MySQL uses its own database server for security. When you first install MySQL, a database called "mysql" is created. There are five tables in this database: db, host, user, tables_priv, and columns_priv. Newer versions of MySQL also create a database called func, but it has nothing to do with security. MySQL uses these tables to determine who is allowed to do what. The user table contains security data that is specific to the server as a whole. The host table contains server access rights for remote computers. Finally, db, tables_priv, and co lumns_priv control access to individual databases, tables, and columns.

We'll take a quick look at all the tables that support security in MySQL, and then we'll look at how they can be used to secure the MySQL kernel.

The user table. The user table has the form shown in Table 3.1:

Table 3.1. User table

FieldTypeNullThe keyDefault value
Hostchar(60) PRI 
Userchar(16) PRI 
Passwordchar(16)   
Select_privenum('N','Y')  N
Insert_privenum('N','Y')  N
Update_privenum('N','Y')  N
Delete_privenum('N','Y')  N
Create_privenum('N','Y')  N
Drop_privenum('N','Y')  N
Reload_privenum('N','Y')  N
Shutdown_privenum('N','Y')  N
Process_privenum('N','Y')  N
File_privenum('N','Y')  N
Grant_privenum('N','Y')  N
References_privenum('N','Y')  N
Index_privenum('N','Y')  N
Alter_privenum('N','Y')  N

In the Host and User columns, you can use the "%" symbol to replace an arbitrary sequence of characters. For example, the host name "chem%lab" includes "chembiolab", "chemtestlab", etc. The moniker name "nobody" acts as a single "%", that is, it covers all users not mentioned elsewhere. The meaning of the various access rights is explained below:

Select_priv - Ability to execute SELECT commands.

Insert_priv - Ability to execute INSERT commands.

Update_priv - Ability to execute UPDATE commands.

Delete_priv - Ability to execute DELETE commands.

Create_priv - Ability to execute CREATE commands or create databases.

Drop_priv - Ability to execute DROP commands to delete databases.

Reload_priv - Ability to update access information using mysqladmin reload.

Shutdown_priv - Ability to stop the server via mysqladmin shutdown.

Process_priv - Ability to manage server processes.

File_priv - Ability to read and write files using commands such as SELECT INTO OUTFILE and LOAD DATA INFILE.

Grant_priv - Ability to give privileges to other users.

Index_priv - Ability to create and destroy indexes.

Alter_priv - Ability to execute the ALTER TABLE command.

MySQL has a special feature that allows you to hide passwords from prying eyes. The password() function encrypts the password. The following steps show how to use the password() function when adding users to the system.

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES ('%', 'bob', password('mypass'), 'Y', 'Y', 'Y', 'Y')

INSERT INTO user (Host, User, Password, Select_priv)

VALUES ('athens.imaginary.com', 'jane', '', 'Y')

INSERT INTO user(Host, User, Password)

VALUES ('%', 'nobody', '')

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES ('athens.imaginary.com', 'nobody', password('thispass'), 'Y', 'Y', 'Y', 'Y')
And

MySQL users are usually not associated with operating system user names. By default, MySQL client tools use operating system user names to register, but no compliance is required. In most MySQL client applications, you can use the -u option to connect to MySQL using any name. Your operating system user name will not appear in the user table unless you specifically include it with privileges.

The first user we create, "bob", can connect to the database from any computer and execute select, INSERT, UPDATE, and DELETE commands. The second user, "jane", can connect with "athens.imaginary.com", has no password, and can only perform SELECT. The third user - "nobody" - from any machine. This user can't do anything at all. The last user - "nobody" - from the machine "athens.imaginary.com", he can perform SELECT, INSERT, UPDATE and DELETE, like the user "bob".

How does MySQL map? A name can actually correspond to multiple records. For example, "nobody @athens.imaginary.com" corresponds to both "nobody@%" and "nobody@athens.imaginary.com". Before searching the user table, MySQL sorts the data as follows:

1. First, a match is searched for nodes that do not contain "%" masks, and the empty Host field is treated as "%".

2. For the same node, the names that do not contain masks match first. An empty User field is treated as containing a "%".

3. The first correspondence found shall be considered final.

In the previous example, the user will first be compared to "nobody" from "athens.imaginary.com" because "athens.imaginary.com" in the sort order is higher than "%". Because computer names are sorted before user names, the privilege values for the computer from which you are connecting take precedence over any specific rights you may have. If the user table contains entries:

HostUser
%jane
athens.imaginary.com 

and jane connects with "athens.imaginary.com", then MySQL will use privileges, data "athens.imaginary.com".

Table db. The user table does not mention specific databases and tables. The user table manages the server as a whole. However, a server typically contains multiple databases that serve different purposes and, accordingly, serve different groups of users. Access rights to individual databases are stored in the db table:

Table 3.2. Table db

FieldTypeNullThe keyDefault value
Hostchar(60) PRI 
Dbchar(32) PRI 
Userchar(16) PRI 
Select_privenum('N','Y')  N
Insert_privenum('N','Y')  N
Update_privenum('N','Y')  N
Delete_privenum('N','Y')  N
Create_privenum('N','Y')  N
Drop_privenum('N','Y')  N
References_privenum('N','Y')  N
Index_privenum('N','Y')  N
Alter_privenum('N','Y')  N

This table is similar in many ways to the user table. The main difference is that instead of the Password column, there is a Db column. the Table manages user rights with respect to certain databases. Because the privileges specified in the user table apply to the server as a whole, the rights assigned to a user in the user table override the rights assigned to the same user in the table. For example, if a user in the user table is allowed INSERT access, this right applies to all databases, regardless of what is specified in the db table.

It is most efficient to create records in the user table for all users in which no rights are granted. In this case, the user can only connect to the server without performing any action. An exception is made only for a user who is designated by a server administrator. Everyone else must gain access rights through the db table. Each user must be present in the user table or they will not be able to connect to the databases.

The same rules that apply to the User and Host columns in the user table apply in the db table, but with some peculiarity. An empty Host field forces MySQL to find an entry corresponding to the user host name in the host table. If no such record is found, MySQL denies access. If a match is found, MySQL defines rights as the intersection of rights defined by the host and db tables. In other words, in both records, the permission must be set to "Y", otherwise access is denied.

The host table. The host table serves a specific purpose. Its structure is shown in Table 3.3:

Table 3.3. Host Table

FieldTypeNullThe keyDefault value
Hostchar(60) PRI 
Dbchar(32) PRI 
Select_privenum('N','Y')  N
Insert_privenum('N','Y')  N
Update_privenum('N','Y')  N
Delete_privenum('N','Y')  N
Create_privenum('N','Y')  N
Drop_privenum('N','Y')  N
Grant_privenum('N','Y')  N
References_privenum('N','Y')  N
Index_privenum('N','Y')  N
Alter_privenum('N','Y')  N

The host table allows you to set basic permissions at the intercomputer level. When checking permissions, MySQL searches the db table for the correspondence between the username and its machine. If it finds an entry that matches a username whose host field is empty, MySQL accesses the host table and uses the intersection of both rights to determine the final access right. If you have a group of servers that you consider less secure, you can deny them all write permissions. If "bob" comes from one of these machines, and his entry in the db table contains an empty host field, he will be denied a write operation, even if it is allowed to him according to the db table.

Tables tables_priv and columns_priv. These two tables essentially refine the data available in the db table. Namely, the right to any operation is first checked against table db, then against table tables_priv, then against table columns_priv. The operation is allowed if one of them gives permission. By using these tables, you can narrow the scope of permissions to the level of tables and columns. You can manage these tables through the SQL GRANT and REVOKE commands.

Access control sequence. 

Let's connect the elements of the MySQL security system together and show how you can use them in real situations. MySQL implements access control in two stages. The first stage is connection. You must connect to the server before you try to do anything.

Two checks are performed when connecting. First, MySQL checks to see if there is an entry in the user table that matches the username and the machine from which it is connecting. Finding a match is based on the rules we discussed earlier. If no match is found, access is denied. In the case when the corresponding entry is found and has a non-empty Password field, you must enter the correct password. An incorrect password causes the connection request to be rejected.

If the connection is established, MySQL proceeds to the verification stage of the request. In this case, the requests you make are compared with your rights. MySQL checks these rights against the user, db, host, tables_pnv and columns_priv tables. As soon as a match is found in the user table with a positive resolution, the command is executed immediately. Otherwise, MySQL continues to search the following tables in the order indicated:

1.db

2. tables_priv

3. columns_priv

If the db table contains permission, further validation is stopped and the command is executed. If not, then MySQL looks for a match in the table tables_priv . If this is a SELECT command that joins two tables, the user must have permissions to both tables. If at least one of the records is denied access or missing, MySQL checks all the columns in the columns_priv table in exactly the same way.

Change access rights. 

MySQL loads access tables when the server starts. The advantage of this approach compared to dynamically accessing tables is speed. The downside is that changes made to MySQL access tables do not immediately take effect. In order for the server to see these changes, you need to run the mysqladmin reload command. If you modify tables by using the GRANT or REVOKE SQL commands, you do not have to explicitly overload the tables.

MySQL Utilities

TSX distributes MySQL with a large set of auxiliary utilities, but the set of utilities offered by third-party developers is even richer.

Command Line Tools:

Isamchk - Scans files containing database data. These files are called ISAM (ISAM) files. This utility can fix most isAM file corruption.

Isamlog - Reads MySQL-generated logs related to ISAM files. You can use these logs to recreate tables or to reproduce changes made to tables over a period of time.

mysql - Creates a direct connection to the database server and allows you to enter queries directly from the MySQL prompt.

mysqlaccess - Modifies MySQL access rights tables and displays them in an easy-to-read form. Using this utility is a good way to learn the structure of MySQL access tables.

Mysqladmin - Performs administrative functions. With this utility, you can add and remove entire databases, as well as shut down the server.

Mysqlbug - Generates a report for TX about a problem that has occurred in MySQL. The report will also be sent to the MySQL mailing list, and an army of MySQL volunteers will investigate the problem.

Mysqldump - Writes the entire contents of a table, including its structure, to a file in the form of SQL commands that can be used to recreate the table. The output of this utility can be used to recreate a table in another database or on another server. Its syntax is mysqldump -u user -p dbname --tab=path, where path is the path to save files.

Mysqlimport - Reads data from a file and enters it into a database table. This should be a delimited file where the delimiter can be of any normal kind, such as a comma or quotation marks.

Mysqlshow - Displays the structure of the databases available on the server and the tables of which they are composed.

Third-party utilities. 

No vendor or developer can independently provide all the support tools necessary for a software product. For the most recent list, visit the MySQL homepage: http://www.mysql.com/Contrib.

Database conversion utilities:

access_to_mysql - Converts Microsoft Access databases to MySQL tables. Enabled in Access as a feature that allows you to save tables in a format that allows you to export them to MySQL.

dbf2mysql - Converts dBASE (DBF) files to MySQL tables. Although dBASE has lost popularity, the DBF format has been established as the most common format for transferring data between different database applications. All major desktop database applications can read and write DBF files. This application is useful for exporting/importing data into commercial desktop databases.

Exportsql/Importsql - Converts Microsoft Access databases to MySQL and vice versa. These utilities are Access features that you can use to export Access tables in a format that is readable for MySQL. You can also use them to convert the MySQL SQL output to a view that is readable by Access.

CGI interfaces:

PHP - Creates HTML pages using special tags recognized by the PHP analyzer. PHP has interfaces to most major databases, including MySQL and mSQL.

Mysql-webadmin - Performs web administration of MySQL databases. By using this tool, you can view tables and modify their contents by using HTML forms.

Mysqladm - Performs web administration of MySQL databases. This CGI program allows you to view tables via the WWW, add tables and change their contents.

www-sql - Creates HTML pages from MySQL database tables. This program parses HTML pages in search of special tags and uses the extracted data to execute SQL commands on the MySQL server.

Client applications:

Mysqlwinadmn - Allows you to administer MySQL from Windows. With this tool, you can execute mysqladmin functions from the GUI.

Xmysql - Provides full access to MySQL database tables for the X Window System client. Supports group inserts and deletions.

Xmysqladmin - Allows you to administer MySQL from the X Window System. It is a GUI tool that allows you to create and drop databases and manage tables. You can also use it to check whether the server is running, overload access tables, and manage threads.

Programming interfaces:

MyODBC - Implements odbc API to MySQL on Windows.

mm.mysql.jdbc - Implements the standard JDBC API (Java Database Connectivity ).

TwzJdbcForMysql - An implementation of the JDBC API for Java.

SQL language.

Structured Query Language (SQL) is used to read and write to MySQL databases. Using SQL, you can search, enter new data, or delete data. SQL is simply the foundational tool needed to interact with MySQL. Even if you use an application or graphical user interface to access the database, somewhere in the back, that application generates SQL commands.

SQL is a type of "natural language". In other words, the SQL command should read, at least at first glance, as an English sentence. This approach has both advantages and disadvantages, but the fact is that this language is very different from traditional programming languages such as C, Java or Perl. Here we will look at the SQL language as it is implemented in MySQL.

SQL Basics. 

SQL is "structured" in the sense that it follows a specific set of rules. It is easy for a computer program to parse a formulated SQL query. Indeed, O'Reilly's book "Lex & Wasss", written by J. S. Miller, has been published by J. S. Miller. Levine, T. Mason, and D. Brown implemented a SQL grammar to demonstrate the process of creating a program that interprets a language! A query is a fully defined command sent to the database server that performs the requested action. The following is an example of a SQL query:

SELECT name FROM people WHERE name LIKE Stac%'

As you can see, this sentence looks almost like a phrase in broken English: "Choose names from a list of people where the names are similar to Stac." SQL makes very little use of the formatting and special characters commonly associated with computer languages. Compare, for example, "$++;($*++/$!); $&$",,;$!" in Perl and "SELECT value FROM table" in SQL.

History of SQL. 

IBM invented SQL in the early 1970s, shortly after Dr. E. F. Codd introduced the concept of a relational database. From the beginning, SQL was an easy to learn but powerful language. It resembles a natural language such as English and therefore does not bore those who are not technical specialists.

SQL was indeed so popular with the users it was intended for that in the 1980s, Oracle released the world's first publicly available commercial SQL system. Oracle SQL was the hit of the season and spawned an entire industry around SQL. Sybase, Informix, Microsoft and a number of other companies entered the market with their own development of relational database management systems (RDBMS) based on SQL.

At the time Oracle and its competitors entered the scene, SQL was a novelty and there were no standards for it. It wasn't until 1989 that the ANSI Standards Commission released the first publicly available SQL standard. Today it is called SQL89. Unfortunately, this new standard did not delve too deeply into the definition of the technical structure of the language. Therefore, although different commercial implementations of SQL converged, differences in syntax made the task of migrating from one implementation of the language to another non-trivial. It wasn't until 1992 that the ANSI SQL standard came into its own.

The 1992 standard is referred to as SQL92 or SQL2. The SQL2 standard included as many extensions as possible added to commercial implementations of the language. Most tools that work with different databases rely on SQL2 as a way to interact with relational databases. However, due to the very wide breadth of the SQL2 standard, relational databases that implement the full standard are very complex and resource-intensive.

SQL2 is not the last word in SQL standards. With the growing popularity of object-oriented DBMSs and object-relational DBMSs (RDBMSs), there is increasing pressure to adopt object-oriented database access as the SQL standard. The answer to this problem should be SQL3. It is not yet an official standard, but now it is quite defined and can become an official standard.

With the advent of MySQL came a new approach to database server development. Instead of creating another giant RDBMS with the risk of not offering anything new in comparison with the "big guys", small and fast implementations of the most commonly used SQL functions were proposed.

SQL architecture. 

SQL is more like a natural human language than a computer language. SQL achieves this similarity through a clear imperative structure. Much like an English sentence, individual SQL commands called queries can be broken down into parts of speech. Let's look at examples:

CREATETABLEpeople (name CHAR(10))
verbsupplementextended definition
INSERTINTO peopleVALUES('me') 
verbindirect additiondirect add-on 
SELECTnameFROM peopleWHERE name LIKE '%e'
verbdirect add-onindirect additionsubordinate clause

Most SQL implementations, including MySQL, are case-insensitive: it doesn't matter in which case you enter SQL keywords, as long as the spelling is correct. For example, create from the top example can be written like this:

cREatE TablE people (name cHaR(10))

Case insensitivity applies only to SQL keywords. In MySQL, the names of databases, tables and columns are case sensitive. But this is not typical for all DBMSs. Therefore, if you are writing an application that should work with any DBMS, you should not use names that differ only in case.

The first element of a SQL query is always a verb. The verb expresses the action that the database engine should perform. Although the rest of the command depends on the verb, it always follows a common format: it specifies the name of the object on which the action is performed, and then describes the data used in the action. For example, the CREATE TABLE people (char(10)) request uses the verb CREATE followed by the addition (object) TABLE. The remainder of the query describes the table that you want to create.

The SQL query comes from the client, the application by which the user interacts with the database. The client makes a request based on the user's actions and sends it to the SQL server. The server must then process the request and perform the specified actions. After doing its job, the server returns one or more values to the client.

Because the primary purpose of SQL is to tell the database server what to do, it does not have the flexibility of a general-purpose language. Most SQL functions are related to database input and output: adding, modifying, deleting, and reading data. SQL provides other capabilities, but always with an eye to how they can be used to manipulate the data in the database.

Create and delete tables. 

After successfully installing MySQL, you can start creating your first table. A table, a structured data receptacle, is a basic concept of relational databases. Before you start entering data into a table, you must define its structure. Consider the following layout:

people
namechar(10) not null
addresstext(100)
idint

The table contains not only the names of the columns, but also the type of each field, as well as possible additional information about the fields. The data type of a field determines what kind of data it can contain. SQL data types are similar to data types in other programming languages. The full SQL standard allows for a wide variety of data types. MySQL implements most of them.

The general syntax for creating tables is as follows:

CREATE TABLE table_name (column_name1 type [modifiers]

[, column_name2 type [modifiers]] )

Which identifiers - the names of tables and columns - are valid depends on the specific DBMS. In MySQL, the length of the identifier can be up to 64 characters, let's say the symbol "$", and the first character can be a number. More importantly, however, MySQL allows the use of any characters from the local set installed on the system. For good SQL portability, avoid names that do not begin with a valid letter.

A column is a single unit of data in a table. A table can contain an arbitrary number of columns, but using large tables is inefficient. Once you have created properly normalized tables, you can join them to search data that is hosted in multiple tables. We will discuss the mechanics of combining tables later.

As happens in life, it is easier to destroy than to create. The following command deletes the table:

DROP TABLE table_name

MySQL will destroy all data in the deleted table. If you have no backup left, there is absolutely no way to undo this operation. Therefore, always keep backups and be very careful when deleting tables. One "beautiful" day it will come in handy.

In MySQL, you can delete multiple tables with a single command, separating their names with commas. For example, DROP TABLE people, animals, plants will delete these three tables. You can also use the IF EXISTS modifier to suppress an error if there is no table to delete. This modifier is useful in large scenarios designed to create a database and all of its tables. Before you create a table, run the DROP TABLE command table_name IF EXISTS.

Data types in SQL. 

Each column of the table has a type. SQL data types are similar to the data types of traditional programming languages. While many languages define the bare minimum types required for work, SQL defines additional types such as MONEY and DATE for the convenience of users. MONEY data could also be stored as one of the main numeric data types, but using a type that specifically takes into account the peculiarities of monetary calculations increases the ease of using SQL.

Table 3.4. The most common data types supported by MySQL

Data typeDescription
INTAn integer, either signed or unsigned.
REALA floating-point number. This type allows for a greater range of values than int, but does not have its accuracy.
CHAR(length)The symbol value of a fixed length. Char fields cannot contain strings longer than the specified value. Margins of shorter length are complemented by spaces.
TEXT(length)The symbol value of the variable length. TEXT is just one of several variable-sized data types.
DATEThe default date value.
TIMEThe standard time value. This type is used to store the time of day regardless of any date. When used together with a date allows you to store a specific date and time. There is an optional DATETIME type for storing the date and time together in the same field.

MySQL supports the UNSIGNED attribute for all numeric types. This modifier allows you to enter only positive (unsigned) numbers into the column. Unsigned fields have an upper limit of values twice that of their corresponding character types. Unsigned TINYINT - a single-byte numeric type of MySQL - has a range from 0 to 255, and not from -127 to 127, as in its iconic counterpart.

MySQL has more types than listed above. However, in practice, the listed types are mainly used. The size of the data you are going to store plays a much bigger role in the development of MySQL tables.

Numeric data types. Before you create a table, you should have a good idea of what kind of data you will store in it. In addition to the obvious decision as to whether this is numeric or symbolic data, you should find out the approximate size of the stored data. If this is a numeric field, what will be the maximum value? Could it change in the future? If the minimum value is always positive, you should consider using an unsigned type. You should always choose the smallest number type that can store the largest conceivable value. If you wanted to store the population of the state in the field, you would have to choose an unsigned INT. There cannot be a negative population in the state, and in order for an unsigned field such as INT to accommodate a number representing its population, the population of the state should be approximately equal to the population of the whole Earth.

Character types. 

Character types are a bit more difficult to work with. You should think not only about the maximum and minimum line length, but also about the average size, the frequency of deviation from it, and the need for indexing. In this context, we refer to the index as the field or group of fields you want to search—basically, in the WHERE clause. Indexing, however, is significantly more complex than such a simplified definition, and we'll deal with it later. It is important to note here that indexing on character fields is much faster if they have a fixed length. If the length of the strings doesn't fluctuate too much, or better yet, is constant, then it's probably best to choose the CHAR type for the field. A good candidate for the CHAR type is the country code. The ISO standard defines two character codes for all countries. CHAR(2) would be the right choice for this field.

To be suitable for the CHAR type, the field does not have to be a fixed length, but the length does not have to fluctuate much. Phone numbers, for example, can be safely stored in the CHAR(13) field, although the length of the numbers varies from country to country. It's just that the difference isn't that great, so it doesn't make sense to make the field for the phone number variable in length. With respect to a CHAR field, it is important to remember that, regardless of the actual length of the stored string, the field will have exactly as many characters as specified in its size - no more and no less. The difference in length between the size of the saved text and the size of the field is filled with spaces. Don't worry about a few extra characters when storing phone numbers, but you wouldn't want to waste a lot of space in some other cases. To do this, there are variable-length text fields.

A good example of a field that requires a variable-length data type is the Internet URL. For the most part, Web addresses take up relatively little space — http://www.ora.com, http://www.hughes.com.au, http://www.mysql.com — and are not a problem. Sometimes, however, you can stumble upon addresses of this kind:

http://www.winespectator.com/Wine/Spectator/notes\5527293926834323221480431354? XvlI=&Xr5=&Xvl =&type-region-search-code=&Xa14=flora+springs&Xv4=.

If you create a CHAR field of length sufficient to store this URL, then almost every other stored URL will waste a very significant amount of space. Variable-length fields allow you to specify such a length that it is possible to store unusually long values, and at the same time no space is wasted when storing ordinary short quantities.

Variable-length fields. 

The advantage of variable-length text fields is that they use exactly as much space as is needed to store a single value. For example, a field of type VARCHAR(255), which stores the string "hello, world", occupies only twelve bytes (one byte for each character plus another byte to store the length). Unlike the ANSI standard, in MySQL varchar fields are not filled with spaces. Before writing, extra spaces are removed from the line.

You cannot save rows that are longer than the specified field size. In the VARCHAR(4) field, you can save a string no longer than 4 characters. If you try to save the string "happy birthday", MySQL will shorten it to "happ". The downside to MySQL's approach to storing variable-length fields is that there is no way to save an unusual string whose length exceeds the value you set. Table 3.5 shows the amount of space required to store the 144-character URL shown above and the regular, 30-character URL.

Table 3.5. Memory space required for various MySQL character types

Data typeSpace to store a string of 144 charactersSpace to store a 30-character stringMaximum string length
SNAR(150)   
VARCHAR(150)   
TINYTEXT(150)   
TECHT(150)   
MEDIUMTEXT(150)   
LONGTEXT(150)   

If, after years of working with your database, you find that the world has changed, and a field that feels comfortable in the VARCHAR(25) type must now contain 30-character strings, all is not lost. MySQL has an ALTER TABLE command that allows you to override the size of a field without losing data.

ALTER TABLE mytable MODIFY mycolumn LONGTEXT

Binary data types. 

In MySQL, there are a number of binary data types corresponding to their symbolic counterparts. The binary types supported by MySQL are CHAR BINARY, VARCHAR BINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The practical difference between character types and their binary counterparts is based on the principle of encoding. Binary data is simply a piece of data that MySQL doesn't try to interpret. In contrast, character data is assumed to represent text data from human-used alphabets. Therefore, they are encoded and sorted based on the rules corresponding to the character set in question. MySQL sorts binary data in a case-insensitive ASCII order.

Enumerations and sets. 

MySQL provides two more special types of data. The ENUM type allows you to specify a list of possible values for a field when you create a table. For example, if you had a column with the name "fruit" in which you would allow only the values "apple", "orange", "kiwi" and "banana" to be placed, it should be assigned the type ENUM:

CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY,

ENUM fruit('apple', 'orange', 'kiwi', 'banana'))

When you write a value in this column, it must be one of the listed fruits. Since MySQL knows in advance what values are valid for this column, it can abstract them with some numeric type. In other words, instead of storing "apple" as a string in the column, MySQL replaces it with a single-byte number, and you see "apple" when you access the table or output the results from it.

The MySQL SET type allows you to store multiple values in a field at the same time.

Other data types.

 Any conceivable data can be stored using numeric or character types. In principle, even numbers can be stored in symbolic form. However, just because it can be done doesn't mean it has to be done. Consider, for example, how to store sums of money in a database. You can do this using INT or REAL. While intuitively REAL may seem more appropriate —after all, monetary amounts need decimal places—it's actually more accurate to use INT. In fields containing floating-point values, such as REAL, it's often impossible to find a number with an exact decimal value. For example, if you enter the number 0.43, which should represent the sum of $0.43, MySQL might write it as 0.42999998. This small difference can cause problems when performing a large number of mathematical operations.

MySQL provides special data types for such sums of money. One is the MONEY type, the other is DATE.

Indices. Although MySQL provides better performance than any large database servers, some tasks still require caution when designing a database. For example, if a table contains millions of rows, it will probably take a long time to find the right row in it. In most databases, search is facilitated by a tool called an index.

Indexes help you store data in your database in a way that allows for quick searches. Unfortunately, for the sake of search speed, you have to sacrifice disk space and the speed of data change. It's most efficient to create indexes for the columns you're most likely to search. MySQL supports the following syntax for creating indexes:

CREATE INDEX index_name ON tablename (column1, column2, columnN)

MySQL also allows you to create an index at the same time as the table is created, using the following syntax:

CREATE TABLE materials (id INT NOT NULL,

name CHAR(50) NOT NULL,

resistance INT,

melting_pt REAL,

INDEX index1 (id, name),

UNIQUE INDEX index2 (name))

This example creates two indexes on a table. The first index1 consists of the id and name fields. The second index includes only the name field and specifies that the values of the name field must be unique. If you try to insert a value in the name field that is already in that field in any string, the operation will fail. All fields specified in the unique index must be declared as NOT NULL.

Although we created a separate index for the name field, we did not create an index separately for the id field. If we need such an index, we do not need to create it - it already exists. When an index contains more than one column (e.g., name, rank, and serial_number), MySQL reads the columns in left-to-right order. Thanks to the index structure used by MySQL, any subset of columns on the left edge automatically becomes an index within the "main" index. For example, when you create an index of name, rank, serial_number, the "free" indexes name and name are also created along with rank. However, the rank or name and serial_number indexes are not created unless explicitly required.

MySQL also supports ANSI SQL semantics for a special index called a primary key. In MySQL, a primary key is a unique index named PRIMARY. When you create a table, you assign a column as a primary key, you make it a unique index that will support table joins. The following example creates a cities table with the primary key id.

 

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY,

name VARCHAR(100),

pop MEDIUMINT,

founded DATE)

Before you create a table, you need to decide which fields will be keys (and whether there will be keys at all). As mentioned, any fields that will participate in the merging of tables are good candidates for the role of primary key.

Sequences and auto-incrementation. 

It is best when the primary key has no value in the table other than the primary key value. To achieve this, the best way is to create a numeric primary key, the value of which is incremented when a new row is added to the table. If we go back to the example with the cities table, then the first city you enter should have an id of 1, the second - 2, the third - 3, etc. To successfully manage such a sequence of primary keys, you need to have some means to ensure that at a given moment only one client can read the number and increase it by one. In a transaction database, you can create a table, say, named sequence, that contains a number that represents the next id. When you need to add a new row to a table, you read the number from that table and insert a number one larger. For this scheme to work, you need to be sure that no one else can read from the table until you have entered a new number. Otherwise, two clients can read the same value and try to use it as the primary key value in the same table.

MySQL does not support transactions, so the described mechanism cannot be used to generate unique numbers. Using the MySQL LOCK TABLE command for this purpose is cumbersome. Nevertheless, the DBMS provides its own version of the concept of sequence, allowing you to generate unique identifiers without worrying about transactions.

Sequence. 

When creating a table in MySQL, you can specify one of the columns as a AUTO_INCREMENT. In this case, when you add a new row that has a null value or 0 in this column, it will automatically be replaced with a value one greater than the largest current value in the column. The column with the modifier AUTO_INCREMENT must be indexed. The following is an example of using a field of type AUTO_INCREMENT:

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100),

pop MEDIUMINT,

founded DATE)

The first time you add a row, the id field is set to 1 if the INSERT command uses a null or 0 value for it. For example, the following command uses the AUTO_INCREMENT feature:

INSERT INTO cities (id, name, pop)

VALUES (NULL, 'Houston', 3000000)

If you run this command when there are no rows in the table, the id field will be set to 1 instead of null. In the case where there are already rows in the table, the field will be assigned a value of 1 greater than the largest id value at the moment.

Another way to implement sequences is to use the value returned by the LAST_INSERT_ID function:

UPDATE table SET id=LAST_INSERT_ID (id+1);

Data management. The first thing you do when you create a table is add data to it. If the data already exists, you may need to change or delete it.

Add data. Adding data to a table is one of the simplest SQL operations. You've already seen a few examples of this. MySQL supports the standard INSERT syntax:

INSERT INTO table_name (column1, column2, ..., columnN)

VALUES (value1, value2, ..., valueN)

The data for the numeric fields is entered as they are. For all other fields, the input is enclosed in single quotation marks. For example, to enter data into the address table, you can run the following command:

INSERT INTO addresses (name, address, city, state, phone, age)

VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY', '(800) 555-1234', 26)

In addition, the control character - by default '\' - allows you to enter single quotation marks in literals and the character '\' itself:

# Enter data in Stacie's Directory, which is located

# in c:\Personal\Stacie

INSERT INTO files (description, location)

VALUES ('Stacie\'s Directory', 'C:\\Personal\\Stacie')

MySQL allows you to omit column names if the values are set for all columns and in the order in which they were specified when the table was created by the CREATE command. However, if you want to use the default values, you need to specify the names of those columns in which you enter values other than the default values. If a column is not set to a default value and it is defined as NOT NULL, you must include the column in an INSERT command with a value other than NULL. MySQL allows you to specify a default value when creating a table in the CREATE command.

New versions of MySQL support INSERT to insert multiple rows at the same time:

INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),

(NULL, 'Bananas', 122, 0, 4, 29),

(NULL, 'Liver', 232, 3, 15. 10)

Although the non-standard syntax supported by MySQL is useful for quickly performing administrative tasks, you should not use it unnecessarily when writing applications. As a general rule, you should stick to the ANSI SQL2 standard as closely as MySQL allows. Thanks to this, you get the opportunity to move to some other database in the future. Portability is especially important for those with medium-sized needs, as such users expect to move to a full-scale database someday.

MySQL supports SQL2 syntax, which allows you to enter the results of a query into a table:

INSERT INTO foods (name, fat)

SELECT food_name, fat_grams FROM recipes

Notice that the number of columns in INSERT corresponds to the number of columns in SELECT. In addition, the data types of the columns in INSERT must match the data types in the corresponding SELECT columns. Finally, the SELECT clause inside an INSERT command must not contain the ORDER BY modifier and cannot fetch from the same table into which data is inserted with the INSERT command.

Change data. 

If your database is not a read-only database, you will probably need to modify the data periodically. The standard SQL command for modifying data looks like this:

UPDATE table_name

SET column1=value1, column2=value2, ..., columnN=valueN

[WHERE clause]

MySQL allows you to calculate the assigned value. You can even calculate a value using the value of another column:

UPDATE years

SET end_year = begin_year+5

In this command, the value of the end_year column is set to the value of the begin_year column plus 5 for each row in the table.

WHERE clause. 

You may have already noticed the WHERE clause. In SQL, the WHERE clause allows you to select table rows with a specified value in a specified column, for example:

UPDATE bands

SET lead_singer = 'Ian Anderson'

WHERE band_name = 'Jethro Tull'

This command - UPDATE - specifies that you need to change the value in the lead_singer column for those rows in which the band_name coincides with "Jethro Tull". If the column in question is not a unique index, the WHERE clause can correspond to multiple rows. Many SQL commands use the WHERE clause to select the rows on which to perform operations. Because the columns involved in the WHERE clause are searched, you should have indexes on the combinations that are commonly used.

Removal. 

To delete data, you simply specify the table from which you want to delete rows, and in the WHERE clause specify the rows that you want to delete:

DELETE FROM table_name [WHERE clause]

As with other commands that allow the use of the WHERE clause, its use is optional. If the WHERE clause is omitted, all records will be deleted from the table!

Enquiries. The most commonly used SQL command is the one that allows you to view the data in the database: SELECT. Data is entered and modified only on an ad hoc basis, and most databases are mostly busy providing data for reading. The general appearance of the SELECT command is as follows:

SELECT column1, column2, ..., columnN

FROM table1, table2, ..., tableN

[WHERE clause]

This syntax is most commonly used to retrieve data from a database that supports SQL. There are different options for performing complex and powerful queries.

The first part of the SELECT command lists the columns you want to retrieve. You can set "*" to indicate that you want to extract all columns. The FROM clause specifies the tables in which these columns are located. The WHERE clause specifies which rows should be used and allows you to define how two tables should be merged.

Merge. 

Joins introduce "relationality" into relational databases. It is the join that allows you to map a row of one table to a row of another. The main type of association is what is sometimes called an inner union. Joining tables consists of equating the columns of two tables:

SELECT book, title, author. name

FROM author, book

WHERE book.author = author.id

Consider a database in which the book table looks like table 3.6.

Table 3.6. Workbook table

IDTitleAuthorPages
 The Green Mile  
 Guards, Guards!  
 Imzadi  
 Gold  
 Howling Mad  

And the author authors table looks like table 3.7.

Table 3.7. Table of authors

IDNameCitizen
 Isaac AsimovUS
 Terry PratchetUK
 Peter DavidUS
 Stephen KingUS
 Neil GaimanUK

Internal joining creates a table that combines the fields of both tables for rows that match the query in both tables. In our example, the query specifies that the author field in the book table must match the id field of the author table. The result of this query is shown in Table 3.8.

Table 3.8. Internal aggregation query results

Book TitleAuthor Name
The Green MileStephen King
Guards, Guards!Terry Pratchet
ImzadiPeter David
GoldIsaac Asimov
Howling MadPeter David

There is no author named Neil Gaiman in these results because his author.id not found in the book.author table. Inner joining contains only those strings that exactly match the query. Below we will discuss the concept of external unification, which turns out to be useful in the case when a writer who does not have books in this database is included in the database.

Aliases. 

Full names that contain the names of tables and columns are often very cumbersome. Also, when using the SQL functions we'll discuss below, it can be difficult to reference the same function more than once within the same command. Aliases, which are usually shorter and more expressive, can be used instead of long names within a single SQL command, for example:

# Column alias

SELECT long_field_names_are_annoying AS myfield

FROM table_name

WHERE myfield = 'Joe'

# Table alias in MySQL

SELECT people.names, tests.score

FROM tests really_long_people_table_name AS people

Grouping and ordering. By default, the order in which the results of the sample appear is not defined. Fortunately, SQL provides some means of bringing order to this random sequence. The first means is ordering. You can require the database to organize the outputs into a column. For example, if you specify that a query should order the results by the last_name field, the results are displayed alphabetically by the value of the last_name field. Ordering is done using the ORDER BY clause:

SELECT last_name, first_name, age

FROM people

ORDER BY last_name, first_name

In this case, the ordering is carried out in two columns. You can arrange any number of columns, but all of them must be specified in the SELECT clause. If we hadn't selected the last_name field in the previous example, we wouldn't have been able to organize it.

Grouping is an ANSI SQL feature implemented in MySQL. As the name suggests, grouping allows you to combine strings with similar values into one for the purpose of their joint processing. This is usually done to apply aggregate functions to the results. We'll talk about the features a little bit later.

Let's look at an example:

mysql> SELECT name, rank, salary FROM people\g

nameranksalary
Jack SmithPrivate 
Jane WalkerGeneral 
June SandersPrivate 
John BarkerSergeant 
Jim CastleSergeant 

5 rows in set (0.01 sec)

After grouping by rank, the issue changes:

mysql> SELECT rank FROM people GROUP BY rank\g

rank
General
Private
Sergeant

3 rows in set (0.01 sec)

After applying the grouping, you can finally find the average salary for each rank. We'll talk about the functions used in this example later.

mysql> SELECT rank, AVG(salary) AS income FROM people GROUP BY rank\g

rankincome
General 
Private 
Sergeant 

3 rows in set (0 04 sec)

Ordering and grouping, combined with the use of SQL functions, allows you to perform a large amount of data processing on the server before retrieving it. But with this power