//表 增加列test
altertable tableName add(test char(10));
//表 test列后增加age列
altertable tableName add age tinyint(2) notnullafter test;
//表 修改列test
altertable tableName modify test char(20) notnull;
//表 修改列test名为test1
altertable tableName Change test test1 test1Type;
//表 修改列test的默认值
altertable tableName alter test setdefault'system';
//表 去掉test默认值
altertable tableName alter test dropdefault;
//表 去掉列test
altertable tableName dropcolumn test;
//表 删除主键
altertable tableName dropprimarykey;
//表 增加主键
altertable tableName addprimarykey DoubleKey(id,name);
数据插入
//将开发部 插入表 department
insertinto department(name,content) values('开发部','开发部');
//
删除数据
//删除指定的name='开发部' 行
deletefrom department where name='开发部';
更新操作
//将开发部更新为前端开发部
update department set name='前端开发部'where name='开发部';
查询操作
//1、显示开发部的人员和职位select a.name,b.name,department_name,c.name,position_name
from staffer a,department b,s_position c
where a.department_id = b.id and a.position_id = c.id and b.name = '开发部';
//2、显示开发部的人数select count(*)
from staffer a,department b
where a.department_id = b.id and b.name = '开发部';
//3、显示各部门人数
select count(*),b.name
from staffer a,department b
where a.department_id = b.id
groupby b.name;
//4、显示第3到7行(结果集标号从0开始)//limit 方法的第一个参数表示从第几个结果开始//第二个参数表示显示几个结果//order by 用于排序(asc:升序,desc:降序)select *
from department
orderby id
limit 2,5;
//5、查询mysql.user表中用户//Distinct 表示结果项不重复//Concat 用户联合多个字符或者列selectDistinctConcat('User:',user,'@',host,';') as Query from mysql.user;