asp.net ADO SQL database note summary is continuously updated
- 2020-05-17 05:09:11
- OfStack
ADO connects to the database
1) get the connection string
Approach 1: remember the connection string
connectionString=" Integrated Security=True; server=. ; database=DBName"
Option 2: in visual studio, click "view" a service explorer a right click "data connection" on the left and select "add connection" a service name: is 1 point. Select the database name, then click "advanced" and copy the connection string at the bottom
2) configure the connection string in web.config
3) create SqlConnection class in DAL layer, including static methods:
Remember to add the Configuration reference and using System.Configuration; The namespace
4) call getConnectionStr() static methods in other classes in the DAL layer
Method for the DAL layer to execute SQL statements
ADO operates on SQL statements: mode 1
ADO operation stored procedure:
The error log
Database technology
Building library table
Build library statement
Build table statements
Operation on a table
Modify the table structure to add remove constraints
alter table person -- modify the table structure
--add NameID int -- add the property \ field NameID column, add the column
--drop column NameID -- delete columns
--alter column ID int not null -- the add field is not empty
--add constraint constraint name (pk_ table name _ column name | pk_ column name)
--add constraint pk_ID primary key(ID) -- add a primary key constraint when modifying a table
--add constraint ck_score check(score < 150) -- add check constraints when modifying a table
--add constraint uk_cardi unique(cardid) -- add a key-only constraint when modifying a table
--add constraint df_age default 19 for age -- add default constraints when modifying a table
--drop constraint CKs 115ens 116ens 1502E78 -- delete constraint (format :drop constraint constraint name)
Modify the table information, add (insert) delete (delete) change (update) check (select)
-- add records < insert table name values(parameter) > < 1 > String or date type plus '' < 2 > bit with or < 2 > Automatically growing a column does not require adding a value
insert person values(12,'wang',0,23,32,34)
insert person(sex,age,cardid) values(0,23,32) -- selective insertion of data ((sex,age,cardid)) refers to data to be manually added
-- modification record < update table name set modifies the object where condition > < 1 > Modify multiple fields separated by commas
update person set age=19,sex=0 where ID=1
update person set age=19,age=12 where ID=2 --- < 1 >
update person set age=19+1 where ID=2-- perform arithmetic operations when modifying information
update person set age=19+1 -- if where is not written, all data is modified
update person set age=SUBSTRING(age,1,1) where ID=1 --substring is a truncation of characters
-- delete records < delete table name where condition > (delete all records if where is not added)
delete person where ID=1
A query to a table
Single table query
Multi-table query
The stored procedure
1 some examples
Database joint access authorization
Pager stored procedures, paging stored procedures
Transaction Transaction
The trigger Trigger
The cursor Cursor
Scattered knowledge
1) intercept string: substring(field name, starting position (the first place is 1), length of intercept)
select SUBSTRING(age,1,1) from person where ID=2
2) about GUID:
select NEWID()
insert person values('','',NEWID())
3) insert 1 table (person2) into another table (person1) (the number of columns should correspond)
insert person1
select column, column, column from person2
4) conditional statement (note: braces {} in C# are replaced by begin end in sql)
5) determine the existence of if exists(select * from TBName where CityName='22')
6) add the auto-growing column row_number over(order by **) ROW_NUMBER is the keyword over which column to sort according to
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID),* from SM_Class - is also an automatic line number. If there is a repeat, the line number will be matched and the next value will be automatically increased by 2
-- the results returned by one query statement serve as the data source table for the other query statement
select * from (select ROW_NUMBER() over(order by column name 1) the new column name here, * from TBName) the new table name where between 1 and 3
7) temporary tables
declare @table table(id uniqueidentifier,name varchar(50))
-- perform the insert operation (insert bar data) and return the hid field of this data and insert it into the id property of the @table table
insert images(Hname,Himage) output inserted.Hid into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table
------------------------------------------------------------
-- the following is the comparison of execution efficiency
--sql statement 1: execution requires s
declare @tempTable table(id varchar(20),name int,score datetime)
insert @tempTable(id,name,score)
select userID,userName,userScore from scoreTable
select * from @tempTable
--sql statement 2: only s is needed for execution
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles
8) operation on date and time
-- get Beijing time and international time from the database
select getdate(), getutcdate()
-- an increase in time (type of increase [year/month/day], increment, to whom add [current time/date]) dateAdd
select dateadd(YEAR,2,GETDATE()) -- add two years to the current year
-- subtraction of time
select DATEDIFF (HOUR, getdate (), getutcdate ()) -- hours of international time minus hours of current Beijing time (back minus front)
-- get the year, month, day and so on in time
select year(getdate())-year(birthday())
select year(getdate())-year('1988-10-07')
9) row and column conversion
select * from ( select * from TableName) a pivot(count(stuName)) for columnName in('aa','bb','cc','dd')
10) double quotation marks can only be used for table names and column names (no double quotation marks are acceptable)
set @aa ='select sd 'from SM_Class' -- notice that the original 'sd' in 'sd' is now written as' sd'
exec (@aa)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- here to pay more attention to -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
declare @bb nvarchar(max);
-- only "e-commerce class 2" can be used when using data values.
set @bb ='select * from SM_Class where ClassName=' e-commerce class 2 '-- note: the original' e-commerce class 2 'will be written as' e-commerce class 2'.
exec (@bb)
11) -- quickly create a table structure
select c.Cid,c.Ccount into newTB1 from ns_comment c where 1 < > 1
12) -- remember one more key point
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
-- @str variable containing SQL statement, define the variable in @str, N is marked as a string to receive the variable in @str (that is, @na = @bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) -- concurrency problem --
-- concept: multiple users interact with one object (library, table) at the same time
-- problem: dirty data, non-repeat reads, lost updates, phantom reads)
-- solution: SQL Server USES locks to ensure transaction integrity (Shared locks, exclusive locks, update locks, intent locks, schema locks, batch update locks)
14)
1) get the connection string
Approach 1: remember the connection string
connectionString=" Integrated Security=True; server=. ; database=DBName"
Option 2: in visual studio, click "view" a service explorer a right click "data connection" on the left and select "add connection" a service name: is 1 point. Select the database name, then click "advanced" and copy the connection string at the bottom
2) configure the connection string in web.config
<connectionStrings>
<addname="SQLconnectionStr"connectionString="Data Source=.;Initial Catalog=NetShopDB;Integrated Security=True"providerName="System.Data.SqlClient"/>
</connectionStrings>
3) create SqlConnection class in DAL layer, including static methods:
Remember to add the Configuration reference and using System.Configuration; The namespace
public static string getConnectionStr()
{
return ConfigurationManager.ConnectionStrings["SQLconnectionStr"].ToString();
}
4) call getConnectionStr() static methods in other classes in the DAL layer
string conStr= SqlConnection.getConnectionStr();
Method for the DAL layer to execute SQL statements
ADO operates on SQL statements: mode 1
public List<student> getData1(string myid, string myname)
{
// Here with using Agile and convenient , Use up con No manual closing is required , Will automatically close the current connection
using(SqlConnection con=new SqlConnection(conStr) )
{
// Open the connection
con.Open();
string cmdStr = "select * from ns_user where userID=@myid and userName=@myname";
SqlCommand cmd = new SqlCommand(cmdStr,con);
// Here, parameter serialization is used to prevent injection attacks
cmd.Parameters.Add(new SqlParameter("@myid", myid));
cmd.Parameters.Add(new SqlParameter("@myname", myname));
// Execute the query and return the number of values in the result set returned by the query 1 The first 1 The column. Ignore other columns or rows
//object myResult = cmd.ExecuteScalar();
// Perform on the connection Transact-SQL Statement and returns the number of affected rows. ( Responsible for executing statements , For example, increased insert, delete delete, change update)
//int ResultRowCount = cmd.ExecuteNonQuery();
SqlDataReader sdr = cmd.ExecuteReader();
List<student> ls = new List<student>();
while (sdr.Read())
{
student s = new student();
s.Sid = sdr["sid"].ToString();
s.Sname = sdr["sname"].ToString();
ls.Add(s);
}
return ls;
}
}
Dataset( remember )
#region Get the details of the teacher
public DataSet GetTeacherInfo()
{
using (SqlConnection conn = new SqlConnection(dbapp))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from table1 ", conn);
DataSet ds = new DataSet(); // define 1 A collection of tables
sda.Fill(ds, "teacher");
return ds;
}
}
#endregion
ADO operation stored procedure:
#region
public int UserCheck(string userID,string userName)
{
using(SqlConnection con=new SqlConnection (conStr))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_MYLogin"; //sp_MYLogin Is the name of the stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
// Assign a value to a stored procedure
// Assignment way 1( Assign the first 1 The value inside the parentheses "@name" Must and stored procedure "@name"1 die 1 sample )
cmd.Parameters.Add(new SqlParameter("@userID", userID));
// Assignment way 2( Assign the first 2 A value )
SqlParameter pwd = new SqlParameter("@pwd", SqlDbType.NVarChar, 50);
pwd.Value = userName;
pwd.Direction = ParameterDirection.Input;
cmd.Parameters.Add(pwd);
// define 1 To accept the return value of the stored procedure
SqlParameter result = new SqlParameter("@return", SqlDbType.Int);
result.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(result);
cmd.ExecuteNonQuery(); // Executing stored procedures
// achieve SQL variable @result The return value of the stored procedure stored in
int num = Convert.ToInt32(cmd.Parameters["@return"].Value); //Parameters is 1 A collection of ["@return"] It's his index
return num;
}
}
#endregion
The error log
catch (Exception ex)
{ // The error log
error.ErrorWrite("UserInsert", ex.Message, DateTime.Now.ToString());
return null;
}
Database technology
Building library table
Build library statement
create database student -- The name of the database you created
on primary --- Specify the parameters of the data file
(
name='studnet3_data', -- All of the strings ' interval
filename='E:\lx\student4_data.mdf', -- The file name contains the path and name . extension
size=3MB, ---- The default size, if you don't write the size, the default is MB
maxsize=100MB, ---- The maximum capacity
filegrowth=1MB --- Automatic increment / capacity , If yes, the capacity will not be automatically expanded
)
log on ----- Each parameter of the log file
(
name='student5_log',
filename='E:\lx\student4_data.ldf',
size=1MB,
maxsize=10MB,
filegrowth=10% --10% It's the maximum capacity %)
)
sp_helpdb student --- Query database name
sp_renamedb student,stu -- Rename database
drop database student -- Delete database
Build table statements
drop table person -- Delete table
create table person -- Create a table
(
--- Note: below are the properties ( The field names ) The data type comes first
ID int primary key identity(2,1) not null, -- primary key Is to set the primary key, ensuring that the column value is only 1 And it's not empty, identity(2,1) The starting value is zero, and the step size is zero
Name nvarchar(10) not null, ---not null It can't be empty
Sex bit not null, --bit is bool type
age int default 18 , -- default 18 It means automatically taking the default value
scroe decimal(4,1) check(score<=100) --4 The number of digits after the decimal point check Check for constraints
cardid int unique --unique Refers to only 1 Key, having multiple columns of data in the table needs to be guaranteed only 1 When columns other than the primary key need to be set to only 1 column
)
Operation on a table
Modify the table structure to add remove constraints
alter table person -- modify the table structure
--add NameID int -- add the property \ field NameID column, add the column
--drop column NameID -- delete columns
--alter column ID int not null -- the add field is not empty
--add constraint constraint name (pk_ table name _ column name | pk_ column name)
--add constraint pk_ID primary key(ID) -- add a primary key constraint when modifying a table
--add constraint ck_score check(score < 150) -- add check constraints when modifying a table
--add constraint uk_cardi unique(cardid) -- add a key-only constraint when modifying a table
--add constraint df_age default 19 for age -- add default constraints when modifying a table
--drop constraint CKs 115ens 116ens 1502E78 -- delete constraint (format :drop constraint constraint name)
Modify the table information, add (insert) delete (delete) change (update) check (select)
-- add records < insert table name values(parameter) > < 1 > String or date type plus '' < 2 > bit with or < 2 > Automatically growing a column does not require adding a value
insert person values(12,'wang',0,23,32,34)
insert person(sex,age,cardid) values(0,23,32) -- selective insertion of data ((sex,age,cardid)) refers to data to be manually added
-- modification record < update table name set modifies the object where condition > < 1 > Modify multiple fields separated by commas
update person set age=19,sex=0 where ID=1
update person set age=19,age=12 where ID=2 --- < 1 >
update person set age=19+1 where ID=2-- perform arithmetic operations when modifying information
update person set age=19+1 -- if where is not written, all data is modified
update person set age=SUBSTRING(age,1,1) where ID=1 --substring is a truncation of characters
-- delete records < delete table name where condition > (delete all records if where is not added)
delete person where ID=1
A query to a table
Single table query
select * from tableName -- The lookup displays the entire table ( All the columns )
select The column name 1, The column name 2 from tableName -- Display partial columns
select The column name 1=' Serial number ', The column name 2=' brand ' from Product1 -- Among them 1 The information system of columns 1 Modify the
-- Modify the column header when displayed ( methods 1)
select ' Serial number '=ProCode,' brand '=ProTypeName from Product1
-- Modify the column header when displayed ( methods 2)
Select The column name 1 ' Serial number ', The column name 2 ' brand ' from Product1
-- Perform arithmetic operations on the columns when displayed , Add columns when displayed
select The column name 1, The column name 2, The column name 3*0.5 ' After the discount ',Number from Product1
select distinct The column name 1 from tableName -- Delete duplicate rows for this column when displayed
select top 3 * from Product1 -- According to the former 3 column
select top 50 percent * from Product1 -- Displays the front of the total number of rows % line
--and Is and , or Is or , Conditions of the : not The column name =' value '
select * from tableName where Number=15 and not age=12 and Name='aa' or sex=1
-- To find the score The range of 0 to 100 The data of
select * from Product1 where score<100 and score >=1
select * from Product1 where score between 1 and 100
--in . not in ( Inclusion or exclusion ) The following are all lookups number Is the data of and
select * from Product1 where Number=10 or Number=15 or Number=20
select * from Product1 where Number in(10,15,20)
select * from Product1 where not Number in(10,15,20)
--<1>like Pattern matching characters % Can be substituted for any number of characters <2> _ Can replace 1 A character <3>[] is 1 Search range
select * from Product1 where ProCode like 'D%' -- Find the first letter is D the ProCode The data of
select * from Product1 where ProCode like '_S%' -- Find the first 2 A character is S the procode data
select * from Product1 where The column name 1 like '1%' -- even float Also want to add ''
select * from Product1 where The column name 1 like '_4_' -- Find the first 2 Is and the number of characters is 3 data
select * from Product1 where The column name 1 like '_[5-9]%' -- Find the first 2 A character is 5 to 9 The column 1 data
-- To find the empty , Data that is not empty
select *from Product1 where proValue is not null
select *from Product1 where proValue is null
go -- The sorting ( desc Descending order asc ascending Default to ascending without writing anything )
select * from Product1 order by proValue desc -- will provalue In descending order
select * from Product1 order by proValue asc -- will provalue In ascending order
select * from Product1 order by Number -- will Number According to the default ( ascending ) arrangement
select * from Product1 order by Number desc,proValue asc -- The first 2 The first condition is number one 1 Only when the conditions are the same
go -- Aggregation function
select MAX(proValue) from Product1 -- To find the proValue Maximum of
select min(proValue) from Product1 -- To find the proValue Minimum of
select sum(proValue) from Product1 -- To find the proValue The sum of the data
select avg(proValue) from Product1 -- To find the proValue Mean value in
select count(*) from Product1 -- Look up the number of rows in the table * You can also use the column name instead
--group by Groups ( where in group by Before, having The filter conditions after grouping, where and having It's all screening criteria.)
-- grouping : You can display grouped column information and grouped information for statistics
select The column name 1,max( The column name 2),min( The column name 3) from tableName where proTypeName=' The TV ' group by The column name 1
select proTypeName,max(proValue),min(proValue) from Product1 group by proTypeName having count(proValue)>1
Multi-table query
-- In the connection inner join Query for information common to both tables
--from Query the column name 1.Name= The column name 2.Name It's an association condition ( The content of the column in the association condition is 1 to )
select * from tableName inner join Product2 on The column name 1.Name= The column name 2.Name
-- Display the partial columns after the query, p1.* It means to show p1 Of all the columns
select p1.*,proArea,proBrand from Product1 as p1 inner join Product2 on p1.ProCode=Product2.ProCode
--Product1 as p1 Mean to Product1 They gave it a different name p1 . as Can be omitted
select * from Product1 as p1 inner join Product2 as p2 on p1.ProCode=p2.ProCode
--where Query, omitted as < format :select * from table , table where Associated conditions >
select * from Product1 p1,Product2 p2 where p1.ProCode=p2.ProCode
-- Outer join -- The data associated with the two tables is displayed first, then the data not associated
go --< format :select * from table left\right\full outer join table on Associated conditions >
select * from Product1 p1 left outer join Product2 p2 on p1.ProCode=p2.ProCode -- The left outer join
select * from Product1 p1 right outer join Product2 p2 on p1.ProCode=p2.ProCode -- Right connection
select * from Product1 p1 full outer join Product2 p2 on p1.ProCode=p2.ProCode -- Full outer join
-- Cross connection ( It's called a cartesian connection : Crazy connection, n right n The connection , No associated condition )
-- format :select * from table cross join table
select * from Product1 cross join Product2
-- Since the connection ( Since the query : Is the 1 A table is divided into two tables )
select c1.*,c2.Name from ST_class c1,ST_class c2 where c1.ID=c2.Department and c1.Name=' School of computer science '
-- Nested query
-- The subquery returns multiple values
select * from product1 where ProCode in(select ProCode from Product2 where proArea=' Beijing ')
select * from product1 where ProCode not in(select ProCode from Product2 where proArea=' Beijing ')
-- Subquery return 1 A value
select* from Product1 where proValue=(select MAX(provalue) from Product1)
-- Can be used when a subquery returns multiple values any Returns any of the results 1 One [smallest] 1 A) all (returns all [maximum] values in the result)
-- The joint query union all ( with union all Join two separate subqueries )
select SNAME,SSEX,SBIRTHDAY from STUDENT union all select TNAME,TSEX,TBIRTHDAY from TEACHER
The stored procedure
-- create (create)/ Modify the (alter)1 Four stored procedures
alter proc sp_SMGetCity ---sp_SMGetCity Is the name of the stored procedure
(
@code nvarchar(50), -- The data type should be the same as the character being compared
@name nvarchar(50) output ,
@grade int=1
--' Notice here : The stored procedure assigns an initial value , Only in the last parameter '
---1 a @ Symbols are local variables
--- two @ Symbols are global variables
)
as
begin
select @name=Name from TBName where Code like @code+'%' and Grade=@grade --begin and end The middle is going to be executed SQL statements
print @name -- Band output parameter
end
declare @outname nvarchar(50) -- define 1 A variable
exec sp_SMGetCity'11',@outname output --@aa Assign a variable to an output parameter to receive the return value
select @outname
sp_help sp_SMGetCity -- See, sp_studentmanager The stored procedure creation information
sp_helptext sp_SMGetCity -- See, sp_studentmanager The stored procedure creation code
drop proc sp_SMGetCity -- Delete stored procedure
--return Only integer data can be returned
-- Delete stored procedure drop proc sp_Name
-- exec(@aa)-- perform @aa(SQL statements ) So I'm going to put parentheses , In the execution string SQL statements
The stored procedure is called by the stored procedure
as
begin
declare @return int
exec @return=sp_checkUser@id,@name -- The stored procedure is called by the stored procedure
if @return=0
print ' There is no repeat ,return Only integer types can be returned '
else
print ' User registered '
end
1 some examples
Database joint access authorization
alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from dbo.Operate
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from dbo.Operate )ta where newid =@num
if(@num>1)
set @ctitle+=@title+','
else
set @ctitle+=@title
set @num=@num-1
end
Declare @sql varchar(8000)
set @sql='select * from(select 1 "isture",rolename,modulename,operatename,role_ID,Module_id from vw_userrole group by rolename,modulename,operatename,role_ID,Module_id)a pivot(count(isture)for operatename in('+@ctitle+')) b'
exec(@sql)
Pager stored procedures, paging stored procedures
alter proc cutpage
(
@tablename nvarchar(100),---- The paging table
@columnname nvarchar(100), ---- Paging column
@ordertype nvarchar(100)='asc', ---- The sorting way
@pageindex int =1,
@pagecount int =1
)
as
begin
declare @aa nvarchar(max);
set @aa= 'select * from
(select *,ROW_NUMBER() over(order by '+@columnname+' '+@ordertype+') as uprow from '+@tablename+' ) as newtable
where uprow between '+cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(nvarchar(100), @pageindex*@pagecount)
exec (@aa) -- Here, @aa You have to put parentheses ()
end
exec cutpage 'SM_Class', 'classid'
Transaction Transaction
--- Transaction key statements ----
BEGIN TRANSACTION --- Transaction keyword transaction
DECLARE @errorSum INT
SET @errorSum=0 -- Initializes to zero error
update bank SET money=money+1000 where name=' zhang 3'
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where name=' li 4'
SET @errorSum=@errorSum+@@error -- Whether there are errors accumulated (@@error Is zero )
if @errorSum<>0
begin
print ' No success, no fault , The error code is: '
print @errorsum
rollback transaction
end
else
begin
print ' successful '
select * from Bank
commit TRANSACTION
end
The trigger Trigger
-- Concept: 1 A special stored procedure , Change the data in this table ( Increases the deletion ) , will automatically execute the implementation-defined statement
-- Features: cascading changes across related tables
-- Key words: trigger
alter trigger trigger_name_f
on SM_Class
for update --(for It is implemented before adding, deleting and modifying after After the addition, deletion and modification, instead of It's all the increments | delete | Change 】 are not implemented , Triggered before, but without changing the value )
as
begin
if update(remark) --- judge SM_Class In the table remark Whether the column has changed data
begin
select * from inserted --- A table for storing modified new values
select * from deleted ---- A table for storing modified old values
print 'remark Column changes '
end
else
print ' The other columns change '
print ' Test the trigger when modifying the table SM_Class ", display this sentence , and for This sentence precedes '
end
The cursor Cursor
-- A cursor is similar to sql dateReader 1 line 1 The read
--1 It is usually used as a last resort , A long time , Heavy server pressure , Eat more memory , broadband ,
-- The cursor is on 1 The traversal read of a collection
declare cur_test cursor
for
select pid,pname from ns_product
open cur_test
declare @id uniqueidentifier;
declare @name nvarchar(50);
-- read 1 line ( The first 1 The second read is of course the first 1 the )
fetch next from cur_test into @id,@name
-- judge 1 See if you read the data , Zero means you've read the data (@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
-- And read it 1 line
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test
Scattered knowledge
1) intercept string: substring(field name, starting position (the first place is 1), length of intercept)
select SUBSTRING(age,1,1) from person where ID=2
2) about GUID:
select NEWID()
insert person values('','',NEWID())
3) insert 1 table (person2) into another table (person1) (the number of columns should correspond)
insert person1
select column, column, column from person2
4) conditional statement (note: braces {} in C# are replaced by begin end in sql)
declare @x int ,@y int
set @x=1
set @y =2
if @x>@y
print 'x>y'
else
print 'x<y'
select code,name,grade,
case Grade
when '1' then ' province '
when '2' then ' The city '
when '3' then ' county '
end ' level '
from SM_PostCode
-------------------------------------------------------------
while (select MAX(DEGREE) from SCORE)<85
begin
if (select MAX(DEGREE) from SCORE where CNO='3-105')>=100
break
end
5) determine the existence of if exists(select * from TBName where CityName='22')
6) add the auto-growing column row_number over(order by **) ROW_NUMBER is the keyword over which column to sort according to
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID),* from SM_Class - is also an automatic line number. If there is a repeat, the line number will be matched and the next value will be automatically increased by 2
-- the results returned by one query statement serve as the data source table for the other query statement
select * from (select ROW_NUMBER() over(order by column name 1) the new column name here, * from TBName) the new table name where between 1 and 3
7) temporary tables
declare @table table(id uniqueidentifier,name varchar(50))
-- perform the insert operation (insert bar data) and return the hid field of this data and insert it into the id property of the @table table
insert images(Hname,Himage) output inserted.Hid into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table
------------------------------------------------------------
-- the following is the comparison of execution efficiency
--sql statement 1: execution requires s
declare @tempTable table(id varchar(20),name int,score datetime)
insert @tempTable(id,name,score)
select userID,userName,userScore from scoreTable
select * from @tempTable
--sql statement 2: only s is needed for execution
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles
8) operation on date and time
-- get Beijing time and international time from the database
select getdate(), getutcdate()
-- an increase in time (type of increase [year/month/day], increment, to whom add [current time/date]) dateAdd
select dateadd(YEAR,2,GETDATE()) -- add two years to the current year
-- subtraction of time
select DATEDIFF (HOUR, getdate (), getutcdate ()) -- hours of international time minus hours of current Beijing time (back minus front)
-- get the year, month, day and so on in time
select year(getdate())-year(birthday())
select year(getdate())-year('1988-10-07')
9) row and column conversion
select * from ( select * from TableName) a pivot(count(stuName)) for columnName in('aa','bb','cc','dd')
10) double quotation marks can only be used for table names and column names (no double quotation marks are acceptable)
set @aa ='select sd 'from SM_Class' -- notice that the original 'sd' in 'sd' is now written as' sd'
exec (@aa)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- here to pay more attention to -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
declare @bb nvarchar(max);
-- only "e-commerce class 2" can be used when using data values.
set @bb ='select * from SM_Class where ClassName=' e-commerce class 2 '-- note: the original' e-commerce class 2 'will be written as' e-commerce class 2'.
exec (@bb)
11) -- quickly create a table structure
select c.Cid,c.Ccount into newTB1 from ns_comment c where 1 < > 1
12) -- remember one more key point
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
-- @str variable containing SQL statement, define the variable in @str, N is marked as a string to receive the variable in @str (that is, @na = @bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) -- concurrency problem --
-- concept: multiple users interact with one object (library, table) at the same time
-- problem: dirty data, non-repeat reads, lost updates, phantom reads)
-- solution: SQL Server USES locks to ensure transaction integrity (Shared locks, exclusive locks, update locks, intent locks, schema locks, batch update locks)
14)