Category:MsSql

  • Detailed explanation of SQL Server building website

    Time:2020-9-26

    Environment: one SQL Server 2008 R2, one web serverSoftware support: IIS, dynamic website source codeTopology: Steps: Database part: 1. Start – click SQL Server Management Studio 2. Enter the name or IP address, login name and password of SQL server server – click link database 3. Create a new database 4. Enter new database name […]

  • Usage of sqlserver as

    Time:2020-9-26

    SQL statements can specify aliases for table or column names. (aliases are sometimes referred to as export columns. Whether they are export columns or aliases, they represent the same thing.) SQL aliasSQL aliases are used to provide temporary names for tables or columns in tables.  SQL aliases are often used to make table or column […]

  • Introduction to the use of with as in SQL Server

    Time:2020-9-25

    1、 The meaning of with as With as phrase, also known as subquery factoring, allows you to do a lot of things and define a SQL fragment that will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of union […]

  • SQL lese dry goods notes

    Time:2020-9-25

    Because the company is basically using stored procedures, so the original dry goods are basically stored procedures.   SELECT TOP 1 Code,Invitation,Num,Typ FROM SignLog WITH(NOLOCK) WHERE [email protected] AND TaskId=2 AND AddTime>[email protected] AND AddTime<@etime Let’s use the above statement for an example Query must specify the field, even if you want to check all the fields, do […]

  • Why SQL Server2008 order by cannot be used directly in union clause

    Time:2020-9-25

    According to the requirements, each takes top 20. Since it is random, SQL Server order by newid() is used, and then all the data are unionized. So I immediately gave the answer: selecttop 20 *fromxxxwheretype=1orderbynewid() union selecttop 20 *fromxxxwheretype=0orderbynewid() But the SQL query analyzer is wrong and the syntax is wrong. At first glance, it […]

  • SQL join query: the difference between left join, right join, inner join and full join

    Time:2020-9-24

    reference resources: https://www.cnblogs.com/lijingran/p/9001302.html https://www.cnblogs.com/assasion/p/7768931.html   Because simply select * from a, B is a Cartesian product. For example, table a has 5 pieces of data and table B has 3 pieces of data, then the final result is 5 * 3 = 15 pieces of data. Therefore, two tables need to be associated: select * […]

  • SQL Server 2008 Express + Management Studio Express for the first time

    Time:2020-9-24

    Because it needs to develop a small CMS system by local test, the following SQL Server 2008 can install the following files. If the enterprise version is officially recommended for installation. Download address: SQL Server 2008 Express Chinese simplified installation package x86: https://download.microsoft.com/download/5/f/a/5fad1d35-e69e-448b-84dc-497061772141/SQLEXPR_x86_CHS.exe x64: https://download.microsoft.com/download/5/f/a/5fad1d35-e69e-448b-84dc-497061772141/SQLEXPR_x64_CHS.exe SQL Server 2008 Management Studio Express Chinese simplified installation package […]

  • SQL server sets that users can only view and access specific databases

    Time:2020-9-23

    Now it is necessary to restrict specific users to view and access specific databases, so as to prevent some misoperations when multiple users operate on databases.  Refer to i6first’s blog on how to let users only access a specific database (MSSQL) 1. Create a new login userLog in to the database as an administrator (the […]

  • SQL Server 2008 Express and Management Studio Express download installation configuration tutorial

    Time:2020-9-23

    This article mainly talks about how to download, install, configure SQL Server 2008 Express and SMSs step by step, and finally use SMSs to connect to local database services. Why write this article? In the first database principle class of this semester, the lecturer shared the materials needed for the course, including a 3.1gb SQL […]

  • SQL server queries tree structure through with as method

    Time:2020-9-23

    1、 With as common table expression It is similar to view, but does not create an object. With as common table expression does not create an object and can only be followed by a select statement 1. Realize recursive query (tree structure) 2. The common table expression can be referenced multiple times in a statement […]

  • Some suggestions on improving query efficiency by SQL

    Time:2020-9-22

    1. If you want to use subqueries, use exists instead of in and not exists to replace not in. Because the subquery introduced by exists only tests whether there are rows that meet the specified conditions in the subquery, which is more efficient. In either case, not in is the most inefficient. Because it does […]

  • Usage of declare variable in sqlserver

    Time:2020-9-22

    Declare usage in SQL I usually write SQL queries and stored procedures by feeling. I haven’t explored the specific syntax of SQL. I always imitate SQL according to C ා. A problem encountered in the project a few days ago has aroused my interest in the scope of declare defined variables. We all know the […]