MySQL Series 3----data import and export, manage tables, query

first, data import and export

1, search system directory: Show variables like "Secure_file_priv"

If the display is empty, you can go to the configuration file to set the path, and copy the file to the allowed directory, set permissions

      +------------------+-----------------------+

| variable_name | Value |

+------------------+-----------------------+

| Secure_file_priv | /var/lib/mysql-files/|

+------------------+-----------------------+

you can see its safe directory as:/var/lib/mysql-files

2. Copy the table to the Security directory:

cp/etc/passwd/var/lib/mysql-file/

3. Import Tables: Create the appropriate databases and tables first

The path of the load data infile "/VAR/LIB/MYSQL-FILES/PASSWD"//Import Table file

into table Test.user//import which table under which database

Fields terminated by ":" Lines terminated by "\ n"; Delimiters and end characters per line

4. Select * FROM Test.user limit 3 to OutFile "/var/lib/mysql-files/user3.txt"//first three lines export

Fields terminated by "*" lines terminated by "\ n"; Specify field separators

Ii. Records of management tables

1. Query table record: Select field Name list from library. Table where match condition

2, the matching conditions are expressed:

A, numerical comparison = = = > < etc.

B, character comparison =! =

C, in-scope comparison: Where field name between value 1 and value 2; Between

In (a list of values); In

Not in (list of values); not at ..... In

D, logical match: And OR!

E, match null, non-null: IS null;          is not null; Distinct//Repeat value not shown, add after select

F, Operation: select Name, 2018-s_year as age from name = "Root";

G, fuzzy query: Where field name like ' expression ':%//0 or more characters _//one character

H, regular match: where field name regexp ' Regular expression ': ' ^....$ ' four digits

I, statistical functions: Sum sum (field), Average AVG (field)

Max Max (field), Min min (field), Count of counts (field)

Select sum (user_id) from sys_in; distinct: Do not display duplicate values for a field

3, the query results grouped:

SELECT * from Stuin order by age; Default ascending order

SELECT * from Stuin order BY age Desc; Descending arrangement

Select Sex,count (Sex) from Stuin group by sex; Statistical gender totals are sorted by sex

SELECT sex as ' gender ', count (sex) as ' number ' from Stuin GROUP by sex;

4. Update the value of a table record field

Update table name set field = value where condition;

5. Delete Table records:

Delete from table name where condition;

6. Nested Queries

Select User,uid from user where uid> (select AVG (UID) from user where uid<500);

//query for records of Uid> (average of uid<500 accounts)

7. Copy table: The key property is not copied to the new table

CREATE TABLE Table 2 Select * FROM table 1 where condition;

8. Multi-Table query: No conditions (Cartesian set)

Select field from table 1, table 2 where condition;

9, left and right connection

Select field Name list from table 1 left JOIN table 2 on condition;//Entry Less

Select field Name list from table 1 right join table 2 on condition;//Entry more

MySQL Series 3----data import and export, manage tables, query