SQL语句

DDL语句:

CREATE:
create database xxx;
create table xxx(id int,uname varchar(32));

DROP:
drop database xxx default charset utf8;
drop table xxx;

ALTER:
alter table xxx add column passwd int;
alter table xxx drop colmun passwd;
alter table xxx modify column passwd varchar(32);
alter table xxx change uname username varchar(32) [first/after id];
alter table xxx rename aaa;

DML语句:

INSERT:
insert into xxx(id,uname,passwd) values(1,'admin','password');
insert into xxx(id,uname) select 2,'test';
insert into xxx set id=3,uname='qwer';

DELETE:
delete from xxx [where id=1];
delete a,b from xxx a,yyy b where a.id=b.id and a.id=001;

UPDATE:
update xxx set passwd=123456 [where id=1];

SELECT:
select * from xxx [where id=1];
select * from xxx order by uname;
select * from xxx order by uname desc;
select * from xxx order by 2 [limit 5];    第2个字段
select * from xxx where id in(select id from yyy);
select * from xxx a where ecists(select id from yyy b where a.id=b.id);

select a.uname,b.passwd from xxx a inner join yyy b on a.id=b.id;    (left join/right join)
left join:返回a表的所有记录和b表中与a表id匹配记录
right join:返回b表的所有记录和a表中与b表匹配的记录
inner join:返回a表与b表内存在且匹配的记录

select * from xxx union [all] select * from yyy;
select * from xxx union select 1,'test';

DCL语句:

GRANT:
grant all privileges on *.* to admin@'%' identified by 'abc';    允许远程登录且有较高权限
grant select ,insert on *.* to admin2@'%' identified by 'abc';    允许远程登录且只有select,insert权限
flush privileges;                  　　　　　　　　　　　　　　　　  刷新权限

数据类型:

tinyint
smallint
mediumint
int
bigint
float
decimal
double

时间日期类型:

date
time
datetime
timestamp
year

字符类型:

char
varchar
binary
varbinary
text
enum
set

比较运算符:

>
<
=
<=
>=
!=

 

关系运算符:

between and
in()
like %x%

 

逻辑运算符:

and
or
xor

 

函数:

user()
version()
database()
length()
position()
locate()
left()
substring()
sleep()
henchmark()
if()
rand()        随机取值(0~1,不包含0和1)
round()        四舍五入
floor()        向下取整
concat()
group_concat()