Initial setup of MySQL database and it’s basic

MySQL is considered to be most popular open source database,

For more information regarding versions and features, please refer http://dev.mysql.com/

Topics

  • Download and Install.
  • Initial Setup
  • Creating database tables
  • Adding/updating/deleting data to tables

Download and install

To download MySQL database for windows,

NOTE: Better to download the MSI installer, 32 bit exe file will have binaries for both 32/64 bit OS.
To create and play with database and tables, we can use the MySQL command prompt.
OR
To access the database tables and to write queries (basically an IDE to access MySQL DB), please download MySQL Workbench zip file.

Initial Setup

While installing MySQL Server, as prompted, enter root password, and then enter an unique name for the “Windows Service Name” and Done.


Then Open the All Programs -> MySQL -> MySQL command line client, enter root password as prompted. (Server installed)

Now Extract the MySQL Workbench zip file, and click on the MySQLWorkbench.exe file to open the IDE.


Creating database tables
Once selected stored connection,  we can play with the database (Schemas),
Now open the Query window and execute below commands to create database, table and columns.
Query to create Database and Table

create database student;

use student;

CREATE TABLE StDetails ( 
    FullName VARCHAR(30) NOT NULL,
    Email VARCHAR(30) NOT NULL, 
    Telephone int(14), 
    Gender VARCHAR(10), 
    YearExp VARCHAR(2) ,
    PRIMARY KEY (Email) );
To run above queries, click on the thunder logo on the Query window, 
Query to see the table details (columns and dataType)

show columns from student.StDetails;

Run the above query, you will see the below details
Adding/updating/deleting data to tables

Query to insert values to the table

INSERT INTO StDetails values (‘Sun Pat’, ‘sunpat1@gmail.com’,’1231231231′, ‘male’, ’12’); 
INSERT INTO StDetails values (‘John Ken’, ‘John@gmail.com’,’77231231′, ‘male’, ‘7’); 

Show details of table data
select * from student.StDetails;

Query to update values to the table
UPDATE student.StDetails SET Telephone = ‘22221111’ where Email = ‘sunpat1@gmail.com’;

For more list of MySQL queries, I found below cheat sheet more useful 

More Queries on MySQL

Content posted is based on learning or working experience, please leave comments if anything needs to be added or updated, discuss your queries on our facebook:qavalidation.com, Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: