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

This article mainly introduces "what is the difference between the usage of MySQL and SQL Server". In daily operation, I believe many people have doubts about the difference between the usage of MySQL and SQL Server. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what is the difference between the usage of MySQL and SQL Server"! Next, please follow the editor to study!

This article will mainly list the differences between MySQL and SQL Server, and focus on the related contents of commonly used stored procedures.

1. Identifier qualifier

Database identifier qualifier

SQL Server []

MySQL ``

two。 String addition

Addition of database strings

SQL Server directly uses +

MySQL concat ()

3. Isnull ()

Database isnull ()

SQL Server isnull ()

MySQL ifnull ()

Note: MySQL also has an isnull () function, but the meaning is different.

4. Getdate ()

Database getdate ()

SQL Server getdate ()

MySQL now ()

5. Newid ()

Database newid ()

SQL Server newid ()

MySQL uuid ()

6. @ @ ROWCOUNT

Database @ @ ROWCOUNT

SQL Server @ @ ROWCOUNT

MySQL row_count ()

Note: this function of MySQL is only valid for update, insert, and delete.

7. SCOPE_IDENTITY ()

Database SCOPE_IDENTITY ()

SQL Server SCOPE_IDENTITY ()

MySQL last_insert_id ()

8. If... Else...

Database if... Else...

SQL Server

IF Boolean_expression {sql_statement | statement_block} [ELSE {sql_statement | statement_block}]-- to define a statement block, use the control flow keywords BEGIN and END.

MySQL

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF Note: for MySql, then, end if is a must. Similarly, there are other flow control statements, which are not listed here.

9. Declare

In fact, both SQL Server and MySQL have this statement to define variables, but the difference is that in MySQL, DECLARE is only used in BEGIN. In the END compound statement, and must be at the beginning of the compound statement, before any other statement. This requirement will feel very BT when writing cursors.

10. The way to write the cursor

SQL Server

Declare @ tempShoppingCart table (ProductId int, Quantity int) insert into @ tempShoppingCart (ProductId, Quantity) select ProductId, Quantity from ShoppingCart where UserGuid = @ UserGuid declare @ productId int declare @ quantity int declare tempCartCursor cursor for select ProductId, Quantity from @ tempShoppingCart open tempCartCursor fetch next from tempCartCursor into @ productId, @ quantity while @ @ FETCH_STATUS = 0 begin update Product set SellCount = SellCount + @ quantity where productId = @ productId fetch next from tempCartCursor into @ productId, @ quantity end close tempCartCursor deallocate tempCartCursor MySQL

Declare m_done int default 0; declare m_sectionId int; declare m_newsId int; declare _ cursor_SN cursor for select sectionid, newsid from _ temp_SN; declare continue handler for not found set m_done = 1; create temporary table _ temp_SN select sectionid, newsid from SectionNews group by sectionid, newsid having count (*) > 1; open _ cursor_SN; while (m_done = 0) do fetch _ cursor_SN into m_sectionId, masked newsID; if (m_done = 0) then-specific processing logic end if; end while Close _ cursor_SN; drop table _ temp_SN; Note: to improve performance, you usually open cursors on table variables, not directly on data tables.

11. Paging processing

SQL Server

Create procedure GetProductByCategoryId (@ CategoryID int, @ PageIndex int = 0, @ PageSize int = 20, @ TotalRecords int output) as begin declare @ ResultTable table (RowIndex int, ProductID int, ProductName nvarchar (50), CategoryID int, Unit nvarchar (10), UnitPrice money, Quantity int); insert into @ ResultTable select row_number () over (order by ProductID asc) as RowIndex, p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity from Products as p where CategoryID = @ CategoryID; select @ TotalRecords = count (*) from @ ResultTable Select * from @ ResultTable where RowIndex > (@ PageSize * @ PageIndex) and RowIndex

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.