SQL/MySQL/String/Regular Expression — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 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;