SQL Database CRUD

1. Delete a table

drop table + table name

2. Modify the table

Alter table+ table name + Add (ADD)+ column name + int (type)

Alter table+ table name + drop (delete)+column (column)+ column name

3. Deleting a database

Drop Datebase + library Name

CRUD Operations

C on behalf of Create add data

R represents read reading data

U modifies data on behalf of update

D on behalf of delete delete data

1. Add Data

Insert (insert/Add) into+ table name + values (added value), ' Convert string '

Insert into Nation values (' n002 ', ' hui ')

Insert into Nation values (' n003 ', ')

Multiple column additions: Insert into Nation (code,name) VALUES (' n003 ', ' Uighur ')

Insert into friends values (' p003 ', ' p007 ')

2. Delete data

Delete from Nation deletes all

Delete from friends where ids=5

3. Modify the data

Update Friends set fcode= ' p016 ' modifies all

Update Friends set fcode= ' p006 ', mcode= ' P002 ' where ids=4

Inquire

1. Simple query

SELECT * FROM info (table name)--Check all data

Select Code,name from Info--Check the data for the specified column

Select Code as ' Code ', name as ' name ' from info (change column name)--Assign alias to column

2. Conditional query

SELECT * FROM info where code= ' p001 '

SELECT * FROM info where sex= ' true ', and nation= ' n001 '-multi-conditional and relational

SELECT * FROM info where sex= ' true ', or nation= ' n001 '--multi-condition or relationship

3. Scope Query

SELECT * from car where price>40 and price<50

SELECT * from car where price between and 50

4. Discrete query

SELECT * from car where code in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

SELECT * from car where code not in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

5. Fuzzy query

SELECT * from car where name like '% BMW% '--check contains BMW's

SELECT * from car where name like ' BMW% '--look at the beginning of the BMW

SELECT * from car where name like '% BMW-check end of BMW '

SELECT * from car where name like ' BMW '--check etc with BMW's

SELECT * from car where name like ' __e% '--check the third character is E's

% represents any number of characters

_ Represents a character

6. Sort queries

SELECT * FROM Car ORDER BY price ASC-in ascending order of prices

SELECT * from the car order by price desc– in descending order of prices

SELECT * FROM car ORDER BY 0il DESC, price ASC-sorted in two fields, preceded by a secondary condition after the main condition

7. Paging Query

Select Top 5 * from car

Select Top 5 * from car where code not in (select Top 5 code from car)

Current page: page = 2 row= 10

Select Top row * from car where code not in (select Top (page-1) * Row code from CAR)

8. Go to re-query

Select distinct brand from car

9. Group queries

Select brand from Car GROUP by Brand has count (*) >2

10. Aggregation function (statistical query)

How much data from car– query all data bars after select COUNT (*) query is complete

Select count (code) from car--query all data bars

Select SUM (Price) is summed from car (table name)

Select SUM (price) from car sum

Select SUM (price) from car sum

Select SUM (price) from car sum

SQL Database CRUD