"SQL. Basic Build-fourth (4/4)"

--Tips: Data updates

--First, insert data

Basic syntax for--1.insert statements

--Syntax:
--insert into < table name > (column 1, column 2, ...) Values (value 1, value 2, ...)

--Example
INSERT into Dbo.conbio
(CONBIO_ID,
Conbio_name,
Conbio_varieties,
Conbio_price1,
Conbio_price2,
Conbio_date
)
VALUES (' 0011 ',--Conbio_id-char (4)
' t-shirts ',--conbio_name-varchar (100)
' Clothes ',--Conbio_varieties-varchar (32)
,--Conbio_price1-int
,---Conbio_price2-int
GETDATE ()--conbio_date-date
);

------------------
SELECT * FROM Conbio-the table with the query table named Conbio.

--------------------------------------

--(1) The column names and values are separated by commas and placed in parentheses, which is called the list.

(SHOHIN_ID,
Shohin_mei,
Shohin_bunrui,
Hanbai_tanka,
Shiire_tanka,
Torokubi
)
--Column list--field

------------------

(' 0011 ',---Shohin_id-char (4)
' t-shirts ',--shohin_mei-varchar (100)
' Clothes ',--Shohin_bunrui-varchar (32)
,--Hanbai_tanka-int
,---Shiire_tanka-int
GETDATE ()--torokubi-date
)
--List of values--record

------------------

INSERT into Dbo.conbio
(CONBIO_ID,
Conbio_name,
Conbio_varieties,
Conbio_price1,
Conbio_price2,
Conbio_date
)
VALUES (' 0014 ',--Conbio_id-char (4)
' t-shirts ',--conbio_name-varchar (100)
' Clothes ',--Conbio_varieties-varchar (32)
,--Conbio_price1-int
,---Conbio_price2-int
GETDATE ()--conbio_date-date
),
(' 0015 ',---Conbio_id-char (4)
' t-shirts ',--conbio_name-varchar (100)
' Clothes ',--Conbio_varieties-varchar (32)
,--Conbio_price1-int
,---Conbio_price2-int
GETDATE ()--conbio_date-date
);

--------------------------------------

--3. Inserting NULL values

INSERT into Dbo.conbio
VALUES (' 0016 ',--Conbio_id-char (4)
' t-shirts ',--conbio_name-varchar (100)
' Clothes ',--Conbio_varieties-varchar (32)
,--Conbio_price1-int
NULL,--Conbio_price2-int
GETDATE ()--conbio_date-date
);

--"Remarks" the column that inserts a NULL value cannot be set to a NOT NULL constraint.

--------------------------------------
SELECT * FROM Conbio--query table
--------------------------------------
----Add columns
SELECT * from Conbio;
ALTER TABLE Conbio add conbio_name_test001 varchar (100);
--------------------------------------
----Delete Columns
ALTER TABLE conbio DROP COLUMN conbio_name_test001;

--------------------------------------

--4. Inserting default values

--Example: setting a default value when creating a table

CREATE TABLE Conbioins
(
ID CHAR (4) is not NULL,
Num INT default 0--default constraint
)

--select * FROM conbioins check table

--Example: Insert default value is displayed when insert

INSERT into Dbo.conbioins
(ID, NUM)
VALUES (' 002 ',--Id-char (4)
DEFAULT--Num-int
)

--Example: Insert implicitly inserts default values

INSERT into Dbo.conbioins (ID)
VALUES (' 003 ')


---"remarks" suggests inserting values using the displayed method. If the column name is implicitly omitted when inserting, and the column does not have a default value specified,
--The value of the column is set to null (if the omitted column is set with a not NULL constraint, execution will be an error).

---"summary" omits the column name of the INSERT statement, which automatically sets the default value for the column (which is set to NULL if no default value).

--------------------------------------

--5. Copying data from other tables

--Grammar
--insert ...
--select ...

--Example
INSERT Dbo.conbioins
(ID, NUM)
SELECT conbio_id, Conbio_price2
From Dbo.conbio

--Example
INSERT into Dbo.conbioins
(ID, NUM)
SELECT id=1, num = 2;

--Example
INSERT into Dbo.conbioins
(ID, NUM)
SELECT 1, 2;

--select * FROM conbioins check table

--In the SELECT statement in the remarks INSERT statement, you can use any SQL syntax such as a WHERE clause or a GROUP by clause. (But using the ORDER by clause does not produce any effect.) ）

--------------------------------------

--Second, delete data

--1.2 Ways to delete data:

--(1) DROP TABLE statement: Delete tables

--(2) DELETE statement: Delete data from table

--Grammar
--delete from < table name >;

--Example
DELETE from Dbo.conbioins; --This table data is deleted as empty all

--The "memo" DELETE statement deletes the object that is a record (row), not a table and a column.

--------------------------------------

--2. Specifying DELETE statements for deleted objects

--Grammar
--delete from < table name >
--where < conditions >;

--Example
DELETE from Dbo.conbio
WHERE Conbio_price1 >= 1000; --Conditional Delete record line

--"notes" can delete part of the data by specifying an object condition from the WHERE clause.

--The GROUP by, have, and ORDER by three clauses cannot be used in the memo DELETE statement.

---"note" If you want to delete all the data in the table, use TRUNCATE < table name, which is more efficient than the DELETE clause.

--select * from Conbio--check table

--------------------------------------

--third, update the data

Basic syntax for--1.update clauses

--Grammar
--update < table name >
--Set < column name > = < expression >

--2. UPDATE statements that specify conditions

--Grammar
--update < table name >
--Set < column name > = < expression >
--where < conditions >;

--Example
UPDATE Dbo.conbio
SET Conbio_price2 = Conbio_price2 * 10
WHERE conbio_varieties = ' kitchen utensils ';

--3, updating with NULL

--Example
UPDATE Dbo.conbio
SET conbio_date = NULL
WHERE conbio_id = ' 008 ';

--"note" Only columns that do not have a not set NULL constraint and a PRIMARY KEY constraint can be emptied to null.

--------------------------------------

--4. Multiple column Updates

--Example
UPDATE Dbo.conbio
SET Conbio_price1 = Conbio_price1 * 10,--comma split
Conbio_price2 = CONBIO_PRICE2/2
WHERE conbio_varieties = ' kitchen utensils '

--------------------------------------

--Four, business

--1. Transactions: A collection of series of update processing that needs to be performed in the same processing unit.

--2. Creating a transaction:
--Grammar
--Transaction Start statement
--DML statement ①;
--DML statement ②;
--    ...
--the concluding sentence of a transaction (COMMIT or ROLLBACK);

--(1) Commit-commit processing: Commits the end instruction of all update processing contained in the transaction. Once committed, it cannot be restored to the state before the transaction started.

--(2) ROLLBACK-Cancel processing: Cancels the end instruction of all update processing that the transaction contains. Once rolled back, the database responds to the state before the transaction begins.

------------------

--Example
BEGIN TRANSACTION;
UPDATE Dbo.conbio
SET Conbio_price1 = conbio_price1-1000
WHERE conbio_name = ' fork ';

UPDATE Dbo.conbio
SET Conbio_price1 = Conbio_price1 + 1000
WHERE conbio_name = ' fork ';

DELETE from Dbo.conbio WHERE conbio_id = ' 6 ';

Rollback -Commit Commitment

--------------------------------------
--3.acid characteristics

--(1) atomicity (atomicity): At the end of a transaction, the update processing contained in it is either fully executed or not executed at all.

--(2) Conformance (consistency): the processing contained in the transaction, which satisfies the constraints of the pre-set of the database, also known as completeness.

--(3) Isolation (lsolation): A feature that guarantees non-interference before different transactions.

--(4) Persistence (durability): Once the transaction is over, the DBMS guarantees that the data state at that point is preserved, also known as durability.

--------------------------------------

--Welcome to Personal public Number: Zkcops

--2018/04/16
　
By: Zkcops writing (hope to be helpful to you, reprint annotated source!) ）
--------------------------------------

"SQL. Basic Build-fourth (4/4)"