#创建数据库
create database stums;
#创建学生信息表
create table tb_students(
stu_num char(12) primary key,
stu_name varchar(20),
stu_sex char(2),
stu_age int
)
#删除学生表
dorp table tb_students;
select * from tb_students;
#添加字段、删除字段
alter table tb_students add stu_des varchar(20) after stu_name;
alter table tb_students drop column studes;
#插入数据
insert into tb_students(stu_num,stu_name,stu_des,stu_sex,stu_age) values('1005','小钱','ii','女',21);
#修改数据
update tb_students set stu_des='oo';

select * from tb_students where stu_age>22 order by stu_age desc;
select avg(stu_age) from tb_students;
select max(stu_age) from tb_students;
#主键外键
create table tb_sc(
sc_stu_num char(12),
sc_course_name char(8),
sc_grade int,
constraint PK_SC primary key (sc_stu_num),
foreign key(sc_stu_num) references tb_students(stu_num)
);
insert into tb_sc(sc_stu_num,sc_course_name,sc_grade) values('1003','韩语',90);
select * from tb_sc;
#多表查询
select * from tb_students s,tb_sc sc where s.stu_num=sc.sc_stu_num;
select * from tb_students s join tb_sc sc on s.stu_num=sc.sc_stu_num;
select * from tb_students s left join tb_sc sc on s.stu_num=sc.sc_stu_num;
select stu_name from tb_students where stu_num in(select distinct sc_stu_num from tb_sc);
#创建索引
create index index_students_stu_age on tb_students(stu_age);
show index from tb_students;
#创建视图
create view V_STU as select * from tb_students where stu_age>22;
select * from v_stu;