Ubuntu下Mysql和Sql-sqlserver利用可视化工具建库建表

操作环境：Linux-Mint

安装过程官网：

Ubuntu安装SqlServer2017
Install Mysql on Linux

下载对应的可视化工具

Install SQL Operations Studio (preview).
WorkBench-Mysql

使用可视化工具快速建库建表

SqlServer
- 创建数据库
The following steps create a database named TutorialDB:

Right click on your server, localhost, and select New Query.
Paste the following snippet into the query window:

USE master
GO
IF NOT EXISTS (
   SELECT name
   FROM sys.databases
   WHERE name = N'TutorialDB'
)
CREATE DATABASE [TutorialDB]
GO

ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON
GO

To execute the query, click Run
创建一张表：
Change the connection context to TutorialDB:
Paste the following snippet into the query window and click Run:

-- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
-- Create the table in the specified schema
CREATE TABLE dbo.Customers
(
   CustomerId        INT    NOT NULL   PRIMARY KEY, -- primary key column
   Name      [NVARCHAR](50)  NOT NULL,
   Location  [NVARCHAR](50)  NOT NULL,
   Email     [NVARCHAR](50)  NOT NULL
);
GO

插值：
Paste the following snippet into the query window and click Run:

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
   ([CustomerId],[Name],[Location],[Email])
VALUES
   ( 1, N'Orlando', N'Australia', N''),
   ( 2, N'Keith', N'India', N'keith0@adventure-works.com'),
   ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
   ( 4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO

查值：
Paste the following snippet into the query window and click Run:

-- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;

下面是个人的作业，可以不用看。
创建数据库：

USE master
GO
IF NOT EXISTS (
   SELECT name
   FROM sys.databases
   WHERE name = N'SPJ'
)
CREATE DATABASE [SPJ]
GO

ALTER DATABASE [SPJ] SET QUERY_STORE=ON
GO

• 建表

-- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
CREATE TABLE dbo.S
(
   S_number     INT    NOT NULL   PRIMARY KEY, -- primary key column
   S_name       [NVARCHAR](50)  NOT NULL,
   S_city       [NVARCHAR](50)  NOT NULL
);
GO
MYSQL:
CREATE TABLE `S`.`S` (
  `SNumber` INT NOT NULL,
  `SName` VARCHAR(45) NOT NULL,
  `SCity` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`SNumber`));



CREATE TABLE dbo.p
(
   P_number     INT    NOT NULL   PRIMARY KEY,
   P_name       [NVARCHAR](50)  NOT NULL,
   P_color      [NVARCHAR](50)  NOT NULL,
   P_weight     INT NOT NULL
);
GO


CREATE TABLE dbo.Jj
(
   J_number     INT    NOT NULL   PRIMARY KEY,
   J_name       [NVARCHAR](50)  NOT NULL,
   J_city      [NVARCHAR](50)  NOT NULL,
);
GO


CREATE TABLE dbo.SPJ
(
   S_number     INT    NOT NULL,
   P_number     INT    NOT NULL,
   J_number     INT    NOT NULL,
   FOREIGN KEY(S_number) REFERENCES Customers,
   FOREIGN KEY(P_number) REFERENCES P,
   FOREIGN KEY(J_number) REFERENCES Jj,
   SPJ_qty      [NVARCHAR](50) NOT NULL
);
GO

• 删表

DROP TABLE dbo.Jj
GO

• 选择

-- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;

SELECT * FROM dbo.P;

• 插入数据

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
   ([S_number],[S_name],[S_city])
VALUES
   ( 6, N'K', N'Beijing'),
   ( 7, N'I', N'ShangHai'),
   ( 8, N'H', N'Shanghai'),
   ( 9, N'G', N'Beijing'),
   ( 10, N'F', N'BiJie')
GO

INSERT INTO dbo.P
   ([P_number],[P_name],[P_color],[P_weight])
VALUES
   ( 1, N'Aa', N'red',1),
   ( 2, N'Bb', N'pink',2),
   ( 3, N'Cc', N'yellow',3),
   ( 4, N'Dd', N'blue',4),
   ( 5, N'Ee', N'red',5),
   ( 6, N'Aa', N'pink',5),
   ( 7, N'Bb', N'white',4),
   ( 8, N'Cc', N'black',3),
   ( 9, N'Dd', N'red',2),
   ( 10, N'Ee', N'pink',1)
GO


INSERT INTO dbo.Jj
   ([J_number],[J_name],[J_city])
VALUES
   ( 1, N'Aj', N'Beijing'),
   ( 2, N'Bj', N'ShangHai'),
   ( 3, N'Cj', N'Shanghai'),
   ( 4, N'Dj', N'Beijing'),
   ( 5, N'Ej', N'BiJie'),
   ( 6, N'Aj', N'Beijing'),
   ( 7, N'Bj', N'ShangHai'),
   ( 8, N'Cj', N'Shanghai'),
   ( 9, N'Dj', N'Beijing'),
   ( 10, N'Ej', N'BiJie')
GO


INSERT INTO dbo.SPJ
   ([S_number],[P_number],[J_number],[SPJ_qty])
VALUES
   ( 1, 2, 10, N'a'),
   ( 2, 6, 9, N'b'),
   ( 3, 6, 8, N'c'),
   ( 4, 8, 7, N'd'),
   ( 5, 7, 6, N'e'),
   ( 6, 4, 5, N'a'),
   ( 7, 5, 4, N' b'),
   ( 8, 5, 6, N'c'),
   ( 9, 4, 7, N'd'),
   ( 10, 1,8, N'e'),
   ( 10, 2, 10, N'a'),
   ( 9, 6, 9, N'b'),
   ( 8, 6, 8, N'c'),
   ( 7, 8, 7, N'd'),
   ( 6, 7, 6, N'e'),
   ( 5, 9, 5, N'a'),
   ( 4, 5, 4, N' b'),
   ( 3, 5, 3, N'c'),
   ( 2, 10, 2, N'd'),
   ( 1, 1,1, N'e')
GO

• Note

SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每一条记录。
主键必须包含唯一的值。
主键列不能包含NULL值。
每个表都应该有一个主键，并且每一个表只能有一个主键。

MySql
- 建表和插值
``
CREATE TABLES.J
(
JNumberINT NOT NULL PRIMARY KEY,
JNameVARCHAR(45) NOT NULL,
JCity` VARCHAR(45) NOT NULL,
);

INSERT INTO J
(JNumber,JName,JCity)
VALUES
( 1, N’Aj’, N’Beijing’),
( 2, N’Bj’, N’ShangHai’),
( 3, N’Cj’, N’Shanghai’),
( 4, N’Dj’, N’Beijing’),
( 5, N’Ej’, N’BiJie’),
( 6, N’Aj’, N’Beijing’),
( 7, N’Bj’, N’ShangHai’),
( 8, N’Cj’, N’Shanghai’),
( 9, N’Dj’, N’Beijing’),
( 10, N’Ej’, N’BiJie’);

- 同上

CREATE TABLE S.P
(
PNumber INT NOT NULL PRIMARY KEY,
PName VARCHAR(45) NOT NULL,
PColor VARCHAR(45) NOT NULL,
PWeight INT NOT NULL
);

INSERT INTO P (PNumber,PName,PColor,PWeight) VALUES ( 1, N’Aa’, N’red’,1),
( 2, N’Bb’, N’pink’,2),
( 3, N’Cc’, N’yellow’,3),
( 4, N’Dd’, N’blue’,4),
( 5, N’Ee’, N’red’,5),
( 6, N’Aa’, N’pink’,5),
( 7, N’Bb’, N’white’,4),
( 8, N’Cc’, N’black’,3),
( 9, N’Dd’, N’red’,2),
( 10, N’Ee’, N’pink’,1);
“`