请在“答题”菜单下选择相应命令,并按照题目要求完成下面的操作,具体要求如下: 注意:下面出现的“考生文件夹”均为%USER%在数据库newtest中现有学...
注意:下面出现的“考生文件夹”均为%USER%在数据库newtest中现有学生表和成绩表,表结构如下:
Student(stuid, name, sex) (说明:学号,姓名,性别)
Score(id,stuid,cname,grade) (说明:编号,学号,课程名,分数)
1. 请使用SQL语句在数据库newtest中创建职工表:Emp(empid,empname,streetname,cityname) ;说明:empid (整型、主键),empname (定长字符串、长度20、字符集GB2312、不可空),streetname (变长字符串、最大长度50),cityname (变长字符串、最大长度30、不可空、缺省值为“N/A”)。要求编写SQL语句,创建该表。
2. 编写SQL语句,按课程名进行分组,并从组内平均分高于85分的分组中、找出该课程的最高分,分别以“课程名”和“最高分数”为别名输出查询结果。请将此SQL语句存入考生文件夹下的sj12.txt文件中。
3. 编写SQL语句,查询每个学生的总成绩,结果显示学生的name、stuid、total等信息,并按total升序排序,其中total为学生总成绩的别名。请将此SQL语句存入考生文件夹下的sj13.txt文件中。
4.编写SQL语句,创建一个学生查询课程成绩的视图StuView,包含name、
cname、grade。
5.将newtest数据库所有表上的SELECT、UPDATE权限,授予给用户abc,密码123,登录机器IP:192.168.1.100。
参考解析: 1.【答案】
CREATE TABLE EMP(
empid INT PRIMARY KEY AUTO_INCREMENT,
empname CHAR(20) CHARACTER SET GB2312 NOT NULL,
streename VARCHAR(50),
cityname VARCHAR(30) NOT NULL DEFAULT 'N/A');
【解题思路】创建表的语法: create table 表名(列1 类型 主键, 如类型为int 建议加自增长标识列2 类型 约束....)其中知识点1:设置编码 character set 编码;2:非空约束 not null;3:默认值 :default ’值’。
2.SELECT cname AS 课程名,MAX(grade) AS 最高分数 FROM score GROUP BY cname HAVING AVG(grade)>85;
【解题思路】语法:select 查询结果 from 表 where 条件 group by 列名;
1、别名的用法:列名 AS(可有可无)别名
2、最大值和平均值以及最小值:max(列名),min(列名),avg(列名),其中列名是数值类型
3、分组查询:由题可知按照课程分组,group by cname
4、HAVING子句中可以使用聚合函数
3.【答案】
SELECT name, student.stuid, SUM(grade) AS total
FROM student,score
WHERE student.stuid=score.stuid GROUP by stuid ORDER BY total ASC;
【解题思路】
语法:
联表查询select 查询结果 from 表1,表2 where 表1.主键=表2.外键 order by 列名 asc
1、由题可知查询结果涉及到两张表,根据联表查询的语法结构首先搭建出上面语句;
2、求和表达式:sum(列名)
3、别名:列名 as 别名
4、排序:order by 列名 asc升序(desc降序 )
4.【答案】
CREATE VIEW StuView
AS
SELECT name,cname,grade FROM student,score WHERE student.stuid=score.stuid;
【解题思路】
创建视图的语法结构:create view 视图名 as select 语句;由题可知查询的结构涉及到两张表,而且两张表有主外键关系。利用联表查询select 查询结果 from 表1,表2 where 表1.主键=表2.外键 即可
5.【答案】
/*使用mysql数据库*/
USE mysql;
/*向用户表中添加一个abc用户*/
INSERT INTO USER(HOST,USER,PASSWORD) VALUES('192.168.1.100','abc','123');
/*重启数据库*/
FLUSH PRIVILEGES;
/*授予操作newtest数据库上score表select,update权限*/
GRANT SELECT ,UPDATE ON newtest.* TO abc@192.168.1.100;
/*重启数据库*/
FLUSH PRIVILEGES;
【解题思路】
知识点1:对数据库用户进行CRUD操作需使用系统数据库MYSQL
语法:INSERT INTO mysql (HOST,USER,PASSWORD) VALUES (“主机名”,”用户名”,”密码”);
知识点2:授予某个用户操作某数据库的某表 CRUD权限
语法:
1. grant 作用在整个 MySQL 服务器上:
grant select on *.* to dba@localhost; — dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; — dba 可以管理 MySQL 中的所有数据库
2. grant 作用在单个数据库上:
grant select on testdb.* to dba@localhost; — dba 可以查询 testdb 中的表。
3. grant 作用在单个数据表上:
grant select, insert, update, delete on testdb.orders to dba@localhost;
注:执行INSERT INTO mysql (HOST,USER,PASSWORD) VALUES (“主机名”,”用户名”,”密码”); 如果出现“Field 'ssl_cipher' doesn't have a default value”错误。需要在my.ini文件设置
打开my.ini,查找 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"修改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
然后重启MYSQL .