mysql操作命令（二）

http://blog.youkuaiyun.com/AlvaPan/article/details/53446351  mysql操作命令（一）

1、删除表

DROP TABLE book;

2、删除表字段

alter table `dept` drop column `updateUser`;

3、修改表名

rename table `user` to `user_tabe`;

4、向一张表中插入一条数据

insert into dept_information values(1,'人力资源部','分管人力资源','','111','1');

5、修改表数据

update dept_information set name='a部门' where id=1;

6、删除表数据

delete from dept_information where id=1;

7、查询表所有信息

SELECT * from dept_information；

8、查询表所需字段并重命名查出来的字段名

SELECT 
`id` as `id`,
`name` as `nameDept`
from dept_information
WHERE status=1

8、关联查询

select 
ut.id as id,
ut.`user_name` as name,
ut.`user_dept` as dept,
ut.`remark` as zhiwei,
di.`name` as nameDept
from user_tabe ut
JOIN dept_information di on ut.user_dept = di.id
WHERE ut.`status`=1

注意：小表名一般为表名开头字母缩写

9、关联查询显示

select a.id as id,a.name as name,a.nameDept as dept,
a.zhiwei as zhiwei
FROM(
select 
ut.id as id,
ut.`user_name` as name,
ut.`user_dept` as dept,
ut.`remark` as zhiwei,
di.`name` as nameDept
from user_tabe ut
JOIN dept_information di on ut.user_dept = di.id
WHERE ut.`status`=1) a