mysql 基础1

一、查询第二大的分数或者ID, 表数据如下

查询语句 ：
SELECT MAX(score) FROM methodlock where score<( SELECT MAX(score) from methodlock);
还可以使用下面的写法：
SELECT MAX(score) from methodlock where score not in ( SELECT MAX(score) FROM methodlock );

– 如果查询两个字段，然后使用一个score 进行分组 ，查询结果会会把两个字段都查询出来，只不过第一是默认升序，
第二个是不排序。
SELECT score,number FROM methodlock where 1=1 ORDER BY score;

二、inner join , left join , rigth join
test 表

methodlock表 ， methodlock表通过tid 和test表关联

a、inner join 内联查询 获取两个表中字段匹配关系的记录
SELECT mk.,t. FROM methodlock mk INNER JOIN test t on mk.tid=t.id where 1=1;
查询结果，因为在test表中，第5条数据在 methodlock 表中没有与之关联的 所有只会查询出有匹配关系的数据

b、left join 获取左表所有记录，即使右表没有对应匹配的记录。

SELECT mk.id,mk.method_name,mk.score,mk.update_time,mk.number,mk.tid, t.id as testid,t.name,t.age,t.number as tnumber FROM methodlock mk LEFT JOIN test t on mk.tid=t.id;
查询结果如下：可以知道如果和methodlock 表中tid在 和test表中没有对应的Id，那么不符合的就会显示为null.

c、rigth join 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SELECT mk.id,mk.method_name,mk.score,mk.update_time,mk.number,mk.tid, t.id as testid,t.name,t.age,t.number as tnumber FROM methodlock mk RIGHT JOIN test t on mk.tid=t.id;

三、在创建表和插入数据时 mysql会 下面的错，
a）、1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决办法: 通过select version(), @@sql_mode; 查看mysql版本号 ，和sql_mode，查询结果如下

然后修改sql_mode ,下面的语句就是用’’ 来替换 ONLY_FULL_GROUP_BY,
SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));
执行成功后。 只在当前查询页面有效，不能一次性解决问题！

当再次创建一个新表是还是有该问题，不能一次性解决该问题。

2、去掉ONLY_FULL_GROUP_BY，重新设置值。
01
set @@global.sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
3、上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：
1
set sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
现在重新插入数据不会报错了（需要关闭下客户端在重新打开 ，再重新插入数据）

如果还不行那么就修改mysql的配置文件(windows下)my.ini 文件 ，修改了之后重启mysql

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=E:\mysql-5.7.9-winx64
# 设置mysql数据库的数据的存放目录
datadir=E:\mysql-5.7.9-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

四、查询成绩。
a、查询每门成绩大于80分的学生姓名和课程
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
score double NOT NULL,
name varchar(50) DEFAULT NULL,
course varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO student VALUES (‘1’, ‘70’, ‘小明’, ‘语文’);
INSERT INTO student VALUES (‘2’, ‘86’, ‘小张’, ‘数学’);
INSERT INTO student VALUES (‘3’, ‘83’, ‘小红’, ‘语文’);
INSERT INTO student VALUES (‘4’, ‘93’, ‘小李’, ‘数学’);
INSERT INTO student VALUES (‘5’, ‘89’, ‘小刘’, ‘语文’);
INSERT INTO student VALUES (‘6’, ‘67’, ‘小宇’, ‘数学’);
INSERT INTO student VALUES (‘7’, ‘77’, ‘小明’, ‘英语’);
INSERT INTO student VALUES (‘8’, ‘66’, ‘小张’, ‘语文’);
INSERT INTO student VALUES (‘9’, ‘85’, ‘小李’, ‘物理’);
INSERT INTO student VALUES (‘10’,‘84’,‘小刘’, ‘化学’);

查询语句
SELECT s.name,s.course,s.score FROM student s where s.score>80 GROUP BY s.score ;

查询不在 80分以下的。
SELECT s.name,s.course,s.score from student s where s.id not in(SELECT s.id FROM student s where s.score<80 GROUP BY s.score )

也可以使用 group by分组后在使用 having 来处理
SELECT s.name,s.course,s.score FROM student s GROUP BY s.score HAVING MIN(s.score)>80;

b、查询各科平均成绩
– 各科成绩
SELECT AVG(CASE WHEN course=‘语文’ THEN score ELSE 0 END) as ‘语文’,
AVG(CASE WHEN course=‘数学’ THEN score ELSE 0 END) as ‘数学’,
AVG(CASE WHEN course=‘英语’ THEN score ELSE 0 END) as ‘英语’,
AVG(CASE WHEN course=‘物理’ THEN score ELSE 0 END) as ‘物理’,
AVG(CASE WHEN course=‘化学’ THEN score ELSE 0 END) as ‘化学’
FROM student

c、查询总成绩 和 总成绩
– 各科成绩
SELECT id,name , sum(CASE WHEN course=‘语文’ THEN score ELSE 0 END) as ‘语文’,
sum(CASE WHEN course=‘数学’ THEN score ELSE 0 END) as ‘数学’,
sum(CASE WHEN course=‘英语’ THEN score ELSE 0 END) as ‘英语’,
sum(CASE WHEN course=‘物理’ THEN score ELSE 0 END) as ‘物理’,
sum(CASE WHEN course=‘化学’ THEN score ELSE 0 END) as ‘化学’,
CONVERT(SUM(score),DECIMAL(5,2)) as total
FROM student GROUP BY name ;

定义用户变量
SET @var_name = expr [, @var_name = expr] …
也可使用select语句来定义：
SELECT @var_name := expr [, @var_name = expr] …

– @来表示，赋值用:=来实现；添加行号
SELECT @ROWNO:= @ROWNO + 1 AS ROWNO, r.*
FROM (SELECT id, name,
sum(CASE WHEN course=‘语文’ THEN score ELSE 0 END) as ‘语文’,
sum(CASE WHEN course=‘数学’ THEN score ELSE 0 END) as ‘数学’,
sum(CASE WHEN course=‘英语’ THEN score ELSE 0 END) as ‘英语’,
sum(CASE WHEN course=‘物理’ THEN score ELSE 0 END) as ‘物理’,
sum(CASE WHEN course=‘化学’ THEN score ELSE 0 END) as ‘化学’,
CONVERT(SUM(score),DECIMAL(5,2)) as total
FROM student GROUP BY name) r,
(SELECT @ROWNO := 0) t

五、GROUP BY 和 order by 一起使用

如果在group by 前面使用 order by ，在执行的时候会报错
order by 应该方法在 group by 后面使用