Comparison between MYSQL and MSSQL and MYSQLMSSQL

Comparison between MYSQL and MSSQL and MYSQLMSSQL

 

Recently, I was modifying the MSSQL statements related to a company's product to statements that can be executed on MYSQL.

 

L Advantage Analysis:

MYSQL is short, easy to use, easy to use, and free to use. Compared with other databases, there are more distinctive and practical syntaxes. SQL can be regarded as a large database. It is stable and can be used as a data warehouse of a general large system. It runs faster than MYSQL. (This advantage is obvious in the case of massive data ).

L disadvantage analysis: it is difficult for MYSQL to act as a data warehouse of a large system. It runs slowly, is not stable enough, and may cause a disconnection. The price of SQL SERVER is expensive (of course not to mention 5 yuan piracy). It is more difficult to use than MYSQL. After all, it is much bigger.

 

 

Syntax comparison between MYSQL and MSSQL:

Summary of other blog: http://blog.csdn.net/shiqijiamengjie/article/details/50396793

What are the differences between SQL Server and Oracle and MySQL?

Https://www.zhihu.com/question/19866767

 

1. mysql supports enum, set, and SQL server.
2. mysql does not support nchar, nvarchar, and ntext
3. the incremental Statement of mysql is AUTO_INCREMENT, while that of mssql is identity)
4. mssql by default, the default value of the table creation statement everywhere is (0), but it is not allowed to contain square brackets in mysql.
5. mysql needs to specify the storage type for the table
6. The mssql identifier is [] and [type], indicating that it is different from the keyword.
7. mssql supports the getdate () method to obtain the current time and date, but mysql can divide the date type and time type to obtain the current date as cur_date (), and the current complete time is the now () function.
8. mssql does not support replace into statements, but in the latest sql20008, it also supports merge syntax
9. mysql supports insert into table1 sett1 = '', t2 ='', but mssql does not.
10. mysql supports insert into tabl1values)
11 mssql does not support limit statements. Unfortunately, it can only replace limt 0, N, row_number () over () with the top function to replace limit N, M
12. When creating a table, mysql must specify a storage engine type for each table, while mssql only supports one storage engine.

Create unique nonclustered index Index_uc_protectedmembers_username_appid on dbo. uc_protectedmembers (Username asc, appid asc)

If (condition) then

End if;

Or

If (condition) then

Else

End if

Or

If (condition) then

Elseif (note that it cannot be written as Else if)

Elseif

...

End if

(Note: It must be called as follows)

Set @ cnt = 'select * from Table name ';

Prepare str from @ cnt;

Execute str;

 

Call function name (the stored procedure name of SQLServer) ('parameter 1', 'parameter 2 ',......)

 

1) function for obtaining the current date: curdate (), current_date ()

2) obtain the current time function: curtime ();

3) Get the current date + time: now ();

4) MySQLdayof... function: dayofweek (), dayofmonth (), and dayofyear () return the date parameters in the week, month, or year respectively.

5) (Note: Sunday = 1, Monday = 2, Tuesday = 3 ,......)

6) return the number of days of the month: select day (last_day (now ()));

7) MySQL adds a time interval for the date: date_add ()

8) selectdate_add (CURRENT_DATE (), interval 'Day to be added ') as Fdate

9) MySQL is a date minus a time interval: date_sub ()

10) selectdate_sub ('2017-01-01 00:00:00 ', interval '1' day_second );

11) MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2)

12) MySQL patchwork Date and Time Functions: makdedate (year, dayofyear), maketime (hour, minute, second)

13) Example: selectmakedate (2001); -- '2017-01-31'

14) selectmakedate (2001); -- '2017-02-01'

15) This week (Start Time)

16) selectdate_sub (CURRENT_DATE (), interval dayofweek (curdate ()-2 day) as Fdate

17) This week's time (end)

18) selectdate_add (CURRENT_DATE (), interval dayofweek (curdate () + 3 day) as Fdate

19) Last week (Start Time)

20) selectdate_sub (CURRENT_DATE (), interval dayofweek (curdate () + 5 days) as Fdate

21) Last week (ended)

22) selectdate_sub (CURRENT_DATE (), interval dayofweek (curdate ()-1 day) as Fdate

23) this month (Start Time)

24) selectDATE_SUB (CURDATE (), interval day (CURDATE ()-1 DAY) as Fdate

25) end of this month)

26) Select date_add (current_date (), intervalday (last_day (CURDATE ()-day (CURDATE () day) as Fdate

27) last month (Start Time)

28) selectDATE_SUB (DATE_SUB (CURDATE (), interval day (CURDATE () DAY), intervalday (last_day (DATE_SUB (CURDATE (), interval day (CURDATE ()) DAY)-1 day) as Fdate

29) last month (ended)

30) selectDATE_SUB (CURDATE (), interval day (CURDATE () DAY) as Fdate

31) this year (Start Time)

32) selectmakedate (year (curdate (), 1) as FDate

33) End of this year)

34) selectDATE_SUB (makedate (year (curdate () + 1, 1), INTERVAL 1 day) as Fdate

35) last year (Start Time)

36) selectmakedate (year (curdate ()-1, 1) as Fdate

37) last year (end)

38) selectDATE_SUB (makedate (year (curdate (), 1), INTERVAL 1 day) as FDate

39) DATE_FORMAT (date, format): format the date value based on the format string. The following modifiers can be used in a format string.

40) % M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM

41) % w days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

42) for example, all other characters are copied to the result without explanation.
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');

43)-> '22: 23: 00'

44) mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y % a % d % m % B % J ');

45)-> '4th 97 Sat 04 10 Oct 277'

46) mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I % r % T % S % W ');

47)-> '22 22 10 10:23:00 22:23:00 6'

IfEXISTS (SELECT * FROM T_Chance WHERE FCustID = CostomerID) return 0

Rewrite:

(Define an out variable in the parameter: out temp varchar (100 );)

BEGIN

Loop1: loop

SELECT count (*) FROM T_Chance WHEREFCustID = CostomerID int @ cnt

If @ cnt> 0 then

Begin

Set temp = 0;

Leaveloop1;

End;

End if

End loop loop1;

 

 

Note: The limit in MySQL cannot be placed in the subquery. The limit is different from that in SQL Server. It can specify the limit a, B -- range a-B.

SQLSERVER: select top 8 * from table1

MYSQL: select * from table1 limit 5;

Createprocedure up_test

()

Begin

Droptable if exists tb1;

CreateTEMPORARY table tb1 // Add TEMPORARYtable

(

Id int,

Name varchar (20)

); // Pay attention to the last plus points

Inserttb1 values ('1', 'jim ');

Select * from tb1;

End

Createtable user

(

Id varchar (10) primary key auto_increment not null,

Name varchar (20) not null,

Password varchar (20 ),

Create_date datetime

);

Auto_increment auto-Increment