SQL Server Basics

 

SQL Server basic knowledge (strongly recommended)

SQL-Structured Query Language

-- (Enable SQL Service: Net start MSSQLServer)

-- (Enter the 'sqlwb 'command in the command line to open the SQL manager)

-- (If you want to execute one of multiple commands, select the command with the mouse and press F5 to execute it)

Create Database sales -- create a database named sales
On
(
Name = 'sales _ data ',
Filename = 'd: \ sales_data.mdf ',
Size = 10,
Maxsize = 50,
Filegrowth = 5
)
Log On
(
Name = 'sales _ log ',
Filename = 'd: \ sales_log.ldf ',
Size = 10,
Maxsize = 50,
Filegrowth = 5
)

Drop database sales -- delete the created database

Sp_helpdb sales -- View database information

Sp_helpfile -- View information about the current database data file and Log File

Sp_detach_db sales -- detach a database when transferring a database

Sp_attach_db sales, @ filename1 = 'data file pathname' -- Integrated and separated Database
, @ Filename2 = 'Log file pathname'

-- (How to reduce the size of the Database Log File: A. Detach the database B. Transfer the log file C. Integrate the database but do not specify the log file)

-- Database Backup

Sp_addumpdevice 'disk', 'mydisk', 'd: \ sales. Bak' -- add a device. Disk indicates the type of the target device, mydisk indicates the logical name of the target device, and D: \ sales. Bak indicates the physical name of the target device.

Backup database sales to mydisk -- write data to the device. Sales indicates the database name, And mydisk is the custom device identifier.

Restore database sales from mydisk -- Restore database

Sp_dropdevice mydisk -- delete a device

Exec sp_dboption 'sales', 'read only', 'true' -- set the database to read-only

Exec sp_dboption 'sales', autoshrink, true -- set the database to auto Compression

Exec sp_dboption 'sales', 'single user' -- set the database as a single user

-- (Single quotes can be added or not added in the preceding command, but spaces in the name must be added. Case Insensitive)

DBCC shrinkdatabase (sales, 10) -- reduces the number of files in the database so that the database has 10% free space

Bytes ---------------------------------------------------------------------------------------------------------------

Create Table goods -- the first constraint syntax for table Creation
(
GID int primary key,
Gname varchar (10) unique,
Price money check (price> 300 ),
Ldate datetime default getdate ()
)

Insert into goods (GID, gname, price) values (105, 'computer5', 1222) -- input data to the table when the table has a default value Constraint

Insert into goods values (107, 'computer ', 13434, default) -- add data to a table if the table has a default value Constraint

Sp_help goods -- used to query table information

Select * from goods -- used to query table content

Create Table goods -- the second constraint syntax for table Creation
(
GID int constraint pg_id primary key, -- use constraint to name the column constraint in the table
Gname varchar (10) Constraint uq_name unique,
Price money constraint ck_price check (price> 300 ),
Ldate datetime constraint df_date default getdate ()
)

Alter table goods drop constraint name -- used to delete a constraint

Create Table goods -- the third constraint syntax for table Creation
(
GID int not null,
Gname varchar (10 ),
Price money,
Ldate datetime
)

alter table Goods add constraint pk_id primary key (GID)
alter table Goods add constraint uq_name unique (gname)
alter table Goods add constraint cj_price check (price> 300 and price <1000)
alter table Goods add constraint df_ldate default getdate () for ldate

Create Table GP -- create a table GP that references goods
(
Wno int identity (1001,) primary key, -- identity is set to auto-increment column ID, is the starting number, references is reference
-- Values cannot be assigned to auto-increment columns when data is inserted. single quotation marks must be used to insert ignore and datetime data.
Gno int constraint fk_id foreign key -- defines gno as the table's foreign key
References goods (GID)
)
Drop table GP
Create Table GP
(
Wno int identity (1001,1) primary key,
Gno int
)

Alter table gp add constraint fk_id foreign key (gno) References goods (GID) -- same effect as above.

Alter table table name Add column name data type -- add the previous column to the table

Alter table Table Name drop column name -- delete a column

Delete from table name where condition (for example, gid = 1001) -- delete a row that meets the where Condition

Insert into table name values (default) -- attaches the default value to the table

Insert into Table Name (column name) values () -- same as above

-- The default value does not affect historical data!

-- When adding data to a table that contains an auto-increment column, no value is required for the auto-increment column.

Delete from table name -- delete all table data

Delete from table name where gid = 1001 -- delete data that meets the condition (gid = 1001)

Truncate table name-truncation table, which cannot contain conditions and cannot be referenced by foreign keys, no matter whether the table has data

Update table name set column name = column value -- used to update data

Where gid = 1000 or gid = 1001

Update table name set column name = column value -- same as above

Where GID in (1000,1001)

Update table name set column name 1 = column value 1, column name 2 = column value 2,... -- Update values for multiple columns

Where condition

-- Transactions can be divided into three types: 1. Explicit transaction 2. Implicit Transaction 3. Automatic transaction commit (the default is automatic transaction commit)

Select * from table name -- Query table data

Tran in TRAN T1 -- start an explicit transaction

Update table name set column name = column value -- Update Data

Where not condition -- Update Condition

Rollback T1 -- roll back a transaction

Commit T1 -- commit the transaction (cannot roll back later)

-- An implicit transaction uses the set implicit_transactions on statement to open an implicit transaction. When the connection is operated by an implicit transaction,
-- A new transaction will be automatically started after commit or rollback. You do not need to describe the start of the transaction, but only need to commit or roll back the transaction.

Set implicit_transactions on

Select * into new table name from old table name -- backup existing table data to new table, it can copy the table structure, data.
-- You can also add conditional filtering. If you only want to copy data to the specified column, replace it with the column name *.

-- If you only want to copy the table structure and do not want to copy the data, add the condition that the table will never be created. (For example, where 1> 3)

-- This statement automatically creates a new table, but the constraints of the original table cannot be copied, but not null and identity attributes can be copied.

Select column name 1, column name 2, column name 3 ,......
Into new table name from old table name -- back up some data in the existing table to the new table

Alter table gp add constraint gp_id foreign key (gno) References
Goods (GID) on Delete cascade on update no action -- this is the syntax used for Cascade update and deletion,
-- Add: 1. Delete Cascade 2. Delete no action 3. Update cascade 4. Update no action

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------

Create Table GP
(
PID int identity (100,1) primary key,
Pname varchar (10 ),
Ptel varchar (12) check (ptel like '[0-9] [0-9] [0-9] [-] [0-9] [0-9] [0-9] [0-9] [0 -9] [0-9] [0-9] [0-9] [0-9] ')
)

-- This is a method to restrict telephone numbers.

Select host_name () -- view the local name

Select getdate () -- get the current time

Select User -- view the current user

Xp_cmdshell 'dir' -- execute the doscommand Dir and write the doscommand In The Middle ''.

Xp_cmdshell 'net user Ekin 1234/add' -- add a Windows user. Ekin is the user name and 1234 is the password.

Xp_cmdshell 'net user Ekin/delete' -- delete Windows users

Xp_mongoshell 'net user administrator 123456' -- change the Administrator Password

Uniqueidentifier -- this is a type of data. It is a globally unique identifier. The newid () function is used to provide values for this type of data.

Select * from GP inner join goods on GP. gno = goods. GID -- the inner join only displays the join of matching rows in two join tables.
 
Select * from GP left Outer Join goods on GP. gno = goods. GID -- left Outer Join includes all rows in the first named table ("Left" table, which appears on the far left of the join clause. Does not include unmatched rows in the "right" table

Select * from GP right Outer Join goods on GP. gno = goods. GID -- right Outer Join includes all rows in the second named table ("right" table, which appears on the rightmost side of the join clause. Does not include unmatched rows in the left table.

Select * from GP full outer join goods on go. gno = goods. GID -- The Complete External join includes all rows in all join tables, whether or not they match

Select * from GP cross join goods -- In the result set of this type of join, each of the two tables may have a pair of rows, whether or not they match

Select * from goods where price between 1300 and 1800 -- interval query. Goods with prices between and

Select * from goods where GID in (1001, 1003) -- Query products with cargo IDs of 1001 and 1003, not goods between and! In pre-plus not refers to goods except 1001 and 1003

Select * from goods where price not between 1300 and 1500 -- check goods whose price is not between 1300 and 1500

Select * from goods where price is null -- Query goods whose price is null

Select * from goods where gname like ''-- fuzzy query. In '', % represents any character in gname, _ represents a character in gname, [] represents a range, and [^] represents not in this range

-- For example: Select * From renyuan where age like '2 [^ 1-4]'

-- In SQL, only one Chinese Character and one symbol or letter occupy one character, and nchar can be used to record Chinese characters.

Select max (price) as highest price from goods -- as is the function that obtains the alias, max () is the function that calculates the maximum value, min () is the minimum value, Arg () is the average value.

Select sum (price) from goods -- calculate the sum of price, sum () is used to calculate the sum

-- A single row cannot be used together with aggregation unless it is grouped based on a single row. (For example, select GID, max (price) from goods)

Select GID, max (price) as highest price from goods group by GID -- group by GID, is to find the highest price of similar GID goods

-- Aggregate functions (for example, where Max (price)> 1300) cannot appear in the WHERE clause)

Select GID, max (price) as maximum price from goods group by GID having max (price)> 1300 -- use having to specify the grouping conditions, instead of where

Create Table info
(
Ino int, age int
)

Insert into info values (12, 22)
Select * from Info order by Ino ASC, age desc -- order by specifies the sorting condition. ASC indicates ascending, and desc indicates descending.

-- The aboveProgramThe result is numbered in ascending order, and sorted by age in descending order when the numbers are the same.

Select max (convert (INT, price) from goods -- query the goods with the highest price in the goods table and forcibly convert the price type

Select top 1 * from goods where price> 4000 -- query the goods whose price is greater than 4000 in the goods table, and only display the first record