day11-数据库开发

内容回顾

# 下次上课时间 : 10.13号
# 直播时间 9.24 9.26
# 中间还会安排两次直播 10.9\10.10

# 代码发布系统的需求采集\自动化运维需求
# 回顾的时间 后面会逐渐减少
    # 每周四 直播里 告诉大家 你这周来上课需要掌握什么

# python基础阶段已经完整的学完了
    # 基础数据类型
    # 流程控制 if for while
    # 文件操作
    # 函数基础
    # 函数的进阶(变量的命名空间和作用域 装饰器\迭代器\生成器\生成器表达式\列表推导式)
    # 内置函数
    # 常用模块(re os sys time datetime random hashlib logging json pickle)
    # 自定义模块
    # 模块的导入,什么是包
    # 软件的开发规范
    # 面向对象(基础语法 单继承多继承)
    # 面向对象进阶(多态 私有的 property classmethod staticmethod 内置方法)
    # 反射
    # 网络编程 : 概念\协议 掌握最基础的socket cs端代码就可以
    # 并发编程 : 概念
# 评估一下自己的开发水平
    # 能够完成需求
    # 面向过程/函数/对象编程
    # 能够独立的完成哪个作业? 前面的作业\计算器\选课系统\ftp

# 复习
# 并发编程(让一个程序能同时做多件事情)
# 进程 : 开销非常大 是计算机中资源分配的最小单位(内存隔离) 能利用多个CPU 由操作系统控制 同时操作内存之外的数据会产生数据的不安全
# 线程 : 开销非常小 是操作系统可以调度的最小单位(内存共享) 能利用多个CPU 由操作系统控制 同时操作全局内存中的数据会产生数据的不安全
    # Cpython解释器下 由于GIL(全局解释器锁)的问题导致了一个进程中的多个线程无法利用多核
    # 数据不安全 += -= *= /=  多个线程同时操作全局/内存外部的变量 需要自己加锁,在+= 操作前后添加lock.acquire()和lock.release()即可
    # append pop extend 你能想到的基础数据类型自带的方法都是数据安全的
# 协程 : 开销几乎为0 不是由操作系统控制的,是由代码控制的.本质是单线程的,不能利用多个CPU 也会产生数据不安全
    # gevent模块 第三方模块 他只能识别有限的IO操作 socket time.sleep

# from threading import Thread,Lock
# n = 0
# def add():
#     global n
#     for i in range(2000000):
#         lock.acquire()
#         n +=1
#         tmp = n
#         n = tmp+1
#         lock.release()

# def sub():
#     global n
#     for i in range(2000000):
#         lock.acquire()
#         n -=1
#         lock.release()

# lock = Lock()
# t1 = Thread(target=add)
# t2 = Thread(target=sub)
# t1.start()
# t2.start()
# t1.join()
# t2.join()
# print(n)

# 并发方面的视频

# 初识数据库

今日内容

# mysql数据库
# 存储引擎
    # innodb 默认的存储方式(存储引擎)
        # 支持事务 外键 行级锁 表级锁
        # 存储为两个文件:一个用来存表结构 一个用来存数据
    # myisam
        # 支持表级锁
# 基础的数据类型
    # int tinyint float(m,n)
    # char(n) varchar(n)
    # datetime date time
    # enum('选项1','选项2') set('选项'...)
# 约束
# create table student6(id int unique auto_increment,name char(12) not null unique,phone char(11) unique,gender enum('male','female') default 'male');
# create table student7(name char(12) not null unique,id int unique auto_increment,phone char(11) unique,gender enum('male','female') default 'male');
# id name phone gender
    # 非空 not null
    # 默认 default
    # 唯一 unique
    # 自增 auto_increment = unique + not null + 自增效果
        # 1.依赖于唯一约束的
        # 2.自带一个not null约束
    # 主键 primary key = 第一个(非空 + 唯一)=这张表的主键
        # pri == primary key 主键
        # 每张表只能有一个主键
        # create table student8(name char(12) not null unique,id int primary key auto_increment,phone char(11) unique,gender enum('male','female') default 'male')
    # 联合唯一(常用)\联合主键(不常用)
        # create table server_info2(id int primary key auto_increment, name char(20), ip char(15) not null,port int(5) not null,unique(ip,port));
        # create table server_info2(id int unique auto_increment, name char(20), ip char(15),port int(5),primary key(ip,port));
    # 外键 foreign key
        # create table stu2(id int,name char(12),class_id int,foreign key(class_id) references class2(cid));
        # create table class2(cid int primary key,classname char(12));

        # 级联更新
        # create table stu3(id int,name char(12),class_id int,foreign key(class_id) references class3(cid) on update cascade);
        # create table class3(cid int primary key,classname char(12));

# 表的删除 drop table 表名;
# 表的修改 alter table
    # 增加字段 删除字段 修改字段名 修改约束 修改数据类型 修改长度 修改表名
# 表的查看
    # desc 表名;
    # show create table 表名;

# 数据操作
# 增 insert
    # insert into 表名 values(值,值2,值3),(值,值2,值3),...,.;
    # insert into 表名(指定字段名1,指定字段2) values(值1,值2),...,.;
    # insert into 表名 select 语句;   # 把查出来的数据都添加到对应的表名中
        # 表1 : id name age      select * from 表1
        # 表二 : id name gender
            # insert into 表2(id,name) select id,name from 表1;
# 删 delete
    # delete from 表名 where 条件   # 删除所有符合条件的行
# 改 update
    # update 表名 set 字段名=值,字段2=值2 where 条件;

# 查 select
    # 单表查询
    # 多表查询

# 增删改
# 查询
    # 单表查询
    # 多表查询
# 其他知识点 :
    # 数据库的备份 mysqldump
        # 恢复数据 mysql> source xxx.sql
    # 事务
'''
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1 where b = 2 for update;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> update t1 set a=2 where b = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)
'''
# pymysql模块
    #

重要问题

# mysql -u用户名 -p密码 -h主机地址
# mysql> 在这里你什么位置都没有,既不在任何一个数据库中,也不能直接操作某个库的表
# mysql> use 数据库名; # 先要选择你要去的数据库(前提是有这样一个库,如果没有需要先创建)
# mysql> 才能开始创建表或者操作表和数据

# 关于编码
    # 数据库有一些默认的配置
    # 查看默认的配置们
    # mysql> show variables;
    # mysql> show variables like '%charac%';
    # mysql> set character_set_server = utf8;
        # 在内存中临时设置server端默认的编码,重启会失效
        # 也可以直接把character_set_server = utf8;写到my.ini文件中,永久生效

    # create table 如果不指定就是用默认的编码
    # 指定字符编码也可以创建表,编码的设置只对这张表生效
    # create table 表名(字段 数据类型 约束) charset = utf8;

表单查询

# select * from 表;
# 百分之99.9999999的概率是需要从表中筛选数据显示的
# 筛选数据 -> 条件

# select
    # select 后面永远跟着的是列的名字;
    # select 筛选字段的名字来指定你要查询的字段
    # select id,emp_name from employee;
    # 四则运算 select emp_name,salary*12 from employee;
    # 重命名 select emp_name as name,salary*12 from employee;
    # 重命名 select emp_name as name,salary*12 as annual_salary from employee;
    # 重命名 select emp_name name,salary*12 annual_salary from employee;
    # 拼接函数 select concat('abc','|||','cde');
        # select concat(emp_name,':',salary) from employee;
    # 去重 distinct
        # select distinct post from employee;
        # select distinct age,post from employee;

# where条件的筛选 根据值/范围约束
    # 根据数值范围查询 > < >= <= != <> = between a and b
    # select * from employee where age=18
    # select * from employee where age='18'  # 可以但是效率差
        # 年龄小于25的
        # select * from employee where age<25;
        # 薪资大于8000的
        # select * from employee where salary>8000;
        # 薪资在10000到20000之间
        # select * from employee where salary between 10000 and 20000;
    # 根据精准的范围查询 in
        # 25 28 78
        # select * from employee where age in (25,28,78);
    # 模糊查询 - like
        # 通配符 % 表示任意长度任意内容
            # select * from employee where emp_name like '程%'
        # 通配符 _ 表示任意1个字符的任意内容
            # select * from employee where emp_name like '程__'
            # select * from employee where emp_name like '程_'
    # 模糊查询 - 正则查询 regexp
        # select * from employee where emp_name regexp '^程';
# 条件的组合 与或非
    #  and 与
        # 找到年龄在25岁以上的所有老师
        # select * from employee where age>25 and post='teacher';
        # 找到年龄在25岁以上的所有老师的姓名
        # select emp_name from employee where age>25 and post='teacher';
    #  or  或
        # 找到所有的女性或者年龄小于20岁的人
        # select * from employee where sex = 'female' or age<20;
    #  not 非
        # 找到年龄不是18,78岁的人
        # select * from employee where age not in (18,78)
# NULL是mysql中的一个关键字
    # NULL不能用 = !=判断
    # 需要用is /is not判断
# 练习讲解:
# 4. 查看岗位描述不为NULL的员工信息
# select * from employee where post_comment is not null;

# 5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
# select * from employee where salary =9000 or salary =10000 or salary = 30000
# select emp_name,age,salary from employee where salary in (30000,9000,10000) and post = 'teacher'

# 聚合函数
    # avg求平均 sum求和 min求最小 max求最大 count计数
    # select avg(salary) from employee;

# 分组 group by 分组之后数据只和被分组的字段有关系,和具体的每一条数据没有关系
    # select * from employee group by post;
    # 求每个部门的人的平均工资
    # select post,avg(salary) from employee group by post;

# 练习
# 查询岗位名以及岗位包含的所有员工名字
    # select * from employee group by post;
    # select post,group_concat(emp_name) from employee group by post;

# 找到employee表中,各部门年龄大于20的平均薪资是多少
# select post,avg(salary) from employee where age>20 group by post

# HAVING 过滤 总是在分组之后才执行过滤条件
# 求平均年龄大于30岁的部门
# select post,avg(age)  from employee group by post having avg(age)>30
# 求平均薪资大于1w的部门
# select post,avg(salary) from employee group by post having avg(salary)>10000;

# order by 排序
# 将选出来的数据根据某一个字段排序,默认是升序(从小到大),desc是倒序
# select * from 表 order by age;  # 升序
# select * from 表 order by age asc;  # 升序
# select * from 表 order by age desc;  # 降序

# limit 取前n个
# select * from 表 order by age limit 3;
# select * from 表 order by age desc limit 3;

# 从m开始,取n个
# select * from 表 order by age limit 9,3;
    # 是从第10条开始,取3条,第10,11,12条会被取出来
# select * from 表 order by age limit 3 offset 9;
    # 是从第10条开始,取3条,第10,11,12条会被取出来

# select
# distinct
# from
# where
# group by
# having
# order by
# limit
# offset

多表查询

# 什么叫连表
# create table t1(a int,b int);
# insert into t1 values(1,2),(3,4),(5,6);
# create table t2(c int,d int,e int);
# insert into t2 values(1,1,1),(2,2,2),(3,3,3);

'''
# 数据准备
create table department(
id int,
name varchar(20)
) charset = utf8;

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
) charset = utf8;

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
'''

# 连表查询 = 内连接
# select * from 表1,表2 where 连接条件
# select * from emp,department where dep_id = department.id;
# select * from emp,department as dep where dep_id = dep.id;

# select * from 表1 inner join 表2 on 条件
# 内连接 inner join 保留两表中所有匹配条件的数据
# select * from emp inner join department on dep_id = department.id;
# 外连接
    # 左外连接 left join  保留左表中所有数据
    # select * from emp left join department on dep_id = department.id;
    # 右外连接 right join 保留右表中所有数据
    # select * from emp right join department on dep_id = department.id;
    # 全外连接 保留左表右表中所有数据
    # select * from emp left join department on dep_id = department.id
    # union
    # select * from emp right join department on dep_id = department.id;

# 1.基于两张表找到每个人的名字和所属的部门,左外连接
# select emp.name from emp left join department on dep_id = department.id;
# select emp.name,department.name from emp left join department on dep_id = department.id;
# select emp.name,dep.name from emp left join department as dep on dep_id = dep.id;

# 2.找到"技术"部的所有人
# 方法一:
# select * from emp inner join department on dep_id = department.id where department.name='技术';
# 方法二:
# select * from emp,department where dep_id = department.id and department.name='技术';

# 3.以内连接的方式查询employee和department表，
# 并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
# select * from emp inner join department on dep_id = department.id
# select emp.name,department.name from emp inner join department on dep_id = department.id where age>25;

# 4.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
# select emp.name,department.name from emp inner join department on dep_id = department.id

# 子查询
# 1.查询平均年龄在25岁以上的部门名
    # 找到了平均年龄在25岁以上的部门id
        # select dep_id from emp group by dep_id having avg(age)>25;
    # 根据部门id找到部门名
        # select * from department where id in (201,202);
   #  select * from department where id in (select dep_id from emp group by dep_id having avg(age)>25);

# 查看技术部员工姓名
    # select id from department where name = '技术';
    # select * from emp where dep_id = 200;
    # select * from emp where dep_id = (select id from department where name = '技术');

# 当连表和子查询都可以出结果的时候,连表的效率高

# F:\python自动化27期\day11\day27.sql

python操作mysql

import pymysql

# conn = pymysql.connect(host='127.0.0.1', user='root', password="123456",
#                  database='py27_day11')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
# cur = conn.cursor()
# cur.execute('select * from emp')
# print(cur.fetchone())
# print(cur.fetchone())
# print(cur.fetchone())
# print(cur.fetchone())  # 如果没有了就为None

# ret = cur.fetchmany(2)
# print(ret)

# ret = cur.fetchall()
# print(ret)
# cur.close()
# conn.close()

# 修改或者写入数据
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123456",
#                  database='py27_day11')
# cur = conn.cursor()
# cur.execute('insert into t1 values (111,222)')
# conn.commit()  # insert update delete 提交数据才能生效
# cur.close()
# conn.close()

# num1 = input('num1>')
# sql = 'select * from t1 where a=%s and b =%s'
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123456",
#                 database='py27_day11')
# cur = conn.cursor()
# cur.execute(sql,(num1,2))
# print(cur.fetchall())
# cur.close()
# conn.close()

作业

# 代码发布系统的需求采集\自动化运维需求

# 回顾作业 知识点思维导图

# 单表查询的小考 : https://www.cnblogs.com/Eva-J/articles/11074845.html

# 多表查询的作业 : https://www.cnblogs.com/Eva-J/articles/9688383.html
    # 最后综合练习
    # 共40题
        # 必做15题
        # 选做25题