Shulou(Shulou.com)06/01 Report--

Accumulation of common statements in SQL:

1. SQL basic sentences

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 basic statement:

1. Description: create a database

Create DATABASE database-name

2. Description: delete the database

Drop database dbname

3. Description: backup sql server

-create a device for backing 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],..)

Create a new table based on an existing table:

A: create table tab_new like tab_old (create a new table using the old table)

B: create table tab_new as select col1,col2... From tab_old definition only

5. Description: delete the 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. The data type cannot be changed after the column is added in DB2, and the only thing that can 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 an index: create [unique] index idxname on tabname (col … (.)

Delete index: drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

9. Description: create 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)

Deleting: delete from table1 where ran

Updating: update table1 set field1=value1 where ran

Look up: select * from table1 where field1 like'% value1%'- the syntax of like is very exquisite, look up the information!

Sort: select * from table1 order by field1,field2 [desc]

Total: select count * as totalcount from table1

Summation: select sum (field1) as sumvalue from table1

Average: select avg (field1) as avgvalue from table1

Maximum: select max (field1) as maxvalue from table1

Minimum: 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, such as TABLE1 and TABLE2, and eliminating any duplicate rows in the table. When ALL is used with UNION (that is, UNION ALL), duplicate lines 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 lines are not eliminated.

C: INTERSECT operator

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

Note: several query result rows that use operands must be consistent.

12. Description: use external connections

A 、 left outer join:

Left outer join (left join): the result set includes the matching rows of the join table, as well as all rows of the left join table.

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B: right outer join:

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

C: full outer join:

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

2. SQL subquery statement

1. Single-row subquery

Select ename,deptno,sal

From emp

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

2. Multi-row subquery

SELECT ename,job,sal

FROM EMP

WHERE deptno in (SELECT deptno FROM dept WHERE dname LIKE'A%')

3. Multi-column subquery

SELECT deptno,ename,job,sal

FROM EMP

WHERE (deptno,sal) IN (SELECT deptno,MAX (sal) FROM EMP GROUP BY deptno)

4. Inline view subquery

(1) SELECT ename,job,sal,rownum

FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)

(2) SELECT ename,job,sal,rownum

FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)

WHERE rownum (SELECT sal FROM EMP WHERE ename='MARTIN')

6. an example of inner connection, left connection, right connection.

Select sys_user.user_id, sys_user.user_code from sys_user inner join XZFW_BANJIE onsys_user.user_id=XZFW_BANJIE.userid

Small examples:

Select top 10 * 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) users order by us_username desc

7. Delete constraint statement:

Alter table dbo.XZFW_SYS_USER drop CONSTRAINT FK1772E1891324F678

8. Query the number of records

Select count (user_pass) from sys_user

Select count (*) from sys_user where username codewords

9. Take a value between ranges (between. And.. Usage)

Select sys_user.user_id,sys_user.user_name,xzfw_shoujian.caseid from sys_user inner join xzfw_shoujian on sys_user.user_id=xzfw_shoujian.userid where user_id between 5 and 100

Or select * from sys_user where user_id1

10. Query examples with three tables: (three tables are: 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 3 * 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 3* from (select top 3* from dbo.USER_DETAILS where USER_DEPT_ID='DEV_01' order byUSER_AGE desc) aa order byUSER_ ID desc

Select * fromdbo.USER_DETAILS where USER_NAME= (select max (USER_NAME) fromdbo.USER_DETAILS)

Third, supplement common sentences.

1. Select employees.employee_id,employees.first_name,employees.last_name,salary* (1x 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

Secondly, let's take a look at some good sql statements.

1. Description: copy the table (only copy the structure, source table name: a new table name: B) (Access available)

Law 1: select * into b from a where 11

Method 2: select top 0 * into b from a

2. Description: copy table (copy data, source table name: a destination table name: B) (Access available)

Insert into b (a, b, c) select d from b

3. Description: copy of tables across databases (use absolute path for specific data) (Access is available)

Insert into b (a, b, c) select djiggy e from b in 'specific database' where condition

Example:.. from b in'"& Server.MapPath (". ") &"\ data.mdb "&" 'where.. "

4. Description: subquery (table name 1: a table name 2: B)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

Select a.title adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from table where table.title=a.title) b

6. Description: external join 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 OUT JOIN b ON a.a = B.C

7. Description: online view query (table name 1: a)

Select * from (Select a FROM c FROM a) T where t.a > 1

8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2

Select a dint bpenc, from table1 where a not between numeric 1 and numeric 2

9. Description: how to use in

Select * from table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6')

10. Description: two associated tables to delete information that has not been found in the secondary table in the primary table

Delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

11. Description: joint check of the four tables:

Select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.

12. Note: five minutes in advance of the schedule

SQL: select * from scheduling where datediff ('minute',f start time, getdate ()) > 5

13. Description: a sql statement completes database paging

Select top 10 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 10 * form table1 where range

15. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b)

Description: derives a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

(select a from tableA) except (select a from tableB) except (select a from tableC)

17. Description: 10 pieces of data are randomly selected

Select top 10 * from tablename order by newid ()

18. Description: randomly select records

Select newid ()

19. Description: delete duplicate records

Delete from tablename where id not in (select max (id) from tablename group by col1,col2,...)

Description: list all the table names in the database

Select name from sysobjects where type='U'

21. Description: list all the items in the table

Select name from syscolumns where id=object_id ('TableName')

22, description: type, vender, pcs fields are listed and arranged in type fields. Case can easily achieve multiple choices, similar to case in select.

Select type,sum (case vender when 'A'then pcs else 0 end), sum (case vender when 'C'then pcs else 0 end), sum (case vender when 'B'then pcs else 0 end) FROM tablename group by type

Display the results:

Type vender pcs

Computer A1

Computer A1

CD B 2

CD A 2

Mobile phone B3

Mobile phone C 3

23. Description: initialization table table1

TRUNCATE TABLE table1

24. Description: select records from 10 to 15

Select top 5 * from (select top 15 * from table order by id asc) table_ alias order by id desc

The basic theories of the database are arranged as follows:

A popular understanding of the three paradigms

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

The first normal form: 1NF is a constraint on the atomicity of attributes, which requires that attributes are atomic and can not be decomposed again.

The second paradigm: 2NF is the uniqueness constraint on the record, which requires the record to have a unique identity, that is, the uniqueness of the entity.

The third paradigm: 3NF is a constraint on field redundancy, that is, no field can be derived from other fields, and it requires that the field has no redundancy.

No redundant database design can do this. 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 standard of paradigm and retain redundant data properly. The specific approach is to follow the third normal form in the conceptual data model design, and the work of reducing the normal form standard is considered in the physical data model design. To lower the paradigm is to add fields, allowing redundancy.

The relationship between the basic table and its fields should satisfy the third paradigm as far as possible. However, database design that meets the third paradigm is often not the best design. In order to improve the operational efficiency of the database, it is often necessary to reduce the standard of paradigm: appropriately increase redundancy to achieve the purpose of exchanging space for 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 meet the third paradigm, because "amount" can be obtained by multiplying "unit price" by "quantity", indicating that "amount" is a redundant field. However, adding the redundant field "amount" can improve the speed of query statistics, which is the practice of exchanging space for time.

In Rose, there are two types of specified columns: data columns and calculated columns. Columns such as "amount" are called "calculated columns", while columns such as "unit price" and "quantity" are called "data columns".

Table 1 Table structure of merchandise table

Commodity name, commodity model, unit price, quantity amount

TV 29 inch 2500 40 100000

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.