SQL Server database design advanced query

-------------------------------------the first chapter of database design-------------------------------------

Software Development cycle:
(1) Demand analysis
(2) Summary design
(3) Detailed design
(4) coding
(5) Integration testing
(6) On-line operation
(7) Post-maintenance

1, database design steps:
(1) Collecting information
(2) Identify objects (determine which tables are in the database)
(3) Identify the properties of an object (determine the example in each table)
(4) Identifying the relationship of an object (two table relationships)
4 Types of relationships:
1 to 1
1-to-many
Many-to-1
Many-to-many


2,e-r Entity Relationship Diagram
Rectangle representation, entity set (i.e. table)
Ellipse representation, attribute (that is, column)
Diamond representation, relationship set (i.e. relationship)

3, the three main paradigms of the database: (not necessarily in the actual work to follow)
Criteria for measuring Database design excellence

First paradigm: Ensure that data in each column is not re-divided
Second paradigm: Require that each table describe only one thing
The third paradigm: Each column is required to have a direct relationship with the primary key, that is, to ensure that each column in the table is directly dependent on the primary key






--------------------------------------the implementation of the second chapter of the database------------------------------------------


1, Database operations
(1) Creating a database with a statement
Create DATABASE name
On primary{--Set database file
Logical name
Name= ' logical name ',--without suffix. mdf
Set Initial Size
SIZE=3MB,--sql server2014 minimum not less than 5MB
Incremental
FILEGROWTH=1MB,
The path includes the file, which is the physical name
Filename= ' path ',
Maximum value of growth
Maxsize= ' 100MB '
}
Log on{--Set up the thing log file
The same as on
}

Cases:
Create DATABASE Bbsdb
On primary
(
Name= ' Bbsdb ', SIZE=3MB,FILEGROWTH=1MB,
Filename= ' E:\SQL sever\sql Server advanced programming \ Database \bbsdb.mdf '
)
Log on
(
Name= ' Bbsdb_log ', SIZE=1MB,FILEGROWTH=1MB,
Filename= ' E:\SQL sever\sql Server advanced programming \ Database \bbsdb_log.ldf '
)






(2) Deleting a database
Drop database name


(3) Determine if the database exists
1>sysdatabases table: Saved in the master database, this table holds information for all databases
sysobjects table: System tables for databases

Example: Determine if the database exists, if there is output a, otherwise output b
if (exists (SELECT * from sysdatabases where name= ' students '))
Begin
Select ' A '
End
Else
Begin
Select ' B '
End


2, table operation
(1) Create a table
Identity identity Column
Primary KEY Primary Key
Not NULL is not empty

CREATE TABLE Table name
(
ID int NOT NULL PRIMARY key identity,
Name varchar () is not NULL,
..................................
)

(2) Delete a table
DROP table Name






3, constraint
(1) PRIMARY KEY constraint primary key
(2) Checking the constraint check
(3) Default constraint defaults
(4) Primary FOREIGN KEY constraint foreign key

1> Adding constraints:

To add a PRIMARY KEY constraint:
ALTER TABLE table name
Add constraint PRIMARY KEY constraint name (canonical "Pk_" start)
Primary key (column name)

To add a check constraint:
ALTER TABLE table name
The add constraint checks the constraint name (the canonical "Ck_") check (expression);

Add a default value constraint
ALTER TABLE table name
Add constraint Default constraint name (beginning with canonical "Df_")
Default (' default value ') for column name


To add a primary foreign KEY constraint:
ALTER TABLE name (foreign key table)
Add constraint foreign KEY constraint name (canonical "Fk_" start)
Foreign key (foreign key name)
References primary key table name (primary key column name)

Note:
You can also add multiple constraints to a table at the same time


2> Deleting a constraint
ALTER TABLE table name
Drip constraint constraint name





4, 6 steps to create users and delete users using commands
(1) Create:
First step: Create a SQL Server login user
EXEC addlogin ' login name ', ' password '
Part Two: Creating the user for the corresponding database
EXEC grantdbaccess ' login name ', ' username '
Step three: Add user action permissions on the specified table
Grant Insert,upadat,select on table name to user name
(2) Delete
First step: Revoke permissions
Revoke permissions on table name from user name
Step two: Delete the database user
EXEC sp_dropuser ' user name '
Step Three: Delete login account
EXEC sp_droplogin ' login name '





---------------------------------------------Chapter III T-SQL programming-------------------------------------------------

1, local variables (custom variables)
DECLARE @ variable name data type .... Later, you can define multiple

2, assigning values to variables
(1) Set assignment
SET @ variable = value
(2) Select Assign Value
SELECT @ variable = column name from table name
Note: The value assigned to a variable is the value of the last statement if the result of this assignment is only one of the data found by the user, if the query results are multiple statements.

3, Output variable
(1) Print variable: output as Text
(2) Select variable: output as a TABLE element


4, global variables
Common:
@ @error Gets the error number of the previous statement
@ @identity Gets the last identity value generated
@ @rowcount The number of rows affected by the previous statement
Not used:
@ @language The name of the language currently in use
@ @max_connections The maximum number of simultaneous connections that can be created
@ @servername The name of the local server
@ @servicname The name of the SQL service on the local computer
@ @timeticks The number of microseconds per tick on the current computer
@ @version The version number of the current SQL Server



5, Logic control statement
if (condition)
Begin

End
Else
Begin
End



Cases:
DECLARE @username varchar (10)
Set @username = ' Sipo '
if (@username = ' super ')
Begin
print ' hehe '
End
Else
Begin
print ' whining ... ‘
End





6,while Loop Statements
While
Begin

End

Cases:
DECLARE @i int
Set @i=0;
while (@i<10)
Begin
Print @i
Set @[email protected]+1
End


7,case. End multi-branch structure
Case
When condition 1 then result 1
When condition 2 then result 2

Else
Other conditions
End

Note: Case...end cannot be used alone and must be nested in the SEL statement



8, the grant of the rationale
Keywords: go


---------------------------------------the fourth Chapter advanced Inquiry-------------------------------------------

1, sub-query features:
(1) When the subquery returns only one value, you can use the comparison operator when the subquery has more than one value.
(2) The column queried in the subquery can only be one column
(3) The type in the subquery must be the same as the type of the condition
(4) Subqueries can be applied in insert,update,select,delete statements
(5) If you use ORDER by in a subquery, you must include the top keyword

2, simple sub-query
Example: SELECT * from Stuinfo where scoreid= (select ID from Stuscore where score=60)

3,in and not in sub-queries
Example: SELECT * from Stuinfo where Scoreid in (select ID from Stuscore where score>60)
Example: SELECT * from Stuinfo where Scoreid not in (select ID from Stuscore where score>60)

4,exists and NOT EXISTS
Check that the queried data exists, that there is a return of true, that there is no return false
Usage:
EXISTS (statement)
Not EXISTS (statement)





-------------------------------------------fifth things, indexes, and views-------------------------------------


1, things
(1) Things as a whole are either executed or not executed.
1> Start Things
BEGIN TRAN (or BEGIN TRANSACTION)

2> Submitting Things
Commit Tran
3> rolling back things
Rollback Tran

(2) The properties of a thing:
Atomicity: Is a whole, can no longer be divided, either is executed, or neither is executed
Consistency: The state of the data remains the same until the start of the thing and the end of the thing
Isolation: Operations on the same data, things are isolated from each other, either a first execution, or B first execution
Permanence: The impact of things on data is permanent


2, Index
(1) Classification:
1> Clustered index: (primary key index belongs to clustered index)
Characteristics:
* Each table can have only one clustered index
* The order of index pages is consistent with data page data
* Creating a primary key automatically creates a clustered index
* Especially effective when querying range values

2> Nonclustered Indexes
Each table can have a maximum of 249, because no table can have a maximum of 249 columns
Select Criteria for indexed columns:
* Columns that are frequently used as query criteria
* Columns with more unique values
* Columns that are not updated frequently
(2) Creating an index using SQL statements
Nonclustered indexes:
Create index name on table name (column name 1, column Name 2, ...)
Clustered index:
Create clustered index name on table name (column name 1, column Name 2, ...)



3, view
To create a view:
Create view name (typically beginning with vi_)
As
Content

Working with Views:
SELECT * FROM view name



--------------------------------------------the 6th chapter of the stored procedure------------------------------------------------


(i), stored procedures
1, the advantages of the stored procedure:
(1) Faster execution speed
(2) Allow modular programming
(3) Improve system security
(4) Reduce network liquidity

2, common system stored procedures

Sp_database Display all database information in the current system
sp_helpdb Show details for all or specified databases
Sp_renamedb Modify Database name example: Sp_renamedb ' old name ', ' new name '
Sp_tables shows an example of all tables under the current database: Sp_tables or sp_tables ' table name '
Sp_columns Display a table column information example: Sp_columes ' table name '
sp_help View all information for a table: sp_help or sp_help ' table name '
Sp_helpconstraint to view a table's constraint example: sp_helpconstraint ' table name '
Sp_helpindex Viewing a table's index example: sp_helpindex ' table name '
Sp_stored_procedures shows all stored procedure examples under the current database: Sp_stored_procedures
Sp_password Modify the currently logged on user's password example: sp_password ' old password ', ' New password '
SP_HELPTEXT displays default values, unencrypted stored procedures, user-defined stored procedures, triggers, or attempted actual text examples: sp_helptext


3, custom stored procedures (typically beginning with proc_)
Grammar:
CREATE PROCEDURE Name
Parameter 1, parameter 2,...... Output parameter outputs
As
Content


(ii), trigger
Automatically triggers when you perform additions and deletions
1, Advantages:
(1) triggers are automatically executed
(2) The table can be Cascade modified
(3) Enforce constraints can be implemented

2, two important tables
Inserted table: A temporary table in memory with the latest inserted data, the modified data in the inserted table
Deleted table: Temporarily save the deleted records, storing the data before the change

3, use of triggers
Insert trigger: Insert
Modify Trigger: Update

Cases:
Inserted trigger:
Create trigger trigger name on table name
For insert
As
declare @id int, @count int
Select @id =scoreid from inserted
Update Stuscore set score=20 where [email protected]

Modified Trigger:
Create trigger trigger name on table name
For update
As
DECLARE @id int
Select @id =scoreid from deleted
Update Stuscore set score=50 where [email protected]



To delete a trigger that exists:
Drop TRIGGER Trigger Name

SQL Server database design advanced query