Basic SQL Server tutorial [warm knowledge new three]

Source: Internet
Author: User
Tags abs add time mathematical functions square root time and date sql server tutorial

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.

vWrite 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.

vBasic 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 Foundation

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 tinying 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 tables

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 queries

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

vSystem functions

1. Statistical (aggregation) functions

    • SUM () sum
    • AVG () Average
    • Max () max value
    • Min () Min value
    • Count () Quantity

2. Date function

  • GetDate () Gets the current time
  • Dateadd () Add time
  • DateDiff (datepart,startdate,enddate)

    startdate and enddate parameters are valid date expressions. The

    datepart parameter can be a value of the following:

    Datepar T 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
    seconds 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
    • About Abs ()/round ()/... The demo of these math functions can be found in my previous blog post.

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
vView

1. Create a View

Create View View name  as query statements in SQL

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]

vStored procedures/triggers/transactions

1.sql Server Stored Procedures

 create  proc  |  procedure   Pro_name  [ {@ parameter data type} [= default value   output     } [ = 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.

Basic SQL Server tutorial [warm knowledge new three]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.