MySQL Basics

Chapter 2 - What is MySQL?
byGareth Downes-Powellet al.
Wrox Press 2003

Now that you have MySQL running, we can configure it.

The MySQL Console

The most basic tool to connect to your MySQL server is called MySQL. On Windows, it is called mysql.exe. The MySQL documentation also refers to this tool as the MySQL monitor.

This tool lets you type all your command and queries, and will return their results. As MySQL can be completely configured with SQL commands, you should be able to issue all the commands you'll need with this tool.

First you will need to find the location of this tool. It is situated in the bin subdirectory of your main install. If you are on Windows and installed MySQL in C: \MySQL, you will find it in C: \MySQL\bin. Next, open a command prompt, go into the bin directory, and type: mysql.exe.

The MySQL console welcome message should look like:

You should be in the prompt of the MySQL monitor, which is where you will type all of your SQL commands, or queries. If haven't configured any users yet, you are running this connection as root. Therefore you have access to every database and all tables.

Note 

The MySQL Monitor can execute multiple queries in one command. All queries you enter in the MySQL monitor must end with a semicolon. But when you'll be writing SQL query in Dreamweaver MX or any other tool, you generally do not have to end them with a semicolon unless you want to execute more than one.

Introduction To Databases and SQL

First of all, we need to understand the database concept: in MySQL a Database is a collection of data and structures that are grouped under a regular name. The collections of data and structure that are stored in a MySQL database are called tables. The tables are the container of all the data in your database. These tables are structured in a list of column and rows, where the columns define the meaning of the data and the rows contains the data themselves.

The following diagram shows you a representation of a Server, with its database, and for each database, the table. In this schema, the server holds three databases: hotel, planning, and projects, with for each database a different number of tables and a different organisation.

When you will be working with a database-oriented application, like the hotel reservation system that we will be looking at later on the book, you will focus on one database only. You will fundamentally work with the following database concepts:

• Tables

• Indexes

• Data (also called a RecordSet in Dreamweaver)

A table is a collection of data structured into columns and rows. You can think to it as a matrix of data. You will generally design the columns of a table and then fill it with rows containing the data.

Example of a Database

As an introductory example, we will present you how you could design a database to handle a company's employee and their working department. This company is called GlassMedia.

Suppose we have two departments in GlassMedia: the sales division and the marketing division. This company employs five people: John, Sally, Gene, Allan, and Robert.

Robert and Allan both work in the sales department and John, Sally, and Gene work in the marketing department.

To represent this information in a SQL server, we will construct one database called 'glassmedia', with two tables: 'employees' and 'department'.

The following table shows you the conceptual representation of the database and its data.

As you can see, the database holds two tables: 'employees' and 'department'. The table 'employees' has two columns: The name of the employee, and the department name into which this employee works. The table 'department' contains the list of the department and a short description of the department.

Suppose now that the company is going well and the sales department wants to hire a new employee. How would we update the database to store the new person's information?

We would just have to create a new record in the table 'employees' containing the new person's name, and the department 'sales'. We don't have to modify the table 'department', as there is no change in the department configuration of the company.

This little example shows you that you will represent an information concept (like an employee or a department) with a table. The column will represent all information you want to store about the concept, while a row will contain an instance of that concept. In the table 'employees', we have five rows, each containing the information related to one and only one employee.

This way, if some information changes for an object, others don't have to be changed as well.

The data stored in your database is stored in the table. You will access them through regular SQL queries. Most of your job when using the SQL server to design a database-oriented application will be to create the queries to interact with the data. Data access is done with the following four types of queries:

• SELECT - Returns a set of data from a table

• INSERT - Inserts new data into the database

• UPDATE - Sets a new value for existing data in a table

• DELETE - Deletes a record from a table

Selecting the Working Database

Every time you work with MySQL you must first specify which database you want to work on. First you need to know which databases are on your server. Issue the following SQL query in your MySQL Monitor:

     SHOW DATABASES; 

You will get a result like this:

    MySQL> SHOW DATABASES;    +-------------+    | Database      |    +-------------+    | MySQL         |    | test         |    +-------------+    2 rows in set (0.01 sec) 

In this result you can read that the server has two databases: MySQL and test.

To create a database issue the SQL command:

     CREATE DATABASE databasename; 

where databasename is the actual name of your database. In our company example, we will use the database glassmedia. To create the glassmedia database, issue the following command:

     CREATE DATABASE glassmedia; 

This query will create a new database called glassmedia. You can now use that database. To use a database you will use the SQL command:

     USE databasename; 

In our example, the SQL command will be:

     USE glassmedia; 

Now, you are ready to use the database.

Note that the USE databasename SQL command will automatically switch the current working database to a new one. When you start the MySQL monitor, unless you specified the database in the command line, it won't know which database you want to use. Therefore the very first SQL statement you must issue is the USE command. After that, you don't need it anymore, unless you want to switch to another database.

Tables in MySQL

In this section we will give a brief overview of how to create a table, how to delete it and how to retrieve information for an existing table. In-depth coverage of the creation of a table will be given in Chapter 7.

Creating a Table

Tables are created using the CREATE TABLE SQL command. To create the table 'employees' from our above example, we will issue the following SQL command:

     CREATE TABLE employees (          name varchar(32),          department varchar(32)     ); 

This command will create a table named 'employees' with two columns: name and department. These two columns are of the type varchar and of size 32. The varchar field type can be used to store a string of characters. When specifying the size 32, it means that the number of characters in an employee's name cannot be greater than 32.

To create the table 'department' we would issue the following SQL statement:

     CREATE TABLE department (          name varchar(32),          description text     ); 

In this case, the column 'description' allows for a larger size.

To complete the example, suppose we wanted to create a table with more information about the employee, with the salary, we would do it this way:

    CREATE TABLE employees (         name varchar(32),         salary integer,         department varchar(32)    ); 

In this query, we see that the type of the column 'salary' is integer. This type indicates to MySQL that the value of this column can be only an integer number.

As far as we've illustrated, the CREATE TABLE syntax is:

     CREATE TABLE tablename (          field1 type1,          field2 type2,          ... ...,     ); 

As you can see, the CREATE TABLE statement requires the new table name, and a list of column definitions.

Deleting a Table

The SQL command to delete a table is:

     DROP TABLE tablename 

where tablename is the name of the table you want to delete. In our glassmedia database, to delete the table 'employees', we would use:

     DROP TABLE employees; 

Retrieving the Table Description

To retrieve the structure of an existing table, also called its definition, use the SQL command DESC as follows:

     DESC tablename; 

where tablename is the name of an existing table.

In the glassmedia database, if we want to know about the table 'employees', we would use the following:

     DESC employees; 

If you issue this SQL command on the table 'employees', you will get the following result in the MySQL Monitor:

Configuring MySQL

As said previously, MySQL configuration is generally done via SQL queries. We will review some basic configuration options that will let you be ready for using it as a production environment. We will review the following configuration tasks:

• Configuring the MySQL database

• Configuring the privileges system

• Creating a user.

Note 

The following commands are SQL queries and should be executed in the MySQL Monitor. The ending semicolon is included to help you in this software, but you should know that this character is used only to indicate to the MySQL Monitor the end of your SQL query.

The MySQL Database

In your MySQL monitor, issue the following sequence of SQL commands:

     USE mysql;     SHOW TABLES; 

The SHOW TABLES command will return the list of the table in the working database. The 'mysql' database has the following tables:

• columns_priv

• db

• func

• host

• tables_priv

• user

These tables contain the MySQL user and privileges functionality. They are organized in an hierarchical manner.

• The table user is the host global privileges table. It contains the list of all users allowed to connect to your MySQL server, and their global privileges. Global privileges have precedence over all other privileges that you may specify in the other tables.

• The table host and db contain the information about the database access from a specific host. For example a user that connects from the local host can have access to every database, while if he connects from the network, have access to only some specific database, but it is still the same user, with the same password.

• The table tables_priv gives users access to specific table. In our glassmedia example, we defined the table 'employees' with the salary of the people. This information is sensitive, and you may decide that all users cannot have access to that table except the boss. This table will contain the definition for these users.

• The table columns_priv gives users access to specific columns. Again, we can increase the granularity of the restrictions on the table 'employees'. Finally you may decide to let all users use that table, but they can't look at the 'salary' column, except for the boss.

To create a user in MySQL, you will handle the table user, and eventually give him the privileges you want. The MySQL privileges system is extremely flexible.

The Privileges System

In MySQL, the privileges system is based on the four tables: user, host and db, tables_priv, and columns. They are shown in order of priority.

The following picture shows a representation of the privileges level:

If a privilege is given in a higher level it will supersede the lower-level restriction you may have set. For example, if a user has the SELECT privileges on the User table, it will be able to do the SELECT on each database, each table, and each column of your database.

The Privileges Table Explained

The user table will contain the list of users allowed to connect to the server. It will also set the location from which they are allowed to connect. The host table gives access to a specific database only. The first level of security is about the connection itself. When a client tries to connect, MySQL will first look in the user table to see if it can connect, without even knowing about the database you're trying to access.

The following table present a brief description of the structure of the USER table, but the other tables work almost the same way.

Host	This column will contain the hostname from which the user is allowed to connect. If you want the user to be able to connect from everywhere, use the "%" value.
User	The username.
Password	The encrypted password. If you want to set up your own user directly by using this table, you need to use the SQL function PASSWORD() to make sure the password will be MySQL-encrypted.
Select_priv	These four privileges are the regular SQL query commands. Remember that if you set the value "Y" here, the user will be able to execute an SQL statement on every database.
Insert_priv
Update_priv
Delete_priv
Create_priv	These privileges concern the CREATE and DROP statement.
Drop_priv	Note that the difference between a DELETE and a DROP statement is that the DELETE will affect only records in a table and leave the structure of the table unmodified, while the DROP statement will destroy completely the table or database.
Reload_priv	These privileges concern the MySQL server process. Giving these permissions to a user is rarely needed. Only the "root" superuser should have them set to "Y"
Shutdown_priv
Process_priv
File_priv
Grant_priv	This privilege allows the user to grant privileges to other users.
References_priv	This privilege is not yet implemented.
Index_priv	Allows the user to create an index.
Alter_priv	Allows the user to alter the table structure.

Allowing and Revoking Privileges

The SQL GRANT statement will help you manage all the privileges you may want to set up on a user.

To illustrate the concept of privileges, we will use the example of the boss and an employee account. We want the boss account to have access to any database from everywhere, while the employee's account to have access to the database 'glassmedia' only, but he can access from everywhere.

To grant all privileges to the boss account, use the following SQL command:

     GRANT ALL PRIVILEGES on * TO boss IDENTIFIED by 'mypasswordisnotgood' 

This command will create an entry to the table 'host', with all privileges set to 'Yes'. This user will be able to connect to the MySQL server from anywhere, with the username 'boss' and the password 'mypasswordisnotgood'.

Why isn't there any other entry? Remember the rule to check if a user has privileges. The first element we check is if the privilege is in the host table. If it is, no need to check further, this user has the privilege, therefore we won't need to specify the privileges for the boss account on lower levels.

Note that if the user does not exists when you issue the GRANT statement, MySQL will automatically insert it in the table 'user'.

Now, we will create the employee's account, called 'emplaccount' with password 'anewbadpassword'. Creating this account is a little bit more complicated. We want the employee account to be able to connect to the server, but be able to use only the database 'glassmedia'. In addition, the account has access to all tables in this database, but for the table 'employees', we don't want him to be able to SELECT the column 'salary'. He will therefore have access to other columns of the table 'employees'.

To create the employee's account, use the following query:

     GRANT SELECT (name,department) ON glassmedia.employees TO emplaccount IDENTIFIED     BY 'anewbadpassword';     GRANT ALL PRIVILEGES ON glassmedia.department TO emplaccount IDENTIFIED BY     'anewbadpassword'; 

Note that there are two queries in the above statement. The first is to give the SELECT privileges on the columns 'name' and 'department' in the table 'employees' from the database 'glasshaus'. While the second gives all access to the table 'department' in the same database. If you create another table later, you will need to grant the account 'emplaccount' access to that table, by using another GRANT statement.

When using the GRANT statement, you should know that MySQL will act by supposing that for a specific level of privileges, everything you don't mention explicitly is granted.

For example, in the first GRANT statement, we didn't specify the host from where he can access the server. Then, if it is not specified, he can access from everywhere. On the second GRANT statement, we did not specify any column name, and therefore all privileges are granted on all columns of the table department.

Now suppose we issue the following statement:

     GRANT ALL PRIVILEGES ON glassmedia TO emplaccount IDENTIFIED by 'anewbadpassword'; 

There, we did not mention any table on the 'glassmedia' database; therefore the user has all privileges on all tables.

The only element on which MySQL will not act this way is for the list of privileges. You always have to specify the exact list of privileges you want to give to a user, either by specifying 'ALL PRIVILEGES' to set all privileges, or by specifying them by hand.

The names for the different privileges that GRANT understand are as follows:

 ALL PRIVILEGES          FILE          RELOAD ALTER                   INDEX         SELECT CREATE                  INSERT        SHUTDOWN DELETE                  PROCESS       UPDATE DROP                    REFERENCES    USAGE 

They are represented by the keyword priv_type in the GRANT syntax below.

The GRANT syntax explained:

     GRANT priv_type [ (column_list) ] [, priv_type [ (column_list)] ...]     ON {tbl_name | * | *.* | db_name.*}     TO user_name [IDENTIFIED BY "password"][, user_name [IDENTIFIED BY "password"]]     [WITH GRANT OPTION] 

We will analyze each part of the GRANT SQL command:

    GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] 

This first part is the list of privileges you are granting to the user. The column_list element is a list of column specifiations. The list is separated by the" , " character. Example: GRANT SELECT, UPDATE (dwmxhotel.*), DELETE (*.*)

    ON {tbl_name | * | *.* | db_name. *} 

The ON sequence identifies the database and table you are granting privileges on. You can use the table name, alternatively the * symbol means all tables, the *.* means all tables on each database and db_name. * means all tables on the specific database. If no database is specified, it will use the current working database. Example: ON dwmxhotel. *.

    TO user_name [IDENTIFIED BY "password"][, user_name [IDENTIFIED BY "password"]] 

This part represents the list of user you're working with. The list is separated by the "," character. The user_name element can be described with wildcards. The syntax for the user is user@host. If you want to let the user dwmxphp access from any host, you will use: dwmxphp@' %' (Note the ' characters).

The IDENTIFIED BY "password" is the plaintext password. MySQL will automatically encrypt the password when manipulating the 'privileges' tables.

When Do Changes Take Effect?

When the MySQL server starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. The server notices modifications to the 'grant' tables that you perform using GRANT or REVOKE immediately. If you modify the 'grant' tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement to tell the server to reload the 'grant' tables.