(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.