mysql的常用語句

mysql的常用語句

select  a.*,b.name, b.mobile, b.city_id from card_manage_broker a JOIN card_broker b on  a.user_id=b.user_id where a.function=5 or a.function=6 order by modify_time desc


select a.user_id, left(a.modify_time,10), b.* from (SELECT * from card_manage_broker where function=5 or FUNCTION=6)a join card_broker b on a.user_id = b.user_id

SELECT id, user_id from card_manage_broker union all  select id, user_id from card_broker ORDER BY id asc

提升查詢效率： https://www.cnblogs.com/peke/p/8036172.html

聯合查詢： http://www.cppcns.com/shujuku/mysql/119728.html

聚合函式出現在where子句中是個偽命題,舉個簡單的例子,現在需要select出所有人中身高大於平均身高的員工資訊,如果sql語句寫成select * from 表名 where height > avg(height); 肯定是錯誤的,因為聚合函式的實現,是基於所有完整資料的基礎上,例如,求和,最大值,最小值,必須保證所有列的資料全部用到,但是,聚合函式如果出現在where子句中,它就要受到整個語句的限制,因為這條語句最終是需要對資料進行篩選的,也就是說整條sql語句產生的結果將會是所有資料的部分資料,而不是全部的完整資料,這與聚合函式的實現的前提—完整的所有資料 是相悖的,因此,聚合函式是不能出現在where子句中

聯合查詢： https://www.cnblogs.com/457248499-qq-com/p/7374364.html

多表聯合查詢、連線查詢、子查詢： https://www.cnblogs.com/peke/p/8036172.html

提高查詢效率:

1)資料庫設計方面

• 對查詢進行優化,應儘量避免全表掃描,首先應考慮在where及order by涉及的列上建立索引
• 應儘量避免在where子句中國對欄位進行null值判斷,否則將導致引擎放棄使用索引而進行全表掃描.如:select id from t where num is null 可以在num上設定預設值0.確保num列沒有null值,然後這樣查詢:select id from t where num=0
• 索引並不是越多越好,索引可以提高相應的select的效率,但同時也降低了insert及update的效率,因為insert或update時有可能會重建索引,索引怎麼建索引需要謹慎考慮,一個表的索引數儘量不要超過6個

以下是sql語句在python中的實現:

    cursor = connection.cursor()
    sql1 = '''select b.fpath from (select id, lp_id from loupan_huxing where lp_id in (%s))a JOIN (select fpath, hx_no, hx_id from loupan_huxing_item where hx_no != '' and fpath != '')b on a.id=b.hx_id;''' % loupan_id
    cursor.execute(sql1)
    threedhuxingimgs = cursor.fetchall()
    if threedhuxingimgs:
        for oneitem in threedhuxingimgs:
            if oneitem[0]:
                img_list.append({'fpath':storage_tool.get_absolute_url(oneitem[0]), 'category':'33'})

    sql = '''select fpath, category from loupan_img where lp_id in (%s) and state=0
            order by (case WHEN category=1 THEN 1 WHEN category=2 THEN 2 WHEN category=3 THEN 3  WHEN category=4 THEN 4 WHEN category=5 THEN 5  WHEN category=6 THEN 6  WHEN category=10 THEN 7 ELSE 8 END),create_time asc;''' % loupan_id
    cursor.execute(sql)
    imgs = cursor.fetchall()
    if imgs:
        for one in imgs:
            if one[0] and one[1]:
                img_list.append({'fpath':storage_tool.get_absolute_url(one[0]), 'category':one[1]})
                introduce_images.append({'fpath':storage_tool.get_absolute_url(one[0]), 'category':one[1]})

    cursor.close()
    print(imgs, "SSSSSSSSSSSSSSSSSSSSSSSSS")

SELECT
 b.mf_user_id AS user_id,
 verified_time AS open_time,
 4 AS source 
FROM
 broker a
 JOIN `user` b ON a.user_id = b.id

SELECT
 a.id,
 a.state,
 a.STATUS,
 a.yft_status,
 a.force_status,
 a.price,
 a.area_name,
 a.city_name,
 a.NAME,
 a.main_layout,
 b.min_area,
 b.max_area,
 d.fpath,
 d.category,
 b.layout 
FROM
 loupan a
 LEFT JOIN (
 SELECT
  lp_id,
  min( area ) min_area,
  max( area ) max_area,
  layout 
 FROM
  loupan_huxing 
 WHERE
  state = 0 
  AND is_show = 1 
 GROUP BY
  lp_id 
 ) b ON a.id = b.lp_id
 LEFT JOIN (
 SELECT a.*
 FROM
 (
 SELECT
  lp_id,
  fpath,
  category
 FROM
  loupan_img 
 WHERE
  state = 0
 ORDER BY
  (
  CASE
    
    WHEN category = 1 THEN
    1 
    WHEN category = 2 THEN
    2 
    WHEN category = 3 THEN
    3 
    WHEN category = 4 THEN
    4 
    WHEN category = 5 THEN
    5 
    WHEN category = 6 THEN
    6 
    WHEN category = 10 THEN
    7 ELSE 8 
   END 
   )
   
   )a
  GROUP BY
  a.lp_id
  ) d ON a.id = d.lp_id
  LEFT JOIN ( SELECT house_id, unread FROM card_newhouse_view_record GROUP BY house_id ) e ON a.id = e.house_id 
 WHERE
  a.state = 0 
  AND ((
    a.STATUS = 1 
    AND a.yft_status IN ( 1, 3 ) 
    AND a.force_status IN ( 0, 1 )) 
  OR a.force_status = 1 
 )