MySQL Commands

SQL commands:

Getting listings of databases;

mysql> SHOW DATABASES;

Creating a database-

mysql> CREATE database <databasename>;

mysql> CREATE database myschool;

Deleting a database

mysql> DROP database <databasename>;

mysql> DROP database myschool;

After we have created the database we use the USE statement to change the current

mysql> USE <database name>;

mysql> USE myschool;

Getting listings of tables in database (myschool)

mysql> SHOW TABLES;

The command DESCRIBE is used to view the structure of a table.

mysql> DESCRIBE <tablename>;

mysql> DESCRIBE student;

To remove a table (DROP)

mysql> drop table <tablename>;

mysql> drop table  student;

Creating a table (CREATE)

Creating a table in the database is achieved with a CREATE table command.

mysql> CREATE TABLE student

(lastname varchar(15),

Firstname varchar(15),

city varchar(20),

class char(2));

Insert data in Table (INSERT)

To insert new rows into an existing table use the INSERT command:

mysql>INSERT INTO student values(‘dwivedi’,’freya’,’Udaipur’,’4’);

Similarly we can insert multiple records.

Note 1: In INSERT command, only those columns can be omitted that have either default value or they allow NULL values.

Note 2: To insert NULL values in a specific column, you can type NULL without quotes.

Note 3: Dates are default entered in ‘YYYY-MM-DD’ format.

INSERT command can also be used to take or derive values from one table and place them in another by using it with a query. To do this, simply replace the VALUES clause with an appropriate query as follows:

mysql>INSERT INTO newstudent SELECT * FROM student

WHERE class=4;

Note: Both the tables must be existing tables of the database.

View data from Table (SELECT)

With the SELECT command we can retrieve (or see) previously inserted rows:

mysql> SELECT * FROM student;

A general form of SELECT is:

SELECT what to select(field name) FROM table(s)

WHERE condition that the data must satisfy;

Conditions can be set with help of following operators:

Comparison operators are: < ; <= ; = ; != or <> ; >= ; >

Logical operators are: AND ; OR ; NOT

Comparison operator for special value NULL: IS

Selecting rows by using the WHERE clause in the SELECT command

mysql> SELECT * FROM student WHERE class=“4";

Selecting specific columns(Projection) by listing their names

mysql> SELECT first_name, class FROM student;

Update data in Table (UPDATE)

To modify or update entries in the table use the UPDATE command

mysql> UPDATE student SET class=“V" WHERE firstname=“freya";

All columns will be updated with same value

mysql> UPDATE student SET class=“V";

Delete data from Table

Deleting selected rows from a table using the DELETE command

mysql> DELETE FROM student WHERE firstname=“amar";

Eliminating Redundant Data: (with Keyword DISTINCT)

DISTINCT keyword eliminates duplicate rows from the result of a SELECT statement.

mysql> SELECT DISTINCT city FROM Student

mysql> SELECT DISTINCT city FROM Student WHERE class=4

BETWEEN      - to access data in specified range

mysql> SELECT * FROM Student WHERE class between 4 and 6;

IN   - operator allows us to easily test if the expression is in the list of values.

mysql> SELECT * FROM Student WHERE class in (4,5,6);

Pattern Matching – LIKE Operator

A string pattern can be used in SQL using the following wild card

% Represents a substring in any length

_ Represents a single character

Example:

‘A%’ represents any string starting /with ‘A’ character.

‘_ _A’ represents any 3 character string ending with ‘A’.

‘_B%’ represents any string having second character ‘B’

‘_ _ _’ represents any 3 letter string.

A pattern is case sensitive and can be used with LIKE operator.

mysql> SELECT * FROM Student WHERE Name LIKE ‘A%’;

mysql> SELECT * FROM Student WHERE Name LIKE%Singh%’;

mysql> SELECT Name, City FROM Student WHERE Class>=8

AND Name LIKE ‘%Kumar%’ ;

ALTER TABLE

1.    The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

2.   The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Looked at numerous ways in which we can alter an existing table:

• Renaming a table
• Renaming a column
• Changing a column's data type
• Adding a constraint
• Removing a constraint
• Adding a column
• Removing a column
• Dropping a table

Action	Command	Notes
Add a column to a table	ALTER TABLE table_name ADD COLUMN column_name data_type CONSTRAINTS;	Alters a table by adding a column with a specified data type and optional constraints.
Alter a column's data type	ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type;	Alters the table by changing the datatype of column.
Rename a table	ALTER TABLE table_name RENAME TO new_table_name;	Changes the name of a table in the currently connected to database.
Rename a column within a table	ALTER TABLE table_name CHANGE old_column_name  new_column_name datetype_size;	Renames a column of the specified table.
Add column constraint (`NOT NULL`)	ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;	Adds a specified constraint to the specified table column.
Add table constraint	ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_clause;	Adds a specified constraint to the specified table.
Remove a table constraint	ALTER TABLE table_name DROP CONSTRAINT constraint_name;	Removes a constraint from the specified table.
Remove a column constraint	ALTER TABLE table_name ALTER COLUMN column_name DROP CONSTRAINT;	Removes a constraint from the specified column. This syntax is necessary for `NOT NULL` constraints, which aren't specifically named.
Remove a column from a table	ALTER TABLE table_name DROP COLUMN column_name;	Removes a column from the specified table.
Delete a table from the database	DROP TABLE table_name;	Permanently deletes the specified table from its database.

 

The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table

ALTER TABLE command is also used to add and drop various constraints on an existing table.

Syntax

ALTER TABLE command to add a New Column in an existing table is as follows.

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE employee

ADD (tel_number integer);

ALTER TABLE command to DROP COLUMN in an existing table is as follows.

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE employee

DROP grade;

ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

ALTER TABLE employee

MODIFY( Job char(30) );

ALTER TABLE command to change name of one column:

ALTER TABLE table_name CHANGE old_column new_column datatype;

ALTER TABLE employee

CHANGE First_Name FName varchar(30);

Ordering Query Result – ORDER BY Clause

A query result can be orders in ascending (A-Z) or descending (Z-A) order as per any column. Default is Ascending order.

mysql> SELECT * FROM Student ORDER BY class;

mysql> SELECT * FROM Student ORDER BY City;

To get descending order use DESC key word.

mysql> SELECT * FROM Student ORDER BY class DESC;

mysql> SELECT * FROM Student ORDER BY City DESC;

mysql> SELECT Name, Fname, City FROM Student

Where Name LIKE ‘R%’ ORDER BY Class;

GROUP BY:

Sometimes it is required to apply a Select query in a group of records instead of the whole table.

The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group.

We can group records by using GROUP BY <column> clause with Select command. A group column is chosen which has non-distinct (repeating) values like City, Job etc.

Lastname      Fname   City        Class

Sharma   Rajesh     Jaipur      12

Kumar     Kamal     Kota       12

Saxena    Rajeev     Kota       10

Singh      Rohit             Ajmer     10

Verma     Sachin     Jaipur      11

Example:

SELECT COUNT(class)    FROM student GROUP BY city;

COUNT(class)

2

2

SELECT city, COUNT(*)   FROM student GROUP BY city;

City        count(*)

Jaipur      2

Kota       2

Ajmer     1

The GROUP BY Clause is used to group rows with the same values.

The GROUP BY Clause is used together with the SQL SELECT statement.

The SELECT statement used in the GROUP BY clause can only be used to contain column names, aggregate functions, constants and expressions.

The HAVING clause is used to restrict the results returned by the GROUP BY clause.

Aggregate Functions

Name            Purpose

SUM()            Returns the sum of the given column.

MIN()             Returns the minimum value in the given column.

MAX()            Returns the maximum value in the given column.

AVG()             Returns the Average value of the given column.

COUNT()        Returns the total number of values/ records as per given

column.

Aggregate Functions & NULL

Null values are excluded while (avg) aggregate function is used

Consider a table Emp having following records as-

Code      Name     Sal

E1           Mohak     NULL

E2           Anuj               4500

E3           Vijay       NULL

E4           Vishal      3500

E5           Anil         4000

SQL Queries                             Result of query

mysql> Select Sum(Sal) from EMP;       12000

mysql> Select Min(Sal) from EMP;        3500

mysql> Select Max(Sal) from EMP;        4500

mysql> Select Count(Sal) from EMP;            3

mysql> Select Avg(Sal) from EMP;        4000

mysql> Select Count(*) from EMP;        5

Aggregate Functions & Group

An Aggregate function may applied on a column with DISTINCT or ALL keyword. If nothing is given ALL is assumed.

Using SUM (<Column>)

This function returns the sum of values in a given column or expression.

mysql> Select Sum(Sal) from EMP;

mysql> Select Sum(DISTINCT Sal) from EMP;

mysql> Select Sum (Sal) from EMP where City=‘Jaipur’;

mysql> Select Sum (Sal) from EMP Group By City;

mysql> Select Job, Sum(Sal) from EMP Group By Job;

Using MIN (<column>)

This function returns the Minimum value in the given column.

mysql> Select Min(Sal) from EMP;

mysql> Select Min(Sal) from EMP Group By City;

mysql> Select Job, Min(Sal) from EMP Group By Job;

Using MAX (<Column>)

This function returns the Maximum value in given column.

mysql> Select Max(Sal) from EMP;

mysql> Select Max(Sal) from EMP where City=‘Jaipur’;

mysql> Select Max(Sal) from EMP Group By City;

Using AVG (<column>)

This functions returns the Average value in the given column.

mysql> Select AVG(Sal) from EMP;

mysql> Select AVG(Sal) from EMP Group By City;

Using COUNT (<*|column>)

This function returns the number of rows in the given column.

mysql> Select Count ( * ) from EMP;

mysql> Select Count(Sal) from EMP Group By City;

mysql> Select Count(*), Sum(Sal) from EMP Group By Job;

Aggregate Functions & Conditions

You may use any condition on group, if required. HAVING <condition> clause is used to apply a condition on a group.

mysql> Select Job,Sum(Pay) from EMP

Group By Job HAVING Sum(Pay)>=8000;

mysql> Select Job, Sum(Pay) from EMP

Group By Job HAVING Avg(Pay)>=7000;

mysql> Select Job, Sum(Pay) from EMP

Group By Job HAVING Count(*)>=5;

mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group

By Job HAVING Sum(Pay)>=8000;

mysql> Select Job, Sum(Pay) from EMP Where City=‘Jaipur’

Note :- Where clause works in respect of the whole table but Having works on Group only. If Where and Having both are used then Where will be executed first.