# Go to repeating fields
#select distinct name,address from student
# column Operations
#select name, age+1 as age from student
# NULL operation
#select ifnull (address,0) + 1 as address from student
# string Addition
#select concat (address, ' str ') as address from student
SELECT * FROM dept;
SELECT * from EMP;
SELECT * from Stu;
SELECT * from emp where job = ' manager ';
SELECT * from emp where job! = ' manager ';
SELECT * from emp where job <> ' manager ';
Select Ename,job from emp where Sal > 20000;
SELECT * from emp where Sal < 10000;
SELECT * from emp where Sal between 10000 and 20000;
SELECT * from EMP where Mgr in (1004,1009);
SELECT * from emp where mgr = 1004 or mgr = 1009;
SELECT * FROM EMP where comm is null;
SELECT * FROM EMP where comm are NOT null;
SELECT * from emp where mgr = 1009 and sal > 20000;
# starts with Z and has 8 characters
SELECT * from emp where ename like ' z_______ ';
# Data starting with Z
SELECT * from emp where ename like ' z% ';
# The query name contains Z
SELECT * from emp where ename like '%z% ';
# Employee information sorted by salary from highest to lowest
Select Ename,sal from emp order by Sal Desc;
# Ascending: Rank from low to high
Select Ename,sal from emp order by SAL ASC;
# Default Collation ASC
Select Ename,sal from emp order by Sal;
Select Ename,sal,comm from emp ORDER by sal ASC, Comm DESC;
# Number of non-nullable records
Select COUNT (*) as Mgr from EMP;
SELECT count (Comm), COUNT (Mgr) from EMP;
Select sum (SAL) from EMP where Sal > 20000;
Select AVG (SAL) from EMP;
Select Max (SAL) max, min (sal) min from emp;
Select COUNT (*) as ' number of companies ', SUM (SAL) as ' maximum wage '
from EMP;
Select min (sal), job as ' post ' from the EMP group by job;
# Jobs with average salary greater than 20000 people less than 3
Select Job, COUNT (*) from the EMP group by job has count (*) < 3;
# first 10 rows of data in the EMP table
SELECT * from emp limit 0, 10;
# Top 5 Payroll in EMP Watch
SELECT * from emp ORDER BY sal desc limit 0, 5;
# EMP Watch Salary is 6 ~ 10
SELECT * from emp ORDER BY sal DESC limit 5, 5;
# Go to repeating fields
#select distinct name,address from student
# column Operations
#select name, age+1 as age from student
# NULL operation
#select ifnull (address,0) + 1 as address from student
# string Addition
#select concat (address, ' str ') as address from student
SELECT * FROM dept;
SELECT * from EMP;
SELECT * from Stu;
SELECT * from emp where job = ' manager ';
SELECT * from emp where job! = ' manager ';
SELECT * from emp where job <> ' manager ';
Select Ename,job from emp where Sal > 20000;
SELECT * from emp where Sal < 10000;
SELECT * from emp where Sal between 10000 and 20000;
SELECT * from EMP where Mgr in (1004,1009);
SELECT * from emp where mgr = 1004 or mgr = 1009;
SELECT * FROM EMP where comm is null;
SELECT * FROM EMP where comm are NOT null;
SELECT * from emp where mgr = 1009 and sal > 20000;
# starts with Z and has 8 characters
SELECT * from emp where ename like ' z_______ ';
# Data starting with Z
SELECT * from emp where ename like ' z% ';
# The query name contains Z
SELECT * from emp where ename like '%z% ';
# Employee information sorted by salary from highest to lowest
Select Ename,sal from emp order by Sal Desc;
# Ascending: Rank from low to high
Select Ename,sal from emp order by SAL ASC;
# Default Collation ASC
Select Ename,sal from emp order by Sal;
Select Ename,sal,comm from emp ORDER by sal ASC, Comm DESC;
# Number of non-nullable records
Select COUNT (*) as Mgr from EMP;
SELECT count (Comm), COUNT (Mgr) from EMP;
Select sum (SAL) from EMP where Sal > 20000;
Select AVG (SAL) from EMP;
Select Max (SAL) max, min (sal) min from emp;
Select COUNT (*) as ' number of companies ', SUM (SAL) as ' maximum wage '
from EMP;
Select min (sal), job as ' post ' from the EMP group by job;
# Jobs with average salary greater than 20000 people less than 3
Select Job, COUNT (*) from the EMP group by job has count (*) < 3;
# first 10 rows of data in the EMP table
SELECT * from emp limit 0, 10;
# Top 5 Payroll in EMP Watch
SELECT * from emp ORDER BY sal desc limit 0, 5;
# EMP Watch Salary is 6 ~ 10
SELECT * from emp ORDER BY sal DESC limit 5, 5;
MySQL Statement Code
