1. Summary

One of the hardest things for many beginners learning MySQL before learning SQL Server is that there are subtle differences in their grammar, such as the absence of keywords such as modify and change in SQL Server, or every statement in MySQL. At the end, SQL Server uses the go keyword to indicate the end of a batch statement, and so on...

This is the case where we want to help those who are also learning the two SQL languages understand the syntax differences between the two databases

Note: Due to the blogger's limited ability, it is not possible to summarize all the grammar, and what is missing or wrong should be corrected.
Finally, from the beginning of the new year, I wish you all 2022 a smooth, healthy and happy family, all of whom will be bald.

2. Basic Grammar

1. Note: The syntax of the notes in the two databases is identical. Example:

# Single-Line Comments
-- Single-Line Comments(Notice the bars space)
/* 
multiline comment
*/

1. End of statement:

   Must be used in MySQL; Separate each statement as the end of the statement, and error occurs when multiple statements are executed together if no semicolon is used to separate the statements

   Example:

use Student; -- Do not use when running two statements at the same time;Separator statement will fail
select * from SC;

Semicolons are optional in SQL Server, you can choose to add or not, and the go keyword is provided in SQL Server as the end of the batch statement. It is recommended that you use go to end the batch statement when writing SQL Server, so that the batch error will not be reported if the next statement requires the previous statement to be executed. Example:

use Student
-- hypothesis SC Data table in Student Under Library,If you don't write at this time go Errors will occur
go
select * from SC

1. Storage Engine:

   Storage engines such as InnoDB | MyISAM | MEMORY | MERGE are commonly used in mysql, with InnoDB being the most used

   The storage of databases in SQL Server is divided into logical and physical implementations.

There can be only one master data file, n (0-n) number data files, and one or more log files (at least one)

At the same time, MySQL can specify the storage engine (default InnoDB) for tables when they are created, whereas SQL Server has only one storage engine

1. MySQL can use single and double quotation marks, while SQL Server only supports single quotation marks

2. Neither strict case sensitivity

3. Locate a table

   mysql: Library name. Table name, example: Student.SC

   SQLServer: Library name. dbo. Table name or library name...Table name

   Among them, dbo is the database owner, that is, the user who has access to the database. It is the only one who has full access to the database and can provide access and functions to other users.

   Example:

Student.dbo.SC -- perhaps:
Student..SC

1. Exc keyword in SQL Server:

-- 1. exec Stored procedure name parameter 1, Parameter 2....
-- Be careful:Executing stored procedures without parentheses
-- 2. exec('sql Sentence'),Indicates execution of the statement

1. A series of sp commands are provided in the SQL Server advanced syntax

2. System databases in SQL Server:

   master: Records all system-level information for the system

   model:Template Database

   msdb: Store plan information, backup and recovery related information, SQL Server Agent scheduling alarm and job scheduling information

   tempdb: Temporary database that provides storage for all temporary tables, temporary stored procedures, and all other temporary operations

   resource: Hidden read-only database containing all system objects but no user data or user data

3. System databases in mysql:

   information_schema: Provides access to database metadata. (Metadata is data about data, such as database or table names, column data types, or access rights. Other terms sometimes used to describe this information include "Data Dictionary" and "System Catalog"), which hold information about all other databases maintained by the MySQL server, such as database names, tables in the database, data types and access rights for table columns, etc.

   In INFORMATION_ There are several read-only tables in SCHEMA. They are actually views, not basic tables

   mysql: Core database (similar to the master table of SQLServer) that stores the control and management information that mysql needs to use by itself, such as users, permission settings, keywords, etc. for the database, such as modifying the root user password

   performance_schema;

   sys;

4. MySQL consumes less memory and CPU under the same load pressure

5. The print statement print is also provided in SQLLServer, but not in mysql. Example:

-- print Self-contained line break
print 'hello'

1. MySQL supports enum and set types, not SQL Server

3. DDL&DML statements

3.1 Library Building

mysql:

-- Create it directly
CREATE DATABASE [IF NOT EXISTS] Database Name 
[character set Character Set Name];

SQLServer:

/* In addition to the database name, you need to specify:
	Logical name of master data file (usually the same name as the database), physical file name of master data (.mdf)
	Initial size of master data file (default 5MB), maximum capacity, growth rate
	Logical name of log file (commonly named library_log) and physical file name of log (.ldf)
	Initial log file size (default 1MB), maximum capacity, growth rate
	Whether to add secondary data files (.ndf), whether to add several log files...
	And logical file naming needs to correspond to physical file naming
	Main data file logical default name is database name
*/
-- Example:
CREATE DATABASE Database Name
[ON  [PRIMARY]
(
    NAME = 'test', 
    FILENAME='D:\test.mdf', 
    [SIZE=10240KB/MB/GB/TB, ]
    [MAXSIZE = UNLIMITED/20480KB/MB/GB/TB,]
    [FILEGROWTH = 10%/1024KB/MB/GB/TB]
)]
[LOG ON 
(
    NAME='test_log',
    FILENAME='D:\test_log.ldf',
    [SIZE=1024KB/MB/GB/TB,]
    [MAXSIZE = 5120KB/MB/GB/TB/UNLIMITED,]
    [FILEGROWTH = 1024KB/MB/GB/TB/%]
)]
GO
/* Where:
	ON Represents a data file defined later
	ON PRIMARY Representation Definition Master Data File
	LOG ON Represents a definition log file
	NAME Represents the logical name of the file
	FILENAME Represents the physical name of the file
	SIZE Represents the initial size, at least the size of the template database model (3M for the main data file and 1M for the log file, respectively)
	MAXSIZE Represents the maximum file size, which can be UNLIMITED (unlimited)
	FILEGROWTH Indicates the growth rate of file size by default of 10%, increasing at least 64 kb at a time
	Default units are MB
	Note: The last line in parentheses has no commas, and all other lines need commas
*/

3.2 View Library

3.2.1 Open the specified library (consistent)

Both are syntactically identical, both are use r library names

3.2.2 View all databases

mysql:

-- View all current databases:
show databases;
-- Query the character set of a database(Query database creation statement is implemented):
show create database name;

SQLServer:

-- View all current databases:
select name, database_id, create_date from sys.databases
go
-- SQLServer Database information stored in sys.databases in
-- Represents the query database name,data base id And creation time,Fixed Writing

-- View database information
sp_helpdb Database Name
go

3.3 Modify Library

Note: Regardless of the database, we rarely modify the information in the database.

mysql:

-- Rename the database
RENAME DATABASE Old Database Name TO New Database Name;
-- Modify the character set of the database
ALTER DATABASE Database Name CHARACTER SET Character Set Name;

SQLServer:

-- Rename the database
sp_renamedb oldname, newname
go
-- To be added

3.4 Delete library (consistent)

Grammar:

DROP DATABASE [IF EXISTS] Database Name;

3.5 Table Building

3.5.1 Maximum capacity

SQLServer can have up to 1024 columns per table and 8060 bytes per row

The total field length of a MySQL table cannot exceed 65535

3.5.2 Tabulation Grammar (Basic Consistency)

Why is it basically consistent, because in SQL Server table building, you can preface the table name with db_name.dbo specifies the database and owner to which I belong, but I do not see a similar syntax in mysql at this time

Grammar:

CRATE TABLE [IF NOT EXISTS] Table Name(
	Column Name Column Type[(length) constraint],
	Column Name Column Type[(length)constraint],
	Column Name Column Type[(length)constraint],
	...
	Column Name Column Type[(length)constraint]
);
-- Note:
-- Constraints are optional and do not have to be filled in
-- You don't need to add a comma after the last column; you need to add a comma for every other column
-- SQLServer Can't pass through this IF NOT EXISTS Formal judgment of existence
-- SQLServer All judgments in can only be passed IF EXISTS(Query Statement)Method implementation
-- Check if there are examples of tables:
IF EXISTS(select count(*) 
          from dbo.sysobjects
         where name = 'table_name')
go
-- Check if there is an example of a field:
IF EXISTS(select count(*) 
          from dbo.syscolumns
         where id = object_id('table_name')
          and name = 'column_name')
go
-- perhaps:
if DB_ID('name') is not null -- Non-existent
create TABLE....

3.6 View Table

mysql:

-- Query all tables in the database(SQLServer No,):
show tables [from Database Name;
-- View table structure(SQLServer No,)
desc Table Name; # View the data structure under the specified table
-- Use database()Function to see which database you are currently in(SQLServer No,)
select database();

SQLServer:

-- Query all tables in the current database,Fixed Writing
select * from sysobjects where xtype = 'U'
-- View table structure
sp_help Table Name; -- perhaps:
sp_columns Table Name;
-- You can also add exec

Sp_ Example help query results:

Sp_ Example columns query results:

3.7 Modify Table

3.7.1 Modify table name

mysql:

ALTER TABLE name rename [to] newName;

SQLServer:

exec sys.sp_rename 

3.7.2 Modify statement

There are no change and modify statements in SQL Server, so SQL Server uses two alter s

3.8 Delete Table

Basic Consistency

3.9 Separate and attach databases:

SQLServer:

-- Separate Database
sp_detach_db Database Name
go
-- Attach database
exec sp_attach_db [@dbname = ]'Database Name',
[@filename1 = ]'File physical name containing path'[...16]
go
-- A maximum of 16 database files can be specified

4. Constraints/Indexes

1. Incremental statement MySQL is AUTO_ INCREMENT, SQL Server is identified (10.1), plus 1 once from 10
2. mysql does not support checking indexes, and SQL Server does

5. Data type

1. MySQL does not have nchar,nvarchar,ntext, etc.

2. SQL Server uses the datetime type as the data type to get the default value for the current time

   MySQL does this using the timestamp time error type

3. MySQL supports unsigned integer types, while SQL Server does not

6. DQL statement

6.1

Query the first few records:

SQL Server provides top keywords

MySQL uses the limit keyword

Example:

select * from Student limit 100;
select top 100 * from Student;

6.2 Full External Connection

mysql does not support writing full outer join or full join directly to represent a full outer join, but it can be replaced by union join query

SQL Server supports full external connections

The rest of the query syntax is basically the same

7. Common Functions

7.1 Calling function methods

Both MySQL and SQLServer call functions use select to call functions. Example:

SELECT Function name(parameter list);

7.2 Get the current time

MySQL can use current_ The date() function gets the current date, or uses CURRENT_ The TIME() function only gets the current time, or uses CURRENT_ The TIMESTAMP() function and the now() function get the current full time, for example:

SELECT CURRENT_DATE(); -- 2021-12-27
SELECT CURRENT_TIME(); -- 01:42:23
SELECT CURRENT_TIMESTAMP(); -- 2021-12-27 01:42:23
SELECT NOW(); -- 2021-12-27 01:42:23

SQL Server can use the getdate() method to get the current time and date, for example:

SELECT getdate();
-- Return value:2021-12-27 01:40:40.907

7.3 Null function

mysql:

-- 1. ifnull(exp1,exp2);
-- Indicate when exp1 Null-time value exp2,Not null time value exp1

-- 2. isnull(exp1);
-- When exp1 Return 1 when empty,Return 0 if not null

-- 3. At the same time MySQL Also available in if function(and if Structural statements are different),Example:
if (exp1,exp2,exp3)
-- Indicates when a conditional expression exp1 Return on Establishment exp2.Otherwise return exp3
-- Be similar to java Trinomial expression in,SQLServer There is no such function in

SQLServer:

-- 1. isnull(exp1,exp2);
-- Indicate when exp1 Null-time value exp2,Not null time value exp1
-- No, ifnull()function
-- relatively speaking mysql Of ifnull and isnull A function is easy to understand

7.4 String Connection Function

mysql:

-- Use concat()function,Example:
SELECT CONCAT('I','stay','Study mysql');
-- Out of commission+Connection String!

SQLServer:

-- 1. Use plus sign+Connection String
select 'hello'+'SQL'
-- 2. Use concat()function,Example:
SELECT CONCAT('I','stay','Study mysql');

8. Process Control Structure

8.1 IF structure

mysql needs to add the then rewrite statement after the if condition and after the else

And the IF structure in mysql can only be written in the begin end block

Grammar:

-- grammar
IF Condition 1 THEN Statement 1;
ELSEIF Condition 2 THEN Statement 2;
...
ELSE Sentence n;
END IF; -- Express IF End of structure
-- Note: Can only be used for BEGIN END In block
-- You can omit a statement if there is only one begin end

You don't need to write then in SQL Server

Grammar:

IF (Condition 1)
BEGIN
	Statement 1
END
else
BEGIN
	Statement 2
END
go
-- Example:
IF (EXISTS (select Sno from Student where Sno = '200001'))
	select Sno from Student where Sno = '200001'
ELSE
	print 'Not changing students'
go

8.2 case structure (consistent)

Need to use then

Instead of writing Begin, you just need to write END in two forms:

1. The case can be followed by a value that can be judged in when to achieve a selection effect (switch-case form)
2. You can also write conditional judgements (in the form of multiple IF s) in when statements without values.

Grammar:

-- 1:
case The field or expression to be judged
    when Constant 1 then Value 1 or statement 1 to display
    when Constant 2 then Value 2 or statement 2 to display
    ...
    else Value to display n Or statement n
    end
-- 2:
case 
    when Condition 1 then Value 1 or statement 1 to display
    when Condition 2 then Value 2 or statement 2 to display
    ...
    else Value to display n Or statement n
end

8.3 Cycle Structure

Basic Consistency

However, you need to add the do keyword after the while loop in MySQL

You also need to write the loop type and the loop representation after the end, for example: WHILE [Label];

SQL Server is not used

9. Views

The from clause in the mysql view does not allow subqueries, and SQL Server supports them

10. Variables, Function Stored Procedures and Triggers

10.1 Custom Variables

mysql does not require @ to define local variables, but @ to define user variables

SQLServer needs to add a custom variable @

10.2 Stored Procedures

1. In, out, in out, out in the mysql stored procedure precede the variable (the initial value of the parameter is null)

   SQLServerOUTPUT behind variables

2. mysql stored procedure does not return,

3. Differences in the use of select assignments for variables (to be supplemented)

4. mysql is written () even if the stored procedure has no parameters

5. MySQL does not need to write AS, but both require AS keywords in the view

6. Call stored procedures, SQL Server uses exec,execute,mysql uses call

11. DCL statement

12. TCL statement

13. Backup and Restore of Database

Backup Restore in 13.1 SQL Server

There are three types of database backups in SQLLServer:

1. Full backup: backup of the database as a whole
2. Differential backup: A backup of parts that have changed since the previous full backup
3. Transaction Log Backup
4. File or File Group Backup

• Note: Differential and transaction log backups can only be performed after a full backup has been made
• Summarize as follows:

For system databases, a full backup is required

For user databases, a full backup is made before a differential backup is made

For backup of transaction log files (differential backup?), Log file backups take less time and space than database backups, but take longer to recover

Be careful:

Backup devices are required to store database, transaction log, or file and filegroup backups

There are three types of backup devices: disk, tape, logical backup device, backup device is stored as a file on disk

Where the identification of the backup device uses the physical device name and the logical device name

The physical name is used by the operating system to reference and manage backup devices

Logical names are used to simplify the names of physical devices, are permanently stored in system tables, and can be used multiple times

Backup time:

1. After modifying the system database
2. After creating a database or index
3. After performing a bulk data operation
4. Transaction log eliminated

Processing plan after an accident occurs (follow the steps below):

1. If the transaction log file is not damaged, backup the transaction log file (trailing log file) first
2. Restore the most recent full backup file
3. Restore the most recent differential backup file (if a differential backup is made)
4. Sequentially restore all transaction log file backups since the differential backup in the order in which they were backed up

Grammar:

-- Create backup device:
sp_addumpdevice 'device_type','logical_name','physical_name'
go
/*
	Parameter description:
	device_type:Device type, which can be'DISK|TAPE', where DISK represents disk and TAPE represents tape
	logical_name:Logical Name
	physical_name:Physical Name
*/

-- Delete Backup Device
EXEC master.dbo.sp_dropdevice  [@logicalname =] Backup device name 
go

-- Make a full backup of the database:
BACKUP DATABASE db_name
TO device_name [with INIT|NOINIT]
/*
	Parameter description:
	INIT:The newly backed up data covers every item of the current backup device
	NOINIT:The newly backed up data is appended to what is already on the backup device
*/

--