Database (ii)

Modify a table  

If you cannot modify the table structure in SQL Server 2008, you are prompted with the error: Do not allow saving changes ...

Solution: tools → options → designers→ table Designer and Database Designer on the left

Remove the hooks in front of "prevent saving changes that require the table to be recreated" and restart the system.

--Modify the name of the database to change the name of student to Xuesheng
Sp_renamedb Student,xuesheng

Add columns:  

Alter table table name add column list type

--Modify the table, add columns, and note that when the word conflicts with the built-in words, the column name plus [] is enclosed.
ALTER TABLE Xinxi add [int] varchar (10)
ALTER TABLE Xinxi add nianling int

To delete a column:  

ALTER TABLE table name drop column name

--Modify table to delete a column
ALTER TABLE xinxi drop column [int]

to modify the type of a column:  

ALTER TABLE name ALTER COLUMN name new type

Insert Increment (add)  

Application: The table structure is unchanged, only one row or a value can be assigned without self-growth, column names are separated by commas, the order of values is the order of the table, if the input value is not complete, you can precede the column name to be entered, order one by one corresponds to values.

--Inserting data
INSERT into Xinxi values (1, ' Zhang San ', 96)
INSERT into Xinxi values (2, ' John Doe ', 91)
INSERT into Xinxi values (3, ' Harry ', 69)

Delete Deletes  

Delete from table name deletes table contents (table structure is still in) this type of deletion will write the log, so the self-growing serial number will continue to grow, and will not start from scratch

Truncate Table name This delete clears the table, is fast, does not write the log, and then enters from the beginning

Delete from table name where column name relationship expression value multi-criteria can be added and OR

Column name between value 1 and value 2 equals column name <= value 2 and column name >= value 1

Column name in (value 1, value 2, value 3,...) filters out a value of 1 or a value of 2 or a value of 3 ... The options

Table, select a data value by ctrl+0 this value to null

update changes, updates  

Update table Name set column name = value, column name = value, ... where column name relationship expression value

Update Xinxi set fenshu=100 where code=6

Retrieve Search, query  

Select *from Table

Select column name, column name, ... from table

Select *from table where column name relational operator value and column name relational operator value

Select *from table where column name between 1 and 100 (range query)

Select *from column name where column name in (3,4,5)

Select DISTINCT column name from table (column to weight)

Select *from column name where name like%5%% any number of arbitrary characters; _ an arbitrary character

---query statement, conditional query
Select *from Xinxi
Select Fenshu,name from Xinxi
Select Fenshu,name from Xinxi where name= ' John Doe '

Select *from Xinxi where Fenshu between and 100--range
Update Xinxi Set nianling = 100 where Fenshu between
Select distinct name from xinxi--for a column to re-display

Update Xinxi set name= ' John Doe ' WHERE code = 9
Select *from xinxi where name= ' John Doe ' and nianling =26
Select *from xinxi where name= ' John Doe ' or nianling =26
Select *from Xinxi where name in (' John Doe ', ' Zhao Liu ')
Select *from Xinxi where name not in (' John Doe ', ' Zhao Liu ')
--fuzzy query name with four, wildcard% means any number of characters
Select *from xinxi where name like '% four '
--Underscore any one character
Select *from xinxi where name like ' Li _ '
--underline the parentheses, equal to in the function, any set of satisfied on the query out
Select *from xinxi where name like ' _[John Doe, Zhao Liu, Tianqi] '

Filter  

Select *from table name where column name relationship expression value

Go heavy  

Select distinct column name from table name to remove duplicate values for this column

Fuzzy Query  

Select *from table name where column name like ' King% '

Wildcard:%: any number of characters; _: an arbitrary character; [4,5,6]: The brackets represent the value of the selection.

Sort  

Select *from table name order BY column name ASC (ascending) or desc (descending)

--sorted by age, ASC ascending, desc descending, default not written ascending
Select *from Xinxi ORDER BY nianling ASC
Select *from xinxi ORDER BY nianling Desc
--ranked in descending order, top three
Select Top 3 *from xinxi ORDER BY Fenshu Desc
--Sort by criteria, check the name of the person named John Doe who has the highest score
Select top 1 *from xinxi where name= ' John Doe ' ORDER by Fenshu Desc

Database (ii)