master databse and IF errors in mySQL?-CodePudding

I'm brand-new to mySQL and sql in general. I have install mysql workbench and mysql community server to start practicing as I saw in many tutorials. I have 2 problems possibly connected to each other. In many scripts I see the usage of

USE master;

in my version of workbench ( version 8.0) when I try to run this query is says "unknown database 'master'. I'm not pretty sure what master database has ( I googled it and I only understood that is a good practice to make your own database inside master database).

My other question which is more confusing for me is when I use IF.

IF NOT EXISTS(SELECT name FROM master.dbo.sysdatabases
    WHERE name = 'mydatabase')
    CREATE DATABASE [mydatabase]

It keeps giving me the error: "IF is not valid at this position, expecting EOF,ALTER,ANALYZE,BEGIN , ..."

When I use BEGIN before the IF is says :IF is not valid at this position, expecting EOF, ';'

At first I assumed it has something to do with the master database so I tried something different:

IF OBJECT_ID('tablename','U') IS NULL
 CREATE TABLE tablename(
 ....);

I get the same errors. The only thing I found on stackoverflow is this: statement-is-not-valid-in-this-position Where someone says " You cannot use IF in an SQL statement. You can only use it in a stored procedure or a function " if that's the case how does the scripts I find online use the IF statement this way? This is an example of an sql code that is supposed to work:

USE [master]
GO

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases 
WHERE name = 'HotelManagement')
CREATE DATABASE [HotelManagement] COLLATE Greek_CI_AI -- Collatiom : CI = Case Insensitive, AI = Accent Insensitive
GO

/* =====================
   CREATE ENTITIES 
======================*/
USE [HotelManagement]
GO

IF OBJECT_ID ('Customers', 'U') IS NULL
BEGIN
CREATE TABLE Customers
(
   Customer_ID int IDENTITY(1,1) PRIMARY KEY, 
   First_name varchar (50) not null, 
   Last_name varchar (50) not null, 
   Birth_date datetime,
   Issuing_authority varchar (50) not null, 
   Email varchar (50),
   Phone varchar (50)
)
END

CodePudding user response：

It's quite confusing, but every brand has their own implementation of SQL.

SQL is based on an ANSI standard, but no vendor implements the whole standard. Also, the SQL standard allows vendors to create their own proprietary extensions to the SQL language. Every vendor does this at least a little bit.

The bottom line is that you shouldn't assume SQL code for one brand of database works at all on a different brand. There are similarities because they're all based on SQL, but you really need to study the documentation for each brand separately.

You even need to study the documentation for the specific version of the database you're using, because each vendor introduces new features (or deprecates some old features too) in each release.

In the case of MySQL:

MySQL does not support compound statements (e.g. IF/THEN/END IF outside of stored routines. See https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html
MySQL does not support square brackets ([ ]) as identifier delimiters. See https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
MySQL's client uses semicolon (;) not GO as the statement terminator. See https://dev.mysql.com/doc/refman/8.0/en/entering-queries.html
MySQL has a column option called AUTO_INCREMENT, not the IDENTITY(1,1) option used by Microsoft. See https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

There are many other differences. Just wait until you get to writing stored procedures!

CodePudding user response：

MySQL, Microsoft SQL Server (MSSQL), Postgres, etc. are all different Relational Database Management Systems. They all use Structured Query Language (SQL) to communicate with the specific server. Each has its own different flavours, cherries and icing - But they almost operate the same functionality and most used syntax is 99% same.

You need a client software/web app to design/write/edit/delete/read/query data on a database engine. These are called client software. Some can run on your local machine. Others might run from a web page or your own web server.

Learn Relational Database Design

Learn Structured Query Language

Learn SQL Clients