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

The following content mainly brings you mysql data import and export and other, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

I. data import and export

1. Search the directory of the system: show variables like "secure_file_priv"

/ / if the display is empty, you can set the path in the configuration file, copy the file to the allowed directory, and set permissions

+-+

| | Variable_name | Value |

+-+

| | secure_file_priv | / var/lib/mysql-files/ |

+-+

You can see that the secure directory is: / var/lib/mysql-files

2. Copy the table to the security directory:

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

3. Import tables: first create the corresponding databases and tables, and then import

Load data infile "/ var/lib/mysql-files/passwd" / / the path to import the table file

Into table test.user / / Import which tables under which database

Fields terminated by ":" lines terminated by "\ n"; / / delimiter and the end of each line

4. Data export:

Select * from test.user limit 3 into outfile "/ var/lib/mysql-files/user3.txt" / / the first three lines export

Fields terminated by "*" lines terminated by "\ n"; / / specify the field delimiter

Second, manage table records

1. Query table record: select field name list from library. Table where matching criteria

2. The representation of matching conditions:

A, numerical comparison =! >

< 等 B、字符比较 = != C、范围内比较：where 字段名 between 值1 and 值2；在。。。。。。之间 in (值列表) ；在。。。。。里 not in (值列表) ；不在..............里 D、逻辑匹配：and or ! E、匹配空，非空 ： is null; is not null; distinct //重复值不显示，加在select后面 F、运算操作：select name ,2018-s_year as age from name ="root"; G、模糊查询：where 字段名 like '表达式' ： % //0个或多个字符 _ //一个字符 H、正则匹配：where 字段名 regexp '正则表达式' : '^....$' 四个数字 I、统计函数：求和 sum(字段)， 平均值 avg(字段) 最大值 max(字段)， 最小值 min(字段)， 统计个数 count(字段) select sum(user_id) from sys_in; distinct :不显示字段的重复值 3、查询结果分组： select * from stuin order by age; //默认升序排列 select * from stuin order by age desc; //降序排列 select sex,count(sex) from stuin group by sex; //统计性别总数以sex排序 SELECT sex AS '性别',count(sex) AS '人数' FROM stuin GROUP BY sex; 4、更新表记录字段的值 update 表名 set 字段=值 where 条件； 5、删除表记录： delete from 表名 where 条件； 6、嵌套查询 select user,uid from user where uid>

(select avg (uid) from user where uid (uid)

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.