(Torch) MSSQLServer database Case Study

(Tornado) MSSQLServer database case study create database: CREATEDATABASETDB Database Name ON (NAMETDB_dat, the logical file name must be unique FILENAMED: mydbTDB_dat.mdf, path and file name SI used by the operating system when creating a file

(Torch) ms SQL Server DATABASE case study CREATE DATABASE: CREATE DATABASE TDB // DATABASE NAME ON (NAME = TDB_dat, // After the logical file name is created, the file name referenced in the statement must be unique FILENAME = 'd: \ mydb \ TDB_dat.mdf ', // The path and file name SI used by the operating system to create a file

(Torch) ms SQL Server database Case Study

Create a database:

Create database tdb // DATABASE Name

ON

(

NAME = TDB_dat, // The logical file NAME must be unique after the database is created.

FILENAME = 'd: \ mydb \ TDB_dat.mdf ', // path and file name used by the operating system to create a file

SIZE = 10, // specify the initial SIZE of the data file or log file (MB by default)

MAXSIZE = 50, // specify the maximum size of the data file or log file, if the file size is not specified, the disk will be full (the file size specified by the UNLIMITED keyword is unrestricted-only limited by the disk size and space)

FILEGROWTH = 5 // specifies the file growth history. The file value cannot exceed the MAXSIZE value. 0 indicates no growth. If this parameter is not specified, the default value is 10%; data File growth mode growth [growth ru θ] n. growth; development; growth; planting

)

LOG ON

(

NAME = TDB_log,

FILENAME = 'd: \ mydb \ TDB_log.ldf ',

SIZE = 10 MB,

MAXSIZE = 50 MB,

FILEGROWTH = 5 MB

)

Delete database logs and shrink the database:

1. Clear logs
Dump transaction database name WITH NO_LOG

2. truncate transaction logs:
Backup log database name WITH NO_LOG

3. Compress database files (if not compressed, the database files will not be reduced
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete
-- Shrink Database

Delete database:

Drop database testdb, TDB...

1. view the structure of the Products table

EXEC SP_HELP Products

2. Insert data

Insert into products (name, sex, ages) values ('xu ', 'male', 24)

Supplement: insert a table from a database to another table in the SQL database. If the two tables have the same structure, insert into data2.table2 select * from data1.table1

If the structure is different or you want to specify fields, USE insert into data2.table2 (Field 1, Field 2, field) select Field j, field k, field m from data1.table1

SQL copies a table in the database to another database:

Select * into beifeng trade. dbo. Category from [Northwind]. dbo. Category

Or

Select * into Northwind trade. Dbo. cat from [Northwind]. dbo. Category

3. Update Data

Update products set productprice = productprice-productprice * 0.5 where or

Update products set productprice = productprice-productprice * 0.5 where id = 1

1. update and select are used to modify SQL statements in batches:

Update B set B. TagValue = a. TagValue from [Nx_TagData] as B, (select * from [Nx_TagData] where TagCode = 205911

And CollectTime> = '2017-11-22 00:00:00. 000' and CollectTime <= '2017-11-23 00:00:00. 000') as

Where B. TagCode in (205915,205920, 205922,206539, 205908,205913, 205917,205918, 205809,205910, 206285, 206060)

And B. CollectTime = a. CollectTime

4. delete data

Delete from Products where productname = 'v8'

5. delete all the data in the table

Delete from products or

Truncate table products

6. Add a field to the products table

Alter table products

Add column producttype varchar (10)

7. Modify the length of the producttype field in the products table

Alter table products

Alter column producttype varchar (50)

8. Delete the products table

Drop table products

Note: drop table name [,…] Multiple tables can be deleted.

9. annotation identifier

-- (Dual-connection )/*... */(Forward slash-asterisk character pair)

10. Create a table and the primary key constraint (a table can only have one primary key constraint)

Create table t_p/* student */

(

P_id char (5) not null,

P_name char (8) not null,

Constraint pk_tp_id primary key (p_id) -- create a primary key Constraint pk_tp_id as the Constraint name

)

Create table rec/* instructor */

(

R_id char (5) not null,

R_name char (8) not null,

R_DATE datetime not null,

Constraint pk_rec_id_name primary key (R_id, name)-R_id and R_name combination Constraints

)

Or

Alter table rec-(add constraints)

Add Constraint pk_rec_id_name primary key (R_id, name)-R_id and R_name combination Constraints

Tip:

(1) The composite primary key can also create table constraints when creating a table as in the rec info table, but cannot create column-level constraints as in the t_p info table.

(2) When adding the primary key constraint as a table modification, the corresponding columns must have non-null constraints when creating the table.

Alter table product

Add constraint pk_id primary key nonclustered ([id] ASC)

Note: nonclustered non-CLUSTERED index (add NON before the CLUSTERED index of CLUSTERED to change to NON-CLUSTERED index)

11. default Constraints

Create table rec

(

R_id char (5) not null,

R_name char (8) not null default getdate (), -- the default value of this column is the current date of the system.

R_date datetime not null

)

Or

Alter table rec

Add constraint df_date defaut getdate () for R_date

12. check Constraints

Create table rec

(

R_id char (5) not null,

R_name char (8) not null,

R_sex char (2) check (sex = 'male' or sex = 'female '), -- the check constraint value must be either the male or female values.

R_DATE datetime not null

Rid char (18 ),

Constraint ck_rec_rid check (len (Rid) = 18 or len (Rid) = 15)-check the length of the ID card value can be either 18 or 15.

)

Or add check constraints.

Alter table rec

Add Constraint ck_rec_rid check (len (Rid) = 18 or len (Rid) = 15)

And

Alter table rec

Add Constraint ck_rec_sex check (sex = 'male' or sex = 'female ')

13. unique constraint

Create table rec

(

R_id char (5) not null,

R_name char (8) not null,

R_sex char (2) check (sex = 'male' or sex = 'female '), -- the check constraint value must be either the male or female values.

R_DATE datetime not null

Rid char (18) unique,

)

Or

Alter table rec

Add constrater un_Rid unique (pid)-the ID card number is unique and does not appear again

14. foreign key constraint of foreign key

The role is to associate the student table with the instructor table's information. The t_id column and the R_id column define the foreign ke constraint.

Create table courses

(

T_id char (5) not null foreign key references t_p (t_id), -- column-level constraints associated with the t_p table

R_id char (5) not null,

Grade char (16 ),

Class char (10 ),

Constraint fk_course_rec_R_id foreign key (R_id) references Rec (R_id)-associate table-level constraints with rec tables

)

Or

Alter table course

Add Constraint fk_course_rec_R_id foreign key (R_id) references Rec (R_id)-associated with rec table

Alter table course

Add constraint fk_course_t_p_t_id foreign key (t_id) references t_p (t_id) -- associate with the t_p table

Knowledge point:

(1) columns in the relevant table corresponding to the t_id and r_id columns of the foreign key column (the t_id column in the student table and the r_id column in the teacher table) must be defined as the primary key constraint or unique constraint.

(2) When a foreign key is created, the data type and length of the t_id and r_id columns must be the same as the primary key columns in the corresponding table (the t_id column in the student table and the r_id column in the teacher table) the data type and length are consistent or can be automatically converted by SQL Server.

15. Delete Constraints

Delete the check constraint named ck_rec_rid in the R_id column.

Alter table t_p

Drop constraint ck_rec_rid

You can use the following command to find the name of the constraint if no name is specified during creation, for example, the server space or the check constraint created on the sex column in the student information table.

Exec sp_constraint t_p or Exec sp_help constraint t_p

Find the expected constraint name after executing the preceding statement.

Again

Alter table t_p

Drop constraint ck_t_p_sex_1367E606

16. Create an index

(1) Non-clustered index-create a clustered index named studid_ind on the stud table

Create clustered index studid_ind on studid)

Note: A table has only one clustered index.

(2) Non-clustered index-create a non-clustered index named studfullname_ind on the stud table

Create unique index studfullname_ind on stud (fname desc, lname) unique index

Create nonclustered index studfullname_ind on stud (fname desc, lname) Non-clustered index

Note: non-clustered unique index desc descending order (remove non as clustered index)

You can create indexes for multiple columns separated by commas (,).

17. view the index of the stud table

Select sp_helpindex stud

18. Use Indexes

Select * from stud (index = studid_ind) where id = '100

19. delete an index

(1) drop index stud. studid_ind

20. Modify the stud table and set studid as the primary key.

Alter table stud

Constraint pk_studid primary key clustered (studid)

An error is returned when you directly Delete the pk_studid index of the primary key constraint.

21. Re-Indexing

(1) Rebuilding the pk_studid Index

Dbcc dbreindex (stud, pk_studid)

Note: Mark of dbreindex reconstruction by dbcc index reconstruction command

(2) rebuild the pk_studid index and set the fill factor to 50%.

Dbcc dbreindex (stud, pk_studid, 50)

(3) Rebuilding the studname_ind Index

Create index studname_id on stud (fname, lname) with drop_existing

Tip:

Because non-clustered indexes contain clustered indexes, non-clustered indexes must be rebuilt when clustered indexes are removed. If clustered indexes are re-built, non-clustered indexes must be re-built to use new indexes.