建表
- 用户表
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;
union
和union all
的使用和区别union
和union all
都可以将多个select
查询组合成一个结果集union
和union all
组合的select
查询的字段数必须一样- 如下一个查询
*
一个只只查询name
就会报错
mysql> select * from user union all select name from user where name='李四'; ERROR 1222 (21000): The used SELECT statements have a different number of columns
union
会过滤相同的数据,如下
mysql> select name from user union select name from user where name='李四'; +--------+ | name | +--------+ | 张三 | | 李四 | | 王五 | | 赵六 | | 刘七 | | 宋八 | +--------+ 6 rows in set (0.00 sec)
union all
不会过滤相同的数据
mysql> select name from user union all select name from user where name='李四'; +--------+ | name | +--------+ | 张三 | | 李四 | | 王五 | | 赵六 | | 李四 | | 刘七 | | 宋八 | | 李四 | | 李四 | +--------+ 9 rows in set (0.00 sec)