MySQL

commands are entered at

the mysql> prompt

exit from mysql

quit at the mysql> command-prompt

What is SQL?

As discussed previously, a database management system (DBMS) provides the means to access the data stored in a database. One of the key methods for achieving this is via a language called the Structured Query Language. This is usually abbreviated to SQL and pronounced Sequel. SQL is essentially a very simple and easy to use language designed specifically to enable the reading and writing of database data. Because SQL contains a small set of keywords it can be learned quickly. In addition, SQL syntax is more or less identical between most DBMS implementations, so having learned SQL for one system, it is likely that your skills will transfer to other database management systems.

Create Database command

CREATE DATABASE MySampleDB; The CREATE SCHEMA statement may also be used for this purpose.

-?, --help

Display this help and exit.

-B, --batch

Don't use history file. Disable interactive behavior. (Enables --silent)

--auto-rehash

Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash.

mysql -h myDBServer -p 5678

If the mysql client is running a different system to the MySQL server, the -h flag may be used to specify the name of the remote host together with -P to specify the port:

-I, --help

Synonym for -?

MySQL Database Architecture

There are two flavors of Database Management System (DBMS) known as shared-file and client-server.

Usage: mysql [OPTIONS] [database]

command-line options:

Columns and Datatypes

Each column represents a data field in the corresponding table. For example, the name, address and telephone data fields of a table are all columns. Each column, in turn, is defined to contain a certain datatype which dictates the type of data the column can contain. A column designed to store numbers would, therefore, be defined as a numerical datatype.

Primary Keys

Each database table must contain one or more columns that can be used to uniquely identify each row in the table. For example, a table may use a bank account number column as the primary key. Alternatively, a customer table may use the customer's social security number of the primary key. Without a primary key it would not be possible to retrieve or delete a specific row in a table because there can be no certainty that the correct row has been selected. Primary keys can comprise a single column or multiple columns in a table. To qualify as a single column primary key, no two rows can contain matching primary key values. When using multiple columns to construct a primary key, individual column values do not need to be unique, but all the columns combined must be unique. Finally, whilst primary keys are not mandatory in database tables their use is strongly recommended.

Database Rows

Each new record that is saved to a table is stored in a row. Each row, in turn, consists of the columns of data associated with the saved record. Each entry in a customer table is equivalent to a row in a spreadsheet and each column contains the data for each customer (name, address, telephone etc). When a new customer is added to the table, a new row is created and the data for that customer stored in the corresponding columns of the new row. Rows are also sometimes referred to as records and these terms can generally be used interchangeably.

client-server DBMS

MySQL falls into the client-server DBMS category. A client-server DBMS is split into two components. The server component typically resides on the same physical computer as the database files and is responsible for all interactions with the database. The second component is the client. The client sends all database requests to the server which in turn processes the request and returns the results of the request back to the client.

-A, --no-auto-rehash

No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. WARNING: options deprecated; use --disable-auto-rehash instead.

Each table in a database

assigned a name which must be unique within that particular database. A table name, once assigned to a table in one database, may only be re-used within the context of a different database

mysql -u john -p

command will prompt for the password for user john before allowing access to the database management system. if your database server requires a username and password to gain access the -u and -p command-line options

shared file based DBMS

database access application which interacts directly with the underlying database files. These types of database are typically designed for less demanding data storage needs and are used almost exclusively on desktop computers. Microsoft Access is a typical example of this category of DBMS. Such database systems are never used in distributed or enterprise level environments.

What is a Database?

entity that stores the actual data (such as ID numbers, names and addresses for example) in a structured way.

The mysql Command-line Utility

mysql is a command-line client tool that is installed as standard with the MySQL package. From the mysql command-prompt it is possible to issue a wide range of commands to the database server such as creating and deleting databases and tables, searching for data, adding new rows and much more. Throughout this book the capabilities of the mysql tool will be covered in great detail.

Database Tables

provide the most basic level of data structure in a database. Each database can contain multiple tables and each table is designed to hold information of a specific type. For example, a database may contain a customer table which contains the name, address and telephone number for all the customers of a particular business. The same database may also include a products table used to store the product descriptions with associated product codes for the items sold by the business.

Commands are terminated by

semi-colon (;). If a command is not terminated by a semi-colon, pressing enter simply continues the current command on the following line.

database management system (DBMS)

software used to store, access and manipulate the data stored in the database. All interactions with the database are always performed via the DBMS.

Database Schema

the characteristics of the data stored in a database table. For example, the table schema for a customer database table might define that the customer name is a string of no more than 20 characters in length, and that the customer phone number is a numerical data field of a certain format. Schema are also used to define the structure of entire databases and the relationship between the various tables contained in each database.

key advantages to the client-server architecture DBMS

Firstly, there is no need for the client to be running on the same computer system as the server. Instead, requests can be sent by the client over a network or internet connection to the server on a remote host. The fact that the server resides on a remote computer is invisible to the client user. This makes the database available to greater numbers of users than a shared-file DBMS offers. In large scale enterprise environments this also allows high levels of fault tolerance and load balancing to be implemented. Secondly, separating the client from the server allows a wider range of client types to be used to access the database. Valid clients can be the MySQL tools, applications written in other programming languages such as C, C++ or Java, or web based applications developed using languages such as PHP or JSP.