[Note] database basics 02 and note database 02

[Note] database basics 02 and note database 02

SQL basic commands

Insert data

Insert into Persion1 (Name, Age) Values ('zhanghui', 21 );

Use newid to automatically generate sequence numbers.

Insert into Persion2 (Id, Name, Age) Values (newid (), 'hui', 22 );

Update Data

1: UPDATE T_Person1 set Age = 30;

2: Update Person set Name = 'xiaohui'

Where Age> = 20;

** In the database, the equal sign is an equal sign.

** "<>": Not equal;

** Or: or;

Delete data

Delete all tables

Delete from Person1

Delete the table data. The table is still in progress.

Delete from Person1 where Age> 21

Data Retrieval

Select * from <*: indicates the Table Name>. You can add the where clause to partition the table size.

Its alias

Select FName as name, FAge as age, FSalary as monthly salary from Student

Aggregate functions

Select count (*) from Student; <sum>

Select max (*) from Student;

Select sum (*) from Student; <and>

Sort

Select * from Student order by Age ASC/DESC <sort by Age>

<ASC: from small to big; DESC: from large to small>

Fuzzy match <wildcard>

* The Single-character wildcard character is '_', which matches a single occurrence character.

Start with any character and the rest is 'erry ':

Select * from Student where FName like '_ erry'

* Multiple characters are matched with '%', which matches any number of characters. "K %" matches the string starting with K.

Select * from Student where FNAme like '% n %'

** The NULL value in the database is unknown and the interpretation in C # is different. No.

The age groups are used to count the number of people with age disconnections:

Select FAge, Count (*) from Student

Group by FAge

** The group by clause must be placed after the where statement.

** If the column in the group by clause does not appear, it cannot be placed in the column name list after the select statement (except for Aggregate functions)

Having statement

Aggregate functions cannot be used in where, Having must be used, and Having must be placed after Group.

Eg:

Select Name from, count (*) as count from Student

Group by Name

Having count (*)> 1 // Having cannot replace where. Having filters groups.

Eg: <only take the first three rows>

Select top 3 * from Student

Order by Name DESC

Eg: <only remove the first three of the five rows>

Select top 3 * from Student
Where Name not in (select top 2 * from Student order by Age DESC)
Order by Age DESC

 

Eg:

Select Gender from Student

Select Gender, chinese from Student

Eg: <remove duplicates after distinct is added>

Select distinct Gender, chinese from Student

<Distinct repeats data in the entire result set, rather than for each column.>

Select distinct Gender from Student

Database commands are the same in different databases, but only SQL databases with the most basic knowledge can be applied to databases such as oclace. Although I am only studying databases, I think it is quite interesting and I like databases very much. Come on.