join جدول با یک فیلد با مقدار جداشده با کاما

به طور مثال یک جدول مانند زیر می باشد

Positions Table

╔══════════╦═════════════════╗
║ id       ║    name         ║
╠══════════╬═════════════════╣
║ ۱        ║ Executive       ║
║ ۲        ║ Corp Admin      ║
║ ۳        ║ Sales           ║
║ ۴        ║ Art             ║
║ ۵        ║ Marketing       ║
╚══════════╩═════════════════╝

و در جدول دوم به صورت زیر اطلاعات ذخیره شده است

Notes Table

╔══════════╦═════════════════╗
║ nid      ║    forDepts     ║
╠══════════╬═════════════════╣
║ ۱        ║ ۱,۲,۴           ║
║ ۲        ║ ۴,۵             ║
╚══════════╩═════════════════╝

میخواهیم خروجی مانند زیر داشته باشیم

    ╠══════════╬════════════════════════════╣
    ║ ۱        ║ Executive, Corp Admin, Art ║
    ║ ۲        ║ 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

کار با json در mysql 5.7

جهت دریافت اطلاعات یک مقدار 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");

انتخاب بیشترین عدد امروز Mysql


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'),
(۵,۱۰۰,'۲۰۱۷-۱۰-۱۰'),
(۶,۶۰۰,'۲۰۱۷-۱۰-۱۰');

 


 


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  آن برابر بیشترین عدد بدست امده باشد آنگاه میتوان با تساوری قراردادن آن بیشترین را بدست آورد

 

 

http://sqlfiddle.com/#!9/8a97e/12

ایجاد Foregin key بین دو جدول در mysql

کلید های خارجی کمک زیادی به مدیریت داده در دیتابیس میکند یکی از کاربردهای ان حذف رکوردهای کلید شده است/

فرض کنید جدول با نام table1 داریم و یک ستون با نام key_cloumn دارد. میخوایم به جدول table2 و ستون id  لینک ایجاد کنیم به صورتی که با حذف id در table 2 رکورد های مرتبط با tabel1 هم حدف شود

ALTER TABLE `table1`ADD KEY `key_cloumn` (`key_cloumn`);

ALTER TABLE `table1`  ADD CONSTRAINT `name_foreign_key` FOREIGN KEY (`table1_key_cloumn`) REFERENCES `table2` (`id`) ON DELETE CASCADE;

در برنامه فوق ابتدا ستون key_cloumn

ساختار LEFT JOIN در MYSQL

Join یکی از دستوارت مهم در mysql است با استفاده از این دستور میتوان موارد مغاییر و نامغاییر دو جدول را بررسی کرد .

	--table1 data
	۱
	۲
	۳
	۴
  
	--table2 data
	۵
	۳
	۲
	۶
  

با توجه به جداول فوق دستور زیر

select  * FROM table1 t1
  LEFT JOIN table2 t2 	 ON   t1.id = t2.id
   

نتیجه برابر خواهد شد

 table1 | table2
 ----------------
 ۱		|	۲
 ۲		|	۳
 ۳ 		|	
 ۴ 		|	
  

هنگامی که شما select from را یک جدول تعیین میکنید معیار انتخاب آن میشود یعنی در ستور فوق مواردی که در table1 هستند انتخاب میشوند به اضافه موارد موجود در table2 ای که در table1 وجود دارد/

 

جمع زدن یک ستون با مقدار خاص در mysql

سلام . امروز میخوام در مورد یکی از دستورهای mysql صحبت کنم این دستور مثل دستور ساده IF رفتار میکنه با این شرایط که اگر ستون مورد نظر برابر مقدار خاصی شد سپس بتواند جمع آن مقدار خاص را بدست بیاورد

			SUM(IF(t7.code = 'pay', t4.amount, 0)) AS 'pay',
			SUM(IF(t7.code = 'rec', t4.amount, 0)) AS 'rec' 
			
			SUM(CASE WHEN t7.code = 'pay' THEN t4.amount ELSE -t4.amount END) AS amount33

ایجاد کلید بین دو جدول موجود در mysql

با دستور زیر میتوان بین دو جدول کلید خارجی تعریف کرد با این حالت که اگر منبع پاک شد مقادیر جدول NULL شود در صورت آپدیت Update شود

  ALTER TABLE user ADD CONSTRAINT bank_id_relation FOREIGN KEY (bank_id) REFERENCES accounting_banks (id)
  ON DELETE SET NULL ON UPDATE CASCADE;