Usage differences between MySQL and SQL Server

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 SQL Server, and focuses on common stored procedures.

1. identifier qualifier

Database identifier qualifier
SQL Server []
MySQL''

2. String Addition

Database string Addition
Use SQL Server directly +
MySQL concat ()

3. isnull ()

Database isnull ()
SQL Server isnull ()
MySQL ifnull ()

Note: MySQL also has the isnull () function, but it has different meanings.

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 MySQL function 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 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 SQL Server 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

SQL Server

Declare @ tempShoppingCart table (ProductId int, Quantity int) insert into @ tempShoppingCart (ProductId, Quantity) select ProductId, quantity from nation where UserGuid = @ UserGuid declare @ productId int declare @ quantity int declare nation cursor for select ProductId, Quantity from @ override 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 your deallocate MySQL

Declare m_done int default 0; declare m_sectionId int; declare m_newsId int; declare _ cursor_SN cursor for select sesid ID, 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, 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, usually open the cursor on the table variable. Do not open the cursor directly on the data table.

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 <= (@ PageSize * (@ PageIndex + 1); end; of course, this is not the only method in SQL Server, but this method 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 (); there are too many differences between end MySQL and SQL Server, the above only lists some specific differences that I think will often encounter in the writing and storage process.