Learn SQL together

I. Introduction

SQL statements, such as DOS and Unix commands, are countless. I plan to learn and review SQL statements in this article.

 

Ii. SQL language verb

Query select

Define create, drop, alter

Insert, update, and delete operations

Control Grant and revoke

 

Iii. SQL data definition statements

Create, delete, and modify

Database CREATE DATABASE DROP DATABASE

Table create table drop table ALTER TABLE

View create view drop View

Index create index drop index

 

4. Create, delete, and modify

1. Create Database database_name;

2. Delete the database drop database database_name;

3. Create Table table_name (ID int not null unique, name char (20 ));

4. Drop table table_name;

5. modify a table

To modify a table, you can add or modify attributes, but cannot delete attributes. You can only delete specified integrity constraints, such as C.

A: alter table table_name add class char (100 );

B: ALTER TABLE table_name modify class char (20 );

C: alter table table_name drop unique (ID );

6. Create an index

When creating an index, the default value is ascending. You can use DESC to specify the value as descending. The unique index does not allow the two rows to have the same value; the key values of the clustered index are in the same order as the physical order of the records of each row in the table; the order of key values of non-clustered indexes is inconsistent with the physical order of records of each row in the table.

A: create unique index index_name on table_name (id desc );

B: Create clustered index index_name on table_name (ID DESC, name, ASC );

C: Create nonclustered index index_name on table_name (id desc, name, ASC );

7. Delete the index drop index index_name;

8. Single Table query

Select * From table_name;

Select ID, age from table_name;

Select ID as A, age as B from table_name;

Select ID, 2010-age as B from table_name;

Select distinct age from table_name;

9. Common query Conditions

Comparison =, <,>, >=, <= ,! =, <> ,! <; Not + comparison operation of the front edge

The value range is between and, not between and.

Set in, not in

Match like, not like

Null is Noll, is not null

Multiple conditions and, or

Select distinct ID from table_name where age> 18 and not age> 60;

Select ID from table-name where age between 18 and 60;

Select ID from table-name where age not between 20 and 30;

Select ID from table-name where name in ('zs', 'LS ');

Select ID from table-name where name like 'z _ S % '; % matches 0 or multiple characters, and _ matches one character. If the literal meaning of _ is used, it is/n.

Select ID from table-name where name is null;

10. Sort the results

Select ID, age from table_name order by name, age DESC; records with the same name are sorted in ascending order by age.

11. Use the function set

Select count (*) from table_name;

Select count (distinct age) from table_name;

Select AVG (AGE) from table_name;

Select max (AGE) from table_name;

Select min (AGE) from table_name;

12. query result grouping

Select age, count (ID) from table_name group by age having count (*)> 5;

13. Connection Query

The front side is a query of a table, and the query of two or more tables is called a join query.

Select table_name1.id, table_name2.age from table_name1, table_name2 where table_name1.age = table_name2.age and table_name2.age <35;

14. nested Query

Select ID from table_name1 where table_name1.age in (select age from table_name2 where age> 20 );

Select ID from table_name1 where table_name1.name = (Select name from table_name2 where age = 60 );

In the preceding example, subqueries with comparison operators are used.

Subqueries with any or all are as follows:

> Any is greater than a certain

> All is greater than all

<Any is smaller

<All less than all

> = Any is greater than or equal to

> = All is greater than or equal to all

<= Any is less than or equal to

<= All is less than or equal to all

= Any is equal to

= All equals to all

! = Any (<> Any) is not equal to

! = All (<> All) is not equal to any

Select ID from table_name1 where table_name1.age <Any (select age from table_name2 where age <30 );

15. query with exists

Exists queries do not return any data, but only return true or false values.

Select ID from table_name1 where exitsts select age from table_name2 where age <30;

16. Set Query

Union

Except

Intersect

 

Select age from table_name1 Union select age from table_name2

Select age from table_name1 distinct T select age from table_name2/* MSSQL not supported */

Select age from table_name1 intersect select age from table_name2/* MSSQL not supported */

17. insert data

Insert into table_name values (10, 20, 30 );

Insert into table_name (ID, age) values (10, 20 );

Insert into table_name1 (ID, age) Select ID, age from table_name2;

18. modify data

Update table_name set age = age + 1

19. delete data

Delete from table_name where age <18;

20. Create a view

Create view view_name as select * From table_name where age> 20;

Create view view_name as select * From table_name where age> 20 with check option;

After the with check option is added, if the view is updated or inserted, the updated/inserted data must meet the constraints in the WHERE clause. Otherwise, an error is returned.

21. delete a view

Drop view view_name;

22. query view

Select * From view_name;

23. Update the view

Update view_name set age = age + 1 where age> 0;

24. Authorization

Grant insert on table table_name to user_name;

Grant update on Database database_name to user_name;

25. revoke permissions

Revoke update on table table_name to user_name;

Revoke insert on Database database_name to user_name;