This section helps you to get familiar with basic MySQL including managing MySQL database and manipulating data using various SQL statements such as INSERT, DELETE, UPDATE and SELECT.

To run mysql on command prompt follow the below steps:

Click your wamp server icon>MySQL>MySQL Console Or You can follow below display:

Or you can also run mysql from your command prompt:

1. Run cmd command
2. Type c: or d: on command prompt. This will be based on your WAMP server installations.
3. Assuming you have installed wamp on D: drive.
4. D:\>cd wamp
4. D:\wamp>cd bin
5. D:\wamp\bin>cd mysql
6. D:\wamp\bin\mysql>cd mysql15.1.36
7. D:\wamp\bin\mysql\mysql15.1.36>cd bin
8. D:\wamp\bin\mysql\mysql15.1.36\bin>mysql.exe -u root

These are the main steps to run mysql on command line

Now Lets Start Learning MySQL Basic

MySQL Create A Database

We need a database, and in order to create a database you need to use the following syntax.

Create a new database name called mydb

CREATE DATABASE IF NOT EXISTS mydb;

After executing the statement, MySQL will returns you a message to notify the new database created successfully or not.

Showing all the databases from your MySQL Server

SHOW DATABASES;

Display

Select the database

If you want to CREATE, INSERT, UPDATE, ALTER or DELETE a table, you need to select a database. Thus, you need to call the common use in order to use the database.

Syntax

USE mydb;

Our Database mydb selected:

Display

Delete the unused database

DROP DATABASE IF EXISTS mydb;

After executing the statement, MySQL will returns you a message to notify the new database dropped successfully or not.

Display

Understanding MySQL Table Types

MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:

• ISAM
• MyISAM
• InnoDB
• BerkeleyDB (BDB)
• MERGE
• HEAP

The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:

ISAM

ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.

MyISAM

MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.

InnoDB

Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.

BDB

BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.

MERGE

Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.

HEAP

Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.

MySQL Data Types

Database table contains multiple columns with specific data types such as numeric or string. MySQL provides you many more specific data types than just “numeric” or “string”. Each data type in MySQL can be determined by the following characteristics:

• What kind of value it can represent.
• The space values take up and whether the values are fixed-length or variable-length.
• The values of a data type can be indexed.
• How MySQL compare values of that data types.

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addtion, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except BIT type. The following table shows you the summary of numeric types in MySQL:

Numeric Types	Description
TINYINT	A very small integer
SMALLINT	A small integer
MEDIUMINT	A medium-sized integer
INT	A standard integer
BIGINT	A large integer
DECIMAL	A fixed-point number
FLOAT	A single-precision floating-point number
DOUBLE	A double-precision floating-point number
BIT	A bit field

String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using LIKE clause or regular expression. The table below shows you the string data types in MySQL:

String Types	Description
CHAR	A fixed-length non-binary (character) string
VARCHAR	A variable-length non-binary string
BINARY	A fixed-length binary string
VARBINARY	A variable-length binary string
TINYBLOB	A very small BLOB (binary large object)
BLOB	A small BLOB
MEDIUMBLOB	A medium-sized BLOB
LONGBLOB	A large BLOB
TINYTEXT	A very small non-binary string
TEXT	A small non-binary string
MEDIUMTEXT	A medium-sized non-binary string
LONGTEXT	A large non-binary string
ENUM	An enumeration; each column value may be assigned one enumeration member
SET	A set; each column value may be assigned zero or more set members

Date and Time Data Types

MySQL provides types for date and time and combination of date and time. In addition, MySQL also provide timestamp data type for tracking last change on a record. If you just want to store the year without date and month, you can use YEAR data type. Here is the table which showing MySQL date and type data types:

Date and Time Types	Description
DATE	A date value in ‘YYYY-MM-DD’ format
TIME	A time value in ‘hh:mm:ss’ format
DATETIME	A date and time value in ‘YYYY-MM-DD hh:mm:ss’ format
TIMESTAMP	A timestamp value in ‘YYYY-MM-DD hh:mm:ss’ format
YEAR	A year value in YYYY or YY format

Spatial Data Types

MySQL support many spatial data types as below table which contains various kind of geometrical and geographical values.

Spatial Data Types	Description
GEOMETRY	A spatial value of any type
POINT	A point (a pair of X Y coordinates)
LINESTRING	A curve (one or more POINT values)
POLYGON	A polygon
GEOMETRYCOLLECTION	A collection of GEOMETRY values
MULTILINESTRING	A collection of LINESTRING values
MULTIPOINT	A collection of POINT values
MULTIPOLYGON	A collection of POLYGON values

MySQL Create A TABLE

We can create a table in the database, and there are many ways to create a table.

Create a new table

CREATE TABLE IF NOT EXISTS testuser (

user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(20),

age INT,

email VARCHAR(50)

);

After executing the statement, MySQL will returns you a message to notify the new table successfully created or not.

Display

Delete a table from a Database

Drop table statement will delete the table permanently.

DROP TABLE IF EXISTS testuser;

Display

Showing Tables from a Database

You want to show the tables in the database. You use the SHOW TABLES.

SHOW TABLES;

Display

Describing Tables from a Database

DESCRIBE user;

Display

You can also use “EXPLAIN” instead of “DESCRIBE”

EXPLAIN user;

Display

INSERT DATA INTO A TABLE

You need permission in order to insert data into a table. There are many ways you can insert data into a table. You can insert data from other tables or you can insert row by rows, or multiple rows.

Create profile table and insert some sample data in order to use in this tutorial

DROP TABLE IF EXISTS `profile`;

CREATE TABLE profile (

id int(11) NOT NULL auto_increment,

first_name varchar(200) default NULL,

last_name varchar(200) default NULL,

email varchar(200) default NULL,

PRIMARY KEY  (id)

) ;

INSERT INTO profile VALUES

(1,'Mirza','Galib','[email protected]'),

(2,'Anamul','Haque','[email protected]'),

(3,'Mamun','goodgood','[email protected]'),

(4,'Sayem','Tutul','[email protected]'),

(5,'Sohel','Alom','[email protected]'),

(6,'Abu','Hanif','[email protected]');

Display

INSERT A ROW

INSERT INTO profile VALUES

(null,'Parvez','Khan','[email protected]');

Display

Description

We insert data into the profile table, as we know the order of the columns, we don’t need to list it here, but it is not a good idea to do that. Usually, we need to list the columns to make sure that we insert data in the right order. We don’t need to specific the id column, because the id is auto increment and primary key, when we assign the null value to the auto increment column, it is automatically generated the unique id for us.

INSERT INTO ROW, List the columns name

INSERT INTO profile(email, first_name, last_name, id)

VALUES('[email protected]','ShiShir','Chakraborty',null);

Display

Description

We can list the column name in any order we want, but we need to make sure that the data that we insert into the table must follow the order that we have listed, else we will get an error or we insert the data wrongly.

Insert into row Using SET

INSERT INTO profile

SET email='[email protected]', first_name='Fuad', last_name='bnp';

Display

INSERT MULTIPLE Records

INSERT INTO profile VALUES(null,'Atikur','Rahman','[email protected]'),

(null,'anwar','khan','[email protected]'),

(null,'kishor','chandra','[email protected]');

Display

Description

Insert multiple records, we must enclosed each record within parentheses and separated by commas. MySQL will automatically generated the key when you supply the insert value with the null/NULL.

INSERT Data From Another table using SELECT QUERY

INSERT INTO testProfile SELECT * FROM profile;

We Can also insert specific data from another table:

INSERT INTO testProfile
SELECT null,first_name, last_name, email
FROM profile WHERE id IN(1,4,6);

Display:

Description

We get the return data from the SELECT statement from the profile table, and we filter the return data in the WHERE clause where the id is IN(1,4,6). After that, we insert the return data from the SELECT statement into the testProfile table. We want to generate a new id in the testProfile table, so we need to assign the null value in the SELECT statement.

Create a new table from an another table with Copy its Values

We create the testTable1 table from the user table.

CREATE TABLE testTable1 AS (SELECT * FROM profile);

Display

Description

We create the testTable1 from the user table. All the records in the user table will be created in the testTable1. We use the describe to describe the table structure.

Create Table from another table without copy its values

CREATE TABLE testProfile LIKE profile;

Display

MySQL SELECT Statement

Select statement is used to retrieve table data; you must provide the table name. The select key word in MySQL is case insensitive, you can use SELECT or Select. It’s the same. We use the SELECT Statement to query the data information.

Simple Select Statement

SELECT * FROM profile;

Display:

Select limited columns

SELECT last_name, email FROM profile;

SELECT column with WHERE Clause

We need to filter the return data and we need to use the WHERE clause. WHERE clause uses to filter data according to the criteria.

SELECT * FROM profile;

SELECT * FROM profile WHERE id=2;

SELECT * FROM profile WHERE

first_name='Mamun' AND last_name='goodgood';

Display

WHERE clause operator

Operator	Description
=	Equal
!=	Not Equal
<>	Not Equal
<	Less than
>	Greater than
>=	Greater than or equal to
<=	Less than or equal to
BETWEEN	Between tow specified values

LIMIT Keyword

LIMIT keyword uses to return row specific by the total number of rows to return.

SELECT name, age FROM user;
 SELECT name, age FROM user LIMIT 2;
 SELECT name, age FROM user LIMIT 3,2;

SELECT DISTINCT rows

The Distinct keyword tells MySQL to return only distinct values

SELECT DISTINCT name From user;

Display:

Description

Tell MySQL to return only the unique values from the query. The Distinct keyword must place before a column name. The Distinct keyword will apply to all the columns. * Note: First query will return all the rows, and we can see that the user name Mirza is displayed twice. In the second query, we can see that the user Mirza is displayed only once.

Update Record

We need to update the record, and we need to use the UPDATE statement.

Update Statement

UPDATE user

SET name='Mina', age=12

WHERE user_id=36;

Description

Make sure that you have the WHERE Clause when you use the UPDATE statement. Without the WHERE Clause, you will tell MySQL to UPDATE all the records.

Delete

We have inserted record into the table, and how do we remove a record from a table. In this tutorial, we will learn how to do that with the DELETE statement.

Suppose you want to delete a user from user table with user_id is 6, just execute the following query:

DELETE FROM user

WHERE user_id = 6;

Display

To delete all users from user table, just remove the WHERE condition as follows:

DELETE FROM user;

Truncate

Truncate uses to empty the whole table. All the records in the table will be deleted when you call this statement.

Example

TRUNCATE TABLE user;

Display

The table user will be empty. We assume that we have inserted many records into the table.

Deference between delete and truncate

Delete
At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.

When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete – because a full table scan always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion – because Oracle is still scanning every single block up to the HWM – even though some (or all) of the blocks may have no data in them.

Truncate
Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to ‘delete’ as you can with the where clause on the delete command.

When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.

If you specify the reuse storage clause, then the extents are not deallocated. This saves time in the recursive SQL department if you intend to reload the table with data from an export for example, and can reduce the time it takes to do the import as there is no need to dynamically allocate any new extents.

Columns alias

AS key for columns alias

SELECT name As 'Full Name', sex As 'Gender' FROM user;

Description

We use the keyword AS to label the columns name to a different name from its default name.

Display

MySQL AND,OR,IS

AND operator

We use AND operator to filter more than one column. It returns only rows that matching all the condition.

Simple Select Statement

SELECT * FROM user;
 SELECT * FROM user WHERE age=21 AND sex='male';

Display:

Description

• The first SELECT Statement return everything
• The second SELECT Statement with the WHERE clause use the AND operator, it returns data when the age= 21 and the sex=male

OR operator

The OR operator is opposite of the AND operator. The OR operator retrieves rows that match either condition.

SELECT * FROM user WHERE age=23 OR sex IS NULL;

Display

Description

Return all the user who is 23 year old OR who is having undefined gender (NULL)

Null Value

We use IS operator to check whether the value is null or not null. NULL is no value, and it is not mean it contains 0 or have an empty string or space.

SELECT * FROM user WHERE sex IS NULL;
 SELECT * FROM user WHERE sex IS NOT NULL;

Display:

Description

• The first statement, it returns the user with the gender that is not defined (NULL)
• The second statement, it returns the user with the gender that is defined (NOT NULL)

NOT and IN Operator

• NOT is to negate a condition. When, NOT operator is placed in front of the IN Operator, it negate the condition
• IN Operator defines a list of condition that can be matched. IN operator takes a comma-delimited list of valid values, and enclosed within a parentheses

SELECT * FROM user;
 SELECT * FROM user WHERE age IN(21,13,23);
 SELECT * FROM user WHERE age NOT IN(21,13,23);

Display:

MySQL LIKE operator

LIKE operator for filter the retrieve data and perform wildcard text searches.

• Don’t misplaced the wildcard symbols, if you do, you will not get the result as you want
• Wildcard search patterns are the slowest to process
• Use another search operator instead of wildcard, such as full-text search which is better in performance

Create Table and Insert Sample record for use in this tutorial

CREATE TABLE `todo` (

`t` datetime NOT NULL,

`content` varchar(255) NOT NULL,

`status` enum('open','closed','in-progress') NOT NULL,

`id` int(11) NOT NULL auto_increment,

PRIMARY KEY  (`id`),

KEY `t` (`t`)

) ;

INSERT INTO `todo` VALUES ('2010-02-20 14:11:32','what is your name','open',1),

('2010-02-20 14:10:30','go to work man','closed',2),

('2010-08-15 23:12:35','lovely','open',6),

('2010-08-15 23:12:59','lovely','open',7);

LIKE ‘%TEXT%’

SELECT * FROM todo;
 SELECT * FROM todo WHERE content LIKE '%your%';
 SELECT * FROM todo WHERE content LIKE 'go%';
 SELECT * FROM todo WHERE content LIKE '%man';

Display:

Description

% matches any number of any character or number. In the second query, it queries data that contains the work your. The third query, it queries data that start with the word go and follow by one or many character or number. The fourth query, it queries data that starts with one or many characters or number and ended with the word man

LIKE operator 2

SELECT * FROM todo WHERE content LIKE '_ovely';
 SELECT * FROM todo WHERE content LIKE 'w%k';
 SELECT * FROM todo WHERE content LIKE 'w%%k';
 SELECT * FROM todo WHERE content LIKE '%w%k';

SELECT * FROM todo WHERE content LIKE '%w%k%';

Display:

Description

• First query, the underscore(_) uses for single character. It returns query that contains the word such as lovely, movely, oovely or movely etc. In this query, the word lovely is return
• Second query, it returns query that starts with w, follows one or many characters or numbers, end with k
• Third query, it returns query that starts with w, follows one or many characters or numbers, end with k
• Fourth query, it returns query that starts with one or many characters or numbers, follow with w than follows by one or many characters or numbers, follow with k, and end with one or many characters or numbers