SQL Server 2019 : T-SQL 基本操作2020/01/16

# [SampleDB] 作成
1> create database SampleDB; 
2> go 

# 各パラメータを指定して作成
1> create database SampleDB2 
2> on primary (
3>  name = 'SampleDB2',
4>  filename = '/var/opt/mssql/data/SampleDB2.mdf',
5>  size = 5GB,
6>  maxsize = unlimited,
7>  filegrowth = 10MB
8> )
9> log on (
10>  name = 'SampleDB2_log',
11>  filename = '/var/opt/mssql/data/SampleDB2_log.ldf',
12>  size = 1GB,
13>  maxsize = 2GB,
14>  filegrowth = 5%
15> )
16> go

# データベース一覧表示
1> select name,create_date from sys.databases;
2> go
name                       create_date
-------------------------- -----------------------
master                     2003-04-08 09:13:36.390
tempdb                     2020-01-15 18:25:30.063
model                      2003-04-08 09:13:36.390
msdb                       2019-12-06 16:24:38.153
SampleDB                   2020-01-15 19:30:57.280
SampleDB2                  2020-01-15 19:32:22.197

(6 rows affected)

# [SampleDB2] データベース削除
1> drop database SampleDB2; 
2> go 

# [Sample_Table] 作成
1> create table dbo.Sample_Table ( 
2> Number nvarchar(10) not null, 
3> First_Name nvarchar(50) not null, 
4> Last_Name nvarchar(50) null, 
5> Last_Update date not null 
6> ) 
7> go 

# テーブル一覧表示
1> select name from sysobjects 
2> where xtype='u' 
3> go 
name                     
-------------------------
Sample_Table             

(1 rows affected)

# [Sample_Table] 削除
1> drop table dbo.Sample_Table; 
2> go 

# データ挿入
1> insert into dbo.Sample_Table ( 
2> Number, First_Name, Last_Name, Last_Update 
3> ) 
4> values ( 
5> '00001', 'CentOS', 'Linux', '2020-01-16' 
6> ) 
7> go 

(1 rows affected)

1> select * from dbo.Sample_Table; 
2> go 
Number     First_Name        Last_Name     Last_Update
---------- ----------------- ------------- ----------------
00001      CentOS            Linux               2020-01-16
00002      RedHat            Linux               2020-01-16
00003      Fedora            Linux               2020-01-16
00004      Ubuntu            Linux               2020-01-16
00005      Debian            Linux               2020-01-16

(5 rows affected)

# 列を指定して表示
1> select Number, First_Name from dbo.Sample_Table; 
2> go 
Number     First_Name
---------- --------------------------------------------------
00001      CentOS
00002      RedHat
00003      Fedora
00004      Ubuntu
00005      Debian

(5 rows affected)

# 上位3件表示
1> select top 3 * from dbo.Sample_Table; 
2> go 
Number     First_Name      Last_Name     Last_Update
---------- --------------- ------------- ----------------
00001      CentOS          Linux               2020-01-16
00002      RedHat          Linux               2020-01-16
00003      Fedora          Linux               2020-01-16

(3 rows affected)

# データ更新
1> update dbo.Sample_Table 
2> set Last_Update = '2020-01-17' 
3> where First_Name = 'Debian' 
4> go 

(1 rows affected)

1> select * from dbo.Sample_Table where First_Name ='Debian'; 
2> go 
Number     First_Name     Last_Name     Last_Update
---------- -------------- ------------- ----------------
00005      Debian         Linux               2020-01-17

(1 rows affected)

# データ削除
1> delete dbo.Sample_Table where First_Name ='Debian'; 
2> go 

(1 rows affected)
1> select * from dbo.Sample_Table where First_Name ='Debian'; 
2> go 
Number     First_Name    Last_Name     Last_Update
---------- ------------- ------------- ----------------

(0 rows affected)

name                   create_date
---------------------- -----------------------
master                 2003-04-08 09:13:36.390
tempdb                 2020-01-15 18:25:30.063
model                  2003-04-08 09:13:36.390
msdb                   2019-12-06 16:24:38.153
SampleDB               2020-01-15 19:30:57.280
SampleDB2              2020-01-15 19:32:22.197

(6 rows affected)