Welcome to my blog

1, Relational database

< "\ 1 / >, data model

There are three types of relationships between entities:

The first mock exam model (1*)

One to one
Relational model uses two-dimensional tables to represent data and data relations. It is the most widely used data model. At present, various commonly used databases, such as Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Oracle, MySQL, SQLite, etc., belong to relational model database management system.

2 *) one to many model

One to many
The hierarchical model uses a tree structure to represent the relationship between data. The nodes of the tree are called records, and there is only a simple hierarchical relationship between records. If there is only one node without a parent node, this node is called the root node; Other nodes have one and only one parent node.

3 *) many to many model

Many to many
Any number of nodes can have no parent node. A node can have multiple parent nodes. There can be two or more connections between two nodes.

< "\ 2 / >, face object model

1 *) object model concept

Object oriented model is a data model developed on the basis of object-oriented technology. It uses object-oriented method to design database.

2 *) object model features

The database of object-oriented model is a kind of storage object, which takes the object as the unit. Each object contains the attributes and methods of the object, and has the characteristics of class and inheritance.

2, Understand the concept and characteristics of relational database

Concept and characteristics of < "\ 1 / > relational database

/(1).*/ (The_first) - Basic Concepts

i *) relationship

The relationship between data and data is called relationship.

ii *) two dimensional table

Relational databases use two-dimensional tables to represent and store relationships. A relationship is a two-dimensional table. Rows in a table are called records and columns are called fields. A database can contain multiple tables.

iii *) records and fields

A row in a table is called a record. The columns in the table are the data items in the record, which are called fields. Fields are also called attributes or columns. Each record can contain multiple fields, and different records contain the same field (field values are different). For example, each record in the user table contains fields such as user name, login password, etc.
A relational database does not allow duplicate records in a table.

VI *) keyword

Fields or field combinations that uniquely identify a record are called keywords. A table can have multiple keywords. The keywords used to identify records are called primary keywords, and other keywords can be called candidate keywords. Only one primary key is allowed for a table. For example, the user name in the user table can be defined as the primary keyword. When adding records, the primary keyword cannot be repeated.

VII *) external keywords

If a field or field combination in one table is used as the primary key of other tables, such a field or field combination is called an external key.

< "\ 2 / > basic features of relational database

/(2).*/(The_second) - features included

The table in relational database is a two-dimensional table, and the fields in the table must be non separable, that is, the table in the table is not allowed.
Duplicate records are not allowed in the same table.
Duplicate fields are not allowed in the same record.
The order of records in the table does not affect the nature of data, and the order of records can be exchanged.
The order of fields in the record does not affect the data, and the order of fields can be exchanged.

3, Common field data types

< "\ 1 /" > data table

data type	explain
integer(size) int(size) smallint(size) inyint(size)	Save integer size, and specify the maximum number of digits
decimal(size, d) numeric(size, d)	Save decimal, size specifies the maximum number of digits, d specifies the maximum number of digits
char(size)	Save fixed length strings (letters, numbers, and special characters). size specifies the length of the string
varchar(size)	Save variable length strings (letters, numbers, and special characters). size specifies the length of the string
date	Save date
centered text	Right aligned text right

4, Link Mysql and Pymysql successfully

First, find the data table login link in Navicat Premium 12

• Note: the data sheet must exist, otherwise the data modification and addition will not be included!!!!

Step 2: after the link is displayed successfully, log in to spyder or pycharm and use pymysql to call mysql

Note: spyder used by Blogger

import pymysql

#Define database link parameters
host = '127.0.0.1'  #Or use local host
port = 3306
db = 'student'
user = 'root'
password = 'lyt2529165097'
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
def main():
    cursor = conn.cursor(pymysql.cursors.SSCursor)  #Streaming cursor, which returns tuples by default
    return cursor
 Through in spyder Enter the host number, port number, host name, password, etc. to log in

Step 3: write code to log in to the system

1 *) write the system login and exit interface

while True:
    print('Please select the following menu number:')
    print('========='*3)
    print('1,Login student information management system')
    print('2,Exit student information management system')
    print('========='*3)
    mc1 = int(input('Enter menu number:'))
    if mc1 == 1:
        login()
    elif mc1 == 2:
        print('Thank you for using the student information management system!')
        break

• Of course, how can you enter the system without a password. Be sure to set your password, or others will delete your code in the middle of the night!!!

2 *) write the user name and password to enter the system

def login():
    administartor = input('Please enter user name:')
    password = input('Please input a password:')
    if administartor == 'Lei Yunteng' and password == 'lyt2529165097':

• After successfully entering the system, we can write the next program. For example, we can write the menu system, so we have an overall architecture

3 *). Write a menu for selection after login

/(1).*/The - first menu

#Menu after login - expand
def login():
    administartor = input('Please enter user name:')
    password = input('Please input a password:')
    if administartor == 'Lei Yunteng' and password == 'lyt2529165097':
        print("Congratulations on your successful login!!")
        while True:
            print('Student information management system')
            print('================')
            print('1,Add student records')
            print('2,Query student records')
            print('3,Modify student records')
            print('4,Delete student record')
            print('5,Show all student records')
            print('6,Previous Menu ')
            print('=================')
            mc2 = int(input('Enter menu number:'))
            if mc2 == 1:
                add_student()
            elif mc2 == 2:
                query_student()
            elif mc2 == 3:
                update_student
            elif mc2 == 4:
                delete_student()
            elif mc2==5:
                print_student()
            else:
                break
    else:
        print('Wrong account or password!')

• After successfully entering the system and having the menu system, we can write the next program. For example, we write the program to insert records first

4 *) prepare procedures for adding student records

#Insert student record
def add_student():
    cursor = main()
    id = int(input('Student No.:'))
    name = input('full name:')
    gender = input('Gender:')
    age = int(input('Age:'))
    class1 = input('Class:')
    major = input('Major:')
    college = input('College:')
    add = cursor.execute('insert into stu (id, name, gender, age, class1, major, college)\
                   values(%s,%s,%s,%s,%s,%s,%s)',(id, name, gender, age, class1, major, college))
    if add == 1:
        conn.commit()
        print('Insert succeeded!')
    else:
        print('Insert failed!')

Fourth, write the code to query student information records

I *). Query student records according to student number

#Query by student number
def Q_by_id():
    cursor = main()
    choice_id = int(input('Please enter student number:'))
    cursor.execute('select * from stu where id =%s',(choice_id))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6])
        print('query was successful')
         re = input('Continue query(yes/no): ')
        if re == 'yes':
            Q_by_id()
        else:
            query_student()

II *). Query student records by name

#Query by name (in case of wrong student number input)
def Q_by_name():
    cursor = main()
    choose_name = input('Please enter your name:')
    cursor.execute('select * from stu where name =%s',(choose_name))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6])
        print()
        re = input('Continue query yes/no: ')
        if re == 'yes':
            Q_by_name()
        else:
            query_student()

III *). Query the information records of all students

#Query all students
def Q_all():
    cursor = main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()
    for student in students:
        print('\t{}\t{}\t{}\t{}\t{}\t{}\t{}' .format(student[0], student[1], student[2], student[3], student[4], student[5], student[6]))

• In this way, the query is successful!, If you still want to query, you can also write code to realize it!

VI *). Write whether to query records

 re = input('Continue query(yes/no): ')
        if re == 'yes':
            Q_by_id()
        else:
            query_student()

• Of course, in addition to being able to query, there must be a menu for us to choose from! Otherwise, we will not be able to enter the program, which will be added, deleted, modified and checked by the bank

VII *). After writing the login menu, we should also write the menu for us to query

/2.*/The - Sencond menu

#Query menu
def query_student():
    while True:
        print('Query student records')
        print('================')
        print('1,Query student records by student number')
        print('2,Query student records by name')
        print('3,Query all student records')
        print('4,Previous Menu ')
        print('=================')
        mc3 = int(input('Please enter the menu number of the query:'))
        if mc3 == 1:
            Q_by_id()
        elif mc3 == 2:
            Q_by_name()
        elif mc3 == 3:
            Q_all()
        else:
            break

• Note: not only write the code, but also write the menu to choose from~

VII *). Prepare procedures for deleting student records

#Delete student record
def delete_student():
    cursor = main()
    id = int(input('Enter the student number of the student you want to delete:'))
    delete = cursor.execute('delete from stu where id = {}' .format(id))
    if delete == 1:
        conn.commit()
        print('Delete succeeded!')
    else:
        print('Deletion failed!')

/(3).*/The—Third menu

#Deleted menu
def delete1_student():
    print('============================')
    print('1,Delete all student information')
    print('2,Return to the initial interface')
    print('============================')
    mc4 = int(input('Input menu number:'))
    if mc4 == 1:
        delete_student()
    elif mc4 == 3:
        login()

• Delete the information with the student name Lei Yunteng
• First of all, we should know his student number, and then his name

• The next step is our most difficult part, which is also our highlight. Modify the designated student records
  You still need to know his student number and name

def update_student():
        cursor = main()
        cur= int(input('Please enter the student number of the student you want to modify:'))
        cursor.execute('select * from stu where id = %s', (cur))
        if cursor.fetchall() == []:
            print('The student number is not found{}Students'.format(cur))
            mc3 = input('Re query?(yes/no)')
            if mc3 != 'no':
                update_student()
            else:
                login()
        else:
            print('==============')
            print('1,Modify name')
            print('2,Modify gender')
            print('3,Modify age')
            print('4,Modify class')
            print('5,Modify specialty')
            print('6,Modify College')
            print('7,Previous Menu ')
            print('==============')
            mc2 = int(input('Please enter the menu number:'))
            if mc2 == 1:
                name = input('Please enter the modified Name:')
                a = cursor.execute('update stu set name = %s where id = %s', (name, cur))
                if a == 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            elif mc2 == 2:
                gender1 = input('Please enter the modified gender:')
                a = cursor.execute('update stu set genden = %s where id = %s', (gender1, cur))
                if a > 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            elif mc2 == 3:
                age1 = int(input('Please enter the modified age:'))
                a = cursor.execute('update stu set age = %s where id = %s', (age1, cur))
                if a > 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            elif mc2 == 4:
                class1 = input('Please enter the modified class:')
                a = cursor.execute('update stu set class = %s where id = %s', (class1, cur))
                if a > 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            elif mc2 == 5:
                major1 = input('Please enter the modified major:')
                a = cursor.execute('update stu set major = %s where id = %s', (major1, cur))
                if a > 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            elif mc2 == 6:
                college1 = input('Please enter the modified College:')
                a = cursor.execute('update stu set college = %s where id = %s', (college1, cur))
                if a > 1:
                    conn.commit()
                    print('Modification succeeded!')
                else:
                    print('Modification failed!')
            else:
                pass#Occupy a space character

• When I haven't closed the data table and it hasn't been updated, we need to close the updated data table and reopen it

• When we close the data table, it will update the data we have changed
  
  
• Of course, in addition to changing the name, you can also change the student number, gender, college and so on

5, All source code (specify)

• I won't tell you this. I've told you so much knowledge. I'm sure you can play it out or even do better with more practice.

6, Blog summary

One, why write code

Now the code will not only increase my knowledge and thinking, but also affect other things with the increase of knowledge. Of course, I often encounter some seemingly thorny problems in life and learning in my blog. At that time, I always forced myself to think I could find a way out when I was struggling. Over time, I also developed such a thinking mode that things can always find the answer. Every time I write a blog and article and get praise from my peers and teachers, I will be excited to pave the way for my next better blog content.

Two, specific thinking

In this process, I also feel that writing code is similar to the previous problem-solving mode. Each time I complete a complete thing, it forms a small part of a specific thinking mode. Maybe this is the fun of writing code.

There, thinking and inspiration

Every time I am confused about a problem that cannot be solved for a long time, I will feel very depressed. At this time, I will try to go out for a breath of fresh air and stretch to empty my mind. You'll find it a lot easier. It's especially easy to come back and solve this confusion. Sometimes inspiration comes after you have been depressed for a long time and suddenly relax, that moment.

Four, brain + hand

• Cultivate your programming thinking and fun. The code is not imagined out of thin air, but knocked out line by line. There is no shortcut to run successfully, only relying on your own brain + hand.