"3-10" database statement writing

I. About DATABASE statements

(1) Create DATABASE database name

Note: Database names cannot start with Chinese, numbers, symbols

Example: Create Datdbase Student

(2) Delete database drop databases name

Example: Drop database Student

(3) Use database name

Example: Use student

Second, the statement of the table in the database

(1) CREATE TABLE table name

（

Column name data type

.......

）

Cases:

(2) DROP table name

Example: Drop table Student

(3) Modify the table:

Add column: ALTER TABLE name add column name data type

Example: ALTER TABLE student add Class nvarchar (50)

Delete column: ALTER TABLE table name drop column name

Example: ALTER TABLE student drop column class

(4) The addition of constraints in the table

1. PRIMARY KEY constraint: Add primary key after constrained column

2. Unique constraint: Add unique after constrained column

3. FOREIGN KEY constraint: ALTER TABLE name of FOREIGN KEY constraint add constraint name foreign key (foreign key field) references primary key table name (constraint column name)

4. Self-increment: identity (start number, self-increment number)

5. Cannot be empty: NOT NULL

Third, the statement of the data in the column

(1) Add data insert into table name values (' String ', ' Time Date ', ' True/false ', value)

Example: INSERT into student values (' s001 ', ' Zhang San ', ' true ', ' 2011-09-01 ', 98.05)

(2) Modify Data Update table name set column name = value

Example: Update student set sex= ' false '

(3) Deleting data delete from table name/truncate table table name

Note: Delete Row by line deletion, truncate all empty delete

Example: Delete from student

TRUNCATE TABLE Student

(4) Query data select *from table name

Example: Select*from student

Four, conditional operation statement

(1) Conditional modification: Update table name set column name = value where column name = value

Example: Update student set score=98 where Name= ' Zhang San '

(2) Conditional deletion:

Delete from table name where column name = value

(3) Advanced Enquiry

1. Condition query: Select column Name 1, column name 2 from table name where column name = (<,>,<=,>=) value and/or column name ...

2, Fuzzy query: Select*from table name where column name like '% value% '

3. Sort query: Select*from table name where order BY column name ASC (ascending)/desc (descending)

4, go to re-query: SELECT DISTINCT column name from table name

5, group query: Select a column name from the table name group by corresponding column name

"3-10" database statement writing