Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the relevant knowledge points of MSSQL review". In daily operation, I believe that many people have doubts about the relevant knowledge points of MSSQL review. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the relevant knowledge points of MSSQL review?" Next, please follow the editor to study!

2.-- determine whether the database file already exists: the database records are stored in the sysdatabases table in the master library-- automatically switch the current database-- use the code to open the perimeter should configure the exec sp_configure 'show advanced options', 1 RECONFIGURE exec sp_configure' xp_cmdshell'. 1 RECONFIGURE-Custom directory xp_cmdshell can create the directory 'mkdir f:\ project':' specify the creation directory exec xp_cmdshell 'mkdir f:\ project' use master-- the exists function determines whether the query statement in () returns a result set If you return a result set, you get true, otherwise you get false if exists (select * from sysdatabases where name='School') drop database School-delete the database create database School on primary (name='School_data',-- logical name) with the current specified name. It explains that you can store 100mb data at most, and if there is no limit, you can store the hard disk full of size=3mb,-- initial size maxsize=100mb,-- maximum capacity filegrowth=10%,-- files increase by 10% filename='f:\ project\ School_data.mdf'),-- create filegroup filegroup mygroup (name='School_data1',-- logical name. Describes the ability to store up to 100mb data If there is no limit, the hard disk can be stored at full size=3mb,-- initial size maxsize=100mb,-- maximum capacity filegrowth=10%,-- file growth 10% filename='F:\ qiyi\ School_data1.ndf') log on (name='School_log',-- logical name size=3mb,-- initial size-maxsize=100mb,-- maximum capacity filegrowth=10%,-- file growth 10% filename='f:\ project\ School_log.ldf') (name='School_log1',-- logical name size=3mb,-- initial size-- maxsize=100mb,-- maximum capacity filegrowth=10%,-- file growth by 10% filename='F:\ qiyi\ School_log1.ldf') 3. Create a datasheet

Syntax:

Create table table name

(

Field name Field Type Field characteristics (whether it is null, default value identifies column primary key unique key foreign key check constraint)

Field name Field Type Field characteristics (whether it is null, default value identifies column primary key unique key foreign key check constraint)

)

Create teacher table Teacher: Id, Name, Gender, Age, Salary, Birthday

Use School-inserts data from all fields of teacher. If no field names to be inserted are specified after the table, all fields are added values by default-- but it is important to note that identity columns can never be custom values-- values cannot be inserted artificially-- explicit values can be specified for identity columns in the table 'Teacher' only if the column list is used and the IDENTITY_INSERT is ON. Insert into Teacher values ('Zhang San', 5 null 1, 30 Name,ClassId,Gender,Age,Salary,Birthday 4000) insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values ('Zhang San', 5 Magi 1) 30 Name,ClassId,Gender,Age,Salary,Birthday 4 000 null fields may not be assigned values-column names or the number of values provided do not match the table definition insert into Teacher (Name,ClassId,Gender,Age,Salary) values (Li Si, 51) 30pr 4000)-non-empty fields must be assigned: the value NULL cannot be inserted into column 'Gender' Table 'School.dbo.Teacher' Columns are not allowed to have Null values. INSERT failed insert into Teacher (Name,ClassId,Age,Salary) values ('Li Si', 5Jing 30pm 4000)-insert values for fields with default values:-- 1. If you do not write this column, let the system automatically assign insert into Teacher (Name,ClassId,Gender,Age) values ('Wang Wu', 5 Name,ClassId,Gender,Age,Salary,Birthday 1pje 30)-- specify either null or default insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values ('Zhao Liu', 5 meme 1) values ('Zhao Liu 1', 5 Name,ClassId,Gender,Age,Salary,Birthday) values ('Zhao Liu 1', 5jue 1, 300 penalty default)-the data must be in full compliance with the table integrity constraint Null)-- any type of data can be included in'', excluding the keyword insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values ('Ma Pengfei','5 'pengfei','5 'pengfei')-- but if the string value is not included in''. Will report the wrong name 'Lanpeng' is invalid. Insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values-- but numeric strings may not use''including insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values)-- date values must be included within''. Otherwise, it is the default value of insert into Teacher (Name,ClassId,Gender,Age,Salary,Birthday) values ('Zou Yuanbiao 2' 9-11')

Syntax:

Delete [from] Table name where condition

The role of select-1. Query-- 2. Output select 1-+ is the operator, the system will automatically convert the type for you select 1-- if both sides of the + are strings, then it is a string concatenator select'1-- then it is a string concatenator-- it can output multi-column values select, select, 34, Top, Distinct select * from Student-- top can get the specified number of records, and the value can be greater than the total number of records. But it cannot be negative select top 100 * from Student-the percentage is ceiling () select top 10 percent * from Student-duplicate records are not related to the original data table data, but only related to the result set of your query distinct can remove duplicate records in the result set-- the value of each column in the result set is the same select distinct LoginPwd,Sex,Email from Student select distinct Sex from Student

4. The function of select

Aggregate function:-- 1. Filter null-- 2. All need to have a parameter-- 3. All return a numeric value-sum (): summation: invalid for strings and dates only for numeric values-avg (): average-count (): count: get the number of records that meet the criteria-max (): maximum: any type of data can be aggregated Sort the phonetic alphabet if it is a string-min (): minimum-get the total number of students select COUNT (*) from Student-query maximum age select MIN (BornDate) from Student select max (BornDate) from Student-query total score select SUM (StudentResult) from Result where StudentNo=2-average score select avg (StudentResult) from Result where SubjectId=1-pay attention to details: select SUM (StudentName) from Student select SUM (BornDate) from Student select min (StudentName) from Student select max (StudentName) from Student-query the student number Name, sex, age, telephone number, address-inquire about girls' select StudentNo,StudentName,Sex,BornDate,Address from Student where Sex=' female 'and BornDate >' 1990-1-1 'and Address=' Guangzhou Chuanzhi podcast'-- specified range select StudentNo,StudentName,Sex,BornDate,Address from Student where BornDate > = '1990-1-1' and BornDate= = 2-1. Aggregation should not appear in the WHERE clause-syntax error select ClassId, COUNT (*) as num from Student where Email is not null GROUP by ClassId having COUNT (*) > = 2 order by num desc-complete sql query family-5 1 2 3 46-select field list from table list where data source filter group by grouping field list having grouping result set screening Order by do record rearrangement select ClassId for result set COUNT (*) as num from Student where Email is not null GROUP by ClassId order by ClassId desc-- sort the execution order of top and then take the top value select top 1 ClassId, COUNT (*) as num from Student GROUP by ClassId order by num desc7. Type conversion function-select: output to result set-virtual table-print: output in text form can only output one string value. Print 1 formatted data select 1 select * from Student-- Type conversion-- Convert (target type, source data, [format])-- date formatted print'my score is:'+ convert (char (3), 100) print 'Today is a big day:' + convert (varchar (30), getdate ()) Select getdate () select len (getdate ())-- cast (source data as target type) it has no format print'my score is:'+ cast (100 as char (3)) 8. Date function-- getdate (): get the current server date select GETDATE ()-- the number of dates that can be appended to a specified time interval when the source date value is select DATEADD (dd,-90,GETDATE ())-- dateDiff: find the difference between two dates in the specified format select StudentName,DATEDIFF (yyyy,getdate (), BornDate) as age from Student order by age-- DATENAME: you can get the string representation of the date in the specified format select DATENAME (dw) Getdate ()-- DATEPART: you can get the specified date part select cast (DATEPART (yyyy,getdate ()) as CHAR (4)) +'-'+ cast (DATEPART (mm,getdate ()) as CHAR (2)) +'-'+ cast (DATEPART (dd,getdate ()) as CHAR (2)) 9. Mathematical function-- rand: random number: returns a number between 0 and 1. Theoretically, it can return 0 but not 1 select RAND ()-- abs:absolute: take the absolute value select ABS (- 100)-- ceiling: get the smallest integer select CEILING (1.00)-- floor: get the maximum integer select floor (1.99999) power: select POWER (3) 4)-- round (): rounded. Only focus on one bit after the specified number of digits select ROUND (1.549)-- sign: positive = = 1 negative = =-10 = 0 select SIGN (- 100) select ceiling (17 / 10 / 1.0) 10. The string function-- 1.CHARINDEX-- IndexOf (): returns the starting position of a string at the source string. Returns 0 if it cannot be found, and returns an index starting at 1 if it can be found-- there is no concept of an array-- the first parameter refers to the string to be queried, and the second is the source string. The third parameter refers to the search for select CHARINDEX ('people', 'people of the people's Republic of China' from the index position of the source character 4)-- LEN (): you can return the number of characters in the specified string select LEN ('people's Republic of China')-- UPPER (): convert lowercase letters to uppercase letters LOWER (): convert uppercase to lowercase select LOWER (UPPER ('sadfasdfa'))-- LTRIM: remove the left space RTIRM: remove the right space select lTRIM (RTRIM (' sdfsd')) +'a'-RIGHT: it can be opened from the right side of the string. Start by intercepting a string with a specified number of digits if the value goes out of range No error will be reported, only all string values will be returned, but it cannot be a negative value select RIGHT ('people's Republic of China', 40) select LEFT ('people's Republic of China', 2)-- SUBSTRING () select SUBSTRING ('people's Republic of China', 310)-- the first parameter of REPLACE is the source string, and the second parameter is the string to be replaced. The third parameter is why you need to replace select REPLACE ('people's Republic of China', 'people', 'resident') select REPLACE ('people's Republic of China', 'people's Republic of China',')-- STUFF: start the source string from A total of several strings have been replaced with the specified string select STUFF ('people's Republic of China', 3pjing2dai 'you know')-sudyfsagfyas@12fasdf6.fsadfdsaf declare @ email varchar (50) = 'sudyfsagfyas@12fasdf6.fsadfdsaf' select CHARINDEX (' @', @ email) select LEFT (@ email,CHARINDEX ('@', @ email)-1)-using right select right (@ email,len (@ email)-CHARINDEX ('@', @ email))-- using substring select SUBSTRING (@ email) CHARINDEX ('@', @ email) + 1 email Len (@ email)-use stuff select STUFF (@ email,1,CHARINDEX ('@', @ email),'') 11. Joint result set union-- joint result set union select * from Student where Sex=' male'--union select * from Student where Sex=' female'--the premise of joint result set is-- 1. The number of columns needs to be consistent: all queries that match using UNION, INTERSECT, or EXCEPT operations must have the same number of expressions in their target list-- 2. The types of columns need to be able to convert select StudentName,Sex from Student to each other-- when sorting strings, the space is the smallest, the front union select cast (ClassId as CHAR (3)), classname from grade-- the difference between union and union all-- union removes duplicate records-- union all does not remove duplicate records: it is more efficient, because there is no need to determine whether the records are duplicated, and there is no need to perform the operation of removing duplicate records in the result. But you can consume more memory space select * from Student where ClassId=2 union all select * from Student where ClassId=2-- query the scores of all students in the office subject, and show its average score, the highest score at the end. The lowest score is select''+ cast (StudentNo as CHAR (3)), cast (SubjectId as CHAR (2)), StudentResult from Result where SubjectId=1 union select'1 'average score', AVG (StudentResult) from Result where SubjectId=1 union select'1 'average score', max (StudentResult) from Result where SubjectId=1 union select'1 'lowest score', min (StudentResult) from Result where SubjectId=1-insert multiple pieces of data at once-1. First copy the data to another new table, delete the source table, and then insert the data from the new table into the source table-- 1.select * / field into new table from source table-- 1. The new table is automatically generated by the system and cannot be created artificially. If the new table name already exists, an error will be reported-- 2. The table structure of the new table is the same as the column obtained by the query statement, but the properties of the column disappear, leaving only the non-empty and identity columns. All others disappear, such as primary key, unique key, relation, constraint, default value select * into newGrade from grade truncate table grade select * from newGrade-- select * into grade from newGrade-- 2.insert into target table select field list / * from data source table-- 1, target table must exist first, if not, error-2. The data queried must conform to the data integrity of the target table-3. The number and type of data columns queried the number and objects of the target columns must correspond exactly to the insert into grade select classname from newGrade delete from admin-- insert multiple records at once using union-- insert into table (field list)-- select values. User defined data-- union-- select value. The usage of the insert into Admin select 'afewnul12.case function in the use of the insert into Admin select 'a'union all select 'aformaitorialnull12.case function.

The equivalent of switch...case in switch case---c# can only make an equivalent judgment.

This determines the field value or expression and returns a user-defined value that generates a new column.

two。 The type of data behind then is required to be consistent.

1. The first kind of case..end that makes equivalence judgment

Case field or expression

When. Worth.. then. Custom value

When. Worth.. then. Custom value

.

Else satisfies the else if it doesn't satisfy all the when above.

End

-- display the name of a specific class select StudentNo,StudentName, case ClassId-- if case is followed by an expression or field, then this structure can only make an equivalent judgment, which is really equivalent to switch..case when 1 then, Class 2, 'when 2 then,' Class 2, 'when 3 then,' Class 3, 'when null then' aa'-- can not judge a null value else 'unclear' end,sex from Student-- 2. Do range judgment, which is equivalent to if..else, it can do null value judgment-- case-- if there is no expression or field, it can achieve range judgment-- when expression then value-- does not require expression to judge the same field-- when expression then value--. -- else other cases-- end select StudentNo,StudentName Case when BornDate > '2000-1-1' then 'kid' when BornDate > '1990-1-1' then 'Youth' when BornDate > '1980-1-1' then 'Youth'-when Sex=' female 'then' is a female 'when BornDate is null then' born unknown 'else' middle-aged end from Student-percentile system converted to quality education 90-A 80 then B 70-- C 60-- D = 90 then'A' when StudentResult > = 80 then'B' when StudentResult > = 70 then'C'when StudentResult > = 60 then 'D'when StudentResult is null then' did not take the exam 'else' E'end score ExamDate from Result13.IF ELSE syntax

1,. No {}, use begin..end. If there is only one sentence after it, you can include it without using begin..end

two。 There is no Bool value, you can only use relational operator expressions

3. Can also be nested and multiple

The () after 4.if can be omitted

Declare @ subjectname nvarchar (50) = 'office'-subject name declare @ subjectId int= (select Subjectid from Subject where SubjectName=@subjectname)-subject ID declare @ avg int-average score set @ avg= (select AVG (StudentResult) from Result where SubjectId=@subjectId and StudentResult is not null)-get average score print @ avg if @ avg > = 60 begin print' score is good, output of the top three: 'select top 3 * from Result where SubjectId=@subjectId order by StudentResult desc end else begin print' is not good Output the last three: 'select top 3 * from Result where SubjectId=@subjectId order by StudentResult end14.WHILE loop syntax

No {}, use begin..end

There is no Bool value, you need to use a conditional expression

Can be nested

You can also use break,continue

Go declare @ subjectName nvarchar (50) = 'office'-- subject name declare @ subjectId int-- subject ID declare @ classid int = (select classid from Subject where SubjectName=@subjectName)-- query which class the current subject belongs to set @ subjectId= (select SubjectId from Subject where SubjectName=@subjectName)-- get the total number of subjects ID declare @ totalCount int--: which class needs to take the course set @ totalCount= (select COUNT (*) from Student where ClassId=@classid) ) print @ totalcount-- 14 declare @ unpassNum int-- set @ unpassNum= (select COUNT (distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in (select StudentNo from Student where ClassId=@classid) and StudentResult@totalCount/2) begin-- perform round robin points update Result set StudentResult+=2 where SubjectId=@subjectId and StudentNo in (select StudentNo from Student where ClassId=@classid) and StudentResult (@ pageindex-1) * @ pagecount and temp.id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.