数据库关于部分操作练习题

一.有一个关于商品供应及顾客订单的数据库有四个表：

供应表apply(id,name,sid,price)说明：供应厂家编号id,供应厂家名称name，sid商品编号，price商品价格。

顾客表customers(gid,name,address,balance)说明：顾客编号gid，address地址，balance余额，顾客姓名name。

订单表orders(sid,gid,date)说明：sid商品编号，gid顾客编号，date订单日期。

商品表goods(sid,name,count)说明：sid商品编号，name商品名称，count商品数量。用SQL语句写出以下要求：

1.查询出2008-8-8这一天顾客的订单信息，要求包括顾客姓名，商品名称及订单日期。

回答：

答案：

SELECT customer.name AS customer_name, goods.name AS product_name, orders.date 2.AS order_date
FROM orders
JOIN customer 
   ON orders.gid = customer.gid
JOIN goods 
   ON orders.sid = goods.sid
WHERE date = '2008-08-08';

2.将该商品表上商品编号为204的商品名称更改为百事可乐

回答：

答案：

UPDATE goods
SET name = '百事可乐'
WHERE sid = 204;

3.将顾客表上余额不足1000元的，将其订单日期延后10天

回答：

答案：

UPDATE orders SET date = DATE_ADD(date, INTERVAL 10 DAY) WHERE gid IN ( SELECT gid FROM customer WHERE balance < 1000 );

4.将商品表中没有顾客订购的商品信息删除

回答：

答案：delete from goods where sid not in (select distinct sid from orders);

5.删除订单表中商品编号为102的订单记录

回答：

答案：delete from orders where sid = '102';

6.从供应表中查询全体供应厂商的基本信息。

回答：

答案：select * from apply

7.从顾客表中查询地址在“长春”的顾客的顾客编号，顾客姓名及余额。

回答：

答案：select gid,name,balance from customers where address = "长春";

8.从商品表中查询以"可乐"两个字结尾的商品名称及数量，并按照商品数量降序排列。

回答：

答案：select name,count from goods where name like "%可乐" order by count desc;

9.从订单表中查询购买商品编号为“101”商品的顾客编号及订单日期。

回答：

答案：select gid,date from order where sid = 101;

10.向商品表中追加一条记录（204,可口可乐,900）

回答：

答案：insert into goods values (204,"可口可乐",900);

11.向商品表中查询最多商品数量，最少商品数量及商品总数量的记录信息

回答：

答案：select max(count),min(count),sum(count) from goods;

二.已知进销存数据库Jxcsjk包含如下数据表：

商品表：商品id （自增），商品名称，单位，单价，商品状态

库存表：商品id（自增），商品id， 库存数量

请完成如下操作SQL语句，表名和列名必须和上面一样

1.创建以上两个表的SQL语句；

回答：

答案：

create table 商品表(

商品id int not null auto_increment primary key,

商品名称 varchar(20) not null,

单位 varchar(10),

单价 double,

商品状态 varchar(10) );

create table 库存表 ( 库存id int not null auto_increment primary key,

商品id int not null,

库存数量 int,

foreign key (商品id) references 商品表(商品id) );

2.在商品表上创建一个触发器（名字自定），当删除商品时自动删除该商品在库存表中的记录。

答案：

DELIMITER $$

CREATE

    /*[DEFINER = { user | CURRENT_USER }]*/

    TRIGGER `jxcxt`.`name1` 

    BEFORE DELETE ON `jxcxt`.`库存表`

    FOR EACH ROW 

    BEGIN

    DELETE FROM 库存表 WHERE 商品id = OLD(库存表(商品id));

    END$$

DELIMITER ;

3.在商品表上创建一个触发器（名字自定），当新增商品是自动在库存表中插入该商品的库存信息（库存数量为0）

答案：

DELIMITER $$

CREATE

    /*[DEFINER = { user | CURRENT_USER }]*/

    TRIGGER `jxcxt`.`name2` 

    AFTER INSERT

    ON `jxcxt`.`商品表`

    FOR EACH ROW 

    BEGIN

    INSERT INTO 库存表 VALUES(NEW`商品id`,0);

    END$$

DELIMITER ;

4.创建一个存储过程(名字自定)，实现商品表的插入，需要判断当前商品名称是否存在。

答案：

DELIMITER $$

CREATE

    PROCEDURE `jxcxt`.`p_insert_product`(IN p_name VARCHAR(20),IN p_session VARCHAR(10),IN p_price INT,IN p_level VARCHAR(20))

    BEGIN

   IF EXISTS(SELECT * FROM 商品表 WHERE 商品名称 = p_name) THEN 

   SELECT '商品已经存在！';

   ELSE 

   INSERT INTO 商品表(商品名称,单位,单价,商品状态)

   VALUES(p_name,p_session,p_price,p_level);

   SELECT '商品添加成功！';

   END IF;

    END$$

DELIMITER ;

三.选课管理系统Xkglxt包含如下数据表：

学生表：学生id，学号，姓名，专业，年级，姓名拼音缩写

课程表：课程id，课程名称，学分

选课表：选课id，学生id，课程id，成绩

请完成如下操作的SQL语句：

1.创建三个表的SQL语句；

答案：

CREATE TABLE 学生表 (
    学生id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    学号 VARCHAR(10) NOT NULL UNIQUE,
    姓名 VARCHAR(20) NOT NULL,
    专业 VARCHAR(20),
    年级 VARCHAR(10),
    姓名拼音缩写 VARCHAR(20)
);

CREATE TABLE 课程表 (
    课程id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    课程名称 VARCHAR(50) NOT NULL,
    学分 FLOAT
);

CREATE TABLE 选课表 (
    选课id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    学生id INT NOT NULL,
    课程id INT NOT NULL,
    成绩 FLOAT,
    FOREIGN KEY (学生id) REFERENCES 学生表(学生id),
    FOREIGN KEY (课程id) REFERENCES 课程表(课程id)
);
 

2.创建视图V_选课，其中包括这些字段：选课id，学号，姓名，课程名，成绩；

答案：

CREATE VIEW V_选课 AS
SELECT 选课id, 学生表.学号, 学生表.姓名, 课程表.课程名称, 选课表.成绩
FROM 选课表
JOIN 学生表 ON 选课表.学生id = 学生表.学生id
JOIN 课程表 ON 选课表.课程id = 课程表.课程id;

3.查询张三的数据开发技术课程成绩。

答案：

SELECT 成绩
FROM V_选课
WHERE 姓名 = '张三' AND 课程名称 = '数据开发技术';
或者

SELECT 成绩 FROM 选课表
JOIN 学生表 ON 选课表.学生id = 学生表.学生id
JOIN 课程表 ON 选课表.课程id = 课程表.课程id
WHERE 学生表.姓名 = '张三'
AND 课程表.课程名称 = '数据开发技术';
4.创建一个存储过程，实现学生表的插入，姓名拼音缩写需要调用getstrpy（getstrpy(p_name varchar(100))）函数实现。

DELIMITER //
CREATE PROCEDURE insert_student(
  IN p_studentno VARCHAR(10),
  IN p_name VARCHAR(20),
  IN p_major VARCHAR(20),
  IN p_grade VARCHAR(10)
)
BEGIN
  DECLARE p_pinyinname VARCHAR(20);
  SET p_pinyinname = getstrpy(p_name); -- 调用 getstrpy 函数获取姓名拼音缩写
  INSERT INTO student(studentid, studentno, name, major, grade, pinyinname)
  VALUES(NULL, p_studentno, p_name, p_major, p_grade, p_pinyinname);
END //
DELIMITER ;

上述代码中，创建了一个名为 insert_student 的存储过程，用于向学生表中插入一条新记录。该存储过程接收四个参数：学号、姓名、专业和年级，其中姓名会调用 getstrpy() 函数获取拼音缩写，并将所有这些信息插入到学生表中。

需要注意的是，在执行上述存储过程之前，需要先创建 getstrpy() 函数并确保其可用。对于 getstrpy() 函数的实现细节，可以参考相关文档或网络资源。

另外，为了能够正确插入学生数据，在 INSERT INTO 语句中需要忽略 studentid 字段（因为该字段为自增长主键，MySQL 会自动为其生成一个新的值）。

5.在学生表上创建一个触发器(名字自定)，当新增学生时自动处理拼音名称缩写（注：自定义函数：pysx('中文字符串')返回该中文字符串的缩写）。

答案：

DELIMITER //
CREATE TRIGGER student_insert_trigger BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  DECLARE p_pinyinname VARCHAR(20);
  SET p_pinyinname = pysx(NEW.name); -- 调用 pysx 函数获取姓名拼音缩写
  SET NEW.pinyinname = p_pinyinname;
END //
DELIMITER ;

上述代码中，创建了一个名为 student_insert_trigger 的触发器，用于在向学生表中插入新记录时自动处理姓名拼音缩写。当有一条新的记录要被插入时，触发器会自动将该记录的姓名字段作为参数传递给 pysx() 函数，获取该字段的拼音缩写并将其保存到新记录的 pinyinname 字段中。

需要注意的是，在执行上述触发器之前，需要先创建 pysx() 自定义函数并确保其可用。对于 pysx() 函数的实现细节，可以参考相关文档或网络资源。

6.创建一个存储过程（名字自定），实现选课表的插入，需要判断当前课程id和学生id是否存在。

DELIMITER //
CREATE PROCEDURE insert_enrollment(
  IN p_studentid INT,
  IN p_courseid INT,
  IN p_grade FLOAT
)
BEGIN
  DECLARE v_student_count INT;
  DECLARE v_course_count INT;
  SELECT COUNT(*) INTO v_student_count FROM student WHERE studentid = p_studentid; -- 查询学生表中是否存在指定的学生id
  SELECT COUNT(*) INTO v_course_count FROM course WHERE courseid = p_courseid; -- 查询课程表中是否存在指定的课程id
  IF v_student_count = 0 THEN -- 如果学生id不存在，则抛出异常并中止存储过程
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid student id';
    RETURN;
  END IF;
  IF v_course_count = 0 THEN -- 如果课程id不存在，则抛出异常并中止存储过程
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid course id';
    RETURN;
  END IF;
  INSERT INTO enrollment(enrollmentid, studentid, courseid, grade)
  VALUES(NULL, p_studentid, p_courseid, p_grade);
END //
DELIMITER ;
创建了一个名为 insert_enrollment 的存储过程，用于向选课表中插入一条新记录，并在插入数据之前先判断所对应的学生id和课程id是否已经存在于对应的表中。如果学生id或课程id不存在，则会抛出异常并中止存储过程的执行。

7.创建一个存储过程（名字自定），实现课程表的插入，需要判断当前课程名称的课程是否存在

DELIMITER //
CREATE PROCEDURE insert_course(
  IN p_coursename VARCHAR(50),
  IN p_credit FLOAT
)
BEGIN
  DECLARE v_course_count INT;
  SELECT COUNT(*) INTO v_course_count FROM course WHERE coursename = p_coursename; -- 查询课程表中是否存在指定的课程名称
  IF v_course_count > 0 THEN -- 如果课程名称已经存在，则抛出异常并中止存储过程
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate course name';
    RETURN;
  END IF;
  INSERT INTO course(courseid, coursename, credit)
  VALUES(NULL, p_coursename, p_credit);
END //
DELIMITER ;
上述代码中，创建了一个名为 insert_course 的存储过程，用于向课程表中插入一条新记录，并在插入数据之前先判断所对应的课程名称是否已经存在于课程表中。如果课程名称已经存在，则会抛出异常并中止存储过程的执行。

8.在学生表上创建一个触发器（名称自定），当修改学生表姓名时自动处理姓名拼音缩写（pysx('中文字符串')）

DELIMITER //
CREATE TRIGGER update_student_pysx
BEFORE UPDATE ON student
FOR EACH ROW
BEGIN
  IF OLD.name <> NEW.name THEN -- 只有当姓名发生了修改时才进行处理
    SET NEW.pysx = pysx(NEW.name);
  END IF;
END //
DELIMITER ;
9.创建一个触发器(名字自定)，当删除课程信息时，自动删除该课程的选课信息。

DELIMITER //

CREATE TRIGGER delete_course_xk AFTER DELETE ON course -- 只在 course 表上执行触发器

FOR EACH ROW

BEGIN

DELETE FROM xk WHERE courseid = OLD.courseid; -- 自动删除与被删除的课程相关联的选课信息

END //

DELIMITER ;

10.创建一个存储过程（名字自定），实现课程表的插入，需要判断当前课程名称的课程是否存在

DELIMITER //
CREATE PROCEDURE insert_course(
  IN p_coursename VARCHAR(50),
  IN p_credit FLOAT
)
BEGIN
  DECLARE v_course_count INT;
  SELECT COUNT(*) INTO v_course_count FROM course WHERE coursename = p_coursename; -- 查询课程表中是否存在指定的课程名称
  IF v_course_count > 0 THEN -- 如果课程名称已经存在，则抛出异常并中止存储过程
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate course name';
    RETURN;
  END IF;
  INSERT INTO course(courseid, coursename, credit)
  VALUES(NULL, p_coursename, p_credit);
END //
DELIMITER ;
四.产品销售数据库cpxs包含如下数据表：

产品表：产品编号，产品名称，价格，库存量

销售商表：客户编号，客户名称，地区，负责人，电话

产品销售表：销售日期，产品编号，数量，销售额。

1.写出产品表的建表语句

CREATE TABLE 产品表 (
    产品编号 VARCHAR(10),
    产品名称 VARCHAR(50),
    价格 DECIMAL(10,2),
    库存量 INT,
    PRIMARY KEY (产品编号)
);

2.像产品表中插入如下记录：0001 空调 3000 200 0002 冰箱 2500 300

INSERT INTO 产品表 (产品编号, 产品名称, 价格, 库存量) 
VALUES 
('0001', '空调', 3000, 200), 
('0002', '冰箱', 2500, 300);

3.将产品表中每种商品价格打八折后进行显示

SELECT 产品编号, 产品名称, 价格*0.8 AS 折后价格, 库存量 FROM 产品表;

4.查找价格在2000到2900之间的产品名称

SELECT 产品名称 FROM 产品表 WHERE 价格>=2000 AND 价格<=2900;

5.在产品销售表上创建“冰箱”产品表的视图bxcp

CREATE VIEW bxcp AS SELECT * FROM 产品销售表 WHERE 产品编号='0002' AND 产品名称='冰箱';