SQL Server programming must know (insert/delete/update data, view)--(80-85 points summary)

------------------------Inserting Data-----------------------
--80.1. Insert a full line
--Each column must be populated in the order in which they appear in the table definition
INSERT into customers
VALUES (' Pep E. Lapew ', ' Main Street ', ' LOS Angeles ', ' CA ', ' 90046 ', ' USA ', null,null)

--INSERT Statement safe operation method, specify column, into keyword is optional
INSERT into customers
(Cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES (' Pep E. Lapew ', ' Main Street ', ' LOS Angeles ', ' CA ', ' 90046 ', ' USA ', null,null)

--80.2. Insert part of a row
INSERT into customers
(Cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (' Pep E. Lapew ', ' Main Street ', ' LOS Angeles ', ' CA ', ' 90046 ', ' USA ')

--80.3. Insert multiple rows, only one row of values at a time, and a single INSERT statement does not support multiple values clauses
INSERT into customers
(Cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (' Pep E. Lapew ', ' Main Street ', ' LOS Angeles ', ' CA ', ' 90046 ', ' USA ')
INSERT into customers
(Cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (' M. Martian ', ' Galaxy ', ' New York ', ' NY ', ' 11213 ', ' USA ')

--80.4. Insert the results of some queries
INSERT into Customers (
Cust_contact,
Cust_email,
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
)
SELECT Cust_contact,
Cust_email,
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
From customers
where cust_id=10001

--80.5. Create a row that contains the retrieved rows from one or more tables, and you can use the SELECT INTO table, the table must not exist, or an error will occur;
SELECT Cust_contact,
Cust_email,
Cust_name,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
Cust_country
Into Customersexport
From customers

------------------------Update Data-----------------------

--the table to be updated
--column names and their new values
--Determine the filter criteria to update rows

--81.1 update the specific line in the table: Update the e-mail address of customer 10005
Update Customers
Set cust_email = ' [email protected] '
where cust_id = 10005

Update Customers
Set cust_email = NULL
where cust_id = 10005

--81.2 update all rows in the table: Update all customer email addresses as ' [email protected] '
Update Customers
Set cust_email = ' [email protected] '

--81.3 update multiple columns for customer 10005, email address and name
Update Customers
Set cust_email = ' [email protected] ',
Cust_name = ' The Fudds '
where cust_id = 10005

------------------------Delete Data-----------------------

--Guidelines for deleting and updating: It is best to use the update and delete clauses with where, SQL Server does not revoke the under button. All must be careful to use update and delete;

--82.1 Delete a specific row from the table: delete cust_id=10006 rows
DELETE from Customers
WHERE cust_id= 10006

--82.2 Remove all rows from the table
DELETE from Customers

------------------------Create and manipulate tables-----------------------

--Two ways to create a table: 1. Use SSMs to create and manage tables for public transport. 2. Manipulating with SQL statements

--Create a table base: The name of the new table, the name and definition of the table column, separated by commas;

--83.1  Create Customers table, the primary key is cust_id unique and automatically grow, can also be composed of multiple columns; The null value is specified with the keyword NULL instead of the empty string, and the empty string ' is a valid value, it is not no value;
CREATE TABLE Customers
(
cust_id          int        not NULL IDENTITY (cust_name ),
      NCHAR ()   NOT NULL,
cust_address    NCHAR   NULL,
cust_city       NCHAR   NULL,
cust_state       NCHAR (5)    null,
cust_zip        NCHAR (TEN)   NULL,
cust_country    NCHAR ()   NULL,
cust_contact    NCHAR (+)   NULL,
cust_email      NCHAR (255) NULL,
PRIMARY KEY (cust_id)
);

CREATE TABLE OrderItems
(
Order_num INT not NULL,
Order_item INT not NULL,
prod_id NCHAR (Ten) not NULL,
Quantity INT not NULL,
Item_price money is not NULL,
PRIMARY KEY (Order_num,order_item)
)

--The identity (1) indicates that the increment for each new number is 1, starting from seed 100, and the Identity (100,10), which starts with the seed, and the increment is 10;

--83.2 Update table, add a column vend_phone to table OrderItems, type char (20), and add columns even if there is data in the table, but it is not recommended
ALTER TABLE OrderItems
ADD Vend_phone CHAR (20)

---83.3 update the table to delete a column vend_phone for table OrderItems, but before deleting, confirm that the column data in the table is also deleted
ALTER TABLE OrderItems
DROP COLUMN Vend_phone

--83.4 Update table, add foreign key

--Add the foreign key fk_ordertiems_orders to the table OrderItems, which is the primary key of the table orders Order_num
ALTER TABLE OrderItems
ADD CONSTRAINT fk_ordertiems_orders FOREIGN KEY (order_num)
REFERENCES orders (Order_num)

ALTER TABLE Products
ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id)
REFERENCES Vendors (vend_id)

-ALTER TABLE can be used to change tables (or constraints, indexes, and other objects)

--83.5 table the manual removal process of structural changes, it is best to make a full backup before the operation, you need to follow the following steps:

-A. Create a new table with a new table name to contain the new columns
CREATE TABLE Orderitems_temp
(
Order_num INT not NULL,
Order_item INT not NULL,
PROD_ID nchar (Ten) not NULL,
Quantity INT not NULL,
Item_price money is not NULL,
Item_price2 money is not NULL,
PRIMARY KEY (Order_num,order_item)
)

--B. Using the Insert SELECT into statement to copy data from the old table to the new table
INSERT into Orderitems_temp
SELECT * from OrderItems

--C. Verify that the data for the new table contains information about the old table, select * FROM Orderitems_temp

--D. Rename old table, suggest renaming old table do not delete, pending verification successful, can be deleted: exec sp_rename ' orderitems ', ' orderitems_old '

-E. Rename the new table with the old table's original name: exec sp_rename ' orderitems_temp ', ' OrderItems '

--F. Recreate triggers, stored procedures, indexes, and foreign keys as needed; re-create foreign keys below

ALTER TABLE Orderitems_old
DROP CONSTRAINT Fk_ordertiems_orders

ALTER TABLE Orderitems_old
DROP CONSTRAINT fk_orderitems_products

ALTER TABLE OrderItems
ADD CONSTRAINT fk_ordertiems_orders FOREIGN KEY (order_num) REFERENCES orders (Order_num)

ALTER TABLE OrderItems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id)

--84.1 Delete table Orderitems_old, the statement does not need to be confirmed, nor can it be undone, and the table will be permanently deleted after execution.

DROP TABLE Orderitems_old

------------------------Use views-----------------------

---85.1 view is a virtual table, unlike a table that contains data, the view contains only queries that retrieve data dynamically when used, and the view contains a SQL query

--85.2 Why use a view

--A. SQL statements can be reused;
--B. Simplify complex SQL operations; You can write the underlying SQL one at a time and then use it as many times as you want;
-C. Use the components of the table rather than the entire table;
-D. Protect data, you can grant access to specific parts of the table rather than the entire table;
-E. Change data format and representation, the view can return different data with the presentation and format of the underlying table;
The view itself does not contain data, and the returned data is retrieved from other tables, and the view returns the changed data when the data in those tables is added or changed.

--85.3 views of rules and restrictions

--A. As with a table, the view must be uniquely named;
--B. There is no limit to the number of views that can be created;
--C. SQL Server view may contain no more than 1024 columns;
--D. In order to create a view, you must have sufficient access rights. These limits are typically granted by the database administrator;
--E. Views can be nested, that is, queries that can retrieve data from other views to construct a view;
--F. Order by cannot be used in a view, but can be used in a SELECT statement that retrieves data from a view;
--G. The view cannot be indexed, nor can it have associated triggers or default values;
--H. Views can be used with tables, such as a SELECT statement that writes a junction table and a view

--Use of 85.4 views

--A. Create a view productcustomers, return a list of all customers who have ordered any product

--Create VIEW Productcustomers
CREATE VIEW Productcustomers as
SELECT Cust_name,cust_contact, prod_id
From customers, orders, OrderItems
WHERE customers.cust_id = orders.cust_id
and Orderitems.order_num = Orders.order_num

--Returns a list of all customers who have ordered any product
SELECT * from Productcustomers

--Returns a list of customers who ordered the product TNT2
SELECT Cust_name,cust_contact
From Productcustomers
WHERE prod_id = ' TNT2 '

--B. Create a view vendorlocations to reformat the retrieved data
CREATE VIEW Verdorlocations as
SELECT RTrim (vend_name) + ' (' + RTrim (vend_country) + ') '
As Vend_title
From vendors

SELECT *
From Verdorlocations
ORDER by Vend_title

--C. Create a view customer_email_list, filter out the non-data, return the customer without an email address
CREATE VIEW Customer_email_list as
SELECT cust_id, Cust_name, Cust_email
From customers
WHERE Cust_email is not NULL

SELECT *
From Customer_email_list

--D. Create a view orderitemsexpanded, simplify the calculation field, retrieve the details of the order 20005;

CREATE VIEW orderitemsexpanded as
SELECT
Order_num,
PROD_ID,
Quantity
Item_price,
Quantity*item_price as Expanded_price
From OrderItems

SELECT *
From orderitemsexpanded
WHERE Order_num = 20005

-E. Delete View orderitemsexpanded:

DROP VIEW orderitemsexpanded

--85.5 View update: Actually adds or deletes rows to the base table; The view is mainly used for data retrieval;

--the views are updatable and can be used with insert,update and delete operations;

--Not all views can be updated, and if the view definition has the following, the view will not be updated;

--A. There are multiple base tables
--B. There are groups (using group by and having)
--C. Have junction (join)
--D. have subqueries
--E. Have and (UNION)
--F. Aggregation function (Min (), Count (), Sum (), etc.)
--G. have distinct keyword
--H. Have an export (computed) column

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1705546

SQL Server programming must know (insert/delete/update data, view)--(80-85 points summary)