数据库SQL语句大全

SQL语句大全-百度百科

ORACLE创建用户操作

MYSQL语句大全

多表连接子查询

MSSQL数据库的字段类型总结

数据库中的聚合函数

数据库三大范式详解

修改SQL数据库中表字段类型时，报“一个或多个对象访问此列”错误的解决方法

用一条insert语句来插入多行数据

进入数据库
use student;
创建学生表
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(4),
Cno char(4),
Primary key (Sno,Cno)
);
找出这个数据库中所有的表
select name FROM sysobjects WHERE type='U'
显示出表中所有数据
select * from student;
select * from Course;
select * from SC;
插入一条记录（'200215128'，'1'）
insert into SC(Sno,Cno) values ('200215128','1');
一次性插入多行数据，用insert into 表名 values 的形式无法实现，用all和不用有什么区别呢？
insert into SC (Sno,Cno,Grade) 
select 200215121,1,92 union all
select 200215121,2,85 union all
select 200215121,3,88 union all
select 200215122,2,90 union all
select 200215122,3,80
查询成绩在80-90的学生信息
select * from student where Sno in(select Sno from SC where Grade between 80 and 90);
查询所以年龄在20岁以下的学生姓名及其年龄
select Sname,Sage from student where Sage < 20;
查询年龄不再20~23岁之间的学生的姓名、系别和年龄
select Sname,Sdept,Sage from student where Sage not between 18 and 19
查询所有姓刘的学生的姓名、学号和性别
select Sname,Sno,Ssex from student where Sname like '刘%'
将计算机科学系全体学生的成绩置零
update SC set Grade = 0 where 'CS'=(select Sdept from student where student.Sno = SC.Sno);
查询计算机科学系的学生及年龄不大于19岁的学生，两种语句
select * from student where Sdept = 'CS' or Sage <= 19;
select * from student where Sdept = 'CS' union select * from student where Sage <= 19;
查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
select Sname,Sage,Sdept from student where Sage < any (select Sage from student where Sdept = 'CS') and Sdept <> 'CS';
删除学号为200215128的学生记录
delete from SC where Sno = '200215128';
删除计算机科学系所有学生的选课记录，两种语句，有什么区别？in语句效率较低，子查询效率较join语句低
delete from SC where Sno in (select Sno from student where Sdept = 'CS');
delete from SC where 'CS' = (select Sdept from student where student.Sno = SC.Sno);
select * from SC where 'CS' = (select Sdept from student where student.Sno = SC.Sno);
select * from SC left join student on SC.Sno = student.Sno where Sdept='CS'
如果有聚合函数或者group by，字段必须在其中之一内，order by默认是asc顺序
select count(sno) as count,cno from SC group by cno order by count desc 
select count(sno) as 统计,cno from SC group by cno order by 统计;
查询与"刘晨"在同一个系学习的学生，两种语句，有什么区别？in考虑重名情况
select * from student where Sdept = (select Sdept from student where Sname = '刘晨');
select * from student where Sdept in (select Sdept from student where Sname = '刘晨');
平均函数,distinct统计非重复非空字段
select avg(Sage) from student where Sdept = 'MA';
select count(distinct Sdept),avg(Sage),sum(Sage),min(Sage),max(Sage) from student;