陆
在远程或本机可以使用 mysql -h 172.5.1.183 -uroot 登陆，这个根
据第二行的策略确定
权限修改生效：
1)net stop mysql
net start mysql
2)c:\mysql\bin\mysqladmin flush-privileges
3)登陆mysql 后，用flush privileges 语句
6、创建数据库staffer
create database staffer;
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
grou