Some summary of the database

The database is just a tool shorthand: SSMS full spell: SQL server Management Studio Server name: The IP address of the computer where the database service is located through ". (This station) "," lacal (local) "Login Click pc start → run →cmd→net start MSSQLSERVER server's startup net stop MSSQLSERVER server stops. mdf database file, there is only one!   (Copy MDF for general replication). LDF indicates that a log is generated and logged, at least one! Create DATABASE Database name: cannot appear with the same name database first letter cannot be used for numeric use database name a database commonly used data type int: Shaping type var CHAR (50): String type Decimal (18,2): Decimal type 18:18 bits can be written after a decimal point 2: Take the two decimal places datetime: Time type image     : Image creation Table name--Create DATABASE table name Note: The table with the same name cannot appear: Add, modify, delete, query (column name data type column name data type NOT NULL,             --not NULL column can be empty column name data type primary key identity (() not NULL,--Set primary key self-growth) Set primary key foreign key formatting primary key column cannot be empty cannot repeat CREATE table name 1 (Column name data type, column name 1 data type primary key NOT NULL,) CREATE TABLE table name 2 (column name 1 data type, foreign K  EY (column name 1) references table name 1 (column name 1), column name data type,) go go can write not write insert into table name values ()--the write in parentheses must correspond to the one by one that you wrote when you created the table Values meaning Uptade table name set the content you want to change WHEre condition delete from table name where condition TRUNCATE TABLE name--Removing the contents of the table without deleting the table database The most important is the query projection select * FROM table name Select column 1, column 2: .  From table name select DISTINCT column name from table name--Go to re-filter select top numeric column |* from table name (a) equivalent and not equivalent select * from table name where column name = value Select * FROM table name where column name = = value SELECT * FROM table name where column name > value SELECT * from table name where column name < value SELECT * FROM table name where column name >= value SELECT * From table name where column name <= value (ii) Multiple conditions and ranges select * from table name where Condition 1 and| | Or Condition 2 ... select * from table name where between ... and ... select * FROM table name where column in (Value list) (iii) Fuzzy query like% _select * from table name wher e-column like '%_ ... ' sort select * from table name where condition order by column name asc| | DESC, column name asc| | DESC Connection query: Multiple tables in a single interface view to see the first method first step: Generate Cartesian product Select*from table 1, table 2 The second step: the Cartesian product screening select*from table 1, table 2 where table 1. Related columns = table 2 . The third step in the related column: Display the columns you need in select Table 1. Column 1, table 1. Column 2, table 2. Column 1 ... from table 1, table 2 where table 1. Related columns = table 2. Related Columns--you get the column must distinguish is the second method of the table--the background run speed than the first  One way to quickly recommend using Select *from Table 1 join table 2 on table 1. Related columns = Table 2. Associated Join table 3 on table 2. Associated column = Table 3. Related columns before join Modifier default INner left JOIN link to left table the information of the main left table is shown in the right table according to the needs of left table show right link to right table as the main right table information all show up the left table according to the needs of the right table to show the subquery (nested query) connection can do the sub-query can do the necessary factors: multiple The table must be at least two layers of the associated column query first query the inner layer of the query of the outer layers of queries to provide query Results union query: The rows of multiple tables in a single interface View View--for more than one table when a table when the or can be a SELECT column 1, column 2 from table 1 WH ere condition unionselect column 1, column 2 from table 2 where Condition group query: group...by...having ... Statistics function (aggregate function) count (), Max (), Min (), SUM (), AVG () count (*) gets all the rows count (column) to get all the non-null numbers in that column. Max (column) This column is the largest, min (column) of this column, and the sum of this column is the average select column name of the AVG (column) column, and the COUNT (*) from table name Group By column name has a condition. Once group by is used, Then select and from the middle can not use *, can only contain two kinds of things: Group by the name of the column, and the other is the statistical function has the following is generally the statistical function. It is used to further filter the data after grouping. For columns generated by statistical functions, the default is no column name, and you can specify the column name by using the following method. Select Column Name 1, column name from 1,AVG (column name 2) as the starting Column Name 2 from table name Group By column name 1 conversion function Print cast (' 123 ' as int) +10--cast First write variable re-write type string converted to Integer Type print convert (int, ' 123 ') +12--convert first write type re-write variable string to integer select year (' 1993-03-09 ')--Take years Select DATEADD ( day,1, ' 1993-03-09 ')--plus 1 days Select DateDiff (Day, ' 1993-03-09 ', ' 2015-04-25 ')--Calculate how many days you live today print getdate ()--Get current PC time Prin T ISDAte (' 2015-04-25 ')--determine if the time format is correctly returned 1 otherwise 0print datename (weekday, ' 2015-04-25 ')--datename returns the value of a string type today a few weeks print Datepa RT (Weekday, ' 2015-04-25 ')--datepart returns the value of an int type today is the day of the week select Year (GETDATE ())-year (' 1993-03-09 ') as age--Get your age s     Elect left (' Asdfgh ', 3)--Get fixed length right and select upper (' asdfgh ')--convert to uppercase lower instead of select LEN (' iubfbjnboidnslk ') --len return value int type returns the total length of print ltrim (' uyh ')--Removes the left space RTrim whereas print substring (' jhhguuiliui ', 6, 3)--intercepts from 3 Length starting from 6 index starting from 1 print replace (' 2b2b2b2b2b2b ', ' 2B ', ' HB ')-replace the string 2B with Hbprint replicate (' Hello ', 5)--Copy Hello 5 Times Display Print reverse (' This is the case ')--Reverse

Some summary of the database