数据库基本操作

#使用文件
use test0414;
#授权
grant select on test0414.mytable to ui;
#收回
revoke select on  test0414.mytable from ui;

#创建表
create table student(
              num int primary key, #主键约束，在表中只能有一个，这一列值不允许重复
              name_1 varchar(45) unique, #唯一约束，在表中可以有多列，这一列值不允许重复
              sex enum('男','女'), #枚举类型
              age datetime default now() #默认约束
              );


#添加列
alter table student add column tt int;

#修改列
alter table student modify tt varchar(45);

#删除列
alter table student drop column tt;

#插入
insert into student values(1,'dfasd','男',now());

insert into student(num,sex) values(2,'女');


#查询
select num,name_1,age from student;

select * from student;

#更改某一列
update student set age = now();

#删除
delete  from student;


#刷新
flush privileges; 

 

#使用文件
use test0414;
#授权
grant select on test0414.mytable to ui;
#收回
revoke select on  test0414.mytable from ui;

#创建表
create table student(
              num int primary key, #主键约束，在表中只能有一个，这一列值不允许重复
              name_1 varchar(45) unique, #唯一约束，在表中可以有多列，这一列值不允许重复
              sex enum('男','女'), #枚举类型
              age datetime default now() #默认约束
              );


#添加列
alter table student add column tt int;

#修改列
alter table student modify tt varchar(45);

#删除列
alter table student drop column tt;

#插入
insert into student values(1,'dfasd','男',now());

insert into student(num,sex) values(2,'女');


#查询
select num,name_1,age from student;

select * from student;

#更改某一列
update student set age = now();

#删除
delete  from student;


#刷新
flush privileges; 


#第一范式 属性不可拆分
#两个手机号写在一起不行


#第二范式不允许组合依赖，会照成数据冗余
#信息依赖学号，课程名依赖课程编号，需要分为两个表


#第三范式 不允许传递依赖
#学校依赖学号，学校地址依赖学校名称
insert into Student values('111' , '李口' , '1990-04-06' , '男');
insert into student(snum) values('30');
select * from student;
select * from student;

create table Student1(Snum varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
create table Course(Cnum varchar(10),Cname nvarchar(10),Tnum varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
create table Teacher(Tnum varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
create table SC(Snum varchar(10),Cnum varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
insert into SC values('09' , '03' , 98);

select * from student where snum = '02';

select * from student where snum not in(1,5,3);

select * from student where snum > 1 and snum < 5;

select * from student where snum > 1 or snum < 5;

delete from student where snum = '06';


#模糊查询  %匹配任意一个或多个字符，_匹配一个
select * from student where sage like '19%';

select * from student where sage like '____-05%';

select * from student;
#求student 男生个数，女生个数
select Ssex ,count(*) from student where Ssex = '男'
union all #联合函数，属性相同连在一起，组合的时候把分号去掉
select Ssex, count(*) from student where Ssex = '女';


#分组函数  +条件 having
select Ssex,count(Ssex) from student group by Ssex;

select Ssex,count(Ssex) from student group by Ssex having count(Ssex) = 3;
select *from sc;


select snum,count(cnum) from sc group by snum having count(cnum) >=2;

select cnum ,count(cnum) from sc group by cnum;


select count(*) from course;
select snum,count(snum)  from sc 
group by snum having count(snum) 
>= (select count(*) from course);







select * from student where snum in(select snum from sc 
group by snum having count(snum) 
>= (select count(*) from course));


select snum ,count(snum) from sc group by snum;




#7、查询学过"李四"老师授课的同学的信息 
select * from teacher;

select Tnum from teacher where Tname = '李四';

select Cnum from course where Tnum in
 (select Tnum from teacher where Tname = '李四');
 
 select * from sc where Cnum  in(
 select Cnum from course where Tnum =
 (select Tnum from teacher where Tname = '李四'));
 
 
 select * from student where snum in(select snum from sc where Cnum = (
 select Cnum from course where Tnum =
 (select Tnum from teacher where Tname = '李四')));
 
 
 
 #15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select * from sc;
select snum from sc where score < 60;

select snum from sc group by snum having snum in (select snum from sc where score < 60);

select * from student where snum in (select snum from sc group by snum having snum in (select snum from sc where score < 60));



#查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
#排序函数order by avg(score) desc 降序排数   asc升序排数
select snum,avg(score)from sc group by snum having avg(score) >= 85 order by avg(score) desc;



select * from student where snum in (
select snum from sc group by snum having avg(score) >= 85);


select cnum, count(cnum) from sc group by cnum;




#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

#select作为列
select * ,(select score from sc where Cnum = '01' and sc.Snum = student.Snum) '01',(select score from sc where Cnum = '02' and sc.Snum = student.Snum) '02'
from student where (select score from sc where Cnum = '01' and sc.Snum = student.Snum) > (select score from sc where Cnum = '02' and sc.Snum = student.Snum);


#select作为列表
select * from (select * ,
(select score from sc where Cnum = '01' and sc.Snum = student.Snum) 's01',
(select score from sc where Cnum = '02' and sc.Snum = student.Snum) 's02'
from student) a  #需要自己取一个名字 
where s01 > s02;

#ifnull(10,0)  判断是否为空 ，为空返回后面的数。




#多表查询
#内联，相互匹配
select *from student inner join sc on student.snum = sc.snum;

#右联,以右边为主用左边去匹配
select *from student right join sc on student.snum = sc.Snum;
#左联，以左边为主右边去匹配
select *from student left join sc on student.snum = sc.Snum;

#笛卡尔积
select * from student,sc where student.Snum = sc.Snum;






select * from student inner join sc on student.Snum = sc.Snum and sc.Cnum = '01'      
inner join sc `sc02` on student.Snum = sc02.Snum and sc02.Cnum = '02'
where sc.score > sc02.score;



#查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
#avg 是求分组后一个组里的某个值的平均数
select * from student inner join (select snum,avg(score) avgs from sc group by snum) sc1 on student.snum = sc1.snum where sc1.avgs >= 85;
select snum,avg(score) avgs from sc group by snum;



#16、检索"01"课程分数小于60，按分数降序排列  的学生信息
select * from student right join sc on student.snum = sc.snum where sc.score < 60 and sc.cnum = '01' order by sc.score desc;




#视图  由一张或者多张表导出的表
#简化sql语句
create view myview as
(select student.*,sc.Cnum,sc.score from student inner join sc on student.snum = sc.snum);


select * from myview;



#函数
delimiter ?
create function myfun(a int,b int)
returns int
begin
    
    declare s int default 0; #局部变量
    set s = 1;
    return a + b;


end?
delimiter ;

select myfun(9,10);


#会话变量 @
set @a = myfun(18,10);
select @a;


#系统变量 @@
#不能用户自己创建

insert into Student values('11' , N'李富士' , '1990-01-01' , N'男');
select * from student;