SQL Server 2022 : T-SQL Basic Usage2023/11/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 2023-11-29 08:56:33.530 model 2003-04-08 09:13:36.390 msdb 2023-10-30 16:52:55.553 SampleDB 2023-11-29 09:21:26.583 SampleDB2 2023-11-29 09:21:52.470 (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 where xtype='u' 2> 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', 'RedHat', 'Linux', '2023-11-29' ), 3> ( '00002', 'Debian', 'Linux', '2023-11-29' ), 4> ( '00003', 'Ubuntu', 'Linux', '2023-11-29' ) 5> go (3 rows affected) 1> select * from dbo.Sample_Table; 2> go Number First_Name Last_Name Last_Update ---------- ---------------- ---------------- ---------------- 00001 RedHat Linux 2023-11-29 00002 Debian Linux 2023-11-29 00003 Ubuntu Linux 2023-11-29 (3 rows affected) # show tables with specific columns 1> select Number, First_Name from dbo.Sample_Table; 2> go Number First_Name ---------- -------------------------- 00001 RedHat 00002 Debian 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 RedHat Linux 2023-11-29 00002 Debian Linux 2023-11-29 (2 rows affected) # update data 1> update dbo.Sample_Table set Last_Update = '2023-11-30' where First_Name = 'RedHat' 2> go (1 rows affected) 1> select * from dbo.Sample_Table where First_Name ='RedHat'; 2> go Number First_Name Last_Name Last_Update ---------- ------------------ ------------------- ---------------- 00001 RedHat Linux 2023-11-30 (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 on a shell 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 2023-11-29 08:56:33.530 model 2003-04-08 09:13:36.390 msdb 2023-10-30 16:52:55.553 SampleDB 2023-11-29 09:21:26.583 SampleDB2 2023-11-29 09:21:52.470 (6 rows affected) |
Sponsored Link |