-- 1. What file does SQL consist? * A: 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? * A: SQL data files are classified into primary and secondary data files. There is only one primary data file. There are 0-N extensions for the second-level data files: primary data files
-- 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. There are 0-N extensions for the second-level data files: primary data files
-- 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. Use code to create 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)