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

Source: Internet
Author: User
Tags getdate rollback


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.