MySQLデータベース(3)

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

       

（1）    
mysql>load data infile "  /   " into table       /      /var/lib/mysql-files,             ，        ，            
    >field terminated by "     "            /                       
    >lines terminated by "
"               /             
mysql> show variables like "secure_file_priv";       
/etc/my.cnf
secure_file_priv="/myfile"

   /etc/passwd       user 
  a.   user(                ) 
    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. /etc/passwd         /var/lib/mysql-files
    cp /etc/passwd /var/lib/mysql-files/

    c.    
    load data infile "/var/lib/mysql-files/passwd" into table user \
    >fields terminated by ":" lines terminated by "
";  /         ":",      "
".

    d.  
    select * from user;

    （2）             sql          
    >sql     into outfile "  /   " fields terminated by "     " lines terminated by "     "
                    /    /var/lib/mysql-files         ，            

    # uid  100      user1.txt   
   >select * from user where uid < 100 into outfile "/var/lib/mysql-files/user.txt" \
   >fields terminated by "*"\     /       *
   >lines terminated by "#"       /      #

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

     

 

insert into   .   values(   ),(   );            /       
insert into   .  (     ) values(   ),(   );  /        

 

select * from   .  ；        /         
select       from   .       /        
#select name ,uid from user;

select * from   .   where   ；
#select * from user where name="root";  /  name   root     

         ： 
       >  >=   <   <=  =  !=
       =   !=

     
(a)     in （   ）      ... 
#select * from user where name in ("root","tom","apache");  /            
(b)    between  1 and  2    ...  
#select * from user where uid between 10 and 50;   /            
(c)not in
#select * from user where name not in ("root","tom","apache"); /    in    ，       

   /  
(a) is null
#select * from user where name is null;
(b) is not null
#select * fro user where name is not null;

      
(a)distinct    
#select shell from user; /     shell      ，        
#select distinct shell from user  /          ，                

      ：        
     and             
     or                     
     !           
#select name from user where name="tom" and uid=500;  /   name   tom  uid=500 ，  name            
#select name from user where name="root" or uid=100;  /  name          name=“root”   uid=100

         +   -   *   /   % 
           
#select uid,gid,uid+gid he from user; /   uid，gid  uid+gid           he(uid+gid      ，  )

    
where     like '   '
(a)"_"       
#select name from user where name like 'r_o_t';    /  '_'      
(b) % 0      
#select name from user where name like 'r%';    /  name  r       
#select name from user where name like '%r%';   /  name    r   
#select name from user where name like '%t';    /  name  t       
#select name from user where name liek 'r%' or name like '%t'; / r    t  

    
where     regexp '     '
#select name from user where name regexp '[0-9]';  /  name         
#select name from user where name regexp '^[0-9]';  /  name           
#select name from user where name regexp '^r';     /  name    r     
#select name from user where name regexp 'r.*t';  /  name   ，r t    0         

               

(a)sum(   )   
#select sum(uid) from user;
(b)avg(   )    
#select avg(uid) from user;
(c)max(   )    
#select max(uid) from user;
(d)min(   )    
#select min(uid) from user;
(e)count(   )     
#select sum(uid) from user;   /       null  

    
>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   group by        /        group  ，         
#select shell from user group by shell; 

         
(a)limit   n;    n 
#select * from user limit 3;
#select * from user where uid between 10 and 50 limit 3;
(b)limit   m，  n;    m   n 
#select * from user limit 1,2;    1             2-3   
#select * from user limit 1,1;        

                      
select * from user where    (select * from    where   )；
#select * from user where name in (select name from user where uid<10);
#select name,uid from user where uid > (select avg(uid) from user);

     
  ：     、    
create table    sql  
#create table user1 select name,uid,homedir from user limit 3; / sql               ，
#create table user2 select name,uid,shell from user limit 4;

    
select       from     ；    
#select * from user1,user2;   /    3*4 12  ，user1       user2     
#select * from user1,user2 where user1.name=user2.name and user1.uid=user2.uid; /   user1  user2 name，uid      

    
(a)           (A)           A， B   
select * from  A left join  B on   ;
#select * from user1 left join user2 on user1.uid=user2.uid;
(b)          (B)           A， B   
#select * from user1 right join user2 on user1.uid=user2.uid;

 
         
(a)update    set    ="";
#update user1 set uid=3;
(b)update    set    ="" where   ；
#update user2 set uid=2 where name="root";

 
    
(a)delete from   ;  /      
#delete from user1;
(b)delete from    where   ； /            
#delete from user1 where name="root";