SQL server foundation notes

SQl server

Chapter I database foundation

1.1. Installation of SQL Server Software

(1) Preparations before installing SQL Server:

The extension is For the installation package file of iso, it is recommended to use the virtual optical drive software Daemon Tools for installation (Note: install Daemon)

Tools (select "free license").

(2) Precautions when installing SQL Server:

Function selection "minimize installation": Database Engine Service (3 sub items), client connection tool, management tool - Basic (1 sub item).

1.2. Uninstallation of SQL Server Software

To completely uninstall SqlServer2012, see the following website:

https://jingyan.baidu.com/article/e4d08ffffd69b6330fd2f60d90.html

1.3 classification of database

Generally speaking, databases are divided into two categories according to organization: relational databases and non relational databases.

At present, the mainstream relational databases include Oracle, DB2, Microsoft SQL Server, MySQL, Microsoft Access, etc.

SqlServer: developed by Microsoft.

Oracle: the world's first open commercialized relational database management system developed by Oracle and launched in 1983

Unification.

DB2: a set of relational database management system developed by IBM, which is mainly used in large-scale application systems.

MySQL:

Developed by Swedish MySQL AB company, it is currently a company under Oracle and is generally used for the development of small and medium-sized websites.

See:

(1)https://www.php.cn/sql/421899.html

(2)https://www.zhihu.com/question/432781043/answer/1604375728

Chapter II Enterprise Manager

2.1. Database files

Extension of database file:

Master database file: mdf (short for primary data fifile)

Secondary database file: ndf (short for secondary data files)

Transaction log file: ldf (short for log data files)

The main data file contains the startup information of the database and points to other files in the database. All tables, views, storage and data are saved

In this file. User data and objects can be stored in this file or in a secondary data file. Each database has a main data file.

Secondary data files are optional and are user-defined and store user data. By placing each file on a different disk drive, secondary files can be used to spread data across multiple disks. In addition, if the database is more than small, you can use secondary data files so that the database can continue to grow.

The transaction log file holds the log information used to recover the database. Each database must have at least one log file.

2.2 common operations of database

1. Offline:

Right click the database name -- > task -- > offline

2. Online:

It is an offline anti operation.

2. Separation:

Right click the database name - > Task - > detach, and check "delete connection".

Right click to "detach" the attached database.

3. Delete database:

Right click the database name -- > delete

2.3 data type of SQL Server

SqlServer common data type reference: https://www.cnblogs.com/jojoword/p/10543763.html

2.3.1 text data type

char: non unicode character of fixed length (one Chinese character takes up two bytes).

varchar: variable length non unicode characters (one Chinese character takes up two bytes).

nchar: a unicode character of fixed length (one Chinese character occupies one byte).

nvarchar: unicode characters of variable length (one Chinese character occupies one byte).

Example:

char(6): you can save 6 letters or numbers, but only 3 Chinese characters.

nchar(6): can save 6 letters, numbers or Chinese characters.

Long text type:

text:

Store variable length non Unicode data with a maximum length of 2 ^ 31-1 (2147483647) characters.

ntext:

Stores variable length Unicode data.

be careful:

nchar, nvarchar and ntext have more "N" than the previous three in terms of names. It indicates that the stored data type is Unicode

The character of the. We know that in Chinese characters, English characters only need one byte to store, but Chinese characters need two bytes to store

When Chinese characters exist at the same time, it is easy to cause confusion. Unicode character set is produced to solve the incompatibility of character set

Some characters are represented by two bytes, that is, English characters are also represented by two bytes. nchar and nvarchar are between 1 and 4000 in length

between. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese;

char and varchar can store up to 8000 English or 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, there is no need to worry about whether the input characters are English or Chinese. It is more convenient, but there is some loss in the quantity of storing English. So generally speaking,

If it contains Chinese characters, use nchar/nvarchar; if it is pure English and numbers, use char/varchar.

2.3.2 integer data type

(1).bigint (equivalent to long in C#)

Each bigint is stored in 8 bytes, of which one binary bit represents the symbol bit and the other 63 binary bits represent the length and size

Small, which can represent all integers within the range of - 2 to the 63rd power ~ 2 to the 63rd power - 1.

(2).int (equivalent to int in C#)

Int or integer. Each int is stored in 4 bytes. One binary bit represents the symbol bit and the other 31 binary bits represent the length

And size, which can represent all integers in the range from the 31st power of - 2 to the 31st power of - 1 of 2.

(3).smallint (equivalent to short in C#)

Each smallint type of data takes up two bytes of storage space, in which one binary bit represents the sign of the integer value and the others

15 bits represent length and size, and can represent all integers within the range of - 2 to the 15th power ~ 2 to the 15th power - 1.

(4).tinyint (equivalent to byte in C#)

Each tinyint type of data takes up one byte of storage space and can represent all integers in the range of 0 ~ 255

2.3.3 floating point data type

The floating-point data type stores decimal decimals and is used to represent the approximate numeric data type of floating-point numeric data. Floating point data is approximate; floating-point

The data of numerical value is stored in Sql Server in the way of rounding only, that is, if and only if the number to be rounded is a non-zero number,

Add 1 to the least significant bit of the reserved digital part, and make the necessary near bit.

(1).real: equivalent to the flfloat type in C #.

It can store positive or negative decimal values. Its storage range is -3.40E+38~-1.18E-38, 0 and 1.18E-

38~3.40E+38. Each real type of data occupies 4 bytes of storage space.

(2)flfloat[(n)]: equivalent to the double type in C #.

Where is the number of bits used to store the mantissa of flfloat value (expressed in scientific counting method), so the accuracy and storage size can be determined. If

N is specified. It must be a value between 1 and 53. The default value of n is 53

It ranges from -1.79E+308-2.23E-308, 0 and 2.23E+3081.79E-308. If you do not specify the length of the data type flfloat

Degree, which takes up 8 bytes of storage space. The flfloat data type can be written in the form of flfloat(n). N is the precision of the specified flfloat data, n

Is an integer value between 153. When n takes 124, a real type of data is actually defined, and the system uses four to store it. When n

When 25 ~ 53 is taken, the system considers it as flfloat type and stores it with 8 bytes.

(3).decimal[(p[,s])] and numeric[(p[,s]): equivalent to the decimal type in C #.

Numeric data type with fixed precision and scale. When the maximum precision is used, the valid values range from - 1038 + 1 to 1038-1.

numeric is functionally equivalent to decimal.

P (precision) specifies the total number of decimal digits that can be stored at most, including the number of digits to the left and right of the decimal point. The precision must be from

1 to the maximum precision of 38. The default precision is 18

S (scale) specifies the maximum number of decimal digits that can be stored to the right of the decimal point. The scale must be a value from 0 to p,

You can specify the number of decimal places only after specifying the precision. The default number of decimal places is 0; Therefore, 0 < = s < = P. The maximum storage size is based on precision

And change. For example, decimal(10,5) means there are 10 digits in total, including 5 integers and 5 decimals.

2.3.4 date time type

datetime: month, day, hour, minute and second millisecond

smalldatetime: mm / DD / yyyy, H / min / S

date: 2.3.5-digit data type

Bit: called bit data type, only 0 or 1 is taken as the value, and the length is 1 byte. It is used to judge whether the value is true or false

When a value other than 0 is entered, the system replaces it with 1.

Chapter III query analyzer

select*from Table name--Query all information in the table
select*from Table name where Conditional expression--Query with condition
select*(It can be replaced by a table name (column name) in a separate table from Table name where Conditional expression--Query with condition

3.1. Database building

Syntax: create database database name

Database building case:

The code to create the database StuInfo2 is as follows.

use master 

go

--If database exists StuInfo2,Delete the library 

if exists(select * from sysdatabases where name='StuInfo2') 

begin 

drop database StuInfo2 

end 

go

--Create database 

create database StuInfo2 

on primary --Master database file 

( 

name='StuInfo2', 

filename='E:\Database\SophomoreYearDB\2020 level\6 class\StuInfo2.mdf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

log on --log file 

( 

name='StuInfo2_log', 

filename='E:\Database\SophomoreYearDB\2020 level\6 class\StuInfo2_log.ldf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

go

3.2. Table building

Syntax: create table table name

Table building cases:

On the basis of the above case code, continue to write the following code:

--Use database StuInfo2 to build tables

use StuInfo2

create table StuBase 

( 

StuNo bigint not null, 

StuName nvarchar(50) not null, 

Gender bit not null, 

IdCard char(18) not null, 

Age int not null, 

Remark nvarchar(200) null 

)

go

create table Score 

(

StuNo bigint not null, 

CSharpScore float not null 

)

go

3.3 constraints

3.3.1 seven common SQL Server constraints

(1) Primary key constraint: primary key (PK)

(2) Unique constraint: unique (UQ). Multiple unique constraints can exist in a table, but the same constraint cannot exist in a unique constraint

Value.

(3) Check constraint: check (CK), a custom constraint.

(4) Default value constraint: default (DF)

(5) Foreign key constraint: foreign key (FK)

(6) Non NULL constraint: not null

(7) The system automatically identifies which value to add each time. Previous table of identity (1,1)

It starts with 1, and the latter represents an increase of 1 each time.

3.4. Add five key constraint cases:

On the basis of the above case code, continue to write the following code:

3.4.1. Add primary key constraint

alter table StuBase add constraint PK_StuNo primary key(StuNo); 

3.4.2. Add default constraint

--alter table StuBase add constraint DF_Gender default(1) for Gender; 

alter table StuBase add constraint DF_Gender default 1 for Gender; 

3.4.3. Add unique constraint (right click any column in the table design interface and select "index / key" to view the added constraint)

alter table StuBase add constraint UQ_IdCard unique(IdCard); 

3.4.4. Add check constraint

alter table StuBase add constraint CK_Age check(Age>=18); 

3.4.5 adding foreign key constraints

alter table Score add constraint FK_StuNo foreign key(StuNo) references 

StuBase(StuNo); 

3.5. Delete constraint

alter table Table name drop constraint Constraint name;

alter table StuBase drop constraint d_Gender;

Unit 4

4.1 operators in T-SQL

4.1.1 comparison operator

Operator meaning

= be equal to

> greater than

< less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

! wrong

4.1.2 logical operators

Logical expression explain Examples

AND Logical and 1 and 1 =; 1 AND 0 = 0; 0 AND 0 = 0;

OR Logical or 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;

NOT Logical non NOT 1 = 0; NOT 0 = 1;

4.2. Simple data query

4.2.1. Check all records in the table:

select * from Table name 

4.2.2 query with conditions:

select*from Table name where Conditional expression 

4.3 delete table

Syntax: drop table Table name

4.4. Insert data

Syntax: insert  into Table name (column name list) values((list of values)

Note: when inserting data, if there is a self incrementing column in the table, the value of this column will be automatically allocated by the system without the value given by the programmer.

4.5. Syntax of updating data line:

update Table name set Listing=New value where Listing=Old value

4.6. Delete data line:

delete from Table name where Row name

delete from team where name=2;

--Truncate table executes faster and uses fewer system resources and transaction logs. You cannot delete the data in the primary key table of the primary key foreign key relationship. To delete the data in the primary key table, you need to use delete.

Truncate table Team.

Truncate table Table name.

Truncate table can delete all data in the foreign key table with primary foreign key relationship.

Truncate table Player

4.7. Insert multiple pieces of data at one time:

insert
select.((inserted data).union

4.8. Multiple data lines at one time:

Copy the data in one table to another new data table, and use Select Into statement
--Copy the data rows in the Team table to Team2

select*into Team2 from Team;

4.9. Three methods to query students' student number, name and gender

--Three ways to alias query columns

select 'Student number'=StuNo,Name 'full name',(case when Gender=1 then 'male' else 'female' end) as 'Gender'
from Baselnfo ;

select  'Student number'=StuNo,'full name'=Name ,'Gender'=(case when Gender=1 then 'male' else 'female' end)  from Baselnfo;

select StuNo 'Student number',Name 'full name',(case when Gender=1 then 'male' else 'female' end) 'Gender' from Baselnfo;

select  StuNo as 'Student number',Name as 'full name',(case when Gender=1 then 'male' else 'female' end) as 'Gender'  from Baselnfo;

select Name+'-'+Idcard as'full name-ID No'
from Baselnfo;

Unit 5

5.1. Relational operators:

<>,!=

5.1.1. Logical operators:

and,or,not

5.2. Existing information in the filter table

5.2.1. Filter duplicate rows (for single column filtering)

select distinct Gender from  Baselnfo;

5.2.2. Filter duplicate rows (for multi column filtering)

select distinct Gender,Name from  Baselnfo;

select top 3 *from Baselnfo

select top 3 *from Baselnfo where Listing

5.3. Use the TOP keyword to display only the first several records

top Integer: how many records before 

top integer percent: What is the top percentage of records

5.4. Use the positive order ranking in the order by query table:

select top 2 * from StuScore where Subject='C#'  order by Score desc ;

5.5. Query links are displayed together

select cast(StuNo as varchar) +'-'+Name as'Student number-full name'
from Baselnfo;

5.6. Query all data except this:

select*from  Table name where Listing<> 'Data in column names'

select*from  Table name where Listing!='Data in column names'

5.7. How much to less data to query:

select*from StuScore where Subject='C#' and Score >= 40 and Score<=90;

5.5. Filter list operators for query results

in: used to match the list of values

not in

5.5.1. in is used to make equivalent comparison with multiple specific values, and any one can be satisfied

select*from Baselnfo where StuNo  in(20209130501);

5.5.2. not in: match with data not equal to these specific values

select*from Baselnfo where StuNo not in(20209130501,20209130503,20209130504);

5.6. Fuzzy query

Syntax: like is used with wildcards (%,, [], [^])

--Query last name'Ha'Student information
select*from Baselnfo where Name like 'Ha%';

select*from Table name where Listing like 'Name of the query';

--Query not last name'Ha'Student information
select*from Baselnfo where Name not like 'Ha%';

select*from Table name where Listing not like 'Query word';

--The query name has only two words, and the second word is not'Ha'My classmate
select*from Baselnfo where Name like'_[^Ha]';

select*from Table name where Listing not like 'Query word';-Underscores represent one byte

5.8. The order by sentence can be sorted according to multiple fields

--Query the score information, sort the C# scores in descending order, and sort the same scores in ascending order by student number

select  * from StuScore where Subject='SQlSever'  order by Score desc, StuNo asc;--Descending order
select  * from StuScore where Subject='SQlSever'  order by Score desc ;--Descending order

select  * from Table name where Listing='Data added in column'  order by Listing desc ;--Descending order

5.9 aggregation function

SUM(): used to count the sum of numeric fields. It can only be used for numeric fields, and NULL values will be ignored.

AVG(): used to calculate the average value of a numeric field. The NULL value in this field will be ignored in the calculation process.

MAX(): used to return the maximum value of the field, which will be ignored when NULL value is encountered during calculation.

MIN(): used to return the minimum value of the field, which will be ignored when NULL value is encountered during calculation.

COUNT(): counts the number of record lines.

5.9.1 total score

select 'Total score'=SUM(Score) from StuScore;

select 'Total score'=SUM(Listing) from Table name;

5.9.2 calculate the total score of each course (each, each)

--In a query statement, you can use any number of fields to group the records in the result field. Each output field in the field list must appear in the GROUP BY sentence or be used in an aggregate function.

select Subject 'subject',SUM(Score)'Total score' from StuScore group by Subject;

select Listing'subject',SUM(Listing)'Total score' from Table name group by Listing;

5.9.3 calculate the average score of each section and arrange it in descending order

select Subject 'subject',AVG(Score)'average' from StuScore group by Subject order by AVG(Score) desc;

5.9.4 seek the highest score of each subject

select Subject 'subject',MAX(Score)'Highest score' from StuScore group by Subject;

5.9.5 seek the lowest score of each subject

select Subject 'subject',MIN(Score)'Lowest score' from StuScore group by Subject;

5.9.6 reference number of each section

select Subject 'subject',count(*)'Number of people actually taking the exam' from StuScore group by Subject;

5.10. HAVING clause: filtering

select Subject 'subject',AVG(Score)'average>=70' from StuScore group by Subject having AVG(Score)>=70 order by AVG(Score) desc;

Chapter VI

If a query needs to operate on multiple tables, it is called join query. The result set or result table of join query is called join between tables

Connect.

Join query is divided into internal join query, self join query, external join query and compound conditional join query.

6.1. Internal (internal) links

select bi.StuNo,Name,Gender,Age,Idcard,Subject,Score
from Baselnfo bi inner join StuScore ss on bi.StuNo= ss.StuNo

--The two table names are separated by commas, and the where clause is used to write the associated fields

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi inner join StuScore ss
on bi.StuNo=ss.StuNo

6.2. External connection

6.2.1. Left [outer] join

--Features: the table on the left of the left join is the master table, and the table on the right is the slave table. All records of all records in the master table are preferentially displayed. If there is no matching record in the slave table, it is filled with "NULL".

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi left join StuScore ss
on bi.StuNo=ss.StuNo

6.2.2 right [outer] join

--Features: the table on the right side of the right join is the master table, and the table on the left is the slave table. All records of all records in the master table are preferentially displayed. If there is no matching record in the slave table, it is filled with "NULL".

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi right join StuScore ss on bi.StuNo=ss.StuNo;

6.3. Cross [outer] join

--Features: the number of records in the query result set is equal to the product of the number of records in the left and right tables of the cross join.

select count(*)
from Baselnfo cross join StuScore

select count(*)from Baselnfo--9
select count(*) from StuScore--7

6.4. Self connection

Joins can be made not only between tables, but also between a table and itself. This kind of join is called self join, and the corresponding query is called self join

Is a self join query.

6.5. More complex join query

SELECT Field list FROM Table 1 INNER JOIN Table 2 ON Conditional expression 1 INNER JOIN Table 3 ON Conditional expression 2 

6.6 set operation

Union and union all -- union operation

intersect -- intersection operation

except -- subtraction

6.6.1 UNION and UNION ALL are used for UNION operation

UNION and UNION ALL combine the two sets of queries. The only difference is that UNION does not allow duplicate columns

Duplicate columns are allowed for ALL.

The title of the result set returned by UNION is obtained only from the first query.

6.6.2. Use INTERSECT for intersection operation

Intersection also operates on multiple result sets. Intersection takes out the same records in the two result sets to form a new set.

6.6.3. Use Excel to perform subtraction operation

Subtractive set refers to a new set formed by comparing two result sets and removing the intersection part of the result set before the excel keyword.

Note: the final result of subtraction is closely related to the order of the two result sets.

Microsoft Visio 2010 software

Posted by daprezjer on Sat, 14 May 2022 08:28:50 +0300