Recently, I'm self-taught jsp, You have to deal with databases , I believe you are right SQLserver and MySQL It's not strange .
In the video, the teacher used sqlserver database , But I used it mysql database , But I really suffered a lot .
Go straight to the code

62 to 64 Line code to achieve is the query is the content of the page , according to sqlserver There is no problem with the query syntax of , Otherwise, the code will directly show red .
But it's starting tomcat But it broke down



Both the console and the web interface have been saying that there is something wrong with my database query statements (62 to 64 That's ok ), I was straight root
cause Baidu search, but inside the statement is really hype , Some say it is because there is no corresponding lead jar The problem with the package , Some say that the field name of the database conflicts with the reserved word , But I can try them all , But it doesn't work .
be careful , At the beginning, I said that the database I used was different from the database used by the teacher ,
I thought about it later , Decisively search the grammatical differences between the two , In the end, it was . The grammatical rules of the two are quite different .
Here is a summary
1. MySQL support enum, and set type ,SQL Server I won't support it

*
MySQL I won't support it nchar,nvarchar,ntext type

*
MySQL The increment statement of is AUTO_INCREMENT, and MS SQL yes identity(1,1)

*
MS SQL By default, the default value of the table creation statement is yes ((0)), And in the MySQL Two brackets are not allowed inside

*
MySQL The storage type needs to be specified for the table

*
MS SQL The identifier is [],[type] It is different from a keyword , however MySQL nevertheless `, That's the key 1 The symbol on the left

*
MS SQL support getdate() Method to get the current time and date , however MySQL It can be divided into date type and time type , Get current date is current_date
(), The current time is the full time now() function

*
MS SQL I won't support it replace into sentence , But in the latest sql20008 inside , Also supported merge grammar

*
MySQL support insert into table1 set t1 = „‟, t2 = „‟ , however MS SQL This is not supported

*
MySQL support insert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1,1), (1,1)

*
MySQL When you create a table, you specify a storage engine type for each table , and MS SQL Only one storage engine is supported

*
MySQL The default value of the current time is not supported datetime type (MS SQL It's easy to do ), stay MySQL inside Yes, it is timestamp type

*
MS SQL Check whether there is a table to delete , It needs to be like this :if exists (select * from dbo.sysobjects where id =
object_id(N’uc_newpm’) and OBJECTPROPERTY(id,N’IsUserTable’)=1) But in the MySQL It just needs to be inside
DROP TABLE IF EXISTS cdb_forums;

*
MySQL Supports unsigned integers , So, it doesn't support unsigned MS SQL You can double the maximum number storage

*
MySQL Not supported in MS SQL It's very convenient to use inside varchar(max) type , This type of MS SQL in It can be used for general data storage , It can also be done blob data storage

*
MySQL To create a nonclustered index, you only need to specify key Just fine , such as :KEY displayorder (fid,displayorder) stay MS
SQL It has to be inside :create unique nonclustered index
index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers (username
asc,appid asc)

*
MySQL text Default value is not allowed for field type

*
MySQL The total field length of one table of is no more than 65XXX.

*
One obvious difference is that MySQL The installation is very simple , And the file size is only 110M( Non installation version ), mutually It's bigger than Microsoft , In terms of installation schedule, it is …..

*
MySQL Stored procedures for only appear in the latest version , Stability and performance may not be as good MS SQL.

*
Same load pressure ,MySQL It costs less CPU And memory ,MS SQL It's very resource intensive .

*
mysql Of ifnull() Function correspondence sql Of isnull() function ;

*
mysql The definition of variables is removed from the stored procedure of @;

*
mysql At the end of each sentence ”;”

*
SQLServer Stored procedure AS stay MySql Need to use begin …..end replace

*
For string concatenation concat() function ; as SQLServer: Temp=‟select * from ‟+‟tablename‟+…+…
MySql:Temp=concat(‟select * from‟, ‟tablecname‟,…,…)

*
mysql Of uuid() corresponding mssql Of GUID();

*
MySql Of out corresponding SQLServer Of output, And mysql Of out Put it before the variable ,SQLServer Of output Put after variable

*
MySql out,in,inout The difference between ——MySQL stored procedure “in” parameter : Follow C Function parameters of language The value of a number is passed similar , MySQL
This parameter may be modified inside the stored procedure , But yes in Modification of type parameters , For callers (caller) It's invisible (not visible).MySQL stored procedure “out”
parameter : Pass values from within the stored procedure to the caller . Inside the stored procedure , The initial value of this parameter is null, Whether the caller sets values for stored procedure parameters or not .MySQL stored procedure inout Parameter following
out similar , Can be passed to the caller from within the stored procedure . The difference is that : Callers can also use the inout Parameter to pass the value to the stored procedure .

*
MySQL Of if The statement is if ( condition ) then end if; perhaps If ( condition ) then Else End if perhaps If( condition )then
Elseif ( Be careful not to write Else if ) Elseif … End if

*
Mysql Of Execute corresponding SqlServer Of exec; ( be careful : You have to call it like this ) Set @cnt=‟select * from Table name ‟;
Prepare str from @cnt; Execute str;

*
MySql Stored procedures call other stored procedures call Call Function name ( Namely SQLServer Stored procedure name for )(‟ parameter 1‟,‟ parameter 2‟,……)

*
mysql Date of

1) Get current date function :curdate(),current_date()

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

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

4) MySQL dayof… function :dayofweek(), dayofmonth(), dayofyear() Return date separately
number , In a week , January , Position in the year .

5) ( notes : Sunday =1, Monday =2, Tuesday =3,……)

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

7) MySQL Add an interval to the date :date_add()

8) select date_add(CURRENT_DATE(),interval „ Days to increase ‟ day) as Fdate

9) MySQL Subtract an interval from the date :date_sub()

10) select date_sub(‘1998-01-01 00:00:00’, interval ‘1 1:1:1’ day_second);

11) MySQL date , Time subtraction function :datediff(date1,date2), timediff(time1,time2)

12) MySQL Patchwork date , Time function :makdedate(year,dayofyear), maketime(hour,minute,second)

13) example :select makedate(2001,31); – ‘2001-01-31’

14) select makedate(2001,32); – ‘2001-02-01’

15) Time of the week ( start )

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

17) Time of the week ( end )

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

19) Last week time ( start )

20) select date_sub(CURRENT_DATE(),interval dayofweek(curdate())+5 day) as
Fdate

21) Last week time ( end )

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

23) Time of the month ( start )

24) select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) as Fdate

25) Time of the month ( end )

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

27) Last month time ( start )

28) select DATE_SUB(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY),interval
day(last_day(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY)))- 1 day) as Fdate

29) Last month time ( end )

30) select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY) as Fdate

31) Time of the year ( start )

32) select makedate(year(curdate()),1) as FDate

33) Time of the year ( end )

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

35) Last year ( start )

36) select makedate(year(curdate())-1,1) as Fdate

37) Last year ( end )

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

39) DATE_FORMAT(date,format): according to format String formatting date value . The following modifiers Can be used in format In string

40) %M Month name (January……December) %W Name of the week (Sunday……Saturday) %D Date of month with English prefix (1st,
2nd, 3rd, wait .) %Y year , number , 4 position %y year , number , 2 position %a Abbreviated name of the week (Sun……Sat) %d Days in month ,
number (00……31) %e Days in month , number (0……31) %m month , number (01……12) %c month , number (1……12) %b
Abbreviated month name (Jan……Dec) %j Days of 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 hour (hh:mm:ss [AP]M) %T
time ,24 hour (hh:mm:ss) %S second (00……59) %s second (00……59) %p AM or PM

41) %w Days of the week (0=Sunday ……6=Saturday ) %U week (0……52), Here Sunday is the first day of the week %u
week (0……52), Here Monday is the first day of the week %% A word “%”.

42) example : All other characters are copied to the result without explanation . mysql> select DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%W
%M %Y’); ->’Saturday October 1997’ mysql>select DATE_FORMAT(‘1997-10-04
22:23:00’, ‘%H:%i:%s’);

43) -> ‘22:23:00’

44) mysql>select DATE_FORMAT(‘1997-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(‘1997-10-04 22:23:00’, ‘%H %k %I %r %T %S %w’);

47) ->’22 22 10 10:23:00 PM 22:23:00 00 6’

*
MySql No in stored procedure return function , stay MySql You can use loop and out Parameter substitution If EXISTS(SELECT * FROM T_Chance
WHERE FCustID=CostomerID) return 0 Rewrite as : ( Define a out variable :out temp varchar(100);)
BEGIN Loop1:loop SELECT count(*) FROM T_Chance WHERE FCustID=CostomerID int
@cnt If @cnt>0 then begin set temp=0; leave loop1; end; end if end loop loop1;

*
(13) select @a=count() from VW_Action stay mySql To be amended as :select count() from VW_Action
into @a;

*
(14)MySQL None of them top keyword , Need to use limit Instead and at the back
be careful , stay MySQL Medium limit Cannot be placed inside a subquery ,limit Different from SQLServer, It can To define the scope limit a,b—— Range a-b SQL SERVER
: select top 8 * from table1 MYSQL: select * from table1 limit 5;

*
(15) Write parentheses even if the stored procedure has no parameters “()”

*
(16) When a temporary table is created in a stored procedure create procedure up_test () begin drop table if exists
tb1; create TEMPORARY table tb1// Pay attention to add TEMPORARY table ( id int, name varchar(20)
);// Pay attention to the last semicolon insert tb1 values(‘1’,’jim’); select * from tb1; end

*
(17) Self growth in table building : create table user ( Id varchar(10) primary key auto_increment
not null, Name varchar(20) not null, Password varchar(20), create_date datetime
); auto_increment Self growth

*
(18) “Unable to convert MySQL date/time value to System.DateTime” This is because on the date
There are ”0000-00-00” Data value , We need to fix this problem , You can set the data to null, Or in the connection string ”Allow Zero Datetime=True”
. 41. (19) MySQL View's FROM Subquery is not allowed in clause , So for SQL Server in FROM
Clause a view with subqueries , Migration needs to be done manually . Can be eliminated by FROM Subquery in Clause , Or will FROM The subquery in clause is reconstructed into a new view for migration .

in summary . The first 62 reach 64 Line code should be changed to
rs = sm.executeQuery( "select * from myusers where userId limit " + ((pageNow
- 1) * pageSize) + "," + pageSize);
be careful sqlserve Of top and mysql Of limit .
That's all for you , Hope to avoid detours , Also give yourself a warning , The most important thing in learning is “ care ”!!