Python与数据库

一：数据库基础：

数据库：存储数据的仓库

sql及其规范：SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言

登录mysql：mysql -u用户名 -p密码
    -mysql -h 127.0.0.1 -P 3306 -uroot -p123456;
    
    
退出：exit；quit；

sql规范：

    sql语句不区分大小写，但数据常量区分大小写，建议命令大写，表库名小写；
    
    sql语句可单行多行抒写，以;结尾,关键词不能跨多行或简写
    
    用空格和缩进来提高语句的可读性，子句通常位于独立行，便于编辑，提高可读性
    
    注释：-- /*....*/
  
数据库操作(DDL)的语句：
    show databases; 查看mysql管理的数据库
    drop database 数据库名      删除指定数据库
    create database 数据库名 [if not exists]  数据库名      创建数据库
    show create database oldboy;  查看数据库创建时的的配置信息
    
    alter database oldboy1 character set utf-8; 修改数据库编码
    
    create database if not exists oldboy character set gbk;设置数据库的字符集
    
    切换数据库 use db_name; 注意：进入到某个数据库过后，没办法在退回之前状态，但可以通过use进行切换
    select database() 查看当前使用的数据库
    

数据表操作：
    
    字段操作
    非空且唯一 not NULL unique      
    进入目标数据库(use database) 创建数据表CREATE TABLE employee
    (
        id INT PRIMARY KEY auto_increment,设置主键不能为空，主键自增（一张表就一个主键）
        name VARCHAR(25),
        gender boolean,会自动转换为TINYINT
        age TINYINT,
        department VARCHAR(20),
        salary DOUBLE(7,2)
        #表的创建，字段的设置
    );
    
    查看当前数据库的数据表 show tables
    查看当前数据表的字段信息 desc employee
    将当前数据表的字段转换为mysql语句  show create table employee
    
    
    #1.修改表结构  alter
    为数据表添加一条字段：alter table employee add is_married tinyint(1)
     alter table employee add entry_date date [约束字段]not null;
     
     
     alter table employee add A int, ADD B VARCHAR(20);添加多个字段用逗号隔开
      
     alter table employee drop B; 删除字段B
     
     alter table employee convert to character set utf8;修改表的编码格式
     
    #2.修改--列类型  modify 
        
         unique 字段唯一不能重值
         #modify 修改字段类型 after放在某个字段的后面
         alter table employee modify age int(10) not null default 18 after id;
     
     
    #3.修改--列名 change
            -----修改列名需要指定列的类型
         alter table employee change department depart varchar(20) after salary
        
     
    #4.修改表名 rename
     
        rename table employee to emp;
        
        
    #5.删除表 drop table tab_name
     
    

表记录操作：增-删-查-改
    
    #1.增加一条数据：insert into emp (id,age,name,gender,salary,depart) value (1,20,"alex","0",5500.1,"运维部");
    #插入多条
        INSERT INTO emp (name,salary,depart) VALUES ("唐","3500","开发部"),("xiao_yu",5000,"python")
    
                                                ,("wang_jun",8000.02,"运营部");
    #直接插入
        INSERT INTO emp VALUES (5,"雨雨",0,8328.88,"管理部")，要与列名一一对应
            
    #set插入
        INSERT INTO emp set name="丹丹",age=26;

    
    
    #2.修改表记录 update tab_name set field1=value1 field2=value2 where id=1;
        
         update emp set salary =salary+20000 where id=2;
    
    
    
    
    查询当前表里面的所有记录 select * from emp;
    
    
    #3.删除表记录
        DELETE FROM emp;    删除所有记录（一条一条的删除），返回操作条数
         
        DELETE FROM emp where id=5 or id=3; 删除表中id为5和id为3的记录
        
        truncate table emp_new; 使用truncate删除表中的所有记录
        (删除表，创建新表)，不返回操作条数
        
    
    #4.查询表记录  select    [distinct] * from table_name
        ---from指定从那张表筛选，*表示查找所有列，可以指定一个列----
        ----表明确知道要查找的列，distinct用来删除重复行---
        
        select filed1,filed2 from table_name;
        
        #限制重复字段
        select distinc filed1,filed2 from emp;
        
        
        #别名使用
        select filed1 as name,filed2 as age from table_name;
        
        
        #使用where子句进行过滤查询
        select filed1,filed2 from table_name where filed2>80;
        != > < >=  <=  <>
        
        select filed1,filed2 from table_name where filed2 between 88 and 100;
        
        select filed1,filed2 from table_name where filed2 in (88,100,200);
        
        
        select filed1,filed2 from table_name where name like "y%";
        select filed1,filed2 from table_name where name like "y_____";
        '''
            pattern  可以是%或者_,
            如果是%则表示任意多字符，
            如果是_则表示一个字符，
        '''
        
        逻辑运算符  and or null
        
        select filed1,filed2 from table_name where name="xxx" and age=xxxx;
        
        select filed1,filed2 from table_name where filed2 is null ;为空
        
        
        order by 指定排序的列，排序的列及可是表中的列名，也可以说是select语句后指定的别名
        select filed1,filed2 from table_name where order by filed1 desc; 排序
        
        
        重点：Select  from  where  group by  having   order by
        
        mysql在执行sql语句时的执行顺序：  from  where  Select  group by  having   order by
        
        
        group by  分组查询：
            --注意：按分组条件分组后每一组只显示第一条记录
            ‘’‘where是分组之前进行过滤，having是group by(先分组) 之后进行过滤’‘’
            --group by子句，其后可以接多个列名，也可以跟having子句，对group by结果进行筛选
            
            #sum聚合函数
            select name,sum(Django) from oldboy1 group by name having sum(Django)>80;
             select name,sum(JS) from employee group by name;
            
            /*
            having 和 where 两者都可以对查询结果进行进一步的过滤，差别有：
                <1>where 语句只能在分组之前的筛选，having可以用在分组之后的筛选
                <2>使用where语句的地方都可以使用haveing进行替换
                <3>having可以使用聚合函数 where不可以
            
            */
            
        
        聚合函数：先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可，
                --一般和分组查询配合使用
                
                select sum(js) from emp;
                
                
        #使用正则表达式
        select * from emp where emp_name REGEXP "^yu"
            
        
        
 #外键
CREATE TABLE oldboy.classCharger(
    #直接指定库下面的一张表
    id TINYINT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    age INT(4),
    is_married boolean
);

INSERT INTO classCharger (name,age,is_married) VALUES ("tang",20,0),("wang",26,0),("li",30,1);

#外键
CREATE TABLE Student3(
    id TINYINT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    charger_id TINYINT,
    FOREIGN KEY (charger_id) REFERENCES classcharger(id)
    --切记 #关联外键  做为外键一定要和关联的主键的数据类型保持一致  有foreing key 的为子表

)ENGINE=INNODB;

INSERT INTO Student3 (name,charger_id) VALUES ("张",3),("朴",3);


#增加外键
ALTER TABLE Student ADD CONSTRAINT abc FOREIGN KEY (charger_id) REFERENCES classcharger(id);

#删除外键
ALTER TABLE Student DROP FOREIGN KEY abc;
       

#INNODB支持的on语句
----外键对子表的含义：如果在父表中找不到候选键，则不允许在字表进行insert/update

----外键约束对父表的含义: 在父表上进行update和delete以更新和删除在子表中有一条或多条对应匹配行的候选键时，
                        父表的行为取决于：在定义字表的外键时指定的 on update/on delete 子句
                        

----------innodb支持的四种方式---------  
                     
---------- cascade方式：在父表上update/delete 记录时，同步update/delete 掉的子表的匹配记录
             外键的级联删除：如果父表中的纪录被删除那么字表中的纪录自动被删除             
             FOREIGN KEY (charger_id) REFERENCES classcharger (id) ON DELETE cascade ;



---------- set null 方式在父表上update/delete记录时，将子表上匹配的记录的列设为null
        --要注意子表的外键不能为not null



#多表查询
     连接查询：
     ‘’‘
        create table A(id int primary key,name varchar(20));
        
        create table B(id int primary key,name varchar(20),A_id int);
        
        insert into A values (1,"tang");
        insert into A values (2,"yuan");
        insert into A values (3,"wang");
        
        insert into B values(1,"张",1);
        insert into B values(2,"王",2);
        insert into B values(3,"李",3);
        
        select * from a,b where a.id=b.A_id; 内连接查询
        select A.id,a.name,b.name from a,b where a.id=b.A_id;
          
     ’‘’
     
            内连接：inner join 
            select * from b inner join a on b.a_id=a.id and xxxx;
            
            
                            
            外连接：left join: 在内连接的基础上增加左边有的右边没有的结果；   right join:在内连接的基础上增加右边有的左边没有的结果；                   
            select a.name,b.name  from b left join a on b.a_id=a.id;
            
            
            全连接：full join
      
      
      
#多表查询之复合条件连接查询#多表查询之复合条件连接查询

SELECT DISTINCT  department.dept_name FROM employee,department WHERE employee.dept_id=department.dept_id
                                                        and employee.age>30;
'''DISTINCT:去重 unique：唯一'''


#多表查询之子查询
    --子查询是将一个查询语句嵌套在另一个查询语句中
    --内层查询语句的结果，可以为外层查询语句提供查询条件
    --子查询中可以包含：IN NOT ANY ALL EXISTS 和 NOT EXISTS等关键字
    --还可以包含比较运算符：= , !=,< ,>等

    ----1.带in关键字的子查询，查询employee表，但dept_id必须在department表中出现过
            select * from emplyoee where dept_id in (select dept_id * from department);


#索引  索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高。PRIMARY KEY--主键索引   UNIQUE

     CREATE TABLE test1 (
                id int PRIMARY KEY AUTO_INCREMENT,
                name  VARCHAR (20),
                resume VARCHAR (255)
                        );

     INSERT INTO test1 (name,resume) values ("alex","111"),("tang","2222"),("zhang","33333");

     给name加上索引
     alter table test1 modify name varchar(20) unique; #unique 唯一索引

     创建普通索引
     CREATE TABLE emp2(
                id int,
                name VARCHAR (20),
                index  index_name (name)#添加索引
                KEY index_name (name)#添加索引
                    );

      创建唯一索引
      CREATE TABLE emp3(
                id int,
                name varchar (30),
                 unique index index_emp (name)
                    );

      创建多列索引
      CREATE TABLE emp4(
                id INT,
                name VARCHAR (30),
                resume varchar (50),
                index index_name(name,resume)
                )

      添加索引
      CREATE TABLE t2 ADD INDEX index_name (id);

      CREATE  INDEX index_name on t2 (id);


      删除索引
      DROP INDEX index_name on t2;




二：python关于数据库的API---pymysql

import pymysql

#连接数据库
conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",passwd='123456',db="oldboy")

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
sql="create table tang(id int (10),name varchar(20))"

#cursor.execute(sql)
ret=cursor.execute("insert into tang values (1,'alex'),(2,'yuan')")
#print(ret)#返回影响的行数

# ret2=cursor.execute("select * from tang;")
# print(ret2)
#
# # print(cursor.fetchone())   查询数据  cursor游标标记当前查询位置
# # print(cursor.fetchall())
# print(cursor.fetchmany(2))
#
# cursor.scroll(1,mode="relative")#设置游标所在的位置


#conn.commit()
# cursor.close()
# conn.close()


'''
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
  
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
  
  
# 提交，不然无法保存新建或者修改的数据
conn.commit()
  
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
3、获取新创建数据自增ID
可以获取到最新自增的ID，也就是最后插入的一条数据ID

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()

'''