[武汉|荣耀师兄]JAVA-甘乐|2018年01月12日的日报-技能树.IT修真院

发表于： 2018-01-12 22:25:59

1 745

一.今日完成

1.梳理SQL语句学习子查询和多表内连接以及索引管理,如下:

#时间格式化

SELECT date_format(from_unixtime(create_at/1000),'%Y-%m-%d') as date, count(id) as count FROM student GROUP BY date_format(from_unixtime(create_at/1000),'%Y-%m-%d');

#视图

CREATE VIEW student_vw AS SELECT id, name, category, oath, tutor, crate_at FROM student;

SELECT name, category, oath,tutor FROM student_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 e.emp_id,e.fname, e.lname, d.name dept_name FROM employee e INNER JOIN department d ON e.dept_id=d.dept_id;

#WHERE子句

SELECT emp_id, fname, lname, start_date, title FROM employee WHERE (title = 'Head Teller' AND start_date > '2006-01-01') OR (title = 'Teller' AND start_date > '2007-01-01');

#group by 和having 子句

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 cust_id, cust_type_cd, city, state, fed_id FROM customer ORDER BY RIGHT(fed_id, 3);

#根据数字占位符排序

SELECT emp_id, title, start_date, fname, lname FROM employee ORDER BY 2, 5;

#不等条件

<> !=

#between操作符

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 ('CHK', 'SAV', 'CD', 'MM');

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

#匹配条件

# _ 正好1个字符

# % 任意数目的字符(包括0)

SELECT lname FROM employee WHERE lname = LIKE '_a%e%';

#正则表达式

SELECT emp_id, fname, lname FROM employee WHERE lname REGEXP '^[FG]' ;

#判空

SELECT emp_id, fname, lname, superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL;

#笛卡儿积

SELECT e.fname, e.lname, d.name FROM employee e JOIN department d;

#内连接

#on子句

SELECT e.fname, e.lname, d.name FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id;

#using子句

SELECT e.fname, e.lname, d.name FROM employee e INNER JOIN department d USING (dept_id);

#符合SQL92版本的ANSI SQL标准

#连接3个或者更多表

SELECT a.account_id, c.fed_id, e.fname, e.lname

FROM account a INNER JOIN customer c

ON a.cust_id = c.cust_id

INNER JOIN employee e

ON a.open_emp_id = e.emp_id

WHERE c.cust_type_cd = 'B';

#范围连接

SELECT e.emp_id, e.fname, e.lname, e.start_date

FROM employee e INNER JOIN product p

ON e.start_date >= p.date_offered

AND e.start_date <=p.date_retired

WHERE p.name = 'no-fee checking';

#不等连接

SELECT e1.fname, e1.lname, 'VS' vs , e2.fname, e2.lname

FROM employee e1 INNER JOIN employee e2

ON e1.emp_id < e2.emp_id

WHERE e1.title = 'Teller' AND e2.title = 'Teller';

#返回日期的时间函数

SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();

SELECT DATE_ADD(CURRENT_DATE(), interval 5 DAY);

SELECT LAST_DAY('2018-01-12');

SELECT CURRENT_TIMESTAMP() current_est, CONVERT_TZ(CURRENT_TIMESTAMP(), 'US/Eastern', 'UTC') current_utc;

SELECT DAYNAME('2018-01-12');

SELECT EXTRACT(YEAR FROM ' 2018-01-12 20:10:58');

#返回数字的时间函数

SELECT DATEDIFF('2018-01-12', '1993-03-21');

SELECT CAST('1456328', AS SIGNED INTEGER);

#分组

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

#处理null

count(*) count(id)的区别

#子查询

#标量子查询

SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE account_id = (SELECT MAX(account_id) FROM account);

#多行单列子查询

SELECT emp_id,fname, lname, title FROM employee WHERE emp_id <> ALL (SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL);

SELECT account_id, cust_id, product_cd, avail_balance FROM account WHERE avail_balance > ANY (SELECT a.avail_balance FROM account a INNER JOIN individual i ON a.cust_id = i.cust_id WHERE i.fname = 'Frank' AND i.lname = 'Tucker');

#多列子查询

SELECT account_id, product_cd, cust_id, FROM account WHERE open_branch_id = (SELECT branch_id FROM branch WHERE name = 'Woburn Branch') AND open_emp_id IN (SELECT emp_id FROM employee WHERE title = 'Teller' OR title = 'Head Teller');

#关联子查询

SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c WHERE 2 = (SELECT COUNT(*) FROM account a WHERE a.cust_id = c.cust_id);

#exists运算符

SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date = '2008-09-22');

#外连接

#左外连接

SELECT c.cust_id, b.name FROM customer c LEFT OUTER JOIN bussiness b ON c.cust_id = b.cust_id;

#右外连接

SELECT c.cust_id, b.name FROM customer c RIGHT OUTER JOIN bussiness b ON c.cust_id = b.cust_id;

#三路外连接

SELECT a.account_id, a.product_cd, CONCAT(i.fname, ' ', i.lname) person_name, b.name bussiness_name FROM account a LEFT OUTER JOIN individual i ON a.cust_id = i.cust_id LEFT OUTER JOIN bussiness b ON a.cust_id = b.cust_id;

#自外连接

SELECT e.fname, e.lname e.mgr.fname mgr_fname, e_mgr.lname mgr_lname FROM employee e INNER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;

#创建索引

ALTER TABLE department ADD INDEX dept_name_idx (name);

#查看索引

SHOW INDEX FROM department \G;

#删除索引

ALTER TABLE student DROP INDEX dept_name_idx;

#limit子句

SELECT open_branch_id, COUNT(*) how_many FROM account GROUP BY open_emp_id LIMIT 3;

SELECT open_branch_id, COUNT(*) how_many FROM account GROUP BY open_emp_id LIMIT 2, 1;

#into outfile子句