SELECT * FROM withdraws_cash WHERE order_state = 1 AND date BETWEEN '2016-09-01' AND '2016-09-03' AND userName IN ('桃2','黃');

SELECT * FROM l_withdraws_cash WHERE userName LIKE '%桃%' AND error_msg IS NOT NULL;

SELECT 1 num, 'abc' str UNION SELECT 9 num,'xyz' str;


SELECT CONCAT(userName,' get money ',ROUND(money/100.0,2),' at: ',date) FROM withdraws_cash WHERE userName LIKE '%桃%' AND error_msg IS NOT NULL;

SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 0 DAY);

SELECT order_state,COUNT(order_state) how_many, MAX(money) max_money,MIN(money) min_money  
FROM l_withdraws_cash GROUP BY order_state;

SELECT EXTRACT(YEAR_MONTH FROM date) month, COUNT(*) how_many FROM withdraws_cash GROUP BY EXTRACT(YEAR_MONTH FROM date);

SELECT emp_id emp FROM employee;

SELECT DISTINCT cust_id FROM account; //去重
//子查詢
SELECT e.emp_id,e.fname,e.lname FROM 
(SELECT emp_id,fname,lname,start_date,title FROM employee) e;

//創(chuàng)建視圖
CREATE VIEW employee_vw AS 
SELECT emp_id,fname,lname,YEAR(start_date) start_year
FROM employee;
//沖視圖里查詢
SELECT emp_id,start_year FROM employee_vw;
//表連接
SELECT employee.emp_id,employee.fname,employee.lname,department.name dept_name
FROM employee INNER JOIN department ON employee.dept_id = department.dept_id;

SELECT emp_id,fname,lname,start_date,title FROM employee WHERE title='Head Teller'
OR start_date > '2006-01-01';
//分組查詢
SELECT d.name, COUNT(e.emp_id) num_employees FROM department d INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.name HAVING COUNT(e.emp_id)>2;

SELECT account_id,product_cd,open_date,avail_balance FROM account ORDER BY avail_balance DESC;

SELECT cust_id,cust_type_cd,city,state,fed_id FROM customer ORDER BY RIGHT(fed_id,3);


SELECT emp_id,fname,lname,start_date FROM employee
WHERE start_date BETWEEN '2005-01-01' AND '2007-01-01';
//嵌套查詢
SELECT account_id,product_cd,cust_id,avail_balance FROM account
WHERE product_cd IN(SELECT product_cd FROM product WHERE product_type_cd = 'ACCOUNT');

SELECT account_id,product_cd,cust_id,avail_balance FROM account
WHERE product_cd NOT IN ('CHK','SAV','CD','MM');

SELECT emp_id,fname,lname FROM employee WHERE lname REGEXP '^[FG]';
//不是NULL的時候
SELECT emp_id,fname,lname,superior_emp_id FROM employee
WHERE superior_emp_id IS NULL;

//查找employee表中fname和lname以及department中name字段 同時
規(guī)定employee表和department表中的dept_id相等
SELECT e.fname,e.lname,d.name FROM employee e INNER JOIN
department d ON e.dept_id = d.dept_id


//鏈接多個表
SELECT a.account_id,a.cust_id,a.open_date,a.product_cd FROM
account a INNER JOIN employee e ON a.open_emp_id = e.emp_id
INNER JOIN branch b ON e.assigned_branch_id = b.branch_id
WHERE e.start_date<'2007-01-01' AND (e.title='Teller' OR e.title = 'Head Teller')
AND b.name = 'Woburn Branch';

//

//錯誤的語句 不能再where中使用聚集函數。在評估where字句時分組還沒有創(chuàng)建
SELECT open_emp_id, COUNT(*) how_many FROM account WHERE COUNT(*)>4 GROUP BY open_branch_id;

//
SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_branch_id HAVING COUNT(*)>4;