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

This article mainly introduces some summaries and handouts of MySQL database, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated articles in industry information.

Data import and export (1) data import mysql > load data infile "directory / file name" into table table name / default directory is / var/lib/mysql-files, you can modify the configuration file to change the default directory, if you do not modify the configuration file Default and must be in this directory > field terminated by "field spacer" / field spacing symbol of imported text data > lines terminated by "\ n" / line end symbol mysql > show variables like "secure_file_priv" of imported text data Check the default directory / etc/my.cnfsecure_file_priv= "/ myfile" to import / etc/passwd data into the table user a. Create table user (fields in the table should be the same as those in the imported data) create table user (> name char (15), > passwd char (8), > uid int, > gid int, > comment varchar (50), > homedir varchar (30), > shell varchar (30), > index (name), > unique index (uid) >); b. Put the / etc/passwd data in the default directory / var/lib/mysql-files cp / etc/passwd / var/lib/mysql-files/ c. Data import load data infile "/ var/lib/mysql-files/passwd" into table user\ > fields terminated by ":" lines terminated by "\ n"; / the field separator in this table is ":" and the line ending symbol is "\ n". d. Verify select * from user (2) data export stores table records to system files through sql query > sql query into outfile "directory / file name" fields terminated by field spacer "lines terminated by" line end symbol / directory default / var/lib/mysql-files if the configuration file is not modified Default and must be in this directory # export data with uid less than 100 to user1.txt file > select * from user where uid

< 100 into outfile "/var/lib/mysql-files/user.txt" \ >

The fields terminated by "*"\ / field spacer is * > lines terminated by "#" / the line spacer adds the insert into library name to the management table record. Table name values (values list), (values list); / assign insert into library names to all fields. Table name (field name list) values (values list), (values list); / only assign values to individual fields to check the select * from library name. Table name; / query table all fields data select field name list from library name. Table name / query individual field data # select name, uid from user;select * from library name. Table name where condition; # select * from user where name= "root"; / query the representation of the matching of all data conditions in which the name field is root: numerical comparison > > =

< sql查询 order by 字段名 /默认升序 desc 降序#select uid from user order by uid; /查询uid字段数据并升序排序#select uid from user order by uid desc; /查询uid字段数据并降序排序#select * from user order by uid; /查询表中所有数据并以uid升序排序查询分组>

Sql query group by field name / put the same data in the group group, which is equivalent to not displaying duplicate values # select shell from user group by shell; limits the number of rows displayed in the query (a) limit number n; display the first n lines # select * from user limit 3; select * from user where uid between 10 and 50 limit 3; (b) limit number m, number n; display n lines after m lines # select * from user limit 1P2 Displaying the two lines after the first line is equivalent to displaying 2-3 lines of content # select * from user limit 1; displaying the second row of content nested queries takes the query results of the inner layer as the outer query condition select * from user where condition (select * from table name where condition) # select * from user where name in (select name from user where uid (select avg (uid) from user); replication table function: quickly build and backup table create table table name sql query # create table user1 select name,uid,homedir from user limit 3; / take the result of sql query as the structure and data of the new table, # create table user2 select name,uid,shell from user limit 4; multi-table query select field name list from table name list The Cartesian set # select * from user1,user2; / is displayed as 3 '412 rows, each row of the user1 corresponds to each row of the user2 # select * from user1,user2 where user1.name=user2.name and user1.uid=user2.uid; / shows the row join query in which the user1 and user2 name,uid fields are equal (a) the left join query uses the left table (A) as the main query under a certain condition, table A, table B data select * from table A left join table B on condition # select * from user1 left join user2 on user1.uid=user2.uid; (b) right join query mainly queries table A under certain conditions with table B on the left. The data in Table B is # select * from user1 right join user2 on user1.uid=user2.uid; to modify the record in a field (a) update table name set field name = ""; # update user1 set uid=3; (b) update surface set field name = "" where condition # update user2 set uid=2 where name= "root"; delete record (a) delete from table name; / delete table record # delete from user1; (b) delete from table name where condition; / delete a row of records under a condition

# delete from user1 where name= "root"

After reading some summaries and handouts about MySQL database above, I hope it can bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.