Python's Operation MySQL database

I. MySQL database start-up and login

Installation here will not elaborate. Remember to configure the installation path into the environment variable after the installation is complete. Then go to the command line to start the database. Note: When theMySQL database installation is complete, the default is enabled.

• Open MySQL database, note: The service name entered here is the service name set at the time of installation,MySQL5.7 version of the default service name is MySQL57
  

• Close MySQL database service

• Log in to MySQL database

two. Build a table

• Display Database

show databases;

• Create a database

Create DATABASE Lianxi DEFAULT Character Set UTF8

• Working with databases

Use Lianxi;

• Multi-table one to many

CREATE TABLE user_info (ID int not NULL auto_increment primary Key,name char (a), Age Int,gender char (1), deparment_id Int,co Nstraint Constraint name Foregin key (deparment_id) references Dep_info (id)) engine = InnoDB default Charset=utf8;create table Dep_inf O (id int not NULL auto_increment primary Key,title char (+),) Engine=innode default Charset=utf8;

• Multiple-to-many tables (relational tables)

CREATE TABLE Boy (ID int. NOT NULL auto_increment primary key,name char) engine = InnoDB default Charset=utf8;create tabl e Girl (id int not NULL auto_increment primary key,name char) engine = innodb default Charset=utf8;create table b2g (ID I NT NOT NULL Auto_increment primary key,b_id int,g_id int,constraint constraint name 1 foregin key (b_id) references Boy (ID), constraint Constraint name 2 Foregin key (g_id) references girl (ID)) engine = InnoDB default CharSet = UTF8;

Application Scenarios:
1, according to the specific business
2, the scene of the HTML
When you create it, you only need to enter the data: Single table
Create a scenario where we have to select a different data scene: one-to-many
When you create multiple selections for some fields: many-to-many

• Deleting a database

Drop database db_name;

three. Adding and deleting changes

• Insert:

INSERT INTO table_name (field) VALUES (value), (value2);--> Two (value) represents interpolation as multiple lines of data INSERT INTO table_name (CNAME) SELECT field from table_name; --Assign the result of select to value as data

• Clear table

Delete from table_name; --The self-increment column will continue before the Idtruncate table table_name;-Physical Delete, fast, recalculate ID

• Delete a bar

Delete from table_name where filed = values and/or ...  --only delete the data that meets the criteria delete from table_name where filed in (1,2,3,4);  

• Modify

UPDATE table_name SET field = ' value '; Update the value of the field field for all data, plus where only modify matching to the row update table_name Set id = 8, name = ' Daxi n ' where age = 18;

• Inquire

SELECT * FROM table_name where ID > 2;select field as ' Alias ' from table_name and alias SELECT * FROM table_name where ID In (n); select * FROM table_name WHERE CID in (select Tid from teacher);

• Conditional query

SELECT * FROM table_name ORDER BY field ASC/DESC (positive order/reverse) SELECT * FROM table_name ORDER BY field ASC Limit 1 Take first value SELECT * F ROM table_name limit (starting position, looking for several) SELECT * FROM table_name where field like '%key% '-Find field fields containing key data% represents any arbitrary character, _ Represents any one character

• Group

SELECT * FROM TABLE_NAME Group BY field-to-group display, deduplication, need to use aggregate functions to count the number of repetitions select Field,count (ID) from the TABLE_NAME group by FI ELD---The ID field is aggregated (others are min (), Max (), SUM (), AVG (), etc.)

• Example

1, how many people to get each class

SELECT Class.caption,count (SID) from Classleft join student on student.class_id = Class.cid GROUP by class.caption;

2, how many people in each class and choose to know more than 2 of the class

Note: If you filter on the results of group BY, you need to use the having and cannot be used where.

SELECT Class.caption,count (SID) as number from Classleft join student on student.class_id = Class.cidgroup by class.captio nhaving number >= 2;

3. Number of failed courses per course

Select Course.cname,count (SID) from Scoreleft join course on score.corse_id = course.cidwhere number < 60group by cours E.cname;

four. Even table

Select Student.sid,student.sname,class.caption from student left JOIN class on student.class_id = Class.cid;
The field in the class table is placed on the left side of the student table, and is displayed after student.class_id = Class.cid match, and the amount of data is based on the table specified in the From
left join:

right join:--infrequently used
to The table behind the join is displayed as a datum.
inner join: (Join IS using)
Keep only entries with data in a single table
example:
1, id= 1 teachers teach the course name
2, teacher's name Blind cat teaching Course command
3, Selected course id=1, name of all students
4, selected physical education, all students ' names
5, selected Puerto to teach any course, all students name

Union
Combine the results of two SQL (merge up and down)
SELECT * FROM Student
Union/union All
SELECT * from teacher;
Note the number of columns in the top and bottom two tables should be uniform
Attention:
1. If all the data is consistent, then Union will take the results
2, union All, will save all

Five. Basic data Types

MySQL's data types are broadly divided into: values, time, and strings.
Number:
integer
tinyint small integers, data types are used to hold some range of integer numeric ranges.
smallint
int
bigint
decimal
float Float type ( the longer the less accurate)
Double floating point (dual precision, slightly higher precision than float) range larger than float
decimal precision (stored internally using strings), suitable for precision requirements
string
Char (19) [character length] fixed length string--takes up space, but high efficiency
varchar (19) [character length] variable length string--Occupy space is mutable, but inefficient
Note: Maximum of 255 characters can be stored
text () 65,535 character (s)
Mediumtext () 16,777,215 character (s)
Longtext () 4,294,967,254 character (s)
Binary :
Tinyblob
Blob
Mediumblob
Longblob
Save file: Although it can be stored in binary, it is generally the path (URL) where the file is stored on the server
Time:
Date Yyyy-mm-dd
Time HH:MM:SS
Year YYYY
DATETIME yyyy-mm-dd HH:MM:SS--Frequently used
TIMESTAMP timestamp Format

　　

Python's Operation MySQL database