MySQL common commands (ii)

1. Index classification
1. General Index
2. Unique index
3. Primary KEY index
4. Foreign KEY Index
2. Normal index
1. Rules of Use
1. You can have more than one index field in a table
2, the value of the field can be duplicated, or it can be a null value
3. Always set the query criteria field to the Index field
4. The key flag of the index field is: MUL
2. Create
1. Create index when table is created
CREATE TABLE T1 (
... ...,
... ...,
Index (ID),
Index (name));
2. Add an indexed field to an existing table
1. Syntax format
Create index index name on table name (field name);
# The index name is usually the same as the field name
3. View
1, DESC table name; --View key flag for MUL
2, show index from the table name \g;
4. Delete
Drop index index name on table name;
Attention:
Delete a normal index only one delete
3, unique index (unique)
1. Rules of Use
1. There can be more than one unique field in a table
2. The value of the unique field is not allowed to be duplicated and can be null
3. Unique key symbol is UNI
2. Create (basically equal to index creation)
1. CREATE table when creating
Unique (field name),
Unique (field name)
2. Created in existing tables
Create unique index index name on table name (field name);
3. Viewing and deleting unique indexes
DESC table name;
Show index from table name;
Drop index index name on table name;
4. Primary key index (primary key) && self-growth attribute (auto_increment)
1. Rules of Use
1. Only one primary key field in a table
2. The value of the corresponding field is not allowed to repeat and cannot be null
3. Key flag for primary key field is PRI
4. Set the field in the table to uniquely identify one record as the primary key, usually set the field of the record number in the table as the primary key
2. Create primary KEY (PRI)
1. CREATE table when creating
1. Field name data type primary key auto_increment,
2.
ID int auto_increment,
..., # Set self-growth start value
Primary key (ID)) auto_increment=10000;
2. Delete primary key
1. Delete the self-growth attribute first (Modify)
ALTER TABLE name modify ID int;
2. Delete primary key
ALTER TABLE name drop PRIMARY key;
3. Adding a primary key to an existing table
ALTER TABLE name Add primary key (field name);
5. Foreign key
1. Definition
Let the value of the current table field be selected within the range of another
2. Syntax format
Foreign key (reference field name)
References referenced table name (referenced field name)
ON DELETE CASCADE action
On UPDATE CASCADE action
3. Case
Table 1, Payment information Form (finance)
School Number name Class payment amount
1 Tong Pak Fu AID01 28000
2 points Chou Xiang AID01 20000
      
Table 2, Student information form (head teacher)
The amount of the number of the study name
1 Tong Pak-Fu 28000
2 points Chou-Heung 20000

1. Create Payment Information Form
CREATE TABLE Jftab (
ID int PRIMARY KEY,
Name Char (15),
Class char (5),
Money int
) default Charset=utf8;

INSERT INTO Jftab values
(1, "Tong Pak Fu", "AID01", 28000),
(2, "Dot Chou-Heung", "AID01", 20000),
(3, "Wish Branches", "AID01", 22000);
2. Create Student information table (from table)
CREATE TABLE Bjtab (
stu_id int,
Name Char (15),
Money int,
Foreign KEY (stu_id) references Jftab (ID)
ON DELETE Cascade
ON UPDATE cascade
);
4. Cascade Action
1, Cascade: Data cascading update
Cascade updates from a table when the primary table deletes records or updates the values of the referenced fields
2. Restrict default
1. When deleting a master table record, the primary table is not allowed to be deleted if there are associated records from the table
2. Update the same
3, SET NULL
1. When the primary table deletes a record, the reference field value from the associated record in the table is automatically set to null
2. Update the same
4. No action
On Delete no action on Update no action
Same as restrict, check foreign key restrictions immediately
5. Delete foreign keys
ALTER TABLE name drop FOREIGN key foreign key name;
1. How to view foreign key names
Show create table table name;
6. Add a foreign key to the existing table
# # will be limited by the existing data in the table
ALTER TABLE name add foreign key (reference field name)
References referenced table name (referenced field name)
ON DELETE CASCADE action
On UPDATE CASCADE Action;
7. Foreign KEY Usage rules
1, two Tables by reference field and Reference field data type to be consistent
2. The referenced field must be one of the keys, usually primary key
6. Data import
1. Function: Import the contents of the file system into the database
2. Grammar
Load Data infile "file name"
into table name
Fields terminated by "delimiter"
Lines terminated by "\ n"
3. Example
Import the contents of the/etc/passwd file into the UserInfo table under the DB2 library
TARENA:X: 1000:1000:
User name password UID number GID number
Tarena,,,:/home/tarena:/bin/bash
User Description User home directory logon permissions
/bin/false
/usr/sbin/nologin
4. Operation procedure
1. Create the corresponding table in the database
CREATE TABLE UserInfo (
Username char (20),
Password char (1),
UID int,
GID int,
Comment varchar (50),
Homedir varchar (50),
Shell varchar (50)
);
2. Copy the file you want to import into the default search path of the database
1. View the default search path for the database
Show variables like "Secure_file_priv";
/var/lib/mysql-files
2. Linux command Line input:
sudo cp/etc/passwd/var/lib/mysql-files/
3. Execute DATA import statement
Load data infile "/VAR/LIB/MYSQL-FILES/PASSWD"
into table UserInfo
Fields terminated by ":"
Lines terminated by "\ n";
5. Exercise: Import the Aid1709.csv file into the database
# CSV file between cells with, delimited
   
/var/lib/mysql-files/aid1709.csv
Ls-l Aid1709.csv
RW-------
chmod 666 Aid1709.csv

1. Create the corresponding table in the database
ID Name score Phone number class
CREATE TABLE Scoretab (
ID int,
Name varchar (20),
Score Float (5,2),
Phone char (11),
Class char (7)
) default Charset=utf8;
2. Copy the imported files to the default search path in the database
CP Source File Destination Path
cp/home/tarena/aid1709.csv/var/lib/mysql-flies/
######## the path with the TAB key #######
3. Execute DATA import statement
Load data infile "/var/lib/mysql-files/aid1709.csv"
into table Scoretab
Fields terminated by ","
Lines terminated by "\ n";

# Modify file Permissions chmod 666 aid1709.csv
7. Data export
1. function
Save records from a database table to a system file
2. Syntax format
Select ... from table name
into outfile "file name"
Fields terminated by "delimiter"
Lines terminated by "\ n";
3. Export the username, password, and UID from the UserInfo table to a file user.txt
Select Username,password,uid from UserInfo
into outfile "/var/lib/mysql-files/user.txt"
Fields terminated by ","
Lines terminated by "\ n";
      
1, Sudo-i
2, cd/var/lib/mysql-files/
3. Cat User.txt
4. Attention
1. The contents of the export are determined by the SQL query statement
2. The path must specify the corresponding database search path when executing the export command
8. Copy of Table
1. Syntax format
CREATE TABLE table name select query command;
2. Example
1. Copy all the records in the UserInfo table, Userinfo2
CREATE TABLE Userinfo2 select * from UserInfo;
2. Copy the 第2-10条 record of Username,password,uid three fields in the UserInfo table, Userinfo3
CREATE TABLE Userinfo3 Select Username,password,uid from UserInfo limit 1, 9;
3. Duplicate table structure
CREATE TABLE table name select query command where false;
4. Attention
Copying a table does not copy the key property of the original table.
9. Nested query (subquery)
1. Definition
The query result of the inner layer as the outer query condition
2. Example
1, the UID value is less than the UID average user name and UID number display
Select Username,uid from UserInfo
Where UID < (select AVG (UID) from userinfo);
10. Connection Query
1. Internal connection
1. Definition
Delete rows from the table that are not matched to other connected tables
2. Syntax format
List of select field names from Table 1
Inner JOIN table 2 on condition INNER JOIN table 3 on condition;
3. Example
1, show the details of the provinces and cities
Select Sheng.s_name,city.c_name from Sheng
Inner join city on sheng.s_id=city.cfather_id;
2, show the provinces and counties detailed information
Select Sheng.s_name,city.c_name,xian.x_name from Sheng
Inner JOIN City on sheng.s_id=city.cfather_id
Inner join Xian on city.c_id=xian.xfather_id;
2. External connection
1, left JOIN connect
1. Definition
Display query results in the left table primarily
2. Syntax format
List of select field names from Table 1
Left JOIN table 2 on condition;
3. Example
1, the province table mainly displays the provinces and cities detailed information
Select Sheng.s_name,city.c_name from Sheng
Left join City on SHENG.S_ID=CITY.CFATHER_ID;
2, show the provinces and cities detailed information, the county is required to show all
Select Sheng.s_name,city.c_name,xian.x_name from Sheng left join City
On sheng.s_id=city.cfather_id
Right join Xian on city.c_id=xian.xfather_id;
3, show the provinces and cities detailed information, demand the city show all
Select Sheng.s_name,city.c_name,xian.x_name from Sheng
Right join City on sheng.s_id=city.cfather_id
Left join Xian on city.c_id=xian.xfather_id;
# # # # result set # # #
2. Right connection
Use the same left JOIN, the right table is the main display query results
11. Multi-Table Query
1, select field Name list from table name list; # Cartesian product
2. Select field List from table name list where condition;
Equivalent to inner join inner join

MySQL common commands (ii)