协程
'''
并发的解决方案1：
多进程
多线程
总结两点：
什么叫并发：看起来同时运行，
如何实现并发*
进程线程都是由操作系统调度的
并发的解决方案2：
协程：单线程下实现的并发，应用程序级别的切换，操作系统无法感知
找到一种解决方案：
1、在多个任务直接切换+保存状态
2、检测应用程序里的IO，实现遇到IO操作时才切换
'''
#串行执行
import time
def consumer(res):
'''任务1:接收数据,处理数据'''
pass
def producer():
'''任务2:生产数据'''
res=[]
for i in range(10000000):
res.append(i)
return res
# start=time.time()
# #串行执行
# res=producer()
# consumer(res)
# stop=time.time()
# print(stop-start) #
#基于yield并发执行
# import time
# def consumer():
# '''任务1:接收数据,处理数据'''
# while True:
# print('consumer')
# x=yield
#
# def producer():
# '''任务2:生产数据'''
# g=consumer()
# next(g)
# for i in range(10000000):
# print('producer')
# g.send(i)
# time.sleep(1000)
#
# start=time.time()
# #基于yield保存状态,实现两个任务直接来回切换,即并发的效果
# #PS:如果每个任务中都加上打印,那么明显地看到两个任务的打印是你一次我一次,即并发执行的.
# producer()
#
# stop=time.time()
# print(stop-start)
#
import time
from greenlet import greenlet
def eat(name):
print('%s eat 1' %name)
time.sleep(1000)
g2.switch('alex')
print('%s eat 2' %name)
g2.switch()
print('%s eat 3' %name)
g2.switch()
def play(name):
print('%s play 1' %name)
g1.switch()
print('%s play 2' %name)
g1.switch()
print('%s play 3' %name)
g1=greenlet(eat)
g2=greenlet(play)
g1.switch('egon')
# g2.switch('alex')
Gevent模块
#pip3 install gevent
from gevent import monkey;monkey.patch_all() #写到文件的首部
import gevent
import time
from threading import current_thread
def eat(name):
print('%s eat 1 %s' %(name,current_thread().getName()))
time.sleep(2)
# gevent.sleep(5)
print('%s eat 2 %s' %(name,current_thread().getName()))
return 10
def play(name):
print('%s play 1 %s' %(name,current_thread().getName()))
time.sleep(3)
# gevent.sleep(7)
print('%s play 2 %s' %(name,current_thread().getName()))
return 20
start=time.time()
g1=gevent.spawn(eat,name='egon')
g2=gevent.spawn(play,'alex')
# print(g1,g2)
# g1.join()
# g2.join()
gevent.joinall([g1,g2])
# print(g1.value)
# print(g2.value)
print(time.time()-start)
数据库介绍
文件夹：库
文件：表（字段就是表的标题）
一行内容：记录
数据库管理软件：MySQL，Oracle，Sql server，DB2
数据库服务器: 安装有数据库管理软件的一台机器
yum install mysql-server mysql
MySQL基本管理
#设置密码
mysqladmin -uroot -p password "123"
mysqladmin -uroot -p123 password "123456"
#破解密码：方式一
关闭mysql
启动mysql
mysqld --skip-grant-tables # 跳过授权表启动
mysql -uroot -p
update mysql.user set password=password('123') where user='root' and host='localhost';
flush privileges;
关闭mysql
启动mysql
#破解密码：修改配置文件
#创建账号
grant select,update on 库.表 to "用户名"@"192.168.1.%" identified by '123';
grant all on 库.表 to "用户名"@"localhost" identified by '123';
grant all on 库.表 to "用户名"@"%" identified by '123';
grant all on *.* to "用户名"@"%" identified by '123';
flush privileges;
基本的sql语句
文件夹：库
增
create database db1 charset utf8;
查
show databases;
show create database db1;
改
alter database db1 charset latin1;
删
drop database db1;
文件：表
切换文件夹（库）：use db1：
select database(); #查看当前所在的库
增
create table t1(
id int,
name char
)engine=innodb;
查
show create table t1;
show tables;
desc t1;
改
alter table t1 add age int;
alter table t1 drop age;
alter table t1 modify name char(4);
alter table t1 change name xxxx char(4);
删
drop table t1;
一行内容：记录
增
insert into db1.t1 values
(1,'a'),
(2,'e'),
(3,'w');
insert into db1.t1(name,id) values
('c',4);
insert into db1.t1(name) values
('d');
查
select * from t1;
select name from t1;
改
update t1 set name='A';
update t1 set name='S' where id=3;
删
delete from t1 where id >=2 and id < 4; #删某几条记录就用delete
删除整张表所有记录
delete from t1; #不要用
要用
truncate t2;
create table t2(id int primary key auto_increment,name char(16));
insert into t2(name) values
('alex1'),
('alex2'),
('alex3'),
('alex4'),
('alex5');
查看帮助：
help create
存储引擎
create database db2;
use db2;
create table t1(x char(4))engine=innodb;
create table t2(x char(4))engine=myisam;
create table t3(x char(4))engine=blackhole;
create table t4(x char(4))engine=memory;
insert into t1 values('aaa');
insert into t2 values('aaa');
insert into t3 values('aaa');
insert into t4 values('aaa');
数据类型
数字类型
整型：年龄，身份证号，ID号，等级
tinyint
int
bigint
浮点型：薪资，身高体重，体质参数
float
double
decimal
字符类型
名字，描述性的信息
char
varchar
时间类型
date:2017-01-27
time:11:11:11
datetime:2017-01-27 11:11:11
timestamp:2017-01-27 11:11:11
year:2017
枚举类型
enum('male','female','mf')
set('play','read','sleep',)
=========================整型验证=======================
create database db3;
use db3;
#整型默认是有符号
create table t1(id int(1));
insert into t1 values(-1);
insert into t1 values(2147483647);
insert into t1 values(2147483648); #报错
指定无符号
create table t2(id int(1) unsigned);
insert into t2 values(-1);
insert into t2 values(4294967295);
insert into t2 values(4294967296);
#整型的宽度指的是显示宽度，不是存储宽度
create table t3(id int(10));
insert into t3 values(2147483647);
insert into t3 values(2147483648); #报错
alter table t3 modify id int(10) zerofill;
mysql> select * from t3;
+------------+
| id |
+------------+
| 2147483647 |
| 0000000001 |
+------------+
2 rows in set (0.00 sec)
alter table t3 modify id int(1) zerofill;
mysql> select * from t3;
+------------+
| id |
+------------+
| 2147483647 |
| 1 |
+------------+
2 rows in set (0.00 sec)
create table t4(id int);
=========================浮点验证=======================
create table t5(salary float(255,30));
create table t6(salary double(255,30));
create table t7(salary decimal(65,30));
insert into t5 values(1.1111111111111111111111111111111);
insert into t6 values(1.1111111111111111111111111111111);
insert into t7 values(1.1111111111111111111111111111111);
mysql> select * from t5;
+----------------------------------+
| salary |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t6;
+----------------------------------+
| salary |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t7;
+----------------------------------+
| salary |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)
=========================字符类型验证=======================
char(5)
a |aa |aaa |
varchar(5)
1bytes+a|1bytes+aa|1bytes+aaa
create table t8(x char(5));
create table t9(x varchar(5));
insert into t8 values('a'); #'a '
insert into t9 values('a'); #'a'
select char_length(x) from t8; #'a '
select char_length(x) from t9; #'a'
mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
mysql> select char_length(x) from t8; #'a '
+----------------+
| char_length(x) |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
mysql> select char_length(x) from t9; #'a '
+----------------+
| char_length(x) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
select * from t8 where x='a ';
select * from t8 where x='a';
select * from t8 where x like 'a ';
=========================日期类型验证=======================
时间类型
date:2017-01-27
time:11:11:11
datetime:2017-01-27 11:11:11
timestamp:2017-01-27 11:11:11
year:2017
create table student(
id int,
name char(16),
birth date,
class_time time,
reg_time datetime,
born_year year
);
insert into student values
(1,'egon',now(),now(),now(),now());
insert into student values
(2,'alex','2017/01/02','11:11:11','2017-01-02 11:11:11',1973);
=========================枚举类型验证=======================
约束条件
#not null default
create table t10(name char(16) not null default "aaaa");
insert into t10 values();
#key
#一张表只能有一个？
#为何一张innodb表必须有一个主键？
#干什么用？
#primary key：not null unique
create table t13(
id int not null unique,
name char(10) not null unique
);
create table t14(
id int primary key,
name char(10) not null unique
);
#unique:唯一
create table t11(id int unique,name char(16));
insert into t11 values
(1,'alex'),
(1,'egon');
create table t12(
id int,
name char(16),
unique(id)
);
insert into t12 values
(1,'alex'),
(1,'egon');
#联合唯一
联合主键
联合唯一
create table service(
id int primary key auto_increment,
host char(32),
port int,
unique(host,port)
);
insert into service(host,port) values
('1.1.1.1',3306),
('1.1.1.2',3306);
#foreign key
#index
#auto_increment
create table t15(id int primary key auto_increment);
set global auto_increment_offset=3; #初始的偏移量为2
set global auto_increment_increment=4; #步长为2
create table t16(id int primary key auto_increment);
insert into t16 values
(),
(),
();
set global auto_increment_offset=1; #初始的偏移量为2
set global auto_increment_increment=1; #步长为2
表关系
#多对一
#先建被关联的表
create table dep(
id int primary key auto_increment,
name char(64)
);
#再建。、。。
create table emp(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
#先插部门表
insert into dep(name) values
('IT'),
('HR'),
('SALE'),
('OPERATION')
;
#再插。。。。
insert into emp(name,dep_id) values
('egon',1),
('alex',1),
('mingyue',2),
('waiawi',3),
('dingding',3),
('yaya',3),
('xingxing',3),
('yuyu',3);
delete from emp where dep_id=1;
delete from dep where id=1;
#多对多
create table book(
id int primary key auto_increment,
name char(32),
type char(64)
);
create table author(
id int primary key auto_increment,
name char(32)
);
create table author2book(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on delete cascade
on update cascade,
foreign key(author_id) references author(id)
on delete cascade
on update cascade
);
#一对一
单表查询的语法
#1、单表查询语法
select distinct 字段1,字段2,... from 表
where 约束条件
group by 分组字段
having 过滤条件
order by 排序字段
limit 显示条数
;
#2、基本查询
select * from emp;
select id from emp;
select id,name from emp;
select name as 姓名,salary*12 as 年薪 from emp;
select distinct post from emp;
select concat('姓名: ',name,' ','年薪',salary*12) from emp;
select concat_ws(':',name,salary*12) from emp;
#3、约束条件where
select * from emp where post='teacher';
select * from emp where post='teacher' and salary > 8000;
select * from emp where post='teacher' and salary > 8300 and salary < 10000;
select * from emp where salary between 8300 and 10000;
select * from emp where salary in (8300,10000,12000);
select * from emp where salary not in (8300,10000,12000);
select * from emp where name like '程咬_';
select * from emp where name like '程__';
select * from emp where name like '__';
select * from emp where name like '程%';
select * from emp where name regexp '^程.*$';
select * from emp where post_comment is null;
select * from emp where post_comment is not null;
#4、分组group by
select post,max(salary) from emp group by post;
select post,min(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select post,sum(salary) from emp group by post;
select post,count(id) from emp group by post;
select sex,count(id) from emp group by sex;
select post,group_concat(name) from emp group by post;
select post,max(salary) from emp where age > 20 group by post;
select max(salary) from emp;
select count(id) from emp;
select * from emp where id > 1;
select count(id) from emp;
#平均工资超过10000的部门
select post,avg(salary) as avg_num from emp group by post having avg(salary) > 150000;
select post,avg(salary) as avg_num from emp group by post having avg(salary) > 1000;
select post,avg(salary) as avg_num from emp group by post having avg(salary) > 1000
order by avg(salary) asc;
select post,avg(salary) as avg_num from emp group by post having avg(salary) > 1000
order by avg(salary) desc;
select post,avg(salary) as avg_num from emp group by post having avg(salary) > 1000
order by avg_num desc;
select * from emp order by age,salary desc;
select * from emp order by id desc limit 3;
select * from emp limit 0,20;
select * from emp limit 20,20;
select * from emp limit 40,20;