数据库——SQL DDL&DML使用

1.实验内容及原理

1. 在 Windows系统中安装 VMWare虚拟机，在VMWare中安装Ubuntu系统,并在

Ubuntu中搭建 LAMP实验环境。

2. 使用 MySQL进行一些基本操作：

（1）登录 MySQL，在 MySQL中创建用户，并对新建的用户赋予权限。

（2）创建并跳转到新的数据库，显示所有数据库和当前数据库。

（3）显示所有的表和查看表的属性。

（4）导入 employees和 sakila两个样例数据库，对这两个数据库进行完整性检查，

对这两个数据库进行备份、导入与导出。

3. SQLDDL使用

（1）创建 DDL脚本，包括创建、删除表，指定主键、候选键和外键；

（2）修改并展示表结构；

（3）创建、删除索引。

4. SQLDDL&DML使用

（1）数据的增、删、查、改等操作

（2）掌握视图的创建、删除和更新

5.简单查询

（1）使用 orderby、groupby、having等子句；

（2）使用各种谓词；

（3）使用集合函数；

（4）在时间字段上查询。

6.复杂查询

（1）嵌套子查询；

（2）多表连接查询，包括左连接、右连接、外连接、自连接。

7. 创建存储过程、函数和触发器，调用创建的存储过程、函数，触动触发器。

8. 安装并使用 phpmyadmin。

2.实验步骤与分析

1.自行在 Windows系统中安装VMWare虚拟机，在VMWare中安装Ubuntu系统，在

Ubuntu中安装 LAMP步骤如下（也可使用安装菜单）：

sudo apt-getinstallupdatesudoapt-getinstallapache2sudoapt-getinstallmysql-serversudoapt-getinstallphp

2.熟悉 MySQL的基本操作步骤如下：

（1）进入 MySQL：mysql-uroot-p

（2）创建新用户：CREATEUSER’newuser’@’localhost’ IDENTIFIEDBY’password’;

（3）赋予权限：GRANTALLPRIVILEGESON* . *TO’newuser’@’localhost’;

（4）创建新的数据库：CREATEDATABASEnewdbname;

（5）跳转到新创建的数据库：USEnewdbname;3

（6）显示所有数据库和当前数据库：SHOWDATABASES; SELECTDATABASE();

（7）显示所有的表并查看表的属性：SHOWTABLES; DESCRIBEtabname;

（8）导入两个样例数据库

① 解压数据包：unziptest_db_master.zip

② 进入数据包目录：cdtest_db_master

③ 导入 employee数据库：sudosudomysql-t<employees.sql

④ 解压数据包：unzipsakila-db.zip

⑤ 进入数据包目录：cdsakila-db

⑥ 进入 MySQL，导入 sakilaschema数据：SOURCE/path/to/sakila-schema.sql

⑦ 导入 sakila数据：SOURCE/path/to/sakila-data.sql

（9）对导入的两个数据库进行完整性检查

① 查看外键检查状态：showvariableslike‘%foreign_key_checks%’;

② 查看主键检查状态：showvariableslike‘%unique_checks%’;

③ 查看外键检查结果：select@@foreign_key_checks;

④ 查看主键检查结果：select@@unique_checks;

（10）数据库备份（以 employee为例）

① 不导出任何数据，只导出数据库表结构：

mysqldump-utest_02 -p –no-dataemployees> employees_bak1.sql

② 只导出数据，而不添加 CREATETABLE语句：

mysqldump-utest_02 -p –no-create-infoemployees> employees_bak2.sql

③ 导出全部数据库：

mysqldump-utest_02 -pemployees> employees_bak3.sql

（11）导出和导入（以 sakila为例）

①导出为 TXT文件：

select* fromcountryintooutfile’/your/file/path/country’;

②导入TXT文件：

loaddatainfile’/your/file/path/country’intotablecountry;

③导出为 CSV文件：4

select* fromcountryintooutfile’/your/file/path/country.csv’;

④导入 CSV文件：

loaddatainfile’/your/file/path/country.csv’intotablecountry;

⑤导出为 xml文件：

mysql-utest_02-p–xml-e’select*fromsakila.country’>

/your/file/path/country.xml

3. SQLDDL使用

（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. SQLDDL&DML使用

（1）使用insert语句将数据插入到相应的表中；

（2）使用 delete语句删除表中有关联表外建对应的记录，能否成功删除，如

果不能请分析原因；

（3）使用 update语句更新外键数据，能否成功修改，如果不能请分析原因。；

（4）创建视图；

（5）对视图进行查询操作；

（6）对表进行联合查询操作；

（7）更新视图，分析更新操作可以执行成功或失败的原因。5

5.简单查询

（1）查询性别为“男”的所有学生的学号、姓名和班级号；（单表简单查询）

（2）查询 xxx表，获得性别为“女”的记录，结果按照班级ID降序排列；（order

by）

（3）查询 xxx表，按照年龄从小到大排序；（获取子串函数、orderby）

（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人的课程号，并按课程号降序排列（groupby,

having,orderby…desc）。

6.复杂查询

（1）查询审批通过人数最多的课程名称和教师姓名；

（2）查询选课人数最多和第二多的课程名称和任课老师姓名；(选做)

（3）查询只选了课程《算法设计》的学生姓名；

（4）查询选修了全部课程的学生姓名；

（5）查询选修了课程 3 的学生学号、姓名、身份证号；

（6）据学生学号将 xxx表与 xxx表做自然连接查询；

（7）据学号将 xxx表与 xxx表进行左连接查询，并解释这样做所具有的业务含6

义；

（8）据学号将 xxx表与 xxx表进行右连接查询，并解释这样做所具有的业务含

义；

（9）总结自然连接、左连接、右连接查询在产生的结果上面有什么区别。

7. 创建并调用存储过程、函数和触发器

（1）创建并调用存储查询过程（以 employee为例）

① 创建一个查询存储过程：

> delimiter##> createprocedureselect_manager(infnamevarchar(20), inlnamevarchar(20))->begin->select* fromdept_managernaturaljoinemployeeswherefirst_name= fnameandlast_name =lname;->end>##

② 调用这个存储查询过程：callselect_manager(‘Xiaobin’,’Spinelli’);

（2）创建并调用函数（以 employee为例）

①创建函数：

> delimiter##> createfunctiontitle_num(title_namevarchar(50))returnsint>begin> declarenumint;>selectcount(title)fromtitleswhereto_date>curdate()andtitle=title_name groupbytitleintonum;>returnnum;>end>##setgloballog_bin_trust_function_creators=1;

② 调用函数：selecttitle_num(“manager”);7

（3）创建并调用触发器（以 employee为例）

① 查看表 dept_manager：select* fromdept_manager;

② 创建新表 quit：createtablequit(emp_noint, depr_nochar(4), quit_date

date);

③ 创建触发器：

mysql> delimiter##mysql> createtriggerafter_insert_ quitafterinsertonquitforeachrow->begin-> updatedept_ managersetto_ date=curdate()whereemp_no=110039;->end->##

④ 触发触发器：insertintoquitvalues(110039,’d001′, curdate());

⑤ 查看新表 quit：select*fromquit;

⑥ 再次查看表 dept_manager：select* fromdept_manager;

8. 安装phpmyadmin步骤如下：

（1）安装 phpmyadmin：sudoapt-getinstallphpmyadmin

（2）安装 php-mbstring：sudoapt-getinstallphp-mbstring

（3）安装 php-gettext：sudoapt-getinstallphp-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.实验结果与总结