drop table name;
truncate table name;
delete from name;
delete from name where sid = 3;
rename table stu to student;
alter table student change dept department varchar(30);
alter table student drop department;
alter table student add dept varchar(10);
alter table student add xxx int first;
alter table student add xxx int after xx;
alter table student add sex varchar(2) default '男' after Name;
alter table 表名 modify 列字段 类型;
alter table 表名 modify 列字段 类型 first;
alter table 表名 modify 列字段 类型 after Salary;
delete和truncate
truncate table name;
delete from name;
create table test
as
select * from jobs;
commit;
select * from test;
set autocommit = false;
delete from test;
select * from test;
rollback;
select * from test;
truncate速度比delete快,使用的资源少
阿里Java开发手册mysql字段命名
表名,字段名使用小写字母或数字
表必备三字段(id,gmt_create(datetime)插入时间,gmt_modified(datetime)更新时间)
表的命名最好是业务名称_表的作用
增删改
insert into student(name,gender,age)values('xiaolan','男',18);
insert into student values(100,'xiaoyou','男',14,'2013-12-30','haibian',23,'daxue');
insert into employee values(1,'张三','男',2000),(2,'李四','男',1000),(3,'王五','女',4000);
insert into student select "04005",Sname,Ssex,Sage,Sdept from student where sno = "04003";
insert into product2(pname,price) select pname,price from product;
update student set address = '重庆';
update student set address = 'shanghai' where sid = 100;
update student set address = 'beijing',score = 100 where sid = 100;
mysql删除含有中文的数据
SQL修改表(ALTER TABLE)