mysql牛客网网刷题函数总结

SQL 55) -LIMIT 分页查询employees表

LIMIT 语句结构： LIMIT X,Y

• Y ：返回几条记录
• X：从第几条记录开始返回（第一条记录序号为0，默认为0）

SELECT *
FROM employees
LIMIT 5,5 表示返回第二页数据，每页5行

sql 54 找排除当前最大、最小salary之后的员工的平均工资avg_salary

即用not in 排除最大和最小

select avg(a.salary) avg_salary
from salaries a
where a.to_date='9999-01-01'
and a.salary not in (select max(b.salary) from salaries b where b.to_date='9999-01-01')
and a.salary not in (select min(b.salary) from salaries b where b.to_date='9999-01-01');

其中，最大值要限定是to_date=‘9999-01-01’ 时的最大值，最小值要限定是to_date='9999-01-01’的最小值，然后求平均即可，本题主题意思不明确或者说是与答案根本不符，本题没有说明要去掉to_date='9999-01-01’这个条件下的最大、最小值，所以很多人就编译不通过。

SQL53 按照dept_no进行汇总

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。
答案：
SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp GROUP BY dept_no

SQL52 获取Employees中的first_name

三种方法都可以用
LEFT(s,n)返回字符串 s 的前 n 个字符
RIGHT(s,n)返回字符串 s 的后 n 个字符
第一种：（right）
select first_name
from employees
order by right(first_name,2)
第二种：（substr）
select first_name from employees  order by substr(first_name,-2)
第三种：（substring）
select first_name from employees  order by substring(first_name,-2)

MySQL substr() 函数 ：截取字符串

substr( )与substring( )意思相等

函数语法

substr(str, pos)
截取从pos位置开始到最后的所有str字符串

substr(str, pos, len)

参数说明:

str为列名/字符串；
pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么 表示就从倒数第几个开始截取；
len为截取字符个数/长度

SQL51 查找字符串 10,A,B 中逗号,出现的次数cnt

思路：
把串 “10,A,B” 中的 逗号用空串替代， 变成了 “10AB”
然后原来串的长度 - 替换之后的串的长度 就是 被替换的 逗号的个数

select (length("10,A,B") - length(replace("10,A,B",",","")) )
as cnt;
==知识点总结==
统计字符串长度：
char_length('string')/char_length(column_name)
1、返回值为字符串string或者对应字段长度，长度的单位为字符，一个多字节字符（例如，汉字）算作一个单字符；
2、不管汉字还是数字或者是字母都算是一个字符；
3、任何编码下，多字节字符都算是一个字符；

length('string')/length(column_name)
1、utf8字符集编码下,一个汉字是算三个字符,一个数字或字母算一个字符。
2、其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。
select char_length("10,A,B")-char_length(replace("10,A,B",",",""))

SQL50 将employees表中的所有员工的last_name和f

问题描述：将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
MYSQL解法：

SELECT CONCAT(RTrim(last_name),"'",LTrim(first_name)) AS name
FROM employees;

注意：
建议这里的last_name,first_name采用去左、去右空格的函数R(L)Trim()，防止联结时部分字段存在空格导致结果出错。
补充：
RTrim():去除字符串右端空格
LTrim():去除字符串左端空格
Trim():去除字符串两端的空格

SQL48 将所有获取奖金的员工当前的薪水增加10%

方法1：连接查询（先join两张表）
update salaries as s join emp_bonus as e on s.emp_no=e.emp_no
set salary=salary*1.1
where to_date='9999-01-01'
方法2：子查询（两次select）

update salaries
set salary=salary*1.1
where to_date='9999-01-01'
    and salaries.emp_no in(select emp_no from emp_bonus)
比较：
推荐使用连接查询（JOIN）
连接查询不需要创建+销毁临时表，因此速度比子查询快。

SQL 46) -ADD CONSTRAINT FOREIGN KEY 创建外键约束

方法）创建外键
代码
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);
创建外键语句结构：
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY (<列名>)
REFERENCES <关联表>（关联列）

补充

外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

创建外键

建表时指定外键约束
-- 创建外键的方式一 : 创建子表同时创建外键

-- 年级表 (id\年级名称)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

SQL45 将titles_test表名修改为titles_2017

关于MySQL中ALTER TABLE 的命令用法

看到有朋友在疑问为啥会是ALTER TABLE + 修改的表+修改内容/方式。
这里补充一下在MySQL中ALTER TABLE 语句的用法。

常见的有:（我写的不是很规范，只是方便大家理解；更规范的语法请参考MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html)

ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
ALTER TABLE 表名 RENAME TO/AS 新表名;
ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
ALTER TABLE 表名 CHANGE 列名和定义都可以改变。