MySQL---创建表和连表、分组、排序等操作

首先这里有两张表做测试用

代码均在命令提示符终端完成

创建部门表

	create table department_table(
	department_id int auto_increment primary key,
	department varchar(32)
	)engine=innodb default charset=utf8;

插入数据

insert into department_table(department) values('财务'),('IT'),('业务');

显示department_table数据

select * from department_table;

结果

	+---------------+------------+
	| department_id | department |
	+---------------+------------+
	|             1 | 财务       |
	|             2 | IT         |
	|             3 | 业务       |
	+---------------+------------+

创建员工表

	create table staff_table(
	id int auto_increment primary key,
	name varchar(32),
	depart_id int,
	constraint fk_depart foreign key(depart_id) references department_table(department_id)
	)engine=innodb default charset=utf8;

插入数据

	insert into staff_table(name,depart_id) values('小孙',1),('小王',2),
	('小李',2),('小赵',1),
	('小黑',2),('小梁',1),
	('小刘',2),('小胡',2),
	('小郑',3),('小虎',3);

显示staff_table数据

select * from staff_table;

结果

	+----+--------+-----------+
	| id | name   | depart_id |
	+----+--------+-----------+
	|  1 | 小孙   |         1 |
	|  2 | 小王   |         2 |
	|  3 | 小李   |         2 |
	|  4 | 小赵   |         1 |
	|  5 | 小黑   |         2 |
	|  6 | 小梁   |         1 |
	|  7 | 小刘   |         2 |
	|  8 | 小胡   |         2 |
	|  9 | 小郑   |         3 |
	| 10 | 小虎   |         3 |
	+----+--------+-----------+

对staff_table进行分组操作：

select depart_id,count(id) '部门人数' from staff_table group by depart_id;

结果

	+-----------+--------------+
	| depart_id | 部门人数     |
	+-----------+--------------+
	|         1 |            3 |
	|         2 |            5 |
	|         3 |            2 |
	+-----------+--------------+

对staff_table按部门id大小对人员进行排序操作

asc为从小到大，desc为从大到小

select * from staff_table order by depart_id asc;

结果

	+----+--------+-----------+
	| id | name   | depart_id |
	+----+--------+-----------+
	|  1 | 小孙   |         1 |
	|  4 | 小赵   |         1 |
	|  6 | 小梁   |         1 |
	|  2 | 小王   |         2 |
	|  3 | 小李   |         2 |
	|  5 | 小黑   |         2 |
	|  7 | 小刘   |         2 |
	|  8 | 小胡   |         2 |
	|  9 | 小郑   |         3 |
	| 10 | 小虎   |         3 |
	+----+--------+-----------+

staff_table和department_table进行连表操作

select id,name,department_id,department from staff_table left join department_table on staff_table.depart_id = department_table.department_id;

结果

				+----+--------+---------------+------------+
				| id | name   | department_id | department |
				+----+--------+---------------+------------+
				|  1 | 小孙   |             1 | 财务       |
				|  4 | 小赵   |             1 | 财务       |
				|  6 | 小梁   |             1 | 财务       |
				|  2 | 小王   |             2 | IT         |
				|  3 | 小李   |             2 | IT         |
				|  5 | 小黑   |             2 | IT         |
				|  7 | 小刘   |             2 | IT         |
				|  8 | 小胡   |             2 | IT         |
				|  9 | 小郑   |             3 | 业务       |
				| 10 | 小虎   |             3 | 业务       |
				+----+--------+---------------+------------+

这里是既可以left join 也可以right join