Shulou(Shulou.com)05/31 Report--

What is the syntax of SQLServer database? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

-- data operation

SELECT-retrieves rows and columns from a database table

INSERT-add new rows to the database table DELETE-delete rows from the database table

UPDATE-- updating data in database tables-- data definition

CREATE TABLE-create a database table

DROP TABLE-Delete tables from the database

ALTER TABLE-modify database table structure

CREATE VIEW-create a view

DROP VIEW-removes a view from the database

CREATE INDEX-create an index for the database table

DROP INDEX-removes an index from the database

CREATE PROCEDURE-create a stored procedure

DROP PROCEDURE-removes stored procedures from the database

CREATE TRIGGER-create a trigger

DROP TRIGGER-remove triggers from the database

CREATE SCHEMA-add a new schema to the database

DROP SCHEMA-removes a schema from the database

CREATE DOMAIN-create a data range

ALTER DOMAIN-change the domain definition

DROP DOMAIN-removes a domain from the database

-- data control

GRANT-Grant access to the user

DENY-deny user access

REVOKE-remove user access rights-transaction control

COMMIT-ends the current transaction

ROLLBACK-aborts the current transaction

SET TRANSACTION-defines the data access characteristics of current transactions

-- programmed SQL

DECLARE-set cursors for queries

EXPLAN-describe the data access plan for the query

OPEN-search the query results and open a cursor

FETCH-retrieve a row of query results

CLOSE-closes the cursor

PREPARE-prepares SQL statements for dynamic execution

EXECUTE-execute SQL statements dynamically

DESCRIBE-describes the prepared query

-Local variable declare @ id char (10)-- set @ id = '10010001'select @ id =' 10010001'

-Global variable-

Must start with @ @

-- IF ELSE declare @ x int @ y int @ z int select @ x = 1 @ y = 2 @ zonal 3 if @ x > @ y print'x > Yachi-print the string'x > y'else if @ y > @ z print'y > z'else print'z > y'--CASE use pangu update employee set e_wage = case when job_level ='1' then e_wage*1.08 when job_level ='2' then e_wage*1.07 when job_level = '3' then e_wage*1.06 else e_wage*1.05 end--WHILE CONTINUE BREAK declare @ x int @ y int @ c int select @ x = 1 @ y while @ x < 3 begin print @ x-- print the value of variable x while @ y < 3 begin select @ c = 100% print @ c-- the value of print variable c select @ y = @ y + 1 end select @ x = @ x + 1 select @ 1 end--WAITFOR-- example, etc. Wait 1 hour, 2 minutes and 3 seconds before executing the SELECT statement waitfor delay'01 select 02 select * select * from employee-- for example, wait until 11: 08 pm before executing the SELECT statement waitfor time'23

* SELECT***

Select * (column name) from table_name (table name) where column_name operator value ex: (host) select * from stock_information where stockid = str (nid) stockname = 'str_name' stockname like'% find this% 'stockname like' [a-zA-Z]%'- (range of values specified) stockname like'[^ Fmurm]%'- (^ exclude specified range)-- -wildcard characters can only be used in where clauses that use the like keyword) or stockpath = 'stock_path' or stocknumber < 1000 and stockindex = 24 not stock*** =' man' stocknumber between 20 and 100 stocknumber in (10 order by stockid desc 20 and 30) order by stockid desc (asc)-sort Desc- descending order, asc- ascending order order by 1 by column number stockname = (select stockname from stock_information where stockid = 4)

-subquery-unless you can ensure that the inner select returns only the value of one row,-use an in qualifier in the outer where clause

Select * from table1, table2 where table1.id * = table2.id-left outer join, some in table1 but not in table2 table1.id = * table2.id-right external connection select stockname from table1 union [all]-union merge query result set, all- retains duplicate rows select stockname from table2

* insert***

Insert into table_name (Stock_name,Stock_number) value ("xxx", "xxxx") value (select Stockname, Stocknumber from Stock_table2)-value is the select statement

* update***

Update table_name set Stockname = "xxx" [where Stockid = 3] Stockname = default Stockname = null Stocknumber = Stockname + 4

* delete***

Delete from table_name where Stockid = 3 truncate table_name-deletes all rows in the table and still maintains the integrity of the table drop table table_name-completely deletes the table

* alter table***-modify database table structure

Alter table database.owner.table_name add column_name char (2) null. Sp_help table_name-shows the existing features of the table create table table_name (name char (20), age smallint, lname varchar (30)) insert into table_name select. -implement the method of deleting columns (creating a new table) alter table table_name drop constraint Stockname_default-delete the default constraint of Stockname

-Statistical function-

AVG-- average COUNT-- Statistical number MAX-- maximum MIN-- minimum SUM-- Sum

-- AVG use pangu select avg (e_wage) as dept_avgWage from employee group by dept_id-- MAX-- the name of the highest-paid employee use pangu select e_name from employee where e_wage = (select max (e_wage) from employee)

-date function-

DAY ()-- the function returns the date value MONTH () in date_expression_r-- the function returns the month value YEAR () in date_expression_r-- the function returns the year value DATEADD (,) in date_expression_r-- the function returns the new date DATEDIFF (,) generated by the specified date date plus the specified extra date interval number-- the function returns the difference in datepart between two specified dates DATENAME ( )-- the function returns the specified part of the date DATEPART (,) as a string-- the function returns the specified part of the date GETDATE () as an integer value-- the function returns the current date and time of the system in the default format of DATETIME

-system function-

APP_NAME ()-the function returns the name of the currently executing application COALESCE ()-the function returns the value COL_LENGTH (,) of the first non-NULL expression in many expressions-the function returns the length value COL_NAME of the specified field in the table )-- the name of the specified field in the function return table, that is, the column name DATALENGTH ()-- the actual length of the data returned by the function DB_ID (['database_name'])-- the number of the database returned by the function DB_NAME (database_id)-- the name of the database returned by the function HOST_ID ()-- the name of the server computer returned by the function HOST_NAME ()-- Function returns the name of the server-side computer IDENTITY ([ Seed increment]) [AS column_name])-- the IDENTITY () function is only used in the SELECT INTO statement to insert an identity column column into the new table ISDATE ()-- the function determines whether the given expression is a reasonable date ISNULL ( )-- the function replaces the NULL value in the expression with the specified value ISNUMERIC ()-- the function determines whether the given expression is a reasonable numeric value NEWID ()-- the function returns a numeric value of type UNIQUEIDENTIFIER NULLIF (,)-- the NULLIF function returns the NULL value when expression_r1 is equal to expression_r2. If it is not equal, it returns expression_r1 to read the above. Have you mastered the methods of SQLServer database syntax? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.