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