Database integrity

define

The integrity of the database is the correctness and compat-abiity of the data. Database integrity is to prevent semantic or incorrect data from entering the database integrity reflects whether the real world is true

meaning

Using proper integrity constraints to standardize data, easy to find and operate, but also convenient for later maintenance and optimization.

Data Model (Relationship)

Structural operational integrity

DBMS integrity mechanism

Provides a mechanism for defining integrity constraints provides a method for integrity checking default handling

Entity integrity (Separate records)

Entity integrity of the relational model

Primary key

Defining entity integrity

Single attributes can define primary codes at column and table levels. Multiple attributes can only define primary codes at table level

DBMS entity integrity checking and default handling

check

The only way to check whether the primary code value in the record is to perform a full table scan. Disadvantages: Time consuming

Default processing

Refuse to operate

Referential integrity (Foreign Key)

Define referential integrity

Sno char(9) Foreign Key (Sno) References Student (Sno), Cno char(4), Grade Smallint References Student (cNO) foreign Key (CNO) references Student (CNO)Copy the code

Refer to integrity checking and breach handling

The student table is the referenced table and the sc table is the reference table

Table to be referenced (e.g. Student)		Reference table (e.g. SC)	Default processing
May compromise referential integrity	< — –	Insert a tuple	Refused to
May compromise referential integrity	< — –	Modify the foreign code value	Refused to
To delete a tuple	— >	May compromise referential integrity	Reject/cascade delete/set to null
Modify the primary code value	— >	May compromise referential integrity	Reject/cascade delete/set to null

Default processing

Refuse to perform (default processing) the cascade operation is set to null

Sno char(9), Cno char(4), grade Smallint, Primary key(sNO, cNO), // Table level definition Reference Integrity Foreign Key (SNO) References Student (SNO) on delete Cascade // Delete the corresponding tuple in the SC table on update Foreign Key (CNO) References Course (CNO) on delete no action// Refuse to delete on if tuples in the course table are inconsistent with those in the SC table Select * from sc where cNO = 'course' and cNO = 'sNO'; select * from SC where cNO = 'course';Copy the code

User-defined integrity

define

User-defined integrity is the semantic requirements that must be met by the RDBMS for a specific application, rather than the application itself

Definition of property constraints

define

Column value null Not NULL Column value unique Checks whether the column value satisfies a conditional expression check

Create table sc(sno char(9) not null, cno char(4) unique not null, Ssex char(2) check(ssex in(' male ',' female ')) Grade smallint check(grade>=0 and grade<=100)Copy the code

Ps: Check whether a field is null ~ WHERE sno is (not) null

Constraint checking and default handling on attributes

Refuse to enforce

Constraints on tuples

In create Table, you can use the check phrase to define constraints on tuples, that is, tuple-level constraints

define

Create table sc(sno char(9), ssex char(2), sname char(10), check(ssex=' female 'or sname not like 'Ms.%') At the beginning)Copy the code

Constraint checking and default handling on tuples

Integrity constraint named clause

The constraint constraints

create

Constraint < integrity constraint name >Copy the code

create table student( sno numeric(6) constraint c1 check(sno between 9000 and 9999), sname char(20) constraint c2 not null, sage numeric(3) constraint c3 check(sage<30), Ssex char(2) constraint c4 check (ssex in(' male ',' female ')), studentKey primary key(sno)) Including primary code constraints (named studentKey and C1, C2, C3, C4)Copy the code

Modify the

You can delete the original constraints and then add new ones

Alter table student drop constraint C4;Copy the code

Alter table student drop constraint c1; alter table student drop constraint c1; alter table student add constraint c1 check (sno between 9000 and 9999); alter table student drop constraint c3; alter table student add constraint c3 check(sage<40> )Copy the code

Integrity limits in the domain (omitted)

assertions

concept

In SQL, you can use the Create Assertion statement in the Data Definition Language to specify more general constraints through declarative assertions

The statement format for creating the assertion

Create Assertion < assertion name > <check clause > Each assertion is assigned a name. The constraints in the <check clause are similar to those in the WHERE clause.

Create Assertion asse_SC_db_num check(60>=(select count(*))// Predicate of this assertion involves the SQL statement from course, SC aggregate operation count Where sc.cno = course.cno adn course.cname=' cname '); // Every time a student takes a course,a tuple (sNO, CNO, NULL) is inserted into the SC table, and asse_SC_DB_num assertion is triggered. If the number of people taking a database course has exceeded 60, check returns "false" and the insert into the SC table is rejected.Copy the code

Delete the statement format of the assertion

Drop assertion < assertion >

The trigger

Transact – An introduction to the SQL language

Quick start: www.jianshu.com/p/a7bb06705… Systematic learning: www.w3cschool.cn/t_sql/t_sql…

concept

T-sql, also known as Transact-SQL, is an enhanced version of SQL on Microsoft SQL Server. It is the main language for applications to communicate with SQL Server. T-sql provides the DDL and DML capabilities of standard SQL, plus extended functions, system stored programs, and programming constructs such as IF and WHILE to make programming more flexible.

Common statements

Two ways to comment

1. “–” ep:use st // Select database go// The batch process ends
2. “—“
   
   Used in other languages
   
   

Batch GO

A complete set of data and instructions passed to the server during batch processing. All statements in a batch as a whole are analyzed, compiled, and executed in groups

The print command

Format: print ‘String to output’ Function: send the user specified information

Use mydata; GO If Exists(select cname from course where cname=' OS ') print 'GOCopy the code

variable

Global variables are defined and maintained by the system and cannot be created or modified by users. Scope all applications that have been applied. @@name ep: @@Servername SELECT @@Servername AS Local server (1) Local variables Local variables are user-defined and are commonly used in batch processing, stored procedures, and triggers. Their scope is only within the program. Local variables must be declared before they are used. T-sql also provides assignment statements for local variables. Declare variable declaration statement, syntax format:

Declare @variable 1 [AS] datatype, @variable 2 [as] datatype... declare @variable 1 [as] datatype...Copy the code

Local variable names must start with @. As may omit the assignment initial value NULL

There are three ways to assign a local variable:

declare@variable 1 [as] datatype = value, @variable 2 [as] datatype = value...Copy the code

Select * from (select);

Select @variable 1 = expression 1, @variable 2 = expression 2...Copy the code

Using the select command, you can assign values to multiple variables at once. The expression can be a common value or a query result. When the expression is the column name of a table, the expression is similar to the column alias used in a common query. You can use subqueries to return multiple values from a table at a time. If the result of a query is multiple rows, only the corresponding column value of the last row is assigned to the variable, unlike PL/SQL, which does not allow multi-row query results to be assigned to a variable

③ The syntax of the set assignment statement is:

setAt sign variable = expressionCopy the code

The basic usage is the same as select, except that a set assignment statement can assign to only one variable, whereas a SELECT statement can assign to multiple variables

[example]declare @sumsal as numeric(10,2),@dno as tinyint
select @dno = deptno,@sumsal = sum(sal) 
from emp 
where deptno = 10
group by deptno
print cast(@dno as varchar)+'：'+cast(@sumsal as varchar)
Copy the code

The operator

Arithmetic operators: +, -, *, /, % more than (a) : string operator + (connection) comparison operators: =, >, > =, <, < =, < > () is not equal to,! >(not greater than),! <(NOT less than) logical operators: NOT, AND, OR, ALL, ANY(OR SOME, ANY), BETWEEN… AND, the EXISTS (), IN (at) AND the LIKE (matching) : the bitwise operators & (AND), | (or), ^ (the bitwise xor) unary operator: + (positive) AND – (negative), ~ (DE) bitwise assignment operator: = (equal to)

function

A function is a set of T-SQL statements that perform a particular function and return the result of the processing, which is called the return value and the process the function body. Functions are divided into system built-in functions and user automatic functions. SQL Server provides a large number of system built-in functions, which can be divided into the following categories: mathematical functions, string functions, date functions, convert functions, aggregate functions. (1) Mathematical functions t-SQL provides common mathematical functions as follows:

Abs () : returns absolute value round(numeric expression, length,[, type]) : rounds to the specified length or precision. Power (m,n) : returns the NTH power of m trunc() : truncates the number to the specified number % : Finds the remainder, SQL Server does not mod(m,n), but uses M %n insteadCopy the code

(2) String function

Ltrim (STR,substr)/rtrim(STR,substr) : STR indicates the string to operate on, and substr indicates the substring to trim. If space is trimmed, substring(STR,position,length) can be omitted: Replace (STR,search_str,rep_str) : Replaces a substring in a string. Search_str represents the substring to search for, rep_str represents the target string to replace left(STR,n) : returns a specified number of characters from the left side of the string len() : evaluates the length of the stringCopy the code

(3) Date and time functions

The following date functions are provided in T-SQL:

Getdate () : returns the current date and time year(date) : returns the integer for the “year” part of the specified date Month (date) : returns the integer for the “month” part of the specified date Day (date) : returns the integer for the “day” part of the specified date datePart (date element, date) : Datename (date element, date) : returns the datename of the date element at the specified time as a string datediff(date element, date 1, date 2) : Returns the difference between two days and converts it to the form dateAdd (date element, number, date) : Returns the new date of “date” plus “number” in the date unit given by “date element”

When it comes to date-time constants, SQL Server recommends using string formats independent of dateFormat and locale Settings. Generally, such string constants conform to the following two forms:

No delimiter is used between dates. The format is YYYYMMDD [hh:mi:[: SS][, MMM]], for example, ‘20070703’, ‘20070703 17:53:00.997’. It is in the standard format of YYYY-MM-DDTHH :mi:ss[.mmm]. The date and time are separated by hyphens (-) and cannot be omitted, for example, ‘2007-07-03T17:53:10’.

(4) Data type conversion function conversion methods are implicit conversion and explicit conversion. Implicit conversion is when SQL Server automatically converts data from one data type to another, invisible to the user. Explicit conversions use the convert function, which casts an expression of one data type to an expression of another. The two data types must be convertible; for example, a char value can be converted to binary, but not to image. The main purpose of this function is to convert numeric or date data to a string, whereas the conversion of a string containing only numbers to numeric data is usually implicit. The fourth argument to the function,n, is optional for date-time data types and character data type conversions.

Program control statement

If else begin end begin < multiple SQL statements > end while Example:

Use mydata Go while(select avg(grade) from sc)<90 begin update sc set grade=grade+5 if(select max(grade) from sc)>80 Break else continue end printCopy the code

Trigger concept

Triggers are special event-driven procedures defined by users on relational tables

Defining a trigger

Stay tuned forCopy the code