SQL Server 2017 : 基本操作2017/10/05

# [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                     2017-10-05 13:24:39.140
model                      2003-04-08 09:13:36.390
msdb                       2017-08-22 19:39:22.887
SampleDB                   2017-10-05 19:22:42.643
SampleDB2                  2017-10-05 19:30:49.513

(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', '2017-10-05' 
6> ) 
7> go 

(1 rows affected)

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

(1 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               2017-10-05
00002      RedHat        Linux               2017-10-05
00003      Fedora        Linux               2017-10-05

(3 rows affected)

# データ更新
1> update dbo.Sample_Table 
2> set Last_Update = '2017-10-06' 
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               2017-10-06

(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                  2017-10-06 13:53:18.030
model                   2003-04-08 09:13:36.390
msdb                    2017-08-22 19:39:22.887
SampleDB                2017-10-05 19:22:42.643

(5 rows affected)