Shulou(Shulou.com)06/01 Report--

The second day of MySQL

Key words: grouping aggregation

Self-correlation

Physically one table, logically two tables

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

Import sql Fil

Source areas.sql

Sample diagram

Sample statement

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=' Shanxi Province 'limit 0100; View

Create view stuscore as + query statement

Business

Four features (ACID)

Atomicity (Atomicity): transaction indivisibility consistency (Consistency): execution order between transactions does not affect result isolation (Isolation): undisturbed persistence (Durability): changes to the database are not lost

Engine type: engine=innodb/bdb supports transactions, default innodb

View the statement created by the table: show create table students

Modify the table type: alter table students engine=innodb

Transaction statement

Begin; / / enable commit; / / submit rollback; / / rollback operation index

View Index

Show index from table name

Create an index

Create index indexName on areas (title (20))

Delete index

Drop index [indexName] on table name

Execution time

Enable execution time monitoring: set profiling=1; execution statement displays monitoring results: database operation of show profiles;Python

Every python session is a transaction

Common class

Connec class

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

Methods of connection object

Close () closes the connection commit () transaction commit, so it needs to be committed before the rollback () transaction rollback takes effect. The previous operation cursor () returns the Cursor object to execute the sql statement and get the result.

Cursor

Execute SQL statement

Cursor1=connection.cursor () / / calls the cursor method to return a cursor object

Common methods of cursor object

Execute (operation, [parameters]) / / executes the statement, returns the number of affected rows fetchone () / / gets the data of the first row of the query result set, returns a tuple fetchall () / / gets all the rows of the query result set, and one row forms a tuple Return a large tuple import MySQLdbtry: connection=MySQLdb.connect (host='localhost',port=3306,db='python3',user='root',passwd='***',charset='utf8') cursor1=connection.cursor () sql='SQL statement to add, delete, modify 'count=cs1.execute (sql) connection.commit () cursor1.close () connection.close () except Exception,e: print (e.message) parameterization

Prevent SQL injection

From MySQLdb import * try: name = raw_input ('Please enter a name') connection = connect (host='localhost',port=3306,db='python3',user='root',passwd='***' Charset='utf8') # sql = 'insert into students (name) values' # sql = 'update students set name=' clever' 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) package 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.passwd 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.message)

5/13/2018 9:57:42 PM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.