数据库教程mysql实验6_mysql实验教程

一、实验环境：

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)