top navigation
Ritesh Mandal Dr.

MySQL - The beginners database


How to start learning Databases?:

How to start learning Databases?:

MySQL is the easiest and best to understand the concept of Databases (Relational Database).

MySQL is owned by MySQL AB. For self use MySQL is free for use and could be downloaded from www.mysql.com . Licenses needs to be paid only if MySQL is supplied as a part of another commercial product or is sold directly. One License costs something around 200 USD (current price may differ).

MySQL is available for nearly all Operating Systems ; Linux , Solaris (e.g. 2.5.1) and Windows (e.g. WinNT, 2000, Win98, WinXP, Win2003).

I started learning MySQL while playing around with PHP.

You can manipulate the MySQL Database in following ways:
1. Using Command prompt.
2. Using Scripts like PHP.
3. Using third party softwares like SQLYog (www.webyog.com), if you work with PHP then use the excellent PHPMyAdmin.

Using MySQL with Linux terminal / console and MS-DOS Prompt

Apart from the initial steps , all operation interface of MySQL is same for Linux as well as Windows.

For Linux :
1. Open terminal window
2. Login as root
3. USE command mysql  : it starts the MySQL monitor. Type status to get the connection status.

mysql running in Mandriva

For Windows :
1. Open DOS prompt
2. Navigate to the folder where MySQL is installed. e.g. C:\mysql ,  there is a folder bin in mysql folder which contains the mysql binaries. Navigate to the 'bin' folder.
3. Don't type the command mysql as in Linux, it will throw errors telling you about insufficient user privileges and ODBC error.
4. USE command mysql -u root. , Where 'root' is the default user. Change it to your user if configured!

Installing MySQL in Windows :
1. Download  MySQL ; the file is zipped
2. Unzip the file; the executable file appears
3. Run setup.exe and follow instructions, MySQL is typically installed at C:\mysql
4. Mysql folder contains a file my-example.cnf (Mysql config.), rename this file to my.cnf  and copy it to C: drive (C:\my.cnf).
5. Installing Mysql daemon : ( to install Mysql as a automatic service) , Note : For non-server Operating systems like Win98 skip this.
5 a) Navigate to C:\mysql\bin ; the aim is to search for mysqld.exe.
5 b) Open command prompt.
5 c) If you find a file in bin folder : mysqld-nt.exe ; USE command : C:\mysql\bin\mysqld-nt --install
5 d) If a file named mysqld-shareware.exe is available in place of mysqld.exe , rename shareware file to "mysqld" ;
       and USE command : C:\mysql\bin\mysqld-nt --install
6. Open the Services Manager (Start Menu > Programs > Administrative Tools > Services) , search Mysql service and set it to Automatic.
7. For Non-Server Operating systems (Win98) every time mysql needs to be started manually ;
    USE command C:\mysql\bin\mysqld-nt --standalone.
8. You may find a file called mysqld-opt sometimes ; it can be used in place of mysqld if your processor is Pentium based.
9. Its installed !


Manipulating database and tables : All steps are common for LINUX and WINDOWS henceforth.
1. mysqladmin create portblair ; where 'portblair' is the name of the database you wish to create. (for windows mysqladmin -u root create portblair).
2. USE command mysql. ; Type use portblair
3. USE show tables ; it will return empty set because we have just created the database and it contains no schema.

using command prompt for mysql

MySQL Datatypes

CHAR's are used to represent fixed length strings. A CHAR string can range from 1-255 characters.
e.g. CHAR(10);

VARCHAR is a more flexible form of the CHAR data type. It also represents data of type String, yet stores this data in variable length format. VARCHAR can hold 1-255 characters. VARCHAR is usually a better option than CHAR due to it's variable length format characteristic. CHAR is faster than VARCHAR

e.g. VARCHAR(41);

INT ()
The INT datatype stores integers ranging from -2147483648 to 2147483647. An optional "unsigned" can be denoted with the declaration, modifying the range to be 0 to 4294967295

ex.INT; or INT unsigned;

A FLOAT is small decimal numbers, used when a somewhat more precise representation of a number is required.

e.g. FLOAT (5,3); - can hold up to five characters and three decimal places


Stores date related information. The default format is 'YYYY-MM-DD', and ranges from '0000-00-00' to '9999-12-31'.

The text and blob datatypes are used when a string of 255 - 65535 characters is required to be stored. This is useful when one would need to store an article such as the one you are reading. However, there is no end space truncation as with VARCHAR AND CHAR. The only difference between BLOB and TEXT is that TEXT is compared case insensitively, while BLOB is compared case sensitively.

MySQL Table types :
MyISAM - default tables of Mysql
Heap - in-memory Heap tables, fast
InnoDB - The InnoDB storage engine, created by Innobase Oy in Helsinki, Finland, provides MySQL with a safe and efficient way to store and process data, and gives MySQL some additional functionality not present with MyISAM or most of the other table types.

CREATE TABLE example_innodb (id INT, data VARCHAR(100)) TYPE=innodb ; Iam leaving it here for now.. Much more to come here about MYSQL soon....

More to follow soon. 1. Update database permissions/privilages. FLUSH PRIVILEGES;

2. Delete a column. alter table [table name] drop column [column name];

3. Add a new column to db. alter table [table name] add column [new column name] varchar (10);

4. Change column name. alter table [table name] change [old column name] [new column name] varchar (10);

5. Making a unique column , no chance of duplicate ..... alter table [table name] add unique ([column name]);

6. Make a column bigger. alter table [table name] modify [column name] VARCHAR(3);

7. Delete unique from table. alter table [table name] drop index [colmn name];

8. Load a CSV file into a table. LOAD DATA INFILE '/tmp/somefile.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3,field4,field5);

9. Change a users password. mysqladmin -u root -h somehostname.com -p password 'new_password'

10. Count no. of rows in a table SELECT COUNT(*) FROM [table name];

11. Show selected records sorted in an ascending (asc) or descending (desc). SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

12. List all databases on the sql server. show databases;

13. To see database's field formats and description. describe [table name];

14. Dump one database for backup. mysqldump -u username -p password --databases databasename >databasename.sql

Learning Data types : The Data types are pretty straight forward

Medical Coding | Web Designing | Oracle | MySQL | Linux | Java | ICH GCP | Clinical Data Management | Indian Clinical Trials | 21 CFR Part 11 | Web server | PHP | 443 & SSL | Web Hosting | GXP India | Apache | Other Databases | Image Editing | Software validation | Networking | Guitar | Disclaimer of use
© www.riteshmandal.com
Contact : ritesh@riteshmandal.com