Parisgabriel every day to write one day a decision to persist for a few years for the dream to start a picture of faith
table field Rename (change)
ALTER TABLE name change formerly known as new name data type;
SQL query
Execution Order:3. Select ... The aggregate function from table name 1. where ... 2. Group BY ... 4. Having a ... 5. Order BY ... 6. Limit ...
Group byStatement
function:To the query results
Grouping
Note:1.
after group byOf
field must beFor
after selectOf
FieldName 2.
ifAfter the Select field and group by
Field Inconsistency, you
must beThe field
for aggregation processing(aggregate function)
havingStatement
function: For query results
further screening
Note:1.
havingStatements typically and
GROUP by combined use，
FilterBy
Group byStatement
the recordset returned2.
where can only be manipulatedIn the table
actualExistence of
Field，
have by can manipulate the display columns generated by the aggregate function
distinct
function: not displayShow Fields
Duplicate Value
Note:Between 1.distinct and from
all fields are the same
will go to heavy2.distinct
cannot be on any fieldDo the aggregation
processing
Operator:
+ - * / %
# # sudo apt-get install PYTHON3-PIP installation PIP3
# # sudo pip3 install pymysql installation MySQL
constraints:
1. Role：
Guaranteed DataOf
FullOf
consistentOf
Validity
2. Constraint classification
1. Default Constraints（default）
Insert FieldWhen
Noto the field
Assign ValueThe
Use default values
2. Non-null constraints（Not NULL）
The field value is not allowed to have a null record
sex enum ("M", "F", "s") not null Defaulf "s"
Index
Definition:
to the databaseOne or more columns of a table color
values to be sorted.A
structure
(btree mode)
Advantages:
Accelerateof the data
Retrieval Speed
Disadvantages:1. Need
occupy physical storage space2. When the data in the table is updated, the index needs
Dynamic Maintenance。
reducedData
Maintenance Speed
Consume system resources
Run 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 values
no constraint3.key Logo:
MUL
CREATE INDEX
Create table when creatingCREATE TABLE (...).Index(field name),Index(field Name 2) ... ）
existing table added index
Create index index name on table name (field name);
View Index:
1.desc table name;Key:mul
2.show index from table name
3.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 fields
2. Constraint: Field values are not allowed to repeatBut
can be null3.key Logo:
UNI
Create a unique:1. Create a table when creating
Unique (field name),..2. Existing tables
Create unique index index name on table name (field name);View, delete
consistent with normal indexes
3. Primary key index (primary key)
Self-increment property（auto_increment，
Mate primary KeyTogether
Use）
usage Rules:1.
there can only be onePrimary key Field 2.
constraint: Duplicate not allowedAnd
cannot be null3.key Logo:
PRI4.
usually setRecord Number field
IDYes
Unique LockA piece
Record
Create 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 index
ALTER TABLE name drop primary key;4. Foreign key index ..... The algorithm is all
BtreeSaving 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 volume
Data import:
function:
Import the contents of the file system into the database
Syntax:
load data infile "file name"
into table name
Fields terminated by "delimiter"
lines terminated by "\ n";
steps:1.
DatabaseIn
Create a correspondingOf
Table2.
Copy the file toof the database
Default search PathIn
1. View the default path
Show variables like "Secure_file_priv";
/var/lib/mysql-files/
2. Copying Files
sudo cp ~/scoretable.csv/var/lib/mysql-files/
3. Import tables into the database
Data export:
function:
Records of tables in the database are exported to system files
Syntax:
Select ... from table name
Into outfile "/var/lib/mysql-files/file name"
Fields terminated by "delimiter"
lines terminated by "\ n";
steps:
1. Directly execute the Export command
2. Automatically create files
3. Export to default search path by default
file permissions:
rwx
rw-
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: 7
View permissions: Ls-l file nameModify file Permissions:
chmod644 File Name
ExcelHow does the table
TurnInto
CSV file
Open Excel file, save as CSV (comma delimited)Change file
encoding Format
Open 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)
