ParisgabrielStick to Handwriting day by day a decision to hold on for a few years for a dream to start a picture today then yesterday's said index has 4 kinds: Normal index:
IndexUnique index:
UniquePrimary KEY index:
PRIMARY KeyFOREIGN KEY index:
FOREIGN KeyIndex query command: Show index from table name \g; non_unique:1: Index non_unique:0: Unique
FOREIGN key Index (foreign key):
definition: Let the current fieldThe value
in another tableThe scope
Internal Selection
Syntax:
foreign key(Reference field name)
references primary table (referenced field name)
On Delete-level linkage
On update level linkage
usage Rules:
Main Table、
from the tableField
data types are consistentMain Table
be consultedThe fields are generally:
PRIMARY Key
To Delete a foreign key:
ALTER TABLE name drop foreign key foreign key name;
FOREIGN Key Name query: Show create table table name;
Cascade Action:
CascadeCascade
Delete, update(Reference fields only)
RestrictDefault
from the tableHave related
Record，
do not allow primary table operations
Set NULL
primary table Delete, update，
from the tableAssociated Records
field value is null
a foreign key has been added to a table:
ALTER TABLE name add
foreign key(reference field) reference Main table (referenced field)
On Delete ...
On update ...
Replication of TablesCopy table:
CREATE TABLE table name Select ... from where table name;Table structure:
CREATE TABLE name select * FROM table name where false;Attention:
Copying TablesThe time
won't putof the original table
keys (key)Properties copied over
SQL Query (Advanced)
Nested Query (sub-query):
definition：
putInner layer
Query results asThe outer
Query Criteria
syntax Format:
Select ... from table name where condition (select ... );
Multi-table query:
two different ways1.
List of Select field names from List of table names ( Cartesian product )
SELECT * from T1,t2
select T1.name,t2.name from T1,t2;2.
Select T1.name,t2.name from T1,t2
where condition
link query:
1. Links within
Select field name from Table 1
inner join table 2 on condition
inner join table 3 on condition ... ;
2. External links
1. Left link
display query results in the left table primarily
Select field name from Table 1
Left join table 2 on condition
Left join table 3 on condition ... ;
2. Right link
display query results in the right table primarilySelect field name from Table 1
RightJoin table 2 on condition
RightJoin table 3 on condition ... ；
Data backup:
mysqldump Operating on Linux endpoints
Full backup:
MySQLdump -u user-P Source Library name > ~/xxx.sql
--all-databases backing up all libraries
Library name back up a single library
- b Library 1 Library 2: Backing up multiple libraries
Library name table 1 table 2... To back up the specified library specified table
Data recovery:
recovering a single library
Mysql-uroot-p < Target library name Xxx.sql
recover a library from all library backups (-one-database)
mysql-uroot-p--one-database Target library name < Xxx.sql
Note:1.
RecoveryLibrary
whenIf
revert to original libraryWill be
in the tableOf
Data Coverage，
new table does not delete2. When data is restored
If the recoveryOf
Library does not exist, you
You must first create an empty library
MySQL account management:
1. Turn on MySQL remote connection
sudo-i
cd/etc/mysql/mysql.conf.d/
Subl mysql.cnf
#bind-address = 127.0.0.1 commented out
/etc/init.d/mysql Restart
2. Add an authorized user
log in to MySQL with the root user
mysql-uroot-p123456
Authorization:
Grant authorization list on library. Table to "user name" @ "%"
identified by "password" with grant option
Permissions List:
All privileges, select, insert
Library. Table: * * all libraries All tables
Example:
1. Add the authorized user Tiger, password 123, all the tables in all libraries have all permissions
Grant all privileges on * * to ' tiger ' @ '% ' identified by ' 123 ' with GRANT option;
Comprehensive Exercises:
Summary: Two tables, one customer information table customers, one order form orders
1, create a Customer information table customers, the field requirements are as follows:
C_ID type is integer, set as primary key, and set as self-growing property
C_name character type, variable length, width 20
C_age micro-integer with a value range of 0~255 (unsigned)
C_sex enumeration type, requires only one value to be selected in (' M ', ' F ')
c_city character type, variable length, width 20
C_salary floating-point type, which requires an integer portion up to 10 bits and a fractional part of 2 bits
Insert 3 records in the table, C_name "Zhangsan", "Lisi", "Wangwu", c_city try to write "Beijing", "Shanghai" ...
INSERT INTO customers values
(1, "Zhangsan", +, "M", "Beijing", 8000),
(2, "Lisi", +, "F", "Shanghai", 10000),
(3, "Wangwu", "M", "Shenzhen", 3000);
2, create an order form orders, the field requirements are as follows:
o_id Integral type
O_name character type, variable length, width 30
O_price floating-point type, integer maximum is 10 bits, fractional part 2-bit
Set the O_ID field in this table as the foreign key for the c_id fields in the Customers table, update delete synchronization
Arbitrarily inserting 5 records in a table (note unexpected key limit)
O_name respectively for "iphone", "ipad", "iwatch", "Mate9", "R11", Other information
INSERT INTO orders values
(1, "iphone", 5288),
(1, "ipad", 3299),
(3, "Mate9", 3688),
(2, "iwatch", 2222),
(2, "R11", 4400);
3, return to the Customers table, pay more than 4000 yuan, or age less than 29 years old, meet such conditions of the first 2 records
4, the Customers table, the age is equal to 25 years old, and the address is Beijing or Shanghai, such people's wages increase 15%
5, the Customers table, the city for Beijing's customers, in descending order of wages, and only return the results of the first record
6. Select the information of the customer with the lowest wage c_salary
7. Find out the record details of which products have been purchased by customers with a salary greater than 5000
8. Remove FOREIGN Key restrictions
9. Remove Customers primary key limit
1. Delete the self-growth attribute
2. Remove the primary key limit
Python Full stack MySQL database (SQL query, backup, restore, Authorization)
