edit

MySQL Introduction

vers 3 updated on 09 Nov 2021 by ( harshal.kambe/ harshal.kambe)

Artical Created By Harshal on 02 September 2021 Updated By Harshal on 06 October 2021 Updated By Harshal on 09 November 2021 (Add Rules)

What is a Database?

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds. Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory, but data fetching and writing would not be so fast and easy with those type of systems. Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.

A Relational DataBase Management System (RDBMS) is a software that:

  • Enables you to implement a database with tables, columns and indexes.
  • Guarantees the Referential Integrity between rows of various tables.
  • Updates the indexes automatically.
  • Interprets an SQL Query and combines information from various tab

RDBMS Terminology

Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.

  • Database: A database is a collection of tables, with related data.
  • Table: A table is a matrix with data. A table in a database looks like a simple spreadsheet.
  • Column: One column (data element) contains data of one and the same kind, for example the column postcode.
  • Row: A row (= tuple, entry or record) is a group of related data. For example, the data of one subscription.
  • Redundancy: Storing data twice, redundantly to make the system faster.
  • Primary Key: A primary key is unique. A key value cannot occur twice in one table. With a key, you can only find one row.
  • Foreign Key: A foreign key is the linking pin between two tables.
  • Compound Key: A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
  • Index: An index in a database resembles an index at the back of a book.
  • Referential Integrity: Referential Integrity makes sure that a foreign key value always points to an existing row.

Administrative MySQL Command

Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database:

  • USE Databasename: This will be used to select a database in the MySQL workarea.
  • SHOW DATABASES: Lists out the databases that are accessible by the MySQL DBMS.
  • SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
  • SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
  • SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
  • SHOW TABLE STATUS LIKE tablename: Reports details of the MySQL DBMS performance and statistics.

Datatype

MySQL Datatypes are the programming language, which contain set of values and data. A datatype defines an appropriate kind of data, like integer, floating-point, Boolean etc. A datatype also defines the attainable values for that type, the operations that should be possible on that sort and then stores the data.

Datatypes are classified into 4 Types

String datatype

CHAR(size) Max Size 255 Characters
VARCHAR(Size) Max Size 255 Characters
TEXT(Size) Max size of 65,535 characters
BINARY(size) Maximum size of 255 characters
VARBINARY(size) Maximum size of 255 characters

Numeric datatype

BIT Very small integer value.
Signed values range from -128 to 127. Unsigned values range from 0 to 255.
FLOAT(p) Floating point number
BOOLEAN Synonym for TINYINT
INT(m) Standard integer value.
Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
INTEGER(m) Standard integer value.
Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
NUMERIC(m,d) Unpacked fixed-point number. m defaults to 10, if not specified. d defaults to 0, if not specified.
DOUBLE(m,d) Double precision floating point number.
DOUBLE PRECISION(m,d) Double precision floating point number

Date/time

DATE Values range from ‘1000-01-01’ to ‘9999-12-31’ Displayed as ‘YYYY-MM-DD’
TIME Values range from ‘-838:59:59’ to ‘838:59:59’ Displayed as ‘HH:MM:SS’
YEAR[(2|4)] Year value as 2 digits or 4 digits Default is 4 digits
DATETIME Values range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. Displayed as ‘YYYY-MM-DD HH:MM:SS’

Large object datatype

TINYBLOB Maximum size of 255 bytes
BLOB(size) Maximum size of 65,535 bytes
LONGTEXT Maximum size of 4GB or 4,294,967,295 characters

MySQL Keys

Super Key – A super key is a group of single or multiple keys which identifies rows in a table.
Primary Key – is a column or group of columns in a table that uniquely identify every row in that table.
Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes.
Alternate Key – is a column or group of columns in a table that uniquely identify every row in that table.
Foreign Key – is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity.
Compound Key – has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database.
Composite Key – is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individual uniqueness is not guaranteed.
Surrogate Key – An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key.

Most Important SQL Commands

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index

The MySQL WHERE Clause

Clause is defined as a set of rules, that makes to understand the concepts of MySQL command in Database. MySQL Clauses are very similar to SQL clause, except some functional operations.

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

WHERE Syntax SELECT column1, column2, ... FROM table_name WHERE condition;

ORDER BY Clause

The MYSQL ORDER BY Clause is used to sort the records in ascending or descending order.

Syntax: SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

GROUP BY Clause

The MYSQL GROUP BY Clause is used to collect data from multiple records and group the result by one or more column. It is generally used in a SELECT statement.

You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc. on the grouped column.

Syntax:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

HAVING Clause

MySQL HAVING Clause is used with GROUP BY clause. It always returns the rows where condition is TRUE.

Syntax: SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

MySQL Functions

MySQL String Functions

CHAR_LENGTH-Returns the length of a string (in characters)
CONCAT-Adds two or more expressions together
CONCAT_WS-Adds two or more expressions together with a separator
FORMAT-Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places
LCASE-Converts a string to lower-case
LEFT-Extracts a number of characters from a string (starting from left)
LENGTH-Returns the length of a string (in bytes)
LOWER-Converts a string to lower-case
LPAD-Left-pads a string with another string, to a certain length
LTRIM-Removes leading spaces from a string
MID-Extracts a substring from a string (starting at any position)
POSITION-Returns the position of the first occurrence of a substring in a string
REPEAT-Repeats a string as many times as specified
REPLACE-Replaces all occurrences of a substring within a string, with a new substring
REVERSE-Reverses a string and returns the result
RIGHT-Extracts a number of characters from a string (starting from right)
RPAD-Right-pads a string with another string, to a certain length
RTRIM-Removes trailing spaces from a string
SPACE-Returns a string of the specified number of space characters
STRCMP-Compares two strings
SUBSTRING-Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX-Returns a substring of a string before a specified number of delimiter occurs
TRIM-Removes leading and trailing spaces from a string
UCASE-Converts a string to upper-case
UPPER-Converts a string to upper-case

MySQL Numeric Functions

ABS-Returns the absolute value of a number
AVG-Returns the average value of an expression
COUNT-Returns the number of records returned by a select query
FLOOR-Returns the largest integer value that is <= to a number
LEAST-Returns the smallest value of the list of arguments
MAX-Returns the maximum value in a set of values
MIN-Returns the minimum value in a set of values
MOD-Returns the remainder of a number divided by another number
RAND-Returns a random number
ROUND-Rounds a number to a specified number of decimal places
SUM-Calculates the sum of a set of values
TRUNCATE-Truncates a number to the specified number of decimal places

MySQL Date Functions

ADDDATE-Adds a time/date interval to a date and then returns the date
ADDTIME-Adds a time interval to a time/datetime and then returns the time/datetime
CURRENT_DATE-Returns the current date
CURRENT_TIME-Returns the current time
CURRENT_TIMESTAMP-Returns the current date and time
CURTIME-Returns the current time
DATE-Extracts the date part from a datetime expression
DATEDIFF-Returns the number of days between two date values
DATE_ADD-Adds a time/date interval to a date and then returns the date
DATE_FORMAT-Formats a date
DATE_SUB-Subtracts a time/date interval from a date and then returns the date
DAY-Returns the day of the month for a given date
DAYNAME-Returns the weekday name for a given date
DAYOFMONTH-Returns the day of the month for a given date
DAYOFWEEK-Returns the weekday index for a given date
DAYOFYEAR-Returns the day of the year for a given date
EXTRACT-Extracts a part from a given date
FROM_DAYS-Returns a date from a numeric datevalue
HOUR-Returns the hour part for a given date
LAST_DAY-Extracts the last day of the month for a given date
LOCALTIME-Returns the current date and time
MAKEDATE-Creates and returns a date based on a year and a number of days value
MAKETIME-Creates and returns a time based on an hour, minute, and second value
MONTH-Returns the month part for a given date
MONTHNAME-Returns the name of the month for a given date
NOW-Returns the current date and time
SUBDATE-Subtracts a time/date interval from a date and then returns the date
SUBTIME-Subtracts a time interval from a datetime and then returns the time/datetime

MySQL JOINS

MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables.
It is performed whenever you need to fetch records from two or more tables.

Image

Best Practices

Always use proper datatype

Use datatypes based on the nature of data.
If you use irrelevant datatypes it may consume more space or may lead to errors.
Example: Using varchar (20) to store date time values instead of DATETIME datatype will lead to errors during date time-related calculations and there is also a possible case of storing invalid data.

Avoid using regional date formats

When you use DATETIME or DATE datatype always use YYYY-MM-DD date format or ISO date format that suits your SQL Engine.
Other regional formats like DD-MM-YYY, MM-DD-YYYY will not be stored properly.

Index key columns

Make sure to index the columns which are used in JOIN clauses so that the query returns the result fast.
If you use UPDATE statement that involves more than one table make sure that all the columns which are used to join the tables are indexed

Do not use functions over indexed columns

Using functions over indexed columns defeats the purpose of the index. Suppose you want to get data where first two character of customer code is AK, do not write SELECT columns FROM table WHERE left (customer_code,2)=’AK’
but rewrite it using SELECT columns FROM table WHERE customer_code like ‘AK%’ which will make use of index which results in faster response time.

Use SELECT * only if needed

Do not just blindly use SELECT * in the code. If there are many columns in the table, all will get returned which will slow down the response time particularly if you send the result to a front-end application. Explicitly type out the column names which are actually needed.

Use ORDER BY Clause only if needed

If you want to show the result in front-end application, let it ORDER the result set. Doing this in SQL may slow down the response time in the multi-user environment.

Use EXISTS clause wherever needed

If you want to check the existence of data, do not use If (SELECT count() from Table WHERE col=’some value’)>0 instead, use EXISTS clause If EXISTS(SELECT from Table WHERE col=’some value’) which is faster in response time.

EXPLAIN your SELECT queries

If you use the EXPLAIN keyword, you can get insight on what MySQL is doing to execute your query.
This can help you detect problems with your query or table structures (e.g. bottlenecks).

An EXPLAIN query results in showing you which indexes are being utilized, how the table is being scanned, sorted, etc.

All you have to do is add the keyword EXPLAIN in front of a SELECT query (preferably a complex one with joins).
Also, if you use phpmyadmin for this, your results will be shown in a nice table.

Use LIMIT 1 when getting a unique row

Sometimes you know in advance that you are looking for just one row when querying your tables. For example, you might be fetching a unique record, or you might just be checking the existence of any number of records that satisfy your WHERE clause.

In such cases, you will want to use the MySQL limit function to increase performance. Here is another of the MySQL best practices: simply add LIMIT 1 to your query. This way the database engine will not have to go through the whole table or index. It will stop scanning when it finds just 1 record of what you are looking for.

// do I have anyusersfrom Alabama?

// what NOT to do: $r = mysql_query(“SELECT * FROM user WHERE state = ‘Alabama'”); if (mysql_num_rows($r) > 0) { // … }

// muchbetter: $r = mysql_query(“SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1”); if (mysql_num_rows($r) > 0) { // … }

Index and use the same column types for joins

Another vital tip of MySQL best practices – if your application has many JOIN queries, make sure that the columns you join by are indexed on both tables.
This affects the internal optimization of the join operation by MySQL.

Also, the joined columns need to be the same type.
For example, if you join a DECIMAL column to an INT column from another table, MySQL won’t be able to use any of the indexes.
Even the character encodings need to be the same type for string type columns.

Hide MySQL from the Internet
Experienced database administrators and security personnel know it – never host the database under the Web’s server’s root.

For Web-enabled applications, MySQL should be hidden behind a firewall. Communication should be enabled only between application servers and your Web servers.

Another option is to use MySQL skip-networking. When it is enabled, MySQL only listens for local socket connections and ignores all TCP ports.

Use the smallest data types possible

Let me tell you a story. When I was attending college, the philosophy was that “memory is scarce”.
Those were the days of 256 MB hard drives. Nowadays, no one seems to care one iota about memory or hard drive space.
The new philosophy is that “memory is cheap”. It might be true in dollar terms, but reading large data types still takes longer than reading smaller ones. Large data types require more disk sectors to be read into memory.

The moral is, ignore the temptation to immediately jump to the largest data type when you design your tables. Think about using an int instead of a bigint.

Also, avoid largechar (255) text fields when a varchar or smaller char is enough.

If you use the right data type, more records will fit in memory or index key block. This leads to fewer reads and faster performance.

Rules For Database in Smartinfologiks

1)Common column -(7 column) Guid Created by Created on Edited by Edited on Blocked - enum tblid

2)Every table name ends with _tbl

3)Child tbl name generated as : parent_child_tbl

4)Every child table includes id of parent table

5)Add indexing on where clause column

6)Use proper data type with size