SQL sever学习笔记

1、创建数据库

create database student  //student -- 数据库名称

2、创建表

create table Student (
Sno char(7) primary key, //primary key 主键
Sname char(10) not null,
Ssex char(2),
Sage tinyint,
Sdept varchar(20)
)

create table Course(
Cno char(6) not null,
Cname varchar(20) not null,
Credit tinyint,
Semester tinyint,
primary key(Cno)
)

create table SC(
Sno char(7) not null,
Cno char(6) not null,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
)

drop table Crouse   //在数据库中删除表“Crouse”

alter table Student  //在表中添加“专业”列
add Spec char(8) null

alter table Student
alter column Spec varchar(20)  //在表中修改“专业”列

alter table Student
drop column Spec  //在表中删除“专业”列

create table Employee(
Eno char(8) primary key,
Ename char(10) not null,
Esex char(2),
Eage tinyint
)

alter table Employee
add Ecall varchar(20) not null  //在表中添加“电话”列

alter table Employee
alter column Ecall char(8)  //在表中修改“电话”列

alter table Employee
drop column Ecall   //在表中删除“电话”列

3、查询

select Sno 学号,Sname 姓名 from Student

select Sno 学号,Sname 姓名,Sdept 系 from Student

select * from Student

select sname,2021 - Sage from Student

select Sname,'出生年份',2021 - Sage from student

select sname as 姓名,'出生年份' as 常量列, 2021 - sage as 年份
from student

select sname 姓名,2021- sage 出生年份
from student

select sno 学号 from sc

select distinct sno from sc

select sname 姓名 from student
where sdept = '计算机系'

select sname,sage from student
where sage < 20

select distinct sno from sc where grade < 60
//distinct -->去除查询结果中重复的语句，放在select后面


//查询年龄在20-23岁之间的学生
select sname 姓名,sdept 所在系,sage 年龄 from student
where sage between 20 and 23

//两种方法结果相同

select sname 姓名,sdept 所在系,sage 年龄 from student
where sage >= 20 and sage < =23


//查询年龄不在20-23岁之间的学生
select sname 姓名,sdept 所在系,sage 年龄 from student
where sage not between 20 and 23

//两种方法结果相同

select sname 姓名,sdept 所在系,sage 年龄 from student
where sage < 20 or sage > 23

//查询信息系、数学系和计算机系学生的姓名和性别

select sname 姓名,ssex 性别 from student
where sdept in ('信息系','数学系','计算机系')

//两种方法结果相同

select sname 姓名,ssex 性别 from student
where sdept = '信息系' or sdept = '数学系' or sdept = '计算机系'

//查询信息系和计算机系之外的其他系的学生姓名、性别和所在系

select sname 姓名,ssex 性别 from student
where sdept not in ('信息系','计算机系')

//两种方法结果相同

select sname 姓名,ssex 性别 from student
where sdept != '信息系' and sdept != '计算机系'

–查询姓"张"的学生信息

select * from student where sname like '张%'

–查询学生表中姓"张",姓"李"和姓"刘"的学生详细信息

select * from student where sname like '[张李刘]%'

select * from student where sname like '张%' or sname like '李%' or sname like '刘%'

–查询名字中第二个字为"小"或"大"的学生姓名和学号

select sname,sno from student where sname like '_[大小]%'

–查询所有不姓王也不姓张的学生姓名

select sname  from student where sname not like '[王张]%'

select sname  from student where sname  like '[^王张]%'

select sname  from student where sname not like '王%' and sname not like '张%'

–查询姓“王”且名字是两个字的学生姓名

select sname from student where sname like '王_'

–查询姓“王”且名字是三个字的学生姓名–rtrim(列名)–>去掉指定列中未尾随的空格

select sname from student where rtrim(sname) like '王__'

–在学生表中查询学号最后一位不是2、3、5的学生信息

select * from student where sno like '%[^235]'

select * from student where sno not like '%[235]'

–查询没有考试成绩的学生的学号和相应的课程号

select sno,cno from sc where grade is null

–查询所有有考试成绩的学生的学号、相应的课程号和成绩

select sno,cno,grade from sc where grade is not null

–多重条件查询

–布尔表达式1 and 布尔表达式2 [and 布尔表达式n]

–布尔表达式1 or 布尔表达式2 [or 布尔表达式n]

–查询计算机系年龄在20岁以下的学生姓名

select sname from student where sdept = '计算机系' and sage < 20

–查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄
–and 的优先级 大于or ，所以要加（）

select sname,sdept,sage from student where (sdept = '计算机系' or sdept = '信息系')
and sage >= 20