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

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

/*创建一个名为mydatabase的数据库*/ Create database mydatabaseOn(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 mydatabaseCreate 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-Sagefrom Student;/*4*/select Sname,'Year of birth:' 'Year of birth:' ,2005-Sage birthday ,  lower(Sdept)  deptfrom Student;/*5*//*lower（）大小写转换函数*/select distinct Snofrom SC;  /*6*//*查询满足条件的元组*/select Snamefrom Studentwhere Sdept='CS'; /*7*/select Sname,Sagefrom Studentwhere Sage>10 and Sage<19; /*8*/select diStinct Snofrom SCwhere Grade<80; /*9*/select Sname,Sdept,Sagefrom Studentwhere Sage  between 20 and 23; /*10*/select Sname,Sdept,Sagefrom Studentwhere Sage not between 20 and 23; /*11*/select Sname,Ssex,Snofrom Studentwhere Sdept in('CS','MA');/*12*/select Sname,Ssex,Snofrom Studentwhere Sdept not in('CS','MA');/*13*/select Snamefrom Studentwhere Sno ='200215121';/*14*//*字符匹配*/select Snamefrom Student/**/where Sname like '王%';/*15*/select Snamefrom StudentWHERE Sname like '欧阳_';/*16*/select Snamefrom Studentwhere Sname like '_阳%';/*17*/select Sname,Sno,Ssexfrom studentwhere Sname not like '王敏';/*18*/select Cno,Ccreditfrom Coursewhere Cname like 'DB\_Design' escape '\';/*19*/     /*  escape'\'表示"\"为换码符  紧跟在"\"后面的字符"_"不再具有通配符含义*/select*from Coursewhere Cname like 'DB\_%i__'escape'\';/*20*/select Sno,Cnofrom SCwhere Grade is null;/*21*/ /* is不能用等号代替 */select Sno,Cnofrom SCwhere Grade is not null;/*22*/select Snamefrom Studentwhere Sdept='CS' and Sage<20 or Sdept='IS' or Sname='刘晨';/*23*//*order by 字句*/select Sno,Gradefrom SCwhere Cno='1' or Cno='2'order by Grade asc;/*24*/select *from Studentorder 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 SCwhere Cno='1'or Cno='2'or Cno ='3';/*28*/select max(Grade)from SCwhere Cno='1'or Cno='2'or Cno ='3';/*29*/select  sum(  Ccredit)  /*30*/from SC,Coursewhere Sno='200215121' and  SC.Cno =  Course.Cno;/*30*/   /*  除COUNT（*）外  其他聚集函数都跳过空值而只去处理非空值 where 子句中不能用聚集函数作为条件表达式 *//*group by子句*/select  Cno, Count(Sno)/*31*/ /* 求各个课程号及其选课人数*/from SCgroup 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  studentto s1revoke  selecton student to s1

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