SQL Server basic knowledge point summary

-- 1. What file does SQL consist?

/* Answer: SQL is composed of data files and log files */

-- 2. What are the two types of SQL data files? What are the extensions? How many can there be?

/* Answer: SQL data files are divided into primary and secondary data files.

There is only one primary data file. The second-level data file has 0 to N extensions: primary data file: MDF second-level data file: NDF */

-- 3. What is the relationship between the SQL file group and the file?

/* A: files are stored in a file group. A file can belong to only one file group. A file group can contain multiple files. */

-- 4. UseCodeCreate databases with multiple data files?

/* Answer:

Create Database databasename primary on

(Name = logical name, filename = physical name, -- the extension is MDF size = initial size, maxsize = maximum value, filegrowth = growth ),

-- Names of filegroup file groups are separated by commas (,).

(Name = logical name, filename = physical name, -- the extension is NDF size = initial size, maxsize = maximum value, filegrowth = growth)

Log On

(Name = logical name, filename = physical name, -- the extension is LDF size = initial size, maxsize = maximum value, filegrowth = growth )*/

-- 5. Use code to create a table?

Create Table table_name -- Name of the table to be created

([ID] int identity () primary key, -- Set ID to the auto-increment primary key

[Name] varchar (20) not null, -- Set Name Not to null

[Age] tinyint check ([age] <120) -- set the age to less than 120)

-- 6. Use code to modify the database and add a file group?

Alter database databasename -- Name of the database to be modified

Add filegroup filegroupname -- name of the file group to be added

-- 7. Use code to modify files and file groups?

/* Answer: -- modify the file example.

Alter database databasename

Modify file (

[Name] = 'original logic name ',

[Newname] = 'new logic name ',

[Filename] = 'new filename ',

[Size] = 'file size ',

[Maxsize] = 'maximum file size ',

[Filegrowth] = 'file growth value or growth percentage ')

*/-- Modify a file group

Alter database demo_20091230new

Modify filegroup filegroup2 name = newfilegroup_name

-- 8. Use code to modify a table to add, modify, and delete columns?

-- Add the column sample code alter table student add [s_number] varchar (20)

-- Delete the column sample code alter table student drop column [s_number]

-- Alter column sample code alter table student alter column [s_name] varchar (8)

-- Modify the column name through the system stored procedure sp_rename 'student. s_name ','s _ name_new'

-- 9. How can I view table and database information using code?

/* Answer: View table: sp_help table name view database: sp_helpdb Database Name

*/-- Sample Code: -- View table: sp_help class

-- View table: sp_helpdb demo_20091230new

-- 10. What are the SQL statements used to rename databases and tables?

-- Rename the SQL statement of the table (calling the Stored Procedure) sp_rename class, classnew

-- Do not add quotation marks sp_rename 'class', 'classnew'

-- Add quotation marks -- alter database demo_20091230new modify name = demo_20091230

-- Rename the database SQL statement sp_renamedb demo_20091230new, demo_20091230

-- Do not add quotation marks sp_renamedb 'demo _ 100', 'demo _ 20091230new' -- add quotation marks

-- 11. What is database integrity?

/* Answer: To ensure database consistency and accuracy, use constraints and triggers. */

-- 12. database integrity classification?

/* Answer: entity Integrity: each row of the table is a unique entity in the table. (Primary Key)

Domain Integrity: indicates that columns in a table meet specific data types and constraints.

Integrity of reference: The primary key and foreign key keywords of the two tables are consistent.

Custom Integrity: constraints on user-defined data types. */

-- 13. What are the two levels of constraints and their relationships with integrity?

/* Answer: constraints on the table sharding level and column level can be converted to each other, but they are written differently.

Table-level constraints must be used to create a joint primary key.

Primary key constraint (entity integrity)

Unique key constraint (for domain integrity)

Not null constraint (implement domain integrity)

Check constraints (implement domain integrity)

Foreign key constraints (for reference integrity) Custom Data Types (for custom integrity) where primary key, unique key, foreign key can create table-level and column-level constraints. */

-- 14. Use code to create a not null constraint?

/* A: add the table directly when creating the table.

*/-- Sample Code: Create Table student ([ID] int not null)

-- 15. Use the code to create the primary key constraint (two methods )?

/* A: The primary key constraint can be created at the column level or table level. When creating a federated primary key, you must create it at the table level.

*/-- Example code (column-level) Create Table student ([ID] int primary key, [name] varchar (20 ))

-- Example code (Table-level) Create Table student ([ID] int, [name] varchar (20) Constraint pk_student primary key ([ID], [name])

-- 16. Use code to create a foreign key constraint (two methods )?

/* A: The foreign key constraint can be created at the column level or table level.

*/-- Example code (column-level) Create Table student ([ID] int primary key, [c_name] varchar (20) References class ([c_name])

-- The Course name in the student table references the course name in the curriculum)

-- Example code (Table Level) Create Table student} ([ID] int primary key, [c_name] varchar (20), constraint fk_c_name foreign key ([c_name]) references class ([c_name]) -- The Course name in the student table references the course name in the course list)

-- 17. Use code to create the unique key constraint (two methods )?

/* A: the unique key constraint can be created at the column level or table level. Add a unique constraint to the column where the constraint is created.

*/-- Example code (column-level) Create Table student ([ID] int primary key, [c_name] varchar (20) References class ([c_name]), -- The Course name in the student table references the course name [s_num] varchar (20) unique in the course list. The student ID must be unique)

-- Sample Code (Table-level)

Create Table student ([ID] int primary key, [c_name] varchar (20) References class ([c_name]), -- The Course name in the student table references the course name [s_num] varchar (20) and constraint un_s_num unique ([s_num]) in the course list. The student ID must be unique)

-- 18. Use code to create check constraints (two methods )?

/* A: The check constraint can be created at the column level or table level.

*/-- Example code (column-level) Create Table student ([ID] int primary key, [c_name] varchar (20) References class ([c_name]), -- The Course name in the student table references the course name [s_num] varchar (20) unique in the curriculum, -- the student ID must be unique [s_age] tinyint check ([s_age] <60) -- age must be less than 60 years old) -- Example code (Table Level) Create Table student ([ID] int primary key, [c_name] varchar (20) References class ([c_name]), -- The Course name in the student table references the course name [s_num] varchar (20), [s_age] tinyint, constraint ck_s_age check ([s_age] <60) in the course list. The student ID must be unique)

-- 19. Use code to modify or delete constraints?

/* A: Modify constraints: Only not null can modify constraints by modifying columns.

Delete constraint: The drop constraint keyword, followed by the constraint name.

*/-- Sample Code (modify the not null constraint by modifying the column .) : Alter table class alter column [name] varchar (20) not null

-- Delete Code: Drop constraint ck_s_age -- delete the unique student ID Constraint

-- 20. Use code to view constraints?

/* A: view the name of the constraint sp_helpconstraint */

-- Sample Code: sp_helpconstraint ck_s_age

-- View the unique student ID constraint sp_helpconstraint ck_s_age

-- 21. What data types does SQL have?

/* Answer: date type: datetime, smalldatetime

Integer type: bigint, Int, smallint, tinyint

Floating Point: Double, float, decimal, real, numeric

Currency type: Money, smallmoney

Character Types: Char, varchar, nchar, and nvarchar

Binary data types: binary, varbinary, and image

Globally Unique Identifier: uniqueidentifier

Large data types: Text, ntext, image */

-- 22. SQL statement classification (DDL, dql, DML, DCL )?

/* Answer: Data Definition Language (DDL): creates, deletes, and modifies database objects.

Such as tables, views, modes, triggers, and stored procedures.

Data Query Language: used by dql users to retrieve databases.

Data manipulation language: DML is used to add, modify, and delete data stored in database objects.

Data Control Language: DCL (Data Control Language) controls database access permissions.

Data Definition Language: Create, drop, alter

Data Query Language: select

Data manipulation language: insert, update, delete

Data Control Language: Grant, deny, and revoke )*/

-- 23. What does the keyword "Identity" mean?

/* Answer: the keyword "Identity" indicates the automatic number, which is automatically increased. */

-- 24. What does uniqueidentifier mean? How to generate?

/* A: uniqueidentifier indicates a globally unique identifier. Use select newid () to generate */

-- Sample Code: Select newid () as globally unique identifier

-- 25. Create by default? Binding default value? Unbind default value?

/* A: There are two default values to create:

A) create a table

B) Use the create default keyword

Difference: Later, it is independent from the table. The former must have a table.

Bind default value: Use sp_bindefault Default Value Name, table name. Column name.

Note: Only the create default keyword needs to be bound.

Unbind default value: Use sp_unbindefault default name, table name. Column name.

Note: Only the create default keyword needs to be bound.

*/-- Example code: Create Table class ([ID] int primary key, [name] varchar (20) default ('Tom '))

-- Code example: Use the create default keyword to create default df_name as 'Tom'

-- Code example: bind the default value to the name column of the class Table sp_bindefault df_name, 'class. name'

-- Code example: unbind the default value to the name column of the class Table sp_unbindefault df_name, 'class. name'

-- 26. Create a rule? Binding rules? Unbind rules?

/* A: Use the create rule keyword to create a rule.

Sp_bindrule system stored procedure for binding rules

Unbind the sp_unbindrule system stored procedure

*/-- Sample Code: Use the create default keyword to create the create rule ru_name as @ name = 'Tom'

-- Code example: bind the rule to the name column of the class Table sp_bindrule ru_name, 'class. name'

-- Code example: unbind the rule to the name column of the class Table sp_unbindrule 'class. name'

-- 27. How to create a custom type?

/* Answer: create a custom type: Use the system stored procedure: sp_addtype */

-- Code example: Create an ID card data type (18 characters) and do not allow null sp_addtype idcard, 'varchar (18) ', not null'

-- 28. What is the execution sequence of select queries?

/* Answer:

1. Execute the from statement and create a worksheet based on one or more tables following the from statement.

If multiple tables exist, the tables are connected to each other.

If there is only one table, it is directly used as a worksheet.

2. If there is a where clause, search for rows that meet the conditions based on the where clause.

3. If the group by clause exists, the result set generated in step 2 is grouped and summarized.

4. If a having clause exists, the result set generated in step 3 is filtered by group.

5. If distinct and top keywords exist, duplicate rows will be filtered out based on step 4.

6. If there is an order by clause, it will be sorted Based on Step 5.

7. display the query results. */

-- 29. What are the types of table relationships in the database? What are the differences?

/* Answer: There are four types: one-to-one, one-to-multiple, multiple-to-one, and multiple-to-many)

Many-to-many: tables must be divided into two multiple-to-one examples. For example, if the relationship between students and courses is many-to-many, a new student elective curriculum is added.

The student-student elective curriculum is one-to-many relationship between the course and the student elective curriculum is one-to-many relationship */

-- 30. What are the keywords for cascading deletion and update? And write the code?

/* Answer: cascade deletion Keyword: On Delete Cascade

Cascade update Keyword: On update Cascade

The code example is as follows: */-- modify the alter table studentclass add constraint jldelete foreign key (fk_s_id) References student (s_id) on Delete cascade on update Cascade

-- 31. What are the types of table connections? What are their meanings?

/* Answer: There are five types.

Inner join: inner join (based on the matching records in the two tables)

Left join/left Outer Join: (the record in the left table prevails)

Right join/right outer join: (based on the records in the table on the right)

Full join/full outer join: (only one record exists in the table on both sides)

Cross join: (Cartesian product of records of two tables )*/

-- 32. What is the keyword used to remove duplicate rows?

/* Answer: distinct (placed after the select keyword )*/

-- 33. What are the keywords that show both detail records and summary values during grouping? -- Please write a sample code?

/* Answer: Keyword: Compute \ compute by */select s_group, s_age as 'average age' from student where s_group = 'two group' order by s_group compute AVG (s_age) by s_group

-- 34. What are the wildcards in SQL? What is the meaning of each type?

/* A: '_' indicates any character;

'%' Indicates 0-N arbitrary characters;

'[ABC]' indicates any character in A, B, or C, often used with '%;

'[^ ABC]' indicates that it is not a and B and any character in C. It is often used with '% ;*/

-- 35. What are the similarities and differences between rollup and cube?

/* Answer: They all extend the aggregate result set in a group.

Cube has more extensions than rollup. */-- Select s_group, AVG (s_age) from student group by s_group

Select s_group, AVG (s_age) from student group by s_group with Rollup

Select s_group, AVG (s_age) from student group by s_group with cube -- group select s_group, s_name, AVG (s_age) from student group by s_group, s_name

Select s_group, s_name, AVG (s_age) from student group by s_group, s_name with Rollup

Select s_group, s_name, AVG (s_age) from student group by s_group, s_name with cube

-- 36. What is the subquery type? What are their differences?

/* Answer: subqueries are classified into standard subqueries (nested subqueries) and related subqueries

Difference: Standard subqueries (nested subqueries) are executed only once;

Related subquery: The subquery is executed 0-N times. */-- Example: -- query the information of students under 23 years of age (in subquery Mode) -- standard subquery select * from student where s_name in (select s_name from student where student. s_age <23)

-- Query student information and calculate the difference between the average age of each student in the class and the average age of the class -- related subquery select *, (select AVG (s_age) from student) as 'average age of the class ', s_age-(select AVG (s_age) from student) as 'difference 'from student

-- 37. What are the meanings of subqueries and the notes for writing subqueries?

/* A: subquery: simplifies complex queries and logically Splits a complex Query into several simple queries.

Subquery considerations:

1. parentheses

2. It can be used in many places

A) When an alias is used;

B) when in or not in is used;

C) when the update, insert, and delete statements are used;

E) when comparison operators are used;

F) when any, some, and all are used;

G) when using exist or not exist;

H) where the expression is used. */

-- 38. What are the meanings of some and all keywords? Please write the sample code?

/* A: Some and all are comparison operators (>, >=, <, <=, And ,! =, <>, =), Used to compare the values returned by some and all queries.

*/-- Example: search for information about older students whose ages are above the average age.

Select * From u_student where [age]> All (select AVG (isnull ([age], 0) from u_student)

-- 39. What is the meaning of the keyword in? Please write the sample code?

/* Answer: the keyword in indicates that it is in...

*/-- Example: Select * From u_student where [name] In ('zhang san', 'Li si ')

-- 40. What are the three types of views? Please write the sample code?

/* A: single table view: data only comes from one table.

Multi-Table view: data only comes from multiple tables.

Nested view: data only comes from the view.

*/-- Example: Nested View -- create Student Information view (single table view) Create view v_studentinfo as select * From u_student us

-- Create a student Name Information view (nested view) Create view v_studentnameinfo as select [name] From v_studentinfo

-- 41. What are the advantages and disadvantages of a view?

/* Answer: View advantages:

1. Simplified query operations

2. conceal sensitive data to improve security

3. Custom Data

4. Data Query and storage Separation

View disadvantages:

1. Low performance

2. Update restrictions: Having, group by, top, distinct, and,

The calculation column and aggregate function keywords cannot be updated */

-- 42. What is the role of indexes? What are the two types of indexes? What are their differences? Please write the sample code?

/* A: The index is used to optimize the query and increase the query speed.

Indexes can be divided into clustered indexes (clustered indexes) or non-clustered indexes (non-clustered indexes)

Difference: 1. the page-level pages of clustered indexes store actual data while

Non-clustered index page stores index information, which is stored in the index page.

2. Non-clustered indexes can be created on the data stack or clustered indexes.

3. A unique index is a non-clustered index, but it has a unique constraint than a non-clustered index. Create Index */

-- Example: Create a clustered index on the [name] column of the u_student table create clustered index clu_index on u_student ([name])

-- Example: create a non-clustered index on the [name] column of the u_student table create nonclustered index clu_index on u_student ([name])

-- Example: create a unique clustered index create unique index clu_index on u_student ([name]) on the [name] column of the u_student table

-- 43. What are the SQL statements used to view table indexes? Please write the sample code?

/* A: the SQL statement for viewing the table index is sp_helpindex table name */-- code example sp_helpindex class

-- 44. What are the SQL statements for reorganizing indexes? Please write the sample code?

/* Answer: the SQL statement for sorting indexes again is DBCC indexdefrag (Database Name, table name, index name) DBCC is a database tool set */-- code example DBCC indexdefrag (demo_20091230new, class, pK _ class _ 49c3f6b7)

-- 45. What are the differences between SQL disks and SQL disks?

/* Answer: SQL disk differentiation: Unified expansion Disk Area and hybrid expansion Disk Area.

Unified extended Disk Area: stores only one type of data page.

Hybrid expansion Disk: stores two or more data pages.

-- 46. How many SQL data pages are there? What are the differences?

/* A: There are eight types of SQL data pages. Data Page, index page, text/image page, available space page,

Global allocation ing table, secondary global allocation ing table, index allocation ing table,

Large Capacity difference ing table \ large capacity difference change ing table.

Data Page: stores real data.

Index page: stores index data, such as non-clustered index page.

Text/image page: stores large data types, such as image, text, and ntext.

Available space page: usage of data pages in the database. 0 indicates no use, and 1 indicates use. */

-- 47. What are the three elements of the E-R (entity-relationship) model?

/* Answer: the three elements of the entity-relationship model are Entity, relation, and attribute.

Entity: a real thing. (People or things that customers need to accomplish their business goals)

Relationship: Relationship between entities: one-to-one, one-to-multiple, multiple-to-one, and multiple-to-many (Table sharding required)

Attribute: the features of an object, which can be mapped to columns in the database.

-- 48. Which steps are involved in the database design process?

/* A: the database design process can be roughly divided into four steps:

A) Demand Analysis: investigate and analyze users' business activities and data usage, and find out the types, scopes, quantities, and communication information of the data used during business activities,

Determine the user's usage requirements and various constraints on the database system;

B) Conceptual Design: the conceptual data model of the real world to be described by users;

C) logical design: the main task is to design the conceptual data model of the real world into a database;

D) Physical Design: Determine the storage structure of the database. */

-- 49. What is the meaning of the keyword Union/Union all? Please write the sample code?

/* Answer: the keyword "Union/Union all" indicates connecting the content of two tables for query.

*/-- Sample code: select * from class where class_name = 'China' Union all select * from class where class_name = 'mat'

-- 50. What is the meaning of the keyword exists? Please write the sample code?

/* Answer: the keyword exists indicates whether the subquery contains records that meet the conditions.

If yes, true is returned. Otherwise, false is returned.

*/-- Example code: Find the course information of the 'mat' course select * from Class A where exists (select * from Class B

Where B. class_name = 'mate' and A. ID = B. ID)

-- Sample Code: checks whether the class table exists and deletes it if it exists.

If exists (Select name from sysobjects where name = 'class' and type = 'U') Drop table class go