SELECT *
FROM `tab1`
JOIN `tab2`
ON LOWER(`tab1`.`name`) LIKE CONCAT('%', LOWER(`tab2`.`name`), '%');
select
class,
price
from (
select
class,
price,
row_number() over (partition by class order by price) rn
from complex
) prices
where
(class=1 and rn < 3) -- 2 econom
or (class=2 and rn < 4) -- 3 comfort
or (class=3 and rn < 2) -- 1 premium
;
SELECT * FROM `advert` WHERE `active` = '1' AND `type` = '1' ORDER BY `price`;
shell> mysqldump db_name > backup-file.sql
shell> mysql db_name < backup-file.sql
update test
set html = replace(
html,
substr(
html,
locate('<nav>', html, 1) + 5,
locate('</nav>', html, locate('<nav>', html, 1))-locate('<nav>', html, 1) - 5
), '');
select
locate('<h4 class="notify__title notify__title--small">', html, 1) as o,
locate('</h4>', html, locate('<h4 class="notify__title notify__title--small">', html, 1)) as c,
concat('<span>',
substr(
html,
locate('<h4 class="notify__title notify__title--small">', html, 1) + 47,
locate('</h4>', html, locate('<h4 class="notify__title notify__title--small">', html, 1))-locate('<h4 class="notify__title notify__title--small">', html, 1) - 47
), '</span>') as new_html
from test;
$stmt = $mysqli->prepare('INSERT INTO viber_messages (`datetimemsg`, `id_viber`, `message`) VALUES (?, ?, ?);');
$stmt->bind_param('sss', $datetimemsg, $sender_id, $message);
$stmt->execute();
SELECT
book.NAME,
SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)) AS CITY1_QUANTITY,
SUM(IF(store.CITY = 'London', QUANTITY, 0)) AS CITY2_QUANTITY,
SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)- IF(store.CITY = 'London', QUANTITY, 0)) AS DIFF_QUANTITY
FROM book
LEFT JOIN book_store ON book_store.BOOK_ID = book.ID
JOIN store ON store.ID = book_store.STORE_ID
WHERE store.CITY IN ('Moscow', 'London')
GROUP BY book.NAME;
UPDATE first_table
JOIN second_table ON first_table.id = second_table.id
SET first_table.count = first_table.count + second_table.total_count, second_table.total_count = 0
WHERE second_table.id = 1;
SELECT
`users`.`name`,
COUNT(`orders`.`id`) as counts,
COALESCE(SUM(`orders`.`amount`), 0) as sums
FROM
`users`
LEFT JOIN `orders` ON
`users`.`id` = `orders`.`user_id` AND
`orders`.`date` BETWEEN '2021-03-29' AND '2021-03-30'
GROUP BY
`users`.`id`, `users`.`name`;
CREATE TABLE Cities (
id int primary key auto_increment,
city varchar(64)
);
CREATE TABLE CityDistnce (
city1 int,
city2 int,
distance int,
foreign key (city1) references Cities(id),
foreign key (city2) references Cities(id),
index (city1, city2)
);
select
m.match_id,
min(opponent_id) as op1, max(opponent_id) as op2
from match_opponents op
inner join matches m on op.match_id = m.match_id
where m.status = "started"
group by m.match_id;
<?php
function split_name($name) {
return mb_ereg_replace_callback(
'([а-яa-z])([А-ЯA-Z])',
function($m) {
return $m[1] . ' ' . $m[2];
},
$name
);
}
echo split_name('Семенова ИринаВикторовна') . PHP_EOL;
echo split_name('Иванова ОльгаВикторовна') . PHP_EOL;
echo split_name('InessaIvanovna Oliynichenko') . PHP_EOL;
$result = mysqli_query($link, "SELECT acc_get_current_balance('@account_id') AS current_balance");
while ($row = mysqli_fetch_assoc($result)) {
echo "Current Balance : {$row['current_balance']} <br>";
}