Tutorial: SQL preparation course: database creation, table creation, constraints, relationships, and some T-SQL statements

-- Database creation

If exists (select * From SYS. sysdatabases where name = 'wf ')
Begin
Use master
Drop database Wf
End
Go

Create Database Wf

On
(Name = n' WF ', filename = n' E: \ mycode \ etc toll station \ etc_data \ WF. MDF ', size = 3 MB, maxsize = unlimited, filegrowth = 1)
Log On
(Name = n 'wf ', filename = n' E: \ mycode \ etc toll station \ etc_data \ wf_log.ldf', size = 3 MB, maxsize = unlimited, filegrowth = 1)

Go

Use Wf
Go

If exists (select * From SYS. sysobjects where name = 'wf ')
Begin
Drop table Wf
End

 

-- Create tables, constraints, and relationships

Use WF
go
Create Table tableok
(
col1 int,
col2_notnull int not null,
col3_default nchar (1) not null default ('male'), -- default male
col4_default datetime not null default (getdate ()), -- the default system time is
col5_check int not null check (col5_check> = 18 and col5_check <= 55). -- add constraints, data values between 18 and 55
col6_check nchar (9) Not null check (col6_check like 'msd0902 [0-9] [^ 6-9] '), -- add constraints, the first seven digits of the data value must be 'msd090 2', the last two digits can be any number in 0-9, and the last digit is not a number between 6-9.
cola_primary nchar (5) not null primary key, -- create a primary key
colb_unique int unique, -- unique constraint
col7_identity int not null identity ), -- auto-increment: from 100, one value is added for each column.
col8_identity numeric () not null identity () -- auto-increment, starting from 1, one value is added for each column, and the maximum value is a five-digit integer
col9_guid uniqueidentifier not null default (newid () -- use the newid () function, randomly retrieve column values
)

-- Alter
-- Primary foreign key/reference/link Constraint
Alter table from table name [with check] -- enable with nocheck -- disable Constraints
Add constraint FK _ primary table name_slave table name
Foreign key (field name in the table) References primary table name (field name in the primary table)

-- Other non-primary foreign key constraints
Alter table Wf
Add constraint name constraint type constraints

Alter table wf -- modify the joint primary key
Add constraint pk_cola_primary primary key (cola_primary, col1)

--- 1. insert [into] <Table Name> [column name] values <Value List>
-- 1) All [column names] can be omitted
Insert stuinfo values ('','','')

Create Table outtable
(
Id int not null primary key,
[Name] nvarchar (4) not null,
Adrress nvarchar (20) default 'address unknown'
)
Truncate table outtable
Alter table outtable alter column ID int identity (4, 1)
Insert outtable values (1, 22 ,'')
Insert outtable values (2, 32 ,'')
Insert outtable values (3,33 ,'')
-- 2) Part
Insert stuinfo (stuno) values ('')
-- 3) automatically increases columns and cannot be inserted manually. Omitting column names and column values

--- 2 insert multiple rows
--- 1) extract the obtained fields from an existing table and insert them to the target table.
Insert into stuinfo (stuno, stuname) Select stuno, stuname from stumarks
Insert into stuinfo (stuno) Select stuno from stumarks

--- 2) extract data from the existing table, create a new table, and insert data into the new table
Select [column name] into <Table Name> from <source table name>
Select ID as 'number', [name] into newtable from outtable
Select cast (ID as varchar (4) + Cast ([name] As nvarchar (4) as 'number and name', Id into newtable from outtable
Select * From newtable
Drop table newtable

--- 3) join existing multiple tables to put data in existing table 3
Insert into <Table Name 3> [column name]
Select * from table 1 Union
Select * from table 2

-- 3. Update statement <Table Name> set <column name = Update value> [where <update condition>]
--- Note: Generally, do not omit the where statement. If this parameter is left blank, the entire table will be modified.

-- 4truncate: delete data
-- Compared with Delete, data can only be deleted in full, but cannot be deleted in part. The deletion efficiency is high and the auto-increment column can be reset.

Select * From stumarks
Select * From stuinfos

-- Raise 5 points for each exam score, and set a threshold of 100 points.
Updated stumarks set writtenexam = 100
Where writtenexam> 95
Update stumarks set label = 100
Where labexam> 95

Update stumarks
Set writtenexam = writtenexam + 5
Where writtenexam + 5 <= 100

Update stumarks
Set labexam = labexam + 5
Where labexam + 5 <= 100

Select examno, stuno, writtenexam + 5 as test, labexam + 5 as test from stumarks where writtenexam + 5 <= 100 and labexam + 5 <= 100

Create Table t
(
Id int,
Name nchar (4 ),
DT datetime,
Age int,
Score int
)

Insert T values (1, 'A', getdate (), 20, 50)
Insert T values (2, 'B', getdate (), 21,60)
Insert T values (3, 'C', getdate (), 21,100)
Insert T values (4, 'D', getdate (), 23,80)

Select * from t
Select Top 2 * from t
Select top 60 percent * from t

Select top 5 * from products
Select top 5 percent * from products

-- Alias: As (can be omitted), =
Select productid as 'number' from products
Select productid 'No.' from products
Select 'number' = productid from products
Select employeeid from employees
-- Declare @ A nvarchar (6)
-- Set @ A = 'employee ID :'
Select n' employee number: '+ Cast (employeeid as nvarchar (2) from employees

Select distinct country from suppliers order by country
Select * from t
Select * from products
Select * from products order by 4 ASC, 6 DESC
Select * from products where unitprice> 16 and productname like't % 'or productid = 16

Select * from suppliers where country in ('Japan ', 'italy ')
(1) Char, varchar, text and nchar, nvarchar, ntext
Char and varchar are both between 1 and 8000 characters in length. The difference is that char is a fixed-length character data while varchar is a variable-length character data. The so-called fixed length is a fixed length. when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length; the variable-length character data is not filled with spaces. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.
Compared with the preceding three data types, the names only contain letters "N", which indicate that the characters of the Unicode data type are stored. Written Program Friends should be familiar with Unicode. It is enough to store only one byte of English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.
(2) datetime and smalldatetime
Datetime: Date and Time data from January 1, 3% to seconds.
Smalldatetime: Date and Time data from January 1, January 1-20, 1900 to January 1, June 6, accurate to minutes.
(3) bitint, Int, smallint, tinyint, and bit
Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807.
INT: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647.
Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767.
Tinyint: integer data from 0 to 255.
Bit: an integer of 1 or 0.
(4) decimal and numeric
The two data types are equivalent. There are two parameters: P (precision) and S (number of decimal places ). P specifies the maximum number of decimal digits that can be stored on the left and right of the decimal point. P must be a value ranging from 1 to 38. S specifies the maximum number of decimal digits that can be stored on the right of the decimal point. s must be a value ranging from 0 to P. The default decimal place is 0.
(5) float and real
Float: floating point data from-1.79 ^ 308 to 1.79 ^ 308.
Real: floating point data from-3.40 ^ 38 to 3.40 ^ 38. In SQL Server, the synonym for real is float (24 ).

Select -- retrieve data rows and columns from database tables
Select col1, col2, col3.... from table
Insert -- add new data rows to the database table
Insert into table (col1, col2....) values (value1, value2 ...)
Insert into table (col1, col2....) Select col1, col2. .. from table
Delete -- delete data rows from a database table
Delete from table
Update -- update data in the database table
Update table set col1 = value ,......
-- Data Definition
Create Table -- create a database table
Create Table tbame (col1 int, col2 char (20 ))
Drop table -- delete a table from a database
Drop table tbname
Alter table -- modify the database table structure
-- Add a column
Alter table # A add col3 int
-- Delete a column
Alter table # A drop column col3
-- Add the column Default Value
Alter table tbname add constraint tf_name default 'A' for col2 create View -- create a view
Create view view_name
Select * From tbname where ....
Drop View -- delete a view from a database

Drop view view_namecreate index -- create an index for the database table

-- Create a simple non-clustered index create index index_name on tbname (vendorid );

-- Create a simple unique non-clustered Index
Create unique index index_name
On tbname (vendorid );

Drop index -- delete an index from a database
Drop index index_name on tbnamecreate procedure -- create a stored procedure
Create procedure name
Begin
......
End
Drop procedure -- delete a stored procedure from a database
Drop procedure namecreate trigger -- create a trigger
Create trigger name on tbname
For insert, update
.......
Drop trigger -- delete a trigger from a database