SQLServer2016--添加登录账号、后续练习脚本
来源:互联网 发布:淘宝店铺被释放怎么办 编辑:程序博客网 时间:2024/05/29 08:55
登录验证方式介绍
SQLServer身份验证:适合于非Windows平台的用户或Interner用户,需要提供账户和密码。
Windows身份验证:适合与windows平台用户,不需要提供密码,和Windows集成验证。
创建多个Windows登录账号的方法
在本机计算创建多个Windows账号
例如-要使用Shark的Windows账号登录数据库:
use mastergoexec sp_grantlogin 'SHARK2\Shark'
注销Windows后–启用新建的Shark用户就可以登录了
此创建方法局限于本机!!
建议用重点方法SQLServer创建登录用户
SQLServer创建登录用户
重点的SQLServer用户创建方法:
use mastergoexec sp_addlogin 'SqlShark','aaa999a9'--登录名,密码
后续练习脚本
--指向当前要使用的数据库use mastergo--判断当前数据库是否存在if exists (select * from sysdatabases where name='StuManageDB')drop database StuManageDB--删除数据库go--创建数据库create database StuManageDBon primary( --数据库文件的逻辑名 name='StuManageDB_data', --数据库物理文件名(绝对路径) filename='D:\SQL\DB\StuManageDB_data.mdf', --数据库文件初始大小 size=10MB, --数据文件增长量 filegrowth=1MB)--创建日志文件log on( name='StuManageDB_log', filename='D:\SQL\DB\StuManageDB_log.ldf', size=2MB, filegrowth=1MB)go--创建学员信息数据表use StuManageDBgoif exists (select * from sysobjects where name='Students')drop table Studentsgocreate table Students( StudentId int identity(100000,1) , StudentName varchar(20) not null, Gender char(2) not null, Birthday smalldatetime not null, StudentIdNo numeric(18,0) not null,--身份证号 Age int not null, PhoneNumber varchar(50), StudentAddress varchar(500), ClassId int not null --班级外键)go--创建班级表if exists(select * from sysobjects where name='StudentClass')drop table StudentClassgocreate table StudentClass( ClassId int primary key, ClassName varchar(20) not null)go--创建成绩表if exists(select * from sysobjects where name='ScoreList')drop table ScoreListgocreate table ScoreList( Id int identity(1,1) primary key, StudentId int not null, CSharp int null, SQLServerDB int null, UpdateTime smalldatetime )go--创建管理员用户表if exists(select * from sysobjects where name='Admins')drop table Adminscreate table Admins( LoginId int identity(1000,1) primary key, LoginPwd varchar(20) not null, AdminName varchar(20) not null)go--创建数据表的各种约束use StuManageDBgo--创建“主键”约束primary keyif exists(select * from sysobjects where name='pk_StudentId')alter table Students drop constraint pk_StudentIdalter table Studentsadd constraint pk_StudentId primary key (StudentId)--创建检查约束checkif exists(select * from sysobjects where name='ck_Age')alter table Students drop constraint ck_Agealter table Studentsadd constraint ck_Age check (Age between 18 and 35) --创建唯一约束uniqueif exists(select * from sysobjects where name='uq_StudentIdNo')alter table Students drop constraint uq_StudentIdNoalter table Studentsadd constraint uq_StudentIdNo unique (StudentIdNo)--创建身份证的长度检查约束if exists(select * from sysobjects where name='ck_StudentIdNo')alter table Students drop constraint ck_StudentIdNoalter table Studentsadd constraint ck_StudentIdNo check (len(StudentIdNo)=18)--创建默认约束 if exists(select * from sysobjects where name='df_StudentAddress')alter table Students drop constraint df_StudentAddressalter table Students add constraint df_StudentAddress default ('地址不详' ) for StudentAddressif exists(select * from sysobjects where name='df_UpdateTime')alter table ScoreList drop constraint df_UpdateTimealter table ScoreList add constraint df_UpdateTime default (getdate() ) for UpdateTime--创建外键约束if exists(select * from sysobjects where name='fk_classId')alter table Students drop constraint fk_classIdalter table Studentsadd constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)if exists(select * from sysobjects where name='fk_StudentId')alter table ScoreList drop constraint fk_StudentIdalter table ScoreListadd constraint fk_StudentId foreign key(StudentId) references Students(StudentId)-------------------------------------------插入数据--------------------------------------use StuManageDBgo--插入班级数据insert into StudentClass(ClassId,ClassName) values(1,'软件1班')insert into StudentClass(ClassId,ClassName) values(2,'软件2班')insert into StudentClass(ClassId,ClassName) values(3,'计算机1班')insert into StudentClass(ClassId,ClassName) values(4,'计算机2班')--insert into StudentClass(ClassId,ClassName) values(5,'网络1班')--insert into StudentClass(ClassId,ClassName) values(6,'网络2班')--插入学员信息insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('王小虎','男','1989-08-07',22,120223198908071111,'022-22222222','天津市南开区红磡公寓5-5-102',1)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('贺小张','女','1989-05-06',22,120223198905062426,'022-33333333','天津市河北区王串场58号',2)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('马小李','男','1990-02-07',21,120223199002078915,'022-44444444','天津市红桥区丁字沽曙光路79号',4)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('冯小强','女','1987-05-12',24,130223198705125167,'022-55555555',default,2)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('杜小丽','女','1986-05-08',25,130223198605081528,'022-66666666','河北衡水路北道69号',1)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('王俊桥','男','1987-07-18',24,130223198707182235,'022-77777777',default,1)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('张永利','男','1988-09-28',24,130223198909282235,'022-88888888','河北保定市风华道12号',3)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('李铭','男','1987-01-18',24,130223198701182257,'022-99999999','河北邢台市幸福路5号',1)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('宁俊燕','女','1987-06-15',24,130223198706152211,'022-11111111',default,3)insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId) values('刘玲玲','女','1989-08-19',24,130223198908192235,'022-11111222',default,4)--插入成绩信息insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,92)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)--插入管理员信息insert into Admins (LoginPwd,AdminName) values(123456,'王晓军')insert into Admins (LoginPwd,AdminName) values(123456,'张明丽')--删除学员信息--delete from Students --truncate table Students --删除全部数据以后,自动标识列重新编号--显示学员信息和班级信息select * from Studentsselect * from StudentClassselect * from ScoreListselect * from Admins
后续所需SQL脚本
>
[基于俩层框架的学生管理系统脚本(http://download.csdn.net/download/qq_36482772/9967561)
阅读全文
1 0
- SQLServer2016--添加登录账号、后续练习脚本
- 批量添加登录账号
- Python脚本练习-登录
- Ubuntu 14.04 登录 界面添加 root账号
- linux ubuntu 12.04 添加root账号登录
- rabbitmq的添加远程账号登录
- 使用expect脚本登录到root账号并执行命令
- 利用脚本和命令绕过系统权限添加账号
- MongoDB shell脚本 自动添加权限与账号密码
- vbscript 脚本-批量添加AD域测试账号
- C++调用Python脚本遇到的问题记录《后续还会添加 方便查阅》
- 通过VBS编写自动输入账号和密码、自动登录程序的脚本
- 百度账号登录测试
- 其他账号登录网站
- SVN修改登录账号
- 账号安全登录猜测
- 登录Google账号
- 魔力宝贝账号登录分析
- EOJ3335&&hdu6162 Ch’s gift 树剖/dfs序/离线查询/主席树,各显神通
- Paint类的其它函数
- 检测绿色征途的回调-加载镜像
- recursive and iterative ways to reverse a LinkedList
- 中兴ZXHN F412/F460/F660等破解超级密码、破解用户限制、关闭远程控制、恢复路由器拨号
- SQLServer2016--添加登录账号、后续练习脚本
- JAVA——Class装载
- Java-工具类之发送邮件
- PC验证CSR8811蓝牙芯片
- Ansible 服务器配置工具常见问题
- SQLServer-添加数据库用户
- JAVA——ClassLoader详解
- spring 注解标签总结
- [python]如何生成微信中好友签名词云