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
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]
