Python MYsql, database, and pythonmysql Database

Python MYsql, database, and pythonmysql Database

I. Database Operations and Installation

1. Installation

 MySQL Community Server 5.7.16 ---- version

2. Initialization

Mysqld-- Inisialize-Insecure

3. Start

Mysqld

4,Start the MySQL client and connect to the MySQL Service

Mysqld-- Initialize-The insecure command does not set a password for the root account by default.

5. Check whether MySQL is started.

Tasklist | findstrMysql

6. Basic database commands

Mysqld -- inisialize-insecure initialize mysqld in the form of no password to start mysqldtasklist | findstr mysqld check whether mysql starts mysql-uroot-p link quit to exit tskill mysql to kill mysqlmysqld -- install in winds mysqldmysqladmin-uroot password 123 set password mysqld -- skip-grant-tables skip restriction and start mysqldupdate mysql directly. user set authentication_string = password (456) where user = 'root' and host = 'localhost'; (version 5.7) the password is changed and mysql is updated. user set password = password (5. 6) flush privileges; refresh the permission select user (); view the current user create user 'mqj' @ 'localhost' identfied by '123 '; create user 'egon' @ '%' identfied by '000000' create Remote Account create user 'wupeiqi '@ '123. 168.20.% 'identified by '123' create database db1 charset utf8 for the remote network account mysql-h192.168.000099-ualex-p123 to remotely connect to the database folder; Add the db1 folder show databases; view All databases show create database db1; view the db1 folder drop database db1; Delete the db1 folder alter da Tabase db1 charset gbk; change db1 encoding operation file (table) to switch to file: use db2 switch Folder \ c cancel command create table t1 (id int, name char (10 )); create table show tables; view all tables in the current file show create table t1; view table alter table t1 add age int; add field alter table t1 modify name char (12 ); change the name character desc t1 in the table; view the table structure drop table t1; Delete the row content (record) of the table operation file insert into db1.t1 values (1, 'egon1'), (2, 'egon2'), (3, 'egon3'); Add the record select * from t1; view the values of all fields select * from t1 where id> 2. view the select name from t1 field whose id is greater than 2; view the single Field update t1 set name = 'SB 'where id = 3; modify the record delete from t1 where id = 3. delete a record in two ways to clear the table record, but the latter is recommended to delete from t1; truncate t1; # When there is a large amount of data, the deletion speed is faster. The whole operation is to delete the auto-increment idcreate table t2 (id int primary key, name char (10 )); create table t3 (id int not null unique, name char (10); create table t2 (id int primary key auto_increment, name char (10 )); add create table t6 select * from t5; copy Table create table t7 select * from t5 where 1 = 2; copy table structure alter tablet7 modify id int primary key auto_increment change table structure delete t7 set name = ''; delete record select database () view the insert into t1 (id) values (1) data insertion method in the folder select * from t1; view the inserted data content select * from t1; data Type 1. Number (all are signed by default, and the width indicates the display width, which is not related to storage) (1) tinyint [unsigned] [zerofill] (1 byte memory) signed:-128 ~~ 127 unsigned: 0 ~~ 255 (2) int [unsigned] [zerofill] (4-byte memory) Signed:-2147483648 ~~ 2147482647 unsigned: 0 ~~ 4294967295 (3) bigint [unsigned] [zerofill] (8-byte memory) Signed:-9223372036854775808 ~~ 9223372036854775808 unsigned: 0 ~~ 4949672952 characters (width refers to the number of characters related to storage): char: Fixed Length (simple and crude, not enough to make up a fixed length for storage, a waste of space, fast access) varchar: variable Length (precise, computing the length of data to be stored, space saving, slow access) 3. Date # registration time datatime 10:39:46 # Date of birth, start time data: # Chat History, course time: 10: 39: 46 # year of birth year: 20174, enumeration and collection enum enumeration: specifies a range, can have multiple values, however, for this field, the ship can only go to a set in the specified range: specifying a range, there can be multiple values, but for this field, the ship is only, only one or more of the specified ranges can be used: integer test create table t1 (id tinyint); create table t2 (id int); create table t3 (id bigint ); # test create table t4 (salary float (3.735); insert into t4 values (3.735684); insert into t4 values ); 2. char and varcahr test create table t6 (name char (4); insert into t6 values ('Alex ') insert into t6 values ('oude Bo '); insert into t6 values ('alics'); create table t7 (x char (5), y varchar (5); insert into t7 values ('addd', 'dss ') # char_length: check the length of the characters insert into t7 values ('Hello! ', 'Hello girl') # char_length: check the length of the characters insert into t7 values ('Hello ', 'Hello girl ') # length: Check the byte length select char_length (x), char_length (y) from t7; note two points: insert into t7 values ('abc ', 'abc'); # length: view the byte length select * from t7 where y = 'abc'; # Remove the trailing space and compare it. 3. create table student (id int, name char (5), born_date date, born_year year, reg_time datetime, class_time time); insert into student values (1, 'Alex ', now (), now (), now (), now (); insert into student values (1, 'Alex ', '2017-09-06', '2016', '2017-09-06 10:09:36 ', '09: 06: 36'); 4. Enumeration and set create table student1 (id int primary key auto_increment, name char (5), sex enum ('male ', 'female'), hobbies set ('music', 'read', 'coding'); insert into student1 (name, sex, hobbies) values ('egon ', 'male', 'music, read, coding '); 1 simple query select * from employee; select name, salary from employee; 2 where condition select name, salary from employee where salary> 10000; select name, salary from employee where salary> 10000 and salary <20000; select name, salary from employee where salary between 10000 and 20000; select name, salary from employee where salary not between 10000 and 20000; select name, salary from employee where salary = 10000 or salary = 20000 or salary = 30000; select name, salary from employee where salary in (Clerk, 20000, 30000); select * from employee where salary = 10000 or age = 18 or sex = 'male'; select * from employee where post_comment is Null; select * from employee where post_comment = Null; select * from employee where post_comment is not Null; select * from employee where name like '% n % '; select * from employee where name like 'e _ n'; 3 group by grouping mysql> select depart_id, group_concat (name) from employee group by depart_id; mysql> select depart_id, count (id) from employee group by depart_id; mysql> select depart_id, group_concat (id) from employee group by depart_id; mysql> select depart_id, count (id) from employee group by depart_id; mysql> select depart_id, max (salary) from employee group by depart_id; mysql> select depart_id, min (salary) from employee group by depart_id; mysql> select depart_id, sum (salary) from employee group by depart_id; mysql> select depart_id, avg (salary) from employee group by depart_id;

 

Ii. Database File Operations

 

Create database db1 charset utf8; add db1 folder show databases; view all databases show create database db1; view db1 folder drop database db1; Delete db1 folder alter database db1 charset gbk; Modify db1 Encoding

 

2. Operating Files

Switch to the file: use db2 switch Folder \ c cancel command create table t1 (id int, name char (10); create table show tables; view all tables in the current file show create table t1; view the alter table t1 add age int; add the field alter table t1 modify name char (12 ); change the name character desc t1 in the table; view the table structure drop table t1; Delete the table

3. Operate the row content of a file

Insert into db1.t1 values (1, 'egon1'), (2, 'egon2'), (3, 'egon3'); add record select * from t1; view the values of all fields select * from t1 where id> 2; view the select name from t1 field whose id is greater than 2; view a single Field update t1 set name = 'SB 'where id = 3; Change the record delete from t1 where id = 3; deleting a record can be used to clear a table record. However, we recommend that you delete from t1; truncate t1; # When the data volume is large, fast deletion: auto-increment idcreate table t2 (id int primary key, name char (10); create table t3 (id int not null unique, name char (10 )); create table t2 (id int primary key auto_increment, name char (10 ));

4. Some uncommon operations

Create table t6 select * from t5; copy table create table t7 select * from t5 where 1 = 2; copy table structure alter tablet7 modify id int primary key auto_increment change table structure delete t7 set name = ''; delete record

Iii. Database Design

Performance consumption of connected tables

(1) Table Connection Design

Method 1:

Class UserType (models. model): "User Type table, the number of frequent changes" "title = models. charField (max_length = 32) class UserInfo (models. model): "User table: Lecturer and class teacher" username = models. charField (max_length = 32) password = models. charField (max_length = 64) email = models. charField (max_length = 32) ut = models. foreignKey (to = "UserType ")

Method 2:

-Choices # class UserInfo (models. model): # "" # User table # "" # username = models. charField (max_length = 32) # password = models. charField (max_length = 64) # email = models. charField (max_length = 32) # user_type_choices = (# (1, 'class teacher '), # (2, 'lecturer'), #) # user_type_id = models. integerField (choices = user_type_choices)

Iv. Transactions

A transaction is used to take multiple SQL statements of some operations as atomic operations. Once an error occurs,

You can roll back to the original state to ensure database data integrity.

V. Storage

The Stored Procedure contains a series of executable SQL statements, which are stored in MySQL,

By calling its name, you can execute a bunch of internal SQL statements.

Advantages of Using Stored Procedures

#1 program and data Implementation decoupling

#2 reduce the amount of data transmitted over the network

Vi. Database optimization query methods

1. Two methods to optimize the query:

(1): prefetch_related

 

(2): select_related

Selsect_releated actively connects to the table and executes the SQL statement once.

Prefetch_releated: Execute the SQL statement three times without connecting to the table.

VII. Complete Constraints

1. Set unique constraints

As follows:

Method 1: create table department1 (id int, name varchar (20) unique, comment varchar (100); Method 2: create table department2 (id int, name varchar (20 ), comment varchar (100), constraint uk_name unique (name ));

8. Database Backup

1. Backup and database recovery

Drop database day43; Delete the original folder

Show databases; view all folders create database day43; create a folder use day43; switch to the day43 folder source E: \ day43. SQL; restore Data

1. log on to MySQL for recovery:
Drop database day51;
Create database day51;
Source D: \ day51. SQL;

 

2. Directly recover from the outside without logging on to MySQL;
Mysql-uroot day51 <D :\\ day51. SQL

 

(2) restore to the specified database:
Create database bak; create a folder
Use dak; switch to the dak folder
Mysql-uroot bak <D :\\ day51. SQL; restore Data

 9. Keyword priority

From

Where

Group

Having

Select

Distinct

Order

Limit

 

1. find the table: from2. take the constraints specified by where and fetch a record from the file/table. 3. group the retrieved records by. If no group by clause is available, it is regarded as a group. obtain a new virtual table based on the select field. If there is an aggregate function, aggregate the data in the group. filter the result of 4: having. If there is an aggregate function, execute aggregation first and then having filter 6. result: select7. deduplication 8. sort results by conditions: order by9. limit the number of Display Results