SQL Server database entry-level learning Summary

Figure 1 wins the 10 Statement: SQL Server database Summary

A rough summary

After a period of study, I also had some knowledge about databases.
Databases are basically composed of tables, relationships, and operations. For Beginners, the first thing to learn is:

1. How does a database store data?
Table, constraint, trigger
2. How does a database operate data?
Insert, update, delete T-SQL function Stored Procedure trigger
3. How does the database display data?
Select

SQL Server database learning Summary

1. SQL Basics
SQL Server2000 installation and configuration, server startup and stop, Enterprise Manager, query Analyzer
First-generation databases-mesh databases and hierarchical databases; second-generation databases-relational databases
Database (DB); database management system (DBMS); database system (DBS)
SQL Server 2000 provides different editions: Enterprise Edition, Standard Edition, individual edition, and development edition.

Data Types in SQL Server: INTEGER: int, smallint, tinyint, bigint; floating point: real, float, decimal; binary: binary, varbinary; logic: bit; character: char, nchar, varchar, nvarchar; text and graphics: text, ntext, image; Date and Time: datetime, smalldatetime; currency: money, smallmoney

Create and delete databases; create, modify, and delete database tables

Data Integrity: entity Integrity: Primary Key, Unique Key, Unique Index, Identity Column; domain Integrity: Default, Check, Foreign Key, Data type, Rule; reference Integrity: Foreign Key, check, Triggers, Procedure; User-Defined Integrity: Rule, Triggers, Procedure; all column-level and Table-level constraints in Create Table

SQL Server has five constraints: Primary Key Constraint, Default Constraint, Check Constraint, and Unique Constraint), Foreign Key constraints (Foreign Key Constraint ).

Relationship Diagram

Database Design Steps: requirement analysis, conceptual structure design, logical structure design, database physical design, database implementation, Database Operation and Maintenance

Relationship between two entities: one-to-one (), one-to-many (1: n), and multiple-to-many (m: n)

Entity Relationship Model-E-R Diagram

Database standardization: streamline the database structure into the simplest form; Delete redundant columns from the table; identify all data dependent on other databases.

Database three paradigms: The first paradigm is non-repeated columns; the second paradigm is that non-primary attributes are not partially dependent on primary keywords; the third paradigm is that attributes do not depend on other non-primary attributes.

2. SQL statements
Structured Query Language )"

Four parts of SQL:
Data Definition Language (DDL) is used to define the Data structure: create, alter, and drop.
Data Control Language (DCL) is used to Control database component access permissions, access permissions, and other commands: grant, revoke.
Data Manipulation Language (DML) is used to manipulate Data commands in a database: insert, update, and delete.
Data Query Language (DQL) is a command used to Query Data in a database: select.

Operators in SQL: Arithmetic Operators, bitwise operators, comparison operators, logical operators, wildcard operators, string connectors, and value assignment operators

3. Query
Simple query, using the TOP clause
Sort query results order
Query where with conditions, using arithmetic expressions, using logical expressions, using the between keyword, using the in keyword,
Fuzzy query like
Aggregate functions: sum (x), avg (x), min (x), max (x), count (x), count (*)
Query group by and having clauses by group
Distinct keyword
Column alias
Select top 6 * from sales order by qty desc
Select au_id, au_fname, au_lname from authors where state in ('ks ', 'CA', 'mi ')
Select au_fname, au_lname, phone from authors where au_id like '72 [234]-%'
Select type, sum (price), avg (price), count (*) from titles group by type having type in ('business', 'sycheology ')

Simple subqueries: Nested subqueries and related subqueries. The select statement of a subquery cannot use the order by clause, and the roder by clause can only sort the final query results.
Nested subquery: During the execution process, the subquery is executed first, and the results of the subquery are not displayed. Instead, the subquery is passed to the outer query as a condition for the outer query, and then the outer query is executed, and the result is displayed.
The execution of nested subqueries does not depend on the outer query. The subquery is executed only once.
Subqueries with comparison operators, subqueries with in and not in, and subqueries with any or all
Related subquery: The subquery is executed once for each row of the outer query. The outer query passes the value of the column referenced by the subquery to the subquery.
The execution of related subqueries depends on the outer query. The subquery must be executed repeatedly.
Related subqueries with exists and not exists.
Multi-table join query: inner join, outer join, self join, and cross join)
Create a new table in the query: The select into statement first creates a new table and then fills the new table with the query results.
Table alias
Select coursename from course where courseid in (select distinct courseid from grade where grade> 10)
Select studname from student where sudbirthday> any (select studbirthday from student where class = 'info') and class <> 'info'
Select studname from student where exists (select * from grade where studid = student. studid and courseid = '01 ')
Select stud1. * from student as stud1 join student as stud2 on stud2.studname = 'mm' and stud1.studsex = stud2.studsex
Select * into girls from student where studsex = 'M'

4. Views, indexes, and transactions
A view is a virtual table or a query table exported from one or more data tables (Basic Tables). It is an important mechanism provided by the relational database system for users to observe data in the database from multiple angles.
Benefits of a view: It simplifies user operations and provides security protection for confidential data.
When creating a view, the view name exists in the sysobjects table. Information about the columns defined in the view is added to the syscolumns table, and information about the view relevance is added to the sysdepends table. In addition, the text of the create view statement is added to the syscomments table.
When inserting data into a table through a view, if the insert Statement List contains columns not selected in the view and columns that are not allowed to be null, this operation is not allowed.
Create view: create view view_employee as select emp_id, fname, lname from employee
Use view: select * from view_employee
Modify view: alter view view_employee as select emp_id, fname, job_id from employee where job_id> 10
Delete view: drop veiw view_employee
View view structure: exec sp_help view_employee
View definition information: exec sp_helptext 'view _ employee'

An Index provides a way to quickly access the data rows of a table based on the values of one or more columns. The Index provides the logical order of the table.
The clustered index sorts and stores the data rows based on the key values of data rows in the table. When a data table uses a column as a keyword to create a clustered index, the data rows in the table are stored in the order of the column (clustered index key. Each table can have only one clustered index.
Non-clustered indexes have a completely independent structure of data rows. A table can have multiple non-clustered indexes.
Create clustered index: create clustered index studid_ind on studid)
Create a non-clustered index: create unique index studfullname_ind on stud (fname desc, lname)
Delete index: drop index stud. studid_ind
View the index on the stud table: exec sp_helpindex stud

A transaction is a mechanism of operation sequence. It contains a set of database operation commands, and all commands are used as a whole to submit or cancel Operation requests to the system.
Transaction Features: Atomicity, Consistenty, Isolation, and Durability ).
Transaction category: displays transactions, hidden transactions, and automatically committed transactions.

Create, use, modify, and delete views, indexes, and transactions

5. Transact-SQL programming
Global variables: defined and maintained by the system, whose names start @
Local variables: defined by the user and assigned values. Their names start with @ characters.
Output statement: print
Logical control statement: begin... end; break; case; continue; goto; if... else; return; while
Common functions: rowset functions, Aggregate functions, and scalar functions
Conversion functions: convert (dt, e, s), cast ()
Mathematical functions: absolute abs (n), rounded up to ceiling (n), rounded down to floor (n), specified power (n, y), rounded to round (n, length), evaluate the sign (n), square root sqrt (n)
Date and Time Functions: dateadd (datepart, num, date), datediff (datepart, date1, date2), datename (datepart, date), datepart (datepart, date ), getdate (), year (date), month (date), day (date)
String functions: lower (e), upper (e), left (e, I), right (e, I), replace (s1, s2, s3) replace 2 in 1 with 3, replicate (e, I) for the specified number of times, stuff (s1, start, length, s2) with 2 to replace the specified position in 1, substring (expression, start, length)
Metadata functions: db_id ('database _ name'), db_name (datebase_id), object_id ('obj _ name'), object_name (obj_id), col_length ('table ', 'column'), col_name (table_id, col_id)
Aggregate functions: avg (expr), count (expr), count (*), max (expr), min (expr), sum (expr)
Select au_lname, au_fname, contory =
Case state
When 'ut' then 'utah'
When 'CA' then 'california'
Else 'World'
End, city from authors order by state desc

While (select avg (price) from titles) <30
Begin
Update titles set price = price * 2
If (select max (price) from titles)> 50 break
Else continue
End
Print 'price too high'

Begin
Insert into jobs values ('a, 80,234)
If @ error <> 0 print 'data insertion failed'
Else goto M
End
M: print 'data inserted successfully'

6. cursor
A cursor is a mechanism that extracts a record from a result set containing multiple data records. Convert a batch operation into a row operation and perform operations on a row in the result set.
Declare author_csr cursor read_only for -- Define read-only cursor
Select au_fname, au_lname from authors where state = 'CA' order by au_fname, au_lname
Declare @ lname varchar (20), @ fname varchar (20) -- define variables
Open author_csr -- open the cursor
Fetch next from author_csr into @ lname, @ fname -- execute a data read operation
While @ fetch_status = 0 -- the cyclic cursor reads data.
Begin
Print 'author name: '+ @ lname + ''+ @ fname
Fetch next from author_csr into @ lname, @ fname
End
Close author_csr -- close the cursor
Deallocate author_csr -- release cursor

7. Stored Procedure
Stored Procedures (stored procedure) are similar to functions in C language. They are a set of SQL statements for specific functions and are compiled and stored in the database. You can specify the name of a stored procedure to give a parameter.
Common system stored procedures: sp_database, sp_helpdb, sp_renamedb, sp_tables, sp_column, sp_help, sp_helpconstraint, sp_helpindex, sp_stored_procedure, sp_password
Create a stored procedure:
Create procedure book_num (@ book_name varchar (26), @ starttime datetime, @ endtime datetime, @ total int output)
As
Select @ total = count (jy. askbookid) from book, jyls jy where bookname like @ book_name and book. isbn = jy. isbn and jy. starttime >=@ starttime and endtime <= @ endtime
Use stored procedures:
Declare @ book_name char (26), @ total int
Set @ book_name = 'object-oriented Analysis and Design'
Exec book_num @ book_name, '2017-01-01 ', '2017-11-01', @ total output
Select @ book_name as bookname, @ total as num

8. triggers
A trigger is a special type of stored procedure that is triggered by practice and executed.
The trigger is mainly used to achieve complex integrity and data consistency that cannot be guaranteed by the primary key and foreign key. Other functions: Strengthen constraints, track changes, cascade intermodal lines, and call stored procedures.
SQL Server 2000 supports two types of triggers:
After trigger: A trigger must be executed only after an operation is executed and can only be defined on a table.
Instead of trigger: indicates that the operation defined by the trigger is not executed, but the trigger itself. You can either define a table or a view, but you can only define one instead of trigger for the same operation.

Working principle:
When an insert trigger is triggered, new data rows are inserted into the trigger table and inserted Table. The trigger checks the inserted Table to determine whether to execute the trigger action or how to execute it.
When an update statement is executed on a table with a trigger defined, the original row is moved to the deleted table, and the update row is moved to the inserted Table. The trigger checks the deleted table, inserted Table, and updated table to determine whether multiple rows are updated and how to execute the trigger action.
When the deleted trigger is triggered, rows deleted from the affected table will be placed in a special deleted table.

Create trigger update_smoke_t_sale on smoke_t_sale for update
As
Declare @ newsalenum int, @ smokeproductname varchar (40)
Select @ newsalenum = salenum from inserted
Select @ smokeproductname = smokeproductname from inserted
If update (salenum) -- determines whether to update
Begin
Update smoke_t_sale set saletotalprice = @ newsalenum * saleprice where smokeproductname = @ smokeproductname
Insert into smoke_log (logContent) values ('updated successfully ')
End
Else
Print 'not updated'

9. Advanced Database Management
The SQL Server security architecture has four levels: client operating system security, SQL Server login security, database use security, and data object use security.
SQL Server Authentication Mode: windows Authentication Mode and Hybrid Authentication Mode (windows Authentication and SQL Server Authentication)
Logon account: the account and password used by the user to log on to (CONNECT) the SQL Server.
Role management: Server role (responsible for managing and maintaining SQL Server groups); database role (a set of user accounts and groups with the same access permissions for a database)
Database User: For each login account that requires access to the database, you must create an access account for the database in the database to be accessed and associate it with the Logon account link to access the database.
Permission management: whether a user can access database resources. Permissions include statement permissions, object permissions, and suggestion permissions.

Grant permissions:
Grant statement [...] to security account [...]
Grant permission [...] on Table or view [(column [,...])] | on Stored Procedure | on User-Defined Function to security account [,...]
Permission denied:
Deny statement [...] to security account [...]
Deny permission [...] on Table or view [(column [,...])] | on Stored Procedure | on User-Defined Function to security account [,...]
Revoke permissions:
Revoke statement [...] from security account [...]
Revoke permission [...] on Table or view [(column [,...])] | on Stored Procedure | on user-defined function from security account [,...]

Backup and recovery:
Before backing up a database, you must create a backup device. Including disks, tapes, and named Pipelines
SQL Server backup policy: only backup databases, backup databases and transaction logs, and differential backup.
Backup database medicaldb to disk = 'medical _ bk1 'with name = 'medicaldb backup' description = 'medicaldb fullbackup 'init
Restore database medicaldb from medical_bk1

Import and export:
Data Transmission Service (DTS) is a Data transmission Service provided by SQL Server. This allows you to extract, convert, and merge data from different data sources into one or more data sources.

Separation and attachment: Create a removable database.