ADO Connection Database
1) Get the connection string
Method 1: Remember to connect strings
connectionString=" Integrated Security=True; server=. ; database=DBName"
Method 2: In visual studio, click "View" à Service Explorer à Right-click "Data Connection" on the left, and select "Add Connection" à Service name: for a point. Select the database name, then click "Advanced", and then copy the connection string at the bottom
2) Configure the connection string in
<connectionStrings>
<addname="SQLconnectionStr"connectionString="Data Source=.;Initial Catalog=NetShopDB;Integrated Security=True"providerName=""/>
</connectionStrings>
3) Create a new SqlConnection class in the DAL layer, including static methods:
Remember to add Configuration reference and using; namespace first
public static string getConnectionStr()
{
return ["SQLconnectionStr"].ToString();
}
4) Call the getConnectionStr() static method in other classes of the DAL layer
string conStr= ();
Methods for executing SQL statements in DAL layer
ADO operation SQL statement: Method 1
public List<student> getData1(string myid, string myname)
{
// Use using here is flexible and convenient. You don't need to close manually after using the con, and the current connection will be automatically closed.
using(SqlConnection con=new SqlConnection(conStr) )
{
//Open the connection
();
string cmdStr = "select * from ns_user where userID=@myid and userName=@myname";
SqlCommand cmd = new SqlCommand(cmdStr,con);
// Use parameter serialization here to prevent injection attacks
(new SqlParameter("@myid", myid));
(new SqlParameter("@myname", myname));
//Execute the query and return the first column of the first row of the result set returned by the query. Ignore other columns or rows
//object myResult = ();
// Execute a Transact-SQL statement on the connection and return the number of affected rows. (Responsible for executing statements, such as adding insert, deleting delete, and changing update)
//int ResultRowCount = ();
SqlDataReader sdr = ();
List<student> ls = new List<student>();
while (())
{
student s = new student();
= sdr["sid"].ToString();
= sdr["sname"].ToString();
(s);
}
return ls;
}
}
Dataset(remember)
#region Get detailed information about teachers
public DataSet GetTeacherInfo()
{
using (SqlConnection conn = new SqlConnection(dbapp))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from table1 ", conn);
DataSet ds = new DataSet(); //Define a set of tables
(ds, "teacher");
return ds;
}
}
#endregion
ADO operation stored procedure:
#region
public int UserCheck(string userID,string userName)
{
using(SqlConnection con=new SqlConnection (conStr))
{
();
SqlCommand cmd = new SqlCommand();
= "sp_MYLogin"; //sp_MYLogin is the stored procedure name
= ;
= con;
// Assign values to stored procedures
//Assign method one (the "@name" in the first value bracket must be exactly the same as the "@name" in the stored procedure)
(new SqlParameter("@userID", userID));
//Assignment method two (assign the second value)
SqlParameter pwd = new SqlParameter("@pwd", , 50);
= userName;
= ;
(pwd);
//Define a variable to accept the return value of the stored procedure
SqlParameter result = new SqlParameter("@return", );
= ;
(result);
(); //Execute stored procedure
//Get the return value of the stored procedure stored in the SQL variable @result
int num = Convert.ToInt32(["@return"].Value); //Parameters is a collection ["@return"] which is its index
return num;
}
}
#endregion
Error log
catch (Exception ex)
{ //Error log
("UserInsert", , ());
return null;
}
Database Technology
Construction library table
Library creation statement
create database student --The created database name
on primary ---Specify the various parameters of the data file
(
name='studnet3_data', --All strings are separated by '
filename='E:\lx\student4_data.mdf', --The file name contains the path and name. Extension
size=3MB, ----Default size, if you do not write the size, the default is MB
maxsize=100MB, ----Maximum capacity
filegrowth=1MB ---Automatic growth/expansion, if so, no automatic expansion
)
log on -----Each parameter of log file
(
name='student5_log',
filename='E:\lx\student4_data.ldf',
size=1MB,
maxsize=10MB,
filegrowth=10% --10% is the maximum capacity %)
)
sp_helpdb student ---Query database name
sp_renamedb student,stu --Rename the database
drop database student --Delete database
Table creation statement
drop table person --Delete table
create table person --Create table
(
---Note: The following is the attribute (field name) first and the data type is behind
ID int primary key identity(2,1) not null, -- primary key is to set the primary key to ensure that the column value is unique and not empty. The starting value of identity(2,1) is, and the step size is
Name nvarchar(10) not null, ---not null means that it cannot be null
Sex bit not null, --bit is the bool type
age int default 18 , -- default 18 refers to automatically obtaining the default value
scroe decimal(4,1) check(score<=100) --4 refers to the total number of decimal places added before and after the decimal places, which represents the number of digits after the decimal places check is a check limit constraint
cardid int unique --unique refers to a unique key. When there are multiple columns of data in the table that need to be unique, columns other than the primary key need to be set as unique columns
)
Operation on tables
Modify the table structure and add delete constraints
alter table person --modify table structure
--add NameID int --Add attribute\field NameID column, add column
--drop column NameID --Delete column
--alter column ID int not null ---Add field not empty
--add constraint constraint constraint (pk_table name_column name|pk_column name)
--add constraint pk_ID primary key(ID) --Add primary key constraints when modifying tables
--add constraint ck_score check(score<150) --Add check constraints when modifying tables
--add constraint uk_cardi unique(cardid) --Add unique key constraint when modifying the table
--add constraint df_age default 19 for age --add default constraint when modifying table
--drop constraint CK__person__score__15502E78 --Delete constraint (format: drop constraint constraint constraint name)
Modify table information, add (insert), delete (update), check (select)
--Add record <insert table name values (parameters)> <1> String or date type plus '' <2>bit with or <2> Automatically grow columns without adding values
insert person values(12,'wang',0,23,32,34)
insert person(sex,age,cardid) values(0,23,32) --Selective insertion data ((sex,age,cardid)) refers to the data to be added manually
--Modify record <update table name set Modify object where conditions> <1>Modify multiple fields, with commas separated in the middle
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---Arithmetic operation is performed when modifying information
update person set age=19+1 --If you do not write where, all data will be modified
update person set age=SUBSTRING(age,1,1) where ID=1 --substring is the intercept of characters
--Delete record < delete table name where condition> (If there is no place, delete all records)
delete person where ID=1
Querying table
Single table query
select * from tableName -- find to display the entire table (all columns)
select column name 1, column name 2 from tableName -- display some columns
select Column name 1='number', column name 2='brand' from Product1 --Use the information in one of the columns uniformly
--Modify the column title when displaying (Method 1)
select 'number'=ProCode,'brand'=ProTypeName from Product1
--Modify the column title when displaying (Method 2)
Select Column name 1 'number', Column name 2 'brand' from Product1
--Arithmetic operations on the columns when displayed, and add columns when displayed
select column name 1, column name 2, column name 3*0.5 'After discount', Number from Product1
select distinct Column name 1 from tableName --Delete duplicate rows of the column when displayed
select top 3 * from Product1 --Show the first three columns
select top 50 percent * from Product1 -- displays the first % rows of the total number of rows
--and is and, or is or , condition is not: not column name = 'value'
select * from tableName where Number=15 and not age=12 and Name='aa' or sex=1
--Find data with score range 0 to 100
select * from Product1 where score<100 and score >=1
select * from Product1 where score between 1 and 100
--in, not in (including or not) The following are all data that searches for number as,
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 match character % can replace any number of characters <2> _ can replace a character <3>[] is a search range
select * from Product1 where ProCode like 'D%' -- find data of ProCode with the initial letter D
select * from Product1 where ProCode like '_S%' -- find procode data whose second character is S
select * from Product1 where column name 1 like '1%' -- even float type must be added ''
select * from Product1 where column name 1 like '_4_' -- find 3 data whose second character is and whose number of characters is
select * from Product1 where column name 1 like '_[5-9]%' -- Find column name 1 data whose second character is 5 to 9
--Find data that is empty, not empty
select *from Product1 where proValue is not null
select *from Product1 where proValue is null
go -- sort (desc descending asc ascending order, asc ascending order is done without writing anything)
select * from Product1 order by proValue desc --Sort provalue in descending order
select * from Product1 order by proValue asc --Sort provalue in ascending order
select * from Product1 order by Number --arrange Numbers in default (ascending order)
select * from Product1 order by Number desc,proValue asc --The second condition is executed only when the first condition is the same
go --Aggregation function
select MAX(proValue) from Product1 -- Find the maximum value in proValue
select min(proValue) from Product1 -- Find the minimum value in proValue
select sum(proValue) from Product1 -- find the sum of data in proValue
select avg(proValue) from Product1 -- Find the average value in proValue
select count(*) from Product1 --The number of rows in the search table* can also be replaced by column names
--group by group (where before group by, filter conditions after having group, where and having are filter conditions)
--Group: You can display the information of the grouped column and the information after grouping for statistics respectively
select column name 1,max(column name 2),min(column name 3) from tableName where proTypeName='TV' group by column name 1
select proTypeName,max(proValue),min(proValue) from Product1 group by proTypeName having count(proValue)>1
Multi-table query
--Inner join query the information shared by the two tables
--from query column name = column name is the association condition (the content of the column in the association condition must be consistent)
select * from tableName inner join Product2 on column name = column name
--Show some columns after query, p1.* means to display all columns of p1
select p1.*,proArea,proBrand from Product1 as p1 inner join Product2 on =
--Product1 as p1 means that Product1 has an alias p1 , as can be omitted
select * from Product1 as p1 inner join Product2 as p2 on =
--where query, omitted as <format:select * from table, table where association conditions>
select * from Product1 p1,Product2 p2 where =
--External connection --First display the data associated with the two tables, and then display the data that cannot be associated
go --<Format:select * from table left\right\full outer join table on association conditions>
select * from Product1 p1 left outer join Product2 p2 on = --Left outer join
select * from Product1 p1 right outer join Product2 p2 on = --right outer join
select * from Product1 p1 full outer join Product2 p2 on = --Full external connection
--Cross-connection (also called Cartesian connection: crazy connection, n-to-n connection, no correlation conditions)
--Format:select * from table cross join table
select * from Product1 cross join Product2
--Self-join (Self-query: It is to decompose a table into two tables for use)
select c1.*, from ST_class c1,ST_class c2 where = and ='College of Computer Science'
--Nested query
--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 returns a value
select* from Product1 where proValue=(select MAX(provalue) from Product1)
-- When a subquery returns multiple values, you can use any (return any one of the results [the smallest one]) all (return all the [the maximum] in the results)
--United Query union all (Use union all to connect two separate subqueries)
select SNAME,SSEX,SBIRTHDAY from STUDENT union all select TNAME,TSEX,TBIRTHDAY from TEACHER
Stored procedures
--Create/modify a stored procedure
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 compared characters
@name nvarchar(50) output ,
@grade int=1
--'Note here: the stored procedure can only be assigned to the last parameter'
---A @ symbol is a local variable
---The two @ symbols are global variables
)
as
begin
select @name=Name from TBName where Code like @code+'%' and Grade=@grade --begin and end are SQL statements to be executed in the middle
print @name -- with output parameters
end
declare @outname nvarchar(50) -- Define a variable
exec sp_SMGetCity'11',@outname output --@aa assigns the variable to the output parameter to receive the return value
select @outname
sp_help sp_SMGetCity --View the creation information of stored procedures named sp_studentmanager
sp_helptext sp_SMGetCity --View the creation code of stored procedure named sp_studentmanager
drop proc sp_SMGetCity --Delete stored procedure
--return can only return integer data
--Delete stored procedure drop proc sp_Name
-- exec(@aa)--Execute @aa(SQL statement), so add brackets and execute SQL statements in the string
Stored procedure calls stored procedures
as
begin
declare @return int
exec @return=sp_checkUser@id,@name --Stored procedure calls stored procedure
if @return=0
print 'No duplication, return can only return integer'
else
print 'User registered'
end
Some examples
Database joint search authorization
alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from )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)
Paging stored procedures, paging stored procedures
alter proc cutpage
(
@tablename nvarchar(100),----Paginated table
@columnname nvarchar(100), ----Paginated columns
@ordertype nvarchar(100)='asc', ----Sorting
@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 must be parenthesed ()
end
exec cutpage 'SM_Class', 'classid'
Transaction
---Explanation of key transaction statements---
BEGIN TRANSACTION --- Transaction keyword transaction
DECLARE @errorSum INT
SET @errorSum=0 --Initialized to, that is, no errors
update bank SET money=money+1000 where name='Zhang San'
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where name='Li Si'
SET @errorSum=@errorSum+@@error --Is there any error in the accumulation (@@error is non-zero)
if @errorSum<>0
begin
print 'Unsuccessful, there is an error, the error code is:'
print @errorsum
rollback transaction
end
else
begin
print 'Success'
select * from Bank
commit TRANSACTION
end
Trigger
--Concept: A special stored procedure, changing the data in this table (adding, deleting, and modifying), and the implementation definition statement will be automatically executed
--Features: Cascading modification across related tables
--Keyword: trigger
alter trigger trigger_name_f
on SM_Class
for update --(for is executed before adding, deleting and modifying after adding, deleting and modifying. Instead of all [adding, deleting, deleting, and modifying] is not executed. It is triggered before, but does not change the value)
as
begin
if update(remark) ---Judge whether the data changes in the remark column in the SM_Class table
begin
select * from inserted --- A table that stores the modified new values
select * from deleted ----The table that stores modified old values
print 'remark column changed'
end
else
print 'Other columns have changed'
print 'Test trigger, when modifying table SM_Class, this sentence is displayed, and for this sentence is first'
end
Cursor
--The cursor is similar to the reading of sql dateReader line by line
--It is generally used under unscrupulous circumstances, long time, high pressure on the server, more memory, broadband,
--The cursor is a traversal reading 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 a line (the first line is of course the first line)
fetch next from cur_test into @id,@name
--Judge whether the data has been read. The status is zero, which means that the data has been read (@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
-- Then read the next line
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test
Scattered knowledge points
1) Intercept the string: substring (field name, starting position (first position is 1), intercepted length)
select SUBSTRING(age,1,1) from person where ID=2
2) About GUID:
select NEWID()
insert person values('','',NEWID())
3) Insert a table (person2) into another table (person1) (the number of columns should correspond)
insert person1
select column, column, column from person2
4) Conditional statement (Note: The 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 'Surprise'
When '2' then '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 whether there are if exists( select * from TBName where CityName='22')
6) Add automatic growth column row_number over(order by **) ROW_NUMBER is the keyword over refers to which column is sorted according to
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID) ,* from SM_Class --- It is also an automatically growing line number. If there is duplication, the line number will be parallel, and the next value will be automatically added to two.
--The result returned by a query statement is used as the data source table of another query statement
select * from ( select ROW_NUMBER() over(order by column name 1) Here new column name, * from TBName) Here new table name where new column name between 1 and 3
7) Temporary table
declare @table table(id uniqueidentifier,name varchar(50))
--Execute the insert operation (insert the data), and return the hid field of this data and insert it into the @table table id attribute
insert images(Hname,Himage) output into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table
------------------------------------------------------------
--The following is a 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 required for execution
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles
8) Operations about date and time
--Get Beijing time and international time in the database
select getdate(), getutcdate()
--Add time (the type of increase [year/month/day], increment, who should add [current time/date] to) dateAdd
select dateadd(YEAR,2,GETDATE()) ----Add the current year to two years
--Subtraction of time DateDiff
select DATEDIFF(HOUR, getdate(), getutcdate()) --The hour of international time minus the hour of current Beijing time (the last one is reduced)
--Get the year, month, day and the same as
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 quotes can only be used for table names and column names (it is also possible without double quotes)
set @aa='select ClassName "sd" from SM_Class' --Note: '' The original 'sd' in it is now written as "sd"
exec (@aa)
--------------------------------------------------------------------------------------------------------------------------------
declare @bb nvarchar(max);
-- When using data values, you can only use "E-commerce Class 2"
set @bb ='select * from SM_Class where ClassName=''E-commerce Class 2''--Note: The original E-commerce Class 2 must be written as E-commerce Class 2'
exec (@bb)
11) --Quickly create table structure
select , into newTB1 from ns_comment c where 1<>1
12) --Remember the key points
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
--@str contains variables of SQL statements, define the variables in @str, N indicates that they are strings, and are used to receive variables of variables in @str (i.e. @na=@bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) ----------------------------------------------------------------------------------------------------------------------------
--Concept: Multiple users interact with an object (library, table) at the same time
-- Problem occurs: dirty data, non-repeated reads, missing updates, phantom reads)
--Solution: SQL Server uses locks to ensure transaction integrity (shared lock, exclusive lock, update lock, intent lock, schema lock, batch update lock)
14)
1) Get the connection string
Method 1: Remember to connect strings
connectionString=" Integrated Security=True; server=. ; database=DBName"
Method 2: In visual studio, click "View" à Service Explorer à Right-click "Data Connection" on the left, and select "Add Connection" à Service name: for a point. Select the database name, then click "Advanced", and then copy the connection string at the bottom
2) Configure the connection string in
Copy the codeThe code is as follows:
<connectionStrings>
<addname="SQLconnectionStr"connectionString="Data Source=.;Initial Catalog=NetShopDB;Integrated Security=True"providerName=""/>
</connectionStrings>
3) Create a new SqlConnection class in the DAL layer, including static methods:
Remember to add Configuration reference and using; namespace first
Copy the codeThe code is as follows:
public static string getConnectionStr()
{
return ["SQLconnectionStr"].ToString();
}
4) Call the getConnectionStr() static method in other classes of the DAL layer
Copy the codeThe code is as follows:
string conStr= ();
Methods for executing SQL statements in DAL layer
ADO operation SQL statement: Method 1
Copy the codeThe code is as follows:
public List<student> getData1(string myid, string myname)
{
// Use using here is flexible and convenient. You don't need to close manually after using the con, and the current connection will be automatically closed.
using(SqlConnection con=new SqlConnection(conStr) )
{
//Open the connection
();
string cmdStr = "select * from ns_user where userID=@myid and userName=@myname";
SqlCommand cmd = new SqlCommand(cmdStr,con);
// Use parameter serialization here to prevent injection attacks
(new SqlParameter("@myid", myid));
(new SqlParameter("@myname", myname));
//Execute the query and return the first column of the first row of the result set returned by the query. Ignore other columns or rows
//object myResult = ();
// Execute a Transact-SQL statement on the connection and return the number of affected rows. (Responsible for executing statements, such as adding insert, deleting delete, and changing update)
//int ResultRowCount = ();
SqlDataReader sdr = ();
List<student> ls = new List<student>();
while (())
{
student s = new student();
= sdr["sid"].ToString();
= sdr["sname"].ToString();
(s);
}
return ls;
}
}
Dataset(remember)
#region Get detailed information about teachers
public DataSet GetTeacherInfo()
{
using (SqlConnection conn = new SqlConnection(dbapp))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from table1 ", conn);
DataSet ds = new DataSet(); //Define a set of tables
(ds, "teacher");
return ds;
}
}
#endregion
ADO operation stored procedure:
Copy the codeThe code is as follows:
#region
public int UserCheck(string userID,string userName)
{
using(SqlConnection con=new SqlConnection (conStr))
{
();
SqlCommand cmd = new SqlCommand();
= "sp_MYLogin"; //sp_MYLogin is the stored procedure name
= ;
= con;
// Assign values to stored procedures
//Assign method one (the "@name" in the first value bracket must be exactly the same as the "@name" in the stored procedure)
(new SqlParameter("@userID", userID));
//Assignment method two (assign the second value)
SqlParameter pwd = new SqlParameter("@pwd", , 50);
= userName;
= ;
(pwd);
//Define a variable to accept the return value of the stored procedure
SqlParameter result = new SqlParameter("@return", );
= ;
(result);
(); //Execute stored procedure
//Get the return value of the stored procedure stored in the SQL variable @result
int num = Convert.ToInt32(["@return"].Value); //Parameters is a collection ["@return"] which is its index
return num;
}
}
#endregion
Error log
Copy the codeThe code is as follows:
catch (Exception ex)
{ //Error log
("UserInsert", , ());
return null;
}
Database Technology
Construction library table
Library creation statement
Copy the codeThe code is as follows:
create database student --The created database name
on primary ---Specify the various parameters of the data file
(
name='studnet3_data', --All strings are separated by '
filename='E:\lx\student4_data.mdf', --The file name contains the path and name. Extension
size=3MB, ----Default size, if you do not write the size, the default is MB
maxsize=100MB, ----Maximum capacity
filegrowth=1MB ---Automatic growth/expansion, if so, no automatic expansion
)
log on -----Each parameter of log file
(
name='student5_log',
filename='E:\lx\student4_data.ldf',
size=1MB,
maxsize=10MB,
filegrowth=10% --10% is the maximum capacity %)
)
sp_helpdb student ---Query database name
sp_renamedb student,stu --Rename the database
drop database student --Delete database
Table creation statement
Copy the codeThe code is as follows:
drop table person --Delete table
create table person --Create table
(
---Note: The following is the attribute (field name) first and the data type is behind
ID int primary key identity(2,1) not null, -- primary key is to set the primary key to ensure that the column value is unique and not empty. The starting value of identity(2,1) is, and the step size is
Name nvarchar(10) not null, ---not null means that it cannot be null
Sex bit not null, --bit is the bool type
age int default 18 , -- default 18 refers to automatically obtaining the default value
scroe decimal(4,1) check(score<=100) --4 refers to the total number of decimal places added before and after the decimal places, which represents the number of digits after the decimal places check is a check limit constraint
cardid int unique --unique refers to a unique key. When there are multiple columns of data in the table that need to be unique, columns other than the primary key need to be set as unique columns
)
Operation on tables
Modify the table structure and add delete constraints
alter table person --modify table structure
--add NameID int --Add attribute\field NameID column, add column
--drop column NameID --Delete column
--alter column ID int not null ---Add field not empty
--add constraint constraint constraint (pk_table name_column name|pk_column name)
--add constraint pk_ID primary key(ID) --Add primary key constraints when modifying tables
--add constraint ck_score check(score<150) --Add check constraints when modifying tables
--add constraint uk_cardi unique(cardid) --Add unique key constraint when modifying the table
--add constraint df_age default 19 for age --add default constraint when modifying table
--drop constraint CK__person__score__15502E78 --Delete constraint (format: drop constraint constraint constraint name)
Modify table information, add (insert), delete (update), check (select)
--Add record <insert table name values (parameters)> <1> String or date type plus '' <2>bit with or <2> Automatically grow columns without adding values
insert person values(12,'wang',0,23,32,34)
insert person(sex,age,cardid) values(0,23,32) --Selective insertion data ((sex,age,cardid)) refers to the data to be added manually
--Modify record <update table name set Modify object where conditions> <1>Modify multiple fields, with commas separated in the middle
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---Arithmetic operation is performed when modifying information
update person set age=19+1 --If you do not write where, all data will be modified
update person set age=SUBSTRING(age,1,1) where ID=1 --substring is the intercept of characters
--Delete record < delete table name where condition> (If there is no place, delete all records)
delete person where ID=1
Querying table
Single table query
Copy the codeThe code is as follows:
select * from tableName -- find to display the entire table (all columns)
select column name 1, column name 2 from tableName -- display some columns
select Column name 1='number', column name 2='brand' from Product1 --Use the information in one of the columns uniformly
--Modify the column title when displaying (Method 1)
select 'number'=ProCode,'brand'=ProTypeName from Product1
--Modify the column title when displaying (Method 2)
Select Column name 1 'number', Column name 2 'brand' from Product1
--Arithmetic operations on the columns when displayed, and add columns when displayed
select column name 1, column name 2, column name 3*0.5 'After discount', Number from Product1
select distinct Column name 1 from tableName --Delete duplicate rows of the column when displayed
select top 3 * from Product1 --Show the first three columns
select top 50 percent * from Product1 -- displays the first % rows of the total number of rows
--and is and, or is or , condition is not: not column name = 'value'
select * from tableName where Number=15 and not age=12 and Name='aa' or sex=1
--Find data with score range 0 to 100
select * from Product1 where score<100 and score >=1
select * from Product1 where score between 1 and 100
--in, not in (including or not) The following are all data that searches for number as,
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 match character % can replace any number of characters <2> _ can replace a character <3>[] is a search range
select * from Product1 where ProCode like 'D%' -- find data of ProCode with the initial letter D
select * from Product1 where ProCode like '_S%' -- find procode data whose second character is S
select * from Product1 where column name 1 like '1%' -- even float type must be added ''
select * from Product1 where column name 1 like '_4_' -- find 3 data whose second character is and whose number of characters is
select * from Product1 where column name 1 like '_[5-9]%' -- Find column name 1 data whose second character is 5 to 9
--Find data that is empty, not empty
select *from Product1 where proValue is not null
select *from Product1 where proValue is null
go -- sort (desc descending asc ascending order, asc ascending order is done without writing anything)
select * from Product1 order by proValue desc --Sort provalue in descending order
select * from Product1 order by proValue asc --Sort provalue in ascending order
select * from Product1 order by Number --arrange Numbers in default (ascending order)
select * from Product1 order by Number desc,proValue asc --The second condition is executed only when the first condition is the same
go --Aggregation function
select MAX(proValue) from Product1 -- Find the maximum value in proValue
select min(proValue) from Product1 -- Find the minimum value in proValue
select sum(proValue) from Product1 -- find the sum of data in proValue
select avg(proValue) from Product1 -- Find the average value in proValue
select count(*) from Product1 --The number of rows in the search table* can also be replaced by column names
--group by group (where before group by, filter conditions after having group, where and having are filter conditions)
--Group: You can display the information of the grouped column and the information after grouping for statistics respectively
select column name 1,max(column name 2),min(column name 3) from tableName where proTypeName='TV' group by column name 1
select proTypeName,max(proValue),min(proValue) from Product1 group by proTypeName having count(proValue)>1
Multi-table query
Copy the codeThe code is as follows:
--Inner join query the information shared by the two tables
--from query column name = column name is the association condition (the content of the column in the association condition must be consistent)
select * from tableName inner join Product2 on column name = column name
--Show some columns after query, p1.* means to display all columns of p1
select p1.*,proArea,proBrand from Product1 as p1 inner join Product2 on =
--Product1 as p1 means that Product1 has an alias p1 , as can be omitted
select * from Product1 as p1 inner join Product2 as p2 on =
--where query, omitted as <format:select * from table, table where association conditions>
select * from Product1 p1,Product2 p2 where =
--External connection --First display the data associated with the two tables, and then display the data that cannot be associated
go --<Format:select * from table left\right\full outer join table on association conditions>
select * from Product1 p1 left outer join Product2 p2 on = --Left outer join
select * from Product1 p1 right outer join Product2 p2 on = --right outer join
select * from Product1 p1 full outer join Product2 p2 on = --Full external connection
--Cross-connection (also called Cartesian connection: crazy connection, n-to-n connection, no correlation conditions)
--Format:select * from table cross join table
select * from Product1 cross join Product2
--Self-join (Self-query: It is to decompose a table into two tables for use)
select c1.*, from ST_class c1,ST_class c2 where = and ='College of Computer Science'
--Nested query
--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 returns a value
select* from Product1 where proValue=(select MAX(provalue) from Product1)
-- When a subquery returns multiple values, you can use any (return any one of the results [the smallest one]) all (return all the [the maximum] in the results)
--United Query union all (Use union all to connect two separate subqueries)
select SNAME,SSEX,SBIRTHDAY from STUDENT union all select TNAME,TSEX,TBIRTHDAY from TEACHER
Stored procedures
Copy the codeThe code is as follows:
--Create/modify a stored procedure
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 compared characters
@name nvarchar(50) output ,
@grade int=1
--'Note here: the stored procedure can only be assigned to the last parameter'
---A @ symbol is a local variable
---The two @ symbols are global variables
)
as
begin
select @name=Name from TBName where Code like @code+'%' and Grade=@grade --begin and end are SQL statements to be executed in the middle
print @name -- with output parameters
end
declare @outname nvarchar(50) -- Define a variable
exec sp_SMGetCity'11',@outname output --@aa assigns the variable to the output parameter to receive the return value
select @outname
sp_help sp_SMGetCity --View the creation information of stored procedures named sp_studentmanager
sp_helptext sp_SMGetCity --View the creation code of stored procedure named sp_studentmanager
drop proc sp_SMGetCity --Delete stored procedure
--return can only return integer data
--Delete stored procedure drop proc sp_Name
-- exec(@aa)--Execute @aa(SQL statement), so add brackets and execute SQL statements in the string
Stored procedure calls stored procedures
as
begin
declare @return int
exec @return=sp_checkUser@id,@name --Stored procedure calls stored procedure
if @return=0
print 'No duplication, return can only return integer'
else
print 'User registered'
end
Some examples
Database joint search authorization
Copy the codeThe code is as follows:
alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from )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)
Paging stored procedures, paging stored procedures
Copy the codeThe code is as follows:
alter proc cutpage
(
@tablename nvarchar(100),----Paginated table
@columnname nvarchar(100), ----Paginated columns
@ordertype nvarchar(100)='asc', ----Sorting
@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 must be parenthesed ()
end
exec cutpage 'SM_Class', 'classid'
Transaction
Copy the codeThe code is as follows:
---Explanation of key transaction statements---
BEGIN TRANSACTION --- Transaction keyword transaction
DECLARE @errorSum INT
SET @errorSum=0 --Initialized to, that is, no errors
update bank SET money=money+1000 where name='Zhang San'
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where name='Li Si'
SET @errorSum=@errorSum+@@error --Is there any error in the accumulation (@@error is non-zero)
if @errorSum<>0
begin
print 'Unsuccessful, there is an error, the error code is:'
print @errorsum
rollback transaction
end
else
begin
print 'Success'
select * from Bank
commit TRANSACTION
end
Trigger
Copy the codeThe code is as follows:
--Concept: A special stored procedure, changing the data in this table (adding, deleting, and modifying), and the implementation definition statement will be automatically executed
--Features: Cascading modification across related tables
--Keyword: trigger
alter trigger trigger_name_f
on SM_Class
for update --(for is executed before adding, deleting and modifying after adding, deleting and modifying. Instead of all [adding, deleting, deleting, and modifying] is not executed. It is triggered before, but does not change the value)
as
begin
if update(remark) ---Judge whether the data changes in the remark column in the SM_Class table
begin
select * from inserted --- A table that stores the modified new values
select * from deleted ----The table that stores modified old values
print 'remark column changed'
end
else
print 'Other columns have changed'
print 'Test trigger, when modifying table SM_Class, this sentence is displayed, and for this sentence is first'
end
Cursor
Copy the codeThe code is as follows:
--The cursor is similar to the reading of sql dateReader line by line
--It is generally used under unscrupulous circumstances, long time, high pressure on the server, more memory, broadband,
--The cursor is a traversal reading 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 a line (the first line is of course the first line)
fetch next from cur_test into @id,@name
--Judge whether the data has been read. The status is zero, which means that the data has been read (@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
-- Then read the next line
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test
Scattered knowledge points
1) Intercept the string: substring (field name, starting position (first position is 1), intercepted length)
select SUBSTRING(age,1,1) from person where ID=2
2) About GUID:
select NEWID()
insert person values('','',NEWID())
3) Insert a table (person2) into another table (person1) (the number of columns should correspond)
insert person1
select column, column, column from person2
4) Conditional statement (Note: The braces {} in C# are replaced by begin end in SQL)
Copy the codeThe code is as follows:
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 'Surprise'
When '2' then '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 whether there are if exists( select * from TBName where CityName='22')
6) Add automatic growth column row_number over(order by **) ROW_NUMBER is the keyword over refers to which column is sorted according to
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID) ,* from SM_Class --- It is also an automatically growing line number. If there is duplication, the line number will be parallel, and the next value will be automatically added to two.
--The result returned by a query statement is used as the data source table of another query statement
select * from ( select ROW_NUMBER() over(order by column name 1) Here new column name, * from TBName) Here new table name where new column name between 1 and 3
7) Temporary table
declare @table table(id uniqueidentifier,name varchar(50))
--Execute the insert operation (insert the data), and return the hid field of this data and insert it into the @table table id attribute
insert images(Hname,Himage) output into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table
------------------------------------------------------------
--The following is a 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 required for execution
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles
8) Operations about date and time
--Get Beijing time and international time in the database
select getdate(), getutcdate()
--Add time (the type of increase [year/month/day], increment, who should add [current time/date] to) dateAdd
select dateadd(YEAR,2,GETDATE()) ----Add the current year to two years
--Subtraction of time DateDiff
select DATEDIFF(HOUR, getdate(), getutcdate()) --The hour of international time minus the hour of current Beijing time (the last one is reduced)
--Get the year, month, day and the same as
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 quotes can only be used for table names and column names (it is also possible without double quotes)
set @aa='select ClassName "sd" from SM_Class' --Note: '' The original 'sd' in it is now written as "sd"
exec (@aa)
--------------------------------------------------------------------------------------------------------------------------------
declare @bb nvarchar(max);
-- When using data values, you can only use "E-commerce Class 2"
set @bb ='select * from SM_Class where ClassName=''E-commerce Class 2''--Note: The original E-commerce Class 2 must be written as E-commerce Class 2'
exec (@bb)
11) --Quickly create table structure
select , into newTB1 from ns_comment c where 1<>1
12) --Remember the key points
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
--@str contains variables of SQL statements, define the variables in @str, N indicates that they are strings, and are used to receive variables of variables in @str (i.e. @na=@bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) ----------------------------------------------------------------------------------------------------------------------------
--Concept: Multiple users interact with an object (library, table) at the same time
-- Problem occurs: dirty data, non-repeated reads, missing updates, phantom reads)
--Solution: SQL Server uses locks to ensure transaction integrity (shared lock, exclusive lock, update lock, intent lock, schema lock, batch update lock)
14)