mysql数据库基础（二）

– 表中增加一列
alter table student add column info text;
alter table student add column info text after age;
alter table student add column ingo text first;
– 删除表中一列
alter table student drop column info text;
– 修改表名
rename table student to students;
alter table student rename to studtents;
– 随机查询两条数据，对大型数据库不可以，没有缓存，而且效率低
select * from student order by rand() limit 2;
select * from student limit 1;
– 利用limit关键字将表分为3页，每页2条数据 ,表分为m页，每页n条数据
select * from student limit 0,2;
select * from student limit 2,2;
select * from student limit 4,2;
select * from student limit m*n-n,n;
– 跨库查询
select * from mysql.user;

select * from student where sname=‘李一’;
select * from student where age>9 && age <13;
select * from student where age between 9 and 13;
alter table student add column address varchar(200) not null after favorite;
update student set address=‘北京市’ where sid = 1;
update student set address=‘合肥市’ where sid = 2;
update student set address=‘郑州市’ where sid = 3;
update student set address=‘北京市’ where sid = 4;
update student set address=‘合肥市’ where sid = 5;
update student set address=‘郑州市’ where sid = 6;

select * from student where address is not null;
select * from student where address is null;

alter table student add column dept varchar(20) after age;
update student set dept=‘英语’ where sid = 1;
update student set dept=‘计算机’ where sid = 2;
update student set dept=‘英语’ where sid = 3;
update student set dept