数据库编程

数据库

概念

分类

 

Oracle

 

Mysql

 

去IOE

NoSQL

 

数据库流行度排名

 

 

MYSQL

参考：https://www.cnblogs.com/wyaokai/p/10921323.html
参考：https://www.jianshu.com/p/75187e19faf2

安装

 

SQL语句

DCL

 

DDL

删除用户

创建数据库

删除数据库

创建表

 DESC

 

练习

PRIMARY KEY主键

索引Index

约束Constraint

PRIMARY KEY约束

外键约束Foreign key

视图 

数据类型

MYSQL中的数据类型

关系操作

DML---CRUD增删改查

Insert语句

Update语句

 

Delete语句

Select语句

 

查询

Limit子句

Where子句

Order by子句

DISTINCT不返回重复记录

 

聚合函数

 

分组查询

　

子查询

连接Join

内连接

外连接

 

自连接

事务Transaction

原子性：要求事务中的所有操作不可分割，不能做了一部分操作，还剩下一部分；
一致性：多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线程读写共享资源一样不可预期，就不能保证一致性；
隔离性，就是指多个事务访问共同的数据了，应该互不干扰。隔离性指的是究竟在一个事物处理期间，其他事务能不能访问的问题；
持久性：比较好理解，就是事务提交后，数据不能丢失。

MYSQL隔离级别

 

隔离级别示例可参考：
https://www.cnblogs.com/ubuntu1/p/8999403.html

事务语法

数据仓库和数据库的区别

 

其他概念

游标Cursor

存储过程、触发器

数据库开发

驱动

pymysql使用

$pip install pymysql

创建数据库和表

 

CREATE DATABASE IF NOT EXISTS school;
SHOW DATABASES;
USE school;

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

连接Connect

 

import pymysql

conn = None
try:
    #建立连接
    conn = pymysql.connect('192.168.10.166','wayne','wayne','test')
    print(conn.ping(False))

finally:
    if conn:
        conn.close()

游标Cursor

操作数据库

 

新增记录

import pymysql

conn = None
try:
    #建立连接
    conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
    print(conn.ping(False))
    #获取游标
    cursor = conn.cursor()
    insert_sql = "insert into student (id,name,age) values(10,'tom',20)"
    rows = cursor.execute(insert_sql)
    print(rows)
    cursor.close()

finally:
    if conn:
        conn.close()

事务管理

import pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
    cursor = conn.cursor()
    
    insert_sql = "insert into student (name,age) values('tom',21)"
    rows = cursor.execute(insert_sql)
    print(rows)
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

批量增加数据

 

import pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
    cursor = conn.cursor()
    for i in range(5):
        insert_sql = "insert into student (name,age) values('tom',21)"
        rows = cursor.execute(insert_sql)
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

一般流程

查询

 

import  pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
    cursor = conn.cursor()
    sql = "select * from student"
    rows = cursor.execute(sql)  #返回影响行数
    print(cursor.fetchone())
    print(cursor.fetchmany(2))
    print(cursor.fetchall())
except:
    pass
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

带列名查询

 

import pymysql
from pymysql.cursors import DictCursor

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
cursor = conn.cursor(DictCursor)

sql = "select * from student"
rows = cursor.execute(sql)
print(cursor.fetchone())#{'id': 1, 'name': 'tom', 'age': 20}
print(cursor.fetchall())#[{'id': 2, 'name': 'tom', 'age': 21}]

 

SQL注入攻击

#找出用户id为5的用户信息语句如下：
import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
cursor = conn.cursor()

userid = '5'
sql = 'select * from student where id = {}'.format(userid)
cursor.execute(sql)
print(cursor.fetchall())    #((5, 'zqk', 23),)

#userid可以变，例如从客户端request请求中获取，直接拼接到查询字符串中
#当userid='5 or 1=1'呢？
import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
cursor = conn.cursor()

userid = '5 or 1=1'
sql = 'select * from student where id = {}'.format(userid)
cursor.execute(sql)
print(cursor.fetchall())    #((1, 'tom', 20), (2, 'tom', 21), (3, 'cy', 22), (4, 'xu', 24), (5, 'zqk', 23))
#运行结果返回了数据库全部数据

 

import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
cursor = conn.cursor()

userid = '5 or 1=1'
sql1 = 'SELECT * FROM student WHERE id = %s'
cursor.execute(sql1,(userid,))   #参数化查询
print(cursor.fetchall())
print('~~~~~~~~~~~~~~')
sql2 = "SELECT * FROM student WHERE name like %(name)s and %(age)s"     #参数化查询
cursor.execute(sql2,{'name':'tom','age':19})
print(cursor.fetchall())

if cursor:
    cursor.close()
if conn:
    conn.close()
#运行结果如下：
D:\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1292, "Truncated incorrect DOUBLE value: '5 or 1=1'")
  result = self._query(query)
((5, 'zqk', 23),)
~~~~~~~~~~~~~~
((1, 'tom', 20), (2, 'tom', 21))

参数化查询为什么提高效率？

上下文支持

查看连接类和游标类的源码

#连接类
class Connection(object):
    def __enter__(self):
        """Context manager that returns a Cursor"""
        return self.cursor()
    
    def __exit__(self, exc, value, traceback):
        """On successful exit, commit. On exception, rollback"""
        if exc:
            self.rollback()
        else:
            self.commit()

#游标类
class Cursor(object):
    def __enter__(self):
        """Context manager that returns a Cursor"""
        return self.cursor()
    
    def __exit__(self, exc, value, traceback):
        """On successful exit, commit. On exception, rollback"""
        if exc:
            self.rollback()
        else:
            self.commit()

#使用游标的上下文
import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')
try:
    with conn.cursor() as cursor:
        for i in range(3):
            insert_sql = "insert into student (name,age) values('tom{0}',21+{0})".format(i)
            rows = cursor.execute(insert_sql)
    conn.commit()
    #此时使用这个关闭的cursor，会抛异常
    # sql = "select * from student"
    # cursor.execute(sql)
    # print(cursor.fetchall())
except Exception as e:
    print(e)
    conn.rollback()
finally:
    conn.close()

#使用连接的上下文
import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')

with conn as cursor:
    for i in range(3):
        insert_sql = "insert into student (name,age) values('tom{0}',21+{0})".format(i)
        rows = cursor.execute(insert_sql)
sql = "select * from student"
cursor.execute(sql)
print(cursor.fetchall())

#关闭
cursor.close()
conn.close()

#同时使用连接类和游标类的上下文管理
import pymysql

conn = pymysql.connect('192.168.10.166','wayne','wayne','school')

with conn as cursor:
    with cursor:
        sql = "select * from student"
        cursor.execute(sql)
        print(cursor.fetchall())

#关闭
conn.close()

连接池的实现

from queue import Queue
import pymysql

class ConnPool:
    def __init__(self,size,*args,**kwargs):
        if not isinstance(size,int)or size<1:
            size = 8
        self._pool = Queue(size)
        for i in range(size):
            self._pool.put(pymysql.connect(*args,**kwargs))

    def get_conn(self):
        return self._pool.get() #阻塞

    def return_conn(self,conn):
        self._pool.put(conn)

#使用连接池
pool = ConnPool(4,'192.168.10.166','wayne','wayne','school')

#获取一个连接
conn = pool.get_conn()

with conn as cursor:
    with cursor:
        sql = "SELECT * FROM student"
        cursor.execute(sql)
        print(cursor.fetchone())
        
        sql = "SHOW PROCESSLIST"    #观察连接，权限小只能看自己的
        cursor.execute(sql)
        for x in cursor:
            print(x)

#归还连接
pool.return_conn(conn)

class ConnPool:
    def __enter__(self):
        return self.get_conn()
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        #归还谁呢？
        pass

from queue import Queue
import pymysql
import threading

class ConnPool:
    def __init__(self,size,*args,**kwargs):
        if not isinstance(size,int)or size<1:
            size = 8
        self._pool = Queue(size)
        for i in range(size):
            self._pool.put(pymysql.connect(*args,**kwargs))
        self.local = threading.local()

    def get_conn(self):
        return self._pool.get() #阻塞

    def return_conn(self,conn):
        self._pool.put(conn)

    def __enter__(self):
        #self.local.conn在当前线程不存在抛属性异常
        if getattr(self.local,'conn',None) is None:
            self.local.conn = self.get_conn()
        return self.local.conn.cursor() #返回一个游标

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.local.conn.rollback()
        else:
            self.local.conm.commit()
        self.return_conn(self.local.conn)
        self.local.conn = None

#使用连接池
pool = ConnPool(4,'192.168.10.166','wayne','wayne','school')

#获取一个连接
conn = pool.get_conn()

with conn as cursor:    #自动拿连接并归还，还自动提交和回滚
    with cursor:
        sql = "SELECT * FROM student"
        cursor.execute(sql)
        print(cursor.fetchone())

        sql = "SHOW PROCESSLIST"    #观察连接，权限小只能看自己的
        cursor.execute(sql)
        for x in cursor:　　#对结果记录进行迭代
            print(x)

with conn as cursor:   
    with cursor:
        threading.Thread(target=xxx,args=(cursor,))