The database of those years of shawl chopping

database, ubiquitous stuff. Database, the headache of the thing. database, boring stuff.

But that all do not fear, small series will split the road for everyone, Chop Demon, all the way to escort everyone to the end!!!

First, the learning database was grabbed from childhood. The basic concept of course to remember, remember

Language of the database: SQL language (current)

Composition of sql: DML: Data used to insert, modify, and delete databases

DDL: Used to build databases, database objects, and define their columns, most of which start with the Create command

DQL: Used to query data in a database

DCL: Used to control access permissions, access rights, etc. of database components

Operators in the database: +-*/%

Assignment operators in the database: =

Comparison operators in the database: = > < <> (not equal to) >= <=! = (Not equal)

Logical operators in the database: And when and only if two Boolean expressions are true, returns true

Returns False if and only if two Boolean expressions are false

Not negate the value of a Boolean expression, with the highest precedence

Anyway, inserting data using SQL

Grammar:

INSERT [into] table name [(column list)] VALUES (Value list)

　

Insert multiple rows of data at one time:

(1) Adding data to a table by using the Insert SELECT statement

(2) Adding data from an existing table to a new table by using the SELECT INTO statement

Grammar:

SELECT identity (data type, seed, identity growth) as list from original table

(3) Insert with Unio keyword merge data

Updating data Using SQL

Grammar:

Update table name set column name = update value [WHERE Update value]

Use SQL to delete data (try not to use it)

Grammar:

delete [from] table name [WHERE < delete condition;]

　　

The above is just to give you a simple introduction of SQL, as well as some common syntax. Of course there are parts of the content that are ignored because there is a small part of the programmer's path that will not be used. Of course, if you do not understand can leave a problem, small series is willing to solve for everyone ~ at the end of this chapter, the small series will leave contact information.

With a database, we use it to store a lot of information. Some are visible to the naked eye, and others to the head. In this way, we have to invent a query method for querying. So, the following small series will show you the query method about the database:

The basis for querying a database:

SELECT    < list >from    < table name >[where    < query condition expression >][order    by < sorted >[ASC or DESC]]

NOTE: DESC is descending sort asc is ascending sort

(1) Querying all data rows and columns

SELECT * from STUDENT

' * ' denotes all columns

(2) querying some rows or columns

SELECT scode,sname,sadressfrom     studentwhere   sadress  = ' Hebei '

(3) using the alias of a column in a query

(4) Query null value

(5) Using constant columns in queries

(6) The number of rows that the query returns limits

Query sort using ORDER BY

Use the function in the query (it is not difficult to look at the difficult, the small part will give you the most simple explanation to let you get the biggest and biggest harvest)

SQL Server provides intrinsic functions, each of which implements different functions, and the different categories of functions can be used in conjunction with SELECT, UPDATE, and INSERT statements.

(1) String function

Name of function	Describe	Example
CHARINDEX	Used to find the starting position of a specified string in another string	SELECT CHARINDEX (' name ', ' My NAME is Tom ', 1) Returns: 4
Len	Returns the length of the string passed to it	SELECT LEN (' SQL SERVER course ') Returns: 12
UPPER	Converts the string passed to it to uppercase	SELECT UPPER (' SQL Server Course ') Back to: SQL SERVER Course
LTRIM	Clear space to the left of the character	SELECT LTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (trailing spaces reserved)
Ririm	Clear the space to the right of the character	SELECT LTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (front space reserved)
Right	Returns the character of the specified bibliography from the right side of the string	SELECT right (' Room loan ', 2) Back to: Loans
REPLACE	Replace a character with a string	SELECT REPLACE (' Struggle on both sides ', double, single) Return: The struggle of the unilateral
STUFF	In a string, delete the specified length of character and insert a new string at that location	SELECT STUFF (' 123 My 456 ', 4, 5, ' his ') Returns: 123 of his 456

(2) Date function:

GETDATE	Get the current system date	SELECT GETDATE () Return: Today's date
DATEADD	The date after the specified date part is added to the established value	SELECT DATEADD (mm, ' 01/01/2009 ') Returns: Returns 05/01/2009 in the current date format
DATEDIFF	Interval between two dates for a specified date part	SELECT DATEDIFF (mm, ' 01/01/2009 ', ' 05/01/2009 ') Returns: 4
Datename	The string form of the date part specified in the date	SELECT datename (DW, ' 01/01/2000 ') Back: Saturday or Saturday
DATEPART	The integer form of the date part specified in the date	SELECT DATEPART (Day, ' 01/15/2000 ') Returns: 15

Comments:

　　

Year	Yy,yyy
Quarter	Qq,q
Month	Mm,m
Day	Dd,d
DayOfYear	Dy,y
Week	Wk,ww
Weekday	Dw,w
Hour	hh
Minute	Mi,m
Second	Ss,s
Millisecond	Ms

(3) Mathematical functions:

RAND	Returns the number of random float from 0 to 1	SELECT RAND () Return: 0.79288062146374
ABS	Take the absolute value of a numeric expression	SELECT ABS ( -43) Return: $
celing	rounding up	SELECT celing (43.5) Return: £ º
Floor	want to go down	SELECT floor (39.8) Return: All-in
Power	takes the power value of a numeric expression	SELECT POWER (5.2) Return: +
ROUND	Rounding a numeric expression	Selecct ROUND (43.543,1) Return: 43.500
Sign	returns +1 for positive numbers, negative but 1 for 0, 0	SELECT sign ( -43) Return:-1
SQRT	takes the square root of a floating-point expression	SELECT SQRT (9) Return: 3

(4) System functions:

CONVERT

Used to transform data types

SELECT Conbert (VARCHAR (5), 12345) Return: String 12345

Due to the system functions are not often applied, again do not explain more, if in doubt, you can leave your problem Oh!

Fuzzy Query and Aggregation function (also a small point oh)

(1) wildcard character: It is a class of characters that can replace one or more real characters and appear as alternate characters when looking for information.

_	A character
%	string of any length
[]	One character in the range specified in parentheses
[^]	Any character that is not in the range specified in parentheses

(2) Use like for fuzzy query (self-summary: Like is in a string to find there is no similar to the condition of the keyword, that is, as long as we have to find the keyword in the line)

(3) Use between to reach a range of queries (self-total: between means that in the---, as the name implies is given a range, in the scope of search)

(4) Use in to query in the example value: (Self-summary: In is that the enumeration value must have the same data type as the matching column)

aggregate function :SUM () function AVG () function MAX () function and min () function COUNT ( ) function

The following please open your eyes, because I want to teach the secret of their own secrets to everyone, must remember in mind!!!

SUM () function: Calculates the sum of the columns

AVG () Function: averages are calculated

Max () Function: Calculates the maximum value

Min () function: calculates the minimum value

Count () function: Calculates the sum

Group query: implemented by the GROUP BY clause. Use group queries frequently for statistics!

SUM ()

Count ()

Max () min ()

AVG ()

Once the group by group is used, we can only get information about the groups, not the members in the group.

The select can only follow the aggregate function and the column following the group by, and the other columns do not allow

Cannot have aggregate function after where

About the query in C # The way to come to an over here, you may feel a bit around, in fact, read more to write more thinking thinking, can be figured out. In fact, there is no doorway in this. Is ' write ', Practice makes perfect!

There may be deficiencies or doubts about my summary. Of course, we must put forward, thought can also let me check the vacancy. So there are doubts and ask you to mention more.

Contact information: QQ 499834993

Email: [Email protected]

The database of those years of shawl chopping