Shulou(Shulou.com)06/01 Report--

Data management: (manage table records)

Data import: store the contents of the system file in the table of the database.

Mysql > create table uesr (

> name varchar (50)

-> mima char (1)

-> UID int (2)

-> GID int (2)

-> ms varchar (100)

-> honame char (100)

Jsq char (25)

-> index (name)

->)

Check the default usage directory and whether it exists: show variables inke "secure_file_priv"

Format: load data infile "system directory / file name" into table library name. Table name fields treminated by "Field interval symbol" lines treminated by "\ n" data Import

Example: load data infile "/ var/lib/mysql-files/passwd" into table uesr fields terminated by ":" lines terminated by "\ n" format "\ n"

System directory / file name table name specifies the delimiter "Field interval symbol" column specifies that the delimiter is "\ n"

Add a line number to facilitate subsequent work: alter table uesr add id int (2) primary key auto_increment first

Table name add ID type

Note:

The field delimiter should be the same as that in the file

Specify the absolute path to the import file

The table field type of the imported data should match the file field

Disable SElinux

Data export: store table records in system files.

Select queries into outfile "directory / file name"; [fields terminated by "symbol" lines terminated by "symbol"]

Select id,name from uesr into outfile "/ var/lib/mysql-files/uesr1.txt"

Format:

Note: the exported content is made by the SQL query statement

Limit selects line 5

Lines terminated by "\ n" line delimiter

Fields terminated by Delimiter field delimiter

Manage table records:

Add: insert into library. Table values (list of field values); # (assign values to one field at a time)

Insert into library. Table (list of field values) values (list of field values); # # (specify field assignment)

Check:

Select field name list from library. Table wher condition # all queries. And if the condition matches and writes the wher condition, it is the condition in the matching table.

Select field name list (column) from library. Table where condition (row); / / specify lookup

Representation of the condition match:

Numerical comparison > > = < (select avg (uid) from uesr); # # same library and same table

Select name from uesr where name not in (select user from mysql.user); # different tables in different libraries

Select name from uesr where name in (select user from mysql.user where user= "zhangsan")

Select name from user where name not in (select user from mysql.user where user= "zhangsan";)

Copy the table; quickly build the table and back up the table

Format: create table library. Table sql query

Create database dbbak; Quick create Library

Create table dbbak.uesr2 select from dc.uesr; cloned a table from the dc library in the dbbak library.

Create database dbbak.uesr3 select from dc.uesr where 1: 2

Create database dbbak.uesr3 select name,uid from dc.uesr limit 3; # # back up the first three lines in the dc.uesr table

Name of the library. Table name list name, list name library, the first three rows of table name

Multi-table query:

Select field name list from table name list; Dekar 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

Select from T1 where t1.uid T2 where t1.uid = t2.uid and t1.name=t2.name

Select t1.,t2/homedir from t1,t2 where t1.uid

Connection query:

Left join query: select field name list from table A left join B on condition

Select * from T3 left join T4 on t3.UID=t4.UID

Right join query: select field name list from table A right join B on condition

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.