SQL学习笔记day1

• What is SQL？

           SQL stands for Structured Query Language. A sequel is a set of commands. SQL is a set of commands that can be sent to the database, then the database is going to interpret those commands and excute them and perform tasks.

•  SQL is not case sensitive(大小写不敏感)
•  Retrieving Data Using the SELECT Clause   

         SELECT * FROM emp /* star is to query everything in that table*/

         SELECT job, ename FROM emp /* comma is to query multiple columns */ 

         notes: 

1. need to specify a single command at a time，每次选中并运行一个命令。
2. a command that ends with the semi-colon statement
3. separates one statement from another is the semi-colon. 分号隔开不同的命令
4. you can have multiple statements, but run one statement at a time。
5. can make the statement multiple lines, which is more readable

• get the unique values in one column of a table

         select distinct job from emp;

• WHERE, return the specific info where some cases satisfied.

        SELECT* FROM EMP WHERE job = 'MANAGER'

        /*if 'maneger', no data found，这里“=”后面的值需要注意大小写*

• AND

        SELECT * FROM emp

        WHERE job = 'MANAGER'

        AND sal = 2850

        AND ename = 'BLAKE'

        AND empno = 7698

• Operations

         =, !=, <=, >=, <, >

         SELECT * FROM EMP

         WHERE JOB != 'MANAGER'

         AND COMM > SAL

• OR

         SELECT * FROM EMP
         WHERE JOB = 'CLERK'
         OR JOB = 'SALESMAN

• IN / NOT IN == OR

         SELECT ENAME, HIREDATE

         FROM EMP

         WHERE DEPTNO = 20

         OR DEPTNO = 30

         SELECT ENAME, HIREDATE

         FROM EMP

         WHERE DEPTNO IN (20, 30)

• BETWEEN... AND...(boundary inclusive) NOT BETWEEN... AND...(boundary not inclusive)

         SELECT * FROM EMP

         WHERE HIREDATE BETWEEN '04/02/1981' AND '01/12/1983'

• IS NULL (is not null), no data, which is different from 0 value.

        SELECT * FROM EMP

        WHERE COMM is null