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
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.
Our Database mydb selected:
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.
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:
- BerkeleyDB (BDB)
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 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 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.
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 is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.
Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.
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:
|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:
|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
|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
|GEOMETRY||A spatial value of any type|
|POINT||A point (a pair of X Y coordinates)|
|LINESTRING||A curve (one or more POINT values)|
|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.
Delete a table from a Database
Drop table statement will delete the table permanently.
DROP TABLE IF EXISTS testuser;
Showing Tables from a Database
You want to show the tables in the database. You use the SHOW TABLES.
Describing Tables from a Database
You can also use “EXPLAIN” instead of “DESCRIBE”
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]');
INSERT A ROW
INSERT INTO profile VALUES (null,'Parvez','Khan','[email protected]');
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);
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';
INSERT MULTIPLE Records
INSERT INTO profile VALUES(null,'Atikur','Rahman','[email protected]'), (null,'anwar','khan','[email protected]'), (null,'kishor','chandra','[email protected]');
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);
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);
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;
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;
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';
WHERE clause operator
|>=||Greater than or equal to|
|<=||Less than or equal to|
|BETWEEN||Between tow specified values|
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;
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.
We need to update the record, and we need to use the UPDATE statement.
UPDATE user SET name='Mina', age=12 WHERE user_id=36;
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.
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;
To delete all users from user table, just remove the WHERE condition as follows:
DELETE FROM user;
Truncate uses to empty the whole table. All the records in the table will be deleted when you call this statement.
TRUNCATE TABLE user;
The table user will be empty. We assume that we have inserted many records into the table.
Deference between delete and truncate
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, 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.
AS key for columns alias
SELECT name As 'Full Name', sex As 'Gender' FROM user;
We use the keyword AS to label the columns name to a different name from its default name.
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';
- 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
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;
Return all the user who is 23 year old OR who is having undefined gender (NULL)
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;
- 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);
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);
SELECT * FROM todo; SELECT * FROM todo WHERE content LIKE '%your%'; SELECT * FROM todo WHERE content LIKE 'go%'; SELECT * FROM todo WHERE content LIKE '%man';
% 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%';
- 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