sql

CBSE ORIENTATION PROGRAMME FOR INFORMATICS PRACTICES

MySQL - 1

MYSQL CHAPTER-1 Introduction

Database

A database is a organized collection of data.

Database -> Data File -> Record - > Data Item

Relational Database

It is a collection of logically related tables.

Table/Relation: A group of rows and columns form a table. The horizontal subset of the Table is known as a Row/Tuple. The vertical subset of the Table is known as a Column/an Attribute.

Example:

Table: Employee

Eno Name Desig Salary 45 Ramanuj Singh Dir 90000 32 Gareema Seth Mgr 78000 12 John Brooke Acc 25000 09 Ahmed Abdul Mgr 78000

A relation in a database has the following characteristics:

1. Every value in a relation is atomic - i.e. it can not be further divided

2. Names of columns are distinct and order of columns is immaterial

3. The rows in the relation are not necessarily ordered

Since relation is a set, and sets are not ordered hence no ordering defined on tuples of relation

Relational Database Management System

The software required to handle/manipulate these tables/relations is known as Relational Database Management System (RDBMS) MySQL, Oracle, Sybase, DB2, MS SQL Server, etc.

RDBMS Terminology:

A Candidate key is an attribute (or set of attributes) that uniquely identifies a row.

Example: Item Number and Item Descrition both may be used to uniquely identify each row in a Stock Table, so both are examples of Candidate keys.

A Primary Key is one of the candidate keys, which is used to identift a row uniquely.


MySQL - 2

2

Introduction To MYSQL Example: Admission Number in the Student Table, Accession Number of a Book in the Book table, Employee Id of an employee in the Employee Table, Item Number of an item in the Stock table, Flight Number of a flight in the Flight Master Table, etc.

Note: A table may have more than one candidate keys but definitely has one and only one primary key.

Alternate Key: Only one of the candidate key will be selected as a primary key of a table. All other candidates are called Alternate keys.

Introduction to MySQL.

MySQL is a fast, easy-to-use RDBMS used for small and big business applications. MySQL is developed, marketed, and supported by a Swedish Company MySQL AB.

MySQL is released under an open-source license so it is customizable. It requires no payment for its usage.

MySQL is a very powerful software to handle RDBMS.

MySQL uses a standard form of the well-known ANSI-SQL standards

MySQL is a platform independent application which works on many operating systems like Windows, UNIX, LINUX etc. And has compatibility with many languages including JAVA , C++, PHP, PERL, etc.

MySQL is a easy to install RDBMS and capable of handling large data sets.


MySQL - 3

MYSQL CHAPTER-2 Installing MySQL

Windows Environment DOWNLOADING MySQL Installation file for MySQL may be downloaded from the link: http://dev.mysql.com/downloads/mysql/5.1.html#downloads (Choose appropriate download link as per the operating system)

Click on the "Download" button for the Community Server. This brings further down the page, to the list of supported platforms (i.e., operating systems that it will run on), which include 32-bit and 64-bit Windows, several different Linux, Solaris, Mac OS X, and a few others.

INSTALLING MySQL After the installation file has finished downloading, double-click it, which begins the MySQL Setup Wizard.


MySQL - 4

4

Introduction To MYSQL At the welcome dialog box, click the "Next" button.

The MySQL Setup Wizard allows to choose the installation directory on the computer, and whether or not to have optional components installed. In the "Setup Type" dialog box, choose Typical from the three options. MySQL will be installed in the default directory, "C:\Program Files\MySQL\MySQL Server 5.0. Now, click the "Next" button.

Now it is ready to install MySQL's files. Click the "Install" button.

After the Setup Wizard has completed, one can and should configure the new server. Click the "Finish" button.


MySQL - 5

CONFIGURING MySQL

At the initial Server Instance Configuration Wizard dialog box, click the "Next" button. Keep selecting the default options provided in subsequent windows. If the configuration does not encounter any errors, then information will be prompted that the configuration file was created, the MySQL service was installed and started, and the security settings have been applied. Note: In the process of configuration of MySQL, a prompt for password will be displayed Remember this password will be required each time to start MySQL

Testing MySQL Follow the steps to start MySQL Start> Programs>MySQL>.>MySQL Command Line Client OR


MySQL - 6

6

Introduction To MYSQL Goto the folder C:\Program Files\MySQL\MySQL Server 5.1\bin [Assuming C:\ drive as the drive having MySQL] And Click on the following file MySQL.EXE

MySQL will prompt a message to provide password (it requires the same password, which was entered during the installation)

Enter Password:**** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.51a-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Mysql>

To exit from MySQL, type QUIT or EXIT

Mysql>QUIT

The above steps ensure successful installation and configuration of MySQL database server. Next time in the MySQL prompt, one can create and use databases, create tables and execute SQL queries.

Linux Environment

Installation of the binary version of MySQL, release 4.0.20, to run on Linux is as follows: DOWNLOADING MYSQL Installation file for MySQL may be downloaded from the link: http://dev.mysql.com/downloads/mysql/5.1.html#downloads (Choose appropriate download link as per the desired operating system) Create MySQL User Account: # cd /usr/local # groupadd mysql # useradd -c "MySQL Software Owner" -g mysql mysql # passwd mysql Changing password for user mysql. New password: ********** BAD PASSWORD: it is too short Retype new password: ********** passwd: all authentication tokens updated successfully.

INSTALLING BINARY VERSION Unzip the files and change the directory to mysql

# cd mysql

# scripts/mysql_install_db --user=mysql

Preparing db table Preparing host table


MySQL - 7

Preparing user table Preparing func table

. . . . . . . . . The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com

STARTING AND STOPPING THE DATABASE SOFTWARE

Starting the MySQL Database # su - # cd /usr/local/mysql # bin/mysqld_safe --user=mysql & Starting mysqld daemon with databases from /usr/local/mysql/data

Stopping the MySQL Database # su - # cd /usr/local/mysql # bin/mysqladmin -u root shutdown 040803 23:36:27 mysqld ended [1]+ Done bin/mysqld_safe --user=mysql

TESTING THE INSTALLATION

Ensure that the MySQL Software, the mysqld server, is running and the initial MySQL tables has been set.


MySQL - 8

8

Introduction To MYSQL MYSQL CHAPTER 3 Starting with MySQL

Connecting To Server

To start working with MySQL, a connection to the database is required. The first step in database management is to create a database (assuming the user has privilege to create database)

mysql> CREATE DATABASE School;

Now the database called School is created. One must be connected to the database before using it.

mysql> use mydatabase; Database Changed

Now, MySQL prompt can accept any query related to the database School.

To create a table in the database

Syntax:

CREATE TABLE ( , , , );

Example:

mysql> CREATE TABLE Student ( RollNo DECIMAL(3), Name VARCHAR(25) ); To add a row(s), in the table, use the following command Syntax: INSERT INTO VALUES (,, ,);

Example:

mysql> INSERT INTO Student VALUES (14,Aruna Asaf Ali); mysql> INSERT INTO Student VALUES (12,Tarun Sinha); mysql> INSERT INTO Student VALUES (16,John Fedrick); mysql> INSERT INTO Student VALUES (10,Yogi Raj Desai); To view the content of a table, use the following command Syntax: SELECT * FROM ;


MySQL - 9

Example:

mysql> SELECT * FROM Student; +---------------------------+

|Rno | Name | +---------------------------+

| 14 | Aruna Asaf Ali | | 12 | Tarun Sinha | | 16 | John Fedrick | | 10 | Yogi Raj Desai | +---------------------------+

Any time to know the database currently in use, the command SELECT DATABASE() can be used.

mysql> select database(); database() school_db 1 row in set 0.0 secs


MySQL - 10

10

Introduction To MYSQL MYSQL CHAPTER 4

Creating and Managing Tables

MySQL Data Types

Every column (or data item) should belong to a unique domain (known as data type). These data types help to describe the kind of information a particular column holds. MySQL supports the ANSI SQL data types. Some of the commonly used data types alongwith their characteristics are as follows:

Class Data Type Description Format Example Text CHAR(size) A fixed-length string between 1

and 255 characters in length right-padded with spaces to the specified length when stored. Values must be enclosed in single quotes or double quotes.

CHAR(size) Maths TexT

VARCHAR(size) A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). Values must be enclosed in single quotes or double quotes.

VARCHAR (size)

Computer Me and u

NUMERIC# DECIMAL(p,s) It can represent number with or

without the fractional part. The size argument has two parts : precision and scale. Precision (p) indicates the number of significant digits and scale (s)maximum number of digits to the right of the decimal point.

Number(p,s) 17.32 345

INT It is used for storing integer values

INT INT(5)

date DATE It represents the date including

day, month and year between 1000-01-01 and 9999-12-31

YYYY-MM-DD

2009-07-02

SQL Commands

SQL commands can be classified into the following:

Data Definition Language (DDL): A database scheme is defined by set of definitions, which are expressed, by a special set of commands called Data Definition Language (DDL). They are used to create tables, databases, identify data items, provide unique names to the data items and to define the length and provide the range of values that each data item can assume. They are CREATE TABLE, ALTER TABLE and DROP TABLE commands.

Data Manipulation Language (DML): The data manipulation language (DML) handles operations such as entering rows into a table, changing data, deleting rows, and


MySQL - 11

extracting data from rows and tables. With DML, one does not change the table's structure, but rather its contents. It contains commands like INSERT, UPDATE and DELETE.

Data Control Language (DCL): This allows definition of a security mechanisms for protecting data from unauthorized access. It contains commands like GRANT and REVOKE. CREATE TABLE Tables are defined with the CREATE TABLE command. When tables are created its columns are named, data types and sizes supplied for each column. At least one column must be specified.

Syntax:

CREATE TABLE (< column name> [ ], (< column name> [ ], ); Example: mysql> USE school_db; Database changed mysql> CREATE TABLE Student( Rollno decimal(3,0), Name varchar(15), Gender char(1), Marks1 int); Query OK, 0 rows affected (0.16 sec)

DESCRIBE The describe statement can be used to see the structure of the table as indicated in the Create Command,

mysql> DESCRIBE Student; +--------+------------------+------+-----+---------+---------------+

| Field | Type | Null | Key |Default | Extra | +--------+------------------+------+-----+---------+---------------+

| rollno | decimal(3,0) | | | | | | name | varchar(15) | | | | | | gender | char(1) | | | | | | marks1 | int | | | | | +--------+------------------+------+-----+---------+---------------+

4 rows in set (0.00 sec) ALTER Command The ALTER statement can add, remove or change table's column(s). Syntax: ALTER TABLE ADD/DROP [datatype]; Example: mysql> ALTER TABLE student ADD games VARCHAR(5); mysql> DESCRIBE Student; +--------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+


MySQL - 12

12

Introduction To MYSQL | rollno | decimal(3,0) | | | | | | name | varchar(15) | | | | | | gender | char(1) | | | | | | marks1 | int | | | | | | games | varchar(5) | | | | | +--------+------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec) After this command the games column will be added and a null value will be assigned for all the rows in this column. mysql> SELECT * from student;

+-----------+---------+-----------+-----------+-------+---------+

| rollno | name | Gender | Marks1 |Grade | games | +-----------+---------+-----------+-----------+-------+---------+

| 15001 | Nidhi | F | 60 | C | NULL | | 15002 | John | M | 70 | B | NULL | | 15003 | Mukesh | M | 78 | B | NULL | | 15004 | Priya R | F | 68 | C | NULL | | 15005 | Ashish Jr| M | 84 | A | NULL | | 15006 |Govindan | M | 52 | D | NULL | | 15007 |Maya | F | 60 | C | NULL | | 15008 |Priya | F | 80 | A | NULL | | 15009 |Radha | F | 55 | D | NULL | | 15010 |Sneha | F | 70 | B | NULL | | 15011 |Ashish | M | 40 | F | NULL | | 15012 |Mukund | M | 55 | D | NULL | | 15013 |Asif | M | 90 | S | NULL | | 15014 |Helen | F | 74 | B | NULL | | 15015 |Goody | F | 87 | S | NULL | +-----------|---------|-----------|-----------|-------|----------+ 15 rows in set (0.15 sec) Changing a Column Definition:

Syntax:

ALTER TABLE MODIFY So to change the newly added games COLUMN to hold 3 figure values we would

enter:

mysql> ALTER TABLE Student MODIFY games INT(3);

Deleting Column of a Table

To delete a column of a table the ALTER command can be used with Drop command.

mysql> ALTER Student DROP games;

mysql> DESC Student;

+--------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+

| rollno | decimal(3,0) | | | | | | name | varchar(15) | | | | | | gender | char(1) | | | | | | marks1 | int | | | | | +--------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)


MySQL - 13

MYSQL CHAPTER -5 MySQL Select Statement

The SQL SELECT command is used to fetch data from MySQL database. There are various ways and combinations, a select command can be used into.

Complete Syntax: SELECT */ select rollno from student; +--------------------------+

| roll no | +--------------------------+

| 15001 | | 15002 | | 15003 | | 15004 | | 15005 | | 15006 | | 15007 | | 15008 | | 15009 | | 15010 | | 15011 | | 15012 | | 15013 | | 15014 | | 15015 | +--------------------------+


Selecting more than one Columns Syntax:

SELECT FROM ;

mysql> SELECT rollno, name from student; +-----------+-----------+

| rollno | name | +-----------+-----------+

| 15001 | Nidhi | | 15002 | John | | 15003 | Mukesh | | 15004 | Priya R | | 15005 | Ashish Jr| | 15006 | Govindan | | 15007 | Maya | | 15008 | Priya | | 15009 | Radha |


MySQL - 14

14

Introduction To MYSQL | 15010 | Sneha | | 15011 | Ashish | | 15012 | Mukund | | 15013 | Asif | | 15014 | Helen | | 15015 | Goody | +-----------|-----------+ 15 rows in set (0.10 sec)

Selecting all Columns

mysql> SELECT * FROM student;

+-----------+---------+----------+-----------+-------+

| rollno | name | Gender | Marks1 |Grade | +-----------+---------+----------+----------+-------+

| 15001 |Nidhi | F | 60 | C | | 15002 |John | M | 70 | B | | 15003 |Mukesh | M | 78 | B | | 15004 |Priya R | F | 68 | C | | 15005 |Ashish Jr| M | 84 | A | | 15006 |Govindan | M | 52 | D | | 15007 |Maya | F | 60 | C | | 15008 |Priya | F | 80 | A | | 15009 |Radha | F | 55 | D | | 15010 |Sneha | F | 70 | B | | 15011 |Ashish | M | 40 | F | | 15012 |Mukund | M | 55 | D | | 15013 |Asif | M | 90 | S | | 15014 |Helen | F | 74 | B | | 15015 |Goody | F | 87 | S | +----------|---------|----------|-----------|-------| 15 rows in set (0.15 sec)

Eliminating redundant data

By default data is selected from all the rows of the table, even if the data in the result gets duplicated. Using the keyword DISTINCT, the duplicity can be eliminated in the result.

mysql> SELECT DISTINCT marks1 FROM student;

This will provide result as

+------+

|marks1| +------+

| 40 | | 52 | | 55 | | 60 | | 68 | | 70 | | 74 | | 78 | | 80 | | 84 | | 87 | | 90 | +------+

12 rows in set (0.15 sec) Selecting specific rows WHERE clause


MySQL - 15

Instead of displaying all the rows certain rows can be displayed based on the criteria for selection of rows using the keyword WHERE

mysql> SELECT marks1 FROM student WHERE marks1 > 70;

The output would be

+------+

|marks1| +------+

| 74 | | 78 | | 80 | | 84 | | 87 | | 90 | +------+

MySQL OPERATORS AND THEIR PRECEDENCE MySQL has the following operators: Arithmetic Operators +, -, *, /, %

mysql> SELECT marks1*2 FROM student WHERE marks1 > 70;

The output would be

+--------+

|marks1*2| +--------+

| 148 | | 156 | | 160 | | 168 | | 174 | | 180 | +-------+

Relational Operators =, >, =, Comparison Operators LIKE, BETWEEN, IN Logical Operators AND, OR All these operators have precedence. Precedence determines which operation will be performed first in an expression comprising of multiple operators. Parenthesis can be used to change the preference of an operator.

Operator Precedence

() 1

* / 2

+ - 3

< >= 4

LIKE IN BETWEEN 5

AND OR 6

Arithmetic Operators MySQL supports the following 5 arithmetic operators: + (Addition), - (Subtraction), *(Multiplication), / (Division) and % (modulo ie remainder)

mysql> SELECT 6+3;


MySQL - 16

16

Introduction To MYSQL +-----+ | 6+3 | +-----+

| 9 | +-----+

1 row in set (0.00 sec)

mysql> SELECT 6-3; +-----+

| 6-3 | +-----+

| 3 | +-----+

1 row in set (0.01 sec) mysql> SELECT 6*3; +-----+

| 6*3 | +-----+

| 18 | +-----+

1 row in set (0.00 sec) mysql> SELECT 6/3; +------+

| 6/3 | +------+

| 2.00 | +------+

1 row in set (0.22 sec) Relational Operators

To compare two values, a relational operator is used. The relational operators are =, >, =, . They are combined with WHERE clause.

Logical Operators

The logical operators OR, AND, NOT are used to connect search conditions in the WHERE .

mysql> SELECT rollno, name FROM student WHERE marks1 > 50 AND marks1 < 60;

The output would be

+------+-----------+

|rollno| name | +------+-----------+

| 15006 |Govindan | | 15009 |Radha | | 15012 |Mukund | +------+----------+

Condition based on Range

The BETWEEN operator defines the range of values that the column values must fall into to make the condition true. The range has both the upper and lower value.

mysql> SELECT rollno, name FROM student WHERE marks1 BETWEEN 50 AND 60;

The output would be

+------+----------+

|rollno| name |


MySQL - 17

+------+----------+ | 15006 |Govindan | | 15009 |Radha | | 15012 |Mukund | +------+----------+

Condition based on a List

The IN operator selects values that match any value in the given list of values that the column values must fall into to make the condition true.

mysql> SELECT rollno, name FROM student WHERE marks1 IN (68,70,76, 80);

The output would be

+--------+----------+

|rollno | name | +--------+----------+

| 15002 |John | | 15004 |Priya R | | 15008 |Priya | | 15010 |Sneha | +--------+----------+

Condition based on pattern matches

Here is generic SQL syntax of SELECT command along with LIKE clause to fetch data from MySQL table:

SELECT , [] WHERE LIKE Pattern [AND [OR]] ;

Like clause can be used instead of equal sign to match patterns (a meta character).

mysql> SELECT * FROM student WHERE name LIKE %ya;

The output would be

+-----------+---------+-----------+-----------+-------+

| rollno | name | Gender | Marks1 |Grade | +-----------+---------+-----------+-----------+-------+

| 15007 |Maya | F | 60 | C | | 15008 |Priya | F | 80 | A | | 15009 |Radha | F | 55 | D | | 15010 |Sneha | F | 70 | B | +-----------|---------|-----------|-----------|-------| 4 rows in set (0.05 sec)

Sorting the Results- ORDER BY

The results obtained using SELECT command can be sorted either in ascending or descending values of a single column or columns using ORDER BY clause.

SELECT , [] WHERE { = condition1] [ORDER BY ];

mysql> SELECT * FROM student WHERE marks1 > 70 ORDER BY name;


MySQL - 18

18

Introduction To MYSQL

The output would be

+-----------+---------+-----------+-----------+-------+


| 15005 |Ashish Jr| M | 84 | A | | 15013 |Asif | M | 90 | S | | 15015 |Goody | F | 87 | S | | 15014 |Helen | F | 74 | B | | 15003 |Mukesh | M | 78 | B | | 15008 |Priya | F | 80 | A | +-----------|---------|-----------|-----------|-------|

mysql> SELECT * FROM student WHERE marks1>60 ORDER BY Gender ASC, marks1 DESC;

The output would be

+-----------+----------+---------+-----------+-------+

| rollno | name | Gender | Marks1 |Grade | +-----------+----------+---------+-----------+-------+

| 15015 |Goody | F | 87 | S | | 15008 |Priya | F | 80 | A | | 15014 |Helen | F | 74 | B | | 15010 |Sneha | F | 70 | B | | 15004 |Priya R | F | 68 | C | | 15013 |Asif | M | 90 | S | | 15005 |Ashish Jr | M | 84 | A | | 15003 |Mukesh | M | 78 | B | | 15002 |John | M | 70 | B | -----------|-----------|---------|-----------|-------| If the name has more than one same value then those rows will be sorted as per the marks1 in descending order.


MySQL - 19

MYSQL CHAPTER-6 Built-in Functions

MySQL supports functions that can be used to manipulate data. Such functions can be used to manipulate data.

Single-row functions return a single result row for every row of a queried table. They are categorize into: Numeric functions, String functions, and Date and Time functions.

Numeric Functions: Numeric functions accept numeric input and return numeric values.

Numeric Functions

SNo Name Description Example

1 POWER() Return the argument raised to the specified power. POW () works the same way.

(i) POW(2,4); Result:16 (ii) POW(2,-2); Result:0.25 (iii) POW(-2,3) Result: -8

2 ROUND() ROUND(X) :Rounds the argument ROUND(X,D) : Rounds the argument to d decimal places.

(i) ROUND(-1.23); Result: -1 (ii) ROUND(-1.58); Result: -2 (iii) ROUND(1.58); Result: 2 (iv) ROUND(3.798, 1); Result: 3.8 (v) ROUND(1.298, 0); Result: 1 (vi) ROUND(23.298, -1); Result: 20

3 TRUNCATE() Truncates the argument to specified number of decimal places

(i) TRUNCATE(7.29,1) Result: 7.2 (ii) TRUNCATE(27.29,-1) Result: 20


MySQL - 20

20

Introduction To MYSQL Character Functions

SNo Name Description Example 1 LENGTH() Returns the length of a string

in bytes. LENGTH(INFORMATICS) Result:11

2 CHAR() Return the character for each integer passed.

CHAR(65) Result : A

3 CONCAT() Returns concatenated string. CONCAT(Informatics, , Practices) Result : Informatics Practices

4 INSTR() Returns the index of the first occurrence of substring.

INSTR(Informatics, mat) Result : 6

5 LOWER() Returns the argument in lowercase.

LOWER(INFORMATICS) Result : informatics

6 LCASE() Works the same way as LOWER().

LCASE(INFORMATICS) Result : informatics

7 UCASE() Returns the argument in uppercase.

UCASE(informatics) Result : INFORMATICS

8 UPPER() Works the same way as UPPER().

UPPER(informatics) Result : INFORMATICS

9 LEFT() Returns the leftmost number of characters as specified.

LEFT(INFORMATICS PRACTICES, 3) Result : INF

10 RIGHT() Returns the specified number of characters from the rightmost position.

RIGHT(INFORMATICS PRACTICES, 3) Result : CES

11 MID() Returns a substring starting from the specified position.

MID(INFORMATICS PRACTICES, 3,4) Result : FORM

12 LTRIM() Removes leading spaces. LTRIM(' INFORMATICS') Result: 'INFORMATICS

13 RTRIM() Removes trailing spaces. RTRIM('INFORMATICS ') Result: 'INFORMATICS

14 TRIM() Removes leading and trailing spaces.

TRIM(' INFORMATICS ') Result: 'INFORMATICS

15 SUBSTR() Returns the substring as specified.

SUBSTRING('Informatics Practices',5) Result : 'rmatics Practices' SUBSTRING('Informatics Practices',5,3) Result : 'rma'

String functions are used to extract, change, format or alter character strings.


MySQL - 21

Date and Time Functions Date Time functions manipulate the display format of dates and time.

SNo Name Description Example 1 CURDATE() Returns the current date CURDATE();

Result: '2009-07-21'

2 NOW() Returns the current date and time

NOW(); Result : '2009-07-21 13:58:11'

3 SYSDATE() Return the time at which the function executes

SYSDATE(); Result: '2009-07-21 13:59:23

4 DATE() Extracts the date part of a date or datetime expression

DATE('2003-12-31 01:02:03'); Result:: '2003-12-31'

5 MONTH() Returns the month from the date passed

MONTH('2009-07-21'); Result : 7

6 YEAR() Returns the year YEAR('2009-07-21'); Result : 2009

7 DAYNAME() Returns the name of the weekday

DAYNAME('2009-07-21'); Result : TUESDAY

8 DAYOFMONTH() Returns the day of the month (0-31)

DAYOFMONTH('2009-07-21'); Result: 21

9 DAYOFWEEK() Returns the weekday index of the argument

DAYOFWEEK('2009-07-21'); Result: 3 (Sunday is counted as 1)

10 DAYOFYEAR() Return the day of the year (1-366)

i) DAYOFYEAR('2009-07-21'); Result: 202 DAYOFYEAR('2009-01-01'); Result: 1

AGGREGATE FUNCTIONS COUNT(), SUM(),AVG(), MAX(), MIN() COUNT() Outputs the number of rows or column values selected by the query Example mysql> USE school_db; Database changed mysql> SELECT COUNT(rollno) from Student;


MySQL - 22

22

Introduction To MYSQL COUNT(rollno) ---------------------------------

14

The most common usage for this is just to specify an asterisks as the column to count the number of rows (or in this case rollno).

Example

SELECT COUNT(*) FROM Student WHERE rollno > 15010;

COUNT(rollno) ---------------------------------

5

If distinct occurrence of values in a column is required, DISTINCT keyword is used with the column name.

SELECT COUNT(DISTINCT marks1) FROM Student WHERE marks1 IN BETWEEN 69 AND 80;

COUNT(marks1) ---------------------------------

3

Even though there were two students with marks 70 only one occurrence was counted.

SUM() - The SUM aggregate function calculates the sum total of values in a column. The column using sum must be of numeric data type.

mysql> SELECT SUM(marks1) FROM Student; SUM(marks1) ---------------------------------

215

AVG()- The AVG( ) calculates the average or arithmetic mean of the values mysql> SELECT AVG(marks1) from Student;

AVG(marks1) ---------------------------------

483.3

MAX()- The MAX( ) calculates the maximum value of the column data

mysql> SELECT MAX(marks1) from Student;

MAX(marks1) ---------------------------------

60 MIN- The MIN( ) calculates the minimum value of the column data

mysql> SELECT MIN(marks1) from Student;

MIN(marks1) ---------------------------------


MySQL - 23

40 COLUMN ALIAS

While displaying the column values after extracting using select queries it might be preferable for the result to show different headings instead of the column name included in the table. This can be done by providing an Alias to the column name (pseudoname). This will not affect the original column headings of the table or their values. For example if the value of the column to be sorted is based on an expression, column alias can be used and the same can be used in the ORDER BY clause.

SELECT Name, Mark1*2 AS Total , grade FROM Student WHERE marks1>60 ORDER BY Total desc; +-----------+---------+-------+

| Name | Total | Grade | +------------+---------+------+

| Asif | 180 | S | | Goody | 174 | S | | Ashish Jr| 168 | A | | Priya | 160 | A | | Mukesh | 156 | B | | Helen | 148 | B | | John | 140 | B | | Sneha | 140 | B | -----------|---------|---------| Working with Character Strings and Dates, Working with NULL values Strings are values such as english, Maximum MARKS. They can use either a single quote or double quotes to surround a string value. Several escape sequences are recognized within strings and can be used to indicate special characters. \0 NULL \ single quote \ double quote \b backspace \t tab \n newline \\ backslash Quotes can be included within strings.

Examples

I cant He said, I told you so. I cant I can\ t Date and Time values. Dates and times are values such as 2009-07-06 or 21:25:43. MySQL also understands combined date/time values such as 2009-07-06 21: 34:25. By default it represents year- month-day format.


MySQL - 24

24

Introduction To MYSQL The NULL Values NULL is something of a typeless value. Generally it is used to mean no value, unknown value or missing value. Inserting NULL values into table columns, retrieve them from tables and test whether or not a value is NULL. NULL values do not allow any arithmetic operation on them.

A condition that compares column value to NULL, does not work properly.

To handle such situation MySQL provides three operators

IS NULL: operator returns true of column value is NULL.

IS NOT NULL: operator returns true of column value is not NULL.

operator compare values, which (unlike the = operator) is true even for two NULL values

Conditions involving NULL are special. One cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it's impossible to tell whether or not they are true. Even NULL = NULL fails.

To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

Suppose a table tcount_tbl is in School_db database and it contains two columns author and count, where a NULL count indicates that the value is unknown: It was created using the command,

mysql> create table tcount_tbl -> ( -> author varchar(40) NOT NULL, -> count INT -> ); Query OK, 0 rows affected (0.05 sec)

After inserting some rows now on firing select query

mysql> SELECT * from tcount_tbl; +-----------------+----------------+

| author | Count | +-----------------+----------------+

| mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | +-----------------+----------------+


To find records where the count column is or is not NULL, the queries should be written like this:

mysql> SELECT * FROM tcount_tbl WHERE count IS NULL; +-----------------+----------------+

| author | count | +-----------------+----------------+

| mahnaz | NULL | | Jen | NULL | +-----------------+----------------+



MySQL - 25

MYSQL CHAPTER-7 Grouping

Generating Summaries One of the most useful things that is done by MySQL is to summarize lots of raw data. The following query shows how many students are there in the student table. The GROUP BY clause lets us group the results of a SELECT command before printing them. It returns single value for a set of rows. It can be applied to all numeric data types, some char and date data types. Grouping can be done by column names or aggregate functions. SELECT COUNT(*), marks1 FROM student GROUP BY marks1; The output would be

+---------+------------+

| marks1 | count(*) | +-----------+-------- +

| 60 | 2 | | 70 | 2 | | 78 | 1 | | 68 | 1 | | 84 | 1 | | 52 | 1 | | 80 | 1 | | 55 | 2 | | 40 | 1 | | 90 | 1 | | 74 | 1 | | 87 | 1 | +---------+------------+

12 rows in set (0.15)

The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can do so.

SELECT COUNT(*), marks1 FROM student GROUP BY marks1 HAVING count(*) > 1;

+---------+-------------+

| marks1 | count)(*) | +---------+-------------+

| 60 | 2 | | 70 | 2 | | 55 | 2 | +---------+-------------+

3 rows in set (0.05) This counts the number of times a row (or field) is returned. COUNT() The most common usage for this is just to specify an asterisks * as the column to count the number of rows (or in this case marks1).


MySQL - 26

26

Introduction To MYSQL MYSQL CHAPTER-8 DML Statements

The data manipulation language (DML) handles operations such as entering rows into a table, changing data, deleting rows, and extracting data from rows and tables. With DML, one does not change the table's structure, but rather its contents. The DML commands are: DELETE removes rows from a table INSERT enters rows into a table SELECT extracts data from rows in a table UPDATE changes the values of the columns in a row As seen in an earlier MYSQL CHAPTER, SELECT is the most comprehensive DML command. It has several functions, but the most common is to extract data from a table.

INSERTING DATA INTO TABLE

The rows are added to the tables using INSERT command of MySQL.

Syntax:

INSERT INTO [] VALUES (, );

mysql> INSERT INTO student VALUES(15016,Ravi,M,80,A); Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0

The order of values should match the order of columns in the CREATE TABLE command of the Student table.

Alternate Syntax :

mysql> INSERT INTO student (rollno,name,gender,marks1,grade) VALUES(15017,Manju,F,80,A); Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0

Here the column names are explicitly specified.

It is possible to insert values for specific columns. Those columns that are not specified in the list will have the default values; if it is defined for them else NULL value will be inserted. Example in the Student table the marks1 column has default value 25 declared.

mysql> INSERT INTO student (rollno,gender, grade) VALUES(15018,F,A); Query OK. 1 row affected.

mysql > Select * from student where rollno = 15018;


MySQL - 27

+--------+---------+--------+-------+-------+

|rollno | name | gender |marks1 |grade | +--------+---------+--------+-------+-------+

| 15018 | | F | 25 | A | +--------+---------+--------+-------+-------+


Inserting NULL Values

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string ''. This is not the case. For example, the following statements are completely different:

Consider a table called my_table having column called PHONE, IDATE

mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (''); Both statements insert a value into the phone column, but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as phone number is not known and the meaning of the second can be regarded as the person is known to have no phone, and thus no phone number.

Inserting Date Values

The default way to store a date in MySQL is with the type DATE. Below is the proper format of a DATE.

YYYY-MM-DD Date Range: 1000-01-01 to 9999-12-31

While entering a date in a format other than the Year-Month-Day format, it might work, but it won't be storing them as expected. To insert the current date into the table MySQL's built-in function CURDATE() can be used in the query. Following are some examples of inserting date values.

mysql> INSERT INTO my_table (idate) VALUES (19970505); mysql> INSERT INTO my_table (idate) VALUES ('97-05-05'); mysql> INSERT INTO my_table (idate) VALUES ('1997.05.05'); mysql> INSERT INTO my_table (idate) VALUES ('1997 05 05'); mysql> INSERT INTO my_table

(idate) VALUES ('0000-00-00');

DELETE COMMAND

This is used to delete rows from a table. This removes the entire rows, not the individual column values.

Syntax:

mysql> DELETE FROM < tablename> [ Where < condn>]; Query OK. 1 row affected mysql > Select * from student where rollno = 15018; +--------+---------+--------+-------+-------+


| | | | | | +--------+---------+--------+-------+-------+

It can be used to delete all rows of the table also.

mysql> Delete from Student;


MySQL - 28

28

Introduction To MYSQL mysql > Select * from student; +--------+---------+--------+-------+-------+


| | | | | | +--------+---------+--------+-------+-------+


UPDATE COMMAND

There may be a requirement where existing data in a MySQL table need to be modified. UPDATE command can help in the same. This will modify any column value of any MySQL table.

Syntax:

UPDATE SET = , [ = , ] [WHERE ]; The command can be used to update one or more column all together. WHERE clause helps in updation of selected rows in a table.

Example: mysql> UPDATE Student SET name = Manjula where rollno = 15017; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0

Multiple columns can also be updated at one time.

mysql> UPDATE Student SET name = Manjula , marks1 = 70, grade = B where rollno = 15017; Query OK , 1 row affected (0.8 sec)

If the condition satisfies the column values of more than one row of the tables then all those rows will be affected.

mysql> UPDATE Student SET grade = E where marks1 between 50 AND 55 Query OK , 1 row affected (0.8 sec)

Mysql> Select * from Student; +-----------+---------+-----------+-----------+-------+


| 15001 | Nidhi | F | 60 | C | | 15002 | John | M | 70 | B | | 15003 | Mukesh | M | 78 | B | | 15004 | Priya R | F | 68 | C | | 15005 | Ashish Jr| M | 84 | A | | 15006 |Govindan | M | 52 | E | | 15007 |Maya | F | 60 | C | | 15008 |Priya | F | 80 | A | | 15009 |Radha | F | 55 | D | | 15010 |Sneha | F | 70 | B | | 15011 |Ashish | M | 40 | F | | 15012 |Mukund | M | 55 | D | | 15013 |Asif | M | 90 | S | | 15014 |Helen | F | 74 | B | | 15015 |Goody | F | 87 | S | +-----------|---------|-----------|-----------|-------|


MySQL - 29

MYSQL CHAPTER-9 Working with Two Tables

Thus far only data from one table had been retrieved at a time. This is fine for simple takes, but in most real world MySQL usage often there is a need to get data from multiple tables in a single query. Multiple tables can be used in a single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.

JOINS can be used in SELECT, UPDATE and DELETE statements to join MySQL tables. Lets see an example of LEFT JOIN also which is different from simple MySQL JOIN.

Suppose we have two tables Student and Activity in School_db. A complete listing is given below:

Example:

Try out following examples:

mysql> use School_db; Database changed mysql> SELECT * from student; +-----------+---------+-----------+-----------+-------+


| 15001 | Nidhi | F | 60 | C | | 15002 | John | M | 70 | B | | 15003 | Mukesh | M | 78 | B | | 15004 | Priya R | F | 68 | C | | 15005 | Ashish Jr| M | 84 | A | | 15006 |Govindan | M | 52 | D | | 15007 |Maya | F | 60 | C | | 15008 |Priya | F | 80 | A | | 15009 |Radha | F | 55 | D | | 15010 |Sneha | F | 70 | B | | 15011 |Ashish | M | 40 | F | | 15012 |Mukund | M | 55 | D | | 15013 |Asif | M | 90 | S | | 15014 |Helen | F | 74 | B | | 15015 |Goody | F | 87 | S | +-----------|---------|----------|-----------|-------| 15 rows in set (0.15 sec) mysql> SELECT * from Activity; +----- --+------- --| | Gender | activity | +--------+-- -------+

| M | cricket | | F | hockey | +--------+----------+

2 rows in set (0.00 sec) mysql>

Now a MySQL query can be written to join these two tables. This query will select all the students from table student and will pickup corresponding activity from Activity table.

mysql> SELECT a.rollno, a.name, b.activity FROM Student a, Activity b WHERE a.gender = b.gender;


MySQL - 30

30


+-----------+-----------+--------------+

| rollno | name | activity + +-----------+-----------+--------------+

| 15001 | Nidhi | hockey | | 15002 | John | cricket | | 15003 | Mukesh | cricket | | 15004 | Priya R | hockey | | 15005 | Ashish Jr| cricket | | 15006 | Govindan | cricket | | 15007 | Maya | hockey | | 15008 | Priya | hockey | | 15009 | Radha | hockey | | 15010 | Sneha | hockey | | 15011 | Ashish | cricket | | 15012 | Mukund | cricket | | 15013 | Asif | cricket | | 15014 | Helen | hockey | | 15015 | Goody | hockey | +-----------|-----------+--------------| This is also called as EQUI JOIN . MySQL LEFT JOIN:

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives extra consideration to the table that is on the left. Consider one more table called Sports of the Student_db;

mysql> use School_db; Database changed Mysql> Select * from Sports; +-------------+-----------+----------+---------+

| rollno | name | gender | activity| +-------------+-----------+----------+---------+

| 15001 | Nidhi | F | cricket | | 15002 | Jo | M | hockey | | 15008 | Priya | F | volleyball| | 15009 | Radha | F | cricket | | 15012 | Mukund | M | hockey | | 15013 | Asif | M | cricket | +--------------+-----------+-----------+----------+

mysql> SELECT a.rollno, a.name, b.activity FROM student a LEFT JOIN activity b ON a.gender = b.gender; +--------+----------+----------------+

| rollno | name | activity | +--------+----------+----------------+

| 15001 |Nidhi | cricket | | 15004 |Priya R | null | | 15007 |Maya | null | | 15008 |Priya | volleyball | | 15009 |Radha | cricket | | 15010 |Sneha | null | | 15014 |Helen | null | | 15015 |Goody | null | +---------+---------+----------------+


MySQL CROSS JOIN: A MySQL cross join returns all the rows from both the tables. Consider one more table called Sports , Hours of the Student_db;


MySQL - 31

mysql> USE School_db; Mysql> SELECT * FROM Sports; +-------------+-----------+----------+---------+

| rollno | name | gender | activity| +-------------+-----------+----------+---------+

| 15001 | Nidhi | F | cricket | | 15002 | Jo | M | hockey | | 15008 | Priya | F | volleyball| | 15009 | Radha | F | cricket | | 15012 | Mukund | M | hockey | | 15013 | Asif | M | cricket | +------------+-----------+----------+----------+

mysql> SELECT * FROM Hours;

+---------------+

| hours | +---------------+

| mor | | eve | +---------------+

Now on performing cross join

mysql> SELECT c.rollno, C.name, O.hours FROM sports as C CROSS JOIN hours as O;

+-------------+-----------+----------+

| rollno | name | hours | +-------------+-----------+----------+

| 15001 | Nidhi | mor | 15002 | Jo | mor | 15008 | Priya | mor | 15009 | Radha | mor | 15012 | Mukund | mor | 15013 | Asif | mor | 15001 | Nidhi | eve | 15002 | Jo | eve | 15008 | Priya | eve | 15009 | Radha | eve | 15012 | Mukund | eve | 15013 | Asif | eve +--------------+-----------+-----------+


MySQL - 32

32


MYSQL CHAPTER-10 Drop Table

To remove all entries from the table the DELETE statement can issued without any conditions. DELETE from activity;

Query OK, 0 rows affected (0.00 sec) However, this does not delete the table. The table still remains, which can be checked with SHOW TABLES; mysql> SHOW TABLES;

+---------------------+

| Tables in school_db | +---------------------+

| student | | sports | | activity | +---------------------+


To delete the table, a DROP table command can be issued.

DROP TABLE activity;

Query OK, 0 rows affected (0.01 sec) Now, we won't get this table in SHOW TABLES; listing

mysql> SHOW TABLES;

+---------------------+

| Tables in school_db | +---------------------+

| student | | sports | +---------------------+



MySQL - 33

MYSQL CHAPTER-11 Concept of Transactions

In the SQL world, the term "transaction" refers to a series of SQL statements which are treated as a single unit by the RDBMS. Typically, a transaction is used to group together SQL statements which are interdependent on each other; a failure in even one of them is considered a failure of the group as a whole. Thus, a transaction is said to be successful only if *all* the individual statements within it are executed successfully.

Any RDBMS, which supports transaction must conform to the so-called "ACID rules", which specify the fundamental principles for truly secure transactions. The START TRANSACTION or BEGIN statement begins a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes.

Let's create a sample table, and see how transactions work:

mysql> CREATE TABLE t (f INT);

Now to begin a transaction, and insert a record:

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t(f) VALUES (1);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;

+------+

| f | +------+

| 1 | +------+


mysql> ROLLBACK;

Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM t;

Empty set (0.00 sec)

Without a COMMIT statement, the insert was not permanent, and was reversed with the ROLLBACK. Note that the added record was visible during the transaction from the same connection that added it.


MySQL - 34

34

Introduction To MYSQL Appendix A

In this section, two methods for making backups of MySQL data and database structures are discussed, namely mysqldump and mysqlhotcopy.

mysqldump

The utility mysqldump provides a rather convenient way to dump existing data and table structures. Note that while mysqldump is not the most efficient method for creating backups (mysqlhotcopy is, described next), it does offer a convenient method for copying data and table structures which could then be used to repopulate another SQL server, that server not even necessarily being MySQL.

The function mysqldump can be used to backup all databases, several databases, one database, or just certain tables within a given database.

Using mysqldump to backup just one database:

%>mysqldump [options] db_name

Using mysqldump to backup several tables within a database:

%>mysqldump [options] db_name table1 table2 . . . tableN

Using mysqldump to backup several databases:

%>mysqldump [options] --databases [options] db_name1 db_name2 . . . db_nameN

mysqlhotcopy

The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.

The function mysqlhotcopy can be executed to backup one database, a number of databases, or only those databases matching a name specified by a regular expression. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqlhotcopy to backup just one database:

%>mysqlhotcopy [options] db_name /path/to/new_directory

Using mysqlhotcopy to backup just several databases:

%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory

The options can be viewed by executing the following command:

%>mysqlhotcopy --help

sql

Documents