MySql文大全(1)追加、検索、削除、変更

2672 ワード

#   
select * from test;  #     
select * from test limit 0,2;  #       
select * from test email like '%qq%' #   qq   _     %    
select * from test order by id asc;#  desc
select * from test id not in('2','3');#id  2,3    not    
select * from test timer between 1 and 10;#   1,10  

#---------------------------     ------------------------------
#          inner join                
select * from A inner join B on A.id = B.id; #  1
select * from A,B where A.id = B.id; #  2
select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#  3       
select a.id as ID,a.title as    from A inner join B on A.id=B.id;#  as  

#         left join                       
select * from A left join B on A.id = B.id;

select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#    

#         right join                       
select * from A right join B on A.id = B.id;

#       full join           
select * from A full join B on A.id = B.id;

#       where         
select * from A cross join B;
-------------------------     ------------------------------------------------------------
#     
describe 
#   
drop table test;
#    
alter table test_old rename test_new;
#   
alter table test add cn int(4) not null;
#   
alter table test change id id1 varchar(10) not null;
#    
alter table test drop cn;
#    
alter table test add index (cn,id);
#    
alter table test drop index cn
#    
insert into test (id,email,ip,state) values(2,'[email protected]','127.0.0.1','0');
#     
delete from test where id = 1;
#    
update test set id='1',email='[email protected]' where id=1;
   insert select  :
INSERT INTO KM_MEMBER_RELA_FULLCUT (
	SELECT
		a.MB_ID,
		b.MPFC_ID,
		1
	FROM
		KM_MEMBER a,
		KM_MEMBER_PREF_FULLCUT b
	WHERE
		b.MPFC_STATUS = 0
	AND NOT EXISTS (
		SELECT
			1
		FROM
			KM_MEMBER_RELA_FULLCUT
		WHERE
			MB_ID = a.MB_ID
		AND MPFC_ID = b.MPFC_ID
	)
)

 
#     
mysql -hlocalhost -uroot -p;
#    
mysqladmin -uroot -pold password new;


#     
show databases;
#     
show tables;
#     
use examples;
#          utf-8    
create database `examples` default character set utf8 collate utf8_general_ci;
#     
drop database examples;
#   
create table test(
    id int(10) unsigned zerofill not null auto_increment,
    email varchar(40) not null,
    ip varchar(15) not null,
    state int(10) not null default '-1',
primary key (id)
)engine=InnoDB;

 
  :https://blog.csdn.net/suifenglie/article/details/74981817