Crud c--create in SQL Add data r--read read Data u--update modify data d--delete Delete data

Operations on the database in SQL Server:

To delete a table:
DROP table Name
To modify a table:
ALTER TABLE table name add column Add column list type
ALTER TABLE table name drop column name

Deleting a database
Drop database name

CRUD Operations
C--create Add data r--read read Data u--update modify data d--delete Delete data

1. Add Data
Insert into table name values (' first column value ', ' second column value ')--use single quotation marks in the database to add each column

The first way:

Insert into table name values (' first column value ', ')--if the second column does not want to be added and cannot be written, the available quotation marks are empty

The second way:

Add to a column: INSERT INTO Nation (column name) VALUES (' column value ')

Add to multiple columns, separated by "," after column names-(columns, columns)

If you use self-growth

In SQL Server: No need to do the first column starts with the second column

My SQL requires the first column of the Self-growth column

2. Delete data

Delete from table name--Delete all data

Delete from table name where ids=5--delete ids=5 data

3. Modify the data

Update table name set fcode= ' p016 ' modifies all fcode values to p016

Update table name set fcode= ' p016 ' where ids=6

Modify multiple columns
Update table name set fcode= ', mcode= ' where ids= columns and columns are separated by commas

String + ',
Boolean ' true ' or ' false ', 0 false or 1 true
Date type ' 1999-2-3 ' + single quote, in by format,
Plastic

Inquire:
1. Simple query
SELECT * FROM table name--Check all data
Select column name, column name from table name--Check the data for the specified column
Set alias Select column name as ' Alias ', column name as ' Alias ' from table name

2. Conditional query

SELECT * FROM table name where column name = ' Data '

SELECT * FROM table name where column name = ' data ' and column name = ' Data '
SELECT * FROM table name where column name = ' data ' or column name = ' Data '--multi-criteria Query

3. Scope Query
Car examples:
SELECT * FROM table name where column name (price) >40 and column name (price) <50

SELECT * from table name where price between and 50

4. Discrete query

SELECT * FROM table name where column name in (' c001 ', ' c005 ', ' c010 ', ' c015 ')
SELECT * FROM table name where column name not in (' c001 ', ' c005 ', ' c010 ', ' c015 ')--Reverse selection

5. Fuzzy query
Take the car watch as an example:
SELECT * FROM table name where column name like '% BMW% '
SELECT * FROM table name where column name like ' BMW% '--query with BMW start
SELECT * FROM table name where column name like '% BMW '--query with BMW end
SELECT * FROM table name where column name like ' BMW '-check equals BMW

_ Represents a character
SELECT * FROM table name where column name like ' __e% '--check the third character is E
% represents any number of characters

6. Sort queries

SELECT * FROM table name order BY column name--Default ascending ASC
SELECT * FROM table name order by column name desc--descending order

SELECT * FROM table name order BY column name desc, column name asc--is sorted in two fields, front main condition, back minor condition

7. Paging Query

Select Top 5 (show several per page) * from table name where column name not in (select Top 5 column name from table name)

Current: page = 2; per page: row = 10;
Select Top row (shows several per page) * from table name where column name not in (select Top (page-1) *row column name from table name)

8. Go to re-query

Select DISTINCT column name from table name

9. Group queries

Select Brand (column name) from table name GROUP by Brand (column name) having (adding conditions in the grouping, there must be group by) count (*) >2

10. Aggregation function (also called statistical query)

Select COUNT (*) from table name--Number of data bars queried

Select COUNT (primary key column) from table name--the number of all data bars queried

Select SUM (price) from car--sum
Select AVG (price) car--averaging
Select Max from car--Max
Select min (price) from car--minimum

Keyword is case insensitive

Crud c--create in SQL Add data r--read read Data u--update modify data d--delete Delete data