Relational Databases-MySQL

MySQL is a relational database. An important feature of relational systems is that a single database can be spread across several tables as opposed to our flat-file phone book example. Related data is stored in separate tables and allows you to put them together by using a key common to both tables. The key is the relation between the tables. The selection of a primary key is one of the most critical decisions you'll make in designing a new database. The most important concept that you need to understand is that you must ensure the selected key is unique. If it's possible that two records past, present, or future share the same value for an attribute, don't use them as a primary key. Including key fields from another table to form a link between tables is called a foreign key relationship, like a boss to employees or a user to a purchase. The relational model is very useful because data is retrieved easier and faster.

Relationship Types

Databases relationships are quantified with the following categories:

• One-to-one relationships
• One-to-many relationships
• Many-to-many relationships

We'll discuss each of these relationships and provide an example. If you think of a family structure when thinking about relationships, you're ahead of the game. When you spend time alone with one parent, that's a specific type of relationship; when you spend time with both your parents, that's another one. If you bring in a significant partner and all of youyour parents, you, and your partnerall do something together, that's another relationship. This is identical to the bucket analogy. All those different types of relationships are like specific buckets that hold the dynamics of your relationships. In the database world, it's the data you've created.

In a one-to-one relationship, each item is related to one and only one other item. Within the example of a bookstore. A one-to-one relationship exists between users and their shipping addresses.

Normalization

Thinking about how your data is related and the most efficient way to organize it is called normalization. Normalization of data is breaking it apart based on the logical relationships to minimize the duplication of data. Generally, duplicated data wastes space and makes maintenance a problem. Should you change information that is duplicated, there's the risk that you miss a portion and you risk inconsistencies in you database.

It's possible to have too much of a good thing though: databases placing each piece of data in their own tables would take too much processing time and queries would be convoluted. Finding a balance in between is the goal.

While the phone book example is very simple, the type of data that you process with a web page can benefit greatly from logically grouping related data.

Let's continue with the bookstore example. The site needs to keep track of the user's data, including login, address, and phone number, as well as information about the books, including the title, author, number of pages, and when each title was purchased.

Read More

Use mysqldump to create a copy of the database?

mysqldump -h mysqlhost -u username -p mydatabasename > tgdbdump.sql

Read More

What is SERIAL data type in MySQL?

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

Read More

What’s the default port for MySQL Server?

3306

Read More

Data Control Language (DCL) statements ?

• Data Control Language (DCL) statements  Allow you to change the permissions on database structures. There are two DCL statements:
  • GRANT  Allows you to give another user access to your database structures, such as tables.
  • REVOKE  Allows you to prevent another user from accessing to your database structures, such as tables.

Read More

Data Definition Language (DDL) statements?

• Data Definition Language (DDL) statements  Allow you to define the data structures, such as tables, that make up a database. There are five basic types of DDL statements:
  • CREATE  Allows you to create a database structure. For example, CREATE TABLE is used to create a table; another example is CREATE USER, which is used to create a database user.
  • ALTER  Allows you to modify a database structure. For example, ALTER TABLE is used to modify a table.
  • DROP  Allows you to remove a database structure. For example, DROP TABLE is used to remove a table.
  • RENAME  Allows you to change the name of a table.
  • TRUNCATE  Allows you to delete the entire contents of a table.

Read More

Data Manipulation Language (DML) statements ?

• Data Manipulation Language (DML) statements  Allow you to modify the contents of tables. There are three DML statements:
  • INSERT  Allows you to add rows to a table.
  • UPDATE  Allows you to change a row.
  • DELETE  Allows you to remove rows.

Read More

What Is a Relational Database?

The basic concepts of a relational database are fairly easy to understand. A relational database is a collection of related information that has been organized into structures known as tables. Each table contains rows that are further organized into columns. These tables are stored in the database in structures known as schemas, which are areas where database users may store their tables. Each user may also choose to grant permissions to other users to access their tables.

Read More

MySQL Subqueries?

You can use the result of a query like you use a list of values with the IN operator to filter a query based on the result of another query. The subquery appears in parentheses after the IN keyword.

The following query fetches all columns from the products table, but only for those product codes that were part of order number 1:

mysql> SELECT *
    -> FROM products
    -> WHERE product_code IN (
    ->   SELECT product_code
    ->   FROM order_lines
    ->   WHERE order_id = 1
    -> );
+--------------+---------------+--------+--------+
| product_code | name          | weight | price  |
+--------------+---------------+--------+--------+
| MINI         | Small product |   1.50 |  5.99  |
| MAXI         | Large product |   8.00 | 15.99  |
+--------------+---------------+--------+--------+
2 rows in set (0.00 sec)

Read More

what is Triggers?

A trigger is a stored database object that contains a series of SQL commands, set to activate automatically when certain events take place.

Each trigger is associated with a table. You can create a trigger that will fire when an INSERT, UPDATE, or DELETE takes place on the named table.

Read More

What Is SQL?

The Structured Query Language (SQL) is the most common way to retrieve and manage database information. An SQL query consists of a series of keywords that define the data set you want to fetch from the database. SQL uses descriptive English keywords, so most queries are easy to understand.

Read More

Restore database (or database table) from backup?

 [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Read More

How set a root password if there is on root password?

# mysqladmin -u root password newpassword

Read More

total number of rows?

mysql> SELECT COUNT(*) FROM table;

Read More

Show unique records mysql?

mysql> SELECT DISTINCT column FROM table;

Read More

CSV tables?

CSV tables are the special tables, data for which is saved into comma-separated values files
 and cannot be indexed.

Read More

MySQL data directory?

MySQL data directory is most important location in which all 

MySQL databases are stored. The default data directory is located in the file mysql.lf.

Read More

difference between mysql_connect and mysql_pconnect?

Mysql_connect:
 Opens a new connection to the database
  The database connection can be closed
  Opens the page every time the page is loaded.

Mysql_pconnect:
 Opens a persistent connection to the database.
The database connection can not be closed.
The page need not be opened every time the page is loaded.

Read More