首页 > 代码库 > python15-day12rabbitmq和mysql
python15-day12rabbitmq和mysql
1. 消息的批量分发
之前的例子都是一对一发送与接收,有时需要让消息被所有的queue收到,类似广播效果,这时候需要exchange了。
exchange在定义的时候是有类型的,以决定那些queue符合条件,可以接受消息。
l fanout:所有的bind到此exchange的queue都可以接收消息,纯广播了。
l direct:通过routingkey和exchange决定的哪一个唯一的queue可以接受消息。
l topic:所有符合routingKey(可以是一个表达式)的所bind的queue可以接收消息。
l headers:通过headers来决定把消息发送给哪些queue。(不常用)
1.1 fanout方式(广播)
发送端:
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
# connection =
pika.BlockingConnection(pika.ConnectionParameters(host=‘192.168.56.15‘))
channel = connection.channel()
channel.exchange_declare(exchange="logs",type=‘fanout‘)
message = ‘ ‘.join(sys.argv[1:]) or "info:Hello World!"
channel.basic_publish(exchange = ‘logs‘,routing_key=‘‘,body=message)
print("[x] sent %r" %message)
connection.close()
接收端(可以定义多个接收端):
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
channel=connection.channel()
channel.exchange_declare(exchange="logs",type=‘fanout‘)
result = channel.queue_declare(exclusive=True)
queue_name = result.method.queue
channel.queue_bind(exchange="logs",queue=queue_name)
print("[x] Waitting for logs.To exit press
CTRL+C")
def callback(ch,method,properties,body):
print("[x] %r "%body)
channel.basic_consume(callback,queue=queue_name,no_ack=True)
channel.start_consuming()
这样可以实现一对多的发送,即一个生产者多个消费者。
1.2 direct方式(关键字)
sent端:
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
channel = connection.channel()
channel.exchange_declare(exchange="direct_log",type="direct")
severity = sys.argv[1] if len(sys.argv) > 1 else
"info"
message = " ".join(sys.argv[2:]) or "hello world"
channel.basic_publish(exchange="direct_log",routing_key=severity,body=message)
print("[x] sent %r:%r" %(severity,message))
connection.close()
receive端:
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
channel = connection.channel()
channel.exchange_declare(exchange="direct_log",type="direct")
result = channel.queue_declare(exclusive=True)
queue_name = result.method.queue
severities = sys.argv[1:]
if not severities:
sys.stderr.write("usage: %s [info] [warning] [error]\n"%sys.argv[0])
sys.exit(1)
for severity
in severities:
channel.queue_bind(exchange="direct_log",queue=queue_name,routing_key=severity)
print("[*] waiting for logs to exit press
ctrl+c")
def callback(ch,method,properties,body):
print("[x] %r:%r"
%(method.routing_key,body))
channel.basic_consume(callback,queue=queue_name,no_ack=True)
channel.start_consuming()
执行结果:
1.3 topic方式(消息过滤)
To receive all the logs run:
python receive_logs_topic.py "#"
To receive all logs from the facility "kern":
python receive_logs_topic.py "kern.*"
Or if you want to hear only about "critical" logs:
python receive_logs_topic.py "*.critical"
You can create multiple bindings:
python receive_logs_topic.py "kern.*" "*.critical"
And to emit a log with a routing key "kern.critical" type:
python emit_log_topic.py "kern.critical" "A critical kernel error"
发送端:
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
channel = connection.channel()
channel.exchange_declare(exchange="topci_log",type="topic")
severity = sys.argv[1] if len(sys.argv) > 1 else
"info"
message = " ".join(sys.argv[2:]) or "hello world"
channel.basic_publish(exchange="topic_log",routing_key=severity,body=message)
print("[x] sent %r:%r" %(severity,message))
connection.close()
消费者:
import pika
import sys
username = ‘python‘ #指定远程rabbitmq的用户名密码
pwd = ‘python‘
user_pwd = pika.PlainCredentials(username, pwd)
connection = pika.BlockingConnection(pika.ConnectionParameters(‘192.168.56.15‘, credentials=user_pwd))
channel = connection.channel()
channel.exchange_declare(exchange="topic_log",type="topic")
result = channel.queue_declare(exclusive=True)
queue_name = result.method.queue
severities = sys.argv[1:]
if not severities:
sys.stderr.write("usage: %s [binding key....]\n"%sys.argv[0])
sys.exit(1)
for severity
in severities:
channel.queue_bind(exchange="topic_log",queue=queue_name,routing_key=severity)
print("[*] waiting for logs to exit press
ctrl+c")
def callback(ch,method,properties,body):
print("[x] %r:%r"
%(method.routing_key,body))
channel.basic_consume(callback,queue=queue_name,no_ack=True)
channel.start_consuming()
备注:和direct基本是一样的,把direct改为topic即可。
结果:
2. 数据库
什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,
每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database
Management System)的特点:
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
2.1 建表语句
mysql> create table students(stu_id int AUTO_INCREMENT,name char(32),age int,register_date DATE,primary key (stu_id));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| students |
+-------------------+
1 row in set (0.00 sec)
2.2 插入语句
mysql> insert into students (name,age,register_date) values ("liangkai",30,"20161228");
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liangkai | 30 | 2016-12-28 |
+--------+----------+------+---------------+
1 row in set (0.00 sec)
2.3 查询语句
mysql> select name,age from students where age >=30; #条件查询
+----------+------+
| name | age |
+----------+------+
| liangkai | 30 |
| xuliqing | 31 |
+----------+------+
2 rows in set (0.00 sec)
mysql> select stu_id,name,age from students limit 2 offset 2; #指定范围
+--------+----------+------+
| stu_id | name | age |
+--------+----------+------+
| 3 | xiaoqing | 25 |
| 4 | kaige | 24 |
+--------+----------+------+
2 rows in set (0.00 sec)
mysql> select stu_id,name,age from students limit 2 offset 1;
+--------+----------+------+
| stu_id | name | age |
+--------+----------+------+
| 2 | xuliqing | 31 |
| 3 | xiaoqing | 25 |
+--------+----------+------+
2 rows in set (0.00 sec)
mysql> select name,age from students where age >30 or age <25;
+----------+------+
| name | age |
+----------+------+
| xuliqing | 31 |
| kaige | 24 |
+----------+------+
2 rows in set (0.00 sec)
备注:使用主键来作为 WHERE 子句的条件查询是非常快速的。
where like语句:
mysql> select * from students where name like "%ng%";
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
+--------+----------+------+---------------+
2 rows in set (0.01 sec)
mysql> select * from students where name like "liang%";
+--------+-------+------+---------------+
| stu_id | name | age | register_date |
+--------+-------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
+--------+-------+------+---------------+
1 row in set (0.00 sec)
mysql>
2.4 修改语句
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | xuliqing | 31 | 2016-12-28 |
| 3 | xiaoqing | 25 | 2015-12-25 |
| 4 | kaige | 24 | 2015-07-10 |
+--------+----------+------+---------------+
4 rows in set (0.00 sec)
mysql> update students set name="qingqing",age=22 where stu_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update students set name="disanzhe",age=100 where stu_id > 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 3 | disanzhe | 100 | 2015-12-25 |
| 4 | disanzhe | 100 | 2015-07-10 |
+--------+----------+------+---------------+
4 rows in set (0.00 sec)
2.5 删除语句
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 3 | disanzhe | 100 | 2015-12-25 |
| 4 | disanzhe | 100 | 2015-07-10 |
+--------+----------+------+---------------+
4 rows in set (0.00 sec)
mysql> delete from students where stu_id >2;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
+--------+----------+------+---------------+
2 rows in set (0.00 sec)
2.6 排序
mysql> select * from students order by age;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 2 | qingqing | 22 | 2016-12-28 |
| 1 | liang | 23 | 2016-12-28 |
| 5 | xiaoming | 32 | 2015-11-24 |
| 6 | xiaohong | 43 | 2012-11-24 |
| 8 | jack | 46 | 2008-11-24 |
| 7 | JACK | 56 | 2002-11-24 |
+--------+----------+------+---------------+
6 rows in set (0.00 sec)
mysql> select * from students order by age desc;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 7 | JACK | 56 | 2002-11-24 |
| 8 | jack | 46 | 2008-11-24 |
| 6 | xiaohong | 43 | 2012-11-24 |
| 5 | xiaoming | 32 | 2015-11-24 |
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
+--------+----------+------+---------------+
6 rows in set (0.00 sec)
备注:使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
2.7 mysql group by语句
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+---------------+
6 rows in set (0.00 sec)
mysql> select register_date,count(register_date) from students group by register_date; #每天注册有多少人
+---------------+----------------------+
| register_date | count(register_date) |
+---------------+----------------------+
| 2016-12-28 | 6 |
+---------------+----------------------+
1 row in set (0.00 sec)
mysql> select avg(age) from students; #统计平均年龄
+----------+
| avg(age) |
+----------+
| 37.0000 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(age) from students; #统计所有人年龄之和
+----------+
| sum(age) |
+----------+
| 222 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select coalesce(register_date,"total"),count(register_date) from students group by register_date with rollup;
+---------------------------------+----------------------+
| coalesce(register_date,"total") | count(register_date) |
+---------------------------------+----------------------+
| 2016-12-28 | 6 |
| total | 6 |
+---------------------------------+----------------------+
2 rows in set (0.00 sec)
mysql>
2.8 添加字段
mysql> alter table students add core int not null; #添加字段
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from students;
+--------+----------+------+---------------+------+
| stu_id | name | age | register_date | core |
+--------+----------+------+---------------+------+
| 1 | liang | 23 | 2016-12-28 | 0 |
| 2 | qingqing | 22 | 2016-12-28 | 0 |
| 5 | xiaoming | 32 | 2016-12-28 | 0 |
| 6 | xiaohong | 43 | 2016-12-28 | 0 |
| 7 | JACK | 56 | 2016-12-28 | 0 |
| 8 | jack | 46 | 2016-12-28 | 0 |
+--------+----------+------+---------------+------+
6 rows in set (0.00 sec)
mysql> alter table students drop core; #删除一个字段
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+---------------+
6 rows in set (0.00 sec)
修改字段:
alter table students modify name char(8);
alter table students change name1 name2 date
mysql> select * from students;
+--------+----------+------+---------------+
| stu_id | name | age | register_date |
+--------+----------+------+---------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+---------------+
6 rows in set (0.00 sec)
mysql> alter table students modify name char(8); #修改字段类型
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> alter table students change register_date regi_date date;#修改字段名
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from students; +--------+----------+------+------------+
| stu_id | name | age | regi_date |
+--------+----------+------+------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+------------+
6 rows in set (0.00 sec)
mysql> desc students;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(8) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| regi_date | date | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table students modify stu_id int(8);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> desc students;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| stu_id | int(8) | NO | PRI | 0 | |
| name | char(8) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| regi_date | date | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.9 事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
l 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
l 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
l 事务用来管理insert,update,delete语句
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
l 事务的原子性:一组事务,要么成功;要么撤回。
l 稳定性 : 有非法数据(外键约束之类),事务撤回。
l 隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
l 可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
例如:
mysql> select * from students;
+--------+----------+------+------------+
| stu_id | name | age | regi_date |
+--------+----------+------+------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+------------+
6 rows in set (0.00 sec)
mysql> begin; #开启一个事务
Query OK, 0 rows affected (0.00 sec)
mysql> delete from students where stu_id=7; #删除一条数据
Query OK, 1 row affected (0.00 sec)
mysql> update students set name="xiaoqing",age=25 where stu_id=2; #修改
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+--------+----------+------+------------+
| stu_id | name | age | regi_date |
+--------+----------+------+------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | xiaoqing | 25 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+------------+
5 rows in set (0.00 sec)
mysql> rollback; #返回事务,这样数据是不会写入的,返回到begin的时候,如果想写到库里,执行commit
Query OK, 0 rows affected (0.01 sec)
mysql> select * from students;
+--------+----------+------+------------+
| stu_id | name | age | regi_date |
+--------+----------+------+------------+
| 1 | liang | 23 | 2016-12-28 |
| 2 | qingqing | 22 | 2016-12-28 |
| 5 | xiaoming | 32 | 2016-12-28 |
| 6 | xiaohong | 43 | 2016-12-28 |
| 7 | JACK | 56 | 2016-12-28 |
| 8 | jack | 46 | 2016-12-28 |
+--------+----------+------+------------+
6 rows in set (0.00 sec)
mysql>
2.10 索引
mySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE
INDEX
indexName
ON
mytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构
ALTER
mytable
ADD
INDEX
[indexName]
ON
(username(length))
创建表的时候直接指定
CREATE
TABLE
mytable(
ID
INT
NOT
NULL,
username
VARCHAR(16)
NOT
NULL,
INDEX
[indexName] (username(length))
);
删除索引的语法
DROP
INDEX
[indexName]
ON
mytable;
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建索引
创建索引
CREATE
UNIQUE
INDEX
indexName
ON
mytable(username(length))
修改表结构
ALTER
mytable
ADD
UNIQUE
[indexName]
ON
(username(length))
创建表的时候直接指定
CREATE
TABLE
mytable(
ID
INT
NOT
NULL,
username
VARCHAR(16)
NOT
NULL,
UNIQUE
[indexName] (username(length))
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引:
ALTER
TABLE
tbl_name
ADD
PRIMARY
KEY
(column_list):
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER
TABLE
tbl_name
ADD
UNIQUE
index_name (column_list):
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER
TABLE
tbl_name
ADD
INDEX
index_name (column_list):
添加普通索引,索引值可出现多次。
ALTER
TABLE
tbl_name
ADD
FULLTEXT index_name (column_list):
该语句指定了索引为 FULLTEXT ,用于全文索引。
以下实例为在表中添加索引。
mysql>
ALTER
TABLE
testalter_tbl
ADD
INDEX
(c);
你还可以在
ALTER
命令中使用
DROP
子句来删除索引。尝试以下实例删除索引:
mysql>
ALTER
TABLE
testalter_tbl
DROP
INDEX
(c);
使用 ALTER 命令添加和删除主键
主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT
NULL
)。实例如下:
mysql>
ALTER
TABLE
testalter_tbl
MODIFY
i
INT
NOT
NULL;
mysql>
ALTER
TABLE
testalter_tbl
ADD
PRIMARY
KEY
(i);
你也可以使用
ALTER
命令删除主键:
mysql>
ALTER
TABLE
testalter_tbl
DROP
PRIMARY
KEY;
删除指定时只需指定PRIMARY
KEY
,但在删除索引时,你必须知道索引名。
显示索引信息
mysql>
SHOW
INDEX
FROM
table_name\G
2.11 外键关联
create_table_class =
cursor.execute("create table class(cid INT AUTO_INCREMENT,caption
CHAR(32),PRIMARY KEY (cid))")
create_table_student = cursor.execute("create table student(sid int
auto_increment,"
"sname
char(32),"
"gender char(16),"
"class_id int(8),"
"primary key (sid),"
"KEY fk_class_id (class_id),"
"CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class
(cid))")
create_table_teacher = cursor.execute("create table teacher(tid INT
AUTO_INCREMENT,tname CHAR(32),PRIMARY KEY (tid))")
create_table_course = cursor.execute("create table course(cid INT
AUTO_INCREMENT,"
"cname CHAR(32),"
"teacher_id INT(8),"
"PRIMARY KEY (cid),"
"KEY fk_course_id (teacher_id),"
"CONSTRAINT fk_course_id FOREIGN KEY (teacher_id) REFERENCES teacher
(tid))") # fk_course_id外键名,可随意设定
create_table_score = cursor.execute("create table score(sid INT
AUTO_INCREMENT,"
"student_id int(8),"
"course_id INT(8),"
"number int(8),"
"PRIMARY KEY (sid),"
"KEY fk_score1_id (student_id),"
"CONSTRAINT fk_score1_id FOREIGN KEY (student_id) REFERENCES student
(sid),"
"KEY fk_score2_id (course_id),"
"CONSTRAINT
fk_score1_id FOREIGN KEY (course_id) REFERENCES course (cid))")
多表联表查询:
mysql> select student.sname,course.cname,score.number from score inner join course on course.cid = score.course_id inner join student on student.sid = score.student_id;
+--------+--------+--------+
| sname | cname | number |
+--------+--------+--------+
| 铁锤 | 生物 | 60 |
| 铁锤 | 体育 | 43 |
| 钢弹 | 体育 | 100 |
| 铁锤 | 物理 | 65 |
| 钢弹 | 物理 | 55 |
| 钢弹 | 生物 | 99 |
| 山炮 | 生物 | 73 |
| 山炮 | 体育 | 59 |
| 山炮 | 物理 | 30 |
+--------+--------+--------+
9 rows in set (0.00 sec)
mysql练习题 http://www.cnblogs.com/wupeiqi/articles/5729934.html
更多mysql知识,请看http://www.cnblogs.com/wupeiqi/articles/5713323.html
3. pymysql
3.1 连接数据库
在数据库创建python库,以及python的用户名密码,允许所有用户访问该数据库。并创建表host,插入数据。
create table host(host_id int(8) AUTO_INCREMENT,host char(32),primary key (host_id));
insert into host (host) values ("1.1.1.1");
查询创建结果:
mysql> select * from python.host;
+---------+---------+
| host_id | host |
+---------+---------+
| 1 | 1.1.1.1 |
+---------+---------+
1 row in set (0.00 sec)
在pycharm创建脚本连接数据库:
import pymysql
conn = pymysql.connect(host="192.168.56.15",port=3306,user="python",password="123456",charset=‘utf8‘) #如果插入的数据中有中文,需要指定字符编码集
cursor =conn.cursor()
cursor.execute(‘create database if not exists python‘)
cursor.execute(‘use python‘)
effect_row=cursor.execute("update
host set host=‘192.168.56.15‘")
conn.commit()
cursor.close()
conn.close()
查看修改结果:
mysql> select * from python.host;
+---------+---------------+
| host_id | host |
+---------+---------------+
| 1 | 192.168.56.15 |
+---------+---------------+
1 row in set (0.00 sec)
3.2 查看mysql的输出结果
import pymysql
conn = pymysql.connect(host="192.168.56.15",port=3306,user="python",password="123456", charset=‘utf8‘)
cursor =conn.cursor()
#输出字典类型数据
#cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
effect_row=cursor.execute("update host set host=‘192.168.56.15‘
WHERE host_id<15")
#执行命令
cursor.execute("select * from host")
# 获取执行命令后输出的第一行数据
#row_1 = cursor.fetchone()
# 获取前n行数据
row_2 =
cursor.fetchmany(3)
# 获取所有数据
#row_3 = cursor.fetchall()
print(row_2)
conn.commit()
cursor.close()
conn.close()
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
l cursor.scroll(1,mode=‘relative‘) # 相对当前位置移动
l cursor.scroll(2,mode=‘absolute‘) # 相对绝对位置移动
输出的结果默认是元组类型的数据,如果想输出字典类型数据可以使用:
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
参考资料:
python-mysqldb : http://www.cnblogs.com/wupeiqi/articles/5095821.html
pymysql : http://www.cnblogs.com/wupeiqi/articles/5713330.html
4. redis
4.1 Redis优点
l 异常快速 : Redis是非常快的,每秒可以执行大约110000设置操作,81000个/每秒的读取操作。
l 支持丰富的数据类型 : Redis支持最大多数开发人员已经知道如列表,集合,可排序集合,哈希等数据类型。
l 这使得在应用中很容易解决的各种问题,因为我们知道哪些问题处理使用哪种数据类型更好解决。
l 操作都是原子的 : 所有 Redis 的操作都是原子,从而确保当两个客户同时访问 Redis 服务器得到的是更新后的值(最新值)。
l MultiUtility工具:Redis是一个多功能实用工具,可以在很多如:缓存,消息传递队列中使用(Redis原生支持发布/订阅),在应用程序中,如:Web应用程序会话,网站页面点击数等任何短暂的数据;
4.2 安装redis:
$ wget http://download.redis.io/releases/redis-2.8.3.tar.gz
$ tar xzf redis-2.8.3.tar.gz
$ cd redis-2.8.3
$ make && make install
4.3 使用redis
4.3.1 设置查询
[root@192.168.56.1][~]
# redis-cli
127.0.0.1:6379> keys * #查询所有key
(empty list or set)
127.0.0.1:6379> set name liangkai #设置key
OK
127.0.0.1:6379> keys *
1) "name"
127.0.0.1:6379> set age 22
OK
127.0.0.1:6379> get name #查询key
"liangkai"
127.0.0.1:6379> get age
"22"
127.0.0.1:6379> mget name age #查询多个key
1) "liangkai"
2) "22"
127.0.0.1:6379>
set的参数:
n ex:过期时间(秒),和setex功能一样
n px:过期时间(毫秒),和psetex(name, time_ms, value)一样
n nx:只有key值不存在时,设置操作才会生效,不会修改已经存在的key值,和setnx一样
n xx:只有key值存在时,设置操作才会生效,会修改已经存在的key值
127.0.0.1:6379> set name kaige ex 5
OK
127.0.0.1:6379> get name
"kaige"
127.0.0.1:6379> get name
(nil)
127.0.0.1:6379> set name kaige px 5000
OK
127.0.0.1:6379> get name
"kaige"
127.0.0.1:6379> get name
(nil)
127.0.0.1:6379>
同时设置多个key
127.0.0.1:6379> mset age 22 sex male
OK
127.0.0.1:6379> get sex
"male"
127.0.0.1:6379> get age
"22"
127.0.0.1:6379>
同时查询多个key
127.0.0.1:6379> mget age sex
1) "22"
2) "male"
127.0.0.1:6379>
getset设置新值并获取原来的值:
127.0.0.1:6379> getset age 35
"22"
127.0.0.1:6379> get age
"35"
127.0.0.1:6379>
getrange获取指定长度的value
127.0.0.1:6379> get sex
"male"
127.0.0.1:6379> getrange sex 2 4
"le"
127.0.0.1:6379> getrange sex 1 3
"ale"
127.0.0.1:6379> getrange sex 2 3
"le"
127.0.0.1:6379>
setrange修改指定长度的value
127.0.0.1:6379> setrange sex 2 kkkkk
(integer) 7
127.0.0.1:6379> get sex
"makkkkk"
127.0.0.1:6379>
setbit设置二进制位值
127.0.0.1:6379> get name #name的value是A
"A"
127.0.0.1:6379> setbit name 7 0
(integer) 1
127.0.0.1:6379> get name
"@"
127.0.0.1:6379>
说明:name的value值是A,它的ascii码是65,也就是01000001,setbit name 7 0 即把第7为改为0,修改后为01000000,根据ascii码表是“@”符号。
getbit获取对应的二进制位
127.0.0.1:6379> getbit name 1 #name的value是01000000
(integer) 1
127.0.0.1:6379> getbit name 0
(integer) 0
127.0.0.1:6379> getbit name 2
(integer) 0
127.0.0.1:6379>
bitcount获取key对应value的二进制中1的个数
127.0.0.1:6379> get name
"@"
127.0.0.1:6379> bitcount name
(integer) 1
127.0.0.1:6379>
总结:关于bit的作用
举例,我们可以统计实时有多少用户登录。
每个用户都有对应的id,我们可以把对应id的二进制位改为1,然后统计1的个数,即是当前在线的用户数。
127.0.0.1:6379> set name 0 #设置一个key值为0
OK
127.0.0.1:6379> bitcount name #查看当前有2个1
(integer) 2
127.0.0.1:6379> setbit name 213 1 #为id为213的用户写一个1
(integer) 0
127.0.0.1:6379> setbit name 21000 1
(integer) 0
127.0.0.1:6379> setbit name 2100 1
(integer) 0
127.0.0.1:6379> setbit name 3002 1
(integer) 0
127.0.0.1:6379> setbit name 301 1
(integer) 0
127.0.0.1:6379> setbit name 51 1
(integer) 0
127.0.0.1:6379> bitcount name #统计1的个数为8个,减去初始的两个,所以当前有6个用户在线。还可以根据1的位置,计算出用户的id。
(integer) 8
127.0.0.1:6379>
strlen计算key对应value的字节长度
127.0.0.1:6379> strlen name
(integer) 2626
127.0.0.1:6379>
incr自增
127.0.0.1:6379> incr name
(integer) 4
127.0.0.1:6379> incr name
(integer) 5
127.0.0.1:6379> incr name
(integer) 6
127.0.0.1:6379> get name
"6"
incrbyfloat自增
127.0.0.1:6379> incrbyfloat name 1.1
"11.1"
127.0.0.1:6379> incrbyfloat name 1.12
"12.22"
decr自减
127.0.0.1:6379> set name 10
OK
127.0.0.1:6379> get name
"10"
127.0.0.1:6379> DECR name
(integer) 9
127.0.0.1:6379> DECR name
(integer) 8
append追加
127.0.0.1:6379> get name
"8"
127.0.0.1:6379> append name 56
(integer) 3
127.0.0.1:6379> get name
"856"
127.0.0.1:6379>
附件列表
python15-day12rabbitmq和mysql