一、实验环境:
windows 7+mysql 5.5.15
二、 实验步骤:
1、 在命令行中输入:mysql –uroot –p,登陆mysql数据库。
2、创建实验环境:
1)创建数据库staffer:createdatabase staffer;
2)切换到staffer数据库:use staffer;
3)创建表staffer,department,position,depart_pos:
create tables_position (
id int notnull auto_increment,
name varchar(20) not null default 'manager', #设定默认值
descriptionvarchar(100),
primary keyPK_positon (id) #设定主键
);
create table department(
id int not null auto_increment,
namevarchar(20) not null default 'AdminDept', #设定默认值
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 'none',
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);
显示当前数据库中有权限的表:show tables;
显示表staffer的结构:desc staffer;
desc depart_pos;
desc department;
desc s_position;
4)修改表结构
4.1、在表s_position中增加列test:alter table s_position add(test char(10));
4.2、表s_position修改列test:alter table s_position modifytest char(20) not null;
4.3、表s_position修改列test默认值:alter table s_position alter testset default 'system';
4.4、表s_position去掉test默认值:alter table s_position alter testdrop default;
4.5、表s_position去掉列test:alter table s_position dropcolumn test;
4.6、删除表depart_pos的主键:alter table depart_pos dropprimary key;
4.7、增加表depart_pos的主键:alter table depart_pos addprimary key PK_depart_pos (department_id,position_id);
5)操作数据:
5.1、插入表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('测试部','测试部');
5.2、插入表s_position:
insert into s_position(name,description) values('总监','总监');
insert into s_position(name,description) values('经理','经理');
insert into s_position(name,description) values('普通员工','普通员工');
5.3、插入表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;
5.4、插入表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);
5.5、查询及删除操作
1)显示系统部的人员和职位:
select a.name,b.name department_name,c.name from staffer a,department b,s_position c where a.department_id=b.id and a.position_id=c.id and b.name='系统部';
2)显示系统部的人数:
select count(*) from staffer a,department b where a.department_id=b.id and b.name='系统部'
3)显示各部门人数:
select count(*) cou,b.name from staffer a,department b where a.department_id=b.id group by b.name;
4)删除客服部:
delete from department where name='客服部';
5)将财务部修改为财务一部:
update department set name='财务一部' where name='财务部';
6、备份和恢复:
1)备份数据库staffer:mysqldump –uroot –p staffer>e:\staffer.sql
2)恢复:注意上面的sql语句没有创建staffer数据库的语句,所以在恢复之前需要先创建数据库staffer:
Create staffer; use staffer; source e:\\staffer.sql
三、实验总结
在做本实验时,由于在安装mysql时未设置默认字符编码为utf-8,出现了中文字符乱码情况,解决方法:
1、查看默认字符编码情况:
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\mysql-5.5.10\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.02 sec)
mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | gbk_chinese_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
2、关闭mysql服务,在mysql安装目录下,my.ini文件进行如下修改,没有则直接进行添加:
[client]
#修改客户端默认字符编码格式为utf8
efault-character-set=utf8
[mysqld]
#修改服务器端默认字符编码格式为utf8
character-set-server = utf8
3、修改后,再次输入命令查看,显示结果如下 ============================================================================== mysql> show variables like 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | D:\mysql-5.5.10\share\charsets\ | +--------------------------+---------------------------------+ 8 rows in set (0.00 sec) mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)