sql试图

最新推荐文章于 2024-07-06 03:40:53 发布

转载最新推荐文章于 2024-07-06 03:40:53 发布 · 389 阅读

sql 同时被 2 个专栏收录

14 篇文章

订阅专栏

随笔

9 篇文章

订阅专栏

本文介绍了使用SQL进行数据库操作的方法，包括创建和删除数据库、表及视图，还涉及了约束条件的添加与移除。通过具体示例展示了如何构建带有唯一约束的表，并创建了两个视图来展示学生信息的不同方面。

--系统视图
select * from sys.tables
select * from sys.objects
--系统表
--select * from sysobjects

if exists(select * from sys.databases where name='demo')
	drop database demo
create database demo
go

use demo
go

if exists(select * from sys.objects where name='test')
	drop table test
create table test
(
	tId int identity(1,1) primary key,
	tName nvarchar(10)
)

if exists(select * from sys.objects where name='UQ_test_tName')
	alter table test drop constraint UQ_test_tName
alter table test
add constraint UQ_test_tName unique(tName)

--创建视图
use MySchool
if exists(select * from sys.objects where name='v_Student')
	drop view v_Student
go
create view v_Student
as
	select sName,sAge,sSex from student



select * from v_Student


if exists(select * from sys.objects where name='v_Student1')
	drop view v_Student1
go
create view v_Student1
as 
	select sName,sAge,
	case 
		when english is null then '缺考'
		when english < 60 then '不及格'
		else convert(varchar,english)
	end as '成绩'
	from student 
	left join score on student.sId = studentId



select * from v_Student1


select * from view_2 where name='刘备'