1.实验内容及原理
1. 在 Windows 系统中安装 VMWare 虚拟机,在 VMWare 中安装 Ubuntu 系统,并在
Ubuntu 中搭建 LAMP 实验环境。
2. 使用 MySQL 进行一些基本操作:
(1)登录 MySQL,在 MySQL 中创建用户,并对新建的用户赋予权限。
(2)创建并跳转到新的数据库,显示所有数据库和当前数据库。
(3)显示所有的表和查看表的属性。
(4)导入 emp loyees 和 sakila 两个样例数据库,对这两个数据库进行完整性检查,对这两个数据库进行备份、导入与导出。
3. SQL DDL 使用
(1) 创建 DDL 脚本,包括创建、删除表,指定主键、候选键和外键;
(2)修改并展示表结构;
(3)创建、删除索引。
4. SQL DDL & DML 使用
(1)数据的增、删、查、改等操作
(2)掌握视图的创建、删除和更新
5. 简单查询
(1)使用 order by、group by、having 等子句;
(2)使用各种谓词;
(3)使用集合函数;
(4)在时间字段上查询。
6. 复杂查询
(1)嵌套子查询;
(2)多表连接查询,包括左连接、右连接、外连接、 自连接。
7. 创建存储过程、函数和触发器,调用创建的存储过程、函数,触动触发器。
8. 安装并使用 phpmyadmin。
2.实验步骤与分析
1. 自行在 Windows 系统中安装 VMWare 虚拟机,在 VMWare 中安装 Ubuntu 系统,在
Ubuntu 中安装 LAMP 步骤如下(也可使用安装菜单):
sudo apt-get install updatesudo apt-get install apache2sudo apt-get install mysq l-serversudo apt-get install php
2. 熟悉 MySQL 的基本操作步骤如下:
(1)进入 MySQL:mysq l -u root -p
(2)创建新用户:CREATE USER 'newuser'@' localhost' IDENTIFIED BY 'password';
(3)赋予权限:GRANT ALL PRIVILEGES ON * . * TO 'newuser'@' localhost';
(4)创建新的数据库:CREATE DATABASE newdbname;
(5)跳转到新创建的数据库:USE newdbname;3
(6)显示所有数据库和当前数据库:SHOW DATABASES; SELECT DATABASE();
(7)显示所有的表并查看表的属性:SHOW TABLES; DESCRIBE tabname;
(8)导入两个样例数据库
① 解压数据包:unzip test_db_master.zip
② 进入数据包目录:cd test_db_master
③ 导入 emp loyee 数据库:sudo sudo mysql -t < emp loyees.sql
④ 解压数据包:unzip sakila-db.zip
⑤ 进入数据包目录:cd sakila-db
⑥ 进入 MySQL,导入 sakila schema 数据:SOURCE /path/to/sakila-schema.sq l
⑦ 导入 sakila 数据:SOURCE /path/to/sakila-data.sq l
(9)对导入的两个数据库进行完整性检查
① 查看外键检查状态:show variables like ‘%foreign_key_checks% ’;
② 查看主键检查状态:show variables like ‘%unique_checks% ’;
③ 查看外键检查结果:select @@foreign_key_checks;
④ 查看主键检查结果:select @@unique_checks;
(10)数据库备份(以 employee 为例)
① 不导出任何数据,只导出数据库表结构:
mysqldump -u test_02 -p --no-data emp loyees> employees_bak1.sq l
② 只导出数据,而不添加 CREATE TABLE 语句:
mysqldump -u test_02 -p --no-create-info employees> emp loyees_bak2.sq l
③ 导出全部数据库:
mysqldump -u test_02 -p emp loyees> emp loyees_bak3.sq l
(11)导出和导入(以 sakila 为例)
① 导出为 TXT 文件:
select * from country into outfile '/your/file/path/country';
② 导入 TXT 文件:
load data infile '/your/file/path/country' into table country;
③ 导出为 CSV 文件:4
select * from country into outfile '/your/file/path/country.csv';
④ 导入 CSV 文件:
load data infile '/your/file/path/country.csv' into table country;
⑤ 导出为 xml 文件:
mysql -u test_02 -p --xml -e 'select * from sakila.country' >
/your/file/path/country.xml
3. SQL DDL 使用
(1)使用 create 语句创建项目所有的表;
(2)使用 alter 命令修改表结构,删除某个表中的“xxx ”字段;修改某个表中
的“xxx ”字段的类型为 char(2),该字段不能为空,默认值为“m”;
(3)添加类型为 char(2)的‘xxx ’字段数据,添加是否成功,如果失败分析
失败的原因并进行必要的操作使字段添加成功;
(4)修改某个表,添加类型为 varchar2(18)的字段,并添加 check 约束,要求该
字段的长度为 18,并且只能由数字组成,并且指定该字段为候选键;
(5)设置某个表中某个字段的 check 约束为大于 0;设置某个字段默认值为“未
审核 ”,设置某个 ”字段的 check 约束为“未审核 ”、“ 审核已通过 ”、“ 审核不通
过”;
(6)在某个表中创建索引;
(7)使用 describe 命令展示表结构。
4. SQL DDL & DML 使用
(1) 使用 insert 语句将数据插入到相应的表中;
(2) 使用 delete 语句删除表中有关联表外建对应的记录,能否成功删除,如
果不能请分析原因;
(3) 使用 update 语句更新外键数据,能否成功修改,如果不能请分析原因。;
(4) 创建视图;
(5) 对视图进行查询操作;
(6) 对表进行联合查询操作;
(7) 更新视图,分析更新操作可以执行成功或失败的原因。5
5. 简单查询
(1) 查询性别为“ 男 ”的所有学生的学号、姓名和班级号;(单表简单查询)
(2) 查询 xxx 表,获得性别为“女 ”的记录,结果按照班级 ID 降序排列;(order
by)
(3) 查询 xxx 表,按照年龄从小到大排序;(获取子串函数、order by)
(4) 查询学号以“2002 ”开头的学生信息,字段包括学号、姓名、班级号;(使用子
串函数)
(5) 查询学号中包含“01 ”的学生信息,字段包括学号、姓名、班级号;(like)
(6) 查询状态为“未审核 ”,且申请时间在 2013 年 9 月 4 日之后的请假申请
单的信息,包含申请时间为 9 月 4 日的申请单;(单表多条件查询)
(7) 查询审核状态为“未审核 ”和“ 审批已通过 ”两种类型的申请单 ID;(in)
(8) 查询时间在 2013 年 8 月 31 日和 2013 年 9 月 2 日之间所提交的申请单 ID,
请假原因; (between、时间)
(9) 查询 XXX 老师所教课程的的选课人次(一人选两门课程,算两人次);
(10) 查询 XXX 老师所教课程的的选课人数(一人选两门课程,算一人);(distinct)
(11) 查询 XX 老师对请假单审核不通过的请假原因与学生姓名;
(12) 统计每门课的学生的个数(集合函数);
(13) 查询选课人数超 过 3 人 的课程 号 , 并按课程 号 降序排列( group by,
having,order by …desc)。
6. 复杂查询
(1)查询审批通过人数最多的课程名称和教师姓名;
(2)查询选课人数最多和第二多的课程名称和任课老师姓名; (选做)
(3)查询只选了课程《算法设计》的学生姓名;
(4)查询选修了全部课程的学生姓名;
(5)查询选修了课程 3 的学生学号、姓名、身份证号;
(6)据学生学号将 xxx 表与 xxx 表做自然连接查询;
(7)据学号将 xxx 表与 xxx 表进行左连接查询,并解释这样做所具有的业务含 6
义;
(8)据学号将 xxx 表与 xxx 表进行右连接查询,并解释这样做所具有的业务含
义;
(9)总结自然连接、左连接、右连接查询在产生的结果上面有什么区别。
7. 创建并调用存储过程、函数和触发器
(1)创建并调用存储查询过程(以 emp loyee 为例)
① 创建一个查询存储过程:
> delimiter##> create procedure select_manager(in fname varchar(20), in lname varchar(20))-> begin->select * from dept_manager natural join emp loyees where first_name = fnameandlast_name = lname;->end> ##
② 调用这个存储查询过程:call select_manager('Xiaobin','Spinelli');
(2)创建并调用函数(以 emp loyee 为例)
① 创建函数:
> delimiter ##> create function title_num(title_name varchar(50)) returns int> begin> declare num int;> select count(title) from titles where to_date > curdate() and title =title_name groupby title into num;> return num;> end> ##set g lobal log_bin_trust_function_creators=1;
② 调用函数:select title_num("manager");7
(3)创建并调用触发器(以 emp loyee 为例)
① 查看表 dept_manager:select * from dept_manager;
② 创建新表 quit:create table quit (emp_no int, depr_no char(4), quit_date
date);
③ 创建触发器:
mysql> delimiter ##mysql> create trigger after_ insert_ quit after insert on quit for each row-> begin-> update dept_ manager set to_ date = curdate( ) where emp_ no = 110039;-> end-> ##
④ 触发触发器: insert into quit values (110039, 'd001', curdate());
⑤ 查看新表 quit:select * from quit;
⑥ 再次查看表 dept_manager:select * from dept_manager;
8. 安装 phpmyadmin 步骤如下:
(1)安装 phpmyadmin:sudo apt-get install phpmyadmin
(2)安装 php-mbstring:sudo apt-get install php-mbstring
(3)安装 php-gettext:sudo apt-get install php-gettext
(4)编辑 php.ini: vim /path/to/php. ini 修改;extension=php-mbstring.so
(5)重启 Apache2:sudo /path/to/apache2 restart
(6)查看 IP 地址: ifconfig
(7)登录 phpmyadmin:从浏览器地址 your. ip.4.address/phpmyadmin
3.实验结果与总结
1. 自行在 Windows 系统中安装 VMWare 虚拟机,在 VMWare 中安装 Ubuntu 系统,在
Ubuntu 中安装 LAMP :
sudo apt-get install updatesudo apt-get install apache2sudo apt-get install mysq l-serversudo apt-get install php
安装 LAMP
2. 熟悉 MySQL 的基本操作:
(1)进入 MySQL:mysq l -u root -p
(2)创建新用户:CREATE USER 'newuser'@' localhost' IDENTIFIED BY 'password';
(3)赋予权限:GRANT ALL PRIVILEGES ON * . * TO 'newuser'@' localhost';
(4)创建新的数据库:CREATE DATABASE newdbname;
(5)跳转到新创建的数据库:USE newdbname;3
(6)显示所有数据库和当前数据库:SHOW DATABASES; SELECT DATABASE();
(如上图所示,mysql 基本操作均正常进行)
(其中的创建新用户步骤)
(7)显示所有的表并查看表的属性:SHOW TABLES; DESCRIBE tabname;
(8)导入两个样例数据库
① 解压数据包:unzip test_db_master.zip
(解压过程中)
(导入 sakila 数据库过程中)
② 进入数据包目录:cd test_db_master
③ 导入 emp loyee 数据库:sudo sudo mysql -t < emp loyees.sql
④ 解压数据包:unzip sakila-db.zip
⑤ 进入数据包目录:cd sakila-db
⑥ 进入 MySQL,导入 sakila schema 数据:SOURCE /path/to/sakila-schema.sq l
⑦ 导入 sakila 数据:SOURCE /path/to/sakila-data.sq l
(导入过程正常运行)
(9)对导入的两个数据库进行完整性检查
① 查看外键检查状态:show variables like ‘%foreign_key_checks% ’;
② 查看主键检查状态:show variables like ‘%unique_checks% ’;
③ 查看外键检查结果:select @@foreign_key_checks;
④ 查看主键检查结果:select @@unique_checks;
(四种检查的结果)
(10)数据库备份(以 employee 为例)
① 不导出任何数据,只导出数据库表结构:
mysqldump -u test_02 -p --no-data emp loyees> employees_bak1.sq l
② 只导出数据,而不添加 CREATE TABLE 语句:
mysqldump -u test_02 -p --no-create-info employees> emp loyees_bak2.sq l
③ 导出全部数据库:
mysqldump -u test_02 -p emp loyees> emp loyees_bak3.sq l
(11)导出和导入(以 sakila 为例)
① 导出为 TXT 文件:
select * from country into outfile '/your/file/path/country';
② 导入 TXT 文件:
load data infile '/your/file/path/country' into table country;
③ 导出为 CSV 文件:4
select * from country into outfile '/your/file/path/country.csv';
④ 导入 CSV 文件:
load data infile '/your/file/path/country.csv' into table country;
⑤ 导出为 xml 文件:
mysql -u test_02 -p --xml -e 'select * from sakila.country' >
/your/file/path/country.xml
(导出过程)
(导出的文本文档内容)
(导出结果)