Some common usage differences between MySql and SqlServer & lt; reprint & gt;

Source: Internet
Author: User
Due to work reasons: the databases of the previous company all use MySql, so they have to use it. Therefore, I also learned some knowledge about MySql, but considering that I may not be able to use it in the future, I want to take a break at home while I am leaving, and I plan to sort out these things so that I can use them in the future, leave a reference resource. Considering that SQL server has been used

Due to work reasons: the databases of the previous company all use MySql, so they have to use it. Therefore, I also learned some knowledge about MySql, but considering that I may not be able to use it in the future, I want to take a break at home while I am leaving, and I plan to sort out these things so that I can use them in the future, leave a reference resource. Considering that SQL server has been used

Due to work reasons: the databases of the previous company all use MySql, so they have to use it. Therefore, I also learned some knowledge about MySql, but considering that I may not be able to use it in the future, I want to take a break at home while I am leaving, and I plan to sort out these things so that I can use them in the future, leave a reference resource. Considering that SQL server has been used, I plan to directly compare it with SQL Server.

This article mainly lists the differences between MySql and SqlServer, and focuses on common stored procedures.

1. identifier qualifier

SqlServer []
MySql ''

2. String Addition

SqlServer Direct use +
MySql Concat ()

3. isnull ()

SqlServer Isnull ()
MySql Ifnull ()
Note: MySql also has the isnull () function, but it has different meanings.

4. getdate ()

SqlServer Getdate ()
MySql Now ()

5. newid ()

SqlServer Newid ()
MySql Uuid ()

6. @ ROWCOUNT

SqlServer @ ROWCOUNT
MySql Row_count ()
Note: This MySql function is only valid for update, insert, and delete.

7. SCOPE_IDENTITY ()

SqlServer SCOPE_IDENTITY ()
MySql Last_insert_id ()

8. if... else...

SqlServer
IF Boolean_expression {SQL _statement | statement_block} [ELSE {SQL _statement | statement_block}] -- to define statement blocks, use the control flow keyword 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 and end if are required. Similarly, other process control statements are not listed here.

9. declare

In fact, both SqlServer and MySql have this statement to define variables, but the difference is that in MySql, DECLARE is only used for BEGIN... in the END compound statement, and must begin with the compound statement, before any other statement. This requirement is very BT when writing the cursor.

10. cursor writing

SqlServer
declare @tempShoppingCart table (ProductId int, Quantity int)insert into @tempShoppingCart (ProductId, Quantity)select ProductId, Quantity from ShoppingCart where UserGuid = @UserGuiddeclare @productId intdeclare @quantity intdeclare tempCartCursor cursor for select ProductId, Quantity from @tempShoppingCartopen tempCartCursorfetch next from tempCartCursor into @productId, @quantitywhile  @@FETCH_STATUS = 0beginupdate Product set SellCount = SellCount + @quantitywhere productId = @productIdfetch next from tempCartCursor into @productId, @quantityendclose tempCartCursordeallocate tempCartCursor
MySql
Declare m_done int default 0; declare m_pId int; declare m_newsId int; declare _ cursor_SN cursor for select pid, newsid from _ temp_SN; declare continue handler for not found set m_done = 1; create temporary table _ temp_SN select pid, newsid from pNews group by pid, newsid having count (*)> 1; open _ cursor_SN; while (m_done = 0) dofetch _ cursor_SN into m_pId, m_newsId; if (m_done = 0) then -- specific processing logic end if; end while; close _ cursor_SN; drop table _ temp_SN;

Note: to improve performance, the cursor is usually opened on the table variable. Do not open the cursor directly on the data table.

11. Paging Processing

SqlServer
create procedure GetProductByCategoryId(     @CategoryID int,     @PageIndex int = 0,     @PageSize int = 20,     @TotalRecords int output) asbegin     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 <= (@PageSize * (@PageIndex+1));      end;

Of course, SQL server does not only use this method, but it is quite common.

MySql
create procedure GetProductsByCategoryId(   in _categoryId int,   in _pageIndex int,   in _pageSize int,   out _totalRecCount int)begin    set @categoryId = _categoryId;   set @startRow = _pageIndex * _pageSize;   set @pageSize = _pageSize;    prepare PageSql from 'select sql_calc_found_rows * from product  where categoryId = ? order by ProductId desc limit ?, ?';   execute PageSql using @categoryId, @startRow, @pageSize;   deallocate prepare PageSql;   set _totalRecCount = found_rows(); end

There are too many differences between MySql and SqlServer. The above only lists some specific differences that I think will often encounter in the write stored procedure.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.