sql 基础操作-CSDN博客

本文链接：https://blog.csdn.net/mr_yan100/article/details/8803806

--第一天，第一、二章，要求：建数据库、建约束、插入数据，
--创建学生数据库
create database student_new;

--使用表
use student_new;
--创建表1--学生表
create table Students
(
SCode      int IDENTITY(1,1),            --学号
SName      char(10) not null,            --姓名
SAddress   nvarchar(50),                 --地址
SGrade     float,                        --年级
SEmail     nvarchar(50),                 --邮箱
CreateTime Datetime default getdate()   --创建时间
);

select *from sysobjects where name = 'Students';

select * from students;
--约束 constraint
--主键 primary key
--外键 foreign key
--检查 check key
--默认 default
--唯一 unique

--为表增加主键约束 --column字段
alter table students
alter column SCode int not null;

--将学生学号设置为主键
alter table students
add constraint pk_Scode primary key(SCode);
--将学生姓名为主键

--学生表新增性别字段
alter table students add SSex int;

--主键约束，不能插入重复键
INSERT INTO students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁','上海松江',6,'ZQC@Sohu.com',1);

--创建表2--成绩表
create table Score
(
ScoreID    int IDENTITY(1,1) primary key, --主键
StudentID int not null,      --学号
CoureID    int,               --学科
Score      smallint            --分数
);

--创建外键约束主表：students 从表：Score
alter table Score
add constraint fk_ScoreID_id
foreign Key(StudentID)
references students(SCode);

--外键约束，成绩表中插入学号为3，但主表中没有
insert into Score(StudentID,CoureID,Score)
VALUES(3,2,80);

--删除约束
ALTER TABLE dbo.students DROP CONSTRAINT fk_ScoreID;

--创建表3--课程表
create table Course
(
CourseID int primary key,
CourseName nvarchar(50)
);

--约束 constraint
--主键 primary key
--外键 foreign key
--检查 check key
--默认 default
--唯一 unique

--检查约束
--是否为空约束年级不能为空
alter table students
alter column SGrade float not null;

INSERT INTO students (SName,SAddress,SEmail,SSEX,SGrade)
VALUES ('张青裁','上海松江','ZQC@Sohu.com',0); --空约束

INSERT INTO students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁','上海松江',6,'ZQC@Sohu.com',0);
--删除字段
alter table students
drop column SSex;

--学生表新增性别字段
alter table students add SSex int;

--查看表的字段
sp_columns students;
sp_columns Score;
sp_columns Course;

--新增检查约束性别只能为0或1
alter table students
add constraint ck_SSex
check(SEmail like '%@%');

--为地址增加默认值默认约束，约束键名为DF_stdents_SAddress
alter table students
add constraint DF_stdents_SAddress
default '长沙软件园实训班' for SAddress;

update students set SSex='4'; -- 性别为4的不允许输入

--分数只能在0到100
alter table Score
add constraint ck_Score
check(Score>=0 and Score<=100);

update Score set score=120; --分数为120分的不允许输入

select * from students
select * from Score
select * from Course

insert into students(SName,SAddress,SGrade,SEmail,SSEX) values('张三','上海松江',3,'ZQC@Sohu.com',0);
insert into students(SName,SAddress,SGrade,SEmail,SSEX) values('李四',default,2,'aa',1);
insert into students(SName,SAddress,SGrade,SEmail,SSEX) values('王五',default,1,'aa',0);

--修改字段名
sp_rename 'students.SEmial','SEmail';
--数据数据库
sp_rename 'Scores','Score'

--在表studentsz中SGrade 修改类型‘长度和不为空
alter table students
alter column SGrade nvarchar(60) not null;
--还原上一步的修改
alter table students
alter column SGrade float null;

--第三章要求：增、删、改、查数据

select * from students
select * from Score
select * from Course

---
---新增数据
---
--给现有的表中插入数据报'TongXunLu' 无效
INSERT INTO TongXunLu (姓名,地址,电子邮件)
SELECT SName,SAddress,SEmail
FROM Students

--从一个表中查询出数据插入到另一个表中
SELECT students.SName,students.SAddress,students.SEmail,IDENTITY(int,1,1) As StudentID
INTO TongXunLuEX
FROM students;

select * from TongXunLuEX;

--插入多行数据INSERT STUDENTS (SName,SGrade,SSex)
INSERT STUDENTS (SName,SGrade,SSex)
SELECT '张可',7,1 UNION
SELECT '李扬',4,0 UNION
SELECT '杨晓',2,0 UNION
SELECT '汤美',3,0 UNION
SELECT '苏三东',7,1 UNION
SELECT '王立岩',3,1 UNION
SELECT '张伟',7,1 UNION
SELECT '陈刚',4,1 UNION
SELECT '王娟娟',7,0

---
---更新数据
---

UPDATE Students
SET SAddress ='北京女子职业技术学校家政班'
WHERE SAddress = '长沙软件园实训班'

--更新数据加约束条件
UPDATE Score
SET Scores = Scores + 5
WHERE Scores <= 95

--删除数据
delete students where SName= '张青裁' and SCode =1;

--外键约束，先删除从表，再删除主表
delete students where SCode =3;

--对象名 'SCode' 无效
DELETE SCode FROM Students

--删除数据，保存表结构
select * from TongXunLuEX
--从一个表中查询出数据插入到另一个表中
insert INTO TongXunLuEX select students.SName,students.SAddress,students.SEmail
FROM students;

truncate table TongXunLuEX;

--清空数据
delete TongXunLuEX where 1=1;

--一个表的表结存放到另一个表中
select *
into ABC
from students where 1=2

select * into aaa from students ; --aaa表不存在,新建表并插入数据
insert into aaa select * from students ---aaa表已存在，只需插入数据

select * from students where 1=2
select * from abc

--查询数据
select * from students;
select * from Score;
select * from Course

----第四章条件查询、表达式和函数
--部分查询
select SCode,SName from students where SGrade = 7 --年级等于7
select SCode,SName from students where SGrade <> 7; --年级不等于7

--使用AS取列的别名
SELECT SCode AS 学员编号,SName AS学员姓名,SAddress AS 学员地址
FROM Students
WHERE SAddress <> '北京女子科学技术学校家政班'

--使用“+”做连接字符串
select SCode AS学员编号,SName+'@'+'yahoo.com.cn' AS 邮箱
from students;

--使用=号取别名
SELECT '学员编号'=SCode,'学员姓名'=SName,学员地址='SAddress'
FROM Students
WHERE SAddress <> '北京女子职业技术学校家政班'

SELECT 学员编号=SCode,学员姓名=SName,学员地址=SAddress
FROM Students

--查询空数据
select * from students
where SEmail is null

--增加常量数据
SELECT 姓名=SName,地址= SAddress,'河北新龙' AS 学校名称
FROM Students

--查询前5行数据
SELECT TOP 5 SName, SAddress
FROM Students WHERE SSex = 0;

--前20%数据
SELECT TOP 20 PERCENT SName, SAddress
FROM Students WHERE SSex = 0

select top 20 * from students ;

--=null查询不出数据
select * from students
where SEmail = null

--排序查询
select * from students order by SGrad --默认升序
select * from students order by SGrade asc --升序
select * from students order by SGrade desc --降序

select * from Score;

insert into Score(scoreid,studentid,coureid,score) values(2,4,2,90);
insert into Score(scoreid,studentid,coureid,score) values(3,3,3,80);

select * from score order by score;
select * from score order by score, scoreid;
select * from score order by score desc;

--函数

--字符串
SELECT REPLACE('莫乐可切.杨可','可','兰')

update Card set PassWord=replace(select PassWord from Card, '哦','零');
update Card set PassWord=replace(select PassWord from Card, 'i','1')

select * from students;
update students set SAddress=replace(SAddress ,'职业','科学');

--有关日期的操作
select getdate()
--日期相加
select dateadd(dd,10,getdate()); --加10天
select dateadd(dd,-10,getdate())
select dateadd(mm,10,getdate()); --加10个月
select dateadd(yy,10,getdate());
select dateadd(hh,10,getdate());
select dateadd(mi,10,getdate());

yymmddhhmi dw星期
--二个日期相减
--默认日期格式：年/月/日
SELECT DATEDIFF(dd,'04/2/14',getdate())
SELECT DATEDIFF(dd,'11/11/01','11/11/05')
SELECT DATEDIFF(mm,'01/01/12','01/05/2');
select 年龄=datediff(yy,convert(datetime,'2004/02/14'),getdate());
select 天数=datediff(dd,convert(datetime,'2004/02/14'),getdate());

--日期中指定日期部分的字符串形式
SELECT DATENAME(dw,getdate());
SELECT DATENAME(yy,getdate());
SELECT DATENAME(mm,getdate());
SELECT DATENAME(dd,getdate());
--日期中指定日期部分的整数形式
SELECT datepart(dw,getdate());
SELECT datepart(yy,getdate());
SELECT datepart(mm,getdate());
SELECT datepart(dd,getdate());
--数据类型转化
select convert(varchar(10),getdate(), 11); --其中11为格式
SELECT CONVERT (VARCHAR (5),12345);

--截取指定长度
select cast(getdate() as varchar(30));

select getdate()

--第五章模糊查询聚合函数
--课前问题
SELECT SCode,SName,SAddress
FROM Students
WHERE SSEX = 0
ORDER BY SAddress;

select * from students where SSEX=1;
--查询班上考试成绩前三名的成绩的SQL语句怎么写？
select top 3 * from Score order by Score desc;
--如何使用SQL语句得到50天以前的日期信息？
select * from students where CreateTime < dateadd(dd,-50,getdate());

--更新时间
update students
set CreateTime = dateadd(dd,-51,getdate())
where Scode =6

--模糊查询
select * from students where SAddress like '北京%' ;

--is null
select * from students where SEmail is null;

--BETWEEN
SELECT StudentID, Score FROM SCore
WHERE Score BETWEEN 60 AND 80;

--in
SELECT SName AS 学员姓名,SAddress As 地址 FROM Students
WHERE SAddress IN ('上海松江','广州','北京%')

--函数求和sum
select sum(ssex) from students;

--函数求平均avg
SELECT AVG(SCore) AS 平均成绩
From Score WHERE Score >=60;

--求最大值max;最小值min
SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,
MIN (Score) AS 最低分 From Score WHERE Score >=60

--求学生数
select count(*) from students;
SELECT COUNT (*) AS 及格人数 From Score
WHERE Score>=60

--去掉重复 distinct
select distinct(saddress) from students

--求各门课的平均成绩
SELECT CoureID AS 课程, AVG(Score) AS 课程平均成绩
FROM Score
GROUP BY CoureID

SELECT StudentID AS 学员编号,CoureID AS 内部测试, AVG(Score) AS 内部测试平均成绩
FROM Score
GROUP BY StudentID,CoureID

--having
SELECT StudentID AS 学员编号,CoureID AS 内部测试,
AVG(Score) AS 内部测试平均成绩
FROM Score
GROUP BY StudentID,CoureID
HAVING COUNT(Score)>1

--内联结(INNER JOIN)

select * from students;
select * from score;

select s.sname,c.score
from students s,score c
where s.scode=c.studentid;

SELECT S.SName,C.CoureID,C.Score
From Students AS S
INNER JOIN Score AS C
ON C.StudentID = S.SCode

--再猜一猜：以下返回多少行？
SELECT S.SName,C.CoureID,C.Score
From Students AS S
INNER JOIN Score AS C
ON C.StudentID <> S.SCode

--猜一猜：这样写，返回的查询结果是一样的吗？

SELECT S.SName,C.CoureID,C.Score
From Students AS S
INNER JOIN Score AS C
ON C.StudentID = S.SCode

--左关联查询
SELECT S.SName,C.CoureID,C.Score
From Students AS S, Score AS C
where C.StudentID = S.SCode

SELECT S.SName,C.CoureID,C.Score
From Students AS S
LEFT JOIN Score AS C
ON C.StudentID = S.SCode

SELECT S.SName,C.CoureID,C.Score
From Score AS C
LEFT JOIN Students AS S
ON C.StudentID = S.SCode

--右外关联
select s.sname,c.coureID,c.score
from students s
RIGHT OUTER JOIN score c
on s.scode=c.studentid

SELECT score, IDENTITY(int,1,1) AS ID
INTO Score_new
FROM score

select * from Score_new;

--查询数据
select * from students;
select * from Score;
select * from Course

--第八章

--变量的声明赋值使用

select * from students;

/*--查找李扬的信息--*/
DECLARE @name varchar(8); --学员姓名
SET @name='李扬'; --使用SET赋值
SELECT * FROM students WHERE sName = @name;

/*--查找李扬的学号左右--*/
DECLARE @scode int --座位号
SELECT @scode=scode FROM students --使用SELECT赋值
WHERE sname=@name
SELECT * FROM students
WHERE (scode = @scode+1) OR (scode = @scode-1)
GO

--输出
print '数据库版本'+@@VERSION;
print '服务器名称'+@@SERVICEnAME
INSERT INTO Students (SCode,SName,SAddress,SGrade,SEmail,SSEX)
VALUES (32,'张青裁','上海松江',6,'ZQC@Sohu.com',0) ;
PRINT '当前错误号：'+convert(varchar(5),@@ERROR);

SELECT * FROM score;

--IF ELSE
declare @avg float;
select @avg=avg(score) from score;
print '本班平均分：'+convert(varchar(5),@avg);
IF(@avg >70)
begin
     print '本班成绩优秀，前三名成绩为：';
     select top 3* from score order by score Desc;
end
else
begin
     print '本班成绩较差，后三名成绩为：';
     select top 3 * from score order by score asc;
end

--WHILE循环
declare @n int;
while(1=1)
begin
   select @n=count(*) from score where score<60;
if(@n>0)
     update score set score=score+2;
   else
     break; --跳出循环
end
select * from score;

--多分支
select studentID,等级=case
                           when score <60 then 'E'
                           when score between 60 and 69 then 'D'
                           when score between 70 and 79 then 'C'
                           when score between 80 and 89 then 'B'
                           else 'A'
                       end
from score;

declare @n int;
select @n=count(*) from score where score<70;
while(@n!= 0)
begin
update score set score=score+2;
select @n=count(*) from score where score<70;
end
select * from score;

--第九章高级查询
--子查询
select * from students
where scode > (select scode from students where sname='张伟');

-- IN 子查询
select *
from students
where saddress in('北京','长沙');

select * from students
where scode =(select studentid from score where score=85);

print '参加考试人员名单：'
select * from students
where scode in (select studentid from score);

print '未参加考试人员名单：'
select sname from students
where scode not in (select studentid from score );

print '参加考试人员名单：'
select * from students
where exists (select studentid from score);

--exists 判定是否存在
if exists( select * from score where score >80 )
update score set score=score+2
else
update score set score=score +5

--综合查询1
declare @number int
declare @pass int
SELECT @number=count(*) from score
select @pass=count(*) from score where score >60
select 应到人数 = (select count(*) from students),
实到人数=(select count(*) from score),
缺考人数=((select count(*) from students)-(select count(*) from score)),
通过人数=(select count(*) from score where score >60),
通过率 =( convert(varchar(5),(@pass * 100 / @number))+'%')
--综合查询2
select 应到人数 = (select count(*) from students),
实到人数=(select count(*) from score),
缺考人数=((select count(*) from students)-(select count(*) from score)),
通过人数=(select count(*) from score where score >60),
通过率 =( convert(varchar(5),((select count(*) from score where score >60) * 100 / (select count(*) from score)))+'%')

--去掉重复数据
select * from students;
select distinct sname from students;

select * from score;

insert into score values(13,6, 80);
delete students where scode=11;
select * from students;

--第10章事务、索引和视图

--事物
CREATE TABLE bank
(
    customerName CHAR(10), --顾客姓名
    currentMoney MONEY     --当前余额
)
GO
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney
       CHECK(currentMoney>=1)
GO
INSERT INTO bank(customerName,currentMoney)
        VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)

select * from bank;

/*--转账测试：张三转账1000元给李四--*/
--我们可能会这样这样编写语句
--张三的账户少1000元，李四的账户多1000元
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName='张三'
UPDATE bank SET currentMoney=currentMoney+1000
WHERE customerName='李四'
GO
--再次查看转账后的结果。
SELECT * FROM bank
GO

--begin transaction
--commit transaction
--rollback

select * from bank;
--关键语句讲解
BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0，即无错误
/*--转账：张三的账户少1000元，李四的账户多1000元*/
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName='张三'
SET @errorSum=@errorSum+@@error
UPDATE bank SET currentMoney=currentMoney+1000
WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误

IF @errorSum<>0 --如果有错误
BEGIN
    print '交易失败，回滚事务'
    ROLLBACK TRANSACTION
END
ELSE
BEGIN
    print '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION
END
GO
print '查看转账事务后的余额'
SELECT * FROM bank
GO

--转800
BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0，即无错误
/*--转账：张三的账户少1000元，李四的账户多1000元*/
UPDATE bank SET currentMoney=currentMoney-800
WHERE customerName='张三'
SET @errorSum=@errorSum+@@error
UPDATE bank SET currentMoney=currentMoney+800
WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误

--索引

select * from students;
--为students表SName字段创建索引
create index ix_SName on students(SName);

--使用索引查询数据
select * from students with(index=ix_SName);

--视图
select * from students;
select * from Score;

--班主任视图
create view View_students_a
as
select 学号=SCode,姓名=SName,班级=SGrade,性别=SSex
from students;
--使用视图
select * from View_students_a

--第11章存储过程
--系统存储过程

sp_databases

sp_tables students;

sp_columns students;
sp_columns score;
sp_columns course;

--创建存储过程无参数
create proc look_avg_score
as
begin
select avg(score) from score;
end
GO
--调用存储过程
exec look_avg_score;

--创建存储过程有输入参数参数

create proc look_pass
as
begin
    select a.sname,b.score
    from students a ,score b
    where b.score>=60 and a.scode=b.studentid;
end
GO

create proc look_pass
@pass_score int
as
begin
    select a.sname,b.score
    from students a ,score b
    where b.score>=@pass_score and a.scode=b.studentid;
end
GO

look_pass 30
look_pass @pass_score=60

--带默认值
create proc look_pass1
@pass_score int=60
as
begin
    select a.sname,b.score
    from students a ,score b
    where b.score>=@pass_score and a.scode=b.studentid;
end

GO
--调用
look_pass1
look_pass1 30
look_pass1 @pass_score=40

--带输出参数
create proc look_notpass
@not_pass_sum int output,
@pass_score int=60
as
begin
    select a.sname,b.score
    from students a ,score b
    where b.score < @pass_score and a.scode=b.studentid;

    select @not_pass_sum=count(*) from score where score<@pass_score;
end

declare @sum int
exec look_notpass @sum output,70
print '---------------------------------------'
if (@sum >=3)
   begin
      print '未通过人数：'+convert(varchar(5),@sum)+'人，超过60%，及格分数线应该下调！'
    end
else
    begin
       print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中！'
     end

drop proc look_notpass
select * from score

--创建存储过程无参数
create procedure look_avg_score
as
begin
select 平均成绩=avg(Score) from Score
end

select * from dbo.Score

--使用存储过程
EXEC look_avg_score

-- 根据学生学号查学生信息 --有参数
create proc select_student
@scode int=1
as
select * from students where scode=@scode;

select_student 2

--删除
drop proc select_student

DELETE score_new1 FROM score_new2

select * into score_new1 from students

select * from score_new2

delete score_new2 where Semail is null

SELECT StudentID, CoureID, AVG(Score) AS 课程平均成绩
FROM dbo.Score
GROUP BY CoureID,StudentID