MySQL database (python), mysql database python

MySQL database (python), mysql database python

Installation tutorial original web site http://www.cnblogs.com/iscodercn/p/5488633.html
MOOC video tutorial Summary Show create table provinces; view encoding, EngineInteger: tingint-> smallint-> mediumint-> int-> bigint;

Floating point type: float (M, D) double (M, D) [total number of digits of m d after decimal point]

Date: time: +-8385959 data: 1000, 1, 1-9999, 23, 59

timestamp：1970-2037               year：1970-2069

Use t1; use the database show database (); view the used database desc tb1; view the table structure to view the content, select;

AUTO_INCREMENT automatic number,And must be used together with the primary keyBy default, the start value is 1, and each increment is 1.
Primary key is used to make the value unique, that is, the value cannot be repeated,If the primary key is not set to auto-increment, you must manually add the number.
Primary key a data table can have only one unique key and can be empty or UNIQUE,
The content in the unique key cannot be repeated. For example, if the username in the table already has a "tom", you cannot add another 'Tom'
Constraints guarantee integrity and uniqueness
Primary key constraint: primary key is similar to UNIQUE constraint of id card id: unique key ensures unique default value of Registered User name: DEFAULT is male non-empty constraint: not null foreign key constraint: foreign key implement one-to-many

Foreign key:
Mysql> create table provinces (-> idSMALLINT UNSIGNEDPrimary key AUTO_INCREMENT,-> pname VARCHAR (20) not null->); mysql> create table users (-> id smallint unsigned primary key AUTO_INCREMENT,-> username VARCHAR (20) not null,-> pidSMALLINT UNSIGNED,-> Foreign key (pid) REFERENCES provinces (id) // pid foreign key column, id reference column, type must be the same-> );
The parent table is written first. For the word table, see
The essence of foreign key constraints is to increase the reusability of data. That is to say, if we have a province table: P, the table stores the names of 34 provinces in China and has a unique number constraint (for example, 1. henan 2. sichuan 3. guangdong 4. jilin Province ......); Now there is A table A for counting the GDP of each province, A table B for counting the population of each province, and A table C for counting the population of each province. If you enter the names of the provinces again, you need to enter the names of the provinces four times, this kind of work is like a comment area. Some people who stick their hands in the crotch and do not perform any operations will surely feel very troublesome. For these people, MySQL has developed a very awesome method: from the P table, directly reference the serial numbers of provinces in Table A, table B, and Table C, in this way, the information in table P is shared by tables A, B, and C, so that the information is input and used permanently.
Table-level constraints and column-level constraints are determined by the number of operations. A column-level constraint can be declared when a column is defined or after a column definition. A table-level constraint can only be declared after a column definition.
Add multiple columns: alter table users1 add (tt varchar (10) not null, ll tinyint not null );

ADD a foreign key constraint: alter table users2 add foreign key (pid) REFERENCES provinces (id );
Add DEFAULT constraint: alter table users2 ALTER age set default 15;
Delete DEFAULT constraint: alter table users2 ALTER age drop default;
Modify a data table: add, delete, add, and delete columns. Use ALTER, while INSERT is used to add INSERT records to the data table.

Delete primary key constraint: alter table tbl_name drop primary key Delete unique constraint: alter table tbl_name DROP {INDEX | KEY} index_name Delete foreign key constraint: alter table tbl_name drop foreign key fk_symbol

The name of the data TABLE is alter table user2 RENAME user3; or rename table user2 TO user3.
MODIFY the column definition. <br> alter table tableName MODIFY columnName smallint unsigned not null first; <br> alter table tableName CHANGE oldColumnName newColumnName int not null after username; <br>
Less modification is created, and the structure is damaged.
Constraint: The not null non-null constraint and default constraint have no table-level constraint and only column-level constraint. The primary key constraint and the unique constraint of the unique key and the foreign key constraint of the foreign key are both table-level and column-level constraints.
Record operation: curd

Section

• The database system solves the following problems: Persistent storage, optimized read/write, and ensured data validity.
• First design the table structure

E (table)-R Model (entity vs relation) Three paradigm

• 1NF: Columns cannot be split-name attributes cannot be split into surname and name
• 2NF: Unique identifier -- id
• 3NF: reference a primary key -- id is based on the primary key

To store data more accurately and ensure that the data is correct and valid, you can add some mandatory verification for the table when creating the table, including the type and constraints of data fields.1. Field Type-Data Validity

• Mysql contains many data types, which are mainly listed below
• Number: int, decimal
• String: char (fixed text, not filled with spaces: Phone), varchar (variable finite text: Name), text (large text)
• Date: datetime (to seconds), date (to days)
• Boolean: bit (3) -- use 0, 1 to indicate gender, with low overhead
• Logical Deletion: 0, 1Important data

2. constraints-Restrictions

• Primary key -- the query speed is fast. Only one table can be found. It is used as a unique id ??? Automatic Growth
• Non-null not null -- name cannot be blank
• Unique-unique ID card
• Default -- default
• Foreign key --

A. Graphical interface ( PhpMyAdmin, navicat)IP address +... Root

Navicat Premium is a set of multi-Connection database development tools that allow you to connect to up to six types of databases simultaneously in a single application: MySQL, MariaDB, SQL Server, SQLite, Oracle, and PostgreSQL, you can quickly and conveniently access all databases at a time.

Reserved columns for Database Design for later modification Mysql directory:

Bin directory storage executable file data directory storage data file docs directory file include directory storage contains header file lib directory Storage Library File share directory error message and Character Set

Statement specification:

Keywords and function name Big write; database, table, and field names in lower case; SQL language end with a semicolon.

Run the cmd command to start or close the service.

Start net start mysql and exit: exit, quit, or \ q

Disabling net stop mysql win other system services also applies to the preceding commands

---- Cmd control to start and stop the MySQL service, or start it in windows Management Service

Cls clears MySQL Logon: mysql-v; (version information); mysql-uroot-p-P3306-h (provided by the server name) 127.0.0.1 (local loopback address ), if the last two parameters are not changed, do not add them. Press enter and enter the password. MySQL exit: mysql> exit; quit; \ q up and down arrows to read

B. Script operation

Go to mysql: mysql-uroot-proot mysql

Password: root

Enter way 2:
Mysql-uroot-p password: root // user name displayed in the utable

Connection test: root/

Remote connection:
Mysql-hip address-uroot-p // you can change the address to an ip number.
    
    

     
     
-H: Enter the IP address of the host to be connected.
     
     
-Write the connection username after u
     
     
-PEnterWRITE password later
    
    
         

• Generally, an isDelete column is set for important data. The column type is bit, indicating logical deletion (protecting data)

3. Data Operations

• Query

Select * from Table Name

• Add

Insert all columns: insert into table name values (...) // values (0, 'Li chun', 1, '2017-1-1 ', 0) correspond to the table structure one by one. Default insertion: insert into Table Name (column 1 ,...) values (value 1 ,...) // (name, gender) values ('yellow Rong ', 0) insert multiple data records simultaneously: insert into table name values (...), (...)...; or insert into Table Name (column 1 ,...) values (value 1 ,...), (value 1 ,...)...; // (name) ('yangge'), ('Guo yun ')

• Primary Key columns are automatically increased, but the placeholder is required during full column insertion. Generally, 0 is used. After successful insertion, the actual data prevails.
• Modify

Update table name set column 1 = value 1,... where Condition

• Delete

Delete from table name where Condition

• Logical deletion. The essence is to modify the update operation.

Alter table students add isdelete bit default 0; update students isdelete = 1 where ...;

Data backup

• Go to super Administrator

sudo -s

• Go to the mysql database directory

cd /var/lib/mysql

• Run the mysqldump command

Mysqldump-uroot-p Database Name> ~ /Desktop/backup file. SQL; enter the mysql password/password as prompted.

Data Recovery-enter the backup folder

• Connect to mysqk and create a database

• Exit the connection and execute the following command:

Mysql-uroot-p database name <~ /Desktop/backup file. SQL enter the mysql password as prompted

Section B: Query (90%)

• Basic query syntax

Select * from table name;

• The from keyword is followed by the table name, indicating that the data source is
• The column name in the table is written after the select statement. If it is *, all columns in the table are displayed in the result.
• In the column name section after select, you can use as to create an alias for the column. This alias appears in the result set.
• If you want to query multiple columns, use commas to separate them.

Eliminate duplicate rows

• Duplicate rows can be eliminated by using distinct before columns after select.

select distinct gender from students;

This row property is compared with other rows
Condition

• Use the where clause to filter data in a table. Rows with true results will appear in the result set.
• Syntax:

Select * from table name where condition;

• Query students whose IDs are greater than 3

select * from students where id>3;

• Query students not deleted

select * from students where isdelete=0;

Logical operators

• And
• Or
• Not
• Query female students whose IDs are greater than 3

select * from students where id>3 and gender=0;

Fuzzy search

• Like
• % Represents any number of arbitrary characters
• _ Represents any character
• Query the students surnamed Huang

Select * from students where sname like 'yellow % ';

Range Query

• In indicates that it is in an discontinuous range.
• Query students numbered 1, 3, or 8

select * from students where id in(1,3,8);

• Between... and... indicates that it is in a continuous range.
• Query students ranging from 3 to 8

select * from students where id between 3 and 8;

Null judgment

• Note: null and ''are different/one does not occupy memory, and the other occupies a null string.
• Null is null
• Query students without an address

select * from students where hometown is null;

• False is not null
• Query the students whose addresses are filled in

select * from students where hometown is not null;

Section C: Aggregation -- for Statistics

• Five Aggregate functions are provided to quickly obtain statistics.
• Count (*) indicates the total number of calculated rows. The stars and column names are written in parentheses and the results are the same.
• Query the total number of students

select count(*) from students;

• Max (column) indicates the maximum value of this column.
• Query the maximum number of a girl

select max(id) from students where gender=0;

• Min (column) indicates the minimum value of this column.
• Query the minimum student ID that has not been deleted

select min(id) from students where isdelete=0;

• Sum (column) indicates the sum of the column
• After querying the boys' numbers

select sum(id) from students where gender=1;

• Avg (column) is used to calculate the average value of this column.
• Query the average number of girls not deleted

select avg(id) from students where isdelete=0 and gender=0;

Group

• Grouping by field indicates that data with the same field will be put into a group.
• Only the same data columns can be queried After grouping. Different data Columns cannot appear in the result set.
• You can make statistics on the grouped data and perform aggregation operations.
• Syntax:

Select column 1, column 2, aggregation... from table name group by column 1, column 2, column 3...

• Query the total number of male and female

Select gender as gender, count (*) from studentsgroup by gender;

• Query the number of users in each city

Select hometown as hometown, count (*) from studentsgroup by hometown;

Data Filtering After grouping

• Syntax:

Select column 1, column 2, aggregation... from table name group by column 1, column 2, column 3... having column 1,... aggregation...

• The conditional operators after having are the same as those after where.
• Query the total number of boys

Solution 1 select count (*) from studentswhere gender = 1;

Sort

• You can sort the data for ease of viewing.
• Syntax:

Select * from table name order by column 1 asc | desc, column 2 asc | desc ,...

• Sort row data by column 1. If the values of Column 1 are the same, they are sorted by column 2, and so on.
• Sort by column value from small to large by default
• Asc is arranged in ascending order.
• Sort desc in descending order
• Query Information about boys and students not deleted, in descending order by student ID

select * from studentswhere gender=1 and isdelete=0order by id desc;

• Query undeleted account information, in ascending order by name

select * from subjectwhere isdelete=0order by stitle;

Paging

• When the data volume is too large, it is very troublesome to view the data on a page.
• Syntax

Select * from table name limit start, count

• Obtain count data records starting from start
• Start index starts from 0

Summary

• Complete select statement

Select distinct * from table name where... group by... having... order by... limit star, count

• The execution sequence is as follows:
  • From table name
  • Where ....
  • Group...
  • Select distinct *
  • Having...
  • Order...
  • Limit star, count
• In actual use, it is only a combination of some parts of the statement, not all

Connection Query

• The connection query category is as follows:
  • Table A inner join Table B: the row that matches table A and table B appears in the result.
  • Table A left join Table B: the row matching table A and table B appears in the result, and the data exclusive to Table A is added. No corresponding data is filled with null.
  • Table A right join Table B: the row matching table A and table B appears in the result, and the data exclusive to table B is added. No corresponding data is filled with null.
• We recommend that you use the "table name. Column name" syntax in queries or conditions.
• If the column names of multiple tables are not repeated, You can omit the "table name." section.
• If the table name is too long, you can use 'as short name' or 'short name' after the table name to start a temporary abbreviated name for the table.

Section D: Excellent Courses for using python to operate Mysql Databases

Connection (database connection object), cursor (Database interaction object), and excptions (Database exception class) of Python db api (unified interface specification)

pip install PyMySQL

Connection:

Import pymysqlconn = pymysql. connect (host = "127.0.0.1", port = 3306, user = "root", passwd = "", db = "imook", charset = "utf8") cur = conn. cursor () print (conn) print (cur) cur. close () # Remember to close conn. close ()

--- Do not use the import package name as the file name!

Create a Cursor on the connection to query and obtain results.

If it is just fetchall, I usually directly traverse it after cursor.exe cute (SQL) without executing fetchall. The effect is the same.
For row in cursor:
Print row

Errors in rolling back objects can be recovered. If you want to allow data tables to support transactions by bank transfers, you need to setENGINE = INNODB

Insert, update, and delete. Add conn. commit () to update the database.

Top

0

Step on

0

View comments