SQL-2. SQL Server Management + 3. SQL basics 1 + 4. SQL basics 2

Sqlserver Management

• Common field types: Bit (optional values: 0, 1), datetime, Int, varchar, and nvarchar (may contain nvarchar for Chinese)
• Differences between varchar, nvarchar, and char (N): Fill in spaces for less than N parts of char (n. VaR: Variable, variable.

 

 

 

SQL statements

• Use strings in SQL statementsSingle quotes.
• SQL statements are case-insensitive.
• Creating and deleting tables not only can be done manually, but can also be done by executing SQL statements, which are used in automated deployment and data import,

Create Table Name

(

Column name 1 data type,

Column name 2 data type,

Column name 3 data type,

....

)

Create Table t_person (ID int not null, name nvarchar (50), age int null)

Drop table t_person1

• SimpleInsertStatement.Insert into t_person (ID, name, age) values (1, 'Jim ', 20)
• (*) SQLDDL(Data Definition Language) andDML(Data operation language.Create Table,Drop table,ALTER TABLEAnd so onDDL,Select,Insert,Update,DeleteAnd so onDML

 

 

Primary key selection-Automatic Identification and guid

Two common primary key data types in sqlserver:Int(OrBigint) + ID column (also known as the auto-increment field );Uniqueidentifier(Also knownGuid,UUID)

Guid generation function in sqlserverNewid (),. Net to generate a guid:Guid. newguid ()And the return value belongs to the guid type.

 

 

 

 

 

 

Data Update (update table name: Set column name)

• Update a column:Update t_person set age = 30
• Update multiple columns:Update t_person set age = 30, name = 'Tom'
• Update part of data:Update t_person set age = 30 Where name = 'Tom', Use the where statement to indicate that only the row whose name is 'Tom 'is updated. Note that the SQL statement is medium to the condition where a single = is used instead of =.
• Complicated logic judgment can also be used in the WHERE clause.Update t_person set age = 30 Where name = 'Tom 'or age <25, Or is equivalent to | (OR) in C)
• Update person1 set nickname = n'two' where (age> 20 and age <30) or (age = 80)
• Other logical operators that can be used in where: Or, and, not, <,
  >,>=, <= ,! = (Or <>)

 

 

Delete data (delete from table name where column name = value)

• Delete all data in the table:Delete from t_person.
• DeleteOnly the data is deleted, the table is still, andDrop tableDifferent.
• DeleteYou can also use the WHERE clause to delete some data:
  Delete from t_personWhereFage> 20

 

 

Data Retrieval (select)

• Simple data retrieval: Select * From t_employee
• You can also retrieve data not associated with any table: select 1 + 1; select newid (); select getdate ();

 

 

Data aggregation (use of Aggregate functions)

• SQL Aggregate functions: max (maximum), min (minimum), AVG (average), sum (and), count (Quantity)
• Highest salary for employees over 25 years old: Select max (fsalary) from t_employee where Fage> 25
• Minimum and maximum wage: select Min (fsalary), max (fsalary) from t_employee

 

 

Order)

• OrderThe clause is located at the end of the SELECT statement. It allows you to specify one or more columns for sorting. You can also specify that the sorting method is ascending (ascending,ASC) Or in descending order (from large to small, DESC ).
• List of all employees by age in ascending order:
  Select * From t_employee order by Fage ASC
• Sort by age from large to small,If the age is the same, the salary is sorted from large to small.:
  Select * From t_employeeOrder by Fage DESC, fsalary DESC
• The order by clause must be placed after the WHERE clause:Select * From t_employee where Fage> 23 order by Fage DESC, fsalary DESC

 

 

Wildcard Filter

• Use wildcard FilterLike. A single-character matching wildcard is a half-width underline "_", which matches a single occurrence character. Start with any character, and the rest is "erry ":
  Select * From t_employee where fname like '_ erry'
• The wildcard for multi-character matching is a half-width percent sign "%", which matches any character that occurs at any number of times (zero or multiple. "K %" matches strings starting with "K" and with any length. Search for employee information whose name contains the letter "N:
  Select * From t_employee where fname like '% N %'

 

 

Null Value Processing

• If no value is specified for a column in the database, the value is null. null in this column is null in C #. null in the database indicates "unknown", instead of indicating no. Therefore, the Select null + 1 result is null becauseDon't know"The result of adding 1 is still" unknown ".
• Select * From t_employee where fname = NULL;
  Select * From t_employee where fname! = NULL;
  There are no returned results because the Database "does not know ".
• SQLIs null,Is not nullTo determine the null value:
  Select * From t_employee where fname is null;
  Select * From t_employee where fname is not null;

 

 

Multi-value matching

• Select Fage, fnumber, fname from t_employee where Fage in (23, 25, 28)
• Range value:Select * From t_employee where Fage> = 23 and Fage <= 27;
  Select * From t_employee where Fage between 23 and 27;