18 sub-query, IDE tools (NAVICAT), Pymysql module (installation, query, anti-SQL injection, add and delete changes)

18

First, the last Class review

Select Concat_ws (":", Name,age,sex,post) as info from EMP; # egon:male:18

Second, sub-query (a problem solving a problem)

Enclose a query statement in parentheses, as a condition of another query statement, called a subquery

Select name from the emp where dep_id = (select id from DEP where name= "technology"); #子查询

Select Emp.name from emp inner JOIN dep on emp.dep_id = dep.id where dep.name= "technology"; #链表

#查询平均年龄在25岁以上的部门名

Select name from DEP where ID in (select dep_id from EMP GROUP by DEP_ID have AVG (age) > 25); #子查询

Select Dep.name from emp inner JOIN dep in emp.dep_id = Dep.id GROUP BY Dep.name have AVG (age) > 25; #链表

#查看不足2人的部门名 (sub-query gets the department ID of someone)

SELECT * from emp where exists (select ID from dep where ID > 3); #exists用法, when () returns true, the outer query statement is queried, and when the return value is false, the outer query statement does not query (empty set)

#查询每个部门最新入职的那位员工

Select T1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as T1 inner join (select Post,max (hire_date) as Ma X_date from EMP Group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date;

Three, IDE tools (NAVICAT) Introduction

1. ER chart (entity-relationship)

2, model; export SQL

3, query, format to beautify SQL

Four, Pymysql module (Installation and query)

1. Install Pymysql (Python-specific MySQL client socket)

PIP3 Install Pymysql

2. mysql Query

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' jxtz0927 ',

Database= ' DB40 ',

charset= ' UTF8 ' #防止乱码

)

Cursor=conn.cursor (pymysql.cursors.DictCursor) #将产生的结果以字典的形式显示 [{' TABLES_IN_DB40 ': ' Department '}, {' TABLES_IN_DB40 ': ' Employee '}]

# cursor=conn.cursor ((' Department ',) (' Employee ',)) #

# Rows=cursor.execute (' Show tables; ') #rows to the number of rows affected

Rows=cursor.execute (' select * from class; ')

Print (rows) #2

Print (Cursor.fetchone ()) #一条

Print (Cursor.fetchmany (2)) #几条

Print (Cursor.fetchall ()) #全部

Cursor.scroll (3, ' absolute ') #mode = ' absolute ', absolute mode, three moves backwards from the first position

Cursor.scroll (1, ' relative ') #mode = ' relative ', relative mode, move backward from current position

Cursor.close ()

Conn.close ()

V. PYMYSQL module (prevents SQL injection problems)

1, the wrong way, the string stitching itself, triggering SQL injection problem (name= Egon '--ASDFG; Name=xxxx ' or 1=1--ASDFG)

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Inp_user=input (' User name >>: '). Strip () #inp_user = ""

Inp_pwd=input (' Password >>: '). Strip () #inp_pwd = ""

Sql= "SELECT * from user where username= '%s ' and password= '%s '"% (inp_user,inp_pwd) #自行拼接, raises SQL injection problem

Print (SQL)

Rows=cursor.execute (SQL)

If rows:

Print (' Login successful ')

Else

Print (' Login failed ')

Cursor.close ()

Conn.close ()

2, in the server to prevent SQL injection problem: Do not own the string, let Pymysql module to splice

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Inp_user=input (' User name >>: '). Strip () #inp_user = ""

Inp_pwd=input (' Password >>: '). Strip () #inp_pwd = ""

Sql= "SELECT * from user where username=%s and password=%s"

Print (SQL)

Rows=cursor.execute (SQL, (INP_USER,INP_PWD))

If rows:

Print (' Login successful ')

Else

Print (' Login failed ')

Cursor.close ()

Conn.close ()

Vi. pymysql Module (Increase and deletion)

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Sql= ' Update user set username= "ALEXSB" where id=2 '

Rows=cursor.execute (SQL) #改数据

Print (rows)

Print (CURSOR.LASTROWID)

Sql= ' INSERT into user (Username,password) VALUES (%s,%s) '

Rows=cursor.executemany (sql,[(' lwz ', ' 123 '), (' Evia ', ' 455 '), (' LSD ', ' 333 ')]) #一次插入多行记录

Print (rows)

Print (CURSOR.LASTROWID) #显示插到哪行了 (ID)

Conn.commit () # Only Commit commits will complete the real change

Cursor.close ()

Conn.close ()

18 sub-query, IDE tools (NAVICAT), Pymysql module (installation, query, anti-SQL injection, add and delete changes)