Summary of SQL video and summary of SQL

Summary of SQL video and summary of SQL

I still like short videos in English for 7 to 8 minutes. The VB videos of Mr Chen Wei and the SQL videos of Mr Yan jianling are all between 30 and 40 minutes. One tomato is not enough and two tomatoes are not enough. But fortunately, no matter whether it's Jack Chen's VB video or Jack Yan jianling's SQLServer video, the amount of information in the video is large, and I can learn a lot after reading it. Draw a picture and organize the knowledge in the video.

This video describes the knowledge of SQL Server and how to use SQL Server.

 

Starting from software:

This section describes how to use Enterprise Manager and T-SQL statements to perform the following operations on databases, database objects, and other related operations.

1. Create
2. View and modify
3. Delete
4. Rename

Introduces the concepts of databases and database objects and operates on them.

1. Database

Fixed System databases

System data table

1. Data Object

The database object is what we can see below the database when the software is opened, including tables, views, triggers, stored procedures, rules, indexes, and roles.

1. Database Files

Main files, secondary files, and log files. And their significance in the database

 

Starting from the language, the language is the same.VBThe language is similar.

1. Data Type

1. Function

1. Cursor
2. Process Control
3. Variable

 

The video focuses on operations on databases, views, tables, roles, stored procedures, indexes, and triggers using enterprise managers and t-SQL statements. Take an example to learn more about the operations performed by the T-SQL statement.

We also talked about the concept of transactions, the role of transactions in data processing, the concept of constraints, and the Creation method.

The description of indexes, triggers, stored procedures, and constraints is also detailed.

 

1. Enterprise Manager does not exist in SQL Server2008. Instead, the operations on SQL Server manger studio are a little different. Some cannot be found. Baidu finds related items.
2. T-SQL code is common

An Introduction to SQL Server is a classic, but I am confused about it. However, the video provides a good explanation of the knowledge, and the knowledge I learned is related to each other. After reading the video, I learned the principles of the database system of my own textbooks, after chapter 5, the knowledge is basically solved. by connecting the knowledge, the knowledge needed to be learned is gradually reduced, and the efficiency is improved.

SQL statement Summary

1. Description: Create a database
Create database database-name
2. Description: delete a database.
Drop database dbname
3. Description: Back up SQL server
--- Create a device for the backup data
USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'
--- Start backup
Backup database pubs TO testBack
4. Description: Create a new table.
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only
5. Description: delete a new table.
Drop table tabname
6. Description: Add a column.
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)
8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: create view viewname as select statement
Delete view: drop view viewname
10. Description: several simple basic SQL statements
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: update table1 set field1 = value1 where range
Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!
Sort: select * from table1 order by field1, field2 [desc]
Total: select count as totalcount from table1
Sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1
11. Description: several advanced query Operators
A: UNION operator
UNION operator through the group... the remaining full text>

Summary of common SQL statements

Common Database Operation statements:
1. Description: Create a database
Create database database-name
2. Description: delete a database.
Drop database dbname
3. Description: Back up SQL server
--- Create a device for the backup data
USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'
--- Start backup
Backup database pubs TO testBack
4. Description: Create a new table.
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only
5. Description: delete a new table.
Drop table tabname
6. Description: Add a column.
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)
8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: create view viewname as select statement
Delete view: drop view viewname
10. Description: several simple basic SQL statements
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: update table1 set field1 = value1 where range
Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!
Sort: select * from table1 order by field1, field2 [desc]
Total: select count as totalcount from table1
Sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1
11. Description: several advanced query Operators
A: UNION operator
U ...... remaining full text>