SQLAlchemy 几种查询方式

#简单查询 print(session.query(User).all()) print(session.query(User.name, User.fullname).all()) print(session.query(User, User.name).all()) #带条件查询 print(session.query(User).filter_by(name='user1').all()) print(session.query(User).filter(User.name == "user").all()) print(session.query(User).filter(User.name.like("user%")).all()) #多条件查询 print(session.query(User).filter(and_(User.name.like("user%"), User.fullname.like("first%"))).all()) print(session.query(User).filter(or_(User.name.like("user%"), User.password != None)).all()) #sql过滤 print(session.query(User).filter("id>:id").params(id=1).all()) #关联查询 print(session.query(User, Address).filter(User.id == Address.user_id).all()) print(session.query(User).join(User.addresses).all()) print(session.query(User).outerjoin(User.addresses).all()) #聚合查询 print(session.query(User.name, func.count('*').label("user_count")).group_by(User.name).all()) print(session.query(User.name, func.sum(User.id).label("user_id_sum")).group_by(User.name).all()) #子查询 stmt = session.query(Address.user_id, func.count('*').label("address_count")).group_by(Address.user_id).subquery() print(session.query(User, stmt.c.address_count).outerjoin((stmt, User.id == stmt.c.user_id)).order_by(User.id).all()) #exists print(session.query(User).filter(exists().where(Address.user_id == User.id))) print(session.query(User).filter(User.addresses.any())) 限制返回字段查询 person = session.query(Person.name, Person.created_at, Person.updated_at).filter_by(name="zhongwei").order_by( Person.created_at).first() 记录总数查询 from sqlalchemy import func # count User records, without # using a subquery. session.query(func.count(User.id)) # return count of user "id" grouped # by "name" session.query(func.count(User.id)).\ group_by(User.name) from sqlalchemy import distinct # count distinct "name" values session.query(func.count(distinct(User.name)))

mysql 行转列

在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。 http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198 现整理解法如下: 数据样本: create table tx( id int primary key, c1 char(2), c2 char(2), c3 int ); insert into tx values (1 ,'A1','B1',9), (2 ,'A2','B1',7), (3 ,'A3','B1',4), (4 ,'A4','B1',2), (5 ,'A1','B2',2), (6 ,'A2','B2',9), (7 ,'A3','B2',8), (8 ,'A4','B2',5), (9 ,'A1','B3',1), (10 ,'A2','B3',8), (11 ,'A3','B3',8), (12 ,'A4','B3',6), (13 ,'A1','B4',8), (14 ,'A2','B4',2), (15 ,'A3','B4',6), (16 ,'A4','B4',9), (17 ,'A1','B4',3), (18 ,'A2','B4',5), (19 ,'A3','B4',2), (20 ,'A4','B4',5); mysql> select * from tx; +----+------+------+------+ | id | c1   | c2   | c3   | +----+------+------+------+ |  1 | A1   | B1   |    9 | |  2 | A2   | B1   |    7 | |  3 | A3   | B1   |    4 | |  4 | A4   | B1   |    2 | |  5 | A1   | B2   |    2 | |  6 | A2   | B2   |    9 | |  7 | A3   | B2   |    8 | |  8 | A4   | B2   |    5 | |  9 | A1   | B3   |    1 | | 10 | A2   | B3   |    8 | | 11 | A3   | B3   |    8 | | 12 | A4   | B3   |    6 | | 13 | A1   | B4   |    8 | | 14 | A2   | B4   |    2 | | 15 | A3   | B4   |    6 | | 16 | A4   | B4   |    9 | | 17 | A1   | B4   |    3 | | 18 | A2   | B4   |    5 | | 19 | A3   | B4   |    2 | | 20 | A4   | B4   |    5 | +----+------+------+------+ 20 rows in set (0.00 sec) mysql> 期望结果 +------+-----+-----+-----+-----+------+ |C1    |B1   |B2   |B3   |B4   |Total | +------+-----+-----+-----+-----+------+ |A1    |9    |2    |1    |11   |23    | |A2    |7    |9    |8    |7    |31    | |A3    |4    |8    |8    |8    |28    | |A4    |2    |5    |6    |14   |27    | |Total |22   |24   |23   |40   |109   | +------+-----+-----+-----+-----+------+ 1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total mysql> SELECT ->     IFNULL(c1,'total') AS total, ->     SUM(IF(c2='B1',c3,0)) AS B1, ->     SUM(IF(c2='B2',c3,0)) AS B2, ->     SUM(IF(c2='B3',c3,0)) AS B3, ->     SUM(IF(c2='B4',c3,0)) AS B4, ->     SUM(IF(c2='total',c3,0)) AS total -> FROM ( ->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3 ->     FROM tx ->     GROUP BY c1,c2 ->     WITH ROLLUP ->     HAVING c1 IS NOT NULL -> ) AS A -> GROUP BY c1 -> WITH ROLLUP; +-------+------+------+------+------+-------+ | total | B1   | B2   | B3   | B4   | total | +-------+------+------+------+------+-------+ | A1    |    9 |    2 |    1 |   11 |    23 | | A2    |    7 |    9 |    8 |    7 |    31 | | A3    |    4 |    8 |    8 |    8 |    28 | | A4    |    2 |    5 |    6 |   14 |    27 | | total |   22 |   24 |   23 |   40 |   109 | +-------+------+------+------+------+-------+ 5 rows in set, 1 warning (0.00 sec) 2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total mysql> select c1, -> sum(if(c2='B1',C3,0)) AS B1, -> sum(if(c2='B2',C3,0)) AS B2, -> sum(if(c2='B3',C3,0)) AS B3, -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL -> from tx -> group by C1 -> UNION -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1, -> sum(if(c2='B2',C3,0)) AS B2, -> sum(if(c2='B3',C3,0)) AS B3, -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX -> ; +-------+------+------+------+------+-------+ | c1    | B1   | B2   | B3   | B4   | TOTAL | +-------+------+------+------+------+-------+ | A1    |    9 |    2 |    1 |   11 |    23 | | A2    |    7 |    9 |    8 |    7 |    31 | | A3    |    4 |    8 |    8 |    8 |    28 | | A4    |    2 |    5 |    6 |   14 |    27 | | TOTAL |   22 |   24 |   23 |   40 |   109 | +-------+------+------+------+------+-------+ 5 rows in set (0.00 sec) mysql> 3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询 mysql> select ifnull(c1,'total'), -> sum(if(c2='B1',C3,0)) AS B1, -> sum(if(c2='B2',C3,0)) AS B2, -> sum(if(c2='B3',C3,0)) AS B3, -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL -> from tx -> group by C1 with rollup ; +--------------------+------+------+------+------+-------+ | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL | +--------------------+------+------+------+------+-------+ | A1                 |    9 |    2 |    1 |   11 |    23 | | A2                 |    7 |    9 |    8 |    7 |    31 | | A3                 |    4 |    8 |    8 |    8 |    28 | | A4                 |    2 |    5 |    6 |   14 |    27 | | total              |   22 |   24 |   23 |   40 |   109 | +--------------------+------+------+------+------+-------+ 5 rows in set (0.00 sec) mysql> 4. 动态,适用于列不确定情况, mysql> SET @EE=''; mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A; mysql> SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP'); Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt2 FROM @QQ; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE stmt2; +--------------------+------+------+------+------+-------+ | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL | +--------------------+------+------+------+------+-------+ | A1                 |    9 |    2 |    1 |   11 |    23 | | A2                 |    7 |    9 |    8 |    7 |    31 | | A3                 |    4 |    8 |    8 |    8 |    28 | | A4                 |    2 |    5 |    6 |   14 |    27 | | total              |   22 |   24 |   23 |   40 |   109 | +--------------------+------+------+------+------+-------+ 5 rows in set (0.00 sec) mysql> 另外: 如果您是在navicat for maysql 中的查询中测试,请输入: SET @EE=''; select @EE :=CONCAT(@EE,'sum(if(C2= \'',C2,'\',C3,0)) as ',C2, ',') FROM (SELECT DISTINCT C2 FROM tx) A; SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP'); PREPARE stmt2 FROM @QQ; EXECUTE stmt2 原文 http://blog.csdn.net/zhoushengchao/article/details/7321688

MySQL exists的用法介绍

有一个查询如下:
SELECT c.CustomerId, CompanyName   
FROM Customers c   
WHERE EXISTS(   
	SELECT OrderID FROM Orders o   
	WHERE o.CustomerID = cu.CustomerID)
这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊,这是如何匹配的呢? EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。 EXISTS 指定一个子查询,检测行的存在。语法:EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型为 Boolean,如果子查询包含行,则返回 TRUE。
    • 在子查询中使用 NULL 仍然返回结果集
这个例子在子查询中指定 NULL,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
    • 比较使用 EXISTS 和 IN 的查询
这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
    (SELECT *
    FROM titles
    WHERE pub_id = publishers.pub_id
    AND type = 'business')

SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
    (SELECT pub_id
    FROM titles
    WHERE type = 'business')
    • 比较使用 EXISTS 和 = ANY 的查询
本示例显示查找与出版商住在同一城市中的作者的两种查询方法:第一种方法使用 = ANY,第二种方法使用 EXISTS。注意这两种方法返回相同的信息。
SELECT au_lname, au_fname
FROM authors
WHERE exists
    (SELECT *
    FROM publishers
    WHERE authors.city = publishers.city)

SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
    (SELECT city
    FROM publishers)
    • 比较使用 EXISTS 和 IN 的查询
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
SELECT title
FROM titles
WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE pub_id = titles.pub_id
    AND city LIKE 'B%')

SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE 'B%')
    • 使用 NOT EXISTS
NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行,则满足 NOT EXISTS 中的 WHERE 子句。本示例查找不出版商业书籍的出版商的名称:
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
    (SELECT *
    FROM titles
    WHERE pub_id = publishers.pub_id
    AND type = 'business')
ORDER BY pub_name
又比如以下 SQL 语句:
select distinct 姓名 from xs
where not exists (
select * from kc
where not exists (
select * from xs_kc
where 学号=xs.学号 and 课程号=kc.课程号
)
把最外层的查询xs里的数据一行一行的做里层的子查询。 中间的 exists 语句只做出对上一层的返回 true 或 false,因为查询的条件都在 where 学号=xs.学号 and 课程号=kc.课程号这句话里。每一个 exists 都会有一行值。它只是告诉一层,最外层的查询条件在这里成立或都不成立,返回的时候值也一样回返回上去。直到最高层的时候如果是 true(真)就返回到结果集。为 false(假)丢弃。
where not exists
select * from xs_kc
where 学号=xs.学号 and 课程号=kc.课程号
这个 exists 就是告诉上一层,这一行语句在我这里不成立。因为他不是最高层,所以还要继续向上返回。 select distinct 姓名 from xs where not exists (这里的 exists 语句收到上一个为 false 的值。他在判断一下,结果就是为 true(成立),由于是最高层所以就会把这行的结果(这里指的是查询条件)返回到结果集。 几个重要的点:
  • 最里层要用到的醒询条件的表比如:xs.学号、kc.课程号等都要在前面的时候说明一下select * from kc,select distinct 姓名 from xs
  • 不要在太注意中间的exists语句.
  • 把exists和not exists嵌套时的返回值弄明白
原文

Redis系列-安装部署维护篇 (转)

Redis是个高性能的key-value数据库,它的key具有丰富的数据结构:string,hash,list set和sorted set。作为NOSQL,比起memcache之类,不仅仅key数据结构丰富,而且具有持久化的功能,并且能够支持主从复制,很方便构建集群。redis高性能很大程度上源于它是个内存型数据库,它的高性能表现在:set操作11w/s,get操作8.1w/s,与其他类型数据库性能差异,可以而参考:http://timyang.net/data/mcdb-tt-redis/   。为了进一步加深对redis的理解总结,我打算写个redis系列的博客。这里主要谈谈redis安装部署及运维维护。 1、下载安装  
  1. [root@xsf003 tool]# wget -c http://redis.googlecode.com/files/redis-2.4.17.tar.gz    #下载
  2. [root@xsf003 tool]# tar -zxvf redis-2.4.17.tar.gz   #解压
  3. [root@xsf003 tool]# cd redis-2.4.17
  4. [root@xsf003 redis-2.4.17]# make  #编译
  5. [root@xsf003 redis-2.4.17]# make install #安装
安装完毕,常用工具会自动拷贝到/user/loca/bin目录下。做为服务器,我们常常还需要把redis设置成开机自启动,源码包中有个很好用的脚本,执行脚步根据提示输入即可。    
  1. [root@xsf003 redis-2.4.17]# cd utils/
  2. [root@xsf003 utils]# ./install_server.sh
  3. Welcome to the redis service installer
  4. This script will help you easily set up a running redis server
  5. Please select the redis port for this instance: [6379]
  6. Selecting default: 6379
  7. Please select the redis config file name [/etc/redis/6379.conf]
  8. Selected default - /etc/redis/6379.conf
  9. Please select the redis log file name [/var/log/redis_6379.log]
  10. Selected default - /var/log/redis_6379.log
  11. Please select the data directory for this instance [/var/lib/redis/6379]
  12. Selected default - /var/lib/redis/6379
  13. Please select the redis executable path [/usr/local/bin/redis-server]
  14. Copied /tmp/6379.conf => /etc/init.d/redis_6379
  15. Installing service...
  16. Successfully added to chkconfig!
  17. Successfully added to runlevels 345!
  18. Starting Redis server...
  19. Installation successful!
  注意执行install_server.sh,需要先进入utils目录,不然脚本会报错,提示找不到相应文件。安装完服务,redis自动启动,可以用ps命令查看到相关信息:  
  1. [root@xsf003 utils]# ps -ef | grep redis
  2. root      4554     1  0 10:55 ?        00:00:02 /usr/local/bin/redis-server /etc/redis/6379.conf
  3. root      4564  2808  0 10:59 pts/0    00:00:00 grep redis
2、手动启动关闭服务    
  1. [root@xsf003 utils]# /etc/init.d/redis_6379 stop   #关闭
  2. [root@xsf003 utils]# /etc/init.d/redis_6379 start  #启动
也可以用下面类似的命令直接启动关闭redis服务:  
  1. /usr/local/bin/redis-server /etc/redis/redis.conf   #指定配置文件 启动
  2. /usr/local/bin/redis-cli -p 6379 shutdown   # 关闭,如果默认端口6379 可以直接 /usr/local/bin/redis-cli shutdown
  3、通过客户端命令行工具连接redis服务查看redis相关信息   a)连接  
  1. [root@xsf003 utils]# redis-cli
  2. redis 127.0.0.1:6379>
b)其他指令    
  1. redis 127.0.0.1:6379> info  #查看server版本内存使用连接等信息
  2. redis 127.0.0.1:6379> client list  #获取客户连接列表
  3. redis 127.0.0.1:6379> client kill 127.0.0.1:33441 #终止某个客户端连接
  4. redis 127.0.0.1:6379> dbsize #当前保存key的数量
  5. redis 127.0.0.1:6379> save #立即保存数据到硬盘
  6. redis 127.0.0.1:6379> bgsave #异步保存数据到硬盘
  7. redis 127.0.0.1:6379> flushdb #当前库中移除所有key
  8. redis 127.0.0.1:6379> flushall #移除所有key从所有库中
  9. redis 127.0.0.1:6379> lastsave #获取上次成功保存到硬盘的unix时间戳
  10. redis 127.0.0.1:6379> monitor #实时监测服务器接收到的请求
  11. redis 127.0.0.1:6379> slowlog len #查询慢查询日志条数
  12. (integer) 3
  13. redis 127.0.0.1:6379> slowlog get #返回所有的慢查询日志,最大值取决于slowlog-max-len配置
  14. redis 127.0.0.1:6379> slowlog get 2 #打印两条慢查询日志
  15. redis 127.0.0.1:6379> slowlog reset #清空慢查询日志信息
通过以上操作,单台服务器基本跑起来了,不过后面的路还很长很长。。。。    

Redis系列-远程连接redis并给redis加锁

本篇其实是可以和上篇合并的,但由于blog太长编辑麻烦,阅读累人,打算新开一篇, 方便阅读查找。 假设两台redis服务器,ip分别为:192.168.1.101和192.168.1.103,如何在101上通过redis-cli访问103上的redis呢?在远程连接103之前,先讲下redis-cli的几个关键参数: 用法:redis-cli [OPTIONS] [cmd [arg [arg ...]]] -h <主机ip>,默认是127.0.0.1 -p <端口>,默认是6379 -a <密码>,如果redis加锁,需要传递密码 --help,显示帮助信息 通过对rendis-cli用法介绍,在101上连接103应该很简单:  
  1. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379
  2. redis 192.168.1.103:6379>
在101上对103设置个个string值 user.1.name=zhangsan    
  1. redis 192.168.1.103:6379> set user.1.name zhangsan
  2. OK
看到ok,表明设置成功了。然后直接在103上登陆,看能不能获取到这个值。    
  1. [root@xsf003 utils]# redis-cli
  2. redis 127.0.0.1:6379> get user.1.name
  3. "zhangsan"
木错吧,确实是zhangsan,这说明101上连的是103上的redis服务器。当然能够成功连接103是有基本条件的,101上可以喝103上的6379端口通信。   人人都可以连接redis服务器是很危险的,我们需要给103上的redis设置个密码,怎么设置呢,需要编辑redis的配置文件/etc/redis/6379.conf  
  1. [root@xsf003 utils]# vim /etc/redis/6379.conf
找到# requirepass foobared 去掉前面的注释#,并把foobared 替换为你自己的密码:hi, coder    
  1. requirepass "hi, coder"
保存配置文件之后,重启redis服务    
  1. [root@xsf003 utils]# /etc/init.d/redis_6379 stop
  2. Stopping ...
  3. Waiting for Redis to shutdown ...
  4. Redis stopped
  5. [root@xsf003 utils]# /etc/init.d/redis_6379 start
  6. Starting Redis server...
101上重新连接103并获取user.1.name的值    
  1. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379
  2. redis 192.168.1.103:6379> get user.1.name
  3. (error) ERR operation not permitted
  4. redis 192.168.1.103:6379>
为什么是error呢,当然是因为连接103时没传递密码了,退出重新连    
  1. redis 192.168.1.103:6379> quit
  2. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379 -a "hi, coder"
  3. redis 192.168.1.103:6379> get user.1.name
  4. "zhangsan"
看到zhangsan,说明你已经连接成功了。关于get、set    用法,在下个blog中讲,没有耐心的观众可以直接看这里:http://redis.io/commands#string   原文地址