Parisgabriel every day to write one day a decision to persist for a few years for the dream to start a picture of faithtable field Rename (change)ALTER TABLE name change formerly known as new name data type;SQL queryExecution Order:3. Select ... The aggregate function from table name 1. where ... 2. Group BY ... 4. Having a ... 5. Order BY ... 6. Limit ... Group byStatementfunction:To the query resultsGroupingNote:1.after group byOffield must beForafter selectOfFieldName 2.ifAfter the Select field and group byField Inconsistency, youmust beThe fieldfor aggregation processing(aggregate function) havingStatementfunction: For query resultsfurther screeningNote:1. havingStatements typically andGROUP by combined use，FilterByGroup byStatementthe recordset returned2.where can only be manipulatedIn the tableactualExistence ofField，have by can manipulate the display columns generated by the aggregate functiondistinctfunction: not displayShow FieldsDuplicate ValueNote:Between 1.distinct and fromall fields are the samewill go to heavy2.distinctcannot be on any fieldDo the aggregationprocessingOperator: + - * / %# # sudo apt-get install PYTHON3-PIP installation PIP3# # sudo pip3 install pymysql installation MySQLconstraints:1. Role：Guaranteed DataOfFullOfconsistentOfValidity2. Constraint classification1. Default Constraints（default）Insert FieldWhenNoto the fieldAssign ValueTheUse default values2. Non-null constraints（Not NULL）The field value is not allowed to have a null recordsex enum ("M", "F", "s") not null Defaulf "s"IndexDefinition:to the databaseOne or more columns of a table colorvalues to be sorted.Astructure(btree mode)
Advantages:Accelerateof the dataRetrieval SpeedDisadvantages:1. Needoccupy physical storage space2. When the data in the table is updated, the index needsDynamic Maintenance。reducedDataMaintenance SpeedConsume system resourcesRun time Detection: Open:set profiling=1; Close:set profiling=0; query MySQL variable:show variables like profiling; query execution record:show profilings; field to create an index:Create index name on T1 (field name);Classification of indexes: 1. Normal index usage Rules:1. Multiple fields can be set 2. Field valuesno constraint3.key Logo:MULCREATE INDEXCreate table when creatingCREATE TABLE (...).Index(field name),Index(field Name 2) ... ）existing table added indexCreate index index name on table name (field name); View Index:1.desc table name;Key:mul2.show index from table name3.show index from\g;To delete an index: Drop index index name on table name;2. Unique index (unique)usage Rules:1. You can set multiple fields2. Constraint: Field values are not allowed to repeatButcan be null3.key Logo:UNICreate a unique:1. Create a table when creatingUnique (field name),..2. Existing tablesCreate unique index index name on table name (field name);View, deleteconsistent with normal indexes3. Primary key index (primary key)Self-increment property（auto_increment，Mate primary KeyTogetherUse）usage Rules:1.there can only be onePrimary key Field 2.constraint: Duplicate not allowedAndcannot be null3.key Logo:PRI4.usually setRecord Number fieldIDYesUnique LockA pieceRecordCreate primary KeyWhen creating a table: 1.ID int primary key auto_increment,2.Starting value: Table ()auto_inctement=10000;Tables already available:ALTER TABLE name add PRIMARY key (ID);add: ALTER TABLE name modify ID int auto_inctement;Delete:1. Remove the self-increment property(Modify)ALTER TABLE name modify ID int;2. Delete primary key indexALTER TABLE name drop primary key;4. Foreign key index ..... The algorithm is allBtreeSaving time is just as different as constraining differenthere Btree algorithm Some people say that Btree is btree is not a binary tree but I think it is a binary tree no differencespeeds up to hundreds of times faster depending on the size of the data volumeData import:function:Import the contents of the file system into the database Syntax: load data infile "file name"into table nameFields terminated by "delimiter" lines terminated by "\ n";steps:1.DatabaseInCreate a correspondingOfTable2.Copy the file toof the databaseDefault search PathIn1. View the default path Show variables like "Secure_file_priv";/var/lib/mysql-files/2. Copying Filessudo cp ~/scoretable.csv/var/lib/mysql-files/3. Import tables into the databaseData export:function:Records of tables in the database are exported to system filesSyntax:Select ... from table nameInto outfile "/var/lib/mysql-files/file name"Fields terminated by "delimiter"lines terminated by "\ n";steps:1. Directly execute the Export command2. Automatically create files3. Export to default search path by default file permissions:rwxrw-rw-1 Tarena Tarena owner-owned group Rwx:tarena user rw-: Other users in the same group rw-: other groups of users (MySQL) R:4 w:2 x:1 Maximum privileges: 7View permissions: Ls-l file nameModify file Permissions:chmod644 File NameExcelHow does the tableTurnIntoCSV file Open Excel file, save as CSV (comma delimited)Change fileencoding FormatOpen with Notepad/editor, save as file--Select encoding Import Example:
Import a scoretable.csv file into a table in the database1, creating the corresponding table in the databaseCreate TableScoretab (IDint, namevarchar( the), scorefloat(5,2), Number bigint, ClassChar(7) ); 2, copy the file to the default search path in the database1, viewing the default search path show variables like"Secure_file_priv"; /var/Lib/Mysql-Files/ 2, copy file sudo cp~/Scoretable.csv/var/Lib/Mysql-Files/ 3, execute data import statementLoadData InFile "/var/Lib/Mysql-Files/scoretable.csv " into Tablescoretab Fields Terminated by"," lines terminated by"\ n";
Sample guide:
The name of the Sanguo table hero under the Moshou Library, the attack value, the country export, Sanguo.txtSelectName,gongji,country fromMoshou.sanguo intoOutFile "/var/Lib/Mysql-Files/sanguo.txt "Fields terminated by"" Lines terminated by"\ n"; In the user table under MySQL libraryUser, the value of the host two field is exported to aUser. txtSelect User, host fromMysql.User intoOutFile "/var/Lib/Mysql-Files/User. txt "Fields terminated by"" Lines terminated by"\ n"; query $ sudo-I $ cd/var/Lib/Mysql-Files/$ ls $ cat sanguo.txt
Python Full stack MySQL database (index, data import, export)
