MySQL Commands with Examples

MySQL Commands with Examples

MySQL Commands with Examples
MySQL Commands with Examples Image Credit : eduCBA

MySQL commands Tutorial

In this Tutorial, you will learn the all MySQL Commands with examples. There are 5 basic MySQL Commands we will cover all this given below :

  1. Data Definition Language commands
  2. Data Query Language commands
  3. Data Manipulation Language commands
  4. Data Control Language Commands
  5. Transaction control Language Commands

1. Data Definition Language(DML) Commands

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

The DDL division of SQL is responsible for defining, redefining, modifying, dropping various database objects. Until now, you have learnt to used database objects. This section is going to discuss some DDL commands that prove very useful while redefining or dropping database object.

Examples of DDL Commands

(a) CREATE

1. CREATE DATABASE

Creating databases is an easier task relatively. In simplest form the Create Database command takes following syntax.

CREATE DATABASE [IF NOT EXISTS] <database name> ;

The IF NOT EXISTS clause, if used, will first test whether a database by the mentioned name already exists or not. If it does, then create database command is simply ignored, otherwise a database with mentioned name is created.

Following are some example database creation commands :

CREATE DATABASE myDB ;

Creates database having name as MyDB

CREATE DATABASE IF NOT EXISTS myDB ;

Creates a database having name as MyDB, if their is no database by the name MyDB already existing.

2. CREATE TABLE Command

Tables are defined with the CREATE TABLE command. When a table is created, its columns are named, data types and sizes are supplied for each column. Each table must have at least one column.

The syntax of CREATE TABLE command is :

CREATE TABLE <table_name>
     (<column name> <data type> [(<size>)],
      (<column name 2> <data type> [(<size>)...] ;

To create table whose schema is as follows :

employee(ecode, ename, sex, grade, gross)

The SQL command will be

CREATE TABLE employee
( ecode integer,
  ename char(20),
  sex   char(1),
  grade char(2),
  gross decimal ) ;

(b) ALTER Command

1. ALTER TABLE Command

When we define a system, we specify what data we need to store, the size and data type of that data. What can we do when the requirement change ? We can alter the tables to accommodate the changed requirements.
The ALTER TABLE command is used to change definition of existing tables. Usually, it can add columns to a table. Sometimes it can delete columns (depending on privileges) or change their sizes.
In general, in MySQL, ALTER TABLE Command is used

  • to add a column
  • to add an integrity constraint
  • to redefine a column (datatype, size, default value).

(a) Adding Columns

Syntax

ALTER TABLE <table_name>
ADD <column_name> <data type><size> [<constraint name>] ;

For example,

ALTER TABLE pet
ADD (tel_number integer) ;

(b) Modifying Column Definitions


You can use the MODIFY clause to change any of the following parts of column definition :

  • datatype
  • size
  • default value
  • NOT NULL column constraint
  • Order of column

The MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition.

Syntax

ALTER TABLE <table_name>
MODIFY (column_name newdatatype (newsize) ) [FIRST/After column] ;

For example,

ALTER TABLE pet
MODIFY (species(15)) ;

(c) Removing Table Components

If you plan to remove a component of a table, then you may use the DROP clause of ALTER TABLE. The keywords mostly used with DROP clause of ALTER TABLE command are :
PRIMARY KEY : Drops the table’s primary key constraint.


COLUMN Removes : mentioned column from the table


FOREIGN KEY : Removes the mentioned foreign key constraint from the table.


For example,

ALTER TABLE pet
DROP PRIMARY KEY, DROP FOREIGN KEY fk_1, DROP COLUMN death  ;

In the above given ALTER TABLE statement, the primary key, the FOREIGN KEY constraint namely fk_1, and the column namely death are all removed from the pet table.

(c) DROP Command.

1. DROP TABLE

The DROP TABLE Command of SQL lets you drop a table from the database.
Syntax :

DROP TABLE [IF EXISTS] <table_name>

That is, to drop table pet, you need to write :

DROP TABLE pet ;
2. DROP Database

Sometimes, you need to remove a database when you don’t need it anymore. But before making this decision, do make sure that you don’t need data stored in different tables of the database. This because, when you drop a database. This is because, when you drop a database, all its tables also get removed along with the database.
To remove a database, you need o issue a command with following syntax.

DROP DATABASE <database name> ;

That is, to drop a database namely myDB, you ‘ll be to write :

DROP DATABASE myDB ;
3. Truncate

These command is used to delete all the rows from table and free the space containing the table.

Syntax

TRUNCATE TABLE table_name;  

Example

TRUNCATE table students;

2. Data Query Language (DQL) Commands :

DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it.
Example of DQL:

(a) SELECT Query

is used to retrieve data from the database.

(a) SELECT Query

The SELECT statement is used to pull information from a table. The general form of the statement is :

SELECT what_to_select
FROM table name
WHERE conditions_to_satisfy ;
(a) Selecting all Data

The simplest form of SELECT retrieves everything from a table.
syntax

SELECT * FROM table name;

The output will display the complete table.

(b) Selecting Particular rows

You can select particular rows from a table by specify filtering condition through WHERE clause of the SELECT statement.
For example consider the table pet given below.

1. Select all pets with gender(sex) as male(“m”).

SELECT * FROM pet
WHERE sex = 'm' ;

2. Select all pets that were born on or after jan 1, 2019.

SELECT * FROM pet
WHERE birth >= '2019-01-01' ;
(c) Selecting Particular Columns

You can select particular column by specifying column-names(i.e., attributes) in the select-list of the SELECT command.
Syntax

SELECT column name

e.g.,

1. Display names and birth-dates of all pets

SELECT name, birth FROM pet ;

2. Display owners of pets born after Dec 2018.

SELECT owner FROM pet
WHERE birth > '2018-12-31' ;
(d) Eliminating Redundant Data (with Keyword DISTINCT)


By default, data is selected from all the rows of the table, even if the data appearing in the result get duplicated. The DISTINCT keyword eliminates duplicate rows from the result of a SELECT statement.
Syntax

SELECT DISTINCT column_name FROM table_name

Example

1. Display names of all pet-owners (non-redundant)

SELECT DISTINCT owner FROM pet ;

2. Display distinct species of pets from table pet.

SELECT DISTINCT(species) FROM pet
(e) Selecting From All the Rows – ALL Keyword

If in place of keyword DISTINCT, you give keyword ALL then the result retains the duplicate output rows. It is just the same as when you specify neither DISTNICT nor ALL ; ALL is essentially a clarifier rather than a functional argument. Thus if you give

SELECT ALL column_name FROM table_name

Example

SELECT ALL owner FROM pets ;

it will give all values of owner column from every row of table without considering the duplicate entries.

(f) Viewing Structure of a Table

If you want to know the structure of a table, you can use DESCRIBE or DESC command as per following syntax :

DESCRIBE or DESC <table name> :

For instance, the commands : DESCRIBE pet ; or DESC pet ; will display the structure of table pet.

DESC pet ;
(g) Performing Simple Calculations

To perform simple calculations, you can write the expression/formula to be calculated next to keyword SELECT, e.g.,

1. To calculate 3.1415966

SELECT  3.14159*6*6 ;

2. To obtain current system date ;

SEELCT curdate() ;
(h) Using Column Aliases

The column that you select in a query can be given a different name i.e., column alias name for output purposes. As per following syntax :

SELECT <column_name> AS [column_alias], ....
.......
From <table_name> ;

For example,

SELECT name AS pet name
FROM pet ;
(i) Condition Based on a Range

The BETWEEN operator defines a range of values that the column must fall in to make the condition true. The range includes both lower value and the upper value. For example, to list the pets of date of birth between 1990 and 1994, the command would be :

SELECT * FROM pet
WHERE birth between 1990-01-01 and 1994-12-31 ;
(j) Searching for NULL


The NULL value in a column can be searched for in table using IS NULL in the WHERE clause. (Relational operators like =, < > etc. can’t be used with NULL). For example, to list details of all pets whose death contain NULL (i.e., no value), you use the command :

SELECT  * FROM pet
WHERE death IS NULL ;

Non-NULL value in a table can be listed IS NOT NULL.

3. Data Manipulation Language (DML) Commands

Is dedicated to manipulating data in one way or another. This section is going to talk about various DML commands that are used for changing data in tables.
Values are placed in and removed from attributes of a relation with three DML commands :
INSERT INTO, DELETE and UPDATE Commands.

(a) INSERT INTO Statement


The rows (tuples) are added to relations using INSERT command of SQL. In it simplest form, INSERT takes the following syntax :

INSERT INTO <table_name> [<column list>]
VALUES (<value1>, <value2>, ...) ;

For example,

INSERT INTO pet
VALUES(rocky, shalu, dog, m, 1996-05-15, NULL) ;

(b) UPDATE Statement

Sometimes you need to change some or all of the values in an existing row. This can be done using the UPDATE command of SQL. The UPDATE command specifies the rows to be changed using the WHERE clause, and the new data using the SET keyword.
Syntax

UPDATE <table_name>
SET some_column = some_value
WHERE some_column = some_value

Example

UPDATE pet
SET sex = m
WHERE sex = NULL ;

(c) DELETE Statement

While working with tables, we may reach at situation where we no longer need some rows of data. In such a case, we would like to remove such rows. This can be done by using DELETE command.
The DELETE command removes rows from a table. This removes the entire rows, not individual field values, so no field argument is needed or accepted.
The DELETE statement takes the following general form :

DELETE FROM <table_name>
WHERE <condition> ;

(a) To remove all the contents of pet table, you use the command :

DELETE FROM pet ;

(b) The table would now be empty and could be destroyed with a DROP TABLE command.

To delete specific row

DELETE FROM pet
WHERE sex = NULL ;

4. Data Control Language (DCL)


DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give “rights & permissions.” Other permission controls parameters of the database system.

Examples of DCL commands :
Commands that come under DCL:

  • Grant
  • Revoke


Grant:

This command is use to give user access privileges to a database.

Syntax :

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

Example

GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke :

It is useful to back permission from the user.

Syntax :

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

Example

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;  

5. Transaction control language (TCL) Commands


Transaction control language or TCL commands deal with the transaction within the database.

Example : Commit, ROLLBACK and SAVEPOINT.

Commit

This command is used to save all the transactions to the database.

Syntax :

COMMIT;

Example

DELETE FROM pets 
WHERE owner = Harold;  
COMMIT;  

Rollback

Rollback command allows you to undo transactions that have not already been saved to the database.

Syntax :

ROLLBACK;  

Example

DELETE FROM Students  
WHERE RollNo =25
ROLLBACK;  

SAVEPOINT

This command helps you to sets a save point within a transaction.

Syntax :

SAVEPOINT SAVEPOINT_NAME;

Example

SAVEPOINT owner;

Related Video MySQL Commands Tutorial

MySQL Commands video Credit : Gate Smashers

Frequently Asked Questions

Related MySQL Commands

What are the 5 basic SQL commands?

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
Data Definition Language (DDL) DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. …
Data Manipulation Language.
Data Control Language.
Transaction Control Language.
Data Query Language.
MySQL Commands Tutorial

How do I start MySQL?

Launch the MySQL Command-Line Client. To launch the client, enter the following command in a Command Prompt window: mysql -u root -p . The -p option is needed only if a root password is defined for MySQL. Enter the password when prompted.
MySQL Commands Tutorial

Which is the basic SQL command?

SQL commands are the instructions used to communicate with a database to perform tasks, functions, and queries with data. SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.
MySQL Commands Tutorial

What is MySQL commands line?

MySQL is a simple SQL shell with input line editing capabilities. It supports interactive and no interactive use. When used interactively, query results are presented in an ASCII-table format. The output format can be changed using command options.
MySQL Commands Tutorial

Why MySQL Commands line is not opening?

You can also check the MySQL service is running in background or not. To do that open Task manager ( Press CTRL + SHIFT + ESC simultaneously ) and look for MySQL service in background process section. If it isn’t listed there then the service is stopped or disabled.
MySQL Commands Tutorial

How do I start the MySQL commands line tool?

Enter mysql.exe –uroot –p , and MySQL will launch using the root user. MySQL will prompt you for your password. Enter the password from the user account you specified with the –u tag, and you’ll connect to the MySQL server.
MySQL commands Tutorial

What are DML commands?

Data Manipulation Language or DML represents a collection of programming languages explicitly used to make changes in the database, such as: CRUD operations to create, read, update, and delete data. Using the INSERT, SELECT, UPDATE and Delete commands.
MySQL commands Tutorial

Read More :

1. MySQL ORDER BY with Examples

2. MySQL GROUP BY Clause with Examples

3. MySQL Commands