MySQL database import and export query modify table record

MySQL data import and export:

Import:
Save the contents of the system file to the table in the database

Basic format for importing data:
mysql> load Data infile "file name" into table name fields terminated by ' delimiter ' lines terminated by ' \ n ';

Example: Save the System user information to the UserInfo table under the HYDRA01 library
Mysql> CREATE TABLE UserInfo (name char), password char (1), UID Int (2), GID Int (2), comment varchar (), Homedir varchar, Shell varchar (+), index (name));
mysql> load Data infile "/etc/passwd" into table Hydra01.userinfo fields terminated by ":" lines terminated by ' \ n '; (Import Data
Mysql> ALTER TABLE userinfo add ID int (2) Auto_increment primary key first; (add number)
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | Int (2) | NO | PRI | NULL | auto_increment |
| name | char (20) | YES | MUL | NULL | |
| password | char (1) | YES | | NULL | |
| UID | Int (2) | YES | | NULL | |
| GID | Int (2) | YES | | NULL | |
| Comment | varchar (50) | YES | | NULL | |
| Homedir | varchar (60) | YES | | NULL | |
| Shell | varchar (25) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

Export:
Storing records from tables in a database in a system file

Basic format for exporting data:
Format one:mysql> select * from library. Table into outfile "filename";
Format two:mysql> select * from library. Table into outfile "filename" fields terminated by "symbol";

Example: Save all the records in the user table in the MySQL library to the system file Xx.txt
Mysql> SELECT * from Mysql.user to outfile "Xx.txt";
[[email protected] ~]# Find/-name "Xx.txt"
/var/lib/mysql/xx.txt (the exported content is stored by default under MySQL)

can also be exported to the specified directory
[Email protected] ~]# mkdir mysqldata
[Email protected] ~]# chown mysql/root/mysqldata (change owner)
Mysql> SELECT * from Mysql.user to outfile "/root/mysqldata/xx2.txt";

——————————————————————————————————————————————————————————————————

Manage Table Records

Insert a new record into the table
Inserts a new record into the table at a time, assigning values to each field of the new record
Format:mysql> insert into library name. Table Name values (field values list);
Example: Inserting a new message into the UserInfo table of the HYDRA01 library
mysql> INSERT into hydra01.userinfo values (+, "Hydra", "X", 2003,20003, "Hail Hydra", "/home/hydra", "/bin/bash");

Insert more than one new record into a table at a time, assigning values to each field in the new record
Format:mysql> insert into library name. Table Name values (field value list), Values (field value list), (field value list);
Example: inserting multiple new information into the UserInfo table of the HYDRA01 library
mysql> INSERT INTO Hydra01.userinfo values ("FBI", "X", 1937,1937, "Hail Hydra", "/home/hydra", "/bin/bash"), (30, " CIA "," X ", 1937,1937," Hail Hydra ","/home/hydra ","/bin/bash ");

Insert more than one new record into a table at a time to assign a value to a specified field in a new record
Format:mysql> insert into library name. Table name (List of field names) values (field value lists);
Example: inserting new information into the UserInfo table of the HYDRA01 library and assigning only values to the specified fields
mysql> INSERT INTO Hydra01.userinfo (Name,password,uid,gid,comment,homedir,shell) VALUES ("Anonymousx", "X", 1937,1937, "Teacher", "/home/anonymousx", "/sbin/nogin");

View Effects
Mysql> select * from UserInfo;
+----+------------+----------+------+-------+------------------------------+---------------------+------------- ---+
| 26 | Hydra | x | 2003 | 20003 | Hail Hydra | /home/hydra | /bin/bash |
| 27 | FBI | x | 2003 | 2003 | Hail Hydra | /home/hydra | /bin/bash |
| 28 | CIA | x | 2003 | 2003 | Hail Hydra | /home/hydra | /bin/bash |
| 29 | FBI | x | 1937 | 1937 | Hail Hydra | /home/hydra | /bin/bash |
| 30 | CIA | x | 1937 | 1937 | Hail Hydra | /home/hydra | /bin/bash |
| 31 | Anonymous | NULL | NULL | NULL | NULL | NULL | NULL |
| 32 | Anonymousx | x | 1937 | 1937 | Teacher | /home/anonymousx | /sbin/nogin |
+----+------------+----------+------+-------+------------------------------+---------------------+------------- ---+

—————————————————————————————————————————————————————————————————————————————————————

Query table
Format:
Select field list from Library name. Table name where condition;

How the condition is expressed:
Numerical comparison:
Conditional formatting: Field name symbol value
= = = < > <= >=
Instance:mysql> select * from userinfo where ID <=10;

Character comparisons:
Conditional formatting: Field name symbol "VALUE"
= != "
Instance:mysql> select * from UserInfo where shell!= "Sbin/nologin";

Range Matching:
Conditional formatting: field name symbol matching
Between. ADN. /In.. Between
In (value list)/Inside
Not in (Value list)/not inside
Instance:mysql> select * from UserInfo where uid between and 20;

Logical match:
Conditional formatting: field name symbol matching (used when multiple query criteria)
And: Multiple conditions match
Or: Multiple conditions to match a certain condition
!: Take counter
Instance:mysql> select * from userinfo where name= "MySQL" or uid=3000;

Match NULL:
Conditional formatting: field name symbol matching
Is null
Instance:mysql> select * from userinfo where name is null;

Match non-null:
Conditional formatting: field name symbol matching
is NOT NULL
Instance:mysql> select * from userinfo where name was NOT null;

Fuzzy query:
Conditional formatting: Field name like ' expression '
%: matches 0 or more characters
_: Matches any one character
Instance:mysql> select * from UserInfo where name is like ' ____ ';

Regular match:
Conditional formatting: Field name RegExp ' Regular expression '
^: Opening
$: End
.: Any character
*: Any character
[]: Interval
Instance:mysql> select * from userinfo where uid regexp ' [0-100] ';

Mathematical calculation:
Conditional format: The field name is calculated as the from name from the calculated table;
+-*/% (taken from)
Example:mysql> Select Uid,gid, (Uid+gid)/2 as Zhi from userinfo;

Aggregation functions:
Conditional format: Select XXX (field name) from table;
Max (field name) to find the maximum value
Min (field name) to find the minimum value
AVG (field name) evaluation value
sum (field name) sum
Count (field name) Number of statistical values
Instance:mysql> select Max (UID) from UserInfo;

To sort the results of a query:
Format: Order By field name sorting method;
Sort by:
ASC: Ascending (default sort mode)
Desc: Descending
Instance:mysql> select Name,uid from UserInfo where UID >=10 and UID <=50 order by uid Desc;

To group Query results:
Format: Group By field name
Example: mysql> Select Shell from the userinfo where UID <=10 Group by Shell;

Limit the number of records displayed for display query results inmit:
Format: limit row number;
Instance:mysql> select Name,uid from userinfo where UID <=8 limit 2;

Comprehensive testing:
1. Output The maximum user information for the UID number in the UserInfo table
Test:mysql> SELECT * from UserInfo ORDER BY uid desc LIMIT 1;

2. The UID number in the output table is the maximum two-digit user name and UID number
Test:mysql> Select Name,uid from userinfo where uid regexp ' ^. $ ' ORDER BY uid desc LIMIT 1;

View the records in the table that match the criteria, the value of the all field
Format:mysql> SELECT * from library name. Table name wher condition;
Instance:
Mysql> SELECT * from userinfo where ID <=10; (numeric comparison)
Mysql> SELECT * from UserInfo where shell!= "Sbin/nologin"; (character comparison)
Mysql> SELECT * from UserInfo where uid between and 20; (Range matching)
Mysql> SELECT * from UserInfo where UID in (5,15,25); (Range matching)
Mysql> SELECT * from UserInfo where name in ("Apache", "MySQL"); (range matching)
Mysql> SELECT * from userinfo where name is not in ("Apache", "MySQL"); (range matching)
Mysql> SELECT * from userinfo where name RegExp ' [0-9] ';(regular)

View the value of the specified field in a table that matches a condition record
Format:mysql> Select specifies the field from the Library name. Table name wher condition;
Instance:
Mysql> Select ID from userinfo where ID <=10; (numeric comparison)
Mysql> select name from UserInfo where shell= "Sbin/nologin"; (character comparison)
Mysql> Select Name,id from UserInfo where uid between and 20; (Range matching)
Mysql> select name from UserInfo where name= "MySQL" and uid=3000; (logical match)
Mysql> select name from UserInfo where name= "MySQL" or uid=3000; (logical match)
Mysql> select name from userinfo where name is null; (match null)
Mysql> select name from userinfo where name is not null; (match non-null)
Mysql> select name from userinfo where name like ' ____ '; (fuzzy query)
Mysql> select Max (UID) from userinfo; (aggregate function)
Mysql> Select Uid,gid, (Uid+gid)/2 as Zhi from userinfo; (mathematical calculation)

—————————————————————————————————————————————————————————————————————————————————————

Nested query: The inner query results as the query criteria for the outer query.
Format: Where condition (subquery);
Example: the user name and UID number showing the value of the UID field in the UserInfo table is less than the average of the secondary field
Test:mysql> Select Name,uid from UserInfo where UID < (select AVG (UID) from userinfo);
Example: Nested queries can be queried across libraries
Test:mysql> select name from UserInfo where name in (select User from Mysql.user where user= "root" and host= "localhost");

Copy table: (The index of the original table will not be copied)
Format: CREATE table new name select * from copied table
Test:mysql> CREATE TABLE Userinfox select * from UserInfo;
Example: Copying the first 10 bars of a userinfo table
Test;mysql> CREATE TABLE userinfox1 select * from UserInfo limit 10;

Duplicate table structure: (duplicate table structure does not replicate data)
Format: Format: CREATE TABLE new name select * from copied table where 1 = 2;
Test:mysql> CREATE TABLE Userinfox2 select * from userinfo where 1 = 2;

Multi-Table query:
Format one: Select field name from the table name, table name;
Format two: Select field name from table name, table name where condition;
Test:mysql> SELECT * from Userinfox,hydra;
Test:mysql> Select Userinfo.name,userinfox.name from userinfo,userinfox where userinfo.uid = Userinfox.uid;

Connection query:
Left JOIN query: Left JOIN ... on (main display of query results in the table on the Ieft)
Format: SELECT * from the left table to the right of the join table on condition;
Test:mysql> SELECT * from userinfo LEFT join Userinfox on Userinfo.name=userinfox.name;

Right-Link query: Rights join ... on (the table on the right is the main display of the query results)
Format: SELECT * from left table right join table on condition;
Test:mysql> SELECT * from UserInfo right join Userinfox on Userinfo.name=userinfox.name;

Modify Table Records
Batch modification:
Format: Update library name. Table Name SET field name = value, field name = value;
Test:mysql> update Userinfox set uid=0,gid=0;
To modify the values of a qualifying record field:
Format: Update library name. Table Name SET field name = value, field name = value where condition;
Test:mysql> update Userinfox set homedir= "/opt" where name= "/bin/bash";

Delete a table record
Delete all records:
Format: delete from table name;
Test: Delete from HYDRA01;
To delete a record that matches a condition:
Format: Delete from table name where condition;
Test:mysql> Delete from UserInfo where ID was not null;

————————————————————————————————————————————————————————————————————————

MySQL database import and export query modify table record