SQL/MySQL/Function/Replace
Содержание
Call concat and replace
/*
mysql> select * from sales;
+-----+------------+--------+--------+--------+------+------------+
| num | name | winter | spring | summer | fall | category |
+-----+------------+--------+--------+--------+------+------------+
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
| 2 | C | 970 | 770 | 531 | 486 | Profession |
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
| 4 | SQL | 782 | 357 | 168 | 250 | Profession |
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
| 8 | Python | 67 | 23 | 83 | 543 | Holiday |
| 9 | PHP | 673 | 48 | 625 | 52 | Profession |
+-----+------------+--------+--------+--------+------+------------+
9 rows in set (0.01 sec)
mysql> SELECT CONCAT((REPLACE(name, "_", " ")), " ", "Sale") AS "Sales Plan"
-> FROM sales WHERE category!="Profession";
+-----------------+
| Sales Plan |
+-----------------+
| Java Sale |
| JavaScript Sale |
| Oracle Sale |
| MySQL Sale |
| Cplus Sale |
| Python Sale |
+-----------------+
6 rows in set (0.00 sec)
*/
Drop table sales;
CREATE TABLE sales(
num MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(20),
winter INT,
spring INT,
summer INT,
fall INT,
category CHAR(13),
primary key(num)
)type=MyISAM;
insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday");
insert into sales value(2, "C",970,770,531,486,"Profession");
insert into sales value(3, "JavaScript",53,13,21,856,"Literary");
insert into sales value(4, "SQL",782,357,168,250,"Profession");
insert into sales value(5, "Oracle",589,795,367,284,"Holiday");
insert into sales value(6, "MySQL",953,582,336,489,"Literary");
insert into sales value(7, "Cplus",752,657,259,478,"Literary");
insert into sales value(8, "Python",67,23,83,543,"Holiday");
insert into sales value(9, "PHP",673,48,625,52,"Profession");
select * from sales;
SELECT CONCAT((REPLACE(name, "_", " ")), " ", "Sale") AS "Sales Plan"
FROM sales WHERE category!="Profession";
Call REPLACE function in select clause
/*
mysql> select * from sales;
+-----+------------+--------+--------+--------+------+------------+
| num | name | winter | spring | summer | fall | category |
+-----+------------+--------+--------+--------+------+------------+
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
| 2 | C | 970 | 770 | 531 | 486 | Profession |
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
| 4 | SQL | 782 | 357 | 168 | 250 | Profession |
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
| 8 | Python | 67 | 23 | 83 | 543 | Holiday |
| 9 | PHP | 673 | 48 | 625 | 52 | Profession |
+-----+------------+--------+--------+--------+------+------------+
9 rows in set (0.01 sec)
mysql> SELECT REPLACE(name, "_", " ") FROM sales;
+-------------------------+
| REPLACE(name, "_", " ") |
+-------------------------+
| Java |
| C |
| JavaScript |
| SQL |
| Oracle |
| MySQL |
| Cplus |
| Python |
| PHP |
+-------------------------+
9 rows in set (0.01 sec)
*/
Drop table sales;
CREATE TABLE sales(
num MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(20),
winter INT,
spring INT,
summer INT,
fall INT,
category CHAR(13),
primary key(num)
)type=MyISAM;
insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday");
insert into sales value(2, "C",970,770,531,486,"Profession");
insert into sales value(3, "JavaScript",53,13,21,856,"Literary");
insert into sales value(4, "SQL",782,357,168,250,"Profession");
insert into sales value(5, "Oracle",589,795,367,284,"Holiday");
insert into sales value(6, "MySQL",953,582,336,489,"Literary");
insert into sales value(7, "Cplus",752,657,259,478,"Literary");
insert into sales value(8, "Python",67,23,83,543,"Holiday");
insert into sales value(9, "PHP",673,48,625,52,"Profession");
select * from sales;
SELECT REPLACE(name, "_", " ") FROM sales;
Do calculation in select clause
/*
mysql> select * from sales;
+-----+------------+--------+--------+--------+------+------------+
| num | name | winter | spring | summer | fall | category |
+-----+------------+--------+--------+--------+------+------------+
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
| 2 | C | 970 | 770 | 531 | 486 | Profession |
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
| 4 | SQL | 782 | 357 | 168 | 250 | Profession |
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
| 8 | Python | 67 | 23 | 83 | 543 | Holiday |
| 9 | PHP | 673 | 48 | 625 | 52 | Profession |
+-----+------------+--------+--------+--------+------+------------+
9 rows in set (0.01 sec)
mysql> SELECT REPLACE(name, "_", " ") AS Outfit,
-> winter + spring + summer + fall AS "Yearly Sales",
-> category AS Category
-> FROM sales;
+------------+--------------+------------+
| Outfit | Yearly Sales | Category |
+------------+--------------+------------+
| Java | 1684 | Holiday |
| C | 2757 | Profession |
| JavaScript | 943 | Literary |
| SQL | 1557 | Profession |
| Oracle | 2035 | Holiday |
| MySQL | 2360 | Literary |
| Cplus | 2146 | Literary |
| Python | 716 | Holiday |
| PHP | 1398 | Profession |
+------------+--------------+------------+
9 rows in set (0.00 sec)
*/
Drop table sales;
CREATE TABLE sales(
num MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(20),
winter INT,
spring INT,
summer INT,
fall INT,
category CHAR(13),
primary key(num)
)type=MyISAM;
insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday");
insert into sales value(2, "C",970,770,531,486,"Profession");
insert into sales value(3, "JavaScript",53,13,21,856,"Literary");
insert into sales value(4, "SQL",782,357,168,250,"Profession");
insert into sales value(5, "Oracle",589,795,367,284,"Holiday");
insert into sales value(6, "MySQL",953,582,336,489,"Literary");
insert into sales value(7, "Cplus",752,657,259,478,"Literary");
insert into sales value(8, "Python",67,23,83,543,"Holiday");
insert into sales value(9, "PHP",673,48,625,52,"Profession");
select * from sales;
SELECT REPLACE(name, "_", " ") AS Outfit,
winter + spring + summer + fall AS "Yearly Sales",
category AS Category
FROM sales;
MySQL replace command
Drop table CDs;
CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
Category VARCHAR(20)
);
INSERT INTO CDs
VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"),
(103, "Class", 9, "Classical"),
(104, "Violin", 24, NULL),
(105, "Blues", 2, "Blues"),
(106, "Tires", 12, "Country"),
(107, "Essence", 5, "New Age"),
(108, "Magic", 42, "Classical"),
(109, "Name", 20, "Opera"),
(110, "Fire", 23, "Country"),
(111, "Live", 18, "Jazz"),
(112, "Blues", 22, "Blues"),
(113, "Stages", 42, "Blues");
CREATE TABLE CDs2a
(
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(5) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL
)
SELECT CDID, CDName, InStock
FROM CDs
WHERE Category="Blues" OR Category="Jazz";
REPLACE INTO CDs2
SELECT CDID, CDName, InStock
FROM CDs
WHERE Category="Country" OR Category="Rock";
select * from CDs2;