21 MySQL

Hiren Joshi

 

MySQL is an open source relational database management system (RDBMS). As like other RDBMS, MySQL support to store and retrieve data. MySQL allows working multiple users simultaneously. It supports that only authorized users can access the data. MySQL uses the standard query language SQL (Structured Query Language). MySQL is considered as the world’s most popular open source database.

 

MySQL available under two license schemes.

 

1. It can be available as open source license (GPL – General Public License)

2. It can be purchased as commercial license.

 

Generally, PHP and MySQL are used to develop dynamic websites as both the soft wares are open source. Both these software work on majority of Operating Systems.

 

MySQL strengths

 

MySQL has many strengths like

  • Performance
  • Portability
  • Easy to learn
  • Support for MySQL is available
  • Low cost

 

Work with MySQL

 

MySQL allows to work with GUI (Graphical User Interface) or command-line interface. To work with MySQL command line, there is option named MySQL console. MySQL console is available in MySQL menu of WAMP server from status bar.

 

As you click on MySQL, the server asks password. By Default, there is no password, so you need to just press the enter key.

 

To login from another machine to MySQL type,

 

Mysql –h <hostname> -u <username>  -p

 

Where –p means password.

 

To use MySQL in GUI, click phpMyAdmin menu of WAMP server from status bar. PhpMyAdmin is a web-based client of MySQL.

 

To work with MySQL, you should have access to MySQL. To access MySQL, the privilege should be assign to user. The user can access the MySQL by using username and password.

 

Let us create a database named Account in MySQL. The database name is Account which having following tables.

 

Vendor (vid, vname, vaddress, vcity,vmobile)

   Invoice (iid,vid,inumber,idate,itotal,ptotal,ctotal,pdate)

 

To create a database, create database statement is used. Following command create a database named account.

 

Create database account

 

To create a database only if it does not exist, if not exists clause is used with create database statement.

 

Create database if not exists account

 

To select a database, use statement is used. For instance, to select account database you can write.

 

use account

 

To drop a database, drop database statement is used. For instance, to drop the database account

 

Drop database account

 

You can also check before drop database that the database exists.

 

Drop database if exists account

 

Before drop database consult the database administration. In most case, though you will not have the privilege to do that.

 

We are using web-based client as it is easy to use. Of course, the user can use command-line interface in case he wants.

 

Let us create a database using MySQL.

 

Start WAMP server. Then click on phpMyAdmin. Click on New button at the left side in the database tab. Then it will ask user to enter database name. Enter database name, optionally you can select collation and press the create button.

 

Now on words for simplicity, I am using GUI to use MySQL. You can see the code that can be used with MySQL monitor (CUI – Character User Interface) in GUI.

 

Run phpMyAdmin from statusbar. Click on database tab. Enter the name of the database and click create button. You can choose any collation from the collation.

 

A collation is used to compare and sort character strings. It is a set of rules. Each collation is belonged to a single character set in MySQL. Every character set is belonged to minimum one collation. The Character set can be belonged two or more collations.

 

 

 

To create tables, in the database account, click on account and it will open options for creating tables in the database account. Enter table name and number of fields required in the table. To create vendor table enter vendor as table name and 5 in number of columns.click go button will open screen to create various filed of table account with its name, data type etc. Enter and select appropriate value for each column.

 

MySQL datatypes

 

MySQL has nine most common data types. These data types divided into 3 groups : numeric, string and date/time.

 

The most common numeric data types are : INT, TINYINT and DECIMAL

 

Data Type Description
INT [(size)]

 

 

 INT (read as Integer) data type stores an integer number whose value

between the -2147483648 and 2147483647. Size is the maximum display

size. By default the size is 11.
TINYINT [(size)]

 

 TINYINT data type stores an integer number whose value between -127 and

128. Size is the maximum display size. By default size for TINYINT is 4.
DECIMAL [(p [, s] )]

 

 

 Decimal data type stores decimal number with fixed precision and scale.

Both precision and scale are optional. By default precision is 10 and scale is

0.

 

 

The most common string data types are: VARCHAR, CHAR and TEXT

  • By default, MySQL use Latin-1 character set.
  • The Latin-1 character set provides 256 characters. Each character occupies 1 byte for storage.
  • The Unicode character set provides 65000+ characters. Each character generally use 2 bytes for storage.

 

Data Type Description
VARCHAR (size)

 

 

 Variable length characters. The size parameter represents maximum number

of characters. The maximum number of characters stored by Varchar data

type is 65535.
CHAR [(size)]

 

 Fixed length characters. The size parameter represents maximum number of

Page 3 of 12

 

 

 

 characters. The maximum number of characters stored by char data type is

255. By Default size is 1.
Text Variable length characters. The maximum size is 65535 bytes.

The most common date and time data types are: DATE, TIME and DATETIME

 

 

You can create vendor table by writing SQL statement in case you are not prefer to use GUI. The syntax for create table is

 

create table

( Columnname1 datatype [columnAttributes] ,

Columnname2 datatype [columnAttributes] ,

Columnname3 datatype [ColumnAttributes],

)

 

To create vendor table you can code.

    create table vendor

 

(vid int primary key auto_increment,

 

vname varchar(50) unique,

 

vaddress varchar(100),

 

vcity varchar(50),

 

vmobile varchar(11));

 

SQL statement is terminated with semi-colon.

 

Attributes are like Primary Key, Foreign Key, Unique , Not Null, default and auto_increment. The 4 attributes – Primary Key, Foreign Key, Unique , Not Null – also known as constraint. Unique constraint ensures that the value for vname column contains unique value only. Null is a special value in SQL. Null is used when the value of a column is not known. Null is neither zero nor empty string. If you define column is not null then the column will not accept null value.

 

In a column more than one attribute can be added separated by space. In the previous example, vid is primary key and it has auto-increment attribute as well. Auto-increment adds value incremented by one as a new record inserted in the database.

 

MySQL provides many type of storage engine like MyISAM, CSV, Memory, InnodB etc for table. The only InnoDB engine support referential integrity. So in a database, it is required to use foreign key, there must be InnoDB storage engine.

 

SQL has mainly use for 4 types of languages:

 

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

3. Data Control Language (DCL)

4.Data Transaction Language (DTL)

 

Data Definition:

 

SQL lets a user define the structure and organization of the stored data and relationships among the stored data.

 

Data Manipulation:

 

SQL allows a user or an application program to retrieve stored data from the database and use it. It also allows a user or an application program to add new data in the database, updating the previously stored data and removing stored data from the database.

 

It is actually a CRUD operation as SQL allows user to Create(Add new data by using Insert command), Read (Retrieve data by using Select Command), Update (Update data by using update command) and Delete (Delete data by using delete command).

 

Data Control:

 

SQL can be used to provide access right and take it from user.

 

Data Transaction:

 

SQL can be used to prevent certain kinds of database errors.

 

DDL Commands:

 

Create

Alter

Truncate

Drop

 

DML Commands:

 

Select

Insert

Update

Delete

 

DCL Commands:

 

Grant

Revoke

 

DTL  Commands:

 

Commit

Rollback

Save Point

 

You should select the database , so the top-row of your phpMyAdmin will show server name and database name. For example,

 

Server: mysql wampserver »Database: account.

 

To use the database in MySQL console mode, you should use

 

Use         <databaseName>

 

statement.

 

i.e. use account

 

To view the list of tables of a database, you should code in MySQL console mode. Show tables;

 

To view the structure of atbale, you should code

 

Describe <tablename>;

 

If you type, desc vendor;  shows the schema/structure of vendor table.

 

    In phpMyAdmin you can also code by click on SQL tab and type the code. For example, in SQL tab you can type

 

desc vendor and click on the Go Button. Following figure shows it.

 

 

Alter table statement is used to

  • Add a new column at the end of the table or after a specified column
  • Drop a column
  • Rename a column
  • modify the column of existing table

 

For example, to add the column vemail after vmobile column you can code

 

alter table vendor

 

add email varchar(50);

 

To drop the column email, you can code

 

alter table vendor

 

drop column email;

  • Drop statement is used to drop the table or database also. To drop database , you can code Drop database account;
  • You should never drop the table or database without first consulting with database administrator.
  • Drop table statement deletes the definition and data of the table. Before drop the table, MySQL will check referential integrity. For example, you cannot delete vendor table if a foreign key in invoice table refers to the vendor table. In that case, you must first drop the invoice table before drop vendor able.
  • If the table does not exists, and you code drop table statement, then drop table statement generate an error. To prevent this error, you should code
  • Drop table if exists  <tablename>

 

Indexes

  • An index facilitate RDBMS to go directly to a row rather than search through all the rows until found the specified searched one. Thus index speeds search.
  • By Default MySQL create index for primary key and unique constraint.
  • In RDBMS, it is required to create indexes for foreign key(s) and other column(s) which are used frequently in search condition or join tables. Index can be created for other columns. It is advisable to avoid to create index for columns which are updated frequently because it slows the insert, update and delete operation.
  • To create index, create index statement is used. The general syntax to create index is:

 

create [unique] index  <indexName>

on <tableName>  (columnName1 [asc|desc] [,columnName2 [asc|desc])

  • Unique keyword is used to specify that index contains only unique values.
  • Asc or desc is used to specify that the index stored in ascending or descending sequence.
  • An index can be created on one or more columns.
  • Index can be created when create table statement is used. In create table code index <indexname> (columnName> after defining all the columns.

 

For example, to create index on vcity column, code

create index idxvcity

on vendor (vcity)

  • To drop an index drop index statement is used.

 

Users and Privileges

  • The root user is automatically created when MySQL is installed. Root user is an administrative user who has all the privileges to perform any operation on any database on the server. So, root user can drop any table or database.
  • To prevent other users from either accidently or intentionally drop entire database or drop tables, other users created with fewer privileges than root user.
  • The following four privileges allows the user to work with data which is stored in the table of database.
  • Select
  • Insert
  • Update and
  • Delete
  • The following four privileges allows the user to modify the structure of the database/table
  • Create
  • Alter
  • drop and
  • Index.

 

Following table shows other privileges

Privileges Description
All  [privileges]

 

 All privileges available at the current level. It does

not include Grant Option privilege.
Grant  Option

 

 Allows a user to grant privilege available to his/her

to other users.
Usage

 

 No privileges. It is used to modify existing accounts

without changing privileges for that account.
  • MySQL assign privileges at four different levels. These four levels are:Global, Database, Table and column. The asterisk (*) is used as wild card character to indicate all.
Level Example Description
Global *.* All databases and all table
Database

 

 account.*

 

 All tables of the account

(specified) database.
Table account.vendor All columns of the vendor table.
Column

 

 (vname) account.vendor

 

 Only vname (specified)

column(s)
  • MySQL has many privileges. For complete list of privileges, you can refer MySQL reference manual. MySQL reference manual is available online. You can search privileges at MySQL manual by typing “privileges provided”.

 

Create , rename and drop user

  • create user statement is used to create a new user.
  • The general syntax to create user is:
  • create user <username>  [@hostname]
  • identified by  ‘<password> ‘
  • For example,

 

create user hiren@localhost

identified by ‘hiren’;

  • If the hostname is not specified, MySQL uses % as a wild card character to indicate that the user can be from any host.

 

create user hiren

identified by ‘hiren’;

  • It is recommended that you should specify host when create a user.
  • Username must be unique for the host. For instance, I cannot create user hiren@localhost as the user hiren exists at localhost.
  • Rename user statement is used to change the name of user. If the original user has privileges, these privileges are transferred to the new name.

         rename user hiren to hdj@localhost;

  • To change, password of a user, grant usage statement is used. o  For example, to change password for hdj@localhost

 

grant usage on *.*

to hdj@localhost

identified by ‘hdj@localhost’;

  • To drop user, drop user statement is used. o F
  • .or example, to drop hdj@localhost

drop user hdj@localhost;

 

Grant Privileges

  • Before you grant privileges, you must connect as an appropriate user.
  • Root user has all the necessary privileges to grant privileges to another user.
  • The general syntax for grant privilege is:

Grant  <privilege(s)>

On [dbname].table

To username1 [identified by ‘password1’] [,

username2 [identified by ‘password2’]…]

[with grant option]

  • In case dbname (databasename) is not specified, MySQL uses the current database.
  • With grant option allows the user to grant the privileges for that user to other users.
  • To grant select privilege to hiren@localhost for the account database

grant select

on account.*

to hiren@localhost;

  • To grant insert, update and delete privileges to hiren@localhost for the account database

 

grant insert, update , delete

on account.*

to hiren@localhost;

  • Generally, create user statement is used to create user and grant statement is used to assign grant to the user. MySQL allows using grant statement to create users and grant privileges in a single statement. For example

 

Following grant statement create two users hj@localhost and hirenj@localhost and grants select, insert, update and delete on account database.

 

grant select,insert, update, delete

on account.*

to hj@localhost identified by ‘hj@localhost’,

hirenj@localhost identified by ‘hirenj@localhost’;

  • The following example shows how to grant privileges for specific columns. The below code allows select privileges for two columns vid and vname and allows update privilege for vaddress column.

 

grant select(vid, vname), update (vaddress)

on account.vendor

to hj@localhost

  • To assign all the privileges, all keyword is used.

 

grant all

on *.*

to dba

with grant option

 

 

View privileges

  • To list all the users of current server, you can use select statement as shown below.

select user, host

from mysql.user

  • MySQL uses an internal database mysql. This database has table named user which keep tracks of its users.
  • Show grant statement is used to view the privileges of the user.
  • Show grants [for user]
  • For User clause is skipped, then it shows grants for current user. show grants.
  • To view the grant of a user for specific user show grants for hj@localhost

   Revoke Privilege

  • Once users are created and privileges are granted to them, it may need to revoke privileges from a user. For example, if a user abuses his/her privileges, it is required to revoke some or all privileges from the user.
  • The general syntax to revoke privileges is

revoke <privilegeslist>

on [dbname.]tablename

from username1 [,username2]

  • For example, to revoke update and delete privileges from account database following code is written.

revoke update,delete

on account.*

from hj@localhost

  • To revoke all privileges, you must have the global create user privilege.
  • To revoke specific privileges, you must have the grant option privilege and you must have the privileges that you are revoking.
  • Following example, demonstrate the use of revoke all.

revoke all, grant option

from hj@localhost

 

 

References:

 

1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,

2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress

3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,

4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O’Reilly Media

5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox

6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible

7. Joel Murach, Ray Harris: Murach’s PHP and MySQL, Shroff/Murach

8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML/Javascript/DHTML/PHP , BPB Publications

9. Joel Murach, “Murach’s MySQL”, Shroff/Murach

10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams

11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education

12. http://www.php.net/

13. http://www.w3schools.com/

14. http://www.tutorialspoint.com/