Advantages & Disadvantages of Mysql

MY SQL:

MySQL is a relational database management system which is an open source database.

(RDBMS)that runs as a server providing multi-user access to a number of databases.

Free-software projects that require a full-featured database management system often use MySQL. Such projects include (for example) WordPress, phpBB, Drupal and other software built on the LAMP software stack. MySQL is also used in many high-profile, large-scale World Wide Web products including , Google and Facebook.Wikipedia

Uses

Many web applications use MySQL as the database component of a LAMP software stack. Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Several high-traffic web sites (including Flickr, Facebook, Wikipedia, Google (though not for searches), Nokia and YouTube) use MySQL for data storage and logging of user data.

Features:

  • Because of its unique storage engine architecture MySQL performance is very high.
  • Supports large number of embedded applications which makes MySql very flexible.
  • Use of Triggers, Stored procedures and views which allows the developer to give a higher productivity.
  • Allows transactions to be rolled back, commit and crash recovery.
  • Triggers & cursor.

Advantages of Mysql vs with other database servers

my sql:
MySQL is characterised as a free, fast, reliable open source relational database. It does lack some sophistication and facilities, but it has an active development team and, as it goes from release to release, more capabilities are added. At certain times there will be a trade-off between speed and capabilities, and the MySQL team intend to keep their database engine fast and reliable

MySQL Advantage is a suite of four open-source software packages–proven, popular, and well-supported ones–that work together to run interactive, dynamic Internet sites.

MySQL Advantage is comprised of Enhanced MySQL, Apache, PHP, and Perl, to create an integrated Web development environment. This technology suite delivers a complete, stable environment for building and deploying database-driven applications for the Internet. Available for Linux, Unix, and Windows, Advantage allows users to develop and deploy on their choice of platforms. This flexible, multiple-platform environment brings unity and scalability to development and Web applications.

MYSQL is widely used for web applications. This acts as a database component for many languages such as BAMP, WAMP, LAMP and MAMP. PHP and ruby added to popularity to the language. PHP and MYSQL are very much used for managing database and web applications.

Install Advantage:

Sometimes you want a MySQL expert to help install the MySQL Server, Enterprise Monitor, Query Analyzer and other MySQL software. MySQL Support Engineers can:

  • Recommend Specific binaries for your platform
  • Walk you through the installation procedures on the phone
  • Remotely login to install the software on your machines (Gold, Platinum only)

ADVANTAGE IN DATABASE:

Advantage Database Server is a relational database management system (RDBMS) for small to medium sized when it was introduced to provide a stable solution for Clipper developers who were tired of slow performance and corrupt indexes inherent to file server-based databases. Over the years, ADS has grown in both popularity and features.

“In short, the Advantage Database Server is a high-performance, low-maintenance, remote database server that permits you to easily build and deploy client/server applications and web-based applications”

Advantage Database Server is most popular among application developers as a client/server backend solution for shared, networked, standalone, mobile and Internet database applications. ADS is unique among other database offerings because it provides both ISAM table-based and SQL based data access.

Advantage Database Server :installs in minutes and is easy to maintain.

DISADVANTAGE PRESENT IN MYSQL:

MYSQL standard has diverted on the standards of implementation of Null and default values. Arithmetic operations are susceptible to integer over flow. Mode of operation of SQL is set to unusual tolerant state which developers hate.

  • MySQL does not support a very large database size as efficiently
  • MySQL does not support ROLE, COMMIT, and Stored procedures in versions less than 5.0
  • Transactions are not handled very efficiently.

Create the Database (GUI & Command line):

First go to applications & select the terminal and click .

Start the command prompt

p008@pbs008:~$ sudo /opt/lampp/lampp start

sudo] password for fp008:

Starting XAMPP for Linux 1.7.2…

XAMPP: Starting Apache with SSL (and PHP5)…

XAMPP: Starting MySQL…

XAMPP: Starting ProFTPD…

XAMPP for Linux started.

fp008@pbs008:~$

then go to url enter http://localhost/xampp/

in that website select phpmy admin and click the mouse.

create the database as student

create the database name as information and create no. Of fields as 3

then created database will be displayed.

Create MYSQL New users? (GUI & Command line):

select the database as mysql –> select user and click.

Select the insert button which we have to insert then what are the details need to insert as our wish

we insert it.

Host: local host

user:admin

pwd:xxxx

then inserted table will be displayed.

Write a PHP Script to connect the backend and retrival of Datas:

Next we will retrieve the information from the database table we created called “friends”

 // Collects data from "friends" table  $data = mysql_query("SELECT * FROM friends")  or die(mysql_error()); 

And we will then temporally put this information into an array to use:

 // puts the "friends" info into the $info array  $info = mysql_fetch_array( $data ); 

Now let’s print out the data to see if it worked:

 // Print out the contents of the entry  Print "<b>Name:</b> ".$info['name'] . " ";  Print "<b>Pet:</b> ".$info['pet'] . " <br>"; 

However this will only give us the first entry in our database. In order to retrieve all the information, we need to make this a loop. Here is an example:

 while($info = mysql_fetch_array( $data ))  {  Print "<b>Name:</b> ".$info['name'] . " ";  Print "<b>Pet:</b> ".$info['pet'] . " <br>";  } 

So let’s put all the these ideas together to create a nicely formatted table with this final php code:

 <?php  // Connects to your Database  mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error());  mysql_select_db("Database_Name") or die(mysql_error());  $data = mysql_query("SELECT * FROM friends")  or die(mysql_error());  Print "<table border cellpadding=3>";  while($info = mysql_fetch_array( $data ))  {  Print "<tr>";  Print "<th>Name:</th> <td>".$info['name'] . "</td> ";  Print "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>";  }  Print "</table>";  ?> 

Display nth highest record in a table:

Assume that in an college several departments are there. In that i want to know details.from which student from which department as scored 80%.i need the record for this year.

Then with the help of database you can get the details.

The first idea is we get the nth highest record and sort them in ascending order. The nth highest record is the last record in the result set. And then we order the result set in descending order and get the first one. Here is SQL script to accomplish it:
Get the Nth highest result set in ascending order:

SELECT * 
FROM table_name
ORDER BY column_name ASC
LIMIT n

Get the nth highest record:

SELECT * 
FROM (
        SELECT * 
        FROM table_name
        ORDER BY column_name ASC
        LIMIT n
) AS tbl
ORDER BY column_name DESC
LIMIT 1

Luckily, with LIMIT clause you can rewrite the query as follows:

SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT n - 1, 1

The query just returns the first row after n-1 row(s) so you get the nth highest record.
For example, if you want to get the second most expensive product (n = 2) in Products database table. You just perform the following query:

SELECT productCode, productName, buyPrice
FROM products 
ORDER BY buyPrice desc 
LIMIT 1, 1 

Here is the result

+-------------+--------------------------------+----------+
| productCode | productName                    | buyPrice |
+-------------+--------------------------------+----------+
| S18_2238    | 1998 Chrysler Plymouth Prowler |   101.51 |
+-------------+--------------------------------+----------+
1 row in set (0.00 sec)

The second technique to get the nth highest record is using subquery

SELECT *
FROM table_name AS a 
WHERE n - 1 = (
        SELECT COUNT(primary_key_column) 
        FROM products b 
        WHERE  b.column_name > a. column_name)

And we achieve the same result as the first technique on Products table to get the second most expensive product by performing the following query:

SELECT productCode, productName, buyPrice
FROM products a 
WHERE 1 = ( 
        SELECT COUNT(productCode) 
        FROM products b 
        WHERE b.buyPrice > a.buyPrice) 

We can count duplicate entry in particular table against Primary Key:

if the user is created the database in an particular table.then user is not allowed to create duplicate key.

eg.

created table is db.you should not created the table as same name/table.

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1


// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause

select city_name
from areas
group by city_name
having count(*) > 1

How to Import / export the Database:

Data Import Options

DatabaseSpy®2010 lets you import data into your databases from conventional CSV files or from files containing XML data to create new tables or update existing tables in your database.

The Import option in the Tools menu or the Import button on the main DatabaseSpy®2010 toolbar opens an Import dialog to set the import source, choose options, and see previews of your data import request.

The XML import option can help ease the transition between modern XML-based applications and legacy relational databases, with special XML-aware options to process data during import.

if the column names in the source file don’t exactly match your database you can reassign them. You can also prevent individual columns from being imported, and you can set key constraints.

If you need to convert data from a dissimilar format before importing it to your database, Altova MapForce®2010 is the visual mapping tool with the power to map any combination of XML, database, text, or EDI files. Altova MapForce®2010 can connect to all major databases and convert source data on the fly for direct insertion, but if connection to the target database is not possible or convenient, one of the many outputs supported is a CSV file that can be imported by DatabaseSpy2010. The combination of the data integration functionality of MapForce®2010 with the database browsing, SQL editing, database design, and import/export features of DatabaseSpy®2010 provides database administrators and other IT professionals with a robust toolset for database management needs.

DatabaseSpy®2010 has two major import options: you can import the data directly, or you can generate the SQL statements necessary to perform the import and place them in the SQL Editor. If you generate SQL, the import request is not actually performed until you press the execute button in the SQL Editor window.

Data Export Options

Once you have connected to a database, the export option is available from the Tools menu, or simply click the Export button in the toolbar to open the export data dialog.

Data Export Formats

DatabaseSpy®2010 supports five formats for data export:

  • XML – Each row in the table is exported with an identifying <Row> XML element and each column entry is identified by an XML element that matches the column name. This format also includes a commented header documenting the SQL SELECT statement used to retrieve the data.
  • XML Structure – Each row in the table is exported with an identifying XML element that matches the exported table name. This lets you preserve table hierarchies and relationships when you export from more than one database table.
  • CSV – Each row is exported as plain text, with multiple delimiter and newline options.
  • HTML – Each row is exported with HTML coding to form an HTML table that can be opened in a Web browser or incorporated into a larger HTML page.
  • Excel – Each row is exported directly to a Microsoft Excel spreadsheet file.

DatabaseSpy®2010 allows you to fine tune the content of the output with special refinement options for each format.

Data Export Source

DatabaseSpy®2010 gives you complete control over the source data selected for export. You can select any table or view from any connected database.
As you browse the hierarchy of your database in the export source window, you have the same Layouts, Filters, Favorites, and the Object Locator features available in the Online Database Browser to simplify navigation of the database structure.

You can also switch the source window from a database hierarchy to a SQL display. If you select one or more tables for output, then switch to SQL Source, DatabaseSpy®2010 displays the SQL query it will use to retrieve data.

You can edit the query in the Source window if you like, or paste SQL statements copied from the DatabaseSpy®2010 SQL Editor window. When you’re working in the SQL Editor you even have a dedicated Export button to let you instantly open an export dialog box pre-loaded with your current SQL Editor statements. This lets you construct a specialized SQL query, test it in the SQL editor, and conveniently export the resuilts in any of the five supported export formats.

Data Export Destination

The destination section of the export dialog lets you specify the path for output files, as you would expect. If your selected format is XML, XML Structure, CSV, or HTML, you can also export directly to Altova XMLSpy. If you select multiple tables or views for export, each is written to a separate file.

MySQL Security

MySQL security works by limiting both the users who have access to a database and what they are allowed to do once they have access. This requires careful consideration of issues such as who is allowed to read from or write to particular database tables and which users have permission to delete tables or use other MySQL features.

security requirements:

In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:

  • MySQL database must be executed in a chrooted environment;
  • MySQL processes must run under a unique UID/GID that is not used by any other system process;
  • Only local access to MySQL will be allowed;
  • MySQL root’s account must be protected by a hard to guess password;
  • The administrator’s account will be renamed;
  • Anonymous access to the database (by using the nobody account) must be disabled;
  • All sample databases and tables must be removed.

MySQL privileges and user security

The MySQL privilege system ensures that all your users can perform only the operations that are allowed to them. When you connect to a MySQL server, your identity is determined by the host system that you are connected to and the user ID that you specify. When you issue SQL statements after connecting, MySQL access control grants privileges according to your identity and what you want to do.

MySQL access control performs the following stages when you are running a client program that connects to the MySQL server:

  • Stage 1: The MySQL server checks whether you are allowed to connect.
  • Stage 2: The MySQL server checks each SQL statement that you issue to determine whether you have sufficient privileges to perform the operation.

MySQL provides the following tools that you can use to administer MySQL, work with MySQL databases, run queries, and so on:

  • MySQL Administrator
  • MySQL Query Browser.

About these ads
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s