Common SQL statements for database ms
Truncate table test -- delete all data in the table
Alter table test alter column bb varchar (10) -- modify the field definition in the table
Alter table aa add constraint fk foreign key ([aa]) references bb ([aa]) -- foreign key constraint
Alter table bb add constraint pr primary key (aa) -- primary key
Exec sp_addtype hezhiwu, 'varchar (11) ', 'not Null'
-- Create a user-defined data type named hezhiwu. Based on the varchar data type, this column cannot be null
Exec sp_addtype hhh, 'int', 'not Null'
Sp_droptype hhh -- delete this type
Create table abc
(
Aa int not null primary key identity (1, 1), -- define the ID field
Bb char (4)
)
Alter table table1113 add columncc int -- add field columncc
Alter table table1113 add constraint df default 'I am the default' for columnb -- add a default constraint to a field
Alter table table1113 drop column columnb -- delete a field. Before deleting a field, delete the constraint.
Alter table table1113 drop constraint df -- delete df constraints
The data type smallint indicates-32768 to 32768, which is equivalent to the int data type of VB.
Update table1113 set columnd = columnd + 1 where columnid = 2
Dbcc sqlperf (logspace)
Sp_helpdb @ dbname = zw
Dbcc shrinkdatabase (zw) -- compresses the zw database
Create table table1113d
(
Aa int not null primary key clustered identity -- clustered clustering index, nonclustered non-clustering index, the default is clustering index
)
Create table table1113e
(
Aa char (20) constraint un unique -- define unique constraints
)
Alter table table1113e add constraint una unique (bb) -- add the unique constraint to field bb
Alter table table1113e add constraint unn unique nonclustered (cc) -- add a non-clustered unique constraint to field bb
Create default myname as 'I am value' -- create default value
Exec sp_bindefault myname, 'table. BB' -- bind the default object myname to the bb field in the table.
Exec sp_unbindefault 'tableadf. bb '-- deletes the default value of the tableadf. bb column of the table.
Drop default mynameb -- delete the default object. You must bind and delete all instances to delete the default object.
Create rule mynamb as @ aa> 100 -- create a rule
Create rule aabc as @ aa like 'ABC % '-- the rule can also use wildcards
Create rule fkalf as @ dskf in (, 9) -- another method for creating rules
Exec sp_bindrule mynamb, 'tablename. columnbb '-- bind the rule to the columnbb column of the tablename table.
Exec sp_unbindrule 'tablename. Columnbb' -- unbind rules
Drop rule mynamb -- delete rule mynamb object
Sp_helpindex @ objname = 'gxa '-- view the gxa index of the table.
Create unique index mynameaab on gxa (bb) -- create a unique non-clustered index mynameaab based on "bb column in Table gxa"
Drop index gxa. mynameaab -- delete the index of the object named mynameaab in table gxa. Note: gxa is a table and mynameaab is an index object, not a column.
Sp_helptext @ objname = 'viewa '-- view the creation statements, rules, stored procedures, and triggers of the view viewa.
--------- Create an encrypted view --------------------
Create view viewc
With encryption
As
Select id from adfadf
----------------------------
Create view viewname (column name) as select aaa + '-' + bbb from tablename -- if select does not have a column name, you can create a view like this
Sp_rename viewname, newviewname -- rename the View Name. You can also rename stored procedures, indexes, and so on ......
----- Modify the View definition -------
Alter view view_name [(column [,... n]
With encryption
As
Select_statement
[With check option]
Example:
Alter view newviewname with encryption as select id from tablename
--------------------------
Drop view name -- Delete view
Exec sp_addtype typename, 'char (50) ', null -- add a custom data type and allow null
-------------- Use of SQL variables -------------------------------
Declare @ var1 char (20), @ var2 int/* definition variable */
Set @ var1 = 'This a test'/* set value assignment */
Select @ var2 = 100/* select value */
Select @ var1, @ var2/* output variable */
------------------------------------------------------
Select distinct aa, bb from aa -- delete duplicate rows, instead of deleting duplicate records of field aa, distinct should be placed at the beginning of the field
Select aa, bb into tablename from aa where aa = 'abc' -- save the query result as a table
Select New column name = columnname + 'connection' from hahaa -- another method
----- Null value judgment --------
Is null
Is not null
----------------------
----------- Compute clause -----------------------------------------------------------------
Select * from tablename compute sum (aa) -- display all data of tablename and the total of field aa
Equivalent
Select * from tablename
Select avg (aa) from tablename
Select * from tablename where aa <10 compute avg (bb) -- displays the average value of field bb in all aa <10 data and aa <10 records
Bytes ------------------------------------------------------------------------------------------------------------
Select * from sysobjects where xtype = 'U' -- query all tables in this database
-------------------------------------------------------------
Use master
Select * from sysdatabases -- query all databases
-------------------------------------------------------------
Begin... end -- if the while clause is not followed by a statement, you must use begin... end.
Rand () -- Returns a random floating point number between 0 and 1.
Str -- convert a number into a string, for example, str (123)
Char (num) -- returns the ASCII code corresponding to num, for example, char (65) returns uppercase
Substring (expression, start, length)
-- Return the length from the start character of express, for example, substring ('abcdef', 2, 3) returns bcd.
Len (string_expression) -- Returns the number of strings, excluding trailing spaces.
Select replace ('abcdefghicde', 'Cde', 'XXX') -- REPLACE cde with xxx, and return abxxxfghixxx.
Replicate (character_expression, integer_expression)
-- Returns abababab with the specified number of repeated characters, for example, replicate ('AB', 4).
Reverse ('string') -- returns the reverse string 'string'
Space (n) -- Returns n repeated spaces, where n is an integer
STUFF (character_expression, start, length, character_expression)
-- Delete characters of the specified length and insert another group of characters at the specified start point.
-- For example, print stuff ('abcdefg', 'XXX') returns aXXXg
UPPER/LOWER -- convert to uppercase/lowercase letters
Col_name (table_id, column_id) -- returns the name of the column corresponding to the specified table and column ID.
Col_length ('Table _ name', 'column _ name') -- returns the definition length of the specified column.
Db_id (['database _ name']) -- returns the database ID.
Db_name (['database _ name']) -- returns the database name.
Host_name () -- returns the host name.
User_name (user_id) -- returns the user name
Getdate () -- returns the current date and time of the system.
Dateadd (datepart, number, date)
-- Returns the time interval specified by the date value plus the datepart and number parameters.
-- Select dateadd (d, 1, '2017-11-16 ') returns 2006-11-17
Datediff (datepart, date1, date2)
-- Returns the interval between date1 and date2.
-- Datediff (d, '2017-11-16 ', '2017-11-16') returns 2006
Datename (datepart, date)
-- Returns the corresponding string of the specified part of the date.
-- Ddatename (d, '1970-11-16 ') returns 16, of which 16 is of the string type.
Datepart (datepart, date)
-- Returns the corresponding string of the specified part of the date.
-- Datepart (d, '2017-11-16 ') returns 16, of which 16 is a numeric type.
--------------------------------------------------------------------------
Declare @ uid varchar (20)
Declare @ num int
Select @ uid = uid, @ num = num from taa where uid = 'idname'
-- Assign the uid and num records of the fields whose uid is idname to the @ uid and @ num variables.
-------------------------------------------------------------------------
------------- Function definition --------------------------------------------------------------
Create function mynameab (@ vara int) returns varchar (20)
Begin
Declare @ stra varchar (20)
Set @ stra =''
If @ vara <10
Begin
Set @ stra = 'parameter less than 10'
End
Else
Begin
Set @ stra = 'parameter greater than or equal to 10'
End
Return @ stra
End
Print dbo. mynameab (82)
Bytes ------------------------------------------------------------------------------------
*********** ****************************
Create proc pa as select * from proca -- define a stored procedure without parameters
Exec pa -- execute the stored procedure
---------------------- Create a stored procedure with parameters --------------------------------------------
Create proc pbb
(
@ Num int,
@ Str varchar (20 ),
@ Uid int
)
As
Select * from proca where bb <@ num and aa like '%' + @ str + '%' and uid <@ uid
Exec pbb 50, 'AfD', 100 -- executes the stored procedure with parameters. The parameters correspond to the parameters defined before as, instead of the parameters defined in SQL statements.
Bytes ------------------------------------------------------------------------------------------------------------
------------- Usage of output keywords ----------------------------------------------------------------------------------
Create proc pdd
(
@ A int,
@ B int,
@ C int output
)
As
Select @ c = @ a + @ B + 5
Declare @ cc int
Exec pdd 2, 3, @ cc output -- note that the output keyword is behind @ cc.
Print @ cc -- return 10
Bytes -------------------------------------------------------------------------------------------------
-------------- Modify the stored procedure alter proc --------------------------------------
Alter proc pdd
(
@ A int,
@ B int
)
As
Select @ a + @ B
-------------------------------------------------------------------------
**************************************** **************************************** ******************
---------------- Define the table alias ---------------------------------------------
Select a. text, a. id from syscomments a -- define syscomments as an alias
------------------------------------------------------------------------
----------------- View the source code of the stored procedure ----------------------------------------------
Select sysobjects. id, syscomments. text
From sysobjects inner join syscomments on sysobjects. id = syscomments. id where sysobjects. type = 'p' and sysobjects. name = 'pdd'
Go
Sp_helptext pdd -- Method 2
---------------------------------------------------------------
Grant execute on pdd to gh -- grant the permission to run the stored procedure pdd to the gh role (User) pdd stored procedure name, gh role, user name
* *************************** Trigger ********* **************************************** ***
Create trigger tria on aabb
With encryption
For delete, insert, update
As
Print 'table aabb performs the delete, insert, or update operation! '
------------------------------------------------------------------------------
Create trigger trib on aabb
For update
As
If update (aa)
Print 'column aa of table aabb is updated'
---------------------------------------------------------------------------------
When a record is inserted into table testa, the trigger is triggered and the record inserted in table testb is also inserted.
And insert time in dd in testb table
Create table testa
(
Uid varchar (20 ),
Con varchar (20)
)
Go
Create table testb
(
Uid varchar (20 ),
Con varchar (20 ),
Dd varchar (20)
)
Go
Create trigger testatrig on testa for insert
As
Declare @ aa varchar (20)
Declare @ bb varchar (20)
Select @ aa = uid, @ bb = con from testa
Insert into testb values (@ aa, @ bb, getdate ())
-----------------------------------------------------------------------------
Create trigger nodel on testb for delete
As
Print 'Sorry, you cannot delete the data in table testb'
Rollback transaction -- rollback
-- Note: truncate cannot be triggered, that is, records can still be deleted using truncate table.
-------------------------------------------------------------------------------
Alter table tt79 disable trigger tt79tri -- disable (disable) trigger tt79tri in tt79 table
Alter table tt79 enable trigger tt79tri -- enable the (enable) trigger tt79tri in the tt79 table
**************************************** **************************************** **************
Bytes ----------------------------------------------------------------------------------------------
Select * from aa where column> any (select num from bb)
-- (In the aa table, the column field value) is greater than (select num from bb result set minimum value) to enter the result set
-- If it is all, it is greater than the maximum value, that is, it is opposite to any.
Select * from aa where column> any (select num from bb)
Equivalent
Select * from aa where column> (select min (num) from bb)
Select * from aa where column> all (select num from bb)
Equivalent
Select * from aa where column> (select max (num) from bb)
In is equal to = any
Select * from anya where aa = any (select aa from anyb) -- Replace = any with in
Bytes --------------------------------------------------------------------------------------------------
* *********************** You can use the like and wildcard ********* for the check constraint ********* **************************************** ********
------------------------------------------------------------------------------
Create table aabbcc
(
Aa int,
Bb varchar (20 ),
Constraint dfl check (bb is null) -- The bb column must be null.
)
Insert into aabbcc (aa, bb) values (12, 'fadf') -- cannot be inserted because bb is not null
Insert into aabbcc (aa) values (12) -- Yes, because bb is null
-------------------------------------------------------------------------------
Create table aabbcc
(
Aa int,
Bb varchar (20 ),
Constraint dfl check (bb like 'ABC % ') -- The bb column can be inserted only with letters starting with abc.
)
Insert into aabbcc (bb) values ('aabb ') -- cannot be inserted because it is not a string starting with abc.
Insert into aabbcc (bb) values ('abcany') -- can be inserted, because it starts with abc
Bytes -----------------------------------------------------------------------------------------------
Create table aabbcc
(
Aa int,
Bb varchar (20 ),
Constraint dfl check (bb like '[a-Z] [a-Z] [0-9] [0-9]') -- Four characters, the first two letters, the last two digits are numbers with a total of four characters.
)
Insert into aabbcc (aa, bb) values (123, 'ad12') -- insert
Insert into aabbcc (aa, bb) values (123, 'a812') -- cannot insert
Insert into aabbcc (aa, bb) values (123, 'ad122') -- cannot insert
Bytes --------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Create table aabbcc
(
Mm varchar (20 ),
Constraint mail check (mm like '% @ %') -- the string must contain @ to be inserted.
)
Insert into aabbcc (mm) values ('ABCD') -- cannot be inserted, conflict with the check constraint
Insert into aabbcc (mm) values ('adf @ ') -- can be inserted
Insert into aabbcc (mm) values ('@') -- insert
Insert into aabbcc (mm) values ('@ fad') -- can be inserted
---------------------------------------------------------------------------------
Bytes -------------------------------------------------------------------------------------
Create table aabbcc
(
Mm int,
Constraint nuk check (mm between 10 and 20) -- only numbers greater than 10 and less than 20 can be inserted.
)
Insert into aabbcc (mm) values (12) -- insert
Insert into aabbcc (mm) values (9) -- cannot insert
Insert into aabbcc (mm) values (20) -- insert
Insert into aabbcc (mm) values (21) -- cannot insert
Bytes --------------------------------------------------------------------------------------
-------------------------------------------------------
Create table aa (aa int) -- create a table
Insert into aa (aa) values (10) -- insert a record
Alter table aa add constraint con check (aa> 50) -- The validation constraint cannot be added and the statement cannot be executed successfully.
Alter table aa with nocheck add constraint con check (aa> 50) -- unverified constraints can be added.
Insert into aa (aa) values (10) -- in some cases, data smaller than or equal to 50 cannot be inserted.
Alter table aa nocheck constraint con -- disable constraint nocheck
Insert into aa (aa) values (10) -- Records can be inserted at this time, because nocheck constraints are disabled.
Alter table aa check constraint con -- enable check constraints
Insert into aa (aa) values (10) -- Unable to insert because constraints are enabled
Bytes ---------------------------------------------------------------------------------------
**************************************** **************************************** ******************
The default value can be written as follows: default ('aaa') or default (getdate ())
----------------------------------------------------------------
Use expressions for computed columns
The following example shows how to use the expression (low + high)/2) to calculate the myavg calculation column.
Create table mytable
(
Low int,
High int,
Myavg AS (low + high)/2 -- calculation column
)
----------------------------------------------------------------------
DATALENGTH returns the number of bytes occupied by any expression. For example, print datalength (1.1)
