MySQL

MySQL


MySQL is a very popular, open source database. Officially pronounced “my Ess Que Ell” (not my sequel). Handles very large databases; very fast performance.

Why are we using MySQL?

  • Free (much cheaper than Oracle!)
  • Each student can install MySQL locally.
  • Easy to use Shell for creating tables, querying tables, etc.
  • Easy to use with Java JDBC

MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), the fast-growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost and freedom from platform lock-in.

MySQL was originally founded and developed in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael "Monty" Widenius, who had worked together since the 1980's.

Crash Course Fundamentals

In order to use JDBC, you need:

a database.

basic understand of SQL (Structured Query Language)

Some students may have database backgrounds; others may not.

The purpose of this lecture is to get all students up to speed on database fundamentals.

Connecting to MySQL

MySQL provides an interactive shell for creating tables, inserting data, etc.

On Windows, just go to c:\mysql\bin, and type:

mysql

Or, click on the Windows icon

Sample Session

For example:

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 241 to server version: 3.23.49

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

To exit the MySQL Shell, just type QUIT or EXIT:

mysql> QUIT

mysql> exit

Basic Queries

Once logged in, you can try some simple queries.

For example:

mysql> SELECT VERSION(), CURRENT_DATE;

+-----------+--------------+

| VERSION() | CURRENT_DATE |

+-----------+--------------+

| 3.23.49 | 2002-05-26 |

+-----------+--------------+

1 row in set (0.00 sec)

Note that most MySQL commands end with a semicolon (;)

MySQL returns the total number of rows found, and the total time to execute the query.

Basic Queries

Keywords may be entered in any letter case.

The following queries are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> select version(), current_date;

mysql> SeLeCt vErSiOn(), current_DATE;

Multi-Line Commands

mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.

Here's a simple multiple-line statement:

mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;

+--------------------+--------------+

| USER() | CURRENT_DATE |

+--------------------+--------------+

| joesmith@localhost | 1999-03-18 |

+--------------------+--------------+

SQL provides a structured language for querying/updating multiple databases.

The more you know SQL, the better.

The most important part of SQL is learning to retrieve data.

selecting rows, columns, boolean operators, pattern matching, etc.

Keep playing around in the MySQL Shell.

TO LOGIN

To login (from unix shell) use -h only if needed.the command used to login is

[mysql dir]/bin/mysql -h hostname -u root -p

DDL COMMANDS

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples:

CREATE, ALTER, DROP statements

CREATE

Creates objects in the database the query used to create the object is

create database [database name];

Example :

create database [employee];

ALTER

Alters objects of the database

ALTER TABLE <table_name>

ADD <column_name1> <datatype1> <constraint1>

ALTER TABLE <table_name>

ALTER COLUMN <column_name1> <datatype1> <constraint1>

ALTER TABLE <table_name>


DROP COLUMN <column_name1> <datatype1>


DROP
deletes objects of the database, the syntax for dorp is

DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....

Removes the table(s) from the database. The IF EXISTS clause will drop the table only if it exists. If this clause is not present an error is generated if the table does not exist. Any data that was in a dropped table is lost so use with care.

DML COMMANDS

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples:

SELECT, UPDATE, INSERT statements

SELECT
SELECT [ DISTINCT | ALL ]
column_expression1, column_expression2, ....
[ FROM from_clause ]
[ WHERE where_expression ]
[ GROUP BY expression1, expression2, .... ]
[ HAVING having_expression ]
[ ORDER BY order_column_expr1, order_column_expr2, .... ]

The SELECT statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. to the output.

UPDATE

UPDATE table_name

SET col_name1 = expression1, col_name2 = expres sion2, ....

[ WHERE expression ]

[ LIMIT limit_amount ]

Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated.

INSERT

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]

VALUES ( expression1_1, expression1_2, .... ),

(expression2_1, expression2_2, .... ), ....

This is the SQL command to insert records into a table in the database. This statement comes in three forms.

The Second form of insert command

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]

SELECT ...
The Third form of insert cmmand
INSERT INTO table_name
SET col_name1 = expression1, col_name2 = expression2, ....

TCL COMMANDS

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.


Examples: COMMIT, ROLLBACK statements

SHOW

SHOW engine_variable

engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS

Shows internal information about the database system. SHOW TABLES returns a list of tables in the database. SHOW STATUS returns debugging and statistical information about the internal state of the database engine. SHOW CONNECTIONS returns a snapshot of the current connections on the database. SHOW SCHEMA lists all the schema defined.

SHUTDOWN:

Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

Lucid solutions offer customised interfaces for MYSQL database management system. We also have 24*7 support and trouble-shooting services for all IT needs. We also schedule, plan, coordinate and test your existing database for performance and also carry out future database upgrades in addition to offering our expertise and other services at the most affordable prices.

MySQL Server Architecture and Design

  • MySQL Remote DBA

  • My SQL Cluster and Replication

  • MySQL High Availability

  • MySQL Performance Tuning and Optimization

We have highly professional, qualified and certified engineers who are dedicated to provide all these MySQL services our clients

Leave a Reply