`
trydofor
  • 浏览: 149951 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

014.科普.有生产力的Sql语句

阅读更多


# 014.科普.有生产力的Sql语句

不等不靠,有囧有料。终日乾乾,或跃在渊。 

如果,我们做足了一些细节,敲出不够烂的代码, 
那么,不少企业就会倒闭,信息化建设就会倒退。

如果,我们做不足一些细节,依赖于流水线。 
那么,高效生产的同时,我们会退化为码工。

@史荣久 / 2015-03-27 / CC-BY-SA-3.0 

## 观看视频

  * [优酷视频(被转720P)](http://v.youku.com/v_show/id_XOTIwODUyNDA4.html)
  * [腾讯视频(被转720P)](http://v.qq.com/page/g/u/k/g0150g2ftuk.html)
  * [百度网盘(原始1080P)](http://pan.baidu.com/share/link?shareid=3935315343&uk=1380913564&fid=395714801385233)

## 内容摘要

  * 安迪-比尔定律(Andy and Bill’s Law)
  * 依赖于框架流水线,我们会从手艺人退化为流水线上的码工
  * 解释和部分解决Mysql的order-by-limit巨慢
  * 分页问题,引申出的sublist,substring
  * 一条语句,显示学生所选的课程:GROUP_CONCAT
  * 各GROUP的TOP-N问题(分组内,各取前N个)
  * 自增主键是个坑,尽量避免(尤其分布式)
  * 大量插入(bulk insert),VALUES
  * 插入忽略:INSERT IGNORE
  * 插入更新:ON DUPLICATE KEY UPDATE(有bug变的特性)
  * 替换插入:REPLACE INTO
  * 大量删除:DELETE & TRUNCATE(索引,事务,自增)
  * 多表联合删除,子查询删除,快速删除
  * 关联更新:单个字段,多个字段
  * 执行计划:explain,其他优化
  * 建立索引:根据sql和explain建立必要的索引
  * JDBC: PreparedStatement(DML) vs Statement(DDL)
  * JDBC: addBatch, executeBatch 的性能
  * JDBC: allowMultiQueries,maxAllowedPacket 安全和速度

## 参考资源

  * [安迪-比尔定律](http://www.baike.com/wiki/安迪-比尔定律)
  * [解释:mysql-order-by-limit](http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/)
  * [解释:late-row-lookups](http://explainextended.com/2011/02/11/late-row-lookups-innodb)
  * [subList OOM,注意jdk版本](http://skydream.iteye.com/blog/1672745)
  * [Mysql-5.5 性能优化](http://dev.mysql.com/doc/refman/5.5/en/statement-optimization.html)

  * [Mysql-5.5 DML:增(insert)](http://dev.mysql.com/doc/refman/5.5/en/insert.html)
  * [Mysql-5.5 DML:删(delete)](http://dev.mysql.com/doc/refman/5.5/en/delete.html)
  * [Mysql-5.5 DML:改(update)](http://dev.mysql.com/doc/refman/5.5/en/update.html)
  * [Mysql-5.5 DML:查(select)](http://dev.mysql.com/doc/refman/5.5/en/select.html)

  * [Mysql-5.5 子查询(subqueries)](http://dev.mysql.com/doc/refman/5.5/en/subqueries.html)
  * [Mysql-5.5 插入或替换(replace)](http://dev.mysql.com/doc/refman/5.5/en/replace.html)
  * [Mysql-5.5 重复时更新(duplicate)](http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html)
  * [Mysql-5.5 聚集:列变行](http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html)
  * [Mysql-5.5 聚集:合计](http://dev.mysql.com/doc/refman/5.5/en/group-by-modifiers.html)

  * [Statement vs PreparedStatement](http://stackoverflow.com/questions/3271249)
  * [Values vs JDBC批量预处理](http://brian.pontarelli.com/2011/06/21/jdbc-batch-vs-multi-row-inserts)
  * [JDBC-URL 能用到的参数](http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html)

## 欢迎围观

if (you.accept(MoilionCircle.SPIRIT)) {
    if(you.haveADL()){
        MoilionCircle we = you.search(MoilionCircle.SLOGAN);
        we.welcome(you);
    }
    if(you.share(this)){
        We.thank(you);
        We.mayFind7Moilion();
    }
}


## 测试SQL

select version();
-- 5.5.41-0ubuntu0.14.04.1

-- 建库
DROP DATABASE IF EXISTS `moilioincircle_r014`;
CREATE DATABASE `moilioincircle_r014` 
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE moilioincircle_r014;

-- 建表
DROP TABLE IF EXISTS `STUDENT`;
CREATE TABLE `STUDENT` (
    `ID` int(11) NOT NULL,
    `NAME` varchar(45) NOT NULL,
    `GENDER` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`ID`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `COURSE`;
CREATE TABLE `COURSE` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(45) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `STUDENT_COURSE_MAP`;
CREATE TABLE `STUDENT_COURSE_MAP` (
  `STUDENT_ID` int(11) NOT NULL,
  `COURSE_ID` int(11) NOT NULL,
  PRIMARY KEY (`STUDENT_ID`,`COURSE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `EXAM`;
CREATE TABLE `EXAM` (
  `STUDENT_ID` int(11) NOT NULL,
  `COURSE_ID` int(11) NOT NULL,
  `SCORE` int(11) NOT NULL,
  PRIMARY KEY (`STUDENT_ID`,`COURSE_ID`),
  KEY `IDX_SCORE` (`SCORE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `AUTOINCR`;
CREATE TABLE `AUTOINCR` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` varchar(45),
    PRIMARY KEY (`ID`)
)  AUTO_INCREMENT = 100
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 数据
INSERT INTO STUDENT (ID,NAME,GENDER)
VALUES
(1,'学生1',2),
(2,'学生2',1),
(3,'学生3',NULL),
(4,'学生4',DEFAULT);

INSERT INTO COURSE (ID,NAME) 
VALUES
(1,'课程1'),
(2,'课程2'),
(3,'课程3'),
(4,'课程4');

INSERT INTO STUDENT_COURSE_MAP (STUDENT_ID,COURSE_ID) 
VALUES
(1,1),
(2,1),(2,2),
(3,1),(3,2),(3,3),
(4,1),(4,2),(4,3),(4,4);

INSERT INTO EXAM (STUDENT_ID,COURSE_ID,SCORE) 
VALUES
(1,1,90),(1,2,70),(1,3,85),(1,4,80),
(2,1,70),(2,2,85),(2,3,75),(2,4,80),
(3,1,75),(3,2,85),(3,3,85),(3,4,65),
(4,1,60),(4,2,75),(4,3,80),(4,4,85);

INSERT INTO AUTOINCR (ID,NAME) 
VALUES
(1,'自增1'),
(DEFAULT,'自增#1'),
(5,'自增5'),
(DEFAULT,'自增#2');

-- 查 -----------------

-- 学生都选了什么课(把列换成行显示)
-- set group_concat_max_len=10000;
SELECT 
    S.NAME,
    GROUP_CONCAT(C.NAME ORDER BY C.NAME ) AS COURSES
FROM 
    STUDENT AS S,
    COURSE AS C,
    STUDENT_COURSE_MAP AS M
WHERE 
    S.ID = M.STUDENT_ID
    AND C.ID = M.COURSE_ID
GROUP BY S.NAME
HAVING COUNT(C.NAME)>1;

-- 把各科考试,前三名取出来。
-- MYSQL 没有 ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)
SELECT 
    C.NAME AS COURSE, 
    S.NAME AS STUDENT, 
    E.SCORE 
FROM 
    EXAM AS E ,
    STUDENT AS S,
    COURSE AS C
WHERE 
    (SELECT COUNT(*) FROM EXAM AS B 
        WHERE B.COURSE_ID = E.COURSE_ID 
        AND B.SCORE >= E.SCORE
    ) <= 3 
    AND E.STUDENT_ID = S.ID
    AND E.COURSE_ID = C.ID
ORDER BY COURSE ASC, SCORE DESC;

SELECT * FROM STUDENT 
WHERE (ID,NAME) IN ((1,'学生1'),(2,'学生2'));

-- ------- 增 ----------
SELECT * FROM COURSE;

INSERT IGNORE INTO COURSE (ID,NAME) 
VALUES 
(1,'课程1'),
(11,'课程1'),
(5,'课程5'),
(6,'课程6');

REPLACE INTO COURSE (ID,NAME) 
VALUES 
(5,'课程55'),
(66,'课程6'),
(7,'课程7');

INSERT INTO COURSE (ID,NAME) 
VALUES (5,'课程5'),(6,'课程6'),(8,'课程6')
ON DUPLICATE KEY UPDATE
    NAME=VALUES(NAME),ID=VALUES(ID);

INSERT INTO COURSE (ID,NAME) 
VALUES (5,'课程5'),(6,'课程6'),(7,'课程7')
ON DUPLICATE KEY UPDATE
    NAME=VALUES(NAME),
    ID=(CASE WHEN NAME = VALUES(NAME)
        THEN VALUES(ID)
        ELSE ID
        END);

-- 删
CREATE TABLE AUTOINCR_1 LIKE AUTOINCR; -- 表结构相同
INSERT INTO AUTOINCR_1 SELECT * FROM AUTOINCR;
DESCRIBE AUTOINCR_1;

CREATE TABLE AUTOINCR_2 SELECT * FROM AUTOINCR WHERE ID <10; -- 不相同
DESCRIBE AUTOINCR_2;

SELECT * FROM AUTOINCR_1;
SELECT * FROM AUTOINCR_2;

DELETE FROM AUTOINCR_1;
INSERT INTO AUTOINCR_1 (NAME) VALUES ('课程1');
TRUNCATE TABLE AUTOINCR_1;

DELETE T1,T2 
FROM AUTOINCR_1 AS T1,AUTOINCR_2 AS T2
WHERE T1.ID = T2.ID;

DELETE QUICK FROM AUTOINCR_2;
OPTIMIZE TABLE AUTOINCR_2;

-- 改 ------
INSERT INTO AUTOINCR_1 SELECT ID,NULL FROM AUTOINCR;
INSERT INTO AUTOINCR_2 SELECT * FROM AUTOINCR WHERE ID <10;

-- 更新单个字段
UPDATE AUTOINCR_2 AS T2, AUTOINCR_1 AS T1
SET T1.NAME = T2.NAME 
WHERE T2.ID = T1.ID;

UPDATE AUTOINCR_1 SET NAME=NULL;
UPDATE AUTOINCR_1 AS T1
SET T1.NAME = (SELECT T2.NAME FROM AUTOINCR_2 AS T2 WHERE T2.ID = T1.ID);

CREATE TABLE STUDENT_1 LIKE STUDENT;
INSERT INTO STUDENT_1 SELECT ID,CONCAT(NAME,'#'),0 FROM STUDENT;

SELECT * FROM STUDENT;
SELECT * FROM STUDENT_1;

-- 更新多个字段
UPDATE STUDENT_1 AS T1
INNER JOIN (SELECT T.ID, T.NAME,  T.GENDER FROM STUDENT AS T) AS T2 
ON T2.ID = T1.ID
SET T1.NAME = T2.NAME ,T1.GENDER = T2.GENDER;


题图:Intel Corp. President Andy Grove, right, shakes hands with Microsoft Chairman Bill Gates at a meeting in Burlingame, Ca., on Monday Nov. 9, 1992, as they team up to offer a new product of videos on personal computers.。
原文:http://www.moilioncircle.com/release/014.know.productive-crud-mysql.html 
0
0
分享到:
评论

相关推荐

    sqljdbc41.jar

    在了解sqljdbc41.jar之前,我们先科普一下JDBC驱动的四种类型: 1. 类型1:纯Java的Net Driver(JDBC-ODBC桥),依赖于本地ODBC驱动。 2. 类型2:部分Java,部分本地代码的驱动,通常用于访问数据库的API不是完全...

    sql注入讲解ppt.pptx

    SQL 注入是指 web 应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在 web 应用程序中事先定义好的查询语句的结尾上添加额外的 SQL 语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库...

    mybatis 动态sql及参数传递

    在实际开发过程中,我们往往需要编写复杂的SQL语句,拼接稍有不注意就会导致错误,Mybatis给开发者提供了动态SQL,大大降低了拼接SQL导致的错误。 动态标签 if标签 if标签通常用那个胡where语句,update语句,insert...

    一种基于编码习惯的SQL语句抄袭检测算法.pdf

    而朴素贝叶斯则是在检测过程中应用到的数学统计方法,它与SQL结合,使得算法能够对短小的SQL语句进行有效的抄袭判断。 在文献研究方面,本文著录格式符合学术规范,引用了相应的文献和数据,并且在文末列出了文献的...

    sql21自学通.pdf

    - 在数据分析和数据科学领域,SQL用于提取和分析大量数据。 - 在系统管理和软件开发中,SQL用于维护和优化数据库性能。 #### 二、SELECT语句的使用 - **一般语法规则:** - SELECT * FROM table_name; - ...

    新质生产力赋能数字农业农村解决方案.pptx

    ### 新质生产力赋能数字农业农村解决方案 #### 一、引言 随着全球数字化浪潮的兴起,农业农村领域也面临着前所未有的发展机遇。传统农业农村存在着诸多问题,如信息不对称、生产效率低、资源浪费等,这些问题严重...

    卢江-新质生产力发展水平论文重现数据包

    卢江,郭子昂,王煜萍.新质生产力发展水平、区域差异与...产业数字生产力以网络普及率、软件服务、数字信息和电子商 务来衡量。 参考文献:参照卢江(2024)的做法,从科技生产力、绿色生产力和数字 生产力3个方面计

    wx469网络安全科普系统-springboot+vue+uniapp.zip(可运行源码+sql文件+文档)

    经过技术的发展目前的网络安全科普里一般也采用了计算机类的科普宣传网,但是都是综合性的平台,信息种类杂乱,所以经过一番考研调查后,决定设计单纯的网络安全科普系统,这样不单单是方便了管理员,对于用户来说...

    wx489青少年科普教学平台-springboot+vue+uniapp.zip(可运行源码+sql文件+文档)

    青少年科普教学系统平台使用Java语言进行编码,使用Mysql创建数据表保存本系统产生的数据。系统可以提供信息显示和相应服务,其管理青少年科普教学系统平台信息,查看青少年科普教学系统平台信息,管理青少年科普...

    科普网站平台建设方案书.pdf

    3. 科普网站的发展需求:科普网站的发展需求包括提高科普网站的知名度、提高科普网站的影响力、提高科普网站的参与度等。 二、科普网站建设方案 本科普网站建设方案的总体设计思路是建设一个综合性的科普网站,...

    基于SQL数据库的性能优化的探讨.pdf

    尽管计算机普及率提高,但硬件配置不足仍是限制SQL数据库性能的瓶颈。 #### 2. SQL数据库性能优化措施 ##### 2.1 索引优化措施 合理地优化索引是提高SQL数据库性能的重要手段。对索引的优化关键在于遵循索引建立的...

    二阶SQL注入攻击防御模型.pdf

    随着互联网技术的迅猛发展,Web应用程序已经变得越来越普及,尤其是那些依赖于数据库的Web应用程序,它们被广泛应用于企业的各种业务系统中。但是,由于开发人员的专业水平和经验层次不齐,Web应用程序的安全隐患...

    wx029居民健康监测系统-springboot+vue+uniapp-小程序.zip(可运行源码+sql文件+文档)

    健康科普管理页面,此页面提供给管理员的功能有:查看已发布的健康科普数据,修改健康科普,健康科普作废,即可删除,还进行了对健康科普名称的模糊查询 健康科普信息的类型查询等等一些条件。公告类型管理页面,此...

    wx325个人健康数据管理系统-ssm+vue+uniapp.zip(可运行源码+sql文件+文档)

    本基于微信小程序的个人健康数据管理系统管理员功能有个人中心,用户管理,知识科普管理,健康信息共享管理,健康计划管理,健康数据管理,数据分析管理,系统管理等。用户功能有个人中心,知识科普,交流区,健康...

    274ssm_mysql_jsp 高校心理测评设计与分析系统.zip(可运行源码+sql文件+文档)

    (6) 科普管理模块:管理员可以通过对心理知识的管理功能推送出适合的文章或者句子。 (7) 用户管理模块:用户管理模块通过管理员管理用户信息,此模块中我们可以给用户的权限发生变化,同样,我们可以查看到用户的...

Global site tag (gtag.js) - Google Analytics