Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL Server 2022 (SQL Server 2022 (Constraints (Default value (ALTER TABLE…
SQL Server 2022
SQL Server 2022
Constraints
Default value
ALTER TABLE Orders
ADD CONSTRAINT DF_OrderDate
DEFAULT GETDATE() FOR OrderDate;
Check(Validate value)
ALTER TABLE Products
ADD CONSTRAINT Check_Product
CHECK (Price>=0 AND Price<=2000);
PrimaryKey => ForeignKey
OR 1 => n
ALTER TABLE Products
ADD CONSTRAINT FK_CategoryProduct
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID);
ALTER TABLE Products
DROP CONSTRAINT contraintName;
Unique
ALTER TABLE Products
ADD CONSTRAINT UN_Product
UNIQUE (ProductName, Price);
CRUD = Create Read Update Delete
Query(Select)
Database's functions
SELECT MAX(Price) as "Highest price" FROM Products;
as "Highest price" => called "Alias"
select Average value
SELECT AVG(Price) as "Average Price" FROM Products;
Calculate total = select SUM
SELECT SUM(Price) as "Sum", COUNT(*) as "Number of Products"
FROM Products;
Show all tables in your DB:
SELECT * FROM Sys.Tables;
SELECT * FROM Customers;
"GROUP BY" must have COUNT
SELECT COUNT(ProductID), CategoryID
FROM Products GROUP BY CategoryID
HAVING CategoryID < 7;
...WHERE Country IN ('Brazil', 'Sweden');
...WHERE CustomerName NOT LIKE '[a-e]%';
...WHERE CustomerName LIKE '%john%'
SELECT * from Customers WHERE CustomerName LIKE 'm%';
SELECT TOP 4 * FROM Customers;
How to paging ?
SELECT TOP 10
FROM Customers OFFSET 0 ROWS;
SELECT TOP 10
FROM Customers OFFSET 10 ROWS;
SELECT TOP 10 * FROM Customers OFFSET 20 ROWS;
...
Join Tables
Categories - Products: 1 - n
Join many tables
SELECT Table1.*, Table2.*, Table3.* FROM Table1
INNER JOIN Table2 ON Table1.id12=Table2.id12
INNER JOIN Table3 ON Table2.id23=Table3.id23
ORDER BY...;
SELECT Products.*, Categories.* FROM Products
INNER JOIN Categories
ON Products.CategoryID=Categories.CategoryID
ORDER BY Products.ProductID;
Backup some data to another Table
SELECT Products.* INTO ProductsBackup FROM Products;
Return JSON values
Copy Result to Notepad and see
Data as "text":
SELECT * FROM Products
FOR JSON AUTO;
Data as "Row":
SELECT ...
FOR JSON PATH;
Delete
DROP DATABASE dbName;
DROP TABLE Products;
DELETE FROM Products
WHERE ProductID=15;
Update
UPDATE Categories
SET Categories.counts=0
WHERE Categories.counts is NULL;
Create
CREATE TABLE Customers (
CustomerID int NOT NULL IDENTITY PRIMARY KEY,
CustomerName nvarchar(300),
Address varchar(1000));
Add more columns into a table:
ALTER TABLE Customers
ADD email VARCHAR(300);
ALTER TABLE Customers
ADD/ALTER COLUMN email VARCHAR(300);
Drop a column:
ALTER TABLE Customers
DROP COLUMN email;
Create a View
SELECT * FROM DbName.viewA;
CREATE VIEW DbName.viewA AS
SELECT * FROM ...
MySQL - the same !
Concatenate a String:
SELECT concat(FirstName,' ',LastName) AS 'Full Name'
FROM tblUsers;
CREATE DATABASE dbName;
Switch to current Database:
USE dbName;
Insert
INSERT INTO Customers(CustomerName,Address,...) VALUES('Thomas Hardy', '120 Hanover Sq',...);
Insert multiple records(bundle)
INSERT INTO Customers(CustomerName,Address,...) VALUES
('Thomas Hardy', '120 Hanover Sq',...),
('John abc', '334 Xyz Sq',...),
.....;
Grant Permissions
Permissions for columns:
Choose Table, RC, Properties, Search "users"
Open Column Permissions
Login and test permissions
Set Permissions for a Table:
Choose Table, RC, Properties, Search "users"
Check grant or deny
Security,Login, RC,New Login
Type login name, password
Choose UserMapping, check DB, DB Roles
Trigger
1.After Insert
2.After Update
3.After Delete
Delete a trigger:
DROP TRIGGER triggerName;
CREATE TRIGGER trg_UpdateProduct
ON Products AFTER UPDATE AS
DECLARE @Price AS FLOAT
SET @Price = (SELECT TOP 1 Price FROM Products WHERE Price<0)
IF @Price < 0
BEGIN RAISERROR ('Cannot update negative Price',16,10);
ROLLBACK
END
CREATE TRIGGER ....
...select CategoryID from deleted
....select CategoryID from inserted(donot have updated)
Trigger on MySQL(don't have AS, have BEFORE):
CREATE TRIGGER tCheckTime
BEFORE INSERT ON Orders
FOR EACH ROW BEGIN
...
END;
IF(NEW.dateTimeStart < NEW.createdDate)....
trigger BEFORE UPDATE: also use NEW
Raise error(MySQL):
signal sqlstate '45000' set message_text = "...error abc..";
Procedure
Synonyms
CREATE SYNONYM personName
FOR Customers.name;
Applied for DB Objects:
1.Stored Procedure
2.Table
3.SQL Scalar function
3.View
Table-valued functions
ALTER FUNCTION functionB()
RETURN [datetime] AS....
Function return a Table:
CREATE FUNCTION TableA.functionA()
RETURN TABLE
AS
RETURN(SELECT ...INNER JOIN...);
Scalar functions
Has 1 param, 1 return value
Transaction
A unit of work
BEGIN TRANSACTION transactionName;
INSERT INTO...
INSERT INTO...
COMMIT;
After commit:
...
COMMIT;
@@TRANCOUNT
Rollback
BEGIN TRANSACTION...
INSERT INTO...
ROLLBACK;
--Nothing change !
Mark/describe a Transaction
BEGIN TRANSACTION transactionName
MARK N'This will do something...'
...
Procedure is a "function"
error
Procedure with "output" parameters
Error Handling
Error number, eg: 5/0
BEGIN TRY
SELECT 5 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
BEGIN TRY
UPDATE Products SET....;
END TRY
BEGIN CATCH
IF
@@ERROR...
END CATCH;
Do not like
@@ERROR
?
SELECT ERROR_STATE() AS ErrorState;
SELECT ERROR_SEVERITY() AS ErrorSeverity;
Casting values
DECLARE @x as INT;
SET @x = 100;
PRINT 'x = '+cast(@x as VARCHAR);
Print value ?
PRINT 'Hello world'
CREATE PROCEDURE procedureName
@countX int OUTPUT AS
SELECT @countX = count(*) FROM Products...
MySQL:
CREATE PROCEDURE searchProducts(nameContain VARCHAR(200))
BEGIN
END;
MYSQL's function:
CREATE FUNCTION funcA(x int, y int) RETURNS VARCHAR(500)
BEGIN
...
RETURN "abc";
END;
Call a Procedure ?
EXECUTE procudureName 2,3;
Show/describe a procedure:
EXEC sp_helptext N'proc_name';
Describe a procedure in MySQL:
show create procedure proc_name
EXECUTE searchProducts N'japanese';
MySQL:
CALL searchProducts("japanese");
Name: Nguyen Duc Hoang
Youtube:
https://www.youtube.com/c/nguyenduchoang