SQL Server 2019 : T-SQL Basic Usage2021/05/30 |
This is some basic example of usage for Transact-SQL (T-SQL).
|
|
[1] | Create/Delete Databases. |
[root@dlp ~]# sqlcmd -S localhost -U SA Password: # create [SampleDB] database 1> create database SampleDB; 2> go # create a database with parameters 1> create database SampleDB2 2> on primary ( name = 'SampleDB2', 3> filename = '/var/opt/mssql/data/SampleDB2.mdf', 4> size = 5GB, maxsize = unlimited, filegrowth = 10MB ) 5> log on ( name = 'SampleDB2_log', 6> filename = '/var/opt/mssql/data/SampleDB2_log.ldf', 7> size = 1GB, maxsize = 2GB, filegrowth = 5% ) 8> 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 2021-05-28 00:33:44.453 model 2003-04-08 09:13:36.390 msdb 2021-03-22 18:49:30.403 SampleDB 2021-05-28 01:36:41.637 SampleDB2 2021-05-28 01:57:46.577 (6 rows affected) # delete [SampleDB2] database 1> drop database SampleDB2; 2> go |
[2] | Create/Delete Tables. |
# connect to SQL Server with a database [root@dlp ~]# sqlcmd -S localhost -U SA -d SampleDB Password: # 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> 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 |
[3] | Insert/Update/Delete Data. |
[root@dlp ~]# sqlcmd -S localhost -U SA -d SampleDB Password: # insert data 1> insert into dbo.Sample_Table ( Number, First_Name, Last_Name, Last_Update ) 2> values ( '00001', 'CentOS', 'Linux', '2021-05-30' ), 3> ( '00002', 'RedHat', 'Linux', '2021-05-30' ), 4> ( '00003', 'Ubuntu', 'Linux', '2021-05-30' ) 5> go (3 rows affected) 1> select * from dbo.Sample_Table; 2> go Number First_Name Last_Name Last_Update ---------- -------------- ------------ ---------------- 00001 CentOS Linux 2021-05-30 00002 RedHat Linux 2021-05-30 00003 Ubuntu Linux 2021-05-30 (3 rows affected) # show tables with specifying columns 1> select Number, First_Name from dbo.Sample_Table; 2> go Number First_Name ---------- -------------------------------------------------- 00001 CentOS 00002 RedHat 00003 Ubuntu (3 rows affected) # show top 2 data 1> select top 2 * from dbo.Sample_Table; 2> go Number First_Name Last_Name Last_Update ---------- -------------- ------------ ---------------- 00001 CentOS Linux 2021-05-30 00002 RedHat Linux 2021-05-30 (2 rows affected) # update data 1> update dbo.Sample_Table set Last_Update = '2021-05-31' where First_Name = 'Ubuntu' 2> go (1 rows affected) 1> select * from dbo.Sample_Table where First_Name ='Ubuntu'; 2> go Number First_Name Last_Name Last_Update ---------- ---------------- -------------- ---------------- 00003 Ubuntu Linux 2021-05-31 (1 rows affected) # delete data 1> delete dbo.Sample_Table where First_Name ='Ubuntu'; 2> go (1 rows affected) 1> select * from dbo.Sample_Table where First_Name ='Ubuntu'; 2> go Number First_Name Last_Name Last_Update ---------- ------------- ------------- ---------------- (0 rows affected) |
[4] | It's also possible to run T-SQL directly like follows. |
[root@dlp ~]# sqlcmd -S localhost -U SA -Q 'select name,create_date from sys.databases' Password: name create_date ------------------ ----------------------- master 2003-04-08 09:13:36.390 tempdb 2021-05-28 00:33:44.453 model 2003-04-08 09:13:36.390 msdb 2021-03-22 18:49:30.403 SampleDB 2021-05-28 01:36:41.637 SampleDB2 2021-05-28 01:57:46.577 (6 rows affected) |
Sponsored Link |