MYSQL常用命令大全（三）【值得收藏】

【友情提示】你必须首先登录到MySQL中，以下操作都是在MySQL的提示符下进行的且每个命令以分号结束。

7、下面的语句在mysql环境在执行

显示用户拥有权限的数据库 show databases;

切换到staffer数据库 use staffer;

显示当前数据库中有权限的表 show tables;

显示表staffer的结构 desc staffer;

8、创建测试环境

1)创建数据库staffer

mysql> create database staffer

2)创建表staffer,department,position,depart_pos

create table s_position(

id int not null auto_increment,

name varchar(20) not null default '经理', #设定默认值

description varchar(100),

primary key PK_positon (id) #设定主键

);

create table department(

id int not null auto_increment,

name varchar(20) not null default '系统部', #设定默认值

description varchar(100),

primary key PK_department (id) #设定主键

);

create table depart_pos(

department_id int not null,

position_id int not null,

primary key PK_depart_pos (department_id,position_id) #设定复和主键

);

create table staffer(

id int not null auto_increment primary key, #设定主键

name varchar(20) not null default '无名氏', #设定默认值

department_id int not null,

position_id int not null,

unique (department_id,position_id) #设定唯一值

);

3)删除

mysql>

drop table depart_pos;

drop table department;

drop table s_position;

drop table staffer;

drop database staffer;

9、修改结构

mysql>

#表position增加列test

alter table position add(test char(10));

#表position修改列test

alter table position modify test char(20) not null;

#表position修改列test默认值

alter table position alter test set default 'system';

#表position去掉test默认值

alter table position alter test drop default;

#表position去掉列test

alter table position drop column test;

#表depart_pos删除主键

alter table depart_pos drop primary key;

#表depart_pos增加主键

alter table depart_pos add primary key PK_depart_pos (department_id,position_id);

10、操作数据

#插入表department

insert into department(name,description) values('系统部','系统部');

insert into department(name,description) values('公关部','公关部');

insert into department(name,description) values('客服部','客服部');

insert into department(name,description) values('财务部','财务部');

insert into department(name,description) values('测试部','测试部');

#插入表s_position

insert into s_position(name,description) values('总监','总监');

insert into s_position(name,description) values('经理','经理');

insert into s_position(name,description) values('普通员工','普通员工');

#插入表depart_pos

insert into depart_pos(department_id,position_id)

select a.id department_id,b.id postion_id

from department a,s_position b;

#插入表staffer

insert into staffer(name,department_id,position_id) values('陈达治',1,1);

insert into staffer(name,department_id,position_id) values('李文宾',1,2);

insert into staffer(name,department_id,position_id) values('马佳',1,3);

insert into staffer(name,department_id,position_id) values('亢志强',5,1);

insert into staffer(name,department_id,position_id) values('杨玉茹',4,1);

11、查询及删除操作

#显示系统部的人员和职位

select a.name,b.name department_name,c.name position_name

from staffer a,department b,s_position c

where a.department_id=b.id and a.position_id=c.id and b.name='系统部';

#显示系统部的人数

select count(*) from staffer a,department b

where a.department_id=b.id and b.name='系统部'

#显示各部门的人数

select count(*) cou,b.name

from staffer a,department b

where a.department_id=b.id

group by b.name;

#删除客服部

delete from department where name='客服部';

#将财务部修改为财务一部

update department set name='财务一部' where name='财务部';