百分百胜率的秘技??好好写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;

#MySQL##笔记#
全部评论
表哥太强了
点赞 回复
分享
发布于 2019-08-20 14:45
牛客必刷SQL题 https://www.nowcoder.com/ta/sql
1 回复
分享
发布于 2019-08-20 14:48
乐元素
校招火热招聘中
官网直投
感谢大表哥的分享
点赞 回复
分享
发布于 2019-08-20 14:44
表哥太强了
点赞 回复
分享
发布于 2019-08-20 14:44
点赞 回复
分享
发布于 2019-08-21 16:47
做了一遍,很基础。。面试里的会考的很难嘛,多表连接,各种子查询那种??
点赞 回复
分享
发布于 2020-06-04 15:17

相关推荐

5 49 评论
分享
牛客网
牛客企业服务