Mysql语法再巩固

话不多说 直接代码 

##删除表 drop table student ; ##创建表 create table student( id int primary key comment 'id', name varchar(255) not null , time datetime not null, card_id varchar(255) not null, score int not null, key asda (name),## 创建普通索引 index asdasd (time), ## 一样 等价于 key 创建 普通索引 UNIQUE INDEX card_id_unique (card_id) #创建唯一索引 )engine=innodb default charset=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生';##utf8mb4_general_ci 排序规则 忽略大小写 忽略 后面空格 select * from student s where s.name = 'j'; insert into student (name,time,card_id,score) values ('jmj2','20250301080700','13512',1), ('jmj3','20250301080700','13512',3), ('jmj4','20250301080700','13512',5), ('jmj5','20250301080700','13512',6), ('jmj6','20250301080700','13512',2) ; ##添加主键 alter table student add primary key(id); ##修改字段 alter table student modify column name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT null; alter table student modify column id int auto_increment; ##创建普通索引 create index asdjkladl on student (name) ##创建唯一索引 CREATE UNIQUE INDEX unique_index_name ON student (card_id); ##删除 索引 DROP INDEX asdjkladl ON student; ##删除唯一索引 DROP INDEX card_id_unique ON student; ##删除主键 ALTER TABLE student DROP PRIMARY KEY; select id from student s union all ##联合操作 select id from product_info pi2 ; select distinct name from student s ; select max(sort) from (select sort from blue_templat_param_group_and_param_relation btpgapr where btpgapr.blue_template_param_group_id = '1879815632434900992' union all select sort from blue_tooth_composite_param btcp where btcp.blue_template_param_group_id = '1879815632434900992' ) as s order by sort asc select id,blue_template_param_group_id from blue_templat_param_group_and_param_relation btpgapr where btpgapr.blue_template_param_group_id = '1879815632434900992' union all #不去重 select id,blue_template_param_group_id from blue_tooth_composite_param btcp where btcp.blue_template_param_group_id = '1879815632434900992' select blue_template_param_group_id from blue_templat_param_group_and_param_relation btpgapr where btpgapr.blue_template_param_group_id = '1879815632434900992' union ##去重的 select blue_template_param_group_id from blue_tooth_composite_param btcp where btcp.blue_template_param_group_id = '1879815632434900992' ##查出成绩为前三的 学生 所有的 SELECT s1.id, s1.name, s1.score FROM student s1 WHERE ( SELECT COUNT(DISTINCT s2.score) FROM student s2 WHERE s2.score > s1.score ) < 3 ORDER BY s1.score DESC; ##窗口函数 -- 查询成绩排名前三的学生 WITH RankedStudents AS ( SELECT id, name, score, DENSE_RANK() OVER (ORDER BY score DESC) as score_rank ##不会漏下并列的 FROM student ) SELECT id, name, score FROM RankedStudents WHERE score_rank <= 3; #这是直接跳过并列计数 WITH RankedStudents AS ( SELECT id, name, score, Rank() OVER (ORDER BY score DESC) as score_rank ##不会漏下并列的 FROM student ) SELECT id, name, score FROM RankedStudents WHERE score_rank <= 3; ## 在窗口内计算某列的总和。 select * , SUM(score) over (partition by card_id) as total_score from student s select * , avg(score) over (partition by card_id) as total_score from student s select * , min(score) over (partition by card_id) as total_score from student s select * , max(score) over (partition by card_id) as total_score from student s ##返回窗口内当前行之前某一行的值。 select * , LAG(score,1,0) over (partition by card_id order by score ) as total_score from student s ##返回窗口内当前行之后某一行的值。 select * , LEAD(score,1,0) over (partition by card_id order by score ) as total_score from student s ##这是子查询 SELECT employee_id, name, salary, (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) as dept_avg_salary FROM employees e1; -- 子查询计算每个部门的平均工资 WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) -- 主查询将员工表与子查询结果进行连接 SELECT e.employee_id, e.name, e.salary, das.avg_salary AS dept_avg_salary FROM employees e JOIN dept_avg_salary das ON e.department_id = das.department_id;