mysql面试题之关于建表的基本操作

建表

用户表

create table `user` (
`id` int(10) unsigned not null auto_increment,
`name` varchar(32) default null,
`age` tinyint(4) default null,
`sex` char(4) default null,
primary key (`id`)
) engine=myisam default charset=utf8

学生表

create table `student` (
`id` int(10) unsigned not null auto_increment,
`user_id` int(11) default null,
`major` varchar(64) default null,
`class` varchar(64) default null,
primary key (`id`),
unique key `unique_user_id` (`user_id`)
) engine=myisam default charset=utf8

---

插入数据

insert into user(name,age,sex) values('张三',18,'男');
insert into user(name,age,sex) values('李四',19,'女');
insert into user(name,age,sex) values('王五',18,'女');
insert into user(name,age,sex) values('赵六',20,'男');
insert into user(name,age,sex) values('李四',20,'男');

insert into student(user_id,major,class) values(2,'计算机','计算机151班');
insert into student(user_id,major,class) values(1,'机电','机电151班');
insert into student(user_id,major,class) values(3,'商学院','商学151班');
insert into student(user_id,major,class) values(4,'经管','经管151班');

查找字段中重复的数据

 select name from user group by name having count(*)>1;