search
HomeDatabaseMysql TutorialSQL Server 应用开发(三)

SQL Server 应用开发(三)

Jun 07, 2016 pm 03:09 PM
serversqlHostapplication developmentEstablishlocalregisterRemotely

(二) 建立注册:在对远程或本地主机进行管理时,需要先进行注册 方法: 1、在选定的 SQL SERVER 组上右击,选择新建 SQL SERVER 注册,弹出相应对话框 2、在向导的可用服务器列表中选择想要进行管理的远程服务器,本地服务器可以用 . 表示,然后添加到右侧

 

(二)建立注册:在对远程或本地主机进行管理时,需要先进行注册
      方法:

      1、在选定的“
SQL SERVER 组”上右击,选择“新建SQL SERVER 注册”,弹出相应对话框
      2、在向导的可用服务器列表中选择想要进行管理的远程服务器,本地服务器可以用“
.”表示,然后添加到右侧列表中,单击“下一步”
      3、选择相应的身份验证方式,分为两种:

         A.以windows身份进行验证:即以windows系统的登录方式进行验证,在成功登录系统后,即可登录SQL SERVER

         B.以SQL SERVERWINDOWS的混合方式进行验证:即在登录操作系统后,在登录SQL SERVER时还需要相应的验证(选择这项后,“下一步”会提示输入相应的验证帐号或密码),选择完成后“下一步”

      4、在这里要选择该注册属于哪个SQL SERVER组,或创建一个新的顶层组

      5、单击“完成”,如果正确连接和验证,将成功注册。


(三)建立登录并赋予权限:

      1、建立SQL SERVER登录帐户:即进入SQLSERVER系统时需要的帐户
         方法:在企业管理器中“安全性”节点下,右击“登录”,选择“新建登录”,会出现相应对话框,具体如下:

         A.常规标签:这里可以填写帐户的名称以及相应的验证方式,验证方式同“建立注册”,还可以指定此帐户的默认登录数据库
         B.服务器角色:这里可以对该帐户在服务器中所处的角色进行设置,角色详细定义此处不做具体解释

         C.数据库访问:在这里可以定义该用户可以访问的数据库以及在被许可访问的数据库中的帐户别名,只有被做许可的数据库,才可以进行访问,帐户别名是该帐户在指定数据库中使用的名称。


      2、建立数据库用户:即定义登录帐户可以进行访问的数据库

         方法:在允许访问的数据库下,选择并右击“用户”,选择“新建数据库用户”,弹出相应对话框,选择好帐户,还可以给帐户命名在该数据库中的别名。


      3、为帐户对数据库中相关表格授与权限:

         方法:在数据库的用户列表中,选择并右击该登录,并在快捷菜单中选择“所有任务”“管理权限”,可以对相应表格授与相应的权限,只有在授权后才能进行相应操作。


六、系统数据库和示例数据库:(理解)

(一)SQL SERVER中数据库按用途分类:
      1、系统数据库:管理和维护
SQL SERVER所必需的数据库

      2、示例数据库(用户数据库):用户自己创建的数据库,示例数据库也是用户数据库,提供一些作为例子的数据库。


(二)系统数据库:

      1、Master数据库:记录所有系统级别的信息

      2、Tempdb数据库:保存所有的临时表和临时存储过程及临时生成的表

      3、Model数据库:用于在系统上创建的所有数据库的模板

      4、Msdb数据库:供SQL SERVER代理程序高度警报、作业及记录操作
(三)示例数据库:
PubsNorthwind


七、创建和管理数据库(掌握)

(一)创建数据库:

      1、数据库的文件结构:一个数据库至少要包括一个数据库文件和一个事务日志文件

         A.数据库文件(DATABASE FILE):存放数据库数据和数据库对象的文件,一个数据库可包括多个数据库文件,一个数据库文件只属于一个数据库,数据库文件可分为主数据库文件(PRIMARY DATABASE FILE)和次数据库文件,前者用于存储数据库的启动信息,扩展名为.mdf,后者扩展名为.ndf


        B.事务日志文件(TRANSACTION LOG FILE):用来记录数据库的更新情况,扩展名为.ldf,一个数据库可有多个事务日志文件。


        C.文件组(FILE GROUP):类似于文件夹,主要用于分配磁盘空间并进行管理,可分为主文件组(PRIMARY FILE GROUP)和次文件组(SECONDARY GROUP FILE



      2、在企业管理器中创建数据库:

         方法:在“数据库”上右击“新建数据库”,会弹出相应对话框

         1)“常规”选项卡:可设定数据库的名称,可为中文

         2)“数据文件”选项卡:

             A.文件名:数据库的物理文件名

             B.位置:数据库的物理位置
             C.初始大小:新建数据库时的空间
             D.文件组:所属的文件组
             E.文件自动增长:在数据增加后是否允许数据库空间进行自动增长,可分为按
%增长和按MB增长

             F.最大文件大小:在数据增长后,是否限定数据库的最大空间

         3) “事务日志”选项卡:设置同“数据文件”选项卡


(二)收缩数据库:
      当数据库数据因更新而出现太多空闲空间时,对数据库文件空间进行缩减,避免空间浪费

      方法:右击需要进行操作的数据库,选择“所有任务”“收缩数据库”


(三)移动数据库:

      当数据库文件需要在不同主机间转移时进行的操作,在移动数据库前要对主机上的数据库进行“分离”操作,“分离”后,将数据库文件直接拷贝到目标主机上,再进行“附加”数据库操作即可

      1、“分离”数据库的方法:右击需要进行操作的数据库,选择“所有任务”“分离数据库”

      2、“附加”数据库的方法:右击“数据库”,选择“所有任务”“附加数据库”,选择数据库文件的位置即可


(四)配置数据库选项:
      对数据库的相关选项进行配置,右击要进行配制的数据库,选择“属性”,选择“选项”选项卡,主要选项如下:

      1、访问限制:若选择“
DB_OWNER, DBCREATOR, SYSADMIN的成员”,则代表只有数据库所有者数据库创建者和系统管理员才有权使用数据库,若选择“单”用户,表示数据库在同一时间只能借一个用户使用。
      2、只读:只能读取,查看,不能修改。

      3、ANSI Null
默认设置:允许在数据库表中输入NULL
      4、自动关闭:当无用户使用数据库时,自动关闭数据库
自动收缩:定期检查数据库,当未用空间超过其大小的25%时,自动进行收缩操作

(五)删除数据库:
      将不使用的数据库删除,右击需要删除的数据库,选择“删除”,删除后不可恢复
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software