在练习牛客网Sql编程题时学到的一些东西
初步整理,再刷再更新吧。一开始做题感觉不难,越往后越觉得自己脑子是糨糊
1. MySQL官方文档有说明,in关键字适合确定数量的情况,一般效率较低,不推荐使用。能用in关键字的语句都可以转化为使用join的语句,推荐使用join关键字。
2. concat 函数可以用作字符串拼接, select CONCAT(last_name," ",first_name) as name from employees 表示将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分。
3. 在创建表时,可以使用 comment 添加注释,如:
create table test1 ( field_name int comment '字段的注释' )comment='表的注释'; -- 修改表的注释 alter table test1 comment '修改后的表的注释'; --修改字段的注释 alter table test1 modify column field_name int comment '修改后的字段注释'; --注意:字段名和字段类型照写就行 -- 查看表注释的方法 -- 在生成的SQL语句中看 show create table test1; -- 在元数据的表里面看 use information_schema; select * from TABLES where TABLE_SCHEMA='my_db' and TABLE_NAME='test1' \G -- 查看字段注释的方法 show full columns from test1; -- 在元数据的表里面看 select * from COLUMNS where TABLE_SCHEMA='my_db' and TABLE_NAME='test1' \G
4. 强制使用索引查找: FORCE INDEX(索引名)
select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;
5. 在已有的表中新增一列
alter table 表名 add (创建列的语句)
6. MySQL的UPDATE或DELETE中子查询不能为同一张表,可将查询结果再次SELECT。
即mysql不允许在子查询原始表的时候,同时更新和删除原表数据,但是sqllite可以这样。
如果想删除,必须给查询到的数据表一个别名,再执行一次从临时表中的查询,之后再删除。
整理自牛客网第42题。
-- 这种是不行的 DELETE FROM employees WHERE emp_no IN (SELECT emp_no FROM employees WHERE emp_no = 10002) -- 这种是不会报错的 DELETE FROM employees WHERE emp_no IN (SELECT * FROM (SELECT emp_no FROM employees WHERE emp_no = 10002) AS t1)
7. 涉及到多处同时更新时,使用逗号链接。
UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01' WHERE to_date = '9999-01-01';
8. 使用replace实现更新
replace语句根据主键的值确定被替换的是哪一条记录,先删除该条记录,再用输入值进行替换。如果执行replace语句时,不存在要替换的记录,就会插入一条新纪录。
-- 替换主键为5的那一行 REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
update可以与replace进行操作
UPDATE titles_test SET emp_no = REPLACE(emp_no, 10001, 10005) WHERE id = 5;
replace(字段,“需要替换的值”,“替换后的值”) ,同时注意替换后的一个赋值操作
9. 对表名进行更新 ALTER TABLE 表名 RENAME TO/AS 新表名;
-- ALTER TABLE 表名 ADD 列名/索引/主键/外键等; -- ALTER TABLE 表名 DROP 列名/索引/主键/外键等; -- ALTER TABLE 表名 ALTER 仅用来改变某列的默认值; -- ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名; -- ALTER TABLE 表名 RENAME TO/AS 新表名; -- ALTER TABLE 表名 MODIFY 列的定义但不改变列名; -- ALTER TABLE 表名 CHANGE 列名和定义都可以改变。
10. 聚合函数 group_concat(X,Y) ,该函数与 group by 配套使用,可以聚合每个分组的情况,默认以逗号链接。 group_concat(emp_no SEPARATOR ';') ,可以此更换链接方式。
11. 关键字 exist 与 not exist 。用在where条件筛选后面。 exists是先从主查询中取得一条数据,再代入到子查询中,执行一次子查询,判断子查询是否能返回结果,主查询有多少条数据,子查询就要执行多少次; in是先执行子查询,得到一个结果集,将结果集代入外层谓词条件执行主查询,子查询只需要执行一次.
12. join时的一点小收获:on后面不一定是等于符号
SELECT s2.emp_no,s2.salary,SUM(s1.salary) AS running_total FROM salaries AS s1 INNER JOIN salaries AS s2 ON s1.emp_no <= s2.emp_no WHERE s1.to_date = "9999-01-01" AND s2.to_date = "9999-01-01" GROUP BY s2.emp_no
13. 窗口函数
这里小小的写上一点,我看这个版块还蛮多且比较重要,之后会详细梳理一下。
基本语法: OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] ) 。over开开窗函数必须与聚合函数或者排序函数一起使用。
-- 计算前n个人的工资和 SELECT emp_no,salary, SUM(salary) OVER (ORDER BY emp_no) AS running_total FROM salaries WHERE to_date = '9999-01-01';
14. 关于四舍五入,有三个函数
-- round(x,d),四舍五入。round(x) ,其实就是round(x,0),也就是默认d为0 select round(110.35,1); # 110.4 -- TRUNCATE(x,d),直接截取需要保留的小数位 select TRUNCATE(110.35,1); # 110.3 -- FORMAT(x,d),四舍五入,保留d位小数,返回string类型 select FORMAT(110.35,1); # 110.4
15. 关于 floor 与 ceiling
floor函数向下取整(返回小于等于该值的最大整数),ceil() / ceiling()向上取整。
SELECT FLOOR(6.3) # 6 SELECT CEILING(6.7) # 7
16. 筛选某段时间的数据 where date_format(date,'%Y')='2025' ,筛选20205年的数据。
17. 在SQL中,WITH语句指定一个临时命名的结果集,称为公共表达式(CTE)。如
with s1 as(select *, row_number() over (partition by product_name order by date asc) as t_rank1, row_number() over (partition by product_name order by date desc) as t_rank2 from order_info) select * from s1