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()