SQL SERVER 2008 Database Learning Note (i)--clown

Recently read a SQL SERVER2010 textbook, by the Way note (PS: This book is not read, so temporarily updated content)! The content for myself to organize, if there are errors, please point out lest affect others ' study.

1. Create a table TestDB

DROP TABLE TestDB

CREATE TABLE TestDB (

ID varchar (+) not NULL,

Name varchar (not NULL),

sex int not NULL,

CONSTRAINT pk_id PRIMARY KEY (ID)

);

2. to TestDB Insert a piece of data into the table

INSERT into TestDB (id,name,sex) VALUES (' 001 ', ' Clown ', 1);

3. Query TestDB all data in

SELECT * from TestDB;

4. a custom alias for the column name of the query

SELECT ID as c1,name as c2,sex as C3 from TestDB;

You can also not add [as]

SELECT ID C1,name c2,sex C3 from TestDB;

You can also add [""] to the alias, you can use the keyword to do the alias

Select id "as", name "SUM", Sex "CHAR" from TestDB;

5. using distinct Eliminate duplicate rows (high computational volume, not recommended for general use)

SELECT DISTINCT name,sex from TestDB;

6. Ordery by in ascending order

SELECT * from TestDB ORDER by sex

7. Ordery by case Sort by Criteria

SELECT ID C1,name c2,sex c3

From TestDB

Order BY case--an order by is an implicit cursor (cursor)

When Name= ' clown '--if the value of [name] column is all [clown]

Then sex--Sort by [sex]

Else ID--otherwise sorted by [ID]

END;

8. ordery by [column] DESC Descending arrangement

SELECT * from TestDB ORDER by ID DESC;

9. WHERE for data filtering

--Filter out all columns of all rows [id] = [001]

SELECT * from TestDB WHERE id= ' 001 ';

Note: You cannot use aggregate functions such as SUM () or count () in where!

Comparison operators fastest for equality [=] followed by unequal [<, <=, >, >=] slowest for not equal to [<> or! =]

The action of selecting a specific row in a table is called a [limit]

Where statement cannot use [alias] of [column name], must use original column name

Where is calculated prior to select

Ten , multi-criteria Query connector: and , or , not

And: Query multiple conditions at the same time as [true]

Or: A condition is established that can

Not: Take the result opposite to the condition

--Filter out all columns of all rows [id] = [001]

SELECT * from TestDB WHERE not id<> ' 001 ';

Note: Simultaneous use is and, or, not priority: not highest followed by and last for OR

One , like Fuzzy Query

--query [name] field contains data for [C]

SELECT * from TestDB WHERE name like '%c% '

--the query [name] field contains 6-bit-length data

SELECT * from TestDB WHERE name like ' _____% '

--Query the [name] field [C] or [a] or [b] at the beginning and end of [Lown] Data

SELECT * from TestDB WHERE name like ' [cab]lown% '

--escape ' \ ' This sentence declaration [\] is equivalent to the escape character of the query is the [_clown] keyword

SELECT * from TestDB WHERE name like ' \_clown% ' ESCAPE ' \ '

--Not a [6] bit or more of the keywords starting with [A]

SELECT * from TestDB WHERE name like ' [^a]clown% '

Note: Like is only tried on strings

A , between filter out a segment greater than or equal to and less than equals

--Filter out data with [ID] greater than or equal to and less than equals

SELECT * FROM TestDB WHERE ID between 1 and 2

- , inch whether the keyword filter value equals the given value

--Filter the values [ID] to [001] and [003]

SELECT * FROM TestDB WHERE ID in (' 001 ', ' 003 ')

- , is NULL whether the value is [NULL] (Ps:[null] does not mean literal )

--Filter the data [name] to [null]; [NULL] does not represent the literal but indicates whether it is empty

SELECT * from TestDB WHERE name is NULL

--Filter out the data [name] is non-null, [NULL] does not represent the literal but indicates whether it is empty

SELECT * from TestDB WHERE name was not NULL

the , SUBSTRING (column,position,length) extracting a string

--Extract [name] column value, starting from [1] bit [1] bit

SELECT SUBSTRING (name,1,1) from TestDB

- , UPPER (String) , LOWER (String) uppercase and lowercase conversion functions

--[name] column converted to lowercase

SELECT LOWER (name) from TestDB

--[name] column converted to uppercase

SELECT UPPER (name) from TestDB

- , gets the current timestamp

--Get the current timestamp

SELECT Current_timestamp

- , type conversion CAST (expression as type)

--Convert [sex] column to [varchar] Type

SELECT CAST (sex as varchar) from TestDB

+ , Case function is equivalent to IF ELSE

--case equivalent if ELSE

SELECT ID, ColumnName =

Case ID

When the ' 001 ' Then ' Road '

When ' 002 ' and ' Mountain '

When ' 003 ' and ' Touring '

ELSE ' Not for sale '

END

From TestDB

ORDER by ID;

- , INNER JOIN links within

---Inside Link-returns [TestDB] table [id] equals [TestDB2] table [OrderId]

SELECT * from TestDB T1

INNER JOIN TestDB2 T2

On t1.id = t2. OrderId

+ , sp_help View the structure of a table

--[g_gamelist] is the table name

Sp_help g_gamelist

A , UPDATE SET Update Data

--[G_gamelist] is the name of the table, [Gg_date] is the field that needs to be modified, and the value, if you do not add, modify all

UPDATE g_gamelist

SET gg_date = ' 2014-10-28 '

WHERE gg_id = ' 1 ';

at , DELETE Delete, do not delete table structure

--delete [g_gamelist] table, delete record can be restored

DELETE g_gamelist

--delete rows in [g_gamelist] table [gg_addid] field [1]

DELETE from G_gamelist WHERE gg_addid= ' 1 ';

- , DROP TABLE Delete, delete table structure and table

--drop Delete table is not recoverable, but faster than delete

DROP TABLE G_gamelist

- , TRUNCATE Delete causes the self-increment field to be zeroed but the data is not recoverable and the table structure is not deleted

TRUNCATE TABLE dbo.a;

- , Constraints

Not NULL prevents null values from being inserted into the column

PRIMARY key sets the primary key of the table

FOREIGN key sets the foreign key of the table

UNIQUE prevents the insertion of duplicate values into a column

Check uses a logical (Boolean) expression to restrict the values in the inserted column

Column constraint: is part of a column definition that is set as a condition for the column.

Table constraint: A condition that differs from a column definition and strengthens to more than one column in a table.

- , CREATE TABLE t_name Create a table

--Create a new table

CREATE TABLE t_testtable (

ID varchar (10),

Name varchar (10)

)

- , DEFAULT set default values for columns

--Create a new table and set the [name] default value to "

CREATE TABLE t_testtable (

ID varchar (10),

Name varchar (ten) not NULL DEFAULT ' '

)

in , PRIMARY key PRIMARY 　 key and foreign key FOREIGN　key

--Set [c_id] as the primary key

CREATE TABLE t_city (

c_id varchar (TEN) not NULL PRIMARY KEY,

C_name varchar (ten) is not NULL,

c_parment int DEFAULT 0,

)

--Create a [t_user] table, [u_id] primary key, [u_city] for the [c_id] column in the [t_city] table for the foreign key primary key

CREATE TABLE T_user (

u_id varchar (TEN) not NULL PRIMARY KEY,

U_name varchar (ten) is not NULL,

u_sex int not NULL DEFAULT 1,

U_city varchar (10),

CONSTRAINT fk_u_city

FOREIGN KEY (u_city)

REFERENCES t_city (c_id)

)

INSERT into T_city (c_id,c_name) VALUES (' 1 ', ' hulunbeier ');

--Foreign keys cannot insert data that is not in the parent table

INSERT into T_user (u_id,u_name,u_sex,u_city) VALUES (' 1 ', ' clown ', 1, ' 1 ');

--The foreign key can be empty

INSERT into T_user (u_id,u_name,u_sex) VALUES (' 2 ', ' Clown ', 1);

Note: A foreign key can be [NULL] but cannot be a value that does not exist in the parent table.

- , UNIQUE Value Unique

CREATE TABLE T_test (

t_id varchar (10),

CONSTRAINT un_id

UNIQUE (t_id)

)

Note: UNIQUE identification [ t_id ] Column Unique values cannot insert duplicate data

to , create a temporary variable table

To create a local variable table:

CREATE TABLE #t_TempTable (

tt_id CHAR (3),

)

To create a global variable table:

CREATE TABLE # #t_AllTempTable (

att_id CHAR (3),

)

Note: Creating a temporary variable table is used to store temporary data when the session ends or the end of a thing is cleared

+ , ALTER Modify

--Adding columns to the [t_city] table [C_time]

ALTER TABLE t_city

ADD c_time VARCHAR (10);

--delete [c_time] Column

ALTER TABLE t_city

DROP COLUMN C_time

- , CREATE 　 index Create indexes

--Create an index for the [c_id] column of the table [t_city]

CREATE INDEX index_name on t_city (c_id);

--Delete Index

DROP INDEX index_name on t_city;

Note: Index queries are faster but modifying and adding data is slow, and indexing requires automatic maintenance when adding or changing data

SQL SERVER 2008 Database Learning Note (i)--clown