mysql惯用建表语句-转

mysql 常用建表语句 -转 http://blog.csdn.net/kakane/article/details/7401111 【1】建立员工档案表 ?? 要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。 【2】合理选择数据类型及字段修饰符，要求有NOT NULL，auto_increment, prima

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',7360196,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) < 2003; 

mysql> select * from workers_info ORDER BY salary limit 1; 
mysql> select * from workers_info ORDER BY salary desc limit 1; 

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;