SQL常用语句

<pre>

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;

//创建视图
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字段 同时
规定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字句时分组还没有创建
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;

</pre>