【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------修改字段名
mysql> 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','[email protected]','yanfa',736019646,20121221);
【7】修改其中兩條記錄的tel和mailbox------修改數據
mysql> UPDATE workers_info SET mailbox = '[email protected]',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;
【11】查詢工資最高和最低的員工姓名------排序
mysql> 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(1) from workers_info where sex="M";
【14】按照入職時間先後進行排序,並顯示前5位員工姓名
mysql> select * from workers_info ORDER BY EmployedDates limit 5;
版權出自:http://blog.csdn.net/kakane/article/details/7401111