--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)"