【mysql】mysql 常用建表语句

 

转自: http://blog.csdn.net/kakane/article/details/7401111

 

【1】建立员工档案表
要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。


【2】合理选择数据类型及字段修饰符，要求有NOT NULL，auto_increment, primary key等。

[sql] view plaincopyprint?

1. --
2. -- make by kakane
3. --
6. DROP TABLE IF EXISTS `workers_info`;
7. CREATE TABLE `workers_info` (
8. `id` int(11) NOTNULL AUTO_INCREMENT,
9. `workername` varchar(20)NOTNULL,
10. `sex` enum(F,M,S),
11. `salary` int(11) DEFAULT '0',
12. `email` varchar(30),
13. `EmployedDates` date,
14. `department` varchar(30),
15. PRIMARY KEY (`id`)
16. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
19. mysql> alter table workers_infoADD sex enum('F','M','S');

---- make by kakane--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');

[sql] view plaincopyprint?

【3】查看表的结构

[html] view plaincopyprint?

1. mysql> desc workers_info;

  mysql> desc  workers_info;

【4】新增qq和tel字段，要求tel字段位于email前，要求入职时间是最后一个字段

mysql> ALTER TABLE workers_info ADD tel varchar(15) after salary;

[sql] view plaincopyprint?

1. mysql> ALTERTABLE workers_infoADD qqint;
2. ALTER TABLE workers_infoMODIFY EmployedDatesdateafter qq;

  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 values(NULL,'xing',10000,'1598232123','[e-mail]','yanfa',736019646,20121221);

[sql] view plaincopyprint?

1. mysql> INSERTINTO workers_info (workername,salary,tel,mailbox,department,qq,EmployedDates)values('xing',10000,'1598232123','[e-mail]','yanfa',736019646,20121221);

  mysql> INSERT INTO workers_info (workername,salary,tel,mailbox,department,qq,EmployedDates) values('xing',10000,'1598232123','[e-mail]','yanfa',736019646,20121221);  

【7】修改其中两条记录的tel和mailbox
mysql> UPDATE workers_info SET mailbox = '[e-mail]' WHERE id = 14;

[sql] view plaincopyprint?

1. mysql> UPDATE workers_infoSET mailbox ='[e-mail]',tel='1246543423'WHERE id = 13;

  mysql> UPDATE workers_info SET mailbox = '[e-mail]',tel='1246543423' WHERE id = 13;

【8】查看所添加记录
mysql> select * from workers_info;

【9】查看姓名和入职时间记录
mysql> select workername,EmployedDates from workers_info;

【10】查询入职时间在2003年以前的

[sql] view plaincopyprint?

1. mysql> select *from workers_infowhereyear(EmployedDates) < 2003;

mysql> select * from workers_info where year(EmployedDates) < 2003;

[sql] view plaincopyprint?

【11】查询工资最高和最低的员工姓名

[sql] view plaincopyprint?

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

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

【12】查询平均工资

[sql] view plaincopyprint?

1. mysql> selectavg(salary)from workers_info;

mysql> select avg(salary) from workers_info;

[sql] view plaincopyprint?

【13】统计男员工人数、女员工人数

[sql] view plaincopyprint?

1. mysql> select count(男) from workers_info where sex="M";
2. mysql> select count(男) from workers_info where sex="M";

mysql> select count(男) from workers_info where sex="M";mysql> select count(男) from workers_info where sex="M";

【14】按照入职时间先后进行排序，并显示前5位员工姓名

[sql] view plaincopyprint?

1. mysql> select *from workers_infoORDERBY EmployedDates limit 5;