【mysql】mysql常⽤建表语句
【1】建⽴员⼯档案表
要求字段：员⼯员⼯编号，员⼯姓名，性别，⼯资，email，⼊职时间，部门。
【2】合理选择数据类型及字段修饰符，要求有NOT NULL，auto_increment, primary key等。
--
-- 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,
简单的mysql语句`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 values(NULL,'xing',10000,'1598232123','xing@qq','yanfa',736019646,20121221);
mysql> INSERT INTO workers_info (workername,salary,tel,mailbox,department,qq,EmployedDates) values('xing',10000,'1598232123','xing@qq','ya
【7】修改其中两条记录的tel和mailbox
mysql> UPDATE workers_info SET mailbox = 'haha@qq' WHERE id = 14;
mysql> UPDATE workers_info SET mailbox = 'haha@qq',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(男) from workers_info where sex="M";
mysql> select count(男) from workers_info where sex="M";
【14】按照⼊职时间先后进⾏排序，并显⽰前5位员⼯姓名 mysql> select * from workers_info ORDER BY EmployedDates limit 5;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。