احتمالا اگر بخواهید از یک روش ساده برای دریافت ۱۰۰ رکورد اول استفاده کنید دستور زیر را وارد میکنید
select sum(amount) from orders limit 100
اما متاسفانه این دستور به درستی عمل نمیکند و ستون amount را در کل رکورد ها جمع میزند . راه حل این است که از sub query استفاده شود. به طور مثال
select sum(tmp.amount) from (
select * from orders limit 100
) as tmp
برای گرفت export از دیتابیس دستور زیر را اجرا نمایید
docker exec docker-test-db-1 /usr/bin/mysqldump -u root --password=secret laravel --no-tablespaces > dump000916-3.sql
docker-test-db-1 همان container name است که با دستور docker ps بدست می آید
laravel نام دیتابیسی است که باید export گرفته شود
dump000916-3.sql نام فایلی است که export گرفته میشود
جهت ورود اطلاعات به دیتابیس import از دستور زیر
docker exec -i docker-test-db-1 /usr/bin/mysql -u root --password=secret laravel --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dump000916-3.sql
docker-test-db-1 همان container name است که با دستور docker ps بدست می آید
laravel نام دیتابیسی است که باید import گرفته شود
dump000916-3.sql نام فایلی است که import میشود
command=”SET SESSION FOREIGN_KEY_CHECKS=0;” کاری میکند که اگر در هنگام ورود خطای foregen key check error بود صرف نظر کند
سلام گاهی اوقات لازم است که query sql مشابه زیر داشته باشیم
select * from (select * from `shop_stores` where `shop_stores`.`deleted_at` is null) as `shop_stores` where `shop_stores`.`deleted_at` is null;
برای این منظور میتوانیم از متد subFrom در query builder لاراول استفاده کنیم.
$firstQuery = stores::query();
$secondQuery = stores::query();
$secondQuery->fromSub($firstQuery,'shop_stores',function ($join){
$join->on('shop_stores.id','=','latest_posts.id');
});
$secondQuery->dd();
هنگامی که دو جدول دارید و میخواهید بعد از ایجاد join میان آن دو از جدول اول یک مقدار را sum کنید می بینید که عدد اشتباه است این مشکل زمانی دیده میشود که جدول دوم دارای بیش از یک رکورد معادل رکورد در جدول اول باشد . برای حل این مشکل با تکه کد زیر میتوان دو جدول را جمع زد
SELECT COUNT(DISTINCT invo.id), SUM(invo.am) FROM invo JOIN (SELECT invo_id FROM invo_tm GROUP BY invo_id) AS xx ON (xx.invo_id = invo.id);
رای ورود اطلاعات به mysql میتوان با روش csv اقدام کرد برای این کار فایل اکسل خود را در google sheet باز کنید و بعد اتمام کار از ان خروحی csv comma seperated بگیرد سپس با ورود به جدول مورد نظر در mysql روی دکمه import کلیک کنید و در پایین
در بخش Column names اگر طبق فایل csv ترتیب همه ستون ها برابر نبود میتوان مطابق ترتیبی که در فایل csv است اینجا وارد کنید و حتما با comma آن را جدا کنید . توجه کنید که در فایل csv نباید ردیف اول شامل تیتر ستون باشد و باید اولین رکورد دیتا قرار بگیرد.
SELECT * FROM users WHERE user_id = 2 AND created_at = (SELECT max(created_at) FROM users where user_id = 2)
روش دوم با استفاده از join
select t2.* from mlm_reagent_history as t1 INNER JOIN (SELECT max(created_at) lastDate FROM mlm_reagent_history where user_id = 2) as t2 on (t1.created_at = t2.lastDate and t1.user_id = 2 )
روش سوم
SELECT * FROM user_log where user_id = 1 order by date_at DESC limit 1
برای اینکه سرعت query ها بالاتر برود لازم است که هر آنچه که قرار است در
where – order by – group by – join استفاده شود را کلید قرار دهید . اما یک نکته در مورد کلید ها وجود دارد اگر کلید از نوع primary باشد امکان تکرار آن در یک جدول نیست اما میتوان کلید را از نوع index قرار داد با دستور
ALTER TABLE invoice ADD INDEX (user_id)
دستور فوق در جدول invoice ستون user_id را به کلید index تبدیل میکند
به طور مثال یک جدول مانند زیر می باشد
Positions Table
╔══════════╦═════════════════╗
║ id ║ name ║
╠══════════╬═════════════════╣
║ 1 ║ Executive ║
║ 2 ║ Corp Admin ║
║ 3 ║ Sales ║
║ 4 ║ Art ║
║ 5 ║ Marketing ║
╚══════════╩═════════════════╝
و در جدول دوم به صورت زیر اطلاعات ذخیره شده است
Notes Table
╔══════════╦═════════════════╗
║ nid ║ forDepts ║
╠══════════╬═════════════════╣
║ 1 ║ 1,2,4 ║
║ 2 ║ 4,5 ║
╚══════════╩═════════════════╝
میخواهیم خروجی مانند زیر داشته باشیم
╠══════════╬════════════════════════════╣
║ 1 ║ Executive, Corp Admin, Art ║
║ 2 ║ Art, Marketing ║
╚══════════╩════════════════════════════╝
جهت انجام این کار مطابق قطع کد زیر میتوان join بین دو جدول بدست آورد
SELECT a.nid,
GROUP_CONCAT(DISTINCT b.name ORDER BY b.id) DepartmentName
FROM Notes a
INNER JOIN Positions b
ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid
منبع https://stackoverflow.com/a/19101646/4406856
دو کد زیر خروجی یکسانی دارند
SELECT * FROM accounting_accounts a cross join accounting_accounts
SELECT * FROM accounting_accounts a, accounting_accounts b;
جهت دریافت اطلاعات یک مقدار json باید از روش زیر استفاده کرد
SELECT detail->"$.mobile" as mobile FROM users
دستور فوق در جدول users ها شماره موبایل در ستون detail نمایش بدست می آورد
در صورتی که می خواهید مقدار بدون کتیشن بدست آید باید به ترتیب زیر عمل کنید
SELECT detail->>"$.mobile" as mobile FROM users
به روزرسانی یک فیلد خاص در json
مثلا میخواهید که یک فیلد خاص را به json اضافه کنید یا اگر موجود است آن را به روز کنید از دستور زیر استفاده شود
UPDATE users SET detail = JSON_SET(detail,"$.gender", "1");
Update nodes as C inner join ( select sum(amount) as total , user_id from bimeh_commission group by user_id ) as A on C.user_id = A.user_id set C.total_amount = A.total
CREATE TABLE `order` (CREATE TABLE `order` (`id` int(11) NOT NULL, `price` int(11) NOT NULL, `datee` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `order` (`id`, `price` , `datee`) VALUES(3,200 ,'2017-10-10'),(4,200,'2017-10-15'), (5,100,'2017-10-10'), (6,600,'2017-10-10');
SELECT a.* FROM `order` a INNER JOIN ( SELECT MAX( price) AS maxx FROM `order` where datee = CURDATE() ) b ON (b.maxx = a.price and a.datee = CURDATE())
دربرنامه فوق ابتدا در سsubquery رکوردهای امروز را درنظر میگیرد و بعد بیشترین رکورد را انتخاب میکند. سپس عدد انتخاب شده حاصل بیشترین عدد امروز است . فرض کنید در کنار جدول order ستون userid را هم داریم و میخواهیم بدانیم عدد بدست آمده در امروز برای کیست . (b.maxx = a.price and a.datee = CURDATE()) با این قسمت همه رکورد های امروز در جدول بخش from را در نظر میگیرم که عدد price آن برابر بیشترین عدد بدست امده باشد آنگاه میتوان با تساوری قراردادن آن بیشترین را بدست آورد