SQL Server 数据库基本操作语句总结
发布时间:2024-01-18 05:12:06
--sql基本操作
--创建数据库
create database Studets
--创建表
create table student ( sno char(5), sname char(20), ssex char(2), sage smallint, sdept char(15) )
create table course ( cno char(3), cname char(30), cpno char(3), ccredit smallint )
create table sc ( sno char(5), cno char(3), grade int )
--查看表信息
select * from student select sno as 学号 from student select * from course select * from sc
--修改表
--插入列
alter table student add scome datetime
--修改列的字段类型 alter table student alter column scome char(50)
--删除 --删除列
alter table student drop column scome
--删除表 drop table student drop table course drop table sc
--完整性约束实现
--sno 非空唯一,ssex检查约束, sage默认大小
create table student ( sno char(5) not null unique, sname char(20), sex char(2), sage smallint default 20, sdept char(15), constraint sex check(sex in('男','女')), )
--删除表的约束 alter table student drop constraint ssex
--添加字段约束 alter table student add constraint ssex check(sex in('男','女'))
--添加主键约束 alter table student add constraint PK_SNO primary key(sno) create table course ( cno char(3) not null unique, cname char(30), cpno char(3), ccredit smallint )
--关联表主键已经存在,可以如下操作添加主键和外键约束
alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFERENCES sc(cno)
create table sc
(
sno char(5) foreign key references student(sno),
cno char(3) foreign key references course(cno),
grade int,
constraint PK_SC primary key(sno,cno)
)
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
--创建sc后,通过如下修改主外键
alter table sc add constraint PK_SC primary key(sno,cno),
constraint FK_SNO foreign key(sno) references student(sno),
constraint FK_CNO foreign key(cno) references course(cno)
--创建索引。
分为聚簇索引(clustered物理顺序)和非聚簇索引(nonclustered逻辑顺序,可多个)
--not null约束字段时候。会创建一个系统内置的约束键值,并且这种非空判断,通过索引查询实现 --的,索引默认创建一个系统索引
create unique index STUsno
on student(sno)
create unique index COUcno
on course(cno)
create unique index SCno
on sc(sno asc,cno desc)
drop index SCno on sc
--显示表的数据和索引的碎块信息 DBCC SHOWCONTIG
--插入数据 select * from student
alter table student alter column sno char(10)
insert into student values('10021','张三','男',20,'计科系')
insert into student values('10022','王朝','女',18,'软件')
insert into student values('10023','朱元璋','男',20,'管理')
insert into student values('10024','刘彻','男',18,'军事')
insert into student values('10025','刘表','男',20,'商学系')
insert into student values('10026','白居易','男',19,'文法')
insert into student values('10027','李清照','女',24,'文法')
select * from course insert into course values('001','数据库','005',4)
insert into course values('002','高等数学','',2)
insert into course values('003','信息系统','001',4)
insert into course values('004','操作系统','006',2)
insert into course values('005','数据结构','007',3)
insert into course values('006','数据处理','',2)
insert into course values('007','C语言','006',5)
select * from sc insert into sc values('10021','002',100)
insert into sc values('10021','001',88)
insert into sc values('10021','006',100)
insert into sc values('10021','007',68)
insert into sc values('10022','002',100)
insert into sc values('10023','005',30)
insert into sc values('10024','002',100)
insert into sc values('10024','006',56)
select * from student --查询操作
--查询 select * from student select * from course select * from sc
--去掉重复行 select distinct sno from sc
--格式化查询
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
--条件查询
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit<5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--匹配查询
select * from student where sname like '刘__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
--算术元算查询
select grade*(1+0.2) as 总成绩,grade/(10) as 绩点 from sc
--分组函数查询
select COUNT(*) as 总人数 from student
select COUNT(distinct sno) as '选修的总人数' from sc
select AVG(grade) as '平均成绩' from sc where sno='10021'
select MAX(grade) as 'MAX成绩' from sc where sno='10021'
select MIN(grade) as 'MIN成绩' from sc where sno='10021'
select SUM(grade) as '总成绩' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as '平均成绩' from sc where sno='10021'
select SUM(grade) as '总成绩' from sc group by sno having sum(grade)>100 -
-连接查询、
--等值连接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--自身连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as '同一个系' from student A,student B where A.sname='白居易' and A.sdept=B.sdept
--外连接
select A.*,B.* from student A left join sc B on A.sno=B.sno
select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
--复合条件连接
select * from sc select * from course
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--字符串连接查询
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade<60) group by sname
--子查询
select * from student where sage>(select AVG(sage) from student)
--是否存在的查询
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql创建用户 sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--权限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
--视图的创建
create view VIEW_STUGrade(学号,姓名,课程,成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件'
--查看视图
select * from VIEW_STUGrade
--视图修改
alter view VIEW_STUGrade(学号,姓名,课程,成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件'
with check option
--更新失败后不影响视图查看
--视图更新
update VIEW_STUGrade set 姓名='王超' where 学号='10022' select * from student where sno='10022'
/* 1,可更新视图: a,单个基本表导出的 2,不可更新视图 a 两个以上基本表导出的 b 视图字段来自表达式或者函数 c 嵌套查询的表 d 分组子句使用distinct */
--删除视图 drop view VIEW_STUGrade
--高级sql编程
--数据类型1,int 2,smallint 3,tinyint (0--255) 4,bigint 5char固定长度<800.如:学号,姓名 6,varchar可变长度小于800 7,text 2GB 8,nvarchar1--4000 */
--运算符和通配符
select GETDATE()-1 昨天,GETDATE() 今天,GETDATE()+1 明天
select 59&12
select 59|12
select 59^12
--模糊查询
select * from student where sname like '%刘%'
select * from student where sno like '1002[5-9]'
--控制流程语句
declare @name char(10) set @name='司马相如'
print @name
--输出一个表达式,不能进行查询 select @name
--输出多个表达式
declare @a nvarchar(50),@b nvarchar(50)
set @a=33 set @b=34 ---简写select@a=33,@b=34
if @a>@b
print '最小值是:'+@a
else
print '最大值是:'+@b
--waitfor间隔一段时间执行
waitfor delay '00:00:04' print '推迟4秒执行'
waitfor time '17:45:50' print '等待这一时刻执行'
--创建函数
CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime )
RETURNS TABLE
AS RETURN (
select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差
)
--创建存储过程,
--查看
GO create proc [dbo].[sel] (
@sno char(10)
)
as
select * from student where sno=@sno
exec sel @sno='10021'
--查看
GO create proc sel2
as
select * from student
exec sel2
--修改
GO create proc updat @sno char(10), @sex char(2)
as
update student set sex=@sex where sno=@sno
select * from student exec updat @sno='10021', @sex='女'
--删除
GO create proc dele @sno char(10)
as
delete student where sno=@sno
select * from student
exec dele @sno='10029'
--插入
GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15)
as
insert into student values(@sno,@sname,@sex,@sage,@sdept)
exec inser @sno='10029', @sname='tom', @sex='男', @sage=100, @sdept='sc' select * from student
--查询操作
--查询
select * from student select * from course select * from sc
--去掉重复行 select distinct sno from sc
--格式化查询
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
--条件查询
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit<5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--匹配查询
select * from student where sname like '刘__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
--算术元算查询
select grade*(1+0.2) as 总成绩,grade/(10) as 绩点 from sc
--分组函数查询
select COUNT(*) as 总人数 from student
select COUNT(distinct sno) as '选修的总人数' from sc select AVG(grade) as '平均成绩' from sc where sno='10021'
select MAX(grade) as 'MAX成绩' from sc where sno='10021'
select MIN(grade) as 'MIN成绩' from sc where sno='10021'
select SUM(grade) as '总成绩' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as '平均成绩' from sc where sno='10021'
select SUM(grade) as '总成绩' from sc group by sno having sum(grade)>100
--连接查询、 --等值连接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--自身连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno select B.sname as '同一个系' from student A,student B where A.sname='白居易' and A.sdept=B.sdept
--外连接
select A.*,B.* from student A left join sc B on A.sno=B.sno select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
-复合条件连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--字符串连接查询
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade<60) group by sname
--子查询
select * from student where sage>(select AVG(sage) from student)
--是否存在的查询
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql创建用户
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--权限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
--视图的创建
create view VIEW_STUGrade(学号,姓名,课程,成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件'
--查看视图
select * from VIEW_STUGrade
--视图修改
alter view VIEW_STUGrade(学号,姓名,课程,成绩) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件'
with check option
--更新失败后不影响视图查看 --视图更新
update VIEW_STUGrade set 姓名='王超' where 学号='10022' select * from student where sno='10022'
/* 1,可更新视图: a,单个基本表导出的 2,不可更新视图 a 两个以上基本表导出的 b 视图字段来自表达式或者函数 c 嵌套查询的表 d 分组子句使用distinct */
--删除视图 drop view VIEW_STUGrade
--触发器
use Studets
GO create trigger insert_Tri
ON student after
insert as print '有新数据插入!'
GO create trigger update_Tri
on student after
update as print '有数据更新!'
GO create trigger delete_Tri
on student after
delete as print '有数据删除!'
--修改触发器
GO alter trigger delete_Tri
on student after delete
as
if '王帅' in (select sname from deleted)
print '该信息不许删除!'
rollback transaction
--执行存储过程查看触发器使用情况
exec sel @sno='10021'
exec inser @sno='10029', @sname='王帅', @sex='男', @sage=25, @sdept='国贸'
exec updat @sno='10029', @sex='女'
exec dele @sno='10029'
--查看,修改,删除触发器
/* sp_*+触发器名称
sp_helptext:触发器正文信息 sp_help:查看一般信息,触发器名称,属性,创建时间,类型 sp_depends:引用或指定表的所有触发器 sp_helptrigger:指定信息 */ sp_help delete_Tri
sp_helptext delete_Tri
sp_depends delete_Tri
sp_helptrigger student
--删除触发器
drop trigger delete_Tri
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 一.Pygame程序基本搭建过程Pygame搭建游戏窗口主要为如下几步1.初始化化程序在使用Pygame编程之前,我们要对程序进行初始化,代
- 要求编写登录接口: 1. 输入用户名和密码2.认证成功后显示欢迎信息3.用户名输错,提示用户不存在,重新输入(5次错误,提示尝试次数过多,退
- 写在前面和小伙伴们分享一些Python 网络编程的一些笔记,博文为《Python Cookbook》读书后笔记整理博文涉及内容包括:TCP/
- 写了个小程序:功能1.测试远程ssh连接是否成功,2.批量执行远程ssh命令效果如下:代码如下:#-*- coding:utf-8 -*-i
- 我就废话不多说了,大家还是直接看代码吧~#coding=utf-8import cv2import numpy as npimg=cv2.i
- 下面的demo是根据需求写的简单测试脚本#!/usr/bin/env python# coding: utf-8# 第一个列表为依赖组件和版
- getattr`getattr`函数属于内建函数,可以通过函数名称获取value = obj.attributevalue = getatt
- 0x00 前言大家对HTTP代理应该都非常熟悉,它在很多方面都有着极为广泛的应用。HTTP代理分为正向代理和反向代理两种,后者一般用于将防火
- 1. 切片不执行越界检查和报错下面代码的输出结果将是什么?list = ['a', 'b', 'c&
- 如何在线创建新表?下面我们以建立一个数码相机库用表为例,看看在ASP程序代码中使用 [CREATE TABLE 相机 (品牌 TEXT(10
- 本文实例讲述了Python线程threading模块用法。分享给大家供大家参考,具体如下:threading-更高级别的线程接口源代码:Li
- 一、日期类型:对于SQL Server 2008 来说(因为2000甚至2005已经稍微有被淘汰的迹象,所以在此不作过多说明,加上自己工作使
- TKinterPython 的 GUI 库非常多,之所以选择 Tkinter,一是最为简单,二是自带库,不需下载安装,随时使用,跨平台兼容性
- 2012年,Hinton的学生Alex Krizhevsky提出了深度卷积神经网络模型AlexNet,它可以算是LeNet的一种更深更宽的版
- 代码如下import unittestdir = "D:\\work_doc\\pycharm2\\python_Basics&q
- 在编写T-SQL代码时,往往需要临时存储某些结果集。前面我们已经广泛使用和介绍了两种临时存储结果集的方法:临时表和表变量。除此之外,还可以使
- 目录模块与包__import__模块缓存imp 与 importlib 模块惰性导入总结参考资料本文不讨论 Python 的导入机制(底层实
- 代码如下:<?php$a;$b = false;$c = '';$d = 0;$e = null;$f = array
- 今天开始学习python,首先环境安装1.在https://www.python.org/downloads/下载python2.X或者3.
- 本文实例为大家分享了Python实现简单猜数字游戏的具体代码,供大家参考,具体内容如下一、需求分析编写一个猜数字游戏,游戏规则:计算机给出一