sq语句大全||数据库的的创建，表的创建修改和删除。表中数据的增删改查，对表中数据的查询where，group by,聚集函数

鉴于前两个又一个总结前两个的sql语句
本次代码大量用啦《数据库系统概论》ps(第四版)的例题：

/*创建一个名为mydatabase的数据库*/
 Create database mydatabase
On
(Name=mydatabase,
 Filename='d:\数据库\mydatabase.mdf',
 Size=3,
 Maxsize=10,
 Filegrowth=1
),
(Name=mydatabase1,
 Filename='d:\数据库\mydatabase.ndf',
 Size=5,
 Maxsize=15,
 Filegrowth=2
)
Log on
(Name=mydatabase_log,
 Filename='d:\数据库\mydatabase_log.ldf.',
 size=5,
 maxsize=10,
 filegrowth=1
)
/*创建三个表分别为student，sc，course*/
use mydatabase
Create Table Student
(Sno Char(12), 
 Sname  Char (10),
  Ssex  Char (2) ,
 Sage  Smallint, 
Sdept  Char (30) );


/*create TABLE Student 
(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
) ;

*/

create TABLE Course
(
Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint,

);

CREATE TABLE SC
(
Sno char(9),
Cno char(4),
Grade smallint,
Primary key (Sno,Cno),

);
/*插入行*/
insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('200215121','李勇','男','20','CS');

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('200215122','刘晨','女','19','CS');

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('200215123','王敏','女','18','MA');

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('200215125','张立','男','19','IS');



insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5','4');

insert into Course(Cno,Cname,Ccredit)
values('2','数学','2');

insert into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1','4');

insert into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6','3');

insert into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7','4');

insert into Course(Cno,Cname,Ccredit)
values('6','数据处理','2');

insert into Course(Cno,Cname,Cpno,Ccredit)
values('7','PASCAL语言','6','4');


insert into SC(Sno,Cno,Grade)
values('200215121','1','92');

insert into SC(Sno,Cno,Grade)
values('200215121','2','85');

insert into SC(Sno,Cno,Grade)
values('200215121','3','88');

insert into SC(Sno,Cno,Grade)
values('200215122','2','90');

insert into SC(Sno,Cno,Grade)
values('200215122','3','80');



/*修改列*/
alter table Student     /*8*/
alter column Sage int;

alter table student 
alter column sname char(20)
/*此处插入解释图片*/

/*加入一列*/
alter table Course  /*9*/
add unique(Cname);

/*3.4.1*/
/*数据查询*/


select Sno,Sname /*1*/
from Student;



select Sname,Sno,Sdept /*2*/
from Student;



select *
from Student;/*3*/



select Sname,2005-Sage
from Student;/*4*/



select Sname,'Year of birth:' 'Year of birth:' ,2005-Sage birthday ,  lower(Sdept)  dept
from Student;/*5*//*lower（）大小写转换函数*/



select distinct Sno
from SC;  /*6*/

/*查询满足条件的元组*/

select Sname
from Student
where Sdept='CS'; /*7*/



select Sname,Sage
from Student
where Sage>10 and Sage<19; /*8*/



select diStinct Sno
from SC
where Grade<80; /*9*/



select Sname,Sdept,Sage
from Student
where Sage  between 20 and 23; /*10*/




select Sname,Sdept,Sage
from Student
where Sage not between 20 and 23; /*11*/



select Sname,Ssex,Sno
from Student
where Sdept in('CS','MA');/*12*/



select Sname,Ssex,Sno
from Student
where Sdept not in('CS','MA');/*13*/



select Sname
from Student
where Sno ='200215121';/*14*/


/*字符匹配*/

select Sname
from Student/**/
where Sname like '王%';/*15*/




select Sname
from Student
WHERE Sname like '欧阳_';/*16*/



select Sname
from Student
where Sname like '_阳%';/*17*/



select Sname,Sno,Ssex
from student
where Sname not like '王敏';/*18*/



select Cno,Ccredit
from Course
where Cname like 'DB\_Design' escape '\';/*19*/     /*  escape'\'表示"\"为换码符  紧跟在"\"后面的字符"_"不再具有通配符含义*/




select*
from Course
where Cname like 'DB\_%i__'escape'\';/*20*/




select Sno,Cno
from SC
where Grade is null;/*21*/ /* is不能用等号代替 */



select Sno,Cno
from SC
where Grade is not null;/*22*/



select Sname
from Student
where Sdept='CS' and Sage<20 or Sdept='IS' or Sname='刘晨';/*23*/
/*order by 字句*/

select Sno,Grade
from SC
where Cno='1' or Cno='2'
order by Grade asc;/*24*/



select *
from Student
order by Sdept  ,Sage desc;/*25*/  /*按系号升序排 默认升序ascend  同系中再按Sage排*/


/*聚集函数*/

select count( /*distinct | all*/ Sno )  num    /*  查询学生总人数*/
/*  all为缺省值  distinct 取消指定列中的重复值 */
from Student;/*26*/




select count(distinct Sno)
from SC;/*27*/



select AVG(Grade)
from SC
where Cno='1'or Cno='2'or Cno ='3';/*28*/




select max(Grade)
from SC
where Cno='1'or Cno='2'or Cno ='3';/*29*/


select  sum(  Ccredit)  /*30*/
from SC,Course
where Sno='200215121' and  SC.Cno =  Course.Cno;/*30*/  
 /*  除COUNT（*）外  其他聚集函数都跳过空值而只去处理非空值 where 子句中不能用聚集函数作为条件表达式 */

/*group by子句*/
select  Cno, Count(Sno)/*31*/
 /* 求各个课程号及其选课人数*/
from SC
group by  Cno;
/*GROUP BY 子句将查询结果按某一列或多列的值分组 值相等的为一组( 相同相邻)*/

select Sno/*32*/
from SC /*查询选修三门课以上课程的学生的学号*/
group by Sno /* 按Sno分组*/
having count(*)>2;/*选出组数大于二的元组 */


select *
from student
/*插入图2*/

select *
from sc
/*插入图3*/

select *
from course
/*插入图4*/





grant  select 
 on  student
to s1

revoke  select
on student 
to s1

鉴于前两个又一个总结前两个的sql语句
本次代码大量用啦《数据库系统概论》ps(第四版)的例题：