百分百胜率的秘技？？好好写sql。。。

近期表哥左右的面试，无一例外的都有一道写sql的题目。。

发一波帖子。。。分享一下学习笔记。。。

复制回去navicat，建立一个查询，执行一下，初始化数据库，就可以了。。下面有sql。。

基础打好后建议刷刷牛客的sql在线编程。。。刷个二三十就好了....

/*

Navicat MySQL Data Transfer

Source Server         : mysql

Source Server Version : 50626

Source Host           : localhost:3306

Source Database       : learn

Target Server Type    : MYSQL

Target Server Version : 50626

File Encoding         : 65001

Date: 2019-08-15 17:18:08

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for class

-- ----------------------------

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

`classNum` int(11) NOT NULL,

`className` varchar(255) NOT NULL,

PRIMARY KEY (`classNum`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of class

-- ----------------------------

INSERT INTO `class` VALUES ('1', '计算机网络');

INSERT INTO `class` VALUES ('2', '操作系统');

INSERT INTO `class` VALUES ('4', '数学分析');

INSERT INTO `class` VALUES ('5', '高等代数');

INSERT INTO `class` VALUES ('6', '解析几何');

/*

Navicat MySQL Data Transfer

Source Server         : mysql

Source Server Version : 50626

Source Host           : localhost:3306

Source Database       : learn

Target Server Type    : MYSQL

Target Server Version : 50626

File Encoding         : 65001

Date: 2019-08-15 17:18:17

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for ref

-- ----------------------------

DROP TABLE IF EXISTS `ref`;

CREATE TABLE `ref` (

`classNum` int(11) NOT NULL,

`studetId` varchar(11) NOT NULL,

`id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `classId` (`classNum`),

KEY `studentId` (`studetId`),

CONSTRAINT `classId` FOREIGN KEY (`classNum`) REFERENCES `class` (`classNum`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `studentId` FOREIGN KEY (`studetId`) REFERENCES `student` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of ref

-- ----------------------------

INSERT INTO `ref` VALUES ('1', 'Elliot', '1');

INSERT INTO `ref` VALUES ('1', 'Shayla1', '2');

INSERT INTO `ref` VALUES ('2', 'Shayla2', '3');

/*

Navicat MySQL Data Transfer

Source Server         : mysql

Source Server Version : 50626

Source Host           : localhost:3306

Source Database       : learn

Target Server Type    : MYSQL

Target Server Version : 50626

File Encoding         : 65001

Date: 2019-08-15 17:18:23

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for score

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`id` int(11) NOT NULL,

`refId` int(11) NOT NULL,

`score` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `refId` (`refId`),

CONSTRAINT `refId` FOREIGN KEY (`refId`) REFERENCES `ref` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '80');

INSERT INTO `score` VALUES ('2', '2', '88');

INSERT INTO `score` VALUES ('3', '3', null);

/*

Navicat MySQL Data Transfer

Source Server         : mysql

Source Server Version : 50626

Source Host           : localhost:3306

Source Database       : learn

Target Server Type    : MYSQL

Target Server Version : 50626

File Encoding         : 65001

Date: 2019-08-15 17:18:33

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`name` varchar(255) NOT NULL,

`password` varchar(255) NOT NULL,

`age` int(11) NOT NULL,

PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('Elliot', '11111', '111');

INSERT INTO `student` VALUES ('Shayla', 'qqqqq', '11');

INSERT INTO `student` VALUES ('Shayla1', 'qqqqq', '11');

INSERT INTO `student` VALUES ('Shayla2', 'qqqqq', '11');

INSERT INTO `student` VALUES ('Shayla3', 'qqqqq', '20');

INSERT INTO `student` VALUES ('Shayla4', 'qqqqq', '2');

INSERT INTO `student` VALUES ('Shayla5', 'qqqqq', '6');

INSERT INTO `student` VALUES ('Shayla6', 'qqqqq', '7');

INSERT INTO `student` VALUES ('Shayla7', 'qqqqq', '8');

#DISTINCT

SELECT DISTINCT password, age FROM student ;

#DESC 降序

SELECT * FROM student ORDER BY age DESC;

#ASC 升序 默认的

SELECT * FROM student ORDER BY age ASC;

SELECT * FROM student ORDER BY age , password;

SELECT * FROM student ORDER BY age , password DESC;

#LIMIT

SELECT * FROM student LIMIT 1, 1;

#LIKE

SELECT * FROM student WHERE `password` LIKE '%q%';

#NOT LIKE

SELECT * FROM student WHERE `password` NOT like '%q%';

#通配符

SELECT * FROM student WHERE `password` LIKE '_q%';

#IN

SELECT * FROM student WHERE `password` IN ('qqqqq', '1111');

#BETWEEN....AND...

SELECT * FROM student WHERE age BETWEEN 1 AND 10;

#NOT....BETWEEN....AND...

SELECT * FROM student WHERE age NOT BETWEEN 1 AND 10;

#BETWEEN....AND...IN

SELECT * FROM student WHERE (age BETWEEN 1 AND 10) AND age NOT IN (2, 6);

#AS

SELECT age AS student_age FROM student;

#笛卡尔积

SELECT * FROM student s, class c;

#LEFT JOIN

SELECT s.name, c.className FROM student s LEFT JOIN class c on s.`name` IN (SELECT r.studetId FROM ref r WHERE r.classNum = c.classNum);

#INNER JOIN

SELECT s.name, c.className FROM student s INNER JOIN class c on s.`name` IN (SELECT r.studetId FROM ref r WHERE r.classNum = c.classNum);

#RIGHT JOIN

SELECT s.name, c.className FROM student s RIGHT JOIN class c on s.`name` IN (SELECT r.studetId FROM ref r WHERE r.classNum = c.classNum);

#FULL JOIN

SELECT s.name, c.className FROM student s FULL JOIN class c on s.`name` IN (SELECT r.studetId FROM ref r WHERE r.classNum = c.classNum);

SELECT 1 = NULL;

SELECT NULL = NULL;

SELECT * FROM score WHERE score IS NULL;

SELECT * FROM score WHERE score IS NOT NULL;

SELECT AVG(score) FROM score;

SELECT AVG(score) FROM score WHERE score IS NOT NULL;

SELECT COUNT(*) FROM score;

#GROUP BY

SELECT classNum, SUM(id) as Id FROM ref GROUP BY classNum;

#GROUP BY...HAVING

SELECT classNum, SUM(id) as Id FROM ref GROUP BY classNum HAVING classNum > 1;