SQL Basics Review

First, the SQL basic statement

SQL classification:

ddl-Data Definition language (Create, Alter, Drop, DECLARE)

dml-Data Manipulation language (Select, Delete, Update, Insert)

dcl-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

First, a brief introduction to the underlying statement:

1. Description: Create Database

Create DATABASE Database-name

2. Description: Delete Database

Drop Database dbname

3. Description: Back up SQL Server

---to create a device that backs up data

Use master

EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '

---start Backup

BACKUP DATABASE pubs to Testback

4. Description: Create a new table

CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

A:create table tab_new like Tab_old (create new table with old table)

B:create table tab_new as Select Col1,col2 ... from tab_old definition only

5. Description: Delete new table drop table TabName

6. Description: Add a column

Alter table tabname Add column col type

Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.

7. Description: Add primary key: Alter table TabName Add primary key (COL)

Description: Delete primary key: Alter table tabname drop primary key (COL)

8. Description: Create INDEX: [unique] index idxname on tabname (col ...)

Drop INDEX: Idxname

Note: The index is immutable and you must remove the rebuild if you want to change it.

9. Description: Creating view: Create VIEW viewname AS SELECT statement

Delete view: Drop View ViewName

10, Description: A few simple basic SQL statements

Select: SELECT * FROM table1 where range

Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)

Delete: Delete from table1 where range

Updated: Update table1 set field1=value1 where range

Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, check the information!

Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]

Total: SELECT Count * as TotalCount from table1

Sum: Select SUM (field1) as Sumvalue from table1

Average: Select AVG (field1) as Avgvalue from table1

Maximum: Select Max (field1) as MaxValue from table1

Min: select min (field1) as MinValue from table1

11. Description: Several advanced query operation words

A:union operator

The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

B:except operator

The EXCEPT operator derives a result table 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.

C:intersect operator

The INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.

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

12. Description: Use external connection

A, LEFT OUTER join:

Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.

Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

B:right outer join:

Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.

C:full outer join:

Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Second, SQL sub-query statements

1, single-row sub-query

Select Ename,deptno,sal

From EMP

where deptno= (select Deptno from dept where loc= ' NEW YORK ');

2, multi-row sub-query

SELECT Ename,job,sal

From EMP

where Deptno in (SELECT deptno from dept WHERE dname like ' A% ');

3, Dolez Query

SELECT Deptno,ename,job,sal

From EMP

WHERE (Deptno,sal) in (SELECT Deptno,max (SAL) from the EMP GROUP by Deptno);

4. Inline View Sub-query

(1) SELECT Ename,job,sal,rownum

From (SELECT ename,job,sal to EMP ORDER by Sal);

(2) SELECT Ename,job,sal,rownum

From (SELECT ename,job,sal to EMP ORDER by Sal)

WHERE rownum<=5;

5. Using subqueries in the HAVING clause

Select Deptno,job,avg (SAL) from EMP GROUP by Deptno,job has AVG (sal) > (SELECT sal from emp WHERE ename= ' MARTIN ');

6, internal connection left connection right connection example;

Select sys_user.user_id, Sys_user.user_code from Sys_user inner join Xzfw_banjie Onsys_user.user_id=xzfw_banjie.userid

Small example:

Select Top * from Sys_user where User_code not in (select User_code from Sys_user where User_code like '%yzj% ')

Select Top 2 * FROM (SELECT top 2 * from Td.users ORDER BY us_username Desc) the users order by us_username DESC

7. Delete the constraint statement:

ALTER TABLE dbo. Xzfw_sys_user Drop CONSTRAINT fk1772e1891324f678

8, record number query

Select COUNT (user_pass) from Sys_user

Select COUNT (*) from Sys_user where user_code!= ' admin '

9. Take values between ranges (between ... and. usage)

Select Sys_user.user_id,sys_user.user_name,xzfw_shoujian.caseid from Sys_user inner joins Xzfw_shoujian on Sys_ User.user_id=xzfw_shoujian.userid where user_id between 5 and 100

or select * from Sys_user where user_id<10 and user_id>1

10, three Table query example: (Three tables: User_details, Subject, score)

Select User_details. User_name,subject.subjectname,score.score from user_details inner join Scoreon user_details. user_id=score.user_id INNER join Subject on Score.subjectid=subject.subjectidwhere user_details. User_id=1

Examples of common queries:

SELECT * FROM dbo. User_details where user_name= ' Cheers Li ' and user_position= ' SQE '

SELECT * FROM dbo. User_dept

SELECT * FROM dbo. User_details

Select top, the FROM dbo. User_details INNER JOIN dbo. User_dept onuser_details. User_dept_id=dbo. User_dept. user_dept_id

INSERT INTO dbo. User_dept (User_dept_id,user_dept_name) VALUES (' qe_01 ', ' Software Quality Engineer ')

Update user_dept set user_dept_id= ' qe_02 ' where user_dept_name= ' quality Control '

Delete FROM dbo. user_dept where user_dept_id= ' qe_01 '

SELECT dbo. User_details. User_name,dbo. User_details. User_age,dbo. User_dept. User_dept_name,user_dept. user_dept_id fromdbo. User_details RIGHT JOIN dbo. User_dept onuser_details. User_dept_id=dbo. User_dept. user_dept_id

Select COUNT (user_name) from dbo. User_details where user_name= ' Cheers Li '

ALTER TABLE user_dept add Testcolumn Char

ALTER TABLE user_dept drop column Testcolumn

Select Top, the From (select Top, from dbo. User_details where user_dept_id= ' dev_01 ' order byuser_age desc) AA ORDER BY user_id DESC

SELECT * FROM dbo. User_details where User_name= (select MAX (user_name) fromdbo. User_details)

Third, to supplement the commonly used statements.

1. Select employees.employee_id,employees.first_name,employees.last_name,salary* (1+0.1) new_salary from Hr.employees;

2. Select Employee_id,first_name from hr.employees where first_name like ' B% ';

3. Select COUNT (*) from hr.employees where first_name like ' B% ';

4. Select Job_id,avg (Salary), sum (Salary), Max (Salary), COUNT (*) from Hr.employees GROUP by job_id;

Next, let's look at some nice SQL statements

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. Ordinal 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

Database Basic Theory collation:

To understand three paradigms in a popular way

A popular understanding of the three paradigms is of great benefit to database design. In the database design, in order to better apply the three paradigms, it is necessary to understand the three paradigms in a popular way (the popular understanding is sufficient understanding, not the most scientific and accurate understanding):

The first paradigm: 1NF is an atomic constraint on attributes, requiring attributes to be atomic and non-decomposed.

The second paradigm: 2NF is a unique constraint on records, requiring records to have a unique identity, that is, the uniqueness of the entity;

The third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from another field, it requires no redundancy in the field.

There is no redundant database design to do. However, a database without redundancy is not necessarily the best database, and sometimes in order to improve operational efficiency, it is necessary to lower the paradigm standard and properly retain redundant data. The practice is to adhere to the third paradigm when designing the conceptual data model, and to lower the standard of normalization into the design of the physical data model. Lowering the paradigm is adding fields, allowing redundancy.

The relationship between the base table and its fields should satisfy the third paradigm as much as possible. However, the design of the database that satisfies the third paradigm is often not the best design. In order to improve the efficiency of database operation, it is often necessary to reduce the standard of normalization: to increase redundancy appropriately and to achieve the purpose of space-changing time.

Example 2〗: There is a basic table for storing goods, as shown in table 1. The existence of the "Amount" field indicates that the design of the table does not satisfy the third paradigm, since "amount" can be obtained by multiplying the "unit price" by "quantity", stating that "amount" is a redundant field. However, increasing the "amount" of this redundant field can increase the speed of query statistics, which is the practice of space-changing time.

In Rose, there are two types of rules: data columns and computed columns. Columns such as amounts are referred to as computed columns, and columns such as unit price and quantity are referred to as data columns.

Table 1 table structure of the commodity table

Product name commodity model Unit Price quantity amount

TV 29 "2500 40 100,000

SQL Basics Review