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