Mysql语句

Flask框架SQL语句使用

• 查询语句
  # 根据id查询所有
  sql = “select * from news where id=%s” % id
  cur.execute(sql)
  res = cur.fetchone()
  #
  sql = “select id,username,level from users limit %s,2” % ((page-1)*2)
  cur.execute(sql)
  res = cur.fetchall()
  pages = range(1, math.ceil(length/2)+1)

  sql = "select group_concat(n_name) from contact where n_id=%s" % id
  cur.execute(sql)
  
  sql = "select n.id,n.name,n.source,n.time,n.s_id,c.n_name,k.keyword from new as n left join (select c.n_id,group_concat(c.n_name) as n_name from contact as c group by c.n_id) as c on n.id=c.n_id left join (select k.n_id,group_concat(k.keyword) as keyword from keynews as k group by k.n_id) as k on n.id=k.n_id"
  # 查询不同分类新闻的最新一条新闻
  sql = "select n_id from contact a where (select count(1) from contact b where 				a.n_name=b.n_name and b.n_id>=a.n_id)<=1"
  cur.execute(sql)
  res3 = cur.fetchall()
  # 多个条件查询语句
  select * from student where sex='男' and age=20;
  # 关于and和or的多个条件查询语句
  sql = "select * from person where (user='%s' and toU='%s') or (user='%s' and toU='%s')" % (user, to, to, user)
  

• 国药查询语句
  sql = "select gys.id,gys.name,group_concat(gyszz.name),group_concat(gyszz.timeout) from gongyingshang as gys left join gongyingshangzizhi as gyszz on gyszz.gid=gys.id where gys.cid = 1 group by gys.id "

  查询供应商资质（当前采购员下的所有供应商的所有资质信息）
  
  select gys.id,gys.name,yp.name from gongyingshang as gys left join yaopin as yp on yp.gid=gys.id where gys.cid=1 
  
  查询药品（当前采购员下的所有供应商的所用药品）
  
  sql = "select gys.id,gys.name,group_concat(ypzz.name),group_concat(ypzz.timeout) from ((gongyingshang as gys left join yaopin as yp on yp.gid=gys.id) left join yaopinzizhi as ypzz on ypzz.yid=yp.id ) where gys.cid=1 group by gys.id"
  
  查询药品资质（当前采购员下所有供应商的所有药品的所有资质）
  (
   (1, '供应商1', '药品交易资格证', datetime.datetime(2018, 11, 30, 0, 0)), 
   (1, '供应商1', '生产许可证', datetime.datetime(2018, 11, 30, 0, 0)), 
   (1, '供应商1', '药品交易资格证', datetime.datetime(2018, 11, 30, 0, 0)), 
   (1, '供应商1', '生产许可证', datetime.datetime(2018, 11, 30, 0, 0)), 
   (2, '供应商2', '药品交易资格证', datetime.datetime(2018, 11, 30, 0, 0)), 
   (2, '供应商2', '生产许可证', datetime.datetime(2018, 11, 30, 0, 0))
  )
  

• 插入语句
  sql = “insert into users (username,password,level) values (’%s’,’%s’,%s)” % (username, password, 2)
  #执行sql语句
  cur.execute(sql)
  db.commit()

  keywordlist = keyword.split("，")
  str = ""
  for item in keywordlist:
    str += "(%s,'%s')," % (res[-1][0], item)
  sql2 = "insert into keynews (n_id,keyword) values"+str[:-1]
  cur.execute(sql2)
  db.commit()
  

• 删除语句
  sql = “delete from users where username=’%s’” % name
  cur.execute(sql)
  db.commit()

• 更新语句
  sql = “update users set password=’%s’ where id=’%s’” % (newpass, id)
  cur.execute(sql)
  db.commit()