SQL Like And Check Constrain

SQL Like And Check Constrain are another two important command in MySQL database.

In this tutorial i will discuss about SQL Like And Check Constrain statement,their purpose , syntax  and suitable example.I hope this must helpful for you.

Purpose of SQL LIKE:

>> The LIKE operator is used to search for a specific pattern in a column or column data.

SQL LIKE Syntax:

Syntax:

SELECT  column_name  FROM table_name  WHERE column_name LIKE pattern

LIKE pattern can be used in three way:

1. For First letter search we will use: LIKE ‘Letter%’

2. For End letter search we will use: LIKE ‘%Letter’

3. For middle  letter/word search we will use: LIKE ‘%Letter or word%’

SQL LIKE Example:

The teacher info table:

TEACHER INFO

Now we want to select the FIRST_NAME that Start  with an “S” from the ” teacher info” table.

We use the following SELECT statement:

SELECT * FROM `teacher info` WHERE FIRST_NAME LIKE 'S%'

The result-set will look like this:

sql like

Now we want to select the FIRST_NAME that End   with an “N” from the ” teacher info” table.

We use the following SELECT statement:

SELECT * FROM `teacher info` WHERE FIRST_NAME LIKE '%L'

The result-set will look like this:

like

Now we want to select the  FIRST_NAME that contain   “VON” from the ” teacher info” table.

We use the following SELECT statement:

SELECT * FROM  `teacher info2` WHERE LAST_NAME LIKE  '%VON%'

The result-set would look like below:

sql like1

Purpose of Check Constrain:

1. CHECK constraint is used to limit the value range that can be recorded in a column of a table.

CHECK constraint declaration in MySQL:

CHECK constraint  declaration in MySQL at the time of table creation.

CREATE TABLE `Teacher`.`teacher info`

(

`TEACHER_ID` INT NOT NULL ,

`FIRST_NAME` VARCHAR( 20 ) NOT NULL ,

`LAST_NAME` VARCHAR( 20 ) NOT NULL ,

`SALARY` VARCHAR( 20 ) NOT NULL

CHECK (TEACHER_ID>0)

)

CHECK constraint declaration in SQL Server/ MS Access / Oracle :

Primary key declaration in SQL Server/ MS Access / Oracle at the time of table creation.

CREATE TABLE `Teacher`.`teacher info` (

`TEACHER_ID` INT NOT NULL CHECK (TEACHER_ID>0),

`FIRST_NAME` VARCHAR( 20 ) NOT NULL ,

`LAST_NAME` VARCHAR( 20 ) NOT NULL ,

`SALARY` VARCHAR( 20 ) NOT NULL

)

CHECK constraint declaration in MySQL after a Table is  created:

SQL syntax:  

Alter table tbl_name add  CHECK (tbl_clm condition)

Example:

Alter table ‘teacher info’ add CHECK (TEACHER_ID>0)

 Related Post or you may like:

  1. Introduction of Database & SQL

  2. Create Database & Table

  3. SQL Insert,Update & Delete

  4. SQL Select and Where

  5. SQL Primary,Unique & Foreign Key

  6. SQL Drop truncate and Alter

  7. SQL Order By& Group By

  8. Database Connection