I. installation and use of navicate

• download

  Baidu searches navicate directly, as shown below

• Connect to database

• New database and new table

  Select and right-click

• modeling

• Using navicate to query practice

--Query the names of all courses and the corresponding teachers
-- SELECT
--  course.cname,
--  teacher.tname 
-- FROM
--  course
--  INNER JOIN teacher ON course.teacher_id = teacher.tid;

-- Query the names and average scores of students with an average score greater than 80
SELECT
    student.sname,
    t1.av 
FROM
    student
    INNER JOIN (
SELECT
    score.student_id,
    avg( score.num ) AS av 
FROM
    score 
GROUP BY
    score.student_id 
HAVING
    avg( score.num ) > 80 
    ) AS t1 ON student.sid = t1.student_id;

-- Check the names of students who did not report to Mr. Li Ping at the same time
-- 1,The course taught by Mr. Zha Liping id
-- 2,go score The students whose courses are reported by Mr. Li Ping id
-- 3,Go to the student list and check the student's name
SELECT
    * 
FROM
    student 
WHERE
    student.sid NOT IN (
SELECT DISTINCT
    score.student_id 
FROM
    score 
WHERE
    score.course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = 'Mr. Li Ping' ) 
    );

Query the names of students who do not take physics courses and physical education subjects at the same time(You can only choose between them)
-- 1,Check physical and sports id Number
#2. Get the id of all the students who have reported physics and sports
SELECT
    student.sname 
FROM
    student 
WHERE
    student.sid IN (
SELECT
    score.student_id 
FROM
    score 
WHERE
    score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( 'Physics', 'Sports' ) ) 
GROUP BY
    score.student_id 
HAVING
    COUNT( score.course_id ) = 1 
    );

-- Check the names and classes of students who have more than two (including two) suspended courses
# 1. Get all scores less than 60 first
SELECT
    student.sname,
    class.caption 
FROM
    student
    INNER JOIN class ON student.class_id = class.cid 
WHERE
    student.sid IN ( SELECT score.student_id FROM score WHERE num < 60 GROUP BY score.student_id HAVING count( score.course_id ) >= 2 );

II. pymysql

• First acquaintance

import pymysql

coon = pymysql.connect(
    user = 'root',
    password = '123456',
    host = '127.0.0.1',
    port = 3306,
    charset = 'utf8',
    database = 'day36_1'
)

cursor = coon.cursor(cursor=pymysql.cursors.DictCursor)  # A cursor object was generated
# cursor=pymysql.cursors.DictCursor returns the result in the form of a dictionary
sql = 'select * from student'
res = cursor.execute(sql)  # Execute sql statement
# print(res)   # execute returns the number of rows affected by the current SQL
# ret = cursor.fetchone()   # Get only one piece of data in the query result
# ret = cursor.fetchall()   # Get all data in query results
# ret = cursor.fetmany()   # Specify to get several pieces of data. If the number exceeds the limit, it will not report an error

# print(ret)


print(cursor.fetchone())
print(cursor.fetchone())


# Relative movement
cursor.scroll(2, 'relative')   # Back offset based on the position of the pointer

# Absolute movement
# cursor.scroll(3, 'absolute')   # Back offset based on start position

print(cursor.fetchall())

Relative movement

Absolute movement

• sql injection problem

import pymysql

coon = pymysql.connect(
    user = 'root',
    password = '123456',
    db = 'day36_1',
    host = '127.0.0.1',
    port = 3306,
    charset = 'utf8'
)

cursor = coon.cursor(cursor=pymysql.cursors.DictCursor)

#Obtain the user name and password entered by the user, and then go to the database for verification
username = input('username>>>:').strip()
password = input('password>>>:').strip()

sql = "select * from emp where name = '%s' and password = '%s'" %(username, password)


cursor.execute(sql)
res = cursor.fetchall()
if res:
    print(res)

else:
    print('username or password error!')


# I. only user name
# username>>>:yafeng ' -- daflakjflal
# password>>>:
# [{'id': 1, 'name': 'yafeng', 'password': '123'}]


# II. The user name and password are unknown
# username>>>:xxx' or 1=1 -- dalfjakdaj
# password>>>:
# [{'id': 1, 'name': 'yafeng', 'password': '123'}]

'''
sql Injection problem    
    //Using special symbols and annotation syntax to skilfully bypass the real sql verification
    
//Solution
//Key data should not be spliced manually, but should be spliced by execute
'''

• Solve injection problems

import pymysql

coon = pymysql.connect(
    user = 'root',
    password = '123456',
    db = 'day36_1',
    host = '127.0.0.1',
    port = 3306,
    charset = 'utf8'
)

cursor = coon.cursor(cursor=pymysql.cursors.DictCursor)

#Obtain the user name and password entered by the user, and then go to the database for verification
username = input('username>>>:').strip()
password = input('password>>>:').strip()

sql = "select * from emp where name = %s and password = %s"
print(sql)
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if res:
    print(res)

else:
    print('username or password error!')

• Data addition, deletion, modification and query

import pymysql


coon = pymysql.connect(
    user = 'root',
    password = '123456',
    db = 'day36_1',
    host = '127.0.0.1',
    port = 3306,
    charset = 'utf8',
    autocommit = True   # Auto submit confirmation
)


cursor = coon.cursor(cursor=pymysql.cursors.DictCursor)
#
# # Obtain the user name and password entered by the user, and then go to the database for verification
# username = input('username>>>:').strip()
# password = input('password>>>:').strip()
#
# sql = "select * from userinfo where name=%s and password=%s"
# print(sql)

'''
//The importance of adding, deleting and modifying operations is too high
//If you really want to operate, you must further confirm the operation (commit)
'''


# increase
# sql = "insert into emp(name,password) values('jason',456)"

# change
# sql = "update emp set name='jason_nb' where id = 2"

# Delete
sql = "delete from emp where id = 1"

res = cursor.execute(sql)
print(res)