MySQL Select

Posted on February 15, 2018 in My SQL

MySQL SELECT Statement

The SELECT statement is used to select data from a database.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Test  Employee Table

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
2 Johnson Maria Female New York
3 Miller David Male London
4 Wilson Maria Female Paris
5 Thomas Mary Female Singapore
6 Martin Steven Male Bangkok
7 Lee Karen Female Dubai 
Try It Now
Tags: ,

Why we are using Backtick

Posted on February 15, 2018 in Feature Questions

What is difference between below two statement

SELECT `id`, `name`, `anotherfield` ...
-- vs --
SELECT id, name, anotherfield ...

Using backticks permits you to use alternative characters. In query writing it’s not such a problem, but if one assumes you can just use backticks, I would assume it lets you get away with ridiculous stuff like

SELECT `id`, `my name`, `another field` , `field,with,comma`

Which does of course generate badly named tables.

If you’re just being concise I don’t see a problem with it, you’ll note if you run your query as such

EXPLAIN EXTENDED Select foo,bar,baz

The generated warning that comes back will have back-ticks and fully qualified table names. So if you’re using query generation features and automated re-writing of queries, backticks would make anything parsing your code less confused.

I think however, instead of mandating whether or not you can use backticks, they should have a standard for names. It solves more ‘real’ problems.

Try It Now
Tags:

What are MySQL database engines?

Posted on February 14, 2018 in Feature Questions 
mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

MySQL Supported Storage Engines

  1. InnoDB: InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.
  2. MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
  3. Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
  4. CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
  5. Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
  6. Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.
  7. NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
  8. Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
  9. Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
  10. Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.
Tags:

MySQL Home

Posted on February 14, 2018 in My SQL

MySQL is a Relational DataBase Management System (RDBMS).

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL is an ANSI (American National Standards Institute) standard

Features of MySQL

1. Speed:Ofcourse, the speed at which a server side program runs depends primarily on the server hardware. Given that the server hardware is optimal, MySQL runs very fast. It supports clustered servers for demanding applications.

2. Ease of use:MySQL is a high-performance, relatively simple database system. From the beginning, MySQL has typically been configured, monitored, and managed from the command line. However, several MySQL graphical interfaces are available as described below:

  • MySQL Administrator: This tool makes it possible for administrators to set up, evaluate, and tune their MySQL database server. This is intended as a replacement for mysqladmin.
  • MySQL Query Browser: Provides database developers and operators with a graphical database operation interface. It is especially useful for seeing multiple query plans and result sets in a single user interface.
  • Configuration Wizard: Administrators can choose from a predefined list of optimal settings, or create their own.
  • MySQL System Tray: Provides Windows-based administrators a single view of their MySQL instance, including the ability to start and stop their database servers.

3. Cost:MySQL is available free of cost. MySQL  is a “Open Source” database. MySQL is part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python) environemtn, a 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, reliability, and documentation.

4.Query Language Support:MySQL understands standards based SQL (Structured Query Language).

5.Capability :Many clients can connect to the server at the same time. Clients can use multiple database simultaneously. You can  access MySQL using several interfaces such as command-line clients, Web browsers.

6.Connectivity and security: MySQL is fully networked, and database can be accessed from anywhere on the Internet, so you can share your data with  anyone, anywhere. The connectivity could be achieved with Windows programs by using ODBC drivers. By using the ODBC connector to MySQL, any ODBC-aware client application (for example, Microsoft Office, report writers, Visual Basic) can connect to MySQL.

7.Portability: MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems, such as Windows and OS/2. MySQL runs on hardware from home PCs to high-end server. MySQL can be installed on Windows XP, Windows Server 2003, Red Hat Fedora Linux, Debian Linux, and others.

Tags: