The database SQL Server in the Novice's eyes

Use this article to help yourself learn the knowledge of the database. I'm going to change my club now. Maybe it'll be enough! September 9, 2015 18:40:23

The database is a very high-end thing. The most primitive data is installed.

First of all, the database that I remember:

A place to store data. Database (database), Data Sheet (table)

We can manipulate data: Add (Insert) Delete (Select) change (Update) put together is IDSU (Love Is You)

It's like that!

However, the database is like this.

Operations database get to know this!

SQL classification:

• DDL: Data Definition language (create "creation", alter "change", Drop "Remove", Declare "declaration")
• DML: Data Manipulation language (select "Query", delete "delete", update "Updates", insert "Insert")
• DCL: Data Control Language (grant "authorization", Revoke "revoke authorization", commit "commit", RollBack "rollback")

Come on, one by one!

Grant Control commands:

The SQL command that gives the user permission.

The format is as follows:

Grant permissions on database object to user

Example:

Grant normal data user, the right to query, insert, UPDATE, delete all table data in the database.

Grant Select on testdb.* to [email protected] '% ' grant insert on testdb.* to [email protected] '% ' grant update on testdb.* t o [email protected] '% ' grant Delete on testdb.* to [email protected] '% '

--or use a single statement to complete
Grant Select,insert, Update,delete on testdb.* to [email protected] ' & '

Grant database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.

Grant create on testdb.* to [email protected] ' 192.168.0.% ', Grant alter on  testdb.* to [email protected] ' 192.168.0.% '; Grant drop on   testdb.* to [email protected] ' 192.168.0.% ';

such as is a key word for the right to give. What permissions are given to

Reovke with the above authorization just the idea, revoked.

Use the same method format as above.

Commit and rollback in things.

Besides, the operator.

UNION operator: a result table derived from combining the other two result tables (Table1 and Table2) and eliminating any duplicate rows in the table.

When all is used with the Union (that is, union ALL) does not eliminate duplicate rows. In both cases, each row of the derived table is either from Table1 or from Table2.

Except operator: a result table is derived by including all rows in Table1 but not in Table2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.

INTERSECT operator: By including only rows in Table1 and Table2 and eliminating all duplicate rows and deriving a result table. When all is used with INTERSECT (INTERSECT All) does not eliminate duplicate rows

Note: Several query result rows that use an operation word must be consistent.

Connection Query

This article mainly lists two and three tables to tell the multi-table connection query.
New Two sheets:
Table 1:student is as follows:

Table 2:course is as follows:

Outer JOIN (left outer connection):

Sql:select * from STUDENT A left OUTER joins COURSE B on A.id=b.id

Execution results

The Left (table) outer JOIN contains all the rows from the left table, and if a row in the left table does not match in the right table, the portion of the right table in the corresponding row in the result is all empty (null). Note: At this point we cannot say the number of rows of results. Of course, the number of rows in the query results here. Because the left and right tables are a one-to-one relationship

Right outer join

SQL statement: SELECT * from STUDENT right OUTER JOIN COURSE on Student.id=course.id

Execution Result:

The right table link contains all the rows from the right join, and if a row in the left table does not match in the right table, the portion of the corresponding left table in the result is all empty (null).

Full outer join (fully outer join)

SQL statement: SELECT * from STUDENT full OUTER JOIN COURSE on Student.id=course.id

Execution Result:

A full outer join contains all the rows in both tables. Left table not in the right table section is empty. The right table is empty in the left table section.


Inner joins inside joins
SQL statement: SELECT * from STUDENT INNER JOIN COURSE on Student.id=course.id
Execution Result:

Give a look, self-understanding according to the results.

Cross Join (crossover connection)
SQL statement: SELECT * from STUDENT cross JOIN COURSE
Execution Result:

If we add a WHERE clause to the SQL statement at this point.
SELECT * from STUDENT cross JOIN COURSE WHERE student.id=course.id
The result set returned at this time is the same as the result of the INNER JOIN


Two-table relationship is a one-to-many, many-to-many or multiple-pair connection statement
Of course, the above two tables are a one-to-one relationship, so how do we write a concatenated SQL statement if Table A and table B are a pair of many, many pairs, or many to many?
In fact, two tables of SQL statements and one-to-one SQL statements are similar, but the results of the query is not the same, of course, the two tables are also slightly modified.
For example, the columns in table 1 can be changed to:
Sno Name Cno
The columns in table 2 can be changed to:
Cno CName
In this way, two tables can write one-to-many and many-to-several SQL statements, just as you would with a single SQL statement above.
Here's a look at how we build tables and SQL statements when the two tables are many-to-many.
Create three new tables:
Table A:student is as follows:

Table B:course is as follows:

Table C:student_course is as follows:

A student can choose multiple courses, a course can be selected by multiple students, so the student table student and curriculum course is a many-to-many relationship.
When the two tables are many-to-many relationships, we need to create an intermediate table Student_course, the intermediate table must have at least two tables of the primary key, of course, there can be other content.
SQL statements: Select S.name,c.cname from Student_course as SC left joins student as s on S.SNO=SC. Sno left joins course as C on C.CNO=SC. Cno
Execution Result:

The result of this SQL execution is the case of the student's course selection.

Then post some SQL statements that will actually be used.

1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1 <>1
Law II: SELECT top 0 * into B from a

2. Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B;

3. Description: Copy of table across databases (use absolute path for specific data) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where."

4, Description: Sub-query (table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a in (all-in-a-

5, Description: Display the article, the author and the last reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

6, Description: External connection query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limits the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between value 1 and value 2

9. Description: How to use
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information
Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

11, Description: Four table linked to check the problem:
SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...

12, Description: Schedule five minutes before the reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

13, Description: A SQL statement to take care of database paging
Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field

14, Description: The first 10 records
Select Top Ten * form table1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table
(select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records
Select NEWID ()

19. Description: Delete duplicate records
Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

20, Description: List all the table names in the database
Select name from sysobjects where type= ' U '

21, Description: List of all the
Select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select.
Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then PCs else 0 end) from tablename GROUP By type
Show Results:
Type Vender pcs
PC A 1
PC A 1
Disc B 2
Disc A 2
Mobile B 3
Mobile C 3

23. Description: Initialize table table1
TRUNCATE TABLE table1

24. Description: Select records from 10 to 15
Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC
  
method of randomly selecting database records (using the Randomize function, implemented by SQL statements)
The random number feature gives the above effect to the data stored in the database, but they can be too slow. You can't ask the ASP to "find a random number" and print it out. A common solution is to create a loop that looks like this:
Randomize
Rnumber = Int (rnd*499) +1
 
While not objrec.eof
If Objrec ("ID") = Rnumber Then
... Here is the Execute script ...
End If
Objrec.movenext


Wend

It's easy to understand. First, you take out a random number within the range of 1 to 500 (assuming that 500 is the total number of records in the database). Then, you traverse each record to test the value of the ID and check if it matches the rnumber. If the condition is met, execute the piece of code that starts with the then keyword. If your rnumber equals 495, it can take longer to cycle through the database. Although the 500 figure looks larger, it is a small database compared to a more robust enterprise solution, which typically contains thousands of records within a database. You're not going to die now?
With SQL, you can quickly find the exact record and open a recordset that contains only that record, as shown here:
Randomize
Rnumber = Int (rnd*499) + 1
 
SQL = "SELECT * from Customers WHERE ID =" & Rnumber
 
Set Objrec = objConn.Execute (SQL)
Response.writernumber & "=" & Objrec ("ID") & "" & Objrec ("C_email")
 
You don't have to write Rnumber and ID, you just need to check the match. As long as you are satisfied with the work of the above code, you can record the "random" operation on demand. The recordset does not contain anything else, so you will soon be able to find the records you need so that the processing time is greatly reduced.
Talk about random numbers again
Now that you are determined to squeeze the last drop of the random function, you may be able to take multiple random records at one time or to use a certain random range of records. By extending the standard random example above, you can use SQL to deal with both of these situations.
To take a few randomly selected records and store them in the same recordset, you can store three random numbers and then query the database for records that match those numbers:
SQL = "SELECT * from Customers WHERE id =" & rnumber & "or id =" & RNumber2 & "or id =" & rnumber 3
 
If you want to select 10 records (perhaps a list of 10 links each time the page is loaded), you can use between or mathematical equations to select the first record and the appropriate number of incremental records. This can be done in several ways, but the SELECT statement only shows one possible (the ID here is an automatically generated number):
SQL = "SELECT * from Customers WHERE ID between" & Rnumber & "and" & Rnumber & "+ 9"

Note: The above code is not intended to check for 9 concurrent records within the database.

Randomly read a number of records, tested
Access syntax: SELECT top * FROM table name ORDER by Rnd (ID)
SQL server:select Top n * FROM table name ORDER by NEWID ()
Mysqlelect * FROM table name Order by rand () Limit n
Access left connection syntax (recently developed to use left connection, Access Help nothing, no SQL instructions for access online, only own test, now write it down for later check)
Syntax elect table1.fd1,table1,fd2,table2.fd2 from table1 left joins table2 on TABLE1.FD1,TABLE2.FD1 where ...
Use SQL statements with ... Instead of a long string display
Grammar:
SQL database: Select Case when Len (field) >10 and left (field,10) + ' ... ' else field end as news_name,news_id from TableName
Access database: SELECT iif (len Field) >2,left (field,2) + ' ... ', field) from TableName;

The database SQL Server in the Novice's eyes