Mysql (iii)-Data import, export, manage table records, condition matching, table Query method

##############################################################################################

Data import: Store the contents of the system files in the table of the database

Command:
Load data infile "directory/file" into table name fields terminated by "field separator" lines terminated by "line delimiter";

Example: Importing/etc/passwd to Studb.user

Create databases Studb; Create a STUDB Library
Use STUDB;//Usage Library
CREATE table User (//Creating tables
Name varchar (50),
Password char (1),
UID Int (2) Zerofill,
GID Int (2) Zerofill,
Comment varchar (50),
Index (name)
);

Desc Studb.user; View table Structure


ALTER TABLE Studb.user add ID int (2) primary key auto_increment first;

Import:
To view the default directory used by the database:
Show variables like "Secure_file_priv"
To set the default directory used by the database:
Mkdir/mysqlload
Chown Mysql/mysqlload
Vim/etc/my.cnf
[MySQL]
secure_file_priv= "/mysqlload"

cp/etc/passwd/mysqlload///files to be imported into the default directory of the database
Load data infile "/MYSQLLOAD/PASSWD" into table user fields terminated by ":" Lines terminated by "\ n";
The delimiter between the fields of the passwd file is: The row delimiter is carriage return (\ n)

View results:
SELECT * from Studb.user;

############################################################################################

II. Data export: Storing table records in system files

SQL query into outfile "/directory/File name"
SQL query into outfile "/directory/filename" terminated by "symbol" lines terminated by "symbol";

SELECT * from Bbs.t1 to outfile "/mysqlload/a.txt";

Note: Because I have set the default directory used by the database before, so my file export path is the directory I set (/mysqlload).

#########################################################################################################

Third, management table records:
1. Insert Table record
insert INTO library. Table values (field value list);
insert INTO library. Table values (field values list), (field value list);

insert INTO library. Table (field list) VALUES (List of field values);
insert INTO library. Table (field list) VALUES (List of field values), (field value list);

2, query table records:
Select field Name list from library. Table;
Select field Name list from library. table where condition;

select * from user;
SELECT * from user where name= "MySQL";

3, update the table record:
Update Library. Table Set field 1= value, field 2= value, field n= value;
Update Library. Table Set field 1= value, field 2= value, field n= value where condition expression;/Modify the value of a field that satisfies a condition

4. Delete Table records:
Delete from library. table where condition expression;//delete table records for fields that meet the criteria
Delete from library. Table;//delete all table records

############################################################################################################### #

Note: The/etc/passwd was previously imported into Studb.user, so the following is an example of the user table:


Four, the conditional matching expression mode:
Numeric comparison: < <= > >= =! =
Character comparison: = = =
Range Matching: Between value 1 and value 2; in;
Match null and non-null: IS null; is not null;
Logical match: and;
Fuzzy query: Where field name like ' expression ';
Regular match: Where field name regexp ' Regular expression ';

1. Numeric Comparison: The field type must be numeric:
Numeric comparison > >= < <= =! =
Field name symbol value
Select name from user where uid=15;
SELECT * from user where id>10;

###########################################################

2. Character comparison =! =
Field name symbol "VALUE"
Select Name,shell from user where shell!= "/bin/bash";
Select Id,name from user where name= "Apache";

###########################################################

3. Match within range
Field name between value 1 and value 2 in ... Between

SELECT * from user where ID between and 15;
Select name from user where UID between 1 and 10;

Field name in (Value list) in ... In
Select Id,name from user where name in ("Apache", "Root", "Bob");
Select Id,name,uid from the user where UID in (10,15,9,12);

Field name not in (value list) is not ... In
Select name from the user where UID not in (0,1,5,7);
SELECT * from user where name is not in ("root", "MySQL", "bin");

#####################################################################

4. Match NULL is NULL
Field name is null
Match non-NULL is NOT NULL
Field name is NOT NULL
The select ID from the user where name is null;
Select Id,name,shell from user where shell was not null;


Insert into user (name) VALUES (""), ("null"), (null);
Select Id,name from user where name= "";
Select Id,name from user where name= "null";

############################################################################

5. Distinct does not display duplicate values
Distinct field name
Select distinct shell from user;
Select DISTINCT shell from user where uid<=10;

#########################################################################

6. Logical match: There are multiple conditions
Logical and multiple conditions must be established
One condition for logic or or multiple conditions can be set
Logical Non! Take counter


Select name from user where name= "Zhangsan" and

uid=500 and Shell= "/bin/bash";

Select name from user where name= "Zhangsan" or uid=500

or shell= "/bin/bash";

###########################################################################


7, math operation +-*/%
The field type must be a numeric type

Select field name symbol field name from table where condition;

Select Uid+gid from user where name= "root";
Select Name,uid,gid,uid+gid he from user;
Select Name,uid,gid,uid+gid he from user where name= "bin";

ALTER TABLE user add age tinyint (2) unsigned default + after name;

Select Name,age,2017-age old from user where name= "Bob";

Select Name,uid,gid, (uid+gid)/2 pjz from user where name= "bin";

###############################################################################
8, fuzzy query like
where field name like ' expression ';
_ Any one character
% of 0 or more characters
Select name from the user where name like ' _ _ _ _ ';
Match the Name field with a value of 4 characters.
Select Name,uid from user where name like ' _ _ _ _ ' and uid<=10;
Select name from the user where name like ' a% ';
Match the beginning of a
Select name from the user where name like '%a% ';
Match field value contains a.

Select Id,name from the user where name like ' _a_ ';
Match field value is 3 characters and the middle one character is a
Select Id,name from the user where name like ' j% ' or '%y ';
Matches the start of J, or the end of Y

###############################################################################
9. Regular matching
The Where field name RegExp ' Regular expression ';
. Any single character
^ with what begins
$ with what end
[] in-range matching
* Previous characters appear 0 to several times
| Or

Insert into user (name) VALUES ("Bob9"), ("J7im"), ("1yaya");

Select name from user where name RegExp ' [0-9] ';
The value of the field that contains the number
Select name from user where name RegExp ' ^[0-9] ';
The value of the field that starts with a number
Select Name,uid from user where uid regexp ' ... ';
field value, field value with a minimum number of characters of 2
Select Name,uid from user where uid regexp ' ^. $';
field value, number of characters is 2 field value

Select Name,uid from user where name regexp ' a.*t ';
A is in the field value and has a T
Select Name,uid from user where name regexp ' ^a.*t ';
Start with a and have T
Select Name,uid from user where name RegExp ' ^r|t$ '
Start with R or end with T

################################################################

10, the Statistical function number field is a numeric type.
Sum of averages to find the minimum statistic number of maximum value
sum (field name) Avg (field name) max (field name) min (field name) count (field name)

Select count (name) from user where shell= "/bin/bash";
Select Max (UID) from user;
Select min (gid) from user;
Select AVG (age) from user;
Select SUM (GID) from user;
Select SUM (GID), count (name) from user;

########################################################################
11, Query sort SQL query order by field name Asc/desc;
Select Name,uid from user where uid between and 50;
Displays the Name,uid field, and satisfies the UID between 10 and 50, in ascending order by default
Select Name,uid from user where UID between and the order by uid Desc; Desc Descending sort

##########################################################################

12. Query group By field name of SQL query;
Select Shell user where uid between and 50;
Select shell from user where UID between ten and group by Shell;
Select shell from the user group by Shell;

Because the shell of different users may be the same, grouping the same shell is done with distinct (not displaying duplicate values).

################################################################################

13. Limit query display number of rows
SQL query limit number; Display first few lines of query results
SQL query Limit number 1, number 2, starting from the next line of the number 1, the number 2 sets the total number of rows displayed
select * from user;
SELECT * from user limit 2;
Show first two lines
SELECT * from User limit 2, 2;
Starting from line 3rd, displaying 2 rows in total, so the 3rd, 4 lines are displayed
SELECT * from the user order by uid Desc;
SELECT * from the user order by uid DESC limit 5;
SELECT * from the user order by uid desc LIMIT 1;

###############################################################################################
Four: Table Query method
Single-Table Query
where nested query
Multi-Table Query
Connection Query


1. Single-table query
All that is used in the preceding is a single-table query.

#############################################################


2, where nested query: The query results of the inner layer as the query criteria for the outer query.

Select field List from table name where condition (select field List from table name where condition);


Select Name,uid from the user where UID > (select AVG (UID) from user);

Displays the value of the user name and UID UID field greater than the average of the UID field. (same sheet)

Select name from user where name is not in (select User from Mysql.user);

Displays the user name, which user names in Studb.user are not in the Mysql.user table (different tables)

Select name from the user where name in (select User from Mysql.user where user= "Zhangsan");

Select name from user where name is not in (select User from Mysql.user where user= "Zhangsan";);


########################################################################################

Copy table: Role: Quick Build table, backup table

CREATE TABLE Library. Table SQL query;

Create Database Dbbak;
CREATE TABLE Dbbak.user2 select * from Studb.user;
CREATE TABLE Dbbak.user3 SELECT * from studb.user where 1 = 2;
CREATE TABLE Dbbak.user4 Select Name,uid from Studb.user limit

3: Multi-table Query
List of select field names from List of table names; Cartesian set
Select field Name list from table name list where condition;

CREATE TABLE Studb.t1 Select Name,uid,shell from user limit 3;
CREATE TABLE Studb.t2 Select Name,uid,homedir from user limit 4;
Show tables;

SELECT * from T1; select * from T2;
Total 3*4=12 table Records

SELECT * from t1,t2 where t1.uid = T2.uid and t1.name=t2.name;

Query the fields in T1 and T2 that have the same UID as name and display
Select t1.*, T2.homedir from t1,t2 where t1.uid = T2.uid and t1.name=t2.name;
Displays all fields in T1, homedir fields in t2, satisfying t1.uid = T2.uid and t1.name=t2.name;

++++++++++++++++++++++
4. Connection Query
Left Join answer Query
Select field Name list from Table A LEFT JOIN table B on condition;

Right connection query
Select field Name list from table A right join table B on condition;

CREATE TABLE STUDB.T3 Select Name,uid,shell from user limit 3;
CREATE TABLE STUDB.T4 Select Name,uid,shell from user limit 5;
Show tables;
select * from T3; select * from T4;

SELECT * from T3 left joins T4 on T3.uid=t4.uid;
using T3 as the reference table, display the results
+--------+------+---------------+--------+------+---------------+
| name | UID | Shell | name | UID | Shell |
+--------+------+---------------+--------+------+---------------+
|    Root | 0 | /bin/bash |    Root | 0 | /bin/bash |
|    Bin | 1 | /sbin/nologin |   Bin | 1 | /sbin/nologin |
|    Daemon | 2 | /sbin/nologin |    Daemon | 2 | /sbin/nologin |
+--------+------+---------------+--------+------+---------------+


SELECT * from T3 right join T4 on T3.uid=t4.uid;

Using T4 as the reference table, the results are displayed:
+--------+------+---------------+--------+------+---------------+
| name | UID | Shell | name | UID | Shell |
+--------+------+---------------+--------+------+---------------+
|    Root | 0 | /bin/bash |    Root | 0 | /bin/bash |
|    Bin | 1 | /sbin/nologin |    Bin | 1 | /sbin/nologin |
|    Daemon | 2 | /sbin/nologin |    Daemon | 2 | /sbin/nologin |
| NULL | NULL | NULL |    ADM | 3 | /sbin/nologin |
| NULL | NULL | NULL |    LP | 4 | /sbin/nologin |
+--------+------+---------------+--------+------+---------------+

#################################################################################

Mysql (iii)-Data import, export, manage table records, condition matching, table Query method