# DataBase：Mysql  Tool：Navicat


# 创建学生信息表
create table Student (
    # 学号作为主键，varchar是可变长字符串
    Sno VARCHAR(20) primary key,
    # 使用default定义默认值
    Sage int default 18,
    Sname VARCHAR(20)
)


# 创建老师表
create table Teacher (
    Tno VARCHAR(20) primary key,
    Tname VARCHAR(20),
    Tage INT,
    AcademyNo VARCHAR(20),
    # 创建外键约束
    constraint Fk_Teacher_Academy foreign key(AcademyNo) references Academy(AcademyNo)
)


# 创建课程表
create table Course (
    # 列级定义主键
    Cno VARCHAR(20) primary key,
    Tno VARCHAR(20), 
    Cname VARCHAR(50)
)
# 添加约束
alter table Course add constraint Fk_Course_Teacher foreign key(Tno) references Teacher(Tno);


# 创建成绩表
create table Sc (
    Sno VARCHAR(20),
    Cno VARCHAR(20),
    Score FLOAT,
  # 表级定义多个主键
    primary key (Sno, Cno),
    # 添加外键并级联操作
    constraint Fk_Sc_Student foreign key(Sno) references Student(Sno) on delete cascade on update cascade,
    # 默认删除跟修改都是restrict
    constraint Fk_Sc_Course foreign key(Cno) references Course(Cno)
)


# 查看创建的表
select * from Student


# 修改表操作，#增加一列
alter table Teacher
add column Tage VARCHAR(3);


# 删除指定列
alter table Teacher
drop column Tname;


# 修改某一列
alter table Teacher
change column Tage Tage int;


# 删除表
drop table Teacher


# Mysql常用五类约束类型：
#    not null：非空约束，指定某列不为空 
#    unique： 唯一约束，指定某列和几列组合的数据不能重复 
#    primary key：主键约束，指定某列的数据不能重复、唯一 
#    foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据 
#    check：检查，指定一个表达式，用于检验指定数据 


# 插入内容
insert into student VALUES(‘2016211‘, 21, ‘tom‘);
# 插入指定内容，注意，主键不为空
insert into student(Sno, Sname) VALUES(‘2016205‘, ‘jerry‘);
# 更新内容
update student
set Sage = 25
where Sno = ‘2016213‘
# 删除内容
delete from student
where Sno = ‘2016218‘


# 选择并映射指定的列
select Sname, Sage from student
# 去掉重复的行 distinct
select distinct Sage from student
# 使用where语句筛选数据
select * from student where Sage > 21
# 指定范围 (not)between .. and ..  包含等号
select * from student where Sage between 21 and 25
# 使用 in 查找属性值属于指定集合的元组
select * from student where Sname in(‘tom‘,‘LiMing‘)


# 字符匹配 like, _代表任意单个字符，%代表任意长度
select * from student where Sno like ‘2016211‘
select * from student where Sname like ‘j%‘
select * from student where Sname like ‘t_m‘
# 转义字符 \, 如 \_hell  表示 _hell 
select * from student where Sname like ‘m\_ary‘
# 涉及空值的查询
select * from student where Sage is NULL


# 与:and  或:or
select * from student where Sno like‘2016%‘ and Sage < 21
select * from student where Sno=‘2016215‘ or Sage >= 24
# order by , DESC降序，ASC升序
select * from student 
where sno like‘2016%‘ 
order by Sage desc


# 常用聚集函数

# COUNT()：统计个数
# sum()：计算总和
# avg()：平均值
# max()：最大值
# min()：最小值


# 统计不同的年龄个数
select count(distinct Sage) from student
# 计算平均年龄
select avg(Sage) 平均年龄 from student

# having的使用作用，类似于where，但可以对聚集函数使用，结合group by使用
select AcademyName 学院, count(s.AcademyNo) 人数
from student s, Academy a
# 等值连接
where s.AcademyNo = a.AcademyNo
# 通过学院代号分组
group by s.AcademyNo
# 筛选分组
having count(s.AcademyNo) >= 2



# 嵌套查询，例：查询选修了数据库原理的所有学生信息
select * from student
# 如果学生学号在子查询的集合里面则判断为 true
where Sno in (
    # 子查询获取的是一个包含所有学生学号的集合
    select s.Sno 
    from sc s, course c
    where s.Cno = c.Cno and Cname = ‘数据库原理‘
)

select * from student
# 如果把集合直接给出，也就类似于上一个嵌套查询的效果
where Sno in(‘2016211‘, ‘2016212‘)


# 使用limit筛选数据 limit 开始下标(0开始) 条数
# 筛选第 1~2条数据
select * from student limit 0,2;
# 筛选前三条数据，相当于limit 0,5
select * from student limit 5;

select * from student
# exists 返回true or false
where exists (
    select * from teacher
    where Tno  = ‘1111‘
)


# 建立视图
create view student_view
as
select Sno 学号, Sname 姓名
from student
# 像基本表一样对视图查询
select * from student_view
# 删除视图
drop view student_view


# check短语使用，但Mysql中check不强制执行
create table people (
    Pid varchar(20) primary key,
    Pname varchar(30),
    # 当然也可以跟PK等一样表级定义
    Psex varchar(2) check (Psex in (‘男‘, ‘女‘))
)


# 授予权限：grant 权限1,权限2.. on 对象类型 对象名 to 用户1,用户2... 
grant select,insert on table student to user1
# 收回权限：revoke 权限1,权限2.. on 对象类型 对象名 from 用户1,用户2... 
revoke delete on table student from user1



# 函数以及变量的使用
# create function 函数名([参数]) returns 返回值类型
# begin
#        declare 变量名 变量类型...
#         sql语句;
#     return 值;
#    end;
create function getStudentAge(Id varchar(20)) returns INT
begin
    # 声明一个变量以及对应类型
    declare age int;
    # 赋值或者set age = (select Sage from student where Sno = Id);
    select Sage from student where Sno = Id into age;
    # 返回值
    return age;
end
# 调用函数获取内容
select getStudentAge(‘2016211‘);
# drop function 函数名
drop function getStudentAge1;



# 存储过程的使用,类似函数
# create procedure proc_getStudentAge(in 变量名 类型,out(返回变量) 变量名 类型)
# begin
#     Sql语句;
#    end
create procedure proc_getStudentAge(Id varchar(20))
begin
    select * from student where Sno = Id;
end

# in表示传入变量，out表示传出变量
create procedure proc_getStudentAge2(in Id varchar(20), out age int)
begin
    select Sage from student where Sno = Id into age;
end

# 执行存储过程
call proc_getStudentAge(‘2016211‘);
# 执行存储过程获取数据并使用数据
call proc_getStudentAge2(‘2016211‘, @age);
select @age;
# 删除存储过程
drop procedure proc_getStudentAge;



# 触发器的使用，new表示新数据，old表示原数据
# create trigger 触发器名称 [before | after] [insert | update | delete] 
# on 表名 for each row 
# begin
#        一个或多个语句列表，列表里面的每条语句必须用分号隔开
# end
create trigger insert_stu after insert
on student for each row
begin
    # 自变量
    declare age int;
    # new.Sno 是指新添元组的Sno
    set age = (select Sage from student where Sno = new.Sno);
    insert into sc values(new.Sno, ‘51‘, (100-age));
end
# 测试上述触发器
insert into student values(‘2016221‘, 20, ‘oppo‘, ‘17‘);
# 删除触发器
drop trigger insert_stu;