1、删除表newpydata:drop table newpydata;
2、查看创建的表字段类型:show create table newtest;
3、向表中添加列项目:alter table newtest add userName char(8);
一次添加多个列项目:alter table newtest add (platFrom char(5),sign char(10));
4、删除列项目:alter table newtest drop column code;
5、修改列类型(常用于增加char(num)):alter table newtest modify platFrom char(8);
6、插入行数值:insert into newtest(phone,userName,userId,platFrom,sign) values(10086,'test2030',15,'MG',123456);
// 注意,插入数据类型为 char、varchar 时,需要加引号,否则会报错:unknown cloumn XXX in field list
7、一次插入多行数值:insert into newtest(phone,userName,userId,platFrom,sign)
values(1347623,'gfollower',6,'MG',111222),(955955,'eva',20,'XX',123456);
8、left join 筛选表记录:select * from newtest left join newpydata on newtest.phone=newpydata.phone;
// 可见,left join 表左表不管是否匹配,全部显示,右表只显示匹配字段
9、right join 筛选表记录:select * from newtest right join newpydata on newtest.phone=newpydata.phone
10、inner join 筛选表记录:select * from newtest inner join newpydata on newtest.phone=newpydata.phone;
11、改变表名称:rename table newpydata to pydata;
12、更新列值(并添加date型数据):update pydata set time='2017/6/21' where code=123456;
13、判断日期:
(1)中间:select * from pydata where time between '2017/1/1' and '2017/12/12';
(2)小于:select * from pydata where time<'2018/1/1';
(3)大于:select * from pydata where time>'2018/1/1';
(4)不等于:select * from pydata where time<>'null';
14、显示警告:show warnings;
15、筛选空值:select * from pydata where isnull(time);
16、建立索引
(1)建立唯一索引(即表中这个索引字段不能重复):create unique index phoneSearch on pydata(phone);
(2)建立简单索引:create index idSearch on newtest(userId);