SQL Server Management(1) Two types of authentication methods for SQL Server: User name verification and Windows authentication, and Windows authentication on development. (2) characters commonly used segment type: bit (optional value 0,1), Datetime,int,varchar,nvarchar (may contain Chinese with nvarchar). (3) The difference between varchar (), nvarchar (). Char (n):
char (n) part of less than length n is padded with spaces。 Var:variable: variable.
Getting Started with SQL statements(1) SQL statements are statements that are specific to the DBMS "talk", and the SQL syntax is recognized by different DBMS. (2) Strings in SQL statements
single quotation marks。 (3) The SQL statement is
Case insensitive, insensitivity refers to the SQL keyword, or the string value is case sensitive. (4) Create a table, delete the table can not only be done manually, you can also execute SQL statements to complete, in the automated deployment, data import with a lot. To create a table:
CREATE table T_person (ID int not null, Name nvarchar (+), age int null) deletes the table:
drop tableT_person(5) Execute a simple INSERT statement:
insert into T_person (id,name,age)
values(1, ' Jim ', () (6) * (familiar): SQL Main sub-DDL (data Definition Language) and DML (data manipulation language) two categories, create table,drop table,alter table, etc. belong to the DDL,
Select,insert,update,deleteAs belonging to DML. Data Insertion (1) INSERT statement
can save the name of the column after token, but not recommended。 INSERT into Person2 values (' Lucy ', ' 38 ') (2) If the values of some fields in the inserted row are indeterminate, then insert does not specify those columns. Data Update (1) Update a column:
Update T_personSet age=30(2) Update multiple columns: Update T_person set age=30,name= ' Tom ' (3) updated part of the data: Update T_person
set age=30 where name= ' Tom', using the WHERE statement to update only the row of Name ' Tom ', note that SQL is equal to the judgment with a single =, not = =. (4) Complex logic judgments can also be used in the Where:Update T_person set age=30 where name= ' Tom ' or age<25. Or is equivalent to the C # | | (OR). (5) Other logical operators that can be used in Where are: or,and,not,<,>,>=,<=,!= (or < >), and so on. Data retrieval (1) Simple data retrieval:
SELECT * fromT_employee. (2) Retrieve only the required columns: Select FName from T_employee. (3) Column aliases: Select Fnumberas
number , FName as name from T_employee. (4) You can also retrieve data that is not associated with any table: Select 1+1,select newId (), select GetDate (). Data summary (1) SQL aggregate function: Max (max), min (min), avg (average), sum (and), count (quantity). Data sorting (1)the
order by clause is at the end of the SELECT statement, which allows you to specify whether to sort by one column or multiple columns, or whether the sort is ascending (from small
to large, ASC) or descending (
from large to small, DESC ). (2) According to the age from the big to the small sort, if the same age, according to the salary from the big to the small sort select * from T_employee ORDER by Fage Desc,fsalary Desc (3)
The ORDER BY clause is placed after the WHERE clause : SELECT * from T_employee
where fage>23 order by Fage desc,fsal ary Desc Wildcard filtering (fuzzy matching) (1) wildcard filtering using
like。 (2)A wildcard character that
matches a single character is a half-width underscore "_" that matches an individual occurrence, beginning with any character, and the remainder as "Erry":SELECT * from T_employee where FName like ' _erry ' (3)The wildcard character
matches The half-width percent "%", which matches any character that occurs any number of times (0 or more), and "K%" matches a string of any length starting with K. Retrieve information for employees whose names contain "n":SELECT * from T_employee where FName like '%n% ' null handling (1) in the database, a column if no value is specified, the value is NULL, this null is not the same as NULL in C #, the null in the database is "not known", Instead of indicating no, the select null+1 result is null because the result of "Don't Know" plus 1 is "not known". (2) SELECT * from T_employee where fname=null and select * from T_employee where fname!=null. None of the results are returned because the database "does not know". (3) using in SQL
is null,is NULL to determine null values。 SELECT * from T_employee where FName are null select * from T_employee where FName are NOT NULL multi-value match:
where(1) Select Fage,fnumber,fname from T_employee where Fage in (34,23,35) (2) Range Value: 1) SELECT * from T_emploee where fage>=23 and fage<=272) SELECT * from T_employee where Fage between and 27 arrays are grouped:
Group by(1) Groups of people of all ages according to age: Select Fage,count (*) from T_employeeGroup by Fage
(2)
The Group by clause must be placed behind the where statement(3) Columns that do not appear in the GROUP BY clause are not placed in the list of column names after the SELECT statement (except for aggregate functions). 1) Error: Select Fage,fsalary from T_employee Group by Fage. 2) Correct: Select Fage,avg (fsalary) from T_employee Group by Fage. HAVING clause:
Having is filtering the group. (1)You
cannot use an aggregate function in where , you must use having and having
to be in the group by. Select Fage,count (*) as number from T_employee
Group by Fage have Count (*) >1 (2) Note that you must not be able to use the columns that are grouped as parameters, having no substitute for where, the effect is not the same,
Having is filtering the group.
Limit the number of result set rows:
Top(1) Select
Top 5 * from T_employee ORDER by Fsalary Desc (2) (*) Search according to the salary from high to low sort to retrieve starting from sixth place altogether three persons of information select Top 3 * from T_employee where fnumber not in (select Top 5 fnumber from T_empl Oyee ORDER BY fsalary Desc) Order BY fsalary Desc. (3) SQL Server2005 adds a simplified implementation of the Row_number function to remove data duplication:
distinct(1) Select Fdepartment from T_employee-->select
distinct fdepartment from T_employee(2) DISTINCT is the
data duplication across the entire result set, and not
for a column, so the following statement does not leave only fdepartment for duplicate value processing. Select distinct Fdepartment,fsubcompany from T_employee
SQL statement Execution Order: 1. The FROM clause assembles data from different data sources 2.the
WHERE clause filters records based on the specified criteria3.the
group by clause divides the data into multiple groupings4. Use the aggregation function to calculate 5. Use
havingThe Clauses Filter group 6. Evaluates all Expressions 7. Use
ORDER bySort the result set by algebra: five basic operations:
and, poor, Cartesian product, selection, projectionDatabase paradigm: 1NF (First Normal Form): When and only if all fields contain only atomic values,
each component is not re-dividedData item, the entity E satisfies the first paradigm 2NF (Second Normal Form): When and only if the entity E satisfies the first paradigm, and each
non-key attributes completely dependent on primary key, the second Paradigm 3NF (third Normal Form) is satisfied: when and only if the entity E is the second normal (2NF), and E does not have
Non-primary property delivery dependencyWhen the third paradigm is met
Control Redundancy(controlled redundancy) differs from non-control redundancy (uncontrolled redundancy): Uncontrolled data storage redundancy can cause problems such as: 1. When the data is updated
Repeat work2. Waste
Space3. Data may be
inconsistentTherefore, ideally, we should design a database without redundancy, but sometimes we need to improve the efficiency of the query, so we introduce control redundancy (controlled redundancy) such as:We store the student name and course number redundancy in the Grade_report table, because we need to query the student's name and the course number at the same time when we query the result. Constraints on data: Integrity constraint 1. Domain constraints: On properties
range of ValuesThe constraint of 2. Key constraints: Each relationship must have a primary key, and each
primary key must be different3. Non-null constraint: attribute value
cannot be null4. Entity integrity constraint: Primary key value cannot be null 5. Referential integrity constraint: A foreign key can take a null value, but
cannot be null if the foreign key is a primary key of another relationship。 6. User-defined integrity constraints insert operations that may violate various data operations: DOMAIN constraints, key constraints, non-null constraints, Entity integrity constraints, referential integrity constraint delete operations: Referential integrity constraint update operations: Domain constraints, key constraints, non-null constraints, Entity integrity constraints, referential integrity constraints
attached: Database basic structure Knowledge Introduction:
noun accumulation: Database: The "storeroom" data that stores and provides data: the base object stored in the database. Database management System (DBMS): A layer of data management software located between the user and the operating system. Database system: Includes a database, DBMS, application system, database Administrator (DBA) primary key (Primary key): A collection of properties or properties used to uniquely identify a record in a table.
foreign KEY (Foreign key)：
used to associate with another table, the foreign key is the primary key of another tableSuper key: A Super key is a collection key (candidate key) that uniquely distinguishes a tuple's attributes or attributes: The extra attributes in the super-key are removed, and the different tuples are still guaranteed to be distinguished. Schema: A description of a database, including the database structure, data type, and constraints. Instance (instance/state): The real data stored at a time in a database. (instance is the embodiment, instantiation of the schema at a certain moment) data manipulation language (Dml:data manipulation Language): Adding and deleting data definition language (ddl:data definition Language): definition, Delete, modify the object Data Control language in the database (Dcl:data control Language): A permission data model that controls user manipulation of the database: abstraction of real-world data features to define how data is organized, The relationship between data and compatibility (Union compatibility): Two relationships with and compatibility need to be guaranteed to have the same number of attributes, and for each attribute have the same domain view: The view is a virtual table, not the data that is physically stored. Instead, the data is derived from a base table or other view. The update to the view is actually converted to an update to the actual base table. Data Model:Basic Concepts: The abstraction of real-world data features to define how data is organized and how the data is related.
level:1. Conceptual model (conceptual): Modeling data and information by user's point of view 2. Logic/Implementation Model (LOGICAL/IMPLEMENTATION): Hierarchical model, mesh model, Relational Model 3. Physical model (physical): How data is physically stored in a specific DBMS product
the three-level schema structure of the database system:1. Internal mode (Internal Schema): (also known as storage mode.) The description of the physical structure and storage of the data is the representation of the data within the database 2. Conceptual mode (conceptual schema): (also known as the global schema. ) sometimes referred to as "mode". is a description of the logical structure and characteristics of all data in the database 3. External mode (External Schemas): (also known as sub-mode or user mode.) A description of the logical structure and characteristics of the local data that the database user is able to see and use. Describe the concept of a transaction and the four characteristics of the transaction. A: A transaction is a user-defined sequence of database operations that is either done or not, and is an inseparable unit of work. A transaction has four features:
atomicity (atomicity),
Consistency (consistency)、
Isolation (isolation) and durability (durability)。 The four properties are also referred to as ACID properties. Atomicity: A transaction is a logical unit of work for a database, and the operations included in the transaction
either you do it or you don't.。 Consistency: The result of a transactional execution must be to change the database from one consistent state to another. Isolation: Execution of one transaction cannot be disturbed by other transactions. That is, the operations within one transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other. Sustainability: Persistent, also known as permanence (permanence), refers to
once a transaction is committed, which is for the data in the database
change is supposed to be permanent.Of The next operation or failure should not have any effect on its execution results.
SQL Sever Learning Notes
