SQLSummary

SQL

Fast Reference

1. select: select columns

2. select distinct: remove redundant data in columns (reduce the number of rows)

3. limit: limit the number of results to be displayed

4. where: limit conditions

   1. like: select string with certain pattern (work with wildcard)

   2. in: select from several values

      1. eg

         where column in (value1, value2,...)

   3. between: select value/string/data between two values

      1. eg

         where column between value1 and value2

5. alias: set alias (used in select, where)

6. order by: show result in certain order

7. insert into: insert a row with certain columns

   1. eg

      insert into table_name (column1, column2, column3,...)
      values (value1, value2, value3,...)

      You do not need to name the columns if the row inserted contains them all.

8. update: update certain rows

   1. eg

      update table_name
      set column1=value1, column2=value2,...
      where <conditoins>

      You have to set conditions when using update.

9. delete: delete certains rows

   1. eg

      delete from table_name
      where <conditions>;

      You have to set conditions when using delete.

10. join: join two several tables

    1. eg

       select <columns>
       from table1_name 
       join table2_name
       on table1_name.key=table2.name.key;

11. union: union several table (with same number of columns, same type in each column and same order of columns)

    1. eg

       select <columns> from table1_name
       union <all>
       select <columns> from table2_name;

       Use all to allow redundant data in the result of union.

12. insert into select: copy data from a table and insert to another

    1. eg

       insert into table2_name <columns>
       select <columns>
       from table1_name

13. create table: create a new table

    1. eg

       create table table_name
       (
           column1 data_type(size) <constraint_name>,
           column2 data_type(size) <constraint_name>,
           ...
       )

    2. constraints: add constraint to give more rules in table

       1. Table

          not null	unique	primary key	foreign key	check	default
          no null in this column	each value in this column is nuique	set primary key (not null & unique)	set foreign key	each value compromise certain condition	set default value

       2. primary key: every table should have one and only one primary key.

          1. eg

             create table table_name
             (
                 column1 data_type(size),
                 column2 data_type(size),
                 ...
                 primary key (column1)
             )

       3. foreign key: foreign key is the primary key of another table

       4. check: check certain condition

          1. eg

             create table table_name
             (
                 column1 data_type(size),
                 column2 data_type(size),
                 ...
                 check <condition>
             )

             or

             create table table_name
             (
                 column1 data_type(size),
                 column2 data_type(size),
                 ...
                 constraint constraint_name check <conditions>
             )

14. index: create index

    1. eg

       create <unique> index index_name
       on table_name (column1, column2,...)

15. group by: group the results; it should work with aggregate function.

    1. eg

       select column1, aggregate_function(column_name)
       from table_name
       group by column1

16. having: aggregate function can not work with where and that is why we need having.

    1. the order of these commands is where -> group by -> having.

17. exist: return true is the result exists