[3] SQL Server – T-SQL
31 stycznia 2022Przedstawię teraz podstawowe użycie Transact-SQL (T-SQL).
[1] Stwórz/skasuj bazę danych.
[root@vlsr01 ~]# sqlcmd -S localhost -U SA Password: #wpisz haslo #stwórz bazę [SampleDB] 1> create database SampleDB; 2> go #stwórz bazę [SampleDB2] z ustawionymi parametrami 1> create database SampleDB2 2> on primary ( name = 'SampleDB2', 3> filename = '/var/opt/mssql/data/SampleDB2.mdf', 4> size = 1GB, 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 #pokaż bazy 1> select name,create_date from sys.databases; 2> go name create_date -------------------------------------------------------------------------------------------------------------------------------- ----------------------- master 2003-04-08 09:13:36.390 tempdb 2022-01-29 18:11:28.670 model 2003-04-08 09:13:36.390 msdb 2022-01-12 22:54:24.927 SampleDB 2022-01-29 18:13:58.667 SampleDB2 2022-01-29 18:15:28.923 (6 rows affected) #skasuj bazę [SampleDB2] 1> drop database SampleDB2; 2> go
[2] Stwórz skasuj tabelę.
#podłącz się do SQL Server z bazą danych [root@vlsr01 ~]# sqlcmd -S localhost -U SA -d SampleDB Password: #wpisz hasło #stwórz tabelę [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> go #pokaż tabele 1> select name from sysobjects 2> where xtype='u' 3> go name -------------------------------------------------------------------------------------------------------------------------------- Sample_Table (1 rows affected) #skasuj tabelę [Sample_Table] 1> drop table dbo.Sample_Table; 2> go
[3] Wstaw/zaktualizuj/skasuj dane.
[root@vlsr01 ~]# sqlcmd -S localhost -U SA -d SampleDB Password: #wpisz hasło #wstaw dane 1> insert into dbo.Sample_Table ( Number, First_Name, Last_Name, Last_Update ) 2> values ( '00001', 'CentOS', 'Linux', '2022-01-29' ), 3> ( '00002', 'RedHat', 'Linux', '2022-01-29' ), 4> ( '00003', 'Ubuntu', 'Linux', '2022-01-29' ) 5> go (3 rows affected) 1> select * from dbo.Sample_Table; 2> go Number First_Name Last_Name Last_Update ---------- -------------------------------------------------- -------------------------- ------------------------ ---------------- 00001 CentOS Linux 2022-01-29 00002 RedHat Linux 2022-01-29 00003 Ubuntu Linux 2022-01-29 (3 rows affected) #pokaż konkretne kolumny w tabeli 1> select Number, First_Name from dbo.Sample_Table; 2> go Number First_Name ---------- -------------------------------------------------- 00001 CentOS 00002 RedHat 00003 Ubuntu (3 rows affected) #pokaż 2 pierwsze wiersze 1> select top 2 * from dbo.Sample_Table; 2> go Number First_Name Last_Name Last_Update ---------- -------------------------------------------------- -------------------------------------------------- ---------------- 00001 CentOS Linux 2022-01-29 00002 RedHat Linux 2022-01-29 (2 rows affected) #zmodyfikuj dane 1> update dbo.Sample_Table set Last_Update = '2022-01-30' 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 2022-01-30 (1 rows affected) #skasuj dane 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] Możliwe jest także uruchomienie T-SQL bezpośrednio.
[root@vlsr01 ~]# sqlcmd -S localhost -U SA -Q 'select name,create_date from sys.databases' Password: #wspisz hasło name create_date -------------------------------------------------------------------------------------------------------------------------------- ----------------------- master 2003-04-08 09:13:36.390 tempdb 2022-01-29 18:11:28.670 model 2003-04-08 09:13:36.390 msdb 2022-01-12 22:54:24.927 SampleDB 2022-01-29 18:13:58.667 (5 rows affected)