MySQL（视图，存储函数，存储过程）

作业1：

?作业实现：

首先创建学生表，课程表，以及学生选课表。

CREATE TABLE Student (
    Sno INT PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Ssex CHAR(1) CHECK (Ssex IN ('男', '女')),
    Sage INT,
    SDept VARCHAR(20) DEFAULT '计算机'
);

CREATE TABLE Course (
    Cno INT PRIMARY KEY,
    Cname VARCHAR(20) NOT NULL
);

CREATE TABLE SC (
    Sno INT,
    Cno INT,
    Score INT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

?1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。

CREATE TABLE Student (
    Sno INT PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Ssex CHAR(1) CHECK (Ssex IN ('男', '女')),
    Sage INT,
    SDept VARCHAR(20) DEFAULT '计算机'
);

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。

ALTER TABLE Student MODIFY Sage SMALLINT;

3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。

CREATE INDEX SC_INDEX ON SC (Sno, Cno);

4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

CREATE VIEW stu_info AS
SELECT Sname, Ssex, Cname, Score
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno;
desc stu_info;

?作业2：

?作业实现：

首先创建sch表并写入数据

CREATE TABLE sch (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    glass VARCHAR(50) NOT NULL
);

insert into sch (id, name, glass) values (NULL, 'xiaommg', 'glass 1');
insert into sch (id, name, glass) values (NULL, 'xiaojun', 'glass 2');
insert into sch (id, name, glass) values (NULL, 'xiaoshuai', 'glass 1');
insert into sch (id, name, glass) values (NULL, 'xiaoliu', 'glass 2');
insert into sch (id, name, glass) values (NULL, 'xiaohui', 'glass 1');
insert into sch (id, name, glass) values (NULL, 'xiaoxi', 'glass 2');

?1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

DELIMITER //
create function count_sch() returns int
begin
	declare total_count int;
    select count(*) into total_count from sch;
    return total_count;
end //

这里创建存储函数时出现报错，“you *might* want to use the less safe log_bin_trust_function_creators variable”

这里用到的解决方法为“SET GLOBAL log_bin_trust_function_creators = 1;”。

2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资。

????????这里首先需要先创建emp表并填入相关数据：

CREATE TABLE `emp`  (
  `empno` int(4) NOT NULL,
  `deptno` int(2) NOT NULL,
  `ename` varchar(255),
  `job` varchar(255),
  PRIMARY KEY (`empno`) USING BTREE
);

INSERT INTO `emp` VALUES (1001, 30, 'asd', '前台');
INSERT INTO `emp` VALUES (1002, 30, 'qwe', '部门经理');
INSERT INTO `emp` VALUES (1003, 30, 'qaz', '销售员');

接下来创建存储过程avg_sai，功能查询emp表dept为30，job为销售员的平均工资。

DELIMITER //
create procedure avg_sai(in deptno int, in job varchar(50), out avg_salary decimal(10, 2))
begin
	select avg(salary) into avg_salary from emp where dept = deptno and job = job;
end //

call avg_sai(30,'销售员',@avg_salary);