hmyqwe的博客

SQL 语言分为四大类：数据查询语言 DQL，数据操纵语言 DML，数据定义语言 DDL，数据控制语言 DCL。数据查询语言 DQL(Data Query Language)基本结构是由 SELECT 子句，FROM 子句，WHERE 子句组成的查询块：SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>数据操纵语言 DML(Data Manipulation Language)主要有三种形式：1）插入：INSERT2）更新

SQL 语句的执行顺序以下方语句作为示例：(7) SELECT(8) DISTINCT <select_list>(1) FROM <left_table>(3) <join_type> JOIN <right_table>(2) ON <join_condition>(4) WHERE <where_condition>(5) GROUP BY <group_by_list>(.

SELECT id, job, score, dn rank FROM ( SELECT id, job, score, rank () over ( PARTITION BY job ORDER BY score ) rn,--正序位置 rank () over ( PARTITION BY job ORDER BY score DESC ) dn,--倒序位置 count() over ( PARTITION BY job ) cnt --参与排序总数 FROM ..

SELECT job,CASE WHEN total % 2 = 0 THEN total / 2 ELSE ( total + 1 )/ 2 END AS `start`,CASE WHEN total % 2 = 0 THEN total / 2+1 ELSE ( total + 1 )/ 2 END AS `end` FROM ( SELECT job, COUNT( 1 ) AS total FROM grade GROUP BY job ) t

SELECT g.id, l.NAME, g.score FROM ( SELECT *, dense_rank () over ( PARTITION BY language_id ORDER BY score DESC ) AS rank FROM grade ) g, LANGUAGE l WHERE g.language_id = l.id AND g.rank <= 2 ORDER BY l.NAME ASC, g.score DESC, g.id ASC..

每个岗位的平均分sql代码如下:(select job,round(avg(score*1.000),3) as avg from gradegroup by job) 可以上面查询出来的数据看成一个新表，用现在的表去联立这个新表，获得当job相同时，现在的表大于新表的分数，就可以得到各个岗位大于平均数的信息，最后按照id升序select grade.* from grade join (select job,round(avg(score*1.000),3) as avg from...

SELECT job, ROUND( AVG( score * 1.0000 ), 3 ) AS `avg` FROM grade GROUP BY job ORDER BY `avg` DESC

分析: 简单求滚动sumSELECT u.NAME AS u_n, c.NAME AS c_n, pn.date AS date, sum( pn.number ) over ( PARTITION BY pn.user_id ORDER BY pn.date ) AS ps_num FROM passing_number pn INNER JOIN USER u ON pn.user_id = u.id INNER JOIN login l ON pn.user_id = l....

SELECT a.date, ROUND( COUNT( b.user_id ) * 1.0 / COUNT( a.user_id ), 3 ) AS p FROM ( SELECT user_id, MIN( date ) AS date FROM login GROUP BY user_id ) a LEFT JOIN login b ON a.user_id = b.user_id AND b.date = date( a.date, '+1 day' ) GROUP BY a...

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。语法<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)<窗口函数>的位置，可以放以下两种函数：专用窗口函数，比如rank, dense_rank, row_number等聚合函数，如sum. avg, count, max, min等使用专用窗口

首先得到每个日期里面，用户登录的数目select l1.date,count(distinct l1.user_id)from login l1group by l1.date再加一个where判断条件就能从这每个日期里面，用户登录的数目取出哪些是新用户select l1.date,count(distinct l1.user_id)from login l1where l1.date =(select min(date) from login where user_id=l1...

思路:（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率总用户计算如下：select count(distinct user_id) from login找到每个用户第一天登陆的日子，和前面找最近登录的日子差不多，一个是max，一个是min,如下:select user_id,min(date) from login group by user_id找到所有的在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期,如下:selec..

SELECT USER .NAME AS u_n, client.NAME AS c_n, login.date FROM login JOIN USER ON login.user_id = USER.id JOIN client ON login.client_id = client.id WHERE ( login.user_id, login.date ) IN ( SELECT user_id, max( date ) FROM login GROUP BY login....

SELECT max( date ) AS d FROM login GROUP BY user_id ORDER BY user_id

SELECT e.date, round( sum( CASE e.type WHEN 'no_completed' THEN 1 ELSE 0 END )* 1.0 / count( e.type ), 3 ) AS p FROM email e WHERE send_id IN ( SELECT id FROM USER WHERE is_blacklist = 0 ) AND receive_id IN ( SELECT id FROM USER WHERE is_blackli...

SELECT p.id, p.NAME, t.content FROM person AS p LEFT JOIN task AS t ON p.id = t.person_id ORDER BY p.id

SELECT p1.id, p1.number, count( DISTINCT p2.number ) rank FROM passing_number p1, passing_number p2 WHERE p1.number <= p2.number GROUP BY p1.id ORDER BY rank ASC, p1.id ASC或者SELECT a.id, a.number, ( SELECT count( DISTINCT b.number ) ..

SELECT number FROM grade GROUP BY number HAVING COUNT( number ) >= 3

SELECT e1.first_name FROM employees e1 WHERE ( SELECT count(*) FROM employees e2 WHERE e1.first_name >= e2.first_name )% 2 = 1

SELECT a.emp_no, a.salary, sum( b.salary ) FROM salaries AS a, salaries AS b WHERE b.emp_no <= a.emp_no AND a.to_date = '9999-01-01' AND b.to_date = '9999-01-01' GROUP BY a.emp_no

CASE具有两种格式。简单CASE函数和CASE搜索函数。简单CASE函数CASE sexWHEN '1' THEN '男'WHEN '2' THEN '女'ELSE '其他' ENDCase搜索函数CASE WHEN sex = '1' THEN '男'WHEN sex = '2' THEN '女'ELSE '其他' END或者CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'WHEN col_1 IN ('a') THEN '

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, ( CASE b.btype WHEN 1 THEN s.salary * 0.1 WHEN 2 THEN s.salary * 0.2 ELSE s.salary * 0.3 END ) AS bonus FROM employees AS e INNER JOIN emp_bonus AS b ON.

SELECT * FROM employees WHERE emp_no IN ( SELECT emp_no FROM emp_v)

EXISTS 和 IN在主表和从表都对关联的列建立索引的前提下：当主表比从表大时，IN查询的效率较高；当从表比主表大时，EXISTS查询的效率较高；举例如下：A表小，B表大；select * from A where cc in (select cc from B)效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)效率高，用到了B表上cc列的索引；反过来select .

SELECT * FROM employees WHERE NOT EXISTS ( SELECT emp_no FROM dept_emp WHERE employees.emp_no = dept_emp.emp_no)或者 not inSELECT * FROM employees WHERE emp_no NOT IN ( SELECT emp_no FROM dept_emp) 拓展:EXISTS 和 IN在主表和从表都对关联的.

SELECT em.emp_no, de.dept_no, eb.btype, eb.received FROM employees AS em INNER JOIN dept_emp AS de ON em.emp_no = de.emp_no LEFT JOIN emp_bonus AS eb ON de.emp_no = eb.emp_no

substr(X,Y,Z) 或 substr(X,Y) 函数;X是要截取的字符串;Y是字符串的起始位置（注意第一个字符的位置为1，不为0）,取值范围是±(1~length(X))，当Y等于length(X)时，截取最后一个字符；当Y等于负整数-n时，从倒数第n个字符处截取;Z是要截取字符串的长度，取值范围是正整数;若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，截取到字符串末尾为止;...

聚合函数group_concat(X,split)，X表示需要连接的列名，split表示连接的分隔符，默认为逗号；group_concat()聚合函数需要结合group by一起来使用，通过group by 来指定字段进行分组；

LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回;在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0）...

SELECT * FROM employees LIMIT 5 OFFSET 5或者SELECT * FROM employees LIMIT 5,5拓展:LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回;在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0）...

SELECT AVG( salary ) AS avg_salary FROM salaries WHERE to_date = '9999-01-01' AND salary NOT IN ( SELECT MAX( salary ) FROM salaries WHERE to_date = '9999-01-01' ) AND salary NOT IN ( SELECT MIN( salary ) FROM salaries WHERE to_date = '9.

SELECT dept_no, group_concat( emp_no ) AS employees FROM dept_emp GROUP BY dept_no或者SELECT dept_no, group_concat( emp_no, ',') AS employeesFROM dept_emp GROUP BY dept_no拓展:聚合函数group_concat(X,split)，X表示需要连接的列名，split表示连接的分隔符，默认为逗号；group_c.

SELECT first_name FROM employees ORDER BY substr( first_name, length( first_name )- 1 )拓展:substr(X,Y,Z) 或 substr(X,Y) 函数;X是要截取的字符串;Y是字符串的起始位置（注意第一个字符的位置为1，不为0）,取值范围是±(1~length(X))，当Y等于length(X)时，截取最后一个字符；当Y等于负整数-n时，从倒数第n个字符处截取;Z是要截取字符串的长度，取值范围是.

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

SELECT last_name || "'" || first_name FROM employees注意:MySQL下写为:SELECT concat( last_name, "‘", first_name ) AS NAME FROM employees

SELECT "select count(*) from " || NAME || ";" AS cnts FROM sqlite_master WHERE type = 'table'**注意:**MySQL中如下:SELECT concat( "select count(*) from ", " ", table_name, ";" ) AS cnts FROM ( SELECT table_name FROM information_schema.TABLES ) AS new.

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN ( SELECT emp_no FROM emp_bonus ) AND to_date = '9999-01-01'

DROP TABLE audit;CREATE TABLE audit ( EMP_no INT NOT NULL, create_date datetime NOT NULL, FOREIGN KEY ( EMP_no ) REFERENCES employees_test ( ID ) );或DROP TABLE audit;CREATE TABLE audit( EMP_no INT NOT NULL REFERENCES employees_test(ID.

修改表名语法:ALTER TABLE 旧表名 RENAME TO 新表名增加列语法:ALTER TABLE 表名 ADD COLUMN 列名 数据类型

RENAME TABLE titles_test TO titles_2017或者ALTER TABLE titles_test RENAME TO titles_2017