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