Python学习（七）

一、MySQL基础

show databases;   查看所有数据库

use db_name;   用那个数据库

show tables;  查看数据库下的所有表

decs table_name;  查看表下的所有字段   等同于    show columns from table_name;

select * from tabel_name;            select * from tabel_name\G;    查看表中的所有数据  

create database  db_name charset utf8;   创建一个数据库

show index from table_name;   查看表的详细索引信息

进入一个数据库后use db_name，在这个数据库下创建表

create table table_name(column_name1  column_type1, column_name2  column_type2);

insert into table_name (column_name1, column_name2) values(value1,value2);  插入一条数据

select  column_name1,column_name2  from table_name where 条件 limit 数据条数 offset 偏移量;  查询表中数据，条件可为 column_name =、> 或者 like 

updat table_name set column_name=value where 条件;  修改数据

delete from table_name where 条件;  删除数据

select column_name from table_name order by column_name desc;  按升序排列查询数据

select column_name,function(column_name ) from table_name where 条件 group by column_name with rollup;   将表按column_name 进行分组，并根据function、column_name 做出选择，例如count、sum，with rollup做出总的统计

alter table table_name add column_name column_type;  为表添加字段

alter table table_name drop column_name;  删除表的字段

alter table table_name modify column_name 旧的column_type 新的column_type;    修改表的字段类型

alter table table_name change 旧的column_name 新的column_name column_type;    可以修改表的字段名称、类型

 外键，一个特殊的索引，用于关键2个表，只能是指定内容

mysql> create table class(

     -> id  int not null primary key ,

     -> name char (16));

Query OK, 0 rows affected (0.02 sec)

 

CREATE TABLE `student2` (

   `id` int (11) NOT NULL ,

   ` name ` char (16) NOT NULL ,

   `class_id` int (11) NOT NULL ,

   PRIMARY KEY (`id`),

   KEY `fk_class_key` (`class_id`),

   CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)

);

mysql> insert into student2(id,name,class_id) values(1,'alex', 1);

此时如果class 表中不存在的id,student2表也插入不了相应的class_id，这就叫外键约束

如果有student2表中跟这个class表有关联的数据，不能删除class表中与其关联的纪录，但可以删除表student2中的数据

MySQL NULL 值处理　　

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

Mysql 连接（left join, right join, inner join ,full join）

我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例如数据库中的两个表A和B，其字段分别为a和b

 

A    B

a     b

1    3

2    4

3    5

4    6

 

Inner join

select * from  A INNER JOIN  B on A.a = B.b;

a | b

--+--

3 | 3

4 | 4

 

Left join

select * from  A  LEFT JOIN  B on A.a = B.b

a |  b

--+-----

1 | null

2 | null

3 |  3

4 |  4

 

Right join

select * from  A  RIGHT JOIN  B on A.a = B.b

a   |  b

-----+----

3   |  3

4   |  4

null  |  5

null |  6

 

Full join

mysql 并不直接支持full join,but 总是难不到我们

select * from  A  LEFT JOIN  B on A.a = B.b UNION select * from  A RIGHT JOIN B on A.a = B.b

| a   | b  |

+ ------+------+

|  3 |  3 |

|  4 |  4 |

|  1 | NULL |

|  2 | NULL |

| NULL |  5 |

| NULL |  6 |

6. 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

mysql> begin ； ＃开始一个事务

mysql> insert into a (a) values (555);  # 执行命令

mysql> rollback ;  # 若数据有问题，回滚 ， 这样数据是不会写入的

如果上面的数据没问题，就输入commit提交命令就行

7.索引　　

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

查看一张表的索引

show index from table_name\G;

一张表的主键默认为一个索引，对于一张生成好的表，将其他字段创建为索引的方法

CREATE INDEX index_Name ON table_name(column_name(length));

也可以在创建表的时候直接指定索引，例如

CREATE TABLE table_name(    

ID INT NOT NULL ,   

username VARCHAR (16) NOT NULL ,  

INDEX [index_Name] (username(length))  

);

删除索引的语法

DROP INDEX [indexName] ON table_name;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

CREATE UNIQUE INDEX index_Name ON table_name(column_name(length));

创建表的时候直接指定

CREATE TABLE table_name( 

ID INT NOT NULL ,  

username VARCHAR (16) NOT NULL , 

UNIQUE [indexName] (column_name(length)) 

);

二、python操作mysql

python通过pymysql模块，与mysql相连，mysql操作语句同上

# !usr/bin/env python
# -*- coding: utf-8 -*-

import pymysql

# 创建链接
conn_sql = pymysql.connect(host='localhost',port=3306,user='root',passwd='123456',db='test_sql')
# 创建游标（光标）
sql_cursor = conn_sql.cursor()
# 执行SQL，并返回受影响行数
effect_row = sql_cursor.execute("select * from student")  # 返回符合结果的数据条数
# effect_row = sql_cursor.execute("insert into student (name,age,register_date) values ('zhang',27,'2017-08-26')")

print(sql_cursor.fetchone())  # 取出一条数据
print(sql_cursor.fetchmany(2))  # 取出n条数据
print('---------------------')
print(sql_cursor.fetchall())  # 取出所有（或剩余的）所有数据

# data = [('li',27,'2017-06-28'),
#         ('zhang',27,'2017-06-28')]
#effect_row = cursor.executemany("insert into student(name,age,register_date)values(%s,%s)",data)  #  执行多条数据

# 提交，不然无法保存新建或者修改的数据
conn_sql.commit()

# 关闭游标
sql_cursor.close()

# 关闭连接
conn_sql.close()

1. ORM介绍

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言，在Python中，最有名的ORM框架是SQLAlchemy。

利用join多表查询时，需要有外键关联

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import func

# 生成表
engine_test = create_engine("mysql+pymysql://root:123456@localhost/test_sql",  # mysql+pymysql://用户名:密码@主机/数据库名
                            encoding='utf-8',
                            echo=False) # echo=True会打印出底层sql语句的信息
Base = declarative_base()  # 生成orm的基类

class Student_db(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)
    def __repr__(self):
        return "id:%s name:%s register_date:%s" %(self.id,self.name,self.register_date)

class StudyRecord_db(Base):
    __tablename__ = 'studyrecord'
    id = Column(Integer, primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    std_id = Column(Integer,ForeignKey('student.id'))  # 创建一个外键关联
    student_to = relationship('Student_db',backref='study_record')  # 通过这个student_to关系，可以访问关联的student表中的数据，
                                                                        # 也可以在student表中，通过study_record关系访问studyrecord表中的数据
    def __repr__(self):
        return "id:%s day:%s status:%s" %(self.id,self.day,self.status)

Base.metadata.create_all(engine_test) # 创建表结构
session_class = sessionmaker(bind=engine_test)  # 创建与数据库的会话session_class，这里返回的是一个类，不是实例
session = session_class() # 实例化对象
# s1 = Student_db(name='zhu',register_date='2017-08-28')
# s2 = Student_db(name='shan',register_date='2015-07-27')
# s3 = Student_db(name='wei',register_date='2014-05-22')
# s4 = Student_db(name='feng',register_date='2016-06-21')
# s5 = Student_db(name='jin',register_date='2015-02-23')
# r1 = StudyRecord_db(day=1,status='yes',std_id=1)
# r2 = StudyRecord_db(day=2,status='no',std_id=1)
# r3 = StudyRecord_db(day=3,status='yes',std_id=1)
# r4 = StudyRecord_db(day=1,status='yes',std_id=4)
# r5 = StudyRecord_db(day=2,status='no',std_id=4)
# session.add_all([s1,s2,s3,s4,s5,r1,r2,r3,r4,r5])  # 把要创建的数据对象一次性放入session中

# 查询数据
stu1 = session.query(Student_db).filter_by(id=1).all()  # filter_by可以用等号查询，filter可以用不等号查询
stu2 = session.query(Student_db).filter(Student_db.id>3).all() # 只有first()、all()，没有last()
stu3 = session.query(Student_db).filter(Student_db.id>3).filter(Student_db.id<5).all()  # 多条件查询
stu4 = session.query(Student_db).filter(Student_db.name.in_(['zhushanwei','feng'])).all()
stu5 = session.query(Student_db).join(StudyRecord_db).all() # 连表查询
print('stu1: ',stu1)
print('stu2: ',stu2)
print('stu3: ',stu3)
print('stu4: ',stu4)
print('stu5: ',stu5)
stu_obj = session.query(Student_db).filter(Student_db.name=='zhushanwei').first()
rec_obj = session.query(StudyRecord_db).filter(StudyRecord_db.id==1).first()
print(stu_obj)
print(rec_obj)
print('----------------')
print(stu_obj.study_record) # 关系访问时，stu_obj=session.的函数后面查询结果要是first(),all()不行
print('----------------')
print(rec_obj.student_to)

# # 修改数据
# stu_obj.name = 'zhushanwei'
# stu_obj.register_date = '2017-08-26'
session.commit() # 提交数据，回滚session.rollback()

# 统计、分组数据
print(session.query(Student_db).filter(Student_db.name.like('zh%')).count()) # 统计个数
print(session.query(Student_db.name,func.count(Student_db.name)).group_by(Student_db.name).all()) # 分组统计个数，func还有sum、min、max等

# 删除数据
# session.delete(stu_obj) # 删除数据需要session.first()，若为all()需要循环删除
# session.query(Student_db).filter(Student_db.id>3).delete() # 也可以用这种方法
# session.commit()

有时一张表需要关联多个表，或者一张表需要更多的关联另一张表，这就需要多个外键关联

# 生成数据表部分
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column,Integer,ForeignKey,String

engine_test = create_engine("mysql+pymysql://root:123456@localhost/test_sql",
                            encoding='utf-8',
                            echo=False)
Base = declarative_base()

class Address_db(Base):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))
    def __repr__(self):
        return "id:%s street:%s city:%s state:%s" %(self.id,self.street,self.city,self.state)

class Customer_db(Base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    billing_address_id = Column(Integer,ForeignKey('address.id'))
    shipping_address_id = Column(Integer,ForeignKey('address.id'))

    billing_address = relationship('Address_db',foreign_keys=[billing_address_id])
    shipping_address = relationship('Address_db',foreign_keys=[shipping_address_id]) # 这样sqlachemy就能分清哪个外键是对应哪个字段了
    def __repr__(self):
        return "id:%s name:%s" %(self.id,self.name)

Base.metadata.create_all(engine_test)

# 插入数据，查询数据部分
import mysql_2
from sqlalchemy.orm import sessionmaker

session_class = sessionmaker(bind=mysql_2.engine_test)
session = session_class()

# addr1 = mysql_2.Address_db(street='chenggong',city='kunming',state='yunnan')
# addr2 = mysql_2.Address_db(street='sanlitun',city='anyang',state='henan')
# addr3 = mysql_2.Address_db(street='zhuli',city='shangcai',state='henan')
# session.add_all([addr1,addr2,addr3])
#
# cust1 = mysql_2.Customer_db(name='zhushanwei',billing_address_id=3,shipping_address_id=1)
# cust2 = mysql_2.Customer_db(name='wangpanpan',billing_address_id=2,shipping_address_id=1)
# cust3 = mysql_2.Customer_db(name='fengjin',billing_address_id=1,shipping_address_id=1)
# session.add_all([cust1,cust2,cust3])

addr_obj1 = session.query(mysql_2.Customer_db).filter(mysql_2.Customer_db.name=='zhushanwei').first()
print(addr_obj1)
print('---------------------')
print(addr_obj1.billing_address)
print('---------------------')
print(addr_obj1.shipping_address)

session.commit()

以上是一张表对应多个表的外键关联，但还有多张表对多张表的外键关联，即多对多的外键关联，例如“图书”与“作者”的关系的表结构，一本书可以有好几个作者一起出版，一个作者可以写好几本书

# 生成表部分，要先创建数据库
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine_test = create_engine("mysql+pymysql://root:123456@localhost/test_sql?charset=utf8",  #加入?charset=utf8支持中文输入
                            encoding='utf-8',
                            echo=False) # echo=True会打印出底层sql语句的信息
Base = declarative_base()  # 生成orm的基类

BookAuthor_db = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )  # 外键关联表，不需要用户自己插入数据，只是用来映射、关联表，不用类来创建

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=BookAuthor_db,backref='books') # 由于books表没有直接关联authors表，
                                                                               # 所以通过secondary=BookAuthor_db，用第三张表来间接关联
    def __repr__(self):
        return "book_name:%s pub_date:%s" %(self.name,self.pub_date)

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    def __repr__(self):
        return "author_name:%s" %self.name

Base.metadata.create_all(engine_test)

# 数据插入、查询、修改部分
import mysql_2
from sqlalchemy.orm import sessionmaker

session_class = sessionmaker(bind=mysql_2.engine_test)
session = session_class()

# 添加数据
# b1 = mysql_2.Book(name="飘渺之旅",pub_date='2010-05-21')
# b2 = mysql_2.Book(name="史上第一混乱",pub_date='2011-04-22')
# b3 = mysql_2.Book(name="修真聊天群",pub_date='2016-06-21')
# b4 = mysql_2.Book(name="我就是妖怪",pub_date='2013-05-25')
# a1 = mysql_2.Author(name="萧潜")
# a2 = mysql_2.Author(name="张小花")
# a3 = mysql_2.Author(name="奶骑")
# a4 = mysql_2.Author(name='朱善玮')
#
# b1.authors = [a1,a4]
# b2.authors = [a2]
# b3.authors = [a3,a4]
# b4.authors = [a2]
#
# session.add_all([b1, b2, b3, b4, a1, a2, a3, a4])

# 查找数据
book_obj = session.query(mysql_2.Book).filter(mysql_2.Book.name=='修真聊天群').first()
print(book_obj)
print(book_obj.authors)
author_obj = session.query(mysql_2.Author).filter(mysql_2.Author.name=='张小花').first()
print(author_obj)
print(author_obj.books)

# 删除数据
book_obj1 = session.query(mysql_2.Book).filter(mysql_2.Book.name=='我就是妖怪').first()
session.delete(book_obj1)
author_obj1 = session.query(mysql_2.Author).filter(mysql_2.Author.name=='朱善玮').first()
book_obj.authors.remove(author_obj1)  #从一本书中删除一个作者，删除作者时，会把这个作者跟所有书的关联关系数据也自动删除
session.commit()

 三、堡垒机

 

转载于:https://www.cnblogs.com/zsw900610/p/7436607.html