mysql 常用建表语句 -转 http://blog.csdn.net/kakane/article/details/7401111 【1】建立员工档案表 要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。 【2】合理选择数据类型及字段修饰符，要求有not null，auto_increment, prima
mysql 常用建表语句 -转
http://blog.csdn.net/kakane/article/details/7401111
【1】建立员工档案表
要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。
【2】合理选择数据类型及字段修饰符，要求有not null，auto_increment, primary key等。
?
drop table if exists `workers_info`; create table `workers_info` ( `id` int(11) not null auto_increment, `workername` varchar(20) not null, `sex` enum(f,m,s), `salary` int(11) default '0', `email` varchar(30), `employeddates` date, `department` varchar(30), primary key (`id`) ) engine=myisam default charset=utf8; mysql> alter table workers_info add sex enum('f','m','s');
?【3】查看表的结构
?
?
mysql> desc workers_info;
?【4】新增qq和tel字段，要求tel字段位于email前，要求入职时间是最后一个字段
?
mysql> alter table workers_info add tel varchar(15) after salary;
mysql> alter table workers_info add qq int; alter table workers_info modify employeddates date after qq;
?【5】把email字段修改成mailbox
alter table workers_info change email mailbox varchar(30);
?【6】向表里添加10条记录
mysql> insert into workers_info (workername,salary,tel,mailbox,department,qq,employeddates) values('xing',10000,'1598232123','xing@qq.com','yanfa',736019646,20121221);
?【7】修改其中两条记录的tel和mailbox
mysql> update workers_info set mailbox = 'haha@qq.com',tel='1246543423' where id = 13;
?【8】查看所添加记录
mysql> select * from workers_info;
【9】查看姓名和入职时间记录
?mysql> select workername,employeddates from workers_info;
?
【10】查询入职时间在2003年以前的
mysql> select * from workers_info where year(employeddates) select * from workers_info order by salary limit 1; mysql> select * from workers_info order by salary desc limit 1;
?【12】查询平均工资mysql> select avg(salary) from workers_info;
?【13】统计男员工人数、女员工人数
mysql> select count(男) from workers_info where sex=m; mysql> select count(女) from workers_info where sex=w;
?【14】按照入职时间先后进行排序，并显示前5位员工姓名
mysql> select * from workers_info order by employeddates limit 5;