数据操作
插入数据
insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0');
删除数据
delete from test where id = 1;
修改数据
update test set id='1',email='q@qq.com' where id=1;
查数据
select * from test; 取所有数据
select * from test by id limit 0,2; 取前两条数据
select * from test where email like '_q%' 查含有q字符 _表示一个 %表示多个
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之间
修改表中数据update
语法：update 表名 set 字段=新值,… where 条件
mysql> update MyClass set name='Mary' where id=1;
子查询
(表名1：aa 表名2：bb)
select a,b,c from aa where a IN (select d from bb
) 或者:
select a,b,c from aa where a IN (1,2,3)
选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
在线视图查询
(表名1：aa )
select * from (select a,b,c FROM aa) t where t.a
> 1;
连接join on
内连接
#等值连接又叫内链接 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;