SQL Server Database Base statement

The previous article describes the mouse operation

Two missing knowledge:

primary foreign key Two column data type must be consistent //int type cannot constrain nvarchar type varchar type cannot constrain nvarchar type

varchar (max) cannot be a primary key column until the length is fixed.

Table name, the database name does not allow the use of Chinese characters

Here is the statement operation

Go to operation file → Point open new query

Come out with a text

Statement (case-insensitive): (after writing the statement, create a database table after the execution of the Remember refresh)

To Create a database:

Create DATACSE database name

Example: Create DATABASE data0219

This means that the database name created is data0219

Database name cannot be Chinese, cannot start with a number, cannot start with a symbol

Deleting a database

Drop database name

Example: Drop Database data0219

Delete Database library name is data0219

Remember to use the DELETE statement to delete the deletion of the statement is careful to forget the subsequent execution of the data deleted when the data is not easy to reply

The statement comment is written as:/* A paragraph ... */

The front plus--is the comment on this sentence

A statement can execute a sentence or execute a set of statements

Use data0219

Using the data0219 Library

Create a table

CREATE TABLE Table name

(

Column name data type (separated by commas)

...

...

Set Primary key columns

Primary key

Unique column

Unique

Non-empty

NOT NULL

Self-increment column

Indentity (first)//starting from 1, each add a data column automatically add 1, whether the data is wrong or no error will add 1

)

Example: creat table student//Creating tables Student

(

IDS int indentity,//Column name IDs data type int self-increment column

Code Nvachar (primary),//code nvarchar type primary key column

Name nvarchar (200),

Sex bit,//gender sex bit type (true false or 1 0)

Birthday datetime unique,//birthday datetime type Unique column

Score Decimal (10,2),

Nation nvarchar,

)

Delete a table

DROP table Name

Example: DROP table student//Delete student tables

Add columns

ALTER TABLE name add column name data type //create this column of data is null if you want to be empty, just delete the table and build it again.

Example: ALTER TABLE student add Class nvarchar

Edit Table Student Add Class column nvarchar type

Delete Column

ALTER TABLE name drop column

Example: ALTER TABLE student drop column class

Edit Table Student Delete class column

Delete data

Delete from table name //Remove all columns in this table this is a row of deletes delete one row of archive records

For example: Delete from student//delete all columns in student not delete table

turncate Table name //Empty all the columns in the table the data cannot be returned to the original state.

Add data

Insert table name into values (' data ', ' data ', ... ) //Use single quote time as early as 1905-06-21 cannot be set

Example: Insert student into values (' s001 ', ' Zhang San ', 1, ' 1999-1-1 ', 95.5, ' N001 ')

CREATE table column name corresponding to the above code name sex birthday score Nation

modifying data

Update table name set column name = value

For example: Update student set nation= ' N001 '//nation This column of data is changed to N001

Change two columns of update student set Nation = ' N001 ', score=100 two column data is N001 100

Querying data

Select *from table name//query all columns of this table * represent all columns

SQL Server Database Base statement