MYSQL练习

第一题：
学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
学号，姓名，性别，年龄，所在系 Sno为主键
课程表：Course (Cno, Cname,)
课程号，课程名 Cno为主键
学生选课表：SC (Sno, Cno, Score)
学号，课程号，成绩 Sno,Cno为主键
1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。
2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。
4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

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

CREATE TABLE student(
	Sno INT PRIMARY KEY COMMENT '学生ID',
	Sname VARCHAR(20) UNIQUE COMMENT  '学生姓名',
	Ssex VARCHAR(2) CHECK(Ssex='男' OR Ssex='女') COMMENT '学生性别',
	Sage INT COMMENT '学生年龄',
	Sdept VARCHAR(20) DEFAULT '计算机' COMMENT '学生所在系'
);

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

ALTER TABLE student MODIFY Sage SMALLINT;
 
DESC student;

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

CREATE TABLE Course(
	Cno INT PRIMARY KEY,
	Cname VARCHAR(20)
);
 
CREATE TABLE SC(
	Sno INT COMMENT '学生ID',
	Cno INT COMMENT '课程号',
	Score INT COMMENT '成绩'
);
CREATE UNIQUE INDEX SC_INDEX ON SC(Sno ASC,Cno ASC);
 
SHOW INDEX FROM SC;

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

CREATE VIEW stu_info AS SELECT
student.Sname,student.Ssex,Course.Cname,SC.Score 
FROM 
student,Course,SC 
WHERE 
student.Sno=SC.Sno AND Course.Cno=SC.Cno;

第二题
1、创建一个存储过程avg_sal_a，有2个参数，分别是部门名称dept_name及接收平均工资，
其中部门名称测试参数为上海中心，接收平均薪资变量为@a。
2、创建存储过程，给定参数员工姓名，查询该员工名所在部门的最高薪资，并返回给变量@a；

1、创建一个存储过程avg_sal_a，有2个参数，分别是部门名称dept_name及接收平均工资其中部门名称测试参数为上海中心，接收平均薪资变量为@a。

mysql> \d $$    ---修改结束符
mysql>  create procedure avg_sal_a(in dept_name varchar(255),out avg_dept int)
    -> begin
    -> select avg(salary) into avg_dept from employee where department_NO in (select number from department where name =dept_name);
    -> end$$
Query OK, 0 rows affected (0.05 sec)
mysql> \d ;
mysql> call avg_sal_a("上海中心",@a);
Query OK, 1 row affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 2800 |
+------+
1 row in set (0.01 sec)

2.创建存储过程，给定参数员工姓名，查询该员工名所在部门的最高薪资，并返回给变量@a;

mysql> \d $$
mysql> create procedure max_sal_e(in e_name varchar(255),out max_sal int)
    -> begin
    -> select max(salary) into max_sal from employee group by department_NO having department_NO =(select department_NO from employee where name =e_name);
    -> end$$
Query OK, 0 rows affected (0.02 sec)

mysql> \d ;
mysql> call max_sal_e("韩金龙",@a);
Query OK, 1 row affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 2800 |
+------+
1 row in set (0.00 sec)