SQL Server 2019 : T-SQL Basic Operation2020/01/20

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

# create a database with parameters
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

# list databases
1> select name,create_date from sys.databases;
2> go
name                        create_date
--------------------------- -----------------------
master                      2003-04-08 09:13:36.390
tempdb                      2020-01-17 16:01:07.007
model                       2003-04-08 09:13:36.390
msdb                        2019-12-06 16:24:38.153
SampleDB                    2020-01-16 21:25:32.233
SampleDB2                   2020-01-16 21:26:47.270

(6 rows affected)

# delete [SampleDB2] database
1> drop database SampleDB2; 
2> go 

# create [Sample_Table] 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 

# list tables
1> select name from sysobjects 
2> where xtype='u' 
3> go 
name                     
-------------------------
Sample_Table             

(1 rows affected)

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

# insert data
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              2020-01-16

(1 rows affected)

# show tables with specifying columns
1> select Number, First_Name from dbo.Sample_Table; 
2> go 
Number     First_Name
---------- --------------
00001      CentOS

(1 rows affected)

# show top 3 data
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)

# update data
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)

# delete data
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-17 16:01:07.007
model                 2003-04-08 09:13:36.390
msdb                  2019-12-06 16:24:38.153
SampleDB              2020-01-16 21:25:32.233
SampleDB2             2020-01-16 21:26:47.270

(6 rows affected)