SQL/MySQL/String/Regular Expression — различия между версиями

Текущая версия на 10:16, 26 мая 2010

Содержание

1 Patter match: string ends with
2 Pattern in Where clause
3 Pattern match: contain substring
4 Pattern Matching
5 Pattern Matching: beginning with "b"
6 Pattern match: letter repeats
7 Pattern match: string begin and end
8 Pattern match: string begin with a certain letter
9 Pattern match: string contains
10 Pattern match: string has 5 characters
11 String begins with
12 String case in regular expression
13 String contains
14 String ends with
15 String with exact length
16 Using Pattern Matching
17 Where clause: like and %
18 Where clause: regular expression 2
19 Where clause: regular expressions

Patter match: string ends with

  
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /* find names ending with "fy", use "$" to match the end of the name: */
mysql> SELECT * FROM Bird WHERE name REGEXP "fy$";
Empty set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/* find names ending with "fy", use "$" to match the end of the name: */
SELECT * FROM Bird WHERE name REGEXP "fy$";

Pattern in Where clause

/* Prepare the data */ 
DROP TABLE Student;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

/* Insert data for testing */ 
INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
  
/* Real command */
SELECT Name FROM Student
WHERE Name REGEXP "^[J].*r$";

Pattern match: contain substring

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
       
SELECT firstname, lastName, title from employee
where title NOT LIKE "%programmer%";

Pattern Matching

/*
mysql> SELECT ExamID,SustainedOn,Comments FROM Exam
    -> WHERE Comments LIKE "%Java%";
+--------+-------------+-----------------+
| ExamID | SustainedOn | Comments        |
+--------+-------------+-----------------+
|      1 | 2003-03-12  | Java Test       |
|      3 | 2005-03-11  | JavaScript Test |
+--------+-------------+-----------------+
2 rows in set (0.00 sec)

*/
/* Prepare the data */ 
DROP TABLE Exam;
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   SustainedOn DATE,
   Comments    VARCHAR(255)
)TYPE = InnoDB;

/* Insert data for testing */ 
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (1,"2003-03-12","Java Test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (2,"2004-03-13","C# test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (3,"2005-03-11","JavaScript Test");
  
/* Real command */
SELECT ExamID,SustainedOn,Comments FROM Exam 
WHERE Comments LIKE "%Java%";

Pattern Matching: beginning with "b"

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Bird WHERE name LIKE "B%";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

*/  
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
SELECT * FROM Bird WHERE name LIKE "B%";

Pattern match: letter repeats

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /* "{n}" "repeat-n-times" operator: */
mysql> SELECT * FROM Bird WHERE name REGEXP "^.{5}$";
Empty set (0.00 sec)

*/  
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/* "{n}" "repeat-n-times" operator: */
SELECT * FROM Bird WHERE name REGEXP "^.{5}$";

Pattern match: string begin and end

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
SELECT lastName from employee where
lastName like "S%" OR lastName like "A%";

Pattern match: string begin with a certain letter

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /* This query matches only lowercase "b" at the beginning of a name: */
mysql> SELECT * FROM Bird WHERE name REGEXP BINARY "^b";
Empty set (0.05 sec)

*/  
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;

/* This query matches only lowercase "b" at the beginning of a name: */
SELECT * FROM Bird WHERE name REGEXP BINARY "^b";

Pattern match: string contains

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /*
mysql> To find names containing a "w", use this query: */
mysql> SELECT * FROM Bird WHERE name REGEXP "w";
Empty set (0.00 sec)

*/  
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/*
To find names containing a "w", use this query: */
SELECT * FROM Bird WHERE name REGEXP "w";

Pattern match: string has 5 characters

  
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /* To find names containing exactly five characters, use "^" and "$" to m
atch the beginning and end of the name, and five instances of "." in between:
mysql> */
mysql> SELECT * FROM Bird WHERE name REGEXP "^.....$";
Empty set (0.00 sec)

*/
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/* To find names containing exactly five characters, use "^" and "$" to match the beginning and end of the name, and five instances of "." in between:
*/
SELECT * FROM Bird WHERE name REGEXP "^.....$";

String begins with

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /*names beginning with "b", use "^" to match the beginning of the name:*/
mysql> SELECT * FROM Bird WHERE name REGEXP "^B";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.02 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/*names beginning with "b", use "^" to match the beginning of the name:*/
SELECT * FROM Bird WHERE name REGEXP "^B";

String case in regular expression

  
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /*
mysql>  Prior to MySQL Version 3.23.4, REGEXP is case sensitive,
mysql>  match either lowercase or uppercase "b", use this query instead:
mysql> */
mysql> SELECT * FROM Bird WHERE name REGEXP "^[bB]";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/*
 Prior to MySQL Version 3.23.4, REGEXP is case sensitive, 
 match either lowercase or uppercase "b", use this query instead:
*/
SELECT * FROM Bird WHERE name REGEXP "^[bB]";

String contains

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /*names containing a "u"*/
mysql> SELECT * FROM Bird WHERE name LIKE "%u%";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.01 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/*names containing a "u"*/
SELECT * FROM Bird WHERE name LIKE "%u%";

String ends with

/*mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> /* names ending with "fy" */
mysql> SELECT * FROM Bird WHERE owner LIKE "%us";
Empty set (0.00 sec)
*/
  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/* names ending with "fy" */
SELECT * FROM Bird WHERE owner LIKE "%us";

String with exact length

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Bird WHERE name LIKE "_______";
+---------+-------+---------+------+------------+------------+
| name    | owner | species | sex  | birth      | death      |
+---------+-------+---------+------+------------+------------+
| RedBird | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+---------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
/*
names containing exactly five characters, 
use five instances of the "_" pattern character:
*/
SELECT * FROM Bird WHERE name LIKE "_______";

Using Pattern Matching

/*
mysql> SELECT Name FROM Student WHERE Name LIKE "__ %";
+-------------+
| Name        |
+-------------+
| JJ Harvests |
+-------------+
1 row in set (0.00 sec)

*/
/* Prepare the data */ 
DROP TABLE Exam;
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   SustainedOn DATE,
   Comments    VARCHAR(255)
)TYPE = InnoDB;

/* Insert data for testing */ 
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (1,"2003-03-12","Java Test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (2,"2004-03-13","C# test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (3,"2005-03-11","JavaScript Test");
  
/* Real command */
SELECT Name FROM Student WHERE Name LIKE "__ %";

Where clause: like and %

/*
mysql> Drop table Sale;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Sale
    -> (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder SMALLINT UNSIGNED NOT NULL,
    ->    Reserved SMALLINT UNSIGNED NOT NULL,
    ->    Department ENUM("Classical", "Popular") NOT NULL,
    ->    Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Cate
gory)
    -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro
ck"),
    ->                 ("Opera",     12,      5,       2,        "Classical","Op
era"),
    ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja
zz"),
    ->                 ("Music",     4,       3,       4,        "Classical","Da
nce"),
    ->                 ("Violin",    25,      2,       5,        "Classical", NU
LL),
    ->                 ("Toscana",   16,      1,       6,        "Classical", NU
LL),
    ->                 ("Blues",     7,       22,      7,        "Popular",   "B
lues"),
    ->                 ("Pure",      38,      5,       11,       "Popular",   NU
LL),
    ->                 ("Mud",       19,      11,      12,       "Popular",  "Co
untry"),
    ->                 ("Essence",   5,       23,      12,       "Popular", "New
 Age"),
    ->                 ("Embrace",   21,      12,      14,       "Popular", "New
 Age"),
    ->                 ("Satie",     42,      17,      15,       "Classical", NU
LL),
    ->                 ("Lake",      23,      47,      28,       "Classical", "D
ance"),
    ->                 ("Favorites", 34,      15,      12,       "Classical", "G
eneral"),
    ->                 ("Boheme",    25,      12,       5,       "Classical", "O
pera"),
    ->                 ("Cantatas",  26,      13,      8,        "Classical", "G
eneral"),
    ->                 ("Road",      27,      13,      17,       "Popular", "Cou
ntry"),
    ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz
z"),
    ->                 ("Woman",     29,      4,        7,       "Popular", "Blu
es"),
    ->                 ("Bach",      21,      13,      16,       "Classical", "G
eneral"),
    ->                 ("Opera",     12,      32,      12,       "Classical", "O
pera"),
    ->                 ("Soul",      13,      30,      14,       "Popular", NULL
),
    ->                 ("Stages",    44,      0,       8,        "Popular", "Blu
es"),
    ->                 ("Bach",      15,      1,       8,        "Classical", "G
eneral");
Query OK, 24 rows affected (0.01 sec)
Records: 24  Duplicates: 0  Warnings: 0
mysql> select * from Sale;
+----+-----------+---------+---------+----------+------------+----------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category |
+----+-----------+---------+---------+----------+------------+----------+
|  1 | Bloodshot |      11 |       6 |        1 | Popular    | Rock     |
|  2 | Opera     |      12 |       5 |        2 | Classical  | Opera    |
|  3 | Jazz      |      13 |       4 |        3 | Popular    | Jazz     |
|  4 | Music     |       4 |       3 |        4 | Classical  | Dance    |
|  5 | Violin    |      25 |       2 |        5 | Classical  | NULL     |
|  6 | Toscana   |      16 |       1 |        6 | Classical  | NULL     |
|  7 | Blues     |       7 |      22 |        7 | Popular    | Blues    |
|  8 | Pure      |      38 |       5 |       11 | Popular    | NULL     |
|  9 | Mud       |      19 |      11 |       12 | Popular    | Country  |
| 10 | Essence   |       5 |      23 |       12 | Popular    | New Age  |
| 11 | Embrace   |      21 |      12 |       14 | Popular    | New Age  |
| 12 | Satie     |      42 |      17 |       15 | Classical  | NULL     |
| 13 | Lake      |      23 |      47 |       28 | Classical  | Dance    |
| 14 | Favorites |      34 |      15 |       12 | Classical  | General  |
| 15 | Boheme    |      25 |      12 |        5 | Classical  | Opera    |
| 16 | Cantatas  |      26 |      13 |        8 | Classical  | General  |
| 17 | Road      |      27 |      13 |       17 | Popular    | Country  |
| 18 | Paris     |      18 |      25 |       10 | Popular    | Jazz     |
| 19 | Woman     |      29 |       4 |        7 | Popular    | Blues    |
| 20 | Bach      |      21 |      13 |       16 | Classical  | General  |
| 21 | Opera     |      12 |      32 |       12 | Classical  | Opera    |
| 22 | Soul      |      13 |      30 |       14 | Popular    | NULL     |
| 23 | Stages    |      44 |       0 |        8 | Popular    | Blues    |
| 24 | Bach      |      15 |       1 |        8 | Classical  | General  |
+----+-----------+---------+---------+----------+------------+----------+
24 rows in set (0.01 sec)
mysql> SELECT Name, InStock+OnOrder-Reserved AS Available
    -> FROM Sale
    -> WHERE Name LIKE "%bach%"
    -> ORDER BY Name;
+------+-----------+
| Name | Available |
+------+-----------+
| Bach |        18 |
| Bach |         8 |
+------+-----------+
2 rows in set (0.03 sec)

*/
Drop table Sale;

CREATE TABLE Sale
(
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM("Classical", "Popular") NOT NULL,
   Category VARCHAR(20)
);

INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Category)
VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Rock"),
                ("Opera",     12,      5,       2,        "Classical","Opera"),
                ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
                ("Music",     4,       3,       4,        "Classical","Dance"),
                ("Violin",    25,      2,       5,        "Classical", NULL),
                ("Toscana",   16,      1,       6,        "Classical", NULL),
                ("Blues",     7,       22,      7,        "Popular",   "Blues"),
                ("Pure",      38,      5,       11,       "Popular",   NULL),
                ("Mud",       19,      11,      12,       "Popular",  "Country"),
                ("Essence",   5,       23,      12,       "Popular", "New Age"),
                ("Embrace",   21,      12,      14,       "Popular", "New Age"),
                ("Satie",     42,      17,      15,       "Classical", NULL),
                ("Lake",      23,      47,      28,       "Classical", "Dance"),
                ("Favorites", 34,      15,      12,       "Classical", "General"),
                ("Boheme",    25,      12,       5,       "Classical", "Opera"),
                ("Cantatas",  26,      13,      8,        "Classical", "General"),
                ("Road",      27,      13,      17,       "Popular", "Country"),
                ("Paris",     18,      25,      10,       "Popular", "Jazz"),
                ("Woman",     29,      4,        7,       "Popular", "Blues"),
                ("Bach",      21,      13,      16,       "Classical", "General"),
                ("Opera",     12,      32,      12,       "Classical", "Opera"),
                ("Soul",      13,      30,      14,       "Popular", NULL),
                ("Stages",    44,      0,       8,        "Popular", "Blues"),
                ("Bach",      15,      1,       8,        "Classical", "General");
  
select * from Sale;
  
SELECT Name, InStock+OnOrder-Reserved AS Available
FROM Sale
WHERE Name LIKE "%bach%"
ORDER BY Name;

Where clause: regular expression 2

/*
mysql> Drop table Sale;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Sale
    -> (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder SMALLINT UNSIGNED NOT NULL,
    ->    Reserved SMALLINT UNSIGNED NOT NULL,
    ->    Department ENUM("Classical", "Popular") NOT NULL,
    ->    Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Cate
gory)
    -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro
ck"),
    ->                 ("Opera",     12,      5,       2,        "Classical","Op
era"),
    ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja
zz"),
    ->                 ("Music",     4,       3,       4,        "Classical","Da
nce"),
    ->                 ("Violin",    25,      2,       5,        "Classical", NU
LL),
    ->                 ("Toscana",   16,      1,       6,        "Classical", NU
LL),
    ->                 ("Blues",     7,       22,      7,        "Popular",   "B
lues"),
    ->                 ("Pure",      38,      5,       11,       "Popular",   NU
LL),
    ->                 ("Mud",       19,      11,      12,       "Popular",  "Co
untry"),
    ->                 ("Essence",   5,       23,      12,       "Popular", "New
 Age"),
    ->                 ("Embrace",   21,      12,      14,       "Popular", "New
 Age"),
    ->                 ("Satie",     42,      17,      15,       "Classical", NU
LL),
    ->                 ("Lake",      23,      47,      28,       "Classical", "D
ance"),
    ->                 ("Favorites", 34,      15,      12,       "Classical", "G
eneral"),
    ->                 ("Boheme",    25,      12,       5,       "Classical", "O
pera"),
    ->                 ("Cantatas",  26,      13,      8,        "Classical", "G
eneral"),
    ->                 ("Road",      27,      13,      17,       "Popular", "Cou
ntry"),
    ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz
z"),
    ->                 ("Woman",     29,      4,        7,       "Popular", "Blu
es"),
    ->                 ("Bach",      21,      13,      16,       "Classical", "G
eneral"),
    ->                 ("Opera",     12,      32,      12,       "Classical", "O
pera"),
    ->                 ("Soul",      13,      30,      14,       "Popular", NULL
),
    ->                 ("Stages",    44,      0,       8,        "Popular", "Blu
es"),
    ->                 ("Bach",      15,      1,       8,        "Classical", "G
eneral");
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0
mysql> select * from Sale;
+----+-----------+---------+---------+----------+------------+----------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category |
+----+-----------+---------+---------+----------+------------+----------+
|  1 | Bloodshot |      11 |       6 |        1 | Popular    | Rock     |
|  2 | Opera     |      12 |       5 |        2 | Classical  | Opera    |
|  3 | Jazz      |      13 |       4 |        3 | Popular    | Jazz     |
|  4 | Music     |       4 |       3 |        4 | Classical  | Dance    |
|  5 | Violin    |      25 |       2 |        5 | Classical  | NULL     |
|  6 | Toscana   |      16 |       1 |        6 | Classical  | NULL     |
|  7 | Blues     |       7 |      22 |        7 | Popular    | Blues    |
|  8 | Pure      |      38 |       5 |       11 | Popular    | NULL     |
|  9 | Mud       |      19 |      11 |       12 | Popular    | Country  |
| 10 | Essence   |       5 |      23 |       12 | Popular    | New Age  |
| 11 | Embrace   |      21 |      12 |       14 | Popular    | New Age  |
| 12 | Satie     |      42 |      17 |       15 | Classical  | NULL     |
| 13 | Lake      |      23 |      47 |       28 | Classical  | Dance    |
| 14 | Favorites |      34 |      15 |       12 | Classical  | General  |
| 15 | Boheme    |      25 |      12 |        5 | Classical  | Opera    |
| 16 | Cantatas  |      26 |      13 |        8 | Classical  | General  |
| 17 | Road      |      27 |      13 |       17 | Popular    | Country  |
| 18 | Paris     |      18 |      25 |       10 | Popular    | Jazz     |
| 19 | Woman     |      29 |       4 |        7 | Popular    | Blues    |
| 20 | Bach      |      21 |      13 |       16 | Classical  | General  |
| 21 | Opera     |      12 |      32 |       12 | Classical  | Opera    |
| 22 | Soul      |      13 |      30 |       14 | Popular    | NULL     |
| 23 | Stages    |      44 |       0 |        8 | Popular    | Blues    |
| 24 | Bach      |      15 |       1 |        8 | Classical  | General  |
+----+-----------+---------+---------+----------+------------+----------+
24 rows in set (0.00 sec)
mysql> SELECT Name, InStock
    -> FROM Sale
    -> WHERE Name REGEXP "^[mn].*[sz]$"
    -> ORDER BY Name;
Empty set (0.00 sec)

*/
Drop table Sale;

CREATE TABLE Sale
(
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM("Classical", "Popular") NOT NULL,
   Category VARCHAR(20)
);

INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Category)
VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Rock"),
                ("Opera",     12,      5,       2,        "Classical","Opera"),
                ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
                ("Music",     4,       3,       4,        "Classical","Dance"),
                ("Violin",    25,      2,       5,        "Classical", NULL),
                ("Toscana",   16,      1,       6,        "Classical", NULL),
                ("Blues",     7,       22,      7,        "Popular",   "Blues"),
                ("Pure",      38,      5,       11,       "Popular",   NULL),
                ("Mud",       19,      11,      12,       "Popular",  "Country"),
                ("Essence",   5,       23,      12,       "Popular", "New Age"),
                ("Embrace",   21,      12,      14,       "Popular", "New Age"),
                ("Satie",     42,      17,      15,       "Classical", NULL),
                ("Lake",      23,      47,      28,       "Classical", "Dance"),
                ("Favorites", 34,      15,      12,       "Classical", "General"),
                ("Boheme",    25,      12,       5,       "Classical", "Opera"),
                ("Cantatas",  26,      13,      8,        "Classical", "General"),
                ("Road",      27,      13,      17,       "Popular", "Country"),
                ("Paris",     18,      25,      10,       "Popular", "Jazz"),
                ("Woman",     29,      4,        7,       "Popular", "Blues"),
                ("Bach",      21,      13,      16,       "Classical", "General"),
                ("Opera",     12,      32,      12,       "Classical", "Opera"),
                ("Soul",      13,      30,      14,       "Popular", NULL),
                ("Stages",    44,      0,       8,        "Popular", "Blues"),
                ("Bach",      15,      1,       8,        "Classical", "General");
  
select * from Sale;
SELECT Name, InStock
FROM Sale
WHERE Name REGEXP "^[mn].*[sz]$"
ORDER BY Name;

Where clause: regular expressions

/*
mysql> Drop table Sale;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Sale
    -> (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder SMALLINT UNSIGNED NOT NULL,
    ->    Reserved SMALLINT UNSIGNED NOT NULL,
    ->    Department ENUM("Classical", "Popular") NOT NULL,
    ->    Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Cate
gory)
    -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro
ck"),
    ->                 ("Opera",     12,      5,       2,        "Classical","Op
era"),
    ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja
zz"),
    ->                 ("Music",     4,       3,       4,        "Classical","Da
nce"),
    ->                 ("Violin",    25,      2,       5,        "Classical", NU
LL),
    ->                 ("Toscana",   16,      1,       6,        "Classical", NU
LL),
    ->                 ("Blues",     7,       22,      7,        "Popular",   "B
lues"),
    ->                 ("Pure",      38,      5,       11,       "Popular",   NU
LL),
    ->                 ("Mud",       19,      11,      12,       "Popular",  "Co
untry"),
    ->                 ("Essence",   5,       23,      12,       "Popular", "New
 Age"),
    ->                 ("Embrace",   21,      12,      14,       "Popular", "New
 Age"),
    ->                 ("Satie",     42,      17,      15,       "Classical", NU
LL),
    ->                 ("Lake",      23,      47,      28,       "Classical", "D
ance"),
    ->                 ("Favorites", 34,      15,      12,       "Classical", "G
eneral"),
    ->                 ("Boheme",    25,      12,       5,       "Classical", "O
pera"),
    ->                 ("Cantatas",  26,      13,      8,        "Classical", "G
eneral"),
    ->                 ("Road",      27,      13,      17,       "Popular", "Cou
ntry"),
    ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz
z"),
    ->                 ("Woman",     29,      4,        7,       "Popular", "Blu
es"),
    ->                 ("Bach",      21,      13,      16,       "Classical", "G
eneral"),
    ->                 ("Opera",     12,      32,      12,       "Classical", "O
pera"),
    ->                 ("Soul",      13,      30,      14,       "Popular", NULL
),
    ->                 ("Stages",    44,      0,       8,        "Popular", "Blu
es"),
    ->                 ("Bach",      15,      1,       8,        "Classical", "G
eneral");
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0
mysql> select * from Sale;
+----+-----------+---------+---------+----------+------------+----------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category |
+----+-----------+---------+---------+----------+------------+----------+
|  1 | Bloodshot |      11 |       6 |        1 | Popular    | Rock     |
|  2 | Opera     |      12 |       5 |        2 | Classical  | Opera    |
|  3 | Jazz      |      13 |       4 |        3 | Popular    | Jazz     |
|  4 | Music     |       4 |       3 |        4 | Classical  | Dance    |
|  5 | Violin    |      25 |       2 |        5 | Classical  | NULL     |
|  6 | Toscana   |      16 |       1 |        6 | Classical  | NULL     |
|  7 | Blues     |       7 |      22 |        7 | Popular    | Blues    |
|  8 | Pure      |      38 |       5 |       11 | Popular    | NULL     |
|  9 | Mud       |      19 |      11 |       12 | Popular    | Country  |
| 10 | Essence   |       5 |      23 |       12 | Popular    | New Age  |
| 11 | Embrace   |      21 |      12 |       14 | Popular    | New Age  |
| 12 | Satie     |      42 |      17 |       15 | Classical  | NULL     |
| 13 | Lake      |      23 |      47 |       28 | Classical  | Dance    |
| 14 | Favorites |      34 |      15 |       12 | Classical  | General  |
| 15 | Boheme    |      25 |      12 |        5 | Classical  | Opera    |
| 16 | Cantatas  |      26 |      13 |        8 | Classical  | General  |
| 17 | Road      |      27 |      13 |       17 | Popular    | Country  |
| 18 | Paris     |      18 |      25 |       10 | Popular    | Jazz     |
| 19 | Woman     |      29 |       4 |        7 | Popular    | Blues    |
| 20 | Bach      |      21 |      13 |       16 | Classical  | General  |
| 21 | Opera     |      12 |      32 |       12 | Classical  | Opera    |
| 22 | Soul      |      13 |      30 |       14 | Popular    | NULL     |
| 23 | Stages    |      44 |       0 |        8 | Popular    | Blues    |
| 24 | Bach      |      15 |       1 |        8 | Classical  | General  |
+----+-----------+---------+---------+----------+------------+----------+
24 rows in set (0.00 sec)
mysql> SELECT Name, InStock+OnOrder-Reserved AS Available
    -> FROM Sale
    -> WHERE Name REGEXP "^[a-f]"
    -> ORDER BY Name;
+-----------+-----------+
| Name      | Available |
+-----------+-----------+
| Bach      |        18 |
| Bach      |         8 |
| Bloodshot |        16 |
| Blues     |        22 |
| Boheme    |        32 |
| Cantatas  |        31 |
| Embrace   |        19 |
| Essence   |        16 |
| Favorites |        37 |
+-----------+-----------+
9 rows in set (0.03 sec)

*/
Drop table Sale;

CREATE TABLE Sale
(
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM("Classical", "Popular") NOT NULL,
   Category VARCHAR(20)
);

INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Category)
VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Rock"),
                ("Opera",     12,      5,       2,        "Classical","Opera"),
                ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
                ("Music",     4,       3,       4,        "Classical","Dance"),
                ("Violin",    25,      2,       5,        "Classical", NULL),
                ("Toscana",   16,      1,       6,        "Classical", NULL),
                ("Blues",     7,       22,      7,        "Popular",   "Blues"),
                ("Pure",      38,      5,       11,       "Popular",   NULL),
                ("Mud",       19,      11,      12,       "Popular",  "Country"),
                ("Essence",   5,       23,      12,       "Popular", "New Age"),
                ("Embrace",   21,      12,      14,       "Popular", "New Age"),
                ("Satie",     42,      17,      15,       "Classical", NULL),
                ("Lake",      23,      47,      28,       "Classical", "Dance"),
                ("Favorites", 34,      15,      12,       "Classical", "General"),
                ("Boheme",    25,      12,       5,       "Classical", "Opera"),
                ("Cantatas",  26,      13,      8,        "Classical", "General"),
                ("Road",      27,      13,      17,       "Popular", "Country"),
                ("Paris",     18,      25,      10,       "Popular", "Jazz"),
                ("Woman",     29,      4,        7,       "Popular", "Blues"),
                ("Bach",      21,      13,      16,       "Classical", "General"),
                ("Opera",     12,      32,      12,       "Classical", "Opera"),
                ("Soul",      13,      30,      14,       "Popular", NULL),
                ("Stages",    44,      0,       8,        "Popular", "Blues"),
                ("Bach",      15,      1,       8,        "Classical", "General");
  
select * from Sale;
  
SELECT Name, InStock+OnOrder-Reserved AS Available
FROM Sale
WHERE Name REGEXP "^[a-f]"
ORDER BY Name;

SQL/MySQL/String/Regular Expression — различия между версиями

Текущая версия на 10:16, 26 мая 2010

Содержание

Patter match: string ends with

Pattern in Where clause

Pattern match: contain substring

Pattern Matching

Pattern Matching: beginning with "b"

Pattern match: letter repeats

Pattern match: string begin and end

Pattern match: string begin with a certain letter

Pattern match: string contains

Pattern match: string has 5 characters

String begins with

String case in regular expression

String contains

String ends with

String with exact length

Using Pattern Matching

Where clause: like and %

Where clause: regular expression 2

Where clause: regular expressions

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 10:16, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 10:16, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)