学习感悟

我的标签

1.外键：

userinfo里面depId数据所到表deparment表里面id数据约束

单对多

哪个表的列需要约束，就在哪张表进行创建外键

create table userinfo(

id int primary key auto_increment,

name varchar(32) not null default '',

email varchar(32) not null default '',

depId int not null default 0,

constraint fk_userinfo_dep foreign key (depId) references deperment(id)

)engine=Innodb charset=utf8;

create table deperment(

id int primary key auto_increment,

depname varchar(32) not null default ''

)engine=Innodb charset=utf8;

表多对多关系

2.视图：

create view v1 as select * from goods limit 10;

show full tables;

3.事务：

事务的特性：

原子性：要么全部执行，要么全部取消

一致性：事务发生前后，数据的总额依然匹配

隔离性：事务对其他事务操作不可见的

持久性：当事务完成后，影响保留下来，不能撤销，只能通过“补偿性事务”抵消之前错误

select database(); //查看选择的库

start transaction; //开启事务

create table user(

id int primary key auto_increment,

name varchar(32) not null default '',

money int not null default 0

)engine=Innodb charset=utf8;

insert into user(name,money) values('Frank',5000);

insert into user(name,money) values('sunny',5000);

start transaction;

update user set money=money-1000 where id =1 ;

update user set money=money+1000 where id =2;

commit;

4.索引

普通索引：

加快查询

show indexes from user \G; //查看索引

alter table user add index name(name);

唯一索引：

alter table userinfo add unique depId(depId); //确认这一列数据不重复

联合索引：

组合索引：将n个列组合一个索引

alter table userinfo add index name_email(name,email);

show indexes from userinfo \G;

删除索引

drop index name_email on userinfo;

创建索引第二种方式：

create index name on userinfo(name);

创建索引第三种方式：

create table userinfo1(

id int primary key auto_increment,

name varchar(32) not null default '',

email varchar(32) not null default '',

depId int not null default 0,

index name1(name)

)engine=Innodb charset=utf8;

注意：

1、经常查询的列设置索引

2、导表时先去除表的索引

3、常用组合索引（查询是两个条件同时满足才用到索引）

explain select * from userinfo1 \G; //查看索引是否引用

mysqldump -uroot -p123456 day12 >/root/abc.sql //导出库day12的数据

mysql -uroot -p123456 day12 </root/day12.sql //建库day12，导入库day12的数据

source /root/day12.sql //登陆后导入数据

5.Python操作mysql的方式：

Python DB_API

API连接过程：

引入API模块

获取与数据库的连接

执行SQL语句和存储过程

关闭数据库连接

PYMYSQL连接MYSQL方式一：

安装：pip3 install pymysql

import pymysql

# 连接数据库
#conn = pymysql.connect('localhost', 'root', 'root')
# 也可以使用关键字参数
conn = pymysql.connect(host='192.168.10.5', port=3306, user='root', passwd='123456',charset='utf8')

# 也可以使用字典进行连接参数的管理
# config = {
#     'host': '127.0.0.1',
#     'port': 3306,
#     'user': 'root',
#     'passwd': 'root',
#     'db': 'test',
#     'charset': 'utf8',
#     # 'cursorclass':pymysql.cursors.DictCursor
# }
# conn = pymysql.connect(**config)
# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()
# conn.autocommit(1) # conn.autocommit(True)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针
try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' % DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' % DB_NAME)
    # 选择数据库
    conn.select_db(DB_NAME)
    # 创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' % TABLE_NAME)
    # 插入单条数据
    sql = 'INSERT INTO user values("%d","%s")' % (1, "jack")
    cursor.execute(sql)

    # 或者
    value = [2, 'John']
    cursor.execute('INSERT INTO user values(%s,%s)', value)
    # 批量插入数据
    values = []
    for i in range(3, 20):
        values.append((i, 'kk' + str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)', values)

    # 获取最新自增ID
    new_id = cursor.lastrowid
    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' % TABLE_NAME)
    print('total records: %d' % count)
    print('total records:', cursor.rowcount)
    # 获取表名信息
    desc = cursor.description
    print("%s %3s" % (desc[0][0], desc[1][0]))
    # 查询一条记录
    print('fetch one record:')
    result = cursor.fetchone()
    print(result)
    print('id: %s,name: %s' % (result[0], result[1]))
    # 查询多条记录
    print('fetch five record:')
    results = cursor.fetchmany(5)
    for r in results:
        print(r)
    # 更新记录
    cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' % (TABLE_NAME, 'Jack', 1))
    # 删除记录
    cursor.execute('DELETE FROM %s WHERE id = %s' % (TABLE_NAME, 2))
    # 如果没有设置自动提交事务，则这里需要手动提交一次
    conn.commit()
except:
    import traceback

    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

SQLACHEMY操作MYSQL

什么是ORM

ORM(object relational mapping)，就是对象关系映射，通过ORM将编程语言的对象模型和数据库的关系模型建立映射关系

什么是SQLALchemy

Sqlalchemy是python的orm程序

安装

pip3 install mymysql

pip3 install sqlalchemy

sqlalchemy操作mysql方式二：

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

engine = create_engine("mysql+pymysql://root:123456@192.168.10.5:3306/day12", max_overflow=5) #创建数据库连接，max_overflow指定最大连接数
DBSession = sessionmaker(bind=engine) #创建DBSession类型,创建了一个自定义了的 Session类
session = DBSession() #创建session对象
Base = declarative_base() #创建对象的基类

class Users(Base): #定义User对象
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=False,default='')
    extra = Column(String(16),nullable=False,default='')

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )

def drop_db():
    Base.metadata.drop_all(engine)

def init_db():
    Base.metadata.create_all(engine)

drop_db()
init_db()

#添加一行数据
# session.add(Users(name='Frank',extra='shop'))
#添加多行数据
session.add_all([
    Users(name='Frank',extra='shop'),
    Users(name='sunny',extra='run')]
)
#删除数据
session.query(Users).filter_by(name='frank').delete()

#修改数据
session.query(Users).filter(Users.id>1).update({'name':'Sunny'})

#查询
ret=session.query(Users).all()
for v in ret:
    print(v.name)

ret=session.query(Users.name,Users.extra).all()
ret=session.query(Users).filter_by(name='Frank').all()

session.commit()

返回顶部

Frank的学习之路

随笔分类

随笔档案

Day_13_总结_MySQL高级学习目录