MySQL学习之数据库基本操作篇

实验前准备：

1、安装mysql数据库，使用“添加/删除程序”组件，安装好mysql相关的组件，建议安

装mysql下面的全部子组件。

2、启动mysql数据库服务器程序:

service mysqld start

3、建议管理员口令，并使用它登录

Mysqladmin -u root password 密码

Mysql –u root -p

建立新用户（或者按照教材内容）

格式：grant all privileges on 数据库.* to 用户名@登录主机identified by "密码"；

例如：增加一个用户test密码为1234,让他只可以在localhost上登录，并可以对数据库Shopex 进行所有的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），在MYSQL 的提示符下：mysql> 录入grant all privileges on shopex.* to test@localhost identified by "1234"; 通过以上操作，你建立一个新的数据库shopex，并增加了一个名为test对shopex数据库有所有操作权限。

实验一：创建并使用一个数据库。

实验背景：

把MySQL安装在Ubuntu linux上之后，我们的老师和学生们仍然不能直接使用MySQL 登记或查看学生的信息。MySQL是关系数据库的一种，关系数据库的数据在逻辑上是以二维表的形式组织起来的。所以我们要先建立数据库和表。

实验目的：

掌握在MySQL中创建数据库，创建表。设置主键，设置字段默认值以及约束的方法。实验分析：

1)使用MySQL的命令创建一个数据库。

2)使用这个新建的数据库。

3)在这个数据库中创建表并设置表中字段的属性。

注意：MySQL的命令通常由SQL语句组成，命令的结尾以分号结束。（有些例外不需要加分号，例如退出的命令exit）

说明：使用MySQL的连接命令连接到MySQL，即进入了MySQL的客户端。

客户端提示符：

●mysql>表示准备好接受一条新命令。

●->为等待多行命令的下一行。

●’>为等待下一行，收集以单引号开始的字符串。

●”>为等待下一行，收集以双引号开始的字符串。

●如果想取消之前输入命令的执行，打入\c取消命令的执行。

注：此手册中用到的SQL语句语法可参考《SQL学习手册》

实验步骤：

步骤一：新建数据库和表。

1)打开终端，使用root用户连接上MySQL。使用命令：mysql –u root –p

2)新建一个数据库，名为student_management。使用命令：create database

student_management;

3)查看现在MySQL中存在的数据库。使用命令：show databases;

4)使用新创建的student_management数据库。使用命令：use student_management;

5)查看student_management数据库中的表。使用命令：show tables;

6)新建一个记录学生信息的表（student_info）。包含的字段有：学号（stu_id）、姓名

（name）、性别（sex）、班级（class）。使用的命令：create table student_info(stu_id int,name varchar(8),sex varchar(4),class varchar(16));

7)查看student_info这个表的结构。使用命令：desc student_info;

步骤二：设置表的主键、默认值及约束。

我们之前创建的student_info表中，字段的属性并不完整。我们只是设定了字段的数据类型，字段还可以有更多的属性。比如：字段是否可以为空，是否唯一，是否为主键，默认值。

1)在student_management数据库中新建教师信息表(teacher_info)。包含的字段有：教

师号（tea_id）、姓名(name)、性别（sex）、入职时间（join_time）、民族（nationality）。

教师号为主键，具有非空和唯一的属性。性别只有男或女两种选择，所以使用check 约束。民族默认为汉族。使用命令：create table teacher_info(tea_id int primary key,name varchar(8),sex varchar(4) check(sex=’男’ or sex=’女’),join_time date,nationality varchar(8) default ’汉族’)

2)查看教师信息表的结构。使用命令：desc tea_info;

扩展练习：

1)在数据库student_management中新建课程信息表(course_info)

要求包含以下字段：

课程编号(course_id)、课程名称(course_name)

其中课程编号为主键。

参考语句：

create table course_info(course_id int,course_name varchar(20),primary key course_id);

2)在数据库student_management中新建学生成绩表(student_score)

要求包含以下字段：

学号(stu_id)、课程编号(course_id)、成绩(score)

其中主键为学号和课程编号两个字段。

参考语句：

create table student_score(stu_id int,course_id int,score float,primary key(stu_id,couse_id)); 实验二：对数据库的数据操作

实验背景：

在MySQL中创建好了数据库及表。软件学院的辅导员就可以往学生信息表中插入本班学生的信息。辅导员在需要的时候也可以修改或删除学生信息表中的数据。

实验目的：

掌握向数据库中插入记录、更新记录、删除记录的方法。

实验步骤：

步骤一：向学生信息表(student_info)中插入学生的信息。

1)打开终端，使用assistant用户连接上数据库，并使用student_management数据库。

注：assistant用户为《MySQL学习手册之linux下安装配置篇》中创建的用户，是辅导员使用的用户。

2)向学生信息表中插入数据。使用命令：insert into student_info values(20080201,’

张帅’,’男’,’计算机软件’);

3)重复上一步操作，插入一部分学生信息的数据。

步骤二：修改学生信息表(student_info)中某学生的信息。

因为王青同学中途更换了专业，由计算机网络改为了计算机软件。在我们的数据库中也需要更改王青同学的信息。

使用命令：update student_info set class=’计算机软件’ where name=’王青’;

步骤三：删除某学生的信息。

张玲同学因为种种原因退学了，在我们的数据库中需要删除张玲同学的信息。

使用命令：delete from student_info where name=’张玲’;

扩展练习：

向实验一的扩展练习中所创建的两个表course_info和student_score内插入数据。

注意：插入数据的格式可参考如下。数据可以自己定，但是student_score表中的stu_id 和course_id两个字段中的数据必须要和student_info表和course_info表中的数据对应。

实验三：查询数据：

实验背景：

学生的个人信息和成绩已经登记在数据库中了。现在就可以使用学生（student）用户连接到MySQL查询学生的个人信息和成绩了。

实验目的：

掌握：单表查询、分组查询、联合查询的方法。

实验分析：

1)单表查询。仅在一个表中查询，将查询的数据按某种方式排序。

2)分组查询。将数据分组查询。

3)联合查询。将相关的几个表通过某些关系连接起来，查询我们需要的数据。

实验步骤：

步骤一：单表查询。

1)在终端中使用student用户连接到数据库。使用命令：mysql –u student

注：student用户为《MySQL学习之linux下安装配置篇》中创建的用户，是学生使用的用户。

2)切换到student_management数据库。使用命令:use student_management;

3)查询所有学生的个人信息，并按照学号排序。使用命令：select * from student_info

order by stu_id;

4)查询李飞同学的学号和所在的班级。使用命令：select stu_id,class from

student_info where name=’李飞’;

5)查询学生信息表中的记录数。使用命令：select count(*) from student_info;

步骤二：分组查询。

查询学生信息表中各班男女学生的人数。使用命令：select class,sex,count(*) from student_info group by class,sex;

步骤三：联合查询。

1)查询指定班级，课程的平均分。

将学生信息表，课程信息表和成绩表联合。查询计算机软件班的大学英语这门课的平均成绩。使用avg函数计算平均分。

使用命令：select avg(c.score) as ‘平均分’ from student_info a,course_info b,student_score c where a.stu_id=c.stu_id and b.course_id=c.course_id and b.course_name=’大学英语’ and a.class=’计算机软件’;

as的作用是给字段起别名，显示的时候含义更明确。

2)查询各门课程的平均分。

把课程信息表和成绩表通过课程编号（course_id）联合，按课程编号和课程名称分组，并使用avg函数计算出来平均分。使用命令：select a.course_name,avg(score) from course_info a,student_score b where a.course_id=b.course_id group by

a.course_id,a.course_name;

3)查询大学英语的最高分和最低分。

将课程信息表和成绩表通过课程编号联合，查询出大学英语的最高分和最低分。

使用命令：select max(score) as ‘最高分’,min(score) as ‘最低分’ from course_info a,student_score c where a.course_id=c.course_id and

a.course_name=’大学英语’;

扩展练习：

1)查询是否有名叫王青的女生。

参考命令：select * from student_info where name=’王青’ and sex=’女’;

2)查询男生的总数。

参考命令：select count(*) as ‘男生总数’from student_info where sex=’

男’;

3)查询张帅的高等数学成绩。

参考命令：select name,score from student_info a,student_score

b,course_info c where a.stu_id=b.stu_id and b.course_id=c.course_id and

https://www.360docs.net/doc/d9983093.html,=’张帅’ and c.course_name=’高等数学’;

实验四：修改表的结构。

实验背景：

在我们使用数据库的过程中，往往会因为某些原因去修改数据库中表的结构。比如：学生信息表中记录的信息太少，我们希望增加一个记录学生手机号码的字段(tel_num)等等。我们可以使用一些修改表结构的SQL语句来实现修改。

实验目的：

掌握：使用SQL语句在表中增加字段、修改字段名称、修改字段属性和删除字段的方法。

实验步骤：

步骤一：添加字段

1)使用root用户连接MySQL。

2)使用student_management数据库。

3)在学生信息表(student_info)中添加一个字段，记录学生的电话号码（tel_num）。

使用命令:alter table student_info add tel_num int(11);

4)查看效果。使用命令desc student_info;

步骤二：修改字段名称

我们发现在学生信息表中，学生的姓名（name）这个字段容易同别的表中的字段混淆。为了表示的更加明确我们希望将name改为stu_name。

1)使用命令：alter table student_info change name stu_name varchar(8);

2)查看效果。使用命令：desc student_info;

步骤三：修改字段属性

在学生信息表中，学号stu_id这个字段应该是这个表的主键。但是我们在建表时并没有把这个字段设为主键。现在我们就可以修改stu_id的属性，让他成为主键。

1)使用命令：alter table student_info modify stu_id int primary key;

2)查看效果。使用命令：desc student_info;

步骤四：删除字段

1)某些时候我们希望删除某一字段，比如现在我们想在学生信息表中将电话这个字段

删除。可以使用命令:alter table student_info drop column tel_num;

2)查看效果。使用命令：desc student_info;

注意：删除字段，此字段所存储的数据会丢失。所以删除字段时一定要慎用，以免造成重要数据丢失的损失。

扩展练习

1)向课程信息表中添加一个任课老师姓名的字段name。

参考命令：alter table course_info add name varchar(6);

2)将添加的name字段改名成tea_name。

参考命令：alter table course_info change name tea_name varchar(6);

3)将tea_name字段由varchar(6)改为varchar(8)并且不能为空。

参考命令：alter table course_info modify tea_name varchar(8) not null;

4)删除tea_name这个字段。

参考命令：alter table course_info drop column tea_name;

实验五：数据库数据的导入、导出。

实验背景：

为了数据的安全，在日常维护数据库的维护中数据库管理员通常会对数据库中的数据进行备份。

实验目的：

掌握MySQL中数据导入、导出的方法。

实验分析：

有两种方法可以实现数据的备份。

1)直接拷贝数据文件。

2)使用命令mysqldump将数据导出为SQL脚本，使用MySQL的命令source 将SQL脚

本导入数据库。

实验步骤：

步骤一：直接拷贝数据文件方式实现导入、导出。

注意：mysql默认的数据文件存放路径为/var/lib/mysql，每个数据库是以数据库名称命名的文件夹。

1)在ubuntu的根目录下新建一个文件夹mysql-backup。用来存放MySQL的备份文件。

使用命令：sudo mkdir /mysql-backup

2)将/var/lib/mysql目录中的student_management文件夹复制到/mysql-backup文件夹

中。使用命令：sudo cp -r /var/lib/mysql/student_management /mysql-backup

3)确认数据文件已经复制到/mysql-backup文件夹中。使用命令：ls /mysql-backup

4)现在假设数据库出问题了，我们删除MySQL数据文件夹下的student_management

文件夹。使用命令：sudo rm -r /var/lib/mysql/student_management

5)使用root用户连接MySQL，并查看数据库。

使用命令：mysql –u root –p和show databases;

6)退出MySQL，将备份的数据文件重新拷贝回MySQL的数据文件存放位置。使用命

令：sudo cp -r /mysql-backup/student_management /var/lib/mysql

7)更改拷贝回来的数据文件所有者和所在组为mysql。使用命令：sudo chown -hR

mysql:mysql /var/lib/mysql/student_management;(为什么要更改数据文件所有者和所在组？请阅读附录一：数据文件需要更改所有者的原因。)

8)再次使用root用户连接MySQL，查看数据库。使用命令：mysql –u root –p和show

databases;

步骤二：使用命令导入、导出数据。

1)使用mysqldump命令将数据库student_management中的数据导出为SQL的脚本文

件。在终端中使用命令：mysqldump –u root –p --opt student_management > mysql.20090305(注意在此命令中“>”后可以为脚本文件所在的路径，如果不写路径直接写文件名则为在当前目录下创建文件，文件存放的路径需要linux系统当前用户有可写权限。mysqldump命令请参考附录二：mysqldump命令详解)

建议：备份出的脚本文件最好由mysql和备份的日期组成，这样方便日后在恢复的时候找到需要的数据。不会因为备份的文件太多而产生混乱。

2)查看是否生成这个文件。在上一步命令中指定的脚本文件生成目录中执行ls。（如

果没有指定生成文件的路径，则为在当前目录下生成脚本文件。）

3)假设数据库出现破坏。删除student_management数据库的数据文件。使用命令：

sudo rm –r /var/lib/mysql/student_management

4)使用root用户连接数据库。在终端中使用命令mysql –u root –p

5)使用SQL语句创建我们需要恢复的数据库student_management(也可以使用其他名

字)。使用命令：create database student_management;

6)使用刚才创建的数据库。使用命令：use student_management;

7)导入备份的脚本文件。使用命令：source /home/qingcha/mysql.20090305

注意：在此/home/qingcha是备份文件所在目录。此目录因系统不同而不同。

8)查看是否恢复数据。使用命令：show tables;和select * from student_info;

实验六：删除数据库和表。

实验背景：

目前我们使用的这个学生档案数据库是一个非常简单的数据库。之后我们会重新建一个学生档案管理数据库。现在我们就把目前这个中的表和数据库删除。

实验目的：

掌握MySQL中删除表和数据库的方法。

实验步骤：

步骤一：在MySQL中删除表。

1)在终端使用root用户登陆MySQL数据库。使用student_management数据库。在终

端中使用命令：mysql –u root –p、在MySQL命令行中使用命令：use student_management;

2)查看目前存在的表。使用命令：show tables;

3)删除course_info表。使用命令：drop table course_info;

4)查看执行删除命令后数据中存在的表。使用命令：show tables;

步骤二：删除MySQL中的数据库。

1)查看MySQL系统中存在的数据库。使用命令：show databases;

2)删除student_management数据库。使用命令：drop database

student_management;

3)查看执行删除数据库命令后，MySQL系统中存在的数据库。使用命令：show

databases;

附录一：数据文件需要更改所有者的原因。

linux是多用户操作系统，所有的文件都有所有者和所属用户组以及文件的读取权限这些属性。这些属性共同决定了一个文件哪些用户可以读取，那些用户可以更改等等。

在备份出来之前，这些数据文件的所有者和所在组都为mysql。

而当我们备份出来之后，这些文件的所有者和所在组改成了root，并且这些文件的读取属性为640。即mysql用户没有权限读取这些文件。

如果我们不更改文件的属性，直接复制到MySQL的数据文件目录。会出现下图所示的错误。

当然除了chown这种方法，我们还可以使用chmod命令更改文件可以被mysql用户读写。但是出于安全方面的考虑，避免linux系统中其他用户误使用其他的一些文本编辑器vim 或emacs等更改数据文件的内容。我们建议使用实验中chown这种方式。

附录二：mysqldump命令详解。

命令格式：

mysqldump [OPTIONS] database [table] > target_file

命令作用：

将数据库中的数据导出为SQL脚本文件。如果指定table则仅导出表中的数据。

参数详解：

--add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。

--add-drop-table

在每个create语句之前增加一个drop table。

-c, --complete-insert

使用完整的insert语句(用列名字)。

-h, --host=..

从命名的主机上的MySQL服务器导出数据。缺省主机是localhost

-u user_name, --user=user_name

与服务器连接时，MySQL使用的用户名。

-p ,--password[password]

Mysql使用用户名的密码

-d, --no-data

不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的!

--opt(常用选项)

同--quick --add-drop-table --add-locks --extended-insert --lock-tables。

应该给你为读入一个MySQL服务器的尽可能最快的导出。

-q, --quick

不缓冲查询，直接导出至stdout;

其他更多选项参见:

--help

显示一条帮助消息并且退出。