SQL Server Usage Brief example

The son said: "Wen so know new, can for the teacher." Confucius said: "Brush up on old knowledge to learn new understanding and experience, with this point can become a teacher." "In particular, we have a program, whether it is a full stack of engineers, are set terrorize in one." But sometimes some knowledge if there is a long time useless, will forget, even forget you do not remember, especially some basic things. So I'm going to write a "warm so know new" series of blog post out, one of these basic things I am relatively forgetful, later to facilitate their own page, and the hope can help some of the newly-started friends. All of the knowledge points recorded in this series are the most (important things to say three times) basic knowledge. Most of the notes I have accumulated while studying.

temperature so that the new series are some basic knowledge, the great God can skip directly.

v written in front

If the terrorize are all mastery, if any weapon you are playing with a kind of model, then this blog post you can skip. Just when you forget, you can take it out and brush it up.

v Basic Concepts

SQL Server is a relational database management system launched by Microsoft Corporation. With ease of use scalability and the high degree of integration of related software, you can span multiple platforms, from laptops running Microsoft Windows 98 to large multiprocessor servers running Microsoft Windows 2012. Microsoft SQL Server is a comprehensive database platform that provides enterprise-class data management with integrated Business Intelligence (BI) tools. The Microsoft SQL Server Database engine provides more secure and reliable storage capabilities for relational and structured data, enabling you to build and manage high-availability and high-performance data applications for your business.

Simple one sentence summary: The data is a certain meaning of numbers, letters, symbols collectively, the database is the storage of data

vsql (structured Query Language) Server Basics

The composition of the 1.SQL server:

• Main database files:. MDF features: There is only one
• Secondary database files:. NDF features: any of
• Log database file:. LDF features: At least one

2. Operation Database:

• Creating a database: Create DATABSE library name
• View all databases: Exec sp_helpdb
• View current database: Exec sp_helpdb library name
• Working with databases: use library names
• Delete database: Drop Library name PS: The database being used cannot be deleted

3. Structure of the table: field Data Type (properties)

4. Data type:

• Integral type
  • Micro-integer tinyint 1 bytes
  • Small integer smallint 2 bytes
  • Integer int 4 bytes
  • Large integer bigint 8 bytes
• Floating point Type
  • Float cannot store values accurately
  • Real cannot store values accurately
  • Decimal (numeric) synonymous for precise storage of numeric values
• Character type
  • Char length between 1 and 8000 fixed long character data
  • varchar length variable long character data from 1 to 8000
  • Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters
• Time and Date type: DateTime month day seconds minute millisecond
• Currency type: Money is accurate to 10 per thousand of the currency unit. The storage size is 8 bytes. stored in the form of 12345.67

vsql Server Table

1. Syntax for creating tables:

CREATE TABLE table name (    field name 1 data type [properties],    field name 2 data type [properties],    ...  )

2. View all table syntax: exec sp_help

3. View the current table syntax: exec sp_help table name

4. Modify the table structure:

• Add a column syntax: ALTER TABLE name add field name data type
• Delete a column syntax: ALTER TABLE table name drop column field name
• Modify a column syntax: ALTER TABLE name ALTER COLUMN field name data type
• Delete Table syntax: drop TABLE table name

5. Operation Table Data:

• Full Insert data syntax: INSERT into table name (field name 1, field name 2 ...) values (value 1, value 2 ...)
• Omit Insert data syntax: INSERT into table name values (value 1, value 2 ...)
• Section Insert Data syntax: INSERT into table name (field name 1, field name 2 ...) values (value 1, value 2 ...)
• MultiRow Insert Data syntax: INSERT into table name values (value 1, value 2 ...), (value 1, value 2 ...)
• View all record syntax: SELECT * FROM table name
• View partial record syntax: Select field name 1, field Name 2 ... from table name
• Modify a record syntax: Update table name set field name = value [WHERE Condition]
• Delete a record syntax: Delete from table name where condition
• Empty table All records
  • Delete from table name
  • Truncate from table name PS: As for their differences, I will not introduce more, interested can see here SQL Server truncate, delete and drop similarities and differences

6. Identity column identity (initial value, add value):

• Definition: can uniquely differentiate each record in a table, and the attribute is automatically grown
• Characteristics
  • There is only one identity column in a table
  • Identity column cannot be edited and cannot be updated
  • Identity column data type can only be integral type
  • Identity column is not NULL
  • Identity column is not duplicated
• Role: Ensure data integrity

7. Operators:

SQL Server operators differ from other languages by listing three

• &&-----and
• || -----or
• !-----not

8. Six major constraints:

• Check constraint check syntax check (condition)
• Default constraint defaults syntax default ' defaults ' statement
• nonempty constraint NOT NULL
• Uniqueness Constraint Unique
• Primary KEY Primary Key
  • The value of the primary key cannot be duplicated
  • There is only one primary key in a table
  • Foreign keys correspond to primary keys
  • The primary key type can be integer, character type
  • Syntax: Field name data type primary key
• Foreign key foreign key references
  • Corresponds to the primary key
  • The value of the foreign key must be within the primary key range
  • The value of the foreign key can be repeated
  • Syntax: Field name data type foreign key references main Table name
  • Precautions:
    • When you manipulate the main table from the table: delete the main table from the table, delete the data, and first
    • When creating a table: Create a primary table, create a table, insert data from it, and Guthrie

vsql Server Query

1. Query for eligible data: Select field name from table name [where condition]

2. Between the

• And ... or
• Between ... and
• Example: Search for students between 23 and 25 years old
  • SELECT * FROM student where age>=23 and age<=25
  • SELECT * from student where age between and 25

3. Do not display duplicates: Distinct select distinct field name from table name

4. Top SELECT top N * FROM table name

5. Sort order by + field name ASC Ascending desc Descending (ascending by default) select * FROM student where age>25 order by name Desc

6. is isn't null/null select * from table name where field name is null

7. Listing aliases as select ID as student number, name as student name from student as a note: As can be omitted from the actual syntax

8. Advanced Query (fuzzy query) like select field name from table name where field name like ' wildcard value wildcard '

SQL Server wildcard characters

• % any character
• [] Any character in the range
• [^] Any character not in range

9. Joint query Join

• Cross-query: Select field name from Table 1 crosstab join table 2 [WHERE Condition]
• Internal connection query: Select field name from Table 1 inner JOIN table 2 on Union condition [WHERE condition]
• External connection
  • Left OUTER join: Select field name from Table 1 left JOIN table 2 on Union condition [WHERE condition]
  • Right outer join: Select field name from Table 1 right join table 2 on Union condition [WHERE condition]
• Full outer join: Select field name from Table 1 full join table 2 on Union condition [WHERE condition]
• Multi-table Connection: Select field name from Table 1 inner JOIN table 2 on federated condition INNER join table 3 on Union condition [WHERE condition]

10. Nested queries (all nested queries can be completed with a federated query), the fields displayed in one table, and the conditions in another table

• In () ... Within the range of
• Not in () ... Within the range of
• exists exists
• Not exists does not exist

11. GROUP BY

v system functions

1. Statistical (aggregation) functions

The Transact-SQL programming language provides the following aggregate functions: AVG returns the average in the specified group, and the null value is ignored. Example: Select Prd_no,avg (qty) from the Sales group by Prd_no2. Count returns the number of items in the specified group. Example: SELECT count (prd_no) from Sales3. Max returns the maximum value for the specified data. Example: Select Prd_no,max (qty) from the Sales group by Prd_no4. Min Returns the minimum value for the specified data. Example: Select Prd_no,min (qty) from the Sales group by Prd_no5. The sum of the specified data is returned, and can only be used for numeric columns, and null values are ignored. Example: Select Prd_no,sum (qty) from the Sales group by Prd_no6. COUNT_BIG returns the number of items in the specified group, unlike the Count function, where COUNT_BIG returns the bigint value, and Count returns an int value. Example: Select COUNT_BIG (prd_no) from Sales7. GROUPING produces an additional column that, when added with the cube or rollup operator, outputs a value of 1. The output value is 0 when the added row is not generated by cube or Rollup. Example: Select Prd_no,sum (Qty), GROUPING (PRD _NO) from the sales group by Prd_no with Rollup8. Binary_checksum returns the binary checksum value computed for a list of rows or expressions in a table to detect changes in rows in a table. Example: Select Prd_no,binary_checksum (qty) from the Sales group by Prd_no9. Checksum_agg returns the checksum value for the specified data, and the null value is ignored. Example: Select Prd_no,checksum_agg (binary_checksum (*)) from the sales group by PRD_NO10. CHECKSUM returns a checksum value computed on the row of a table or on an expression list, used to generate a hash index. STDEV returns the statistical standard deviation of all values in the given expression. Example: Select Stdev (prd_no) from SALES12. STDEVP returns the fill statistic standard deviation for all values in the given expression. Cases: Select STDEVP (prd_no) from Sales13. VAR returns the statistical variance of all values in the given expression. Example: Select VAR (prd_no) from SALES14. VARP returns the statistical variance of the fill for all values in the given expression. Example: Select VarP (prd_no) from sales

2. Date function

• GetDate () Gets the current time
• Dateadd () Add time

• DateDiff (datepart,startdate,enddate)

  startdate   and   enddate   The argument is a valid date expression. The

  datepart   parameter can be the following values:

  tr>

  datepart	Abbreviation
  year	yy, yyyy
  Quarterly	QQ, q
  Month	mm, M
  Year of the day	dy, y
  Day	DD, D
  week	wk, ww
  week	DW, W
  hours	hh
  minutes	mi, n
  sec	SS, S
  milliseconds	ms
  subtle	MCs
  nanoseconds	ns

• The DATEPART (datepart,date) function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on, DATEPARTD parameters can be found in the table above
• Datename (datepart,date) returns a character string representing the specified datepart of the specified date

3. Mathematical functions

• ABS () take absolute value
• Round () rounding
• The floor () function returns the largest integer less than or equal to the given number expression
• The ceiling () function returns the smallest integer greater than or equal to the given number expression
• sqrt () Open square root

4. String functions

• Left () Right intercept string
• Right () Intercept string
• LTrim () go left space
• RTrim () go to right space
• Replace (string, old string, new string)
• substring (string, position, length) truncated string Ps:sql in string subscript starting from 1
• Reverse () reversal
• Len () length
• Upper () Turn capital
• Lower () Turn lowercase

Vt-sql

1. Declaring variable syntax: DECLARE @ variable name data type

Assigning values to variables

• SET @ variable name = value
• SELECT @ Variable name = value

Output variable SELECT @ variable name

PS: If you want to output variables, declare variables, variable assignments, and variable output three statements need to be executed together

2. Programming statements

• Begin...end
• If...else

Back to Topv View

1. Create a View

CREATE VIEW name Assql query statement

2. Use the view select * from view name

3. View exec sp_help

4. View the contents of the view Exec sp_helptext view name

5. Modify a view alter view view name as SELECT * from table name [where condition]

6. Drop View Name

7. Modify the View Update view name set field name = value [WHERE Condition]

v stored procedures/triggers/transactions

1.sql Server Stored Procedures

create proc | Procedure Pro_name    [{@ parameter data type} [= default] [output],     {@ parameter data type} [= default] [Output], ....    ] As        Select ...

2.sql server triggers

• Insert Trigger
• UPDATE trigger
• Delete Trigger
• About SQL Server triggers there's not much to be introduced here, more details we're interested to see here.

SQL Server Usage Brief example