MySQL Third day

MySQL the next day

Keywords: grouping aggregation

Self-correlating

physically a table, logically two tables

create table areas(id int primary key,atitle varchar(20),pid int,foreign key(pid) references areas(id));  

Import SQL File

source areas.sql;  

Example Diagram

Example Statements

select sheng.id as sid,sheng.title as stile,shi.id as shiid,shi.title as shititle from areas as shenginner join areas as shi on sheng.id=shi.pidwhere sheng.pid is null and sheng.title=‘山西省‘limit 0,100;

View

create view stuscore as + 查询语句

Transaction

Four properties (ACID)

• Atomicity (atomicity): Transaction indivisible
• Consistency (consistency): The order of execution between transactions does not affect the result
• Isolation (Isolation): undisturbed
• Persistence (Durability): Changes to the database are not lost

Engine Type: ENGINE=INNODB/BDB support transaction, default InnoDB
To view the statements created by the table: show create table students;
To modify a table type: alter table students engine=innodb;

Transaction Statements

begin; //开启commit; //提交rollback; // 回滚操作

Index

View Index

show index from 表名；

Create an index

create index indexName on areas(title(20));  

Delete Index

drop index [indexName] on 表名;  

Execution Time

• Turn on Execution time monitoring: set profiling=1；
• EXECUTE statement
• Show monitoring results:show profiles；

Database operations for Python

Each Python session is a single transaction

Common classes

Connec class

connection = connect(host,port,db,user,passwd,charset)

Methods for connection objects

close() 关闭连接commit() 事务提交，所以需要提交才会生效rollback() 事务回滚，放弃之前的操作cursor() 返回Cursor对象，用于执行sql语句并获得结果  

Cursor

Execute SQL statement

cursor1=connection.cursor()   // 调用cursor方法 返回一个cursor对象    

Common methods for Cursor objects

execute(operation ,[ parameters ]) //执行语句，返回受影响的行数fetchone() //获取查询结果集的第一个行数据，返回一个元组fetchall()  //获取查询结果集的所有行，一行构成一个元组，返回一个大元组

Change and delete

import MySQLdbtry:    connection=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘python3‘,user=‘root‘,passwd=‘***‘,charset=‘utf8‘)    cursor1=connection.cursor()    sql=‘SQL语句增删查改‘    count=cs1.execute(sql)    connection.commit()    cursor1.close()    connection.close()except Exception,e:    print (e.message)

Parameterization of

Prevent SQL injection

from MySQLdb import *try:    name = raw_input(‘请输入一个名字‘)    connection = connect(host=‘localhost‘,port=3306,db=‘python3‘,user=‘root‘,passwd=‘***‘,charset=‘utf8‘)    #sql = ‘insert into students(name) values("小乖巧")‘    #sql = ‘update students set name=‘乖巧‘ where id=3‘    #sql = ‘delete from students where id = 3‘    sql = ‘insert into students(name) values(%s)‘    cursor1.execute(sql,[name])      connection.commit()    cursor1.close()    connection.close()except Exception,e:    print (e.message)

Packaging

  class Mysql:def __init__ (self,host,port=3306,db,user,passwd,charset= ' uft8 '): Self.host = Host    Self.port = Port Self.db = db Self.user = user SELF.PASSWD = passwd Self.charset = CharSet def open (self): self.connection = Connect (host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.pass        Wd,charset=self.charset) Self.cursor = Self.connection.cursor () def close (self): Self.cursor.close ()            Self.connection.close () def curd (self): Try:self.open () self.cursor (Sql,param) Self.commit () self.close () except Exception,e:print (E.message) def all (Self,sql,param            =[]): Try:self.open () self.cursor (sql,param) result = Self.cursor.fetchall () Self.commit () Self.close () return result except Exception,e:print (E.MESSAG e)  

5/13/2018 9:57:42 PM

MySQL Third day