"Notes" Database Fundamentals 02

Some basic commands for SQL

Inserting Data

Insert into Persion1 (name,age) Values (' Zhanghui ', 21);

Automatically generate serial numbers with NEWID.

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;

* * equals number is an equal sign in the database.

**     "<>": means not equal;

* * or: for OR;

Delete data

Delete Table All

Delete from Person1

Delete table data, table is still in.

Delete from Person1 where age>21

Data Retrieval

Select * FROM <*: Indicates the table name > The following can also be added where to partition size

The alias of the start

Select FName as name, Fage as age, fsalary as monthly from Student

Aggregation Functions

Select Count (*) from Student; < sum >

Select Max (*) from Student;

Select SUM (*) from Student; < and >

Sort

Select * from Student ORDER by ages Asc/desc < sorted by age >

<ASC: from small to large; DESC: from Big to Little >

Fuzzy Matching < wildcard characters >

* Single wildcard with ' _ ', which matches individual occurrences of the character.

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

Select * from Student where FName like ' _erry '

* multiple characters match with '% ', which matches any character that appears in this number. "K%" matches a string that begins with K.

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

* * NULL in the database is ' not known ' and is not interpreted in C #. There is no value.

Grouping of data

The number of people at all ages is grouped according to age:

Select Fage, Count (*) from Student

GROUP BY Fage

* * the GROUP BY clause must be placed after the where statement.

* * the column in the GROUP BY clause does not appear in the list of column names after the SELECT statement (except aggregate functions)

Having statements

You cannot use aggregate functions in where, you must use having,having after group by.

eg

Select Name from, COUNT (*) as number from Student

GROUP BY Name

Having count (*) >1 //having can not replace where,having is to filter the group.

Limit the number of rows in a result set

eg: < only take the first 3 lines >

Select Top 3 * from Student

ORDER BY Name DESC

eg: < only remove the first 3 > of 5 rows

Select Top 3 * from Student
where Name not in (select top to Student order by age DESC)
ORDER BY age DESC

<: Why is there an error??? >

Remove Duplicate data

eg

Select Gender from Student

Select Gender, Chinese from Student

eg: < after adding distinct, remove duplicates >

Select distinct Gender,chinese from Student

<distinct is the process of data duplication across the entire result set, not for each column. >

Select distinct Gender from Student

The commands for the database are the same in different databases, but only the most basic SQL database can be applied to the Oclace database. Although now just learning database, but I think it is very interesting, I like the database. Come on.

"Notes" Database Fundamentals 02