"Personal notes" SQL Operations basics

1. Primary key
It is divided into logical primary key and business primary key.
Business PRIMARY key: with real meaning, such as ID card, bank card, etc., once changed, difficult to maintain.
Logical PRIMARY KEY: There is no actual meaning, only to identify the unique identity of the current column's location in the current data table.
cannot be edited manually, except in special cases.
• An identity column is recommended for each table ·
• Logical PRIMARY KEY recommended ·
• The primary key column is recommended for each table, and the identity is set ·
• The primary key identification column, even if the data is deleted, will grow according to the original identity

Command mode to create a database

Create DATABASE myschool– Name
On
(
Name= ' Myschool_data ', – database name
Filename= ' D:\MySchool_data.mdf ', – Physical file name
size=5mb,– Initial Size
maxsize=10mb,– Maximum file size
filegrowth=15%– main file growth rate
)
Log on
(
Name= ' Myschool_log ', – log file name
Filename= ' myschool_log.ldf,– Log physical file name
SIZE=2MB,
MAXSIZE=4MB,
Filegrowth=1mb
)
Go

Command mode to create a table-select the location where you want to create the table first

Format:
Column name type identifies whether the primary key is allowed to be empty
ID INT IDENTITY (+)
CREATE TABLE Student
(
ID INT IDENTITY (PRIMARY KEY),
Name NVARCHAR (+) is not NULL,
Age INT Not NULL
)

DML database Operation language

• Add/delete
• Increase data
INSERT into table name (...) Column name) VALUES (... The data to be inserted)
· INSERT into table name VALUES (... Data to be inserted) – fully inserted in the
· INSERT into table name (...) Column name to insert) VALUES (... corresponding data) – selective insertion of the

    Note: 1. When inserting data, the bit field uses "0/1" to denote True/false 2. The time field is enclosed in single quotation marks, and the contents of the single quotation mark follow the basic time format, such as the column in the ' 2014-10-10 ' 3.INSERT statement The number is the same as the number of values after value 4. You cannot insert data for an identity column (in special cases, set IDENTITY_INSERT table name on) (same as SET IDENTITY_INSERT table name OFF) 5. Direct drag ' column ' to To quickly insert a column name 6. In addition to the identity column, all other fields are written in the same order, and are written in sequence 7. Null in the datasheet is not a null pointer or an empty string, but instead indicates that the database does not know why the value 8. UN in code executed on the server         Icode string constants must be prefixed with the uppercase letter N to avoid the inability to recognize 9.INSERT into table names (column names) when inserting special characters values can be the default value 10.INSERT into Student (Name) VALUES (' three ') insert a single quote in the data, you need to enter two single quotes to indicate a single quotation mark (escape character) • Delete data delete from St Udent--Delete all data from the Table truncata table Studeng--Resets the tables (including resetting the identity) • The efficiency of the above is very different, if the use of Delete will generate a lot of logs, and using TRUNCATE will only produce a row    Log. DROP table Student--delete the table directly from the database (with caution) • Modify statement UPDATE table name set column Name 1 = data 1, column Name 2 = data 2 WHERE Column Name 1 = data 1 (and column Name 2 = data 2 and column name 3 = Data 3) (OR column Name 2 = data 2 (and column Name 2 = data 2 OR column Name 3 = data 3) Update Student set age=age+1 update Student set age+=1 update Student setPhone= ' 10010 ' where name= ' Harry ' and Gender=1 or name= ' King 56 ' UPDATE Student SET address= ' unknown ' where Address is NULL Where address is not NULL where address= ' where address Note: 1. and takes precedence over OR executes 2. When SET, the original value of 3 can be removed. Other logical operators that can be used in Where: (| |) Or, (&&) and, (!) Not, <, >, >=, <=, <> (same! =) (unequal) constraints: non-NULL constraint: Data cannot be null PRIMARY KEY constraint: Unique and cannot be NULL UNIQUE constraint: Data unique, allowed to be empty, but only once the default about Bundle: If you do not give a value, check the constraint for the default value: scope and various restrictions. Right--check constraint foreign KEY constraint on the column name of the design interface: Add primary foreign key relationship, foreign key value must come from primary key table • Query statement Select Column Name 1 (as rename), Column name 2 (as rename) ... From table name where condition select column Name 1 (as ' rename '), Column name 2 (as ' rename ') ... From table name where condition select column Name 1 (rename), Column name 2 (rename) ...        From table name where condition Select +-Evaluates expression value Select GETDATE ()--quick and easy access to server system time-top--Take the first part of the data in the entire result set of the query Select Top * FROM Student--Remove all data from the top 10 of the Student table select top Name,gender,age from Student--Remove the first 10 in the Student table Part of the data select top percent * from Student-Remove the first 10% of the data from the Student table, all the calculated results are rounded, as21/10=2.1=>3-order by (less efficient, not used)-sort the data results that are currently queried, sorted by the columns specified later--order by column name desc: Descending Ordinal data--order By column name ASC: Sort data in ascending order select top * from Student ORDER by ID desc-distinct--for query After the result, the duplicate rows are removed.        Two rows of data are not considered duplicates as long as one data is different. Select distinct name,gender,age from Student--duplicates the entire result set of the query.

• Aggregation Functions
MAX (column name) – Returns a row of data
MIN (column name) – Returns a row of data
AVG (column name) – Returns a row of data
SUM (column name) – Returns a row of data
COUNT (column name) – Returns the amount of data that was successfully filtered. Recommended use of COUNT (ID)

注：1.聚合函数对NULL值不计算。如果一行的值都是NULL，COUNT(*) 包含对空值行、重复行的统计。    2.对于查询表中的所有数据，【select 分别列出所有的列名 from 表名】要比【select * from Student】 运行效率高。例：select max(age) from Student  --取出表中的最大年龄    select min(age),max(age),avg(age),sum(age) from Student  --计算出各种值    select count(*) from Student where age=10  --计算符合要求的行的数据量

• Sorting Rules
-sequential numbers, letters, men (Pinyin)

· WHERE
-< > <= >= <>! = and other judgment symbols

- BETWEEN...AND... （该函数经过优化，在大数据中使用时，明显查询效率）    -例如 BETWEEN 20 AND 30 其显示的结果中包含20与30     -SELECT * FROM STUDENT WHERE AGE BETWEEN 20 AND 30- IN (数据1,2...) （查询表中列中的值为括号中的值的数据）    -SELECT * FROM STUDENT WHERE CID IN (1，2) 同 SELECT * FROM STUDENT WHERE CID=1 OR CID=2    -SELECT * FROM dbo.Student WHERE Gender IN (‘男‘,‘女‘)注：1.BETWEEN and 在数据库内部是做过特殊优化的，执行效率比> and < 等这种方式快

• Fuzzy Query
-Filter data by defined rules using a system-defined match
-Match Character: _% [] ^
_ Represents an arbitrary character
% represents 0 or more arbitrary characters
[] represents the range of values for a character
-Case insensitive
^ Mates [] used to indicate that they do not belong to this interval
-The match database is incompatible, SQL Server can use it, and the other database uses the not like

Note: 1. Using a match to filter data is very inefficient 2. When escaping a match, it is generally used [] to escape, while ' ^ ' does not, because when ' ^ ' is outside of [] represents a common ' ^ ' symbol-for example: SELECT * from STUDENT WHERE NAME like '% Wang ' --Filter out a list of people with a ' king ' in their name select * from STUDENT where name like ' King% '-filter out the name of the person named ' King ' from the table select * from STUDENT WHERE NAM E like ' _a% '--filter out the table the second word is ' a ' person select * from STUDENT WHERE name is like '%[a-z]%]--filter out the table with the letters in the name SELECT *       From STUDENT SHERE name like '%[0-9]% '--a person with a number in the name of the query table SELECT * from STUDENT WHERE name like '%[0-9 (,) a-z]% '-- SELECT * from STUDENT where name like '%[^0-9a-z]% '--query for people with no numbers and letters in the name SELECT * from STUDENT WHERE name is not a like '           %[0-9a-z]% '--ibid. The person who has no numbers and letters in the name is special: SELECT * from STUDENT WHERE name is like '%[%]% '--the person who has a '% ' in the name of the query SELECT * from STUDENT where name like '%[_]% '--Query the name of a person who has a ' _ ' SELECT * from the STUDENT where name like '%[']% '--Query the person with a single quote in the name SELECT * from STUDENT where name like '%[']% '--Ibid. SELECT * from STUDENT WHERE Name like '%^% '--Match out nameWho has a ' ^ ' in it. 

• Null value judgment
-select * from STUDENT WHERE ADRESS is NULL
-select * from STUDENT WHERE ADRESS are not NULL

· ISNULL () function
-such as: SELECT Name,isnull (age, other value of age type) from STUDENT WHERE ... – When the result of the query, the value of age is ' NULL ', then it is displayed as the value to be replaced

• Type Conversion
-cast (age as NVARCHAR (10)) – Converts an age of type int to NVARCHAR (10) type
-convert (int, ' 123 ') – Converts the string ' 123 ' to type int
-

· GROUP by
CAST (EXPRESSION as Data_type)
CONVERT (Data_type,expression,[style])
-Grouped by a column of data, a row in the returned data table represents a group, usually in conjunction with an aggregate function.
-for example: SELECT Gender,max (age), MIN (age), SUM (age) from STUDENT GROUP by GENDER
SELECT * from STUDENT GROUP by GENDER
-Note: 1. Only the original table data that is grouped by the group is obtained, and the other data needs to be obtained through the aggregate function.

-HAVING    -其作用是对分组后的信息进行过滤。可用的参数为GROUP BY子句中的参数和聚合函数    -例如：SELECT AGE,COUNT(ID) FROM STUDENT GROUP BY AGE HAVING AGE>25           SELECT AGE,COUNT(ID) FROM STUDENT GROUP BY AGE HAVING COUNT(ID)>1

· Order of execution of SQL statements
TOP DISTINCT, 1.SELECT
2.FROM table
3.WHERE condition – form result set
4.GROUP by column
5.HAVING Filter Criteria
6.ORDER by column

' Union union query
-1. Two result sets must have the same number of columns
2. Columns have the same data type (at least can be implicitly converted)
3. The column name of the final output collection is determined by the column name of the first result set

-SELECT * FROM STUDENT WHERE AGE>25 UNION SELECT * FROM STUDENT WHERE AGE<30 UNITON ... （可进行多结果集联合，但结果中不会出现重复数据） -SELECT * FROM STUDENT WHERE AGE>25 UNION ALL SELECT * FROM STUDENT WHERE AGE<30 UNITON ALL ... （可进行多结果集联合，结果中可能包含重复数据）

• BULK INSERT = INSERT statement + result set
-Insert the result set as a value into the database, with the same number of columns and types as the result set
INSERT into STUDENT (Stuno,english,math)
SELECT 1,80,100 UNION
SELECT 1,80,100 UNION
SELECT 3,50,59 UNION All
SELECT 4,66,89 UNION
SELECT 5,59,100

 -可以复制一张表，但是没有主键    Select * into Score2 from Score where 1<>1

• String function
-len (string) computes the number of characters in a string
-datalength (string) computes the byte length of the string
-lower (String) converts the string to lowercase
-upper (STR ING) converts a string to uppercase
-ltrim (string) to remove white space characters to the left of the string
-rtrim (string) to remove whitespace characters to the right of the string
-left (string,length) Gets the length string from the left side of the string
-right (String,length) takes the string from the right to start taking the length of the string
-substring (String,start_num, Length) takes a length character from the start_num character in the string

• Date function
-getdate () get the current date
-dateadd (part_name,num,date) increases NUM's time based on DATE
Example: SELECT DATEADD (Year,2,getdate ())
SELECT DATEADD (Month,2,getdate ())
SELECT DATEADD (Day,2,getdate ())
SELECT DATEADD (Hour,2,getdate ())
...
-datediff (Date_name,start_date,end_date) obtains the difference between two time units of Date_name as the time unit
Example: SELECT DATEDIFF (Minute,getdate (), DATEADD (Year,2,gatdate ()))
-datepart (date) Gets a specific part of a date
Example: SELECT DATEPART (Year,getdate ())
SELECT DATEPART (Month,getdate ())
SELECT DATEPART (Hour,getdate ())
-year (date) year of acquisition time
-month (date) month of acquisition time
-day (date) Day of acquisition time

• Absolute ABS (value)

"Advanced Application"
-insert
1. Precede the values of the INSERT statement with the output INSERTED. Column name so that the column name value of the new data can be obtained, and the column name value will be returned when OUTPUT is used, similar to the @ @IDENTITY, compared to the normal INSERT statement.

-delete
1. DELETED. Column name, used before where, the effect is the same as INSERTED. Column name. (Multiple values are returned if multiple rows are deleted)

Add a default value constraint for a column of an existing table
ALTER table [table name] ADD CONSTRAINT [constraint name] default ([default]) for [column name]

Personal note SQL Operation Basics